A Simple Bookkeeping Spreadsheet

What’s in This Article

This article gives an example of a simple spreadsheet design that can be used by small business owners to record their business income and expenses in an organised and efficient manner.

It includes tips regarding what should be analysed out to help with year-end returns.  

Who Should Read This

Small business owners, the self-employed, and those thinking of starting a new business.

recent articles

The Flat Rate
Scheme Falls Flat

A change being made to the VAT Flat Rate Scheme on 1 April 2017 will force many businesses to leave the scheme.

READ MORE >>

Charging VAT
For The First Time

What are the key things to be aware of when you begin charging your customers VAT for the first time?

READ MORE >>

SEE ALL ARTICLES >>

Many small business owners have a tight enough grip on their business without needing to implement a complex and expensive bookkeeping system. They know which customers have yet to pay their invoices, which expenses are currently owing and how much cash they have in the bank to meet their financial obligations.

But having some kind of organised bookkeeping system, however basic, will always save you time if you prepare you own VAT or tax returns, or will save you money if you ask an accountant to do so.

In addition, the discipline of recording transactions in one form or another as you go along, rather than waiting for tax return time, will greatly reduce the chance that transactions are missing from your records, or recorded incorrectly. This can lead to incorrect tax returns, possibly resulting in fines when tax has been underpaid.

The Shoe Box System

Whatever bookkeeping system you put in place will hopefully be better than the “shoe box system”. This still popular method involves placing all your receipts and invoices in a shoe box and hoping they will go away. Clearly this is not to be recommended.

If you must stick with the shoe box system, we would at least recommend using a biscuit tin instead; at least this way you would need to eat all of the biscuits first for “business reasons”.

The Cash Based System

If your main requirement for financial data is the completion of your year-end tax return, or you have relatively simple VAT reporting requirements, a cash based bookkeeping system may be perfectly adequate for your businesses. Sales revenue can be recorded when it is actually received in the bank account, and business expenditure is recorded when paid.

Using spreadsheets as the basis for this method is by far the best approach. A spreadsheet can be backed up, can help eliminate recording errors and can form the basis for all other reporting requirements such as tax returns. 

There are many free spreadsheet applications. If you are unable, or maybe unwilling, to use the Windows Office Starter Excel spreadsheet, a free alternative is available for download from OpenOffice here, but many others are available.  

An Example of a Cash Based Spreadsheet

The simplest spreadsheet based bookkeeping system should detail all your cash transactions in an organised manner, whilst an analysis section can be included to identify in more detail what each transaction relates to. An example of one such spreadsheet is shown below.

Article 3 - Bookkeeping Spreadsheet Example

Click on the above to enlarge – image will open in a new tab

Section A. This side of the spreadsheet summarises each cash transaction, showing the amount, the date, the customer or supplier’s name and a description of the item. Where more than one bank account is used by the business, either a separate column can be used for each bank account or, alternatively, a separate spreadsheet.

There is also a column for a reference number. In the case of sales income, this can be your own invoice number, whilst for expenses a sequential number, beginning “E” for instance, could be used and then written on the related physical invoice.

At the very least, you should be looking to update this section of the spreadsheet on a regular basis.

Section B. On the right hand side, a more detailed analysis and breakdown of each item of income or expenditure is undertaken. This is particularly useful for expenditure, which needs to be carefully analysed for tax return purposes.  Completing Section B enables you to analyse a single transaction across multiple expense categories where that single payment or invoice covers multiple things.

As an example of this, item “E94” is a single payment that comprises the purchase of a printer and some printer paper. Whilst the entire expense is related to printing, the tax treatment of the printer purchase differs from that of the paper purchase. It therefore makes sense to analyse out the total cost across more than one category, in this case “New Printer” and “Postage and Stationary”. This will aid the year-end tax return completion and the preparation of any year-end accounts if required.

There are also check totals for each section which should agree with other; if not, something has been input incorrectly somewhere on the spreadsheet.

How Much Analysis to Do?

