This excel cash book is suitable for any small business that wants an easy bookkeeping method. It is totally free and fully customizable.
The cash book can also be used by individuals or families who want to keep a detailed record of what type of expenses their money goes on.
This can be useful for developing a budget.
Instructions for Download
Click on graphic image above. This opens a zip version of the excel cash book.
Download to your computer.
Unzip the download.
Save it to your documents or desktop.
Open and save the cash book with a different name (like the name of your business) so that the original remains as is and can be used again.
Now the fun begins!
Across the bottom of the cash book you will see coloured tabs:-
The tabs can be renamed to suit your requirements.
You will know what page you are on when the tab is white. In the above example the Jan tab is white... did you notice!
The sample page shows you how to enter information into the cash book and gives you an idea of how it will look.
The control page is where you can enter in the business name and your income and expense header names. All other monthly tabs are connected to this control page and will automatically pick up the header names you enter here.
There is also a section on this page for entering income and expense totals that you may already have for the financial year so far. Enter the totals here and they automatically carry through to the Income statement.
In the Jan tab you should change the headings in the green cells to suit your business requirements. The headings on every monthly tab are linked to January so you will not have to type them in again and again.
There are three income, two cost of goods sold, and six expense columns.
There are also special columns, one for Capital (funds introduced by the owner), Assets and Drawings. None of these are linked to the income statement because they only go on a balance sheet report (not included with this cash book).
The cost of goods sold columns link to the COGS section of the income statement.
You can easily add in more columns but then you will have to manually link them to the income statement. If you are experienced with Excel, you should be able to do this easily, but it is not recommended if you have no experience.
The grey total columns have formulas in them so they update automatically. Beware! If you manually type anything into the total or bank balance columns you will overtype the formulas and the totals will be messed up.
The only exception is the opening balance as detailed below.
In the January tab, in the yellow cell, you will need to enter the opening balance for the first day of the month which you can get off your bank statement. In this example below, it is 400.00.
If you are starting your cash book in any other
month and not January, then simply enter the opening balance into the
yellow cell in the month you are starting.
If your financial year starts in April, enter the opening cash book balance into the yellow cell on the April tab. When you get to January you may need to manually enter December's closing balance into January's opening balance.
When reconciling the cash book to the bank statement you can select different letters from the drop-down list. R for reconciled; C for checked; X for not reconciled; a dash for whatever reason you may want; you chose which one you want to use.
Excel Cash Book In the Red
When there is not enough funds in the account, in other words, when it goes into overdraft, the cash book shows the number in red as in the example here where it is -$30.00
As mentioned above, the Income Statement is linked to the other pages. The 'Headings' are linked to the Control tab; and the 'Amounts' are linked to all tabs so you get the total for all months combined. This is the same throughout the income statement for each section :- Income, COGS, Expenses.
This excel cash book is a great way to keep simple bookkeeping records.
Excel Cash Book Question
Jarod asks: Hi, I downloaded the excel cash book
for use of bookkeeping. May I know where to input the initial capital
because the opening balance of every month is linked to the previous
Answer: The yellow opening balance cell is the place to enter your initial amount.
It does not matter that it is linked to the previous month.
When you type your initial balance into it, the link to the previous month will be removed; you won't need it anyway.
So, say you start in October 2012 and work through the whole year until you come to the end of September 2013 you need to archive this one, download a new, blank one, enter September's closing balance into the October opening balance and start the cycle again.
Rent and Commission Question
Nadeem asks: How do you put in a two column cash book, the following:-
Most of the tenants pay their rent by cheque. Commission is included in the rent payment. For example:-
You would simply show:-
See example below (click on image to enlarge).
At the end of the month you can add up the rent and commission columns.
This is helpful if you receive rent from several different tenants because it will enable you to quickly track how much commission you are actually earning.