10 Model Building No-Nos
Having worked with a large variety of Excel-based models, in a number of different settings—investment management, telecom corporate finance, and strategy consulting—I’ve seen a lot of practices that seriously undermine the usability of a model. Here are my top 10:
- Pasted values: I cannot emphasize this enough: never paste values! Flattening formulas into pasted values means that the model is effectively useless now, as changing inputs/assumptions related to the pasted values will no longer lead to changing outputs. Furthermore, it prevents the user from understanding how the value is derived, which means it’s something of a black-box. The last thing you want is to be telling the CEO on a conference call that you don’t know how that number was generated—and that worse, you won’t be able to figure it out, either.
- Failing to differentiate between inputs, formulas and outputs: When inputs, formulas and outputs are not distinguished in some way, new users of the model will have a much steeper learning curve, and the model builder might even forget where inputs are located. Part of your model-building modus operandi should be at least coding inputs. In many industries, blue font is used to indicate an input. Installing a macro kit like Macabacus will make this easier for you.
- Different components on different worksheets: When an Income Statement and the associated Balance Sheet are located on different worksheets, it is all too easy to have columns shift, so that what corresponds to 4Q15 on the Income Statement is now 1Q16 on the Balance Sheet. Best practice is to leave everything on the same worksheet, at least as it pertains to a single scenario.
- Not showing months/years: Some models I’ve seen simply provide a single value (Price x Quantity x Periods) instead of that same value spread out over months or years. This reduces the ability of the model’s users to know when cash flows will occur. Breaking out months/years is helpful for at least two reasons: it allows Financial Planning and Analysis (“FP&A”) to plan for upcoming cash flows on a monthly or yearly basis, and it permits discounting of cash flows. So always break your cash flows into smaller time periods—which leads to my next point.
- Showing years, not months/quarters: Best practice for model building is to use the most granular period unit available, though without needlessly complicating things. For many businesses, this is months, and for investment analysis firms, quarters, given the availability of financial filings. Modeling only on the basis of larger periods (e.g., years) results in the loss of crucial information, and drastically reduces the flexibility of the model.
- Inputs in many places: A strong model will centralize all inputs in a single place. A weak one will have inputs all over the place, so that new users remain unaware of additional input options. A superior model will centralize all inputs in a single place, and then display some summary outputs on the same screen as well, so that whoever is playing with the inputs can immediately see their effect on the outputs.
- Unformatted numbers: Strong formatting bespeaks strong modeling skills, even if, in reality, this is not the case. Do your best to make sure that everything is formatted correctly, because, rightly or wrongly, in many peoples’ minds it will spell the difference between a superior and an inferior analyst.
- No summary: Models are meant to provide information to decisionmakers, who will not always be interested in monthly cash flows and the 20 different classifications of cost used by modelers. A summary—revenue, expense and capital cash flows by year, for instance—is immensely helpful. Know your audience, and do your best to tailor your outputs to that audience.
- No p’s and q’s: Whenever possible, revenue and cost items should be broken into their components—their p’s (price) and q’s (quantity). Inputs then can be modified at a more granular level, providing deeper insight and greater flexibility.
- Overly complex formulas: The master modeler’s dream is, of course, a template that can take a handful of inputs and turn it into a fully functioning model without ever changing a single formula again. The problem, of course, with building this type of template—in which every possible variation is foreseen and embedded into the template’s initial formulas—is that formulas will invariably become overly complex. I know—I’ve done it many times myself. But once there are 10 or 15 nested IF statements, the model quickly becomes unusable to other users—and perhaps worse, almost impossible to debug. Those with a background in programming have to accept the fact that Excel does not lend itself to complex IF statements and switches. There are ways around this, of course, with VBA being the most accessible (though this opens a whole other can of worms). But even better is to allow for the need to fine-tune and tweak the formulas in your template on a case-by-case basis—other users will thank you.