Principles for Effective Financial Model Design

A friend of mine, an avid pilot in his spare time, once explained how he interprets weather forecasts when he determines his flight plans:

Weather forecasts are best used as directional guides, not as predictions of specific events. How accurate they are depends on your viewpoint. If you use them to determine if it's going to rain in one specific spot at one specific time, you'll view them as woefully inaccurate. If you use them as a guide it will rain at some point today in an area near you, they're very accurate.

I think that line of thinking is applicable to financial models and financial forecasts. If you view them as completed instruments that predicts the future, you'll be disappointed, but if you view them as a part of an organizational learning process to use today's data to make decisions about the future, then you'll see them as valuable, even when the numbers are predictably incorrect.

In March 2012, I ran a survey to understand what entrepreneurs thought about financial models, and one recurring thought stuck out: that financial models are largely BS.

A financial model is just a fancy equation with a bunch of input variables. If the input variables are mistaken, it doesn’t matter how good the equation is, the whole thing is useless – or even worse than useless, as it breeds false confidence.

Framed as the iterative tools of a planning process, instead of static artifacts or outputs - analysis, not artifact - financial models can be inputs into organizational learning processes and valuable tools for making decisions in an imperfect world.

Being able to use financial models towards those lofty goals, however, requires effective spreadsheet design. We usually build spreadsheets as one-off analyses to address an immediate need, trading off design for immediacy. But the future lives of a model are unknown at conception. Once shared and used, they often take on lives of their own, and as the use expands beyond the original intent, the technical and structural debt of the initial design becomes embedded into the processes and decisions the model is used to support. Redesigning spreadsheets requires redesigning processes. That's backwards.

As our uses of models evolve, so should the model itself, and building a model that can effectively do that without requiring to be rewritten requires a set of core concepts for model design. ^[1] Here's what I keep in mind when I build and use models:

• Structure is everything.
• The decisions we make using a model is more important than the model itself.
• The level of complexity of the model should match the impact of a decision. (Meaning, don't over-engineer.)
• Separate inputs, calculations, and presentations.
• Don't hide the most important takeaway in cell AD543, make your key insights easy to understand.
• Notes help other people understand what a model does, they also help you remember why a model was built the way it was.
• Use visuals, charts and summaries to make the key points obvious at a scan.
• If you make someone interpret a model, they'll probably get it wrong. Make your point obvious.
• Precision does not result in accuracy.
• Don't let detail obfuscate insight.
• Long formulas invite errors. ^[2]
• Atomize. Avoid hard-coding inputs into calculations.
• Range inputs help you deal with the uncertainty of point estimates. The more you think in terms of ranges of values for your inputs, the easier it is to create scenarios and sensitivity analyses to understand how your inputs impact your key metrics.
• Spend the majority of your time modeling on the things that matter for your business.

The last point guides my thinking behind templates. There is a perception in some parts that the only way to understand your model is to build it yourself, and you should never use a template. I understand the idea.

Most templates are highly opinionated, and the opinions can get in the way of building your own analysis and insights. Rigid and inflexible, hard to modify if they don't fit the specifics of your business.

I think of Foresight's templates more as frameworks with prebuilt components. There's a core to the models - usually the Forecast sheet - that handles the core calculations, then a series of components for financial reporting - Statements - management analysis - Summary, Key Reports, Breakdown, Budget, Sources and Uses - with a revenue component - Revenues in the Standard Model, or any of the stand-alone forecasting tools - that can be easily swapped in, out, replicated, or customized for one's specific needs. Yes, the models have opinions, but the intent is to make it more of a system of components than a monolithic template.

The BYOM approach is core to how I make that work; use these templates for all the parts that are not going to drive business insights, and spend your time modeling the mechanics and aspects unique to your business.

Products before spreadsheets, obvs, and if you do have to build spreadsheets, spend your time building the things that matter.