Ever build a financial projection model – one that is sophisticated enough to iterate and where changes automatically flow through all the financial statements – and then the balance sheets don’t quite balance when you’re done? It can be a frightening, frustrating, and a mega time-consuming affair.

Here’s some advice and tricks of the trade you can use to fix a leaky projection model. Oh, how I wish someone had shared these with me earlier in my career; I might even have hair left.

Step One: Start with the balance sheet. Go through the balance sheet line by line and trace each line item to the cash flow statement. Make sure each item is represented once and only once on both statements. Put a check beside the balance sheet line item if that is the case. If you get to the end and still haven’t found the problem, there are 3 probable causes to check:

1. Did you reverse a sign?
2. Did you double-count an item?
3. Did you forget to include an item?

Step Two: Let’s further diagnose the problem. A very telling sign is whether or not your balance sheet is off by a constant number each period.

1. If Off By Constant Number Each Period: This usually means just one number is either missing or flowing through all your statements. Look through the first balance sheet for that exact number. One common cause is that you may have forgotten to include the number in a sum formula.
2. If Off By a Varying Number Each Period: This may mean that there is more than one mistake, or that the mistake is part of a calculation or percentage. Pay particular attention to tricky items like Deferred Revenue. Here are two good strategies you can use in this situation:

• Strategy #1: In a column off to the side of the offending balance sheets, calculate the absolute amount of change between periods (the delta) and compare this to the amount the balance sheet is off. Focus in particular on the first period. Try to find the exact delta number on the first balance sheet.
• Strategy #2: Do exactly the same as Strategy #1, but also calculate the cumulative sum total of the deltas and the delta of the deltas (to see if there is consistency in the delta growth or if the problem is compounding by period).

If all of the above has failed, there is one last base to cover before contemplating the “nuclear option”* …you may wish to examine the input or assumptions sheet if your model has one. Occasionally, the problem doesn’t even reside on your balance sheet or your cash flow statement, but rather in one of your input formulas or assumptions. Check it out.

Next time I’ll begin to discuss Equity Value and Enterprise Value – two fundamental but very important concepts in Corporate Development and M&A.

*The “nuclear option” is, of course, trashing the whole damn model and doing it more carefully next time.

Posted by: Mory Watkins