Our Excel cash book example is based on a small computer company selling hardware and consultancy. If you have not already taken a look at our free template it can be found at Excel cash book template.
Our example is fully working so you can download it and enter your own figures to get used to using the spreadsheet before implementing it for your own business.
Instruction worksheet for cash book
The first task is to enter the name on the instruction worksheet, this will then appear on the top of all the worksheets for each month. For our example enter ‘ABC Computers’ in the green highlighted area.
Our example is self-employed and works in line with the tax year dates from HMRC 6th April to 5th April. At the beginning of the year, there is £1020 in the bank. This figure is posted on the instruction page in green, the month can be found below using a drop-down list. Once this information is correct it will post it as the opening balance in the correct month.
Posting Account names
Each small company is different and may require different account names for income and expenditure. The next task is to decide on the account names. Our example the uses the following codes:
- Income accounts
- Sales Hardware
- Sales Consultancy
- Interest Income
- Expenditure accounts
- Cost of Sales
- Bank Charges
These are entered in the account codes worksheet and will appear each month for the columns and totals page.
At this stage, everything is set up and transactions can be taken from your bank statements and entered into the cash book.
The statement we have is for April so start by entering the date of the transaction in format dd/mm/yy, supplier or customer name, details – this may be a reference number or type of transaction and total amount. The next column has a drop-down list of the account names that was created on the initial setup, choose the right one and it will automatically post it to the correct column and to the totals page.
As each transaction is posted it is worth checking that the bank balance is correct, if there is a difference check that everything is posted correctly. Our bank balance at the end of April is £964.71 as per the bank statement.
At the end of the month when everything is entered the profit or loss can be found on the totals page, our example shows a loss of £55.29
To start the next month, the bank balance is already posted so all you need to do is post the transactions. Once the year is complete you will need to start afresh spreadsheet using the end balance as the opening balance.
It is worth spending some time on the example entering figures to see what your outcome is before starting on your own version. Try changing the account codes, start date, opening figure and check the totals. This will give you more confidence in using your own version.
Return from our example page to Free Excel cash book template page.