When using something like Section B to break down income and expenditure into distinct types or categories, which categories you choose to use is up to you. For example, “Postage and Stationary” is usually fine as a column heading, but you may choose instead to break down such expenditure into separate “Postage” and “Stationary” columns.

Whilst there is no right or wrong answer, there are certain things that should always be analysed separately:

Income V Expense. You should always ensure your trading income is separately analysed. Turnover represents a key figure for tax and VAT purposes and you should ensure it is recorded correctly.

Capital Expenditure. Where business assets are purchased that you expect to use for a period of more than one year, these should be analysed out separately. As with the printer purchase example above, the tax treatment of such capital expenditure may be subject to specific rules and your spreadsheet should therefore include separate columns to identify such expenditure.

VAT. In the example above, VAT on income and expenditure is shown separately; if your business is not registered for VAT, this is not required.

The more detailed breakdown you can make of your expenditure the less likelihood of errors being made on tax returns. HMRC have set out the expenditure categories they require some businesses to use on their tax returns, and these can be a good starting point to use when deciding which categories to break your expenses down into.

These categories are:

  • Accountancy, legal and other professional fees.
  • Advertising and business entertainment costs.
  • Bank, credit card and other financial charges.
  • Car, van and travel expenses.
  • Communications, stationery and other office costs (mobile, internet, email costs).
  • Cost of goods that you are going to sell or use in providing a service.
  • Insurance.
  • Interest on bank and other business loans.
  • Rent, rates, power and insurance costs.
  • Repairs and renewals for property and equipment.
  • Wages, salaries and other staff costs.
  • Other business expenses.

You do not need to stick with only these categories and, in practice, breaking expenditure down into further sub-categories is often a good way to go.

How Often Should I Update?

You should update this spreadsheet on a regular basis; depending upon the volume of your transactions this may be daily, weekly, twice monthly or on a monthly basis only. Avoid the temptation to revert back to the shoe box system; recording transactions on the spreadsheet in a timely manner always reduces the chance of errors.

But You Really Don’t Want to Be a Bookkeeper?

If the prospect of maintaining even a spreadsheet based system fills you with dread (or boredom), it may make more sense to engage the services of an accountant who can visit you a few times a year to keep your books and records up to date. Again, leaving everything to after your financial year end should be avoided.

When Things Get More Complicated

Where business volumes increase, a cash based bookkeeping system may no longer be applicable for your business.

Knowing which sales invoices are outstanding, when and how much must be paid to suppliers and to enable quarterly VAT returns to be completed may mean your bookkeeping system needs to record sales, purchases and other expenditure as they occur rather than when the related cash movements take place.

In these cases, your bookkeeping system may need to be accruals based rather than cash based; sales revenue would be recorded when sales invoices are issued to customers rather than when payment is received; purchase invoices are recorded when you receive them rather than when you pay them.

Unfortunately, using spreadsheets for an accruals based system gets complicated and messy; alternatives are therefore needed.

Purchase bookkeeping software. Bookkeeping software can be used to record financial transactions as they occur (such as the issuing of a sales invoice, for example) and matched with the related cash movement. Software may also automate the production of sales invoices, control stock levels, create tax and VAT returns and have a variety of reports available that allow management to monitor their business. In most cases there is a charge to buy or use such software, and there can often be a steep learning curve when first adopting a software based system.

Hand it all over to an accountant. Well we would say that wouldn’t we, but asking an accountant to perform all your bookkeeping tasks on a regular basis has many advantages, not least the related handling of VAT and tax returns; where an accountant has compiled the data they can also prepare these returns.

The “Something in Between” System

It isn’t always necessary for smaller businesses to hand all their bookkeeping functions to an accountant; it may be that the best bookkeeping solution lies somewhere in between.

You could maintain a basic spreadsheet system yourself and then hand this data over to an accountant at regular intervals. This will enable the accountant to check your data and prepare your VAT and tax returns whilst keeping your costs down.

However straightforward your business is, always resist the temptation to use the shoe box system of bookkeeping. Recording your transactions as you go along, or engaging an accountant to do it for you, will always save you money in the long term and reduce the potential for costly errors being made on your tax returns. 

