Quick answer: The trading and profit and loss account format records a business’s results for the year, prepared from the trial balance at the year-end. The Trading Account finds the gross profit: net sales plus closing stock, minus opening stock, net purchases and direct expenses. The Profit & Loss Account then subtracts indirect expenses, depreciation and provisions, and adds other income, to find the net profit. The Balance Sheet lists what the business owns and owes on the last day of the year. The free Xplorexcel template prepares all three in the traditional T-format from your trial balance, adjustments included.
Key takeaways
- Enter the trial balance once and pick a head for each account. The final accounts are then prepared automatically.
- Covers the common adjustments: closing stock, outstanding and prepaid expenses, accrued income, income received in advance, depreciation, bad debts, provision for doubtful debts, interest on capital and interest on drawings.
- Traditional T-format with Dr and Cr sides, “To” and “By” wording, and gross profit carried down (c/d) and brought down (b/d).
- Checks that the trial balance tallies, that every account has a head, and that the balance sheet tallies.
- Calculates key ratios: gross profit, net profit, current, quick, stock turnover and return on capital.
Trading and profit and loss account format
Trading Account (finds gross profit)
| Dr. (debit side) | Cr. (credit side) |
|---|---|
| To Opening Stock | By Sales (less Sales Returns) |
| To Purchases (less Purchase Returns) | By Closing Stock |
| To Direct Expenses (wages, carriage inward, freight, power …) | By Gross Loss c/d (if any) |
| To Gross Profit c/d (balancing figure) |
Profit & Loss Account (finds net profit)
| Dr. (debit side) | Cr. (credit side) |
|---|---|
| To Gross Loss b/d (if any) | By Gross Profit b/d |
| To Indirect Expenses (salaries, rent, electricity, insurance …) | By Indirect Income (commission, discount, interest received …) |
| To Bad Debts and Provision for Doubtful Debts | By Interest on Drawings |
| To Depreciation | By Net Loss (if any) |
| To Interest on Capital | |
| To Net Profit (transferred to Capital) |
As a rule of thumb, direct expenses (those involved in buying or making the goods) go in the Trading Account, and indirect expenses (running the office and selling) go in the Profit & Loss Account. The template puts each account on the right side according to the head you choose.
Balance sheet format (sole proprietor)
| Liabilities | Assets |
|---|---|
| Capital + Net Profit (or − Net Loss) + Interest on Capital − Drawings − Interest on Drawings | Fixed Assets (less depreciation) |
| Long-term Loans | Investments |
| Sundry Creditors, Bills Payable | Closing Stock |
| Outstanding Expenses | Sundry Debtors (less bad debts and provision) |
| Income Received in Advance | Cash, Bank, Prepaid Expenses, Accrued Income |
How adjustments are treated (the two-way effect)
Every adjustment appears in two places. This is the part students most often get wrong, and the template handles it automatically.
| Adjustment | Effect 1 | Effect 2 |
|---|---|---|
| Closing stock | Trading A/c, credit side | Balance Sheet, assets |
| Outstanding expense | Added to that expense | Balance Sheet, liabilities |
| Prepaid expense | Deducted from that expense | Balance Sheet, assets |
| Accrued income | Added to that income | Balance Sheet, assets |
| Income received in advance | Deducted from that income | Balance Sheet, liabilities |
| Depreciation | P&L A/c, debit side | Deducted from the asset |
| Further bad debts and new provision | P&L A/c, debit side (less old provision) | Deducted from debtors |
| Interest on capital | P&L A/c, debit side | Added to capital |
| Interest on drawings | P&L A/c, credit side | Deducted from capital |
Worked example: M/s Xplore Traders
The sample file uses a 28-account trial balance totalling ₹11,97,000. The adjustments are: closing stock ₹70,000, outstanding salaries ₹5,000, outstanding wages ₹4,000, prepaid insurance ₹1,500, accrued commission ₹1,000, depreciation on machinery and furniture at 10% and on the building at 5%, further bad debts ₹2,000, provision at 5% on debtors, interest on capital at 6% and interest on drawings ₹1,500.
| Net sales | ₹6,85,000 |
| Gross profit | ₹2,65,000 (38.7%) |
| Net profit | ₹1,17,600 (17.2%) |
| Closing capital | ₹4,04,100 |
| Balance sheet total | ₹5,88,100 on both sides |
How to prepare final accounts in Excel (4 steps)
- Setup: Enter the firm name and the year-end date.
- Trial Balance: Enter each ledger balance as a debit or credit and choose its head, such as Purchases, Direct Expenses or Fixed Assets. For fixed assets, add the depreciation rate. Make sure the trial balance tallies.
- Adjustments: Enter the closing stock, outstanding and prepaid expenses, accrued and advance income, bad debts, provision, and interest on capital and drawings.
- Final Accounts: Check that the balance sheet tallies, then print. The Ratios sheet shows the key numbers.
Download the Final Accounts Template in Excel (Free)
File: Final Accounts – Trading, P&L and Balance Sheet 1.0 · Format: .xlsx · Macros: none · Price: free.
Students: try solving a textbook question by hand first, then check your answer with the template.
Frequently asked questions
What is the format of the trading and profit and loss account?
Both use a two-sided T-format. The Trading Account shows opening stock, net purchases and direct expenses on the debit side, and net sales and closing stock on the credit side, which gives the gross profit. The Profit & Loss Account starts with the gross profit, then shows indirect expenses on the debit side and indirect incomes on the credit side, which gives the net profit.
What is the difference between direct and indirect expenses?
Direct expenses are linked to buying or making goods, such as wages, carriage inward, freight and factory power, and go to the Trading Account. Indirect expenses relate to running and selling, such as salaries, rent, advertising and depreciation, and go to the Profit & Loss Account.
Why is closing stock shown twice?
Closing stock is an adjustment. It reduces the cost of goods sold, so it appears on the credit side of the Trading Account, and it is an asset at the year-end, so it also appears in the Balance Sheet.
How is provision for doubtful debts treated?
The new provision is worked out on debtors after deducting further bad debts. In the P&L Account, bad debts plus the further bad debts plus the new provision, minus the old provision, is debited. In the Balance Sheet, further bad debts and the new provision are deducted from debtors.
Is this format valid for companies?
No. This is the sole-proprietor format used in school and college accountancy and by small traders. Companies must prepare financial statements in the Schedule III format under the Companies Act, 2013.
Can I use this template in Google Sheets?
Yes. Upload the .xlsx file to Google Drive and open it with Google Sheets. It has no macros.
More free accounting templates from Xplorexcel
- Cash Book Template
- Petty Cash Book Format in Excel
- Purchase Order Format with GST
- Income and Expenses Tracker
- All Excel templates
Last reviewed: October 2026.
