Sole Trader Accounts Excel - Free Template

1. Start on the Instructions sheet and review the category lookup table. Use the listed categories so the workbook can return the related VAT rate and allowable-status result.
2. Open Transactions, then enter one business item per row from row 3 onwards. Type the transaction date, reference, customer or supplier, description, transaction type, category, payment method and net amount.
3. Select Income or Expense for each item. Enter Income for money received from trading and Expense for business costs so Cash Movement is calculated with the correct sign.
4. Enter a clear reference such as INV-001 or a bank-reference code. Mark Receipt / Invoice Held? as Yes or No and use Notes to explain deposits, split payments or other useful context.
5. Check the calculated Tax Year, VAT Amount, Gross Amount, Allowable for Tax?, Cash Movement and Running Cash Position columns. Investigate any row where the allowable result displays Check.
6. Review the Dashboard after entering a batch of records. Compare total income, allowable expenses, estimated trading profit, receipt count and category analysis with your source documents.

A sole trader often needs a practical record before the year-end, not a complex accounting system. This workbook suits a freelance designer, tradesperson, consultant or online seller who receives payments directly and pays recurring business costs from a bank account or card.

The Transactions sheet is especially useful when your records arrive from several places: invoices, bank transfers, direct debits and card receipts. Instead of trying to reconstruct a quarter from a bank statement, you capture the commercial reason for each movement as it happens.

For project-based work

A web designer might record a £1,250 net website deposit as Income with reference INV-001, then add the customer name and a note that the balance is due on completion. The supplied sample row shows this exact type of transaction, with a bank transfer and a receipt or invoice held status.

If the same project involves £45 net business broadband by direct debit, that is entered separately as an Expense under Telephone & Internet. Two rows preserve the distinction between money earned from a customer and a supplier cost, rather than leaving one unexplained difference in a bank balance.

For regular operating costs

A self-employed photographer may have software subscriptions, travel and insurance across a month. The Dashboard groups expenses into six listed categories, including Motor & Travel and Software & Subscriptions, so you can see both the total cost and the number of entries behind it.

For example, four £30 software entries create £120 of net expenditure and a transaction count of 4 in that category analysis. That is more useful than a single monthly total when you need to question whether duplicate subscriptions have been recorded.

For keeping evidence with the entry

The Receipt / Invoice Held? field makes this spreadsheet useful at the point a transaction is recorded, rather than only when figures are needed later. A sole trader can mark a £275 supplier payment No while the invoice is still missing, then use the Dashboard count to chase it.

Image 1 shows the Transactions sheet and image 2 shows the Dashboard. Use image 3, the Instructions sheet, when you need to check the available categories and the notes attached to them.

The key technical choice in this workbook is to enter the net amount once and let the adjacent columns calculate the VAT amount and gross amount. This is safer than typing three related figures manually, because one input change updates the dependent values together.

For a net entry of £1,250 with a category lookup rate of 0.20, the VAT Amount formula calculates £250 and Gross Amount becomes £1,500. The rate is returned by VLOOKUP from Instructions, not typed into each transaction row.

Category-driven checks

The category field controls two outputs: VAT Rate and Allowable for Tax?. The lookup table includes categories such as Sales Income, Telephone & Internet, Motor & Travel, Office Costs, Rent & Workspace and Insurance.

When a selected category is not found in the lookup range, the allowable-status formula displays Check. Treat that as a data-quality warning: do not override a calculated column or assume the row has been classified correctly.

Signed cash movement

Cash Movement takes the Gross Amount as positive for Income and negative for Expense. The Running Cash Position starts with the first row's cash movement, then each following row adds its own signed value to the preceding position.

Using the supplied sample values, £1,500 gross income followed by a £54 gross expense produces a running position of £1,446. This calculation only works if Transaction Type is entered accurately; an expense marked as Income would increase cash rather than reduce it.

Dashboard calculations

The Dashboard totals net Income with SUMIF and totals allowable net Expenses with SUMIFS. Estimated trading profit is the difference between those two outputs, so it reflects the rows currently recorded rather than a separate manually maintained profit figure.

