"Bank reconciliation? What's a bank reconciliation?" By David Carter

David

I'd be interested to know on which software package you were asked to advise as the one we use is Dutch. I could tell you some tales! If you don't want to post it here please mail me at richard@cambriahouse.co.uk.
Thanks

Last year I was appointed to a company who had not reconciled the bank for four years, had no opening balances and the previous accountants would not reply to my professional enquiries. The last accounts omitted a loan account for £50,000, the depreciation charge in the P&L didn’t tie in with the movement in the fixed asset note and that was just the start !

I had no choice but to start by creating an opening balance sheet from sundry information I was able to find within the company. The difference was written off to P&L. The Revenue were kept fully aware of the situation and were very supportive since what I was attempting to do would be a whole lot better than what had been presented previously.

Part of the exercise was to bring the bank reconciliation up to date. I found the best way to do this was to scan in or get somebody to key the bank statements into a spreadsheet and generate a calculating balance to confirm the accuracy of the input. Then import the accounts data (6,600 entries in my situation) and place along side the bank statement data. If you then create a hash record for each transaction of say the cheque number and value you will be able to automatically match entries that are in both documents using vlookup. It will not deal with all the transactions but it will certainly resolve the majority. Then its back to electronic ticking of the entries but at least while you are working off line on Excel you can keep a running total of the entries agreed to prove the reconciliation as you develop it. Once you have reconciled as much as possible off line you will need to reconcile the entries on the accounts system - then pass it back to the client ASAP !

Hope this helps

Gary

Even though I do all my banking over the internet - I still reconcile all bank accounts daily.

Only yesterday a US Supplier paid us for the 2nd time (Paid the proforma before shipment, and paid the commercial invoice sent with the shipment) in error.

I have noticed that we often get paid twice - a least once a mont h - often more than £10K! I've always put it down to poor accounting systems - but I now know its because companies don't reconcile their accounts. I send out statements every month - all over the world - I wonder how many are reconcilled against the accounts!

One of the best and cheapest solutions to to any reconcilliation problem I have found is using several colours of highight pen there are about 5 suffiecently distint colours to use. The only trouble sometimes is remembering what you you used each colour for!

In our system, we record the bank reconciliations within sessions, with reconciled items kept within their session, so each reconciled item has a session number stored against it automatically. All you have to do is mark the items as reconciled, then close the session when you've finished ticking items to the statement.
Each session then represents a statement (or statement page if you want to do it like that). The account can then be printed by session.
I'm amazed that you've found so few other systems that can do such a basic function.

Perhaps I am on the wrong track but Charlies difference could be a combination of a simple transpostion (£46998 / 9) and the odd £2.

Something to keep in mind when doing bank recs over a long period is that unpresented cheques go stale after 6 months. This means that they can be written back to creditors at least, if not P&L if not queried.

This means that if one does the rec backwards from the present one can reach a point where all the unpresenteds can be w/o the cash book. This assumes that any unpresented lodgements will have been queried a bit quick at the time! If the misbalance is immaterial it's not a problem. If it is not, then one has to trawl back to check for, for instance, missing standing orders etc.