| |
Classic Large Model Miscues
Say you take a new job and inherit a large financial model built by someone else. What to do: rebuild from scratch or modify the existing model?
In the short term, there’s no choice—you have to use the existing model to move the project forward. But the problems models solve change over time, and that means adding functionality. I’m often tempted to take what the model has already and just add to it. The first problem with this approach is that I’m responsible for everything in the output, whether I built it or not, and so I wind up scrutinizing every single line. When I take a look--Mama Mia!-- it can be ugly. Typically I wish I hadn’t, because I see mistakes that push me to scrap and rebuild. Here’s what I see in classic model mistakes:
- Time periods not synced up across pages. You know, March of 2011 is in Column U on the revenue page, Column S on the expense page, and Column W on the income statement. When examining formulas in the income statement it’s hard to see that everything is brought across properly. This is a simple discipline to adhere to: keep time periods in the same columns.
- Embedded numbers in formulas. I thought we learned this lesson long ago, but I still see embedded numbers in formulas. I’m ok with a “/12” to convert from annual to monthly, but a “+40”? Really? How hard can it be to add a row with the assumption labeled so that the input is made clear to everyone?
- Doing the same calcs over and over again. This has a few varieties. For example, if:
J20 = $G$10*$G$11*$G$12*J15 and
K20 = $G$10*$G$11*$G$12*K15 and so on,
I want to replace the “$G$10*$G$11*$G$12” by doing that calc in, say, $G$13 and having J20=$G$13 * J20 instead. This example is trivial, but sometimes the repeated portions are 100 characters long. I wind up auditing these and breaking them apart, and my current feeling is that it’s better to just understand the concept and replicate things in a simpler formula that I build myself. The second variety of this is when, say Total Revenue is calced once to add revenue and then later as part of an accounts receivable calc. The risk is that one formula gets updated but not the other, leading to inconsistencies further later in the model.
- Very inefficient formulas, making it hard to understand and slower to calc. I see IF statements where people put an IF wrapper and replace the entire cell in both the true and false branches instead of having the IF scoped just to the place where it matters. Consider which one you’d rather debug:
- =IF(A, B*C*D*E*F*G, B*C*D*E*F*H) versus
- =B*C*D*E*F*IF(A,G,H)
- Consistency with positive and negative numbers. I prefer to handle math operations in formulas, so when I have a contra revenue, I subtract it instead of making the cell a negative number. Now display is one thing—I’ll have a report that shows it as a negative, because that’s easier to read. But I want all numbers to be positive. The problems of doing this the other way are that when you subtract a bunch of numbers that are added but one is subtracted and the original number is negative, well, it’s a lot to keep track of, leading to either mistakes or confusion. Best to keep it simple!
So after finding these types of issues, I’m convinced that I have to build from scratch. Then, after functionality is added, we need to do a reconciliation process for customer acceptance. In the best case, we install software switches to replicate all oddities of the old model, but that takes a long time. An alternative is to do unit testing and reconcile sections, e.g. Revenue COS, Capex, and be ready to explain differences away. As the new model is cleaner, the onus is on the folks who did v1 of the model to explain why theirs is giving correct answers.
Given that modeling is about identifying the critical 80-20 drivers, I strive to keep assumptions simple, adding complexity only when it makes sense. A recent case illustrated this point nicely. The v1 model had hundreds of lines leading to an opex number, 500 headcount expense lines plus dozens of other lines, but it had an opex minimum as a % of revenue, so over half the expenses were plugged. It didn’t stand out visually at first since a few other lines were left out of the calc, so it fluctuated over time and thus was obscured. So I cut out the heavy detail and left the single input. Yes, it’s a crude attempt at forecasting, but it accurately matches the old model in less space and with greater clarity, so I count it as a win.
In the final analysis, I’m convinced that most models are better off scrapped. With solid unit testing, you can quickly get an organization to accept a new, more powerful tool that you can use to inform strategic discussion.
|
|
|
