"Bank reconciliation? What's a bank reconciliation?" By David Carter
“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.