<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

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

Published on
9,246 Points
3,146 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
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

C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month