Share this content

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

24th Jan 2007
Share this content
Kashflow logo

“Bank reconciliation? What is a bank reconciliation?”
“What are these cheques that you have in England?”

A few years back I was recruited to advise a Dutch company that wanted to introduce a (very good) accounts package into the UK. One thing I noticed was that the package didn’t have a bank reconciliation module.

I told them that this was very important in our country. But they said that nobody needed one in Holland and they couldn’t understand why people needed one in England. And I couldn’t understand why they couldn’t understand. It was a dialogue of the deaf.

Eventually we sorted it out. In Holland and most other European countries, if someone wants to pay a bill, they do it differently. In the UK we send a cheque to the supplier, who presents it to their bank. But the continental way is to instruct the bank to make a payment to the supplier’s bank. It’s all done through the banks. As a result in Holland they never have unpresented cheques floating around with suppliers. And this in turn means they don’t need to do a bank reconciliation.

In Holland, as the Dutchmen explained to me, your bank sends you an electronic copy of their bank statement and you use that as a record of the bank account. They were much too polite to say it, but they were clearly taken aback at the antiquated banking system they found in Britain.

Until then I had simply assumed that the whole world used cheques, but apparently this is not so. And it seems that if you work in a country that uses cheques you will have to do a bank rec, whereas if it doesn’t, you won’t.

But with electronic banking and BACS payments becoming more and more common, we are now obviously moving towards the European model in which the payer instructs his bank.

So will bank reconciliations soon become a thing of the past? Should we be keying in bank receipts and payments at all? Will our accounts package incorporate an RSS feed that pumps in transactions directly from the bank’s computer? I confess that I am confused about how it will all end up.

Back to reality
But coming back down to earth, we have had a couple of Any Answers questions recently about bank recs. Charlie found himself in a bank recs mess after taking up a post as an accountant in a small company. He inherited a banking nightmare, with a credit of £47,000 unreconciled. He tried looking back through Sage and reckons he’s never going to find the sources of the variance. He’s tempted to take one big hit this month and write the difference off just to make the bank reconcile. “Is this in principle incorrect?” he asks.

All the writers sternly agreed: it most definitely is incorrect. Charlie has no choice – he’s got to slog away until he gets it right. As Raymond Carr pointed out, “If your predecessor has buggered up the accounts by that much, what other balance sheet accounts are incorrect?”

To ease Charlie’s pain, Ken Howard made the practical point that he had had to go through the same exercise himself. In his case he found that some suppliers had been paid several times over, while many customers hadn’t paid their bills at all, despite it being shown on the sales ledger. So in the end he recovered many tens of thousands for his company and became a hero.

Between the lines, one or two writers obviously wondered if Charlie was made of the right stuff to be an accountant. Surely, the quality that distinguishes an accountant from other mortals is a dogged insistence on getting things right? While everyone else is saying “that’s good enough, get on home”, the accountant’s conscience just won’t let him/her accept second best and s/he will stay at the office until 10 pm to find the error.

Any impulse carried to excess looks batty, but surely this impulse (I write here as a non-accountant) is at heart a noble one? It reflects a desire to find the truth for its own sake and to satisfy one’s own intellectual standards. It is the basis of scientific method and the distinguishing feature of Western civilisation.

Doing bank reconciliations in Excel
So Charlie has got to sort out his bank rec, but how? Perhaps the answer lies in Excel? Rehan Arain recently said in another Any Answer questionthat he wants to speed up Bank reconciliation when using Excel.

Richard Willis suggested a pivot table, while Alastair Harris thought that a database like Microsoft Access would be better for this sort of work. Reader "ASH" suggested using the Autofilter and an IF statements to calculate the unreconciled difference. Jim Johnson said he was in the process of writing a full bank reconciliation program as an Add-in for Excel which should save many hours for those who have to perform reconciliations regularly.

What about the bank rec in the accounts package?
But should we be using Excel at all? It is necessary to distinguish, I think, between using Excel to do a one-off reconciliation to discover where one has gone wrong and between using it permanently to operate a bank account.

As several correspondents pointed out, the bank account is normally a component within an accounts package, and interacts with other elements such as debtors and creditors, so the essential thing is to get the bank reconciled within the accounts package. There is no point in also running run the bank as a stand-alone module in Excel or Access as well, as you are now having to run two bank recs.

But Excel does come in useful when you are trying to find differences. I had a similar situation to Charlie, where a bank account had not been reconciled for the past 12 months. I used Excel to complete the reconciliation and find the uncleared cheques, but it took me a couple of days which I had to charge to the client.

Did it really have to take so long, and why did I have to do it in Excel in the first place? Why couldn’t I do it in the accounts package itself (in this particular case, Exchequer Enterprise)?

“A tick is not enough”
My own experience of bank reconciliation in accounts packages is that most of them work fine when everything is done right, but are not so good when someone makes a mistake. In other words, they give you no help to recover. I think the reason for this is that they are simply reproducing the method of a manual bank reconciliation. That is, when you match a cheque on the statement, you tick it.

But as Michael Eaton pointed out, when you reconcile an item to the bank statement, it is not enough just to put a tick or a Y against it. You have to record a cross-reference to the statement as well – the statement page number or the cleared date.

This is the crucial point. Why? Let’s go back to first principles for a moment. The bank account in the nominal ledger in your accounts package is basically the same as the bank account in your bank statement, but with one difference - they are sorted into a different order. In the accounts package, the transactions are sorted according to the date when they were posted onto the computer (posting date). In the bank statement they are sorted into the order they were presented at the bank (statement date).

As I see it, we need the facility to print out our computer bank account in both sort orders – in posting date order and in statement date order. If we can print it in statement date order, it then becomes a mirror image of the bank statement. Add a running total column, and doing a bank reconciliation then becomes the easiest thing in the world. You just print out the computer bank account in statement date order, set it down next to the actual bank statements, and compare the running totals. Wherever the running totals start to diverge, that’s where the error is.

The computer must show the statement date or the statement page
This then, is the essential requirement: when you tick an item as cleared during a bank reconciliation, the computer must also store some cross-reference to the statement. This could be the cleared date on the statement or, as Michael suggested, the bank statement page number. Either of them will allow you to re-sort the computer bank account into bank statement order.

When testing bank rec modules for our software Lab Tests this is the key feature I look for. Unfortunately, I’ve never yet found an accounts package that has a report printing the bank account in statement date order. However, about half of them do record a cross-reference to the statement when you reconcile, so you would be able to write such a report yourself with a report writer. Enterprise was not one of the packages that recorded a statement reference – hence the nightmare if ever the bank rec went wrong.

So this is why most accounts packages are useless when the bank rec goes wrong. But pointing this out doesn’t help Charlie very much and he needs some practical help. Fortunately, his package is Sage and Sage is one of the packages that does record the statement date. In my next article on this topic, I’ll have a look at using Excel to help him find that £47,000 difference.

Replies (7)

Please login or register to join the discussion.

By Richard Willis
01st Feb 2007 18:09

Which software?

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 [email protected].

Thanks (0)
By gary_taylor
30th Jan 2007 16:56

Use Excel to resolve the back issue then revert to the accounts
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


Thanks (0)
By frauke
26th Jan 2007 17:16

Bank Recs
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!

Thanks (0)
By EMichaelJakins
27th Jan 2007 18:00

A simple answer.
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!

Thanks (0)
26th Jan 2007 16:23

No bank statement date order?
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.

Thanks (0)
By AnonymousUser
26th Jan 2007 15:57

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

Thanks (0)
By Richard Willis
03rd Feb 2007 09:35

Try Backwards
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.

Thanks (0)