<

Banking, Bookkeeping & Excel - made very simple, do your accountant a favour.

Published on
9,165 Points
3,065 Views
1 Endorsement
Last Modified:
Approved
By an accountant

1. Why this subject?

It is the basis for using Excel for bookkeeping in this modern age of bank data download availability.

2. Who this is for?


 Self employed, company owner managers, any small business or indeed anyone who is still punching in numbers "for the accountant".

3. Where? (Country)

Anywhere, but written in the context of the UK, where I mention VAT just think "Sales Tax".

4. 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:
[step="1 " title=""]can provide his online sales system report to his accountant as his sales record, in CSV format or even Excel format.[/step][step="2 " title=""]drop the bank account csv into a spreadsheet at year end.[/step]

3.

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!

Excel:

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 Experts Exchange 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.

Experts Exchange is just the place to ask questions about details all about this article.

ADM
1
Comment
1 Comment
LVL 38

Expert Comment

by:younghv
Anthony - very nicely done and the timing for US readers is perfect. We are 11 days away from "Tax Day", so the awareness of what we might do to improve next year's tax torture is kind of top of mind.

"Yes" vote above and I appreciate your doing this.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Join & Write a Comment

Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month