What’s in This Article

This article gives an example of a simple spreadsheet design that can be used by small business owners to record their business income and expenses in an organised and efficient manner.

It includes tips regarding what should be analysed out to help with year-end returns.  

Who Should Read This

Small business owners, the self-employed, and those thinking of starting a new business.

Many small business owners have a tight enough grip on their business without needing to implement a complex and expensive bookkeeping system. They know which customers have yet to pay their invoices, which expenses are currently owing and how much cash they have in the bank to meet their financial obligations.

But having some kind of organised bookkeeping system, however basic, will always save you time if you prepare you own VAT or tax returns, or will save you money if you ask an accountant to do so.

In addition, the discipline of recording transactions in one form or another as you go along, rather than waiting for tax return time, will greatly reduce the chance that transactions are missing from your records, or recorded incorrectly. This can lead to incorrect tax returns, possibly resulting in fines when tax has been underpaid.

The Shoe Box System

Whatever bookkeeping system you put in place will hopefully be better than the “shoe box system”. This still popular method involves placing all your receipts and invoices in a shoe box and hoping they will go away. Clearly this is not to be recommended.

If you must stick with the shoe box system, we would at least recommend using a biscuit tin instead; at least this way you would need to eat all of the biscuits first for “business reasons”.

The Cash Based System

If your main requirement for financial data is the completion of your year-end tax return, or you have relatively simple VAT reporting requirements, a cash based bookkeeping system may be perfectly adequate for your businesses. Sales revenue can be recorded when it is actually received in the bank account, and business expenditure is recorded when paid.

Using spreadsheets as the basis for this method is by far the best approach. A spreadsheet can be backed up, can help eliminate recording errors and can form the basis for all other reporting requirements such as tax returns. 

There are many free spreadsheet applications. If you are unable, or maybe unwilling, to use the Windows Office Starter Excel spreadsheet, a free alternative is available for download from OpenOffice here, but many others are available.  

An Example of a Cash Based Spreadsheet

The simplest spreadsheet based bookkeeping system should detail all your cash transactions in an organised manner, whilst an analysis section can be included to identify in more detail what each transaction relates to. An example of one such spreadsheet is shown below.

Article 3 - Bookkeeping Spreadsheet Example

Click on the above to enlarge – image will open in a new tab

Section A. This side of the spreadsheet summarises each cash transaction, showing the amount, the date, the customer or supplier’s name and a description of the item. Where more than one bank account is used by the business, either a separate column can be used for each bank account or, alternatively, a separate spreadsheet.

There is also a column for a reference number. In the case of sales income, this can be your own invoice number, whilst for expenses a sequential number, beginning “E” for instance, could be used and then written on the related physical invoice.

At the very least, you should be looking to update this section of the spreadsheet on a regular basis.

Section B. On the right hand side, a more detailed analysis and breakdown of each item of income or expenditure is undertaken. This is particularly useful for expenditure, which needs to be carefully analysed for tax return purposes.  Completing Section B enables you to analyse a single transaction across multiple expense categories where that single payment or invoice covers multiple things.

As an example of this, item “E94” is a single payment that comprises the purchase of a printer and some printer paper. Whilst the entire expense is related to printing, the tax treatment of the printer purchase differs from that of the paper purchase. It therefore makes sense to analyse out the total cost across more than one category, in this case “New Printer” and “Postage and Stationary”. This will aid the year-end tax return completion and the preparation of any year-end accounts if required.

There are also check totals for each section which should agree with other; if not, something has been input incorrectly somewhere on the spreadsheet.

How Much Analysis to Do?

When using something like Section B to break down income and expenditure into distinct types or categories, which categories you choose to use is up to you. For example, “Postage and Stationary” is usually fine as a column heading, but you may choose instead to break down such expenditure into separate “Postage” and “Stationary” columns.

Whilst there is no right or wrong answer, there are certain things that should always be analysed separately:

Income V Expense. You should always ensure your trading income is separately analysed. Turnover represents a key figure for tax and VAT purposes and you should ensure it is recorded correctly.

