What’s wrong with cell (GF256)!?

“Bust in the Model” is a phrase that will strike utter terror into the heart of almost anyone who works in a financial capacity whether they be in investment management, research, banking, or corporate finance.

It is helpful to dissect the phrase into its parts.  The “model” is the labyrinthine excel spreadsheet file, often composed of many sheet tabs and sometimes running into the dozens of columns and hundreds of rows per tab.  The model attempts to do two things:

First, it presents the historical record of a company’s financial performance.  From revenue to net income on the income statement, assets and liabilities from the balance sheet, and the historical operating, investing, and financing cash flows of a particular business.  All this data must be punched in cell by cell in a mind-numbing and soul-destroying exercise.

Secondly, and this is where things can go wrong, the model is used to “project” the future performance of a business (if you’re a banker or PE guy, the model will be some hideously complex morass that attempts to show the financial outcome of a merger or the returns on a leveraged buy-out).  This act of fortune telling is very tenuous business and many in the financial world take greater comfort in what the model tells them about the future than what their own common sense would dictate.  To bolster this false sense of security, some financial analysts will add greater and greater amounts of tedious detail to their model, breaking down the big financial picture until it’s completely pixelated into unrecognizable bits of information.  If Wal-Mart sold one more snickers bar this month, they’ll have that in the model.

The more skilled quant jock will then use excel and all its data-crunching capabilities to “sensitize” numerous variables so that the manipulation of one variable will flow through the rest of the model and change the outcomes.  A book could be written about how the model is manipulated to produce the results you want your superiors or investment committee to see, but I digress.  Suffice it to say that when a model gets complex enough, it will often contain one or more “circular references” which is excel-banker-speak for ‘the damn thing begins to take on a life of its own and doesn’t know its head from its tail’.  Essentially, the manipulation of one input variable flows through the model affecting other variables that ultimately feed back into the first variable.

Now, when there is a “bust in the model” this means your model has an error of some sort and the problem needs to be resolved or you’re going to look like a fool or worse.  It could be as simple as an accidentally deleted cell, or something as complex as excel being locked in a brain fart trying to calculate dozens of circular references you created because you thought you were some kind of financial genius.  Either way, the tell-tale signs are cells with angry-looking exclamatory nonsense like #REF!, #DIV/0!, #VALUE!, and #NAME? (and no, these are not cute twitter hashtags, these are signs of the devil).  If you see an output page full of these errors right before you’re due for an investment committee or to sit down with your MD to “go over the numbers” (a future FEotD), chances are your bowels will evacuate.

Thankfully it usually doesn’t come to that.  What happens more often is that as you’re wrapping up that big presentation at about 10 PM the night before it’s due, you notice one of your projected ratios looks out of whack (like an IRR of -21000758%, or a leverage ratio of 15x).  In a fit of panic and madness (and using all the excel gymnastics you can muster) you trace backwards from that cell to all the formulas and input cells that led to the nefarious result.  You search bug-eyed across dozens of lines on multiple pages hunting for the proverbial needle in a haystack where things first went wrong.

When you have at last untied the Gordian Knot it’s 2:30 AM.  You can now get home for an uneasy 3 hour sleep before you get up and get back to the office to make sure the model didn’t change itself while you were away.