You know this tricky feeling when you have just completed a 3-way financial model and scroll to check whether the balance sheet balances out! More often than not financial analysts see that the model is not perfectly built and needs corrections to balance things out. Sometimes you find the error very soon, while in more complex situations you can spend hours looking for the error. So what shall you do if your 3-way financial model doesn’t balance?
What are the best ways to spot the errors?
First of all, it is important to correctly link together the 3 statements. The chart below summarizes the process.
As you know, most parts of the cashflow statement are derived from the balance sheet and the P&L statements. However, to ensure that the model balances out, you have to:
- feed the Net Income after Tax item on the P&L statement into the Retained Earnings item on the balance sheet
- feed the ending cash balance from the Cashflow statement into the Cash item on the Balance sheet
If you have done this correctly and if your balance sheet balances out, then congratulations, there is a 90%+ chance that your model is correct. The remaining probability attributes to cases when some items don’t change with time and you can’t spot that they have been incorrectly linked to the CF statement.
There is just one key thing you have to understand! As long as you grasp it, consider you’ve almost mastered the 3-way model! Remember: Every item on the balance sheet has to be linked to the cashflow statement. Moreover, it has to be linked only once and not more than once!
All the errors happen due to either:
- not linking an item to the CF statement (e.g. forget to link other current assets or some other minor item)
- linking it with a wrong sign (e.g. add an increase in AR instead of subtracting)
- double-counting an item (e.g. subtract a repayment of a short-term loan and then include a decrease in the loan)
Some items are, of course, more tricky than others and could consist of several components. For example, changes in the Fixed assets are usually included as CAPEX and depreciation.
So here are a number of techniques to spot the errors:
- Check whether the statements are correctly linked
- Check whether all the summation formulas are correct. It’s so embarrassing to spend hours looking for an error and at the end of the day to find that one of the SUM functions omits a row.
- Calculate the following 3 items on the balance sheet:
- What is the difference between Assets and Equity+Liabilities?
- What is the growth of the difference? In the example in the picture below, every month the difference grows by exactly $21,400. This is equal to the monthly drop in LT liabilities, so we could be almost sure that the problem is there.
- What is the ½ of the growth? In another example, we show a model that is OK up until April. In April the company increases the inventory by $1 mln while the difference grows to $2 mln. This is a clear sign that the changes in the Inventory were incorrectly linked to the CF statement with the wrong sign. Placing the wrong sign doubles the amount of error, so it is important to calculate also the ½ of the difference to easier spot the potential source of error.You should also note that up until April the balance sheet was balanced even despite the formulas on the CF statement were still wrong. As long as a balance sheet item is not changing, you just can’t spot the error you’ve made. This is why even if you have balanced your balance sheet, it still does not exclude a possibility of an error.
- If you still can’t spot the problem, we suggest the following method. Play around with different components of your model. For example, if you plan to attract $1 million in new debt, change the assumptions to e.g. $999 billion. If you have correctly linked this part of the model, then both assets and liabilities of the model should skyrocket but the difference should not change. Undo the changes and play with another component, e.g. CAPEX. However, if the difference has skyrocketed too, then this is a clear sign that the new debt is either wrongly linked itself, or it is indirectly influencing the wrongly linked component (which could be Interest in this case).