UK Cash Book Excel - Free Template

1. Open the Cash Book sheet and start with the opening bank balance for the account you are tracking. Confirm that the amount agrees with the bank statement at the start of your chosen period.
2. Enter each transaction using the Date column in DD/MM/YYYY format. Add a concise Description, such as customer payment, van fuel or supplier invoice.
3. Select or enter a consistent Category, add the bank reference, and record the Payment Method. Use the same wording for recurring items so that your later analysis is reliable.
4. Put receipts in Money In (£) and payments in Money Out (£). Do not enter the same transaction in both columns, and leave the unused amount blank or at zero.
5. Choose the relevant VAT Rate (%) from the supplied lookup table where the transaction is within the VAT-registered business records. Check the VAT invoice before treating an amount as recoverable input VAT.
6. Review the Balance (£) and Net Amount (£) columns after each batch of entries. Compare the closing balance with the bank statement and investigate any difference before relying on the dashboard.
7. Use the Summary Dashboard for a high-level review, then follow the Instructions sheet when you need to check the layout or adapt the workbook for your business.

A cash book is most useful when one bank account holds the day-to-day trading activity and you need a simple audit trail rather than a full accounting package. A sole trader may update it every Friday before checking upcoming payments, while a bookkeeper at a small Ltd company may post the entries after the monthly bank statement arrives.

For example, consider a plumbing firm with four employees. In one week it receives £6,000 from completed jobs, pays £1,200 for materials, £240 for fuel and £3,100 towards wages and other regular costs. Entering those items separately makes the movement in the bank account visible instead of leaving the owner to judge the position from a single balance.

Useful at the point of payment

The Cash Book sheet is suited to recording actual bank movements: a customer receipt, a supplier payment, a bank charge or a transfer. The Description and Reference fields are particularly valuable when a payment appears on the statement as an abbreviated name and you need to identify it later.

An office manager at a tradesperson's firm can enter transactions during the week and send the workbook to the external bookkeeper at month-end. A club treasurer can use the same approach for subscriptions, hall hire, equipment purchases and grant receipts, provided the account being tracked is kept separate from personal spending.

When a dashboard helps

Image 1 shows the ledger with the ten fields needed to describe each movement, including Category and Payment Method. Image 2 is the Summary Dashboard, which is useful when a committee or business owner wants the headline figures without scanning every row.

The workbook is a sensible starting point for a small volume of transactions. If an online shop has 300 orders a month, however, importing a bank feed or sales report into accounting software will usually be safer than typing every receipt into a spreadsheet.

A cash book is evidence for your bookkeeping, not automatically a complete tax record. HMRC expects you to keep records of sales, income, business expenses, purchase documents, bank statements and calculations. A company normally keeps accounting records for 6 years from the end of the relevant financial year; a self-employed person generally keeps records for 5 years after the 31 January Self Assessment deadline.

The cash book should therefore be supported by invoices, receipts and bank statements. If a sole trader's 2025/26 online return is filed by 31 January 2027, the underlying records are normally retained until at least 31 January 2032. Use a clear file name and a regular backup rather than treating the workbook as the only evidence.

VAT rates and transaction values

If you are VAT-registered, the workbook's VAT Rate (%) field can help you distinguish standard-rate transactions at 20%, reduced-rate transactions at 5% and zero-rated transactions. The VAT registration threshold is £90,000 of taxable turnover in a rolling 12-month period in 2026, not profit and not simply the balance paid into the bank.

Suppose you record a £1,200 gross purchase at 20% VAT. The net value is £1,000 and the VAT is £200. Keep the supplier's valid VAT invoice before reclaiming the £200; a category or rate entered in Excel does not create evidence that the tax was chargeable.

Cash records and tax returns

For a cash-basis trader, the timing of payment can be central to the profit calculation. A traditional profit and loss account may instead require accruals and prepayments, so do not assume that every cash book total is the taxable profit of a company.

VAT-registered businesses submitting quarterly returns under Making Tax Digital (MTD) need digital records and compatible submission software. This workbook can organise source data, but it is not by itself MTD filing software. Review the 6 April to 5 April tax year separately from a Ltd company's accounting year, which may end on another date.

The most expensive cash book errors are usually small entries repeated every week. I have seen a business record card receipts as bank income but forget the associated card-fee payment, leaving the ledger £35 short after a month. That difference then creates unnecessary time during reconciliation and can conceal a genuine missing payment.

Gross and net amounts confused

A common VAT mistake is entering the net amount in Money In (£) when the bank received the gross amount. A £2,400 customer payment at 20% VAT is £2,000 net and £400 output VAT, but the bank movement is £2,400. Recording £2,000 as the receipt understates the bank balance by £400 and distorts the VAT review.

The opposite error occurs with purchases: someone enters the VAT-exclusive figure in Money Out (£), even though £600 left the bank. If the purchase was £500 plus £100 VAT, the cash book must reflect the £600 payment; the net and VAT analysis can sit alongside it.

Dates, duplicates and transfers

Using the invoice date instead of the bank transaction date can move a receipt into the wrong month. That matters when you are checking cash available for payroll or comparing a quarter's bank movements with a VAT return. A duplicated supplier payment of £850 is not a harmless spreadsheet error: it overstates costs and may remain unnoticed until the supplier statement or bank reconciliation.

Transfers between two business accounts are another trap. Entering the transfer as income in one account and failing to record the matching payment in the other makes the combined position appear £2,000 higher when the transfer was only £2,000 moved internally.

Categories that stop meaning anything

If one person types Fuel, another types Vehicle fuel and a third types Van costs, category totals become unreliable. The result is often a wasted two-hour tidy-up before year-end accounts. Image 1's Category and Reference fields are useful only when you apply a short, agreed list and record the supporting document against the same reference.

The simplest routine is to attach the Cash Book to an event that already happens. Enter transactions every Friday, then complete a fuller check when the monthly bank statement arrives. For a business with 80 entries a month, spending 15 minutes each Friday is far less painful than reconstructing all 80 from memory at year-end.

Use a fixed review sequence

• Download or open the bank statement and work from the oldest unreconciled item.
• Enter the date, description, category and reference before moving to the next line.
• Check that total Money In (£) less total Money Out (£), plus the opening balance, agrees with the closing bank balance.
• Review unusual VAT rates and attach or file the related invoice or receipt.

Keep the entry style consistent. For example, use Customer receipt - ABC Ltd rather than changing descriptions between ABC, ABC payment and bank receipt. The Summary Dashboard is then more useful because its totals are based on comparable entries.

Protect the working file

Keep one master workbook, save a dated backup at month-end and restrict editing of the lookup area to the person responsible for bookkeeping. The Instructions sheet, shown in image 3, is a useful handover point when a volunteer treasurer or new office administrator takes over.

Do not add a second opening balance halfway through a year. If you need to start a new period, copy the file, retain the previous closing balance as the new opening figure and label the copy clearly. That preserves the trail between periods.

Know when to move on

A spreadsheet is appropriate for a modest ledger with one or a few bank accounts. Move to bookkeeping software when you have several hundred monthly transactions, recurring invoices, stock, payroll, multiple VAT schemes or more than one person entering data. At 300 online orders a month, automated bank feeds and sales integration usually save more time and reduce duplicate-entry risk than extending a manual cash book.