Balance Sheet Reconciliations

Matching = Reconciling
Photo by Priscilla Du Preez on Unsplash

Balance sheet reconciliations are a key component of regular accounting to ensure that your transactions are correct. They help you detect issues such as missing transactions or failure of controls or process. This article sets out what they are, what they’re for and how to do them.

The purpose of a balance sheet reconciliation is to “prove” that each figure in your balance sheet is correct.

Common Examples of balance sheet reconciliations

The most common example of this is the bank reconciliation. This is where you compare the figure in your cash book in your accounting system to the amount on your bank statement, and identify the differences. For example, you may have recorded a cheque payment to a supplier in your accounting system but the supplier hasn’t cashed it yet, so your actual bank account shows more money than your accounting system.

Finance teams often reconcile general ledger control accounts to primary ledgers. For example, they will run an aged debtor listing from the Sales Ledger and check that the grand total on the report is the same as the Sales Ledger control account in the trial balance.

If you are VAT-registered, you will keep track of your input and output VAT in control accounts. When you do your VAT return you will clear these accounts and record a VAT creditor or debtor. You reconcile the balances by running a dummy VAT return and checking the figures in boxes 1 and 4 against your control accounts.

Another common balance sheet account will be your PAYE and pensions control accounts. These record the amounts you have deducted from employees and contributed as an employer for Tax, NI, apprenticeship levy and pensions. The balance in your balance sheet should equal the amount your payroll software tells you that you owe to HMRC.

If you have any fixed assets, you’ll have a fixed asset register, either in your accounting software or in a spreadsheet. Reconciling your fixed asset accounts means checking that the total value in your balance sheet agrees to the register.

Beyond these, you will probably have some very business specific control accounts.

The golden rule is – if it’s in your balance sheet, you need to reconcile it to something!

Why do you need balance sheet reconciliations?

There are several reasons to reconcile your balance sheet regularly;

  • It helps identify missing transactions.
  • It helps identify mistakes – particularly the sort of errors that are very easy to make such as transposition errors. (Eg you intended to transfer £4,951 but you actually transferred £4,591).
  • Transactions in the balance sheet are often “hidden”, i.e. they are not reported in management accounts. If somebody creates a balance sheet account and starts debiting it when they make a payment, they could be concealing expenditure.  

How often should you do them?

As often as you need to. This may sound flippant or obvious but lots of organisations think that “regularly” has to mean “monthly”. If you have a control account with a huge number of transactions and lots of potential for error, you should reconcile it weekly or even daily. If you have an account which is almost dormant then it’s probably fine to just check it once a quarter.

Even in a small organisation I would want to see a full trial balance with linked, completed reconciliations every quarter. It’s important to do it from the trial balance so that you pick up any new account codes. In other words, don’t just take the last set of reconciliations and update them.

Common issues with balance sheet recs

  • Timing. You need to do them when you are reasonably sure the figures are complete, i.e. that you have posted everything you’re expecting to. This is why lots of finance teams opt to do reconciliations monthly, after they have closed the ledgers.
  • Making sure your reconciliation is actually proving something. As an auditor reviewed many meaningless reconciliations. Often, what was presented as a reconciliation was just a slightly summarised version of transactions in that account. For example, a PAYE reconciliation should reconcile your balance sheet figures to a separate report from your payroll software. If it just lists the journals you’ve posted to those accounts, it’s not a reconciliation.
  • Identifying the error but not the reason or solution. Your difference is £50, look, here’s a transaction for £50, so you put it down as “reason for difference”. But unless you can explain why it’s wrong and how you need to fix it, you haven’t reconciled anything.

Further tips for balance sheet reconciliations

  • If you have a difference that you can’t identify, see whether the digits add up to a multiple of 9. (i.e. if your difference is £459: 4+5+9 = 18 = a multiple of 9). If it does – it’s probably a transposition error somewhere.
  • Does your difference mean something to you if you either halve it or multiple it by two? It probably indicates a signage error.
  • If you have to do more complex reconciliations, where the only way to solve it is to compare lists of transactions from one source to another, there are a number of tricks in Excel (and power query) you can use to speed this up. For example, Excel and power query both have “remove duplicate” functionality and also the ability to transform numbers to their absolutes. Eg ABS(-3) returns 3.  Depending on your data set, you can quickly use a combination of these to remove everything that nets off.

Final thought – simplifying the balance sheet reconciliation

Can you speed up and simplify balance sheet reconciliations by changing the underlying processes?

For example, in a former role, we collected income on behalf of a third party alongside our own. We credited money into a control account during the week and then paid it over weekly based on a sales report. This was a challenge to reconcile because the weekly payover amount equalled a number of separate transactions so you couldn’t automate the matching process. So we created daily payover journals instead of weekly.

This may sound like more work, but we first automated the payover journal from a CSV derived from the report. So although it was a couple of minutes extra work to post seven transactions instead of one, it saved hours of time in reconciling the differences afterwards.

The longer term solution was a better sales system that didn’t create these issues in the first place, but that’s another story.

To conclude…

Balance sheet reconciliations are essential within a system of internal control, but in my experience they can often be inadequate or inefficient. If you’d like an independent review of what you have in place, please contact me.

If you want a quarterly newsletter summarising latest developments in Excel and other things I've found interesting or useful, please sign up here. I won't use your data for anything else.

Leave a Reply

Your email address will not be published. Required fields are marked *