(Apologies to the Sound of Music)
Part 6: Dummies guide to debit and credits: Bank Reconciliations
A few years ago an associate of mine described the process of doing bank reconciliations as “banging your head against a brick wall until either your head, or the wall, gives way”. Certainly comparing records in your accounting system with bank statements has never been an easy task. Checking off each transaction against sometimes up to 20 to 30 pages of bank records per month, is enough to do anyone’s head in. Yet this is one of the initial and most important controls in verifying the accuracy of your accounting records.
In most cases you would assume that your bank statements are correct (only once in twenty years have I known the bank to make a mistake). More likely, its human error at the data input stage, either by entering an incorrect amount, a dating error or applying the wrong VAT code (“dummies guide to VAT”). This “double check” allows you to make the necessary adjustments in the system, until the amount matches the ending balance on your bank statement. Once this is complete you can verify your debtor and creditor position, and plan future cash flows. What it won’t do is confirm that each transaction has been posted to the correct nominal code (A British Gas invoice posted could go to office supplies in error). A separate investigation would be required to confirm that. What is known is that the all the amounts that have hit the bank are correct, and if some are in the wrong category, then they can be transferred to the right place.
The problem comes in the detailed work of matching these amounts together in a sea of numbers, like trying to find a needle in a haystack. There is nothing more frustrating than allocating all the transactions only to be still being slightly out at the end of the process and to start all over again to find what is missing.
Here are a few things that can be done, within your accounting package, to make it a bit easier:
- The first is to make sure everything that hits the bank account is an amount that you would expect to appear on the statement. If you have bulk BACS payments, wages, cash tills or Paypal accounts then use a control account to allocate the individual transactions, then transfer the final amount into the bank account. As long as the control account is zero at the end then no further reconciliation work needs to be done. If you have individual amounts going straight to the bank account, you would have to find them in your records, manually add them up to match them to the amount on the statement. The example below sets out how this process can be used in a supplier BACS payments run.
Allocate individual invoice payments to control account
Transfer the overall amount to the bank account.
The result is an equal amount in and out of the account leaving the amount at zero.
When you get to do the bank rec only the summary figure appears matching the amount on the bank statement.
- Some software allows for importing of bank transactions directly into the system and auto matching these against open invoices or direct payments. This is done by downloading your bank statements either in excel, csv or quik files that can then post to the account by predefined certain fields. For example all BT invoices can be allocated to telephone with the correct VAT applied (example below from Account Edge). This functionality is available in programs such as Account Edge, Zero and Quickbooks. Although not necessarily 100%, it does cut out some of the human data entry problems which should therefore make it easier to reconcile.
- The other method I use is to enter payments and receipts in the one entry screen in date order. Not all software allows this functionality. In the package I use, Mamut, provides for allocating supplier payments customer receipts, purchase invoices, credit notes and bank charges all processed from one journal entry screen. Each item is applied in order of the bank statement, which virtually reconciles the bank for me. With a review screen to preview the results, I can then check the overall position before posting this to the general ledgers. If I don’t have an invoice to match against, I can still enter this from the same screen, and then allocate it to the bank later. By the time I get to reconcile the bank account I just tick all the transactions in that month and I get an end figure that is very close to the final balance. With a quick overview I can spot the last few errors, apply the correction, and I’m done. I have found that this method halves the amount of time I spend reconciling accounts.
Next in the series “How to use excel to do bank reconciliations.”
Malcolm Ford has had 25 years business experience including working as a financial controller for small to medium businesses. He now implements enterprise software across a wide range of sectors
