By Anthony MellorNo Comments

BookkeepingBy an accountant – me

  • Why this subject?
    It is the basis for using Excel for bookkeeping in this modern age of bank data download availability.
  • Who this is for?
    Self employed, company owner managers, any small business or indeed anyone who is still punching in numbers “for the accountant”.
  • Where? (Country)
    Anywhere, but written in the context of the UK, where I mention VAT just think “Sales Tax”.
  • What you need to know
    Excel can open csv files directly.

This is about how to give your accountant what he needs without doing any bookkeeping. Bear in mind I am one of those accountants.

Motivation

I was chatting with a business owner the other day. I asked how he does his bookkeeping and he said he uses Sage so as to help his accountant.

It turns out he uses his own sales invoicing and ledger system online, not Sage, he uses his credit card for all expenditure to speak of and his girlfriend types ALL these transactions individually into Sage. He is NOT Vat registered.What a staggering waste of time. Every week or month they do all that work (and more in fact). Indeed Sage can accept imports too I believe.We established he does NOT use the Sage output for managing the business at all. He uses his live online data.

My advice to him

After some further chat he now understands that he can drop using Sage or anything else and:

  • Can provide his online sales system report to his accountant as his sales record, in CSV format or even Excel format
  • Drop the bank account csv into a spreadsheet at year end
  • Drop the credit card csv into a spreadsheet at year end

…and that’s pretty much it. Any competent accountant who properly knows his client can reconcile and report accounts from that data in under an hour, two at most as long as the integrity of the data is OK.

What will be left will be any bank account credits that are not from the sales system to explain and any cheques, not that he uses a cheque book, or other odd items of expenditure that did not go via the credit card.

Presto!

ExcelExcel:

Pivot reports practically instantly from comma separated value files, which can be “file opened” directly or as external data files.

Powerpivot can also be useful here as it can exclude repeated header lines.

Now ALL this is written as if the accountant will do the data imports into Excel, but there is no reason why The Marketing Compass readers cannot do this for themselves, asking questions as needed.

Some UK VAT accounting hints:

If he was Vat registered and/or has payroll he could (where appropriate):

  • sign up for annual accounting (reconcile the vat once per YEAR)
  • sign up for quarterly direct debit payment of vat (depending on cashflow!)
  • sign up for the flat rate scheme (no more scraps of paper taking hours to manage)
  • sign up for annual payroll

And If he has a cheque book:

STOP using it! Use a debit card instead and pay online. If time is an issue, then use online banking to delegate to staff to set up payments and then go in to authorise them all in one short session. Remember cheques are being scrapped anyway (UK Banking system, eventually.).

Modern banking systems pretty much operate themselves these days and can be used to create accounting records, some even output files directly for well known systems, but the one man business only needs a spreadsheet to collate them, anything else is overkill, whatever the marketing adverts say. Why waste your time? It takes us accountants MUCH longer to fix the problems when you try to do your own bookkeeping “for us” without using the information for running the business. Much better to let us collate a few spreadsheet files…. as long as they are complete – and you can learn that very quickly.

Our example happened to use Sage, it doesn’t matter what the package is, it is overkill for a business that does not use the dynamism offered by such systems and it is much more difficult to fix. Sage has its excellent place, I used to run a 3m turnover business with it.

Nothing is perfect for everything

Common sense needs to prevail, even if it isn’t always so common.

Developing individual spreadsheet bookkeeping is very easy to do with a bit of practice – I do it all the time for my own clients for free because it turns them into great clients. Just make sure you use a column of analysis descriptions and not columns across for extending the analysis – a topic for a later article.

I do hope this provides a little balance in these heavily marketed times.

Leave a Reply