I wonder what percentage of a financial modeller’s time is used trying to balance Balance Sheets? Here are some tips which will hopefully speed up this process:
1. Do it in the morning
I hate to think of how many hours I wasted late at night in the early years trying to balance Balance Sheets.
2. Put Cashflow, Profit and Loss and Balance Sheet on one worksheet
This makes the statements much easier to check.
3. Have a TRUE/FALSE Balance Sheet check on a checks page
Make this check obvious by using either a Watch Window or a check at the top of each worksheet.
Preferably set this up so that all cases are checked simultaneously.
Then, if this check shows FALSE, go the bottom of the Balance Sheet (set up a name so that you can go there quickly).
4. Show the imbalance numbers (not a TRUE/FALSE) for each year at the bottom of the Balance Sheet
These numbers should show:
a.Imbalance in each period (Total Assets less Total Liabilities – Total Shareholders’ Funds)
b.Change in imbalance from period to period
c.Half the imbalance (this helps to pick up incorrect signs)
5. Look for the number or half the number
a.In the same column
b.In the previous column
c.Two periods before
6. Check the sub-totals through all the statements
On my financial modelling courses this is one of the most likely reasons for errors.
7. Check the signs
This is another very likely cause for errors.
8. Think about the timing
When does the imbalance start and when does it finish? What is happening in those particular periods?
9. Check the accumulations
Not accumulating assets, depreciation and share capital is another common cause for errors.
10. Test specific assumptions by putting them to zero
For example, set the tax rate to zero and see if the Balance Sheet now balances.
11. Switch between cases
An imbalance on a Stress Case can often highlight problems in the model.
12. Check each item individually
If none of the above helps, then take each item individually and check the entries in the three statements.
And finally:
13. Never use a fudge factor
Masking Balance Sheet errors is a bad idea as errors can easily affect the numbers in the Cashflow or Profit and Loss.