Free Excel Cash Book


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.


Benefits of an Excel Cash Book

  • Instantly see how much is being spent on each expense type every month (10 options available)
  • Instantly see how much is being earned every month (4 options available)
  • Process bank reconciliations to ensure the cash book balance equals the bank account balance at the end of every month.
  • View an Income Statement that is linked to each month; this provides year to date totals for each income and expense type and the resulting profit or loss total
  • No bookkeeping experience necessary
  • Sample page to show you how it works
  • Free and easy to format to suit your business/personal requirements.

Requirements

  • You need to have Microsoft Excel (2003 upwards) on your computer.
  • A little knowledge of how to use Excel would be beneficial but if you know nothing excel has a really good 'Help' link with heaps of how-to information, and there are plenty of free tutorials on the world wide web.

Two Versions Available

  • The first download contains a smaller spreadsheet for a business with fewer types of transactions. It has 4 income columns, 10 expense columns, and 31 rows.
  • The second download, is for businesses with more transaction types. It has 7 income columns, 22 expense columns and 41 rows.

Small Cash Book



Due to popular demand!
A new, bigger cash book with more columns.


Instructions for Download

Click on graphic image of your choice 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 click on 'save as'. This will let you save the cash book with a different name (like the name of your business) and takes it out of read only mode so you can edit it - in other words use it.

On downloading this cash book Excel may pop up with a yellow bar at the top advising the book is in Protected Mode. You will need to click on 'Enable Editing' to be able to use the book.

Now the fun begins!


Excel Cash Book Details

The first thing to keep in mind is that this cashbook is only for use with one type of bank account. If you have a general bank account, a savings bank account, a petty cash account or credit card you will need to open an excel cashbook for each one.

If you want one Income Statement showing combined totals from all four cash books, you will have to do one up yourself.

Thanks to a site visitor (Keith) you can set-up a page in Excel to easily access all your cashbooks from one place, and this would be a good place to set up a combined Income Statement too. Here are the instructions to set up one access point.

If your business is such that you have lots of different types of bank accounts or more transactions than these Excel spreadsheets can fit you should consider using bookkeeping software such as this free, open source one called Manager

Tabs

Across the bottom of the cash book you will see coloured tabs:-


  • The first one is the 'Sample' page which shows you how information is entered in the cash book.
  • The next one is the Control page for business name and headings.
  • Then comes one tab/page for each month
  • Then there is the Income Statement, also known as Profit and Loss Statement. It is linked to the 12 months so is automatically updated.
  • Finally, there is the reconciliation sheet which can be used to reconcile the bank account to the cash book.

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!



Sample Page

The sample page shows you how to enter information into the cash book and gives you an idea of how it will look.


Control Page

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.

You cannot use this control page to format all the pages in the cash book. If you need to change row heights or column widths, text type or size, you will have to do this on each and every page.


Headings

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.


Total Columns

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.

Opening Balance

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.

Reconciliation

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




Income Statement

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 month?

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:-

  1. Rent received from tenants,
  2. Then deduct commission,
  3. Balance paid to landlord?

Most of the tenants pay their rent by cheque. Commission is included in the rent payment. For example:-

  1. Tenant pays £1,210.00
  2. 5% commission due to me is £60.50
  3. Payable to landlord is £1,149.50

Answer:

You would simply show:-

  1. Rent received as money in
  2. Payment to landlord as money out
  3. The balance left will be the amount of the commission which you would have to keep a record of in a notebook if there is no column for it.

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.


You are here:

Excel Cash Book


Facebook Comments

Have your say about what you just read!
Leave me a comment in the box below.