Capital Expenditure. Where business assets are purchased that you expect to use for a period of more than one year, these should be analysed out separately. As with the printer purchase example above, the tax treatment of such capital expenditure may be subject to specific rules and your spreadsheet should therefore include separate columns to identify such expenditure.

VAT. In the example above, VAT on income and expenditure is shown separately; if your business is not registered for VAT, this is not required.

The more detailed breakdown you can make of your expenditure the less likelihood of errors being made on tax returns. HMRC have set out the expenditure categories they require some businesses to use on their tax returns, and these can be a good starting point to use when deciding which categories to break your expenses down into.

These categories are:

  • Accountancy, legal and other professional fees.
  • Advertising and business entertainment costs.
  • Bank, credit card and other financial charges.
  • Car, van and travel expenses.
  • Communications, stationery and other office costs (mobile, internet, email costs).
  • Cost of goods that you are going to sell or use in providing a service.
  • Insurance.
  • Interest on bank and other business loans.
  • Rent, rates, power and insurance costs.
  • Repairs and renewals for property and equipment.
  • Wages, salaries and other staff costs.
  • Other business expenses.

You do not need to stick with only these categories and, in practice, breaking expenditure down into further sub-categories is often a good way to go.

How Often Should I Update?

You should update this spreadsheet on a regular basis; depending upon the volume of your transactions this may be daily, weekly, twice monthly or on a monthly basis only. Avoid the temptation to revert back to the shoe box system; recording transactions on the spreadsheet in a timely manner always reduces the chance of errors.

But You Really Don’t Want to Be a Bookkeeper?

If the prospect of maintaining even a spreadsheet based system fills you with dread (or boredom), it may make more sense to engage the services of an accountant who can visit you a few times a year to keep your books and records up to date. Again, leaving everything to after your financial year end should be avoided.

When Things Get More Complicated

Where business volumes increase, a cash based bookkeeping system may no longer be applicable for your business.

Knowing which sales invoices are outstanding, when and how much must be paid to suppliers and to enable quarterly VAT returns to be completed may mean your bookkeeping system needs to record sales, purchases and other expenditure as they occur rather than when the related cash movements take place.

In these cases, your bookkeeping system may need to be accruals based rather than cash based; sales revenue would be recorded when sales invoices are issued to customers rather than when payment is received; purchase invoices are recorded when you receive them rather than when you pay them.

Unfortunately, using spreadsheets for an accruals based system gets complicated and messy; alternatives are therefore needed.

Purchase bookkeeping software. Bookkeeping software can be used to record financial transactions as they occur (such as the issuing of a sales invoice, for example) and matched with the related cash movement. Software may also automate the production of sales invoices, control stock levels, create tax and VAT returns and have a variety of reports available that allow management to monitor their business. In most cases there is a charge to buy or use such software, and there can often be a steep learning curve when first adopting a software based system.

Hand it all over to an accountant. Well we would say that wouldn’t we, but asking an accountant to perform all your bookkeeping tasks on a regular basis has many advantages, not least the related handling of VAT and tax returns; where an accountant has compiled the data they can also prepare these returns.

The “Something in Between” System

It isn’t always necessary for smaller businesses to hand all their bookkeeping functions to an accountant; it may be that the best bookkeeping solution lies somewhere in between.

You could maintain a basic spreadsheet system yourself and then hand this data over to an accountant at regular intervals. This will enable the accountant to check your data and prepare your VAT and tax returns whilst keeping your costs down.

However straightforward your business is, always resist the temptation to use the shoe box system of bookkeeping. Recording your transactions as you go along, or engaging an accountant to do it for you, will always save you money in the long term and reduce the potential for costly errors being made on your tax returns. 

recent articles

The Flat Rate Scheme Falls Flat

A change being made to the VAT Flat Rate Scheme on 1 April 2017 will force many businesses to leave the scheme.

READ MORE >>

Charging VAT For The First Time

What are the key things to be aware of when you begin charging your customers VAT for the first time?

READ MORE >>

SEE ALL ARTICLES >>