Excel Cash Book Example
Our easy to use Excel cash book example uses a small computer company selling computer hardware and consultancy. If you have not already taken a look at our free template, it’s available at Excel cash book template.
Our example is fully working, so you can download it and enter some sample figures to get used to using the spreadsheet before implementing it for your own business.
Many small businesses, charities and individuals use our Excel cash book.
Instruction worksheet for cash book
Once you have downloaded the spreadsheet, open it up in Excel or an alternative like Google Sheets.
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 correct month.
I get asked regularly that the financial year starts in a different month, can I still use it?
The answer is yes; once the whole year is entered, the totals page will show the entire year figures. The order of months does not matter. The figures recorded in the self-assessment return are the figures for the year.
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
- Advertising
- Cost of Sales
- Bank Charges
- Insurance
- Post
- Sundries
- Telephone
- Travel
These are entered in the account codes worksheet and 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 the format dd/mm/yy, supplier or customer name, and 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 month-end, when everything is entered the profit or loss can be found on the totals page. Our example shows a loss of £55.29
The bank balance is posted when you start the next month, 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 Example Format
The cash book format is simple; each month has a separate sheet. The totals from each month appear on 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 your outcome before starting on your version. Try changing the account codes, start date, opening figures and checking the totals. It will give you more confidence in using your version.
Alternatives to Using a Cash Book
A cash book can be an excellent solution for small businesses, as it provides easy-to-understand and straightforward financial information. However, as the business grows, it will need more comprehensive financial data to make sound financial decisions. The best way to do this is by using accounting software.
Accounting software automatically tracks sales, accounts receivable, inventory, payroll, accounts payable and more. It can also integrate with your bank account to download transactions directly into the system. There are many different types of accounting software available for small businesses — each providing a different range of tools and options designed to match the business owner’s specific needs.
Licence Agreement
By downloading the Excel Cash Book template example, you agree to our licence agreement, allowing you to use the templates for your own personal or business use only. You may not share, distribute, or resell the templates to anyone else in any way.
Return from Excel cash book example page to Free Excel cash book template page.
