This is a library of small accounting templates in Excel, each one a live sheet in its own tab that opens on a worked example. Replace the example with your figures, then save it as an Excel file with its formulas from the download button below the sheet.
In the shop example: net sales are SAR 1,231,500 and cost of goods sold is SAR 856,900, so gross profit is SAR 374,600 (30.4%); after operating expenses of SAR 289,000, operating profit is SAR 85,600.
How to use the templates
- Pick the template from the tabs; the figures in each tab stay as you entered them when you switch between tabs.
- Type your figures over the example or press "Start blank". Shaded columns are calculated, not typed.
Trading account and operating profit
A trading account is the first part of the income statement for a business that sells goods: net sales minus cost of goods sold equals gross profit. Cost of goods sold = opening stock + purchases − purchase returns + purchase expenses − closing stock. In the example: 142,000 + 860,000 − 12,400 + 23,600 − 156,300 = SAR 856,900. The stock rows are highlighted because any error in the stock count passes straight into gross profit.
Advance payments register
An advance from a customer is a liability until you deliver the goods, and an advance paid to a supplier is an asset until the invoice arrives. In the example, SAR 26,000 of customer advances and SAR 16,850 of supplier advances are still unsettled.
Receivable and payable account reconciliation
Reconciling a customer or supplier account means comparing your books with the other party's statement transaction by transaction. In the customer example, our books show SAR 56,125 and the customer statement SAR 45,850. The SAR 10,275 difference is explained by three rows: a return the customer has not recorded, a cheque we have not recorded, and an invoice recorded at two different amounts.
Accrual basis and cash basis
The cash basis records revenue when cash is received and expenses when paid; the accrual basis records them in the period they belong to. To convert: cash + accrued at end − accrued at start + prepaid at start − prepaid at end. In the example, net profit is SAR 441,500 on a cash basis and SAR 526,100 on an accrual basis; the biggest reason is SAR 144,000 of rent paid, of which only SAR 96,000 belongs to the period. An accrual entry records the expense at period end and is cleared by the payment entry when it is paid, under double-entry bookkeeping.
Contribution margin and marginal revenue
Contribution margin is revenue minus variable costs. In the bakery example it totals SAR 52,754, a ratio of 47.5%, against fixed costs of SAR 45,500, so operating profit is SAR 7,254 and break-even sales are SAR 95,797.19. The custom occasion cake loses SAR 12 on every unit. Marginal revenue, what one extra unit adds to total revenue, has its own sheet in advanced mode.
Common mistakes
- Valuing closing stock at selling price instead of cost, which inflates gross profit.
- Recording a customer advance as revenue before the goods are delivered.
- Booking rent paid for two years as one year's expense.
Regulatory and accounting notes
An advance received by a VAT-registered supplier in Saudi Arabia may trigger VAT when it is received, under the Implementing Regulations of the VAT Law; check the current rule with the Zakat, Tax and Customs Authority. The 15% rate in advanced mode is editable.
When separate spreadsheets multiply and their figures disagree
Microtec accounting software is an ERP system built for businesses in Saudi Arabia. When the invoice, the advance and the stock count are recorded in one system, the customer balance and gross profit come from the same source.
Frequently asked questions
How do I calculate gross profit in a trading account?
Subtract cost of goods sold from net sales. Net sales are sales minus returns, and cost of goods sold needs opening and closing stock from the stock count.
What is the difference between accrual basis and cash basis?
The cash basis follows cash movements; the accrual basis follows the period a revenue or expense belongs to. That is why net profit differs between them whenever there are accruals or prepayments.
When is a supplier account reconciliation complete?
When the "Difference to reconcile" card shows zero, meaning each side has recorded its missing items and corrected any amount differences.
Do the formulas stay in the Excel file?
Yes. The difference, remaining balance, totals and summary are real formulas, and the adjustable rates sit in the settings sheet.