Cash book template (Excel) and end-of-day check
Keep the till’s cash book in Excel for free: every amount in or out on its own row, and the balance carries over from day to day. Start with 500, take 1,250 in cash sales, pay out 400 and 60, and the till should hold 1,290.
Updated:
| Date | Details | In | Out | Balance |
|---|---|---|---|---|
| 3 Nov 2026 | Carried over from yesterday (example) | 500.00 | 500.00 | |
| 3 Nov 2026 | Cash sales (example) | 1,250.00 | 1,750.00 | |
| 3 Nov 2026 | Paid to supplier (example) | 400.00 | 1,350.00 | |
| 3 Nov 2026 | Tea and cleaning (example) | 60.00 | 1,290.00 |
The Excel file works out the totals when it opens. The PDF is made in your browser; nothing you type is sent.
What is in the template?
- Cash book sheet: date, details, in, out and balance; 300 rows ready, the balance worked out by a formula.
- End of day sheet: the cash you counted in the till, what the book says should be there, and the difference.
- How to use sheet: a short guide.
- Opens in Excel, Google Sheets, LibreOffice and Numbers; the only formulas are SUM, IF and AND.
Carried over from yesterday: in 500 → balance 500 Cash sales: in 1,250 → balance 1,750 Paid to supplier: out 400 → balance 1,350 Tea and cleaning: out 60 → balance 1,290
How do you carry the balance over?
On the first row, write the cash in the till at opening as “Carried over” in the In column. The days that follow go on below it on the same sheet; on each row the balance takes everything paid out so far from everything taken in so far, so one day’s closing balance becomes the next day’s opening. For a new month or year, copy the file and write the closing balance on the first row as the amount carried over.
How do you close the day?
- Count the cash in the till note by note: the cash count calculator gives the total.
- Type the counted cash on the “End of day” sheet.
- The sheet shows what the book says should be in the till and the difference: plus means over, minus means short. Example: the book says 1,290 and you count 1,280, so the difference is −10: the till is 10 short.
- If there is a difference, look for the cause: an expense not written down, a card or mobile payment taken for cash, a sale on credit not written in the credit book, or wrong change.
Where do sales on credit and card payments go?
A cash book holds only the cash that comes in and goes out by hand. A sale on credit does not go into the till; it goes in when the customer pays. Card and mobile payments go to the bank, so they stay out of the cash book. Keep credit in the credit book Excel template or in Baki Fast; Baki Fast’s cash book shows the day’s cash in and out apart from credit.
This template is for keeping track day by day; it does not replace the official books that tax law may require. Ask your accountant which books you must keep.
Questions
What is the format of a cash book?
Columns for the date, the details, money in, money out and the balance: the first row carries over yesterday’s balance, and each row adds what came in and takes off what went out. Textbooks often draw it in two halves, receipts left and payments right, with the same balance.
How do I make a cash book in Excel with formulas?
Make columns for date, details, in, out and balance, then type =SUM($C$2:C2)-SUM($D$2:D2) in the first balance cell and drag it down. The template has this formula ready.
Does it work in Google Sheets?
Yes. Upload the .xlsx file to Google Drive and open it with Google Sheets; the formulas keep working.
What if the till is over or short?
First look for an expense not written down, a card payment and a sale on credit. If the difference stays, write a “cash difference” row to correct the book, and count the till more often the next day.