It also calculates output VAT, input VAT on expenses, estimated VAT payable, average income transaction and missing receipts. VAT Threshold Progress divides VATable Turnover by an embedded £90,000 benchmark in the formula; use it as a workbook monitoring indicator and confirm any registration position independently.

For end-of-year reporting, those VAT and profit totals also sit neatly alongside a charity accounts workbook when the same records need to support formal accounts.

The most costly problem is not usually a missing formula; it is a transaction that cannot be identified or supported later. A £600 payment with no reference, supplier name or invoice status is much harder to review than the same £600 recorded with a bank reference, description and a clear No against Receipt / Invoice Held?.

This template has distinct fields for Customer / Supplier, Description, Reference and Notes because each answers a different question. Do not replace them with vague text such as bank payment when you can state who was paid and why.

Duplicated or incomplete entries

Entering a supplier direct debit twice creates two expense rows and doubles its impact on both cash movement and the expense analysis. For example, two £45 net broadband records produce £90 of net Telephone & Internet cost before any VAT calculation, even though only one payment occurred.

Conversely, recording only a gross bank amount in Net Amount overstates the calculated Gross Amount again when the category returns a VAT rate. The workbook assumes Net Amount is exclusive of the calculated VAT figure, so use the correct source figure consistently.

Wrong transaction type

Transaction Type drives the sign in Cash Movement and determines whether a row contributes to income or expense Dashboard formulas. A £300 expense incorrectly marked Income adds £300 to total net income and makes the running cash position £300 higher before gross calculations are considered.

That error also keeps the row out of Total Allowable Expenses because the Dashboard formula filters for Expense and Yes. Correct the type in the entry row; do not try to repair the Dashboard result manually.

Category drift and unsupported claims

Free-text categories would weaken the lookup, which is why the category field should follow the supplied list. A slight variation such as Software subscription instead of Software & Subscriptions may fail to match and return Check rather than a reliable allowable-status result.

Do not mark every cost as business-related simply to improve the estimated trading profit figure. The Instructions sheet flags that mixed-use costs require attention, and the workbook is an organising aid rather than a completed Self Assessment return or professional advice.

A useful accounts spreadsheet becomes routine when it is tied to a real event, such as checking the business bank account every Friday. Set aside 15 minutes to enter that week's payments and receipts while references, suppliers and documents are still easy to recognise.

If you make 6 transactions a week, a 15-minute weekly check creates roughly 24 reviewed entries in a four-week month. Leaving them for three months turns the same task into around 72 rows, with far more forgotten descriptions and missing invoices.

Use a short closing checklist

• Enter every bank transfer, direct debit, card payment and customer receipt since the previous review.
• Attach or file the matching invoice or receipt, then update Receipt / Invoice Held? to Yes.
• Review any Check result in Allowable for Tax? and correct the category rather than editing a formula.
• Open the Dashboard and investigate any unexpected change in income, expenses or missing receipts.

Keep the transaction date in DD/MM/YYYY format and use a stable reference pattern, such as INV-001 for sales invoices and a supplier or bank code for costs. Consistent references make it quicker to trace a £45 direct debit from the Dashboard back to its source row.

Review at the month end

At month end, compare the running cash position with the sequence of entries you have recorded, then look at the expense category counts. Four Software & Subscriptions entries may be normal; eight may justify checking for duplicates or separate products.

The Dashboard uses fixed ranges from Transactions rows 3 to 12 in its formulas. If your records exceed that supplied range, extend the formula ranges carefully and consistently before relying on the summary; do not assume additional rows are automatically included.

When to move beyond the file

Move to a dedicated bookkeeping system when transaction volume, multiple users, bank feeds, invoicing or more detailed reporting makes manual entry unreliable. Until then, this workbook is most effective when you update it little and often, preserve evidence with each line, and use the Dashboard as a review point rather than a replacement for source records.

That review point is also where a bank reconciliation template becomes the next practical check, because the workbook should still match the bank balance before you rely on the Dashboard as your summary.