A cash book template for a small business is a single table with six columns: date, detail, category, money in, money out and a running balance. Each line is one payment or receipt. The balance column adds money in and subtracts money out, so after the month's last line it should equal the closing balance on your bank statement.
What columns does a cash book template need?
A cash book template needs a date, a short detail of who paid or was paid, a category, a money-in column, a money-out column and a running balance. A reference column for the receipt or invoice number is a useful seventh. That is the whole layout; anything more belongs in a separate report.
Here is the template with example rows. Every figure is made up for illustration.
| Date | Detail | Category | Ref | In | Out | Balance |
|---|---|---|---|---|---|---|
| 10-01 | Opening balance | 2,000.00 | ||||
| 10-03 | Client payment, Harbor Cafe | Sales | INV-31 | 850.00 | 2,850.00 | |
| 10-06 | Phone bill | Phone | R-101 | 45.99 | 2,804.01 | |
| 10-14 | Packaging supplies | Supplies | R-102 | 120.00 | 2,684.01 | |
| 10-20 | Client payment, Lane Florist | Sales | INV-32 | 300.00 | 2,984.01 | |
| 10-31 | Monthly account fee | Bank fees | 8.50 | 2,975.51 |
Two columns for money, rather than one signed amount, make the cash book easy to read at a glance and easy to print. The category column is what turns the book into a tax record later. Pick the names from a short, fixed list so that totals by category work; a chart of accounts for a small business suggests a list that stays short.
The Ref column points to the paper. A cash book line shows that money moved. The receipt shows what it bought. Ways to store receipts for free covers keeping those safe without paying for an app.
The cash book running balance formula
The running balance formula takes the balance from the line above, adds this line's money in and subtracts its money out. Write it once in the second row and fill it down. In the template above, with In in column E, Out in column F and Balance in column G, the formula in G3 is below.
=G2+E3-F3
Empty cells count as zero, so a line with only money out still works. Put the opening balance in G2 by hand: copy it from the opening balance printed on the statement, not from memory.
A small refinement keeps the column tidy as you add lines in advance. Wrap the formula so it shows nothing until a date is entered:
=IF(A3="","",G2+E3-F3)
Do not type over the balance column, ever. If a balance looks wrong, the cause is in the In or Out cells, and typing a corrected balance hides the mistake rather than fixing it.
Filling the cash book from bank statement rows
Filling the cash book from bank statement rows saves typing every line. If your bank lets you download transactions as CSV, paste the date, description and amount columns into the template, then move negative amounts into the Out column and positive ones into In. A spreadsheet formula can do that split for you.
If the statement is only a PDF, you have two cheap routes. Excel's Get Data feature can read some PDF tables, and whether Power Query is free with your Excel explains which versions include it. Or a converter can produce the rows directly. CheapBankConvert, not yet open, is being built to turn a PDF statement into CSV or Excel with date, description, amount and balance columns, and its free two-page preview and per-statement prices are on the homepage.
Whichever route you use, keep the bank's own description in the detail column at first. Shorten it later if you like, but the original text is what you search for when a line needs checking.
Why doesn't the cash book balance match the bank statement?
A cash book balance fails to match the bank statement for one of four reasons: a line is missing, a line is entered twice, an amount was typed with digits swapped, or a payment is in the book but has not cleared the bank yet. Each leaves a recognizable gap.
Work out the difference between your balance and the statement's, then read it:
- The gap equals one amount in the statement. That line is missing from the book or entered twice. Look it up on the statement and compare.
- The gap divides evenly by 9. Digits were probably swapped, such as 45.99 typed as 49.59. The difference is 3.60, which is 9 × 0.40.
- The gap matches a check you wrote. The payee has not cashed it yet. It belongs in the book; the bank will catch up.
- The gap is a small fee or interest. These appear only on the statement, so add them to the book when you see them.
Check the opening balance first. A cash book that started from the wrong number will never match, however carefully the lines were entered.
Petty cash and card purchases in a small business cash book
Petty cash and card purchases need their own handling in a small business cash book, because neither moves the bank balance line by line. Petty cash leaves the bank as one withdrawal and is spent in small amounts. A business credit card is paid off in one lump from the bank.
For petty cash, keep a second, smaller cash book with the same columns, starting from the amount withdrawn. Each coffee for a client or parking fee goes on its own line, and the balance should equal the coins and notes in the tin. In the main cash book, the withdrawal is one line with the category "Petty cash".
For a business card, record the individual purchases from the card statement, each under its own category, and record the payment from the bank as a transfer that is not an expense. If you record both the purchases and the payment as spending, your costs double.
When the business takes a lot of cash or runs several cards, the template starts to strain. That is a signal to keep one cash book per account, each matching its own statement, rather than forcing everything into one table.