Using MS Excel to do bank recs

This follows on from our previous article on doing bank reconciliations.

Once you have completed your first round of reconciliations within the system, and the balances still don’t match, it’s difficult to go back and find missing transactions. A majority of accounting software will only allow you to tick each matching item and it’s not obvious what has still to be allocated, yet if you export the information to excel, there are a few tricks to help in isolating errant transactions. This is where the tools in excel come into their own. We have provided a download to guide you through the process, it can be found here: Bank Reconciliation Exercise

1. If you pay suppliers by BACS, or other electronic means, make the reference the same as the description on the accounting system. Cheque number can also be used but it won’t give the suppliers name. This is a good way to match text descriptions which comes in handy if there are a number of transitions that are the same amount. Try to get your customers to do the same so you can allocate which invoices they are clearing down.
2. Download your statement from the bank’s website in a csv or excel format. From your accounts system you can export bank transactions in the same way. Copy these side by side in one spreadsheet and sort both in date order so the transactions from each side are close to matching.
3. Recreate the rolling balance by taking the amount from the previous statement and adding the “paid in” and subtracting what is “paid out” (Formula =G3+E4-F4). You can then visually compare variances between the balances from the bank and your accounting system. They won’t match precisely, but you may be able to isolate a date range upon which there is a variance.
4. Use a plus/minus formula to compare the difference in the bank statement and accounting records.
5. Sum the debits and credits from each source to see whether you are looking on the income side, or expense. In our download example we can see it is the expenses that are out.
6. If you are lucky and are only out by a certain amount you can check that figure by highlighting the column and using the “find” function (Cntl F) to select that cell.
7. Instead of ticking the transactions, use different colour formatting in each cell for the amounts that match. This provides a visual audit trail so you can easily see what is still left to reconcile. In the later versions of excel you have greater choice of gradients so you can use combinations for categories of income or expenses. On the related download you can see how easy it is to see what you have done.
8. If it is not an exact amount but you know the range (numbers between 25 and 40) then again highlight the column and set up a conditional formatting formula to change the colour of cells within that criteria.
9. Failing that you can highlight a range of cells to sum the overall amount.

Once the account has been reconciled you still need to clear out all the transactions that are left over that are not unpesented items to appear in future. I went to one client that had several pages of unreconciled transactions that went back over a number of years which meant that even though the system said it was reconciled the actual trial balance beared no resemblance to the bank statement. These tricks are more of a visual aid to help you isolate the transactions that may be out. The adjustments still need to be placed into the system but this method does allow for easily identifying what needs to be done to bring the account into line.

For further information on formulas in excel go to our favourite formulas section or downloads area.

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