Our Excel cash book example uses a small computer company selling hardware and consultancy if you have not already taken a look at our free template, its available at Excel cash book template.
Our example is fully working so you can download it and enter your 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 sheets 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, and the month using a drop-down list. Once this information is correct, it will post it as the opening balance in the right month.
Excel Cash Book Example – 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 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 were created on the initial setup, choose the right one, and it will automatically post it to the correct column and the totals page.
As you enter each transaction, 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
When you start the next month, the bank balance is posted, so all you need to do is post the transactions. Once the year is complete, you will need to start with a blank spreadsheet using the end balance as the opening balance.
Cash Book Format
The cash book format is simple; each month has a separate sheet. The totals from each month appear in the totals page. It enables you to view month by month figures.
It is worth spending some time on the example, entering figures to see what your outcome is before starting on your version. Try changing the account codes, start date, opening figure and check the totals. It will give you more confidence in using your version.
Return from Excel cash book example page to Free Excel cash book template page.