Self-Employed accounting spreadsheets – The essential ingredients.

Many self-employed individuals will start off their business bookkeeping records on an accounting spreadsheet.

The popularity of spreadsheets such as Microsoft’s Excel means that it is often the first ‘financial tool’ a businessman reaches for. But, is it the right one?

In a previous article  ‘Accounting Software – are bookkeeping spreadsheets the answer for small businesses’  I examined the ‘bare minimum’ bookkeeping requirements for a small business accounting spreadsheet and outlined what  controls needed to be in place to augment these records. 

The article concluded that a properly designed and fully reconciled Cash Book accounting spreadsheet coupled with a Sales Day Book and a Purchase Day Book could form the basis of a workable bookkeeping solution.

For the self-employed businessman this means making sure that your records will HELP rather than HINDER the work of your accountant. Many accountants find that they ‘have to do the work again’ because of the inadequacy or inaccuracy of the bookkeeping data presented to them.

I would strongly advocate showing your accounts spreadsheets to your accountant at an early stage and be prepared to implement any changes he might suggest.

What should you include on your accounting spreadsheets?

There are many examples of good bookkeeping spreadsheets, and all of them will incorporate the following essential data for EACH of your business transactions.

·         The transaction Date

·         The transaction TYPE (Purchase Invoice, Bank Payment, PayPal Receipt etc.)

·         Who is the transaction with (Customer Name, Supplier Name etc.)

·         The transaction Reference (Invoice Number, Cheque Number, etc.)

·         An ANALYSIS of the transaction (Sales, Rent, Wages, Interest Paid etc.)

·         The GROSS transaction value

·         The VAT value (if applicable) of the transaction

·         The NET value (after VAT) of the transaction

·         The STATUS of the transaction (Invoice Paid, Bank transaction matched  etc.)

So, a series of three bookkeeping spreadsheets designed in a monthly columnar format with these column headings is a good starting point:

DATE, TYPE, NAME, REFERENCE, ANALYSIS, GROSS, VAT, NET and STATUS.

Each business transaction should be listed on a new line and should be sorted chronologically.

To help your accountant, I would recommend that you use a standard naming convention for your transactions analysis.  (Sales, Rent, Wages, Interest Paid etc.)

After you have entered your basic information onto the accounting spreadsheets, you should then examine your bank, credit card and other statements to make sure that you have not missed any transactions. If you have, then these need to be recorded on your listings.

You need to make sure that the whole accounting spreadsheet BALANCES. Add up the individual COLUMN totals and then add up the individual ROW totals, they should be the same.

Finally, send your first months accounting worksheets to your accountant for correction and approval.

As a guide you can view several examples of professionally designed accounting spreadsheets on our website.