My 3-way Financial Model Doesn’t Balance! What’s wrong?

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 build and needs corrections to balance the 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?

In my opinion balancing the 3-way financials model is all about practice. I recall that during the first years of my work I had hard times to balance the models correctly – it took some time to review and to find mistakes. But the more models I made – the easier it was to balance them. Now, after 18 years in FP&A and financial modeling, my models usually balance out straight away.

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:

1. not linking an item to the CF statement (e.g. forget to link other current assets or some other minor item)
2. linking it with a wrong sign (e.g. add an increase in AR instead of substracting)
3. double-counting an item (e.g. substract 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:

1. Check whether the statements are correctly linked
2. 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.
3. 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 on 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 I 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.

4. If you still can’t spot the problem, I 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).

I don’t want to spend 18 years mastering financial modeling like you did! Are there any shortcuts?

Sure, it didn’t take me the whole 18 years as well. Just practice a bit and you will see your models balancing out from the first attempt. There are 2 general ways to practice that come to my mind:

1. ModelOff Financial Modeling World Championships publish almost all the cases from the previous championships. A very good case for the purpose of mastering the 3-way financials model is called “Bread & Butter” and is available here: https://www.modeloff.com/questions/ . BTW, you get a worked solution there as well.
2. You can get a plenty of hands-on financial modeling courses on Udemy.com. Most of them are structured around building a 3-way financials model.

By the way, I am working on my own course on Financial Modeling for Corporate Budgeting and plan to publish it soon. The course would include a lot of financial modeling tips and tricks and the tasks would definitely include balancing a 3-way financials model. I would probably use Udemy as a platform for the course as well. Seems that this article could serve as a good base to create the section on error-checking your 3-way financial model!

If you are interested in financial modeling – follow me on LinkedIn or send me an invitation to connect! I will keep posting relevant materials on financial modeling. I would also let my followers know when I publish the Budgeting course.