Painful spreadsheets

It is not uncommon for a spreadsheet to be virtually useless to your accountant or tax inspector where the information is inaccurate or incomplete, or where it is simply too complex to be capable of producing simple, readable paper printouts.

Putting your “books” onto a computer spreadsheet will not make up for any shortfalls in your understanding of what “book-keeping” is about, nor any shortcomings in your administration or organisational procedures. Book-keeping isn’t just a simple list of in’s and out’s which is typically all that people think they have to do, but even then, mistakes are often made.

We often find fundamental mistakes in spreadsheets given to us by clients who are often convinced that they’ve done everything properly. This adds to the time taken and increases their fees!

The following points should help you to avoid some of the common pitfalls.

• Other users of your spreadsheet, eg. your accountant and the tax man may want a paper printout of your accounting data – keep this in mind when creating your spreadsheet model.

• Make sure that you have an adequate “back up” procedure in case of computer theft or failure. You don’t want to have to start again to reconstruct many months or years of transactions!

• Do not use different colours, especially for negative numbers – format the cells to show a “-” (minus sign) or to show negative figures in brackets ( ) instead. Other users of your sheet may be colour-blind or may not have a colour screen or printer.

• Take particular care with calculations and formulae if a new row or column is added to ensure that all totals and links are arithmetically correct.

• Format numeric cells to be two decimal places so that the columns of figures are properly aligned.

• Don’t use excessive formatting – the contents of the spreadsheet are far more important than “how it looks”. Try to keep to simple black print on white background with a clear and simple font and without excessive use of “bold” or “italics”.

• Don’t enter “£” signs or any other text in a numeric cell as the numerical value may not be included in totals.

• Don’t make the spreadsheet too large. Remember that it may have to be printed out so try to format it to fit onto one or several A4 sheets. If the spreadsheet becomes too large, split it into several smaller ones, i.e. one per month or separate pages for ins and outs.

• Do not use “cell content narrative” as these won’t show when the sheet is printed – use a “narrative” column instead.

• Don’t assume that the columns are correctly added – it is worth checking each addition after changes have been made.

• Build in “controls” to check your figures against your bank or credit card statements. Use the totals from the spreadsheet(s) to prove the balances against the bank statements. The net difference between the income and expenditure totals should agree with the increase or decrease in the bank balance as per the bank statements – if they don’t, your spreadsheets are not correct and need attention.

• “Garbage in – Garbage Out” is as true for spreadsheets as any other computer software!

A better alternative to computerised spreadsheets is to use an “off the shelf” accounting package.

There are plenty of cheap (or even free) book-keeping software solutions that are easy to use, such as VT Transaction, VT Cash Book (Free), Quickbooks, Freeagent and Xero.

Contact us for more information about any of my services and how we can help you grow your business and minimise your tax.