Balancing Your Balance Sheets

Balance Sheet
2 posts / 0 new
Last post
James Longden A+ 103
Balancing Your Balance Sheets

Anyone who has ever juggled an integrated financial model in Excel will have asked this question at some point in their life.

Some of us ask it more often than others (!) but for everyone concerned it's undoubtedly a headache. The following file provides a few handy hints on how to identify the two main causes of a Balance Sheet not to balancing. Importantly, the file also outlines the key techniques that you can use to locate where the errors are. No more 2am finishes trying to balance your Balance Sheet...

There are two simple approaches:

  • If there is a difference in your balance sheet but that amount doesn't change each period, then there is likely to be an issue with bringing your opening balances (or last historical period amounts) into the outputs.

An item has been omitted from the first period and remains missing for all periods, hence there is no change in the differences amount each period. Temporarily change each Opening Balance Sheet amount one by one - if the difference amount in the outputs changes then that item is part of the reason why your Balance Sheet output doesn't balance.

  • If the amount of the difference changes each period then the source of the error is located in the heart of the calculations linking into the balance sheet.

This is much more common, and slightly more problematic...

However, the process of finding these errors is similar to the Opening Balance Sheet - enter values to see what impacts on the difference. The difference amount in an unbalanced Balance Sheet is often similar in magnitude to the amounts contained in the Balance Sheet itself. Therefore, when making changes to numbers (per point 2. above) the effect is hard to spot if you change items by similarly sized number. 

The secret technique is to enter (temporary) Big Rounded Numbers into your assumptions and calculations as these become far more visible in the Balance Sheet check. See our file via the above link for a worked example of this.

 

Big Rounded Numbers - Your New Best Friend

Tim Emonson A+ 7

A few tricks I use to help balance those Balance Sheets that are causing problems:

  1. Start with calculating the difference between the Net Assets and the Net Equity, per James' diagram above.
  2. Then calculate the movement in the difference from one period to the next. This will often produce a pattern that you will recognise from the underlying calculations and will help narrow down your search.
  3. As a last trick, I will then divide the movement in the difference by 2 (to halve it), as often the error is caused by a wrong sign (i.e. something positive comes in negative or vice-versa which will cause the impact to be doubled). The set of numbers from this may produce a pattern that you will recongise from the underlying calculations and will help narrow down your search as well.