BUILDING BETTER FINANCIAL MODELS
How to Stop Building Financial Models that Suck (Part 1)
Reasons why the established model-building methods don’t work and how to do it better.
The economic impact of Covid-19 on many businesses has already been or will be devastating. Which businesses will survive, how and why? This question has been occupying my mind a lot lately. There are undoubtedly many things that create the resilience required to weather a storm like the current one. In my opinion, having an effective, mature continuous planning system, underpinned by a flexible forecasting model is one of the most important tools you can have. It is the map that will help you navigate through the storm to calmer waters.
The way we traditionally build our models, frankly, sucks though. They quickly become like spaghetti, are difficult to keep up to date with the latest information, let alone modify, provide little accountability and invariably create a massive key person dependency because only one person truly understands the model. Until the model eventually becomes so complicated that the cost of changing the model outweighs the benefit you will derive from making the change. At which point your model is no longer useful as a planning tool. So, you have no choice but to throw it away and start again. [I lay bare my bias by henceforth referring to these types of models as “Spaghetti Models”.]
I spent most of the past 8 or so years as the CFO of an African fintech start-up. During that time, our business changed several times and we experienced numerous existential shocks that severely tested our resolve and our resilience. In particular, our forecasting muscle was tested. In some instances, our forecasting stood up to the challenges. And in others, it did not, requiring a wholesale change. But one thing remained consistent. No matter what we did or how many times we redid our Spaghetti Model, we inevitably ended up moving up the Feature-Complexity curve until, first we created a key-person dependency, and, eventually the model became too complex. So we had to throw it away and start again. Rinse and repeat…
What it ultimately taught me however is that the way we were building our financial forecast trapped us into an inescapable trade-off between features and complexity that only ever seemed to have one outcome.
I, therefore, set out to come up with a different way of building models that avoids that trap; a way that decouples the relationship between the features in your model and its complexity. This article describes the method I have developed through trial and error. My hope is that you can benefit from it and thereby help you to also stop building models that suck (or at the very least, suck less).
Before I get into that though, first some context…
Prior to my time as CFO, I worked as a software engineer for a few years. To be honest, I wasn’t very good at it. But I was fortunate to work with a team of experienced “grey-hairs” who were gracious enough to tolerate me. They had seen many technology “silver bullets” come and go…and then come again under a different name. Suffice to say, they were cynical about technology. But they were fastidious about architecture, consistency and sweating the small things. They had learned from bitter experience that 1) change is constant and inevitable, 2) complexity abounds unless it is actively tamed and 3) small things grow into big things that can bite you. Their non-negotiable antidote was using well-defined design patterns underpinned by robust architecture and good habits.
Why am I telling you this? Well, it explains why my approach is more like building software than building a financial model in the traditional sense. Throughout this article I will use techniques that I learned from software engineering; techniques that help engineers optimise software for changeability and simplicity that can similarly be applied to building financial models.
Building a forecast financial model is a technical topic and I am wary of getting into the minutiae too quickly. So, in this first part, I provide some high-level principles that guide my design. In part 2, I explore some of the theories behind my solution and in part 3 I go through a practical example.
To begin with, I will start by describing the base functionality typically required in a business model used in your Continuous Planning Process. (If you don’t do that already, you should!) Your specific circumstances might mean that you do not need all these immediately, but I am willing to bet that eventually, you will. I am all for the “MVP” (Minimal Viable Product) approach to doing things. But I also know that making architectural decisions without considering functionality you know will be needed is like setting off for a trail run without your shoes on “because the first 100m is grass”. My architecture makes allowance for all these features. And therefore, when it comes time to introduce them, you can be confident your model can handle them.
- At a bare minimum, the model should be able to produce an income statement, balance sheet and cashflow out 3–5 years, rolled up by month, quarter, year etc. The model needs to provide that 3–5-year horizon at all times. I.e. as you move forward in time, the horizon needs to keep moving forward too.
- In addition to financial metrics, the reports should also include operating metrics and pertinent ratios. These are crucial to ensure your scenarios are plausible/coherent.
- The reports need to be able to show data across multiple axes including (but not limited to): by entity (including consolidated), by product, by market, by currency, by customer segment, by different time buckets, etc. However, this functionality should not result in a proliferation of reporting tabs which become a maintenance and change nightmare.
- The model needs to be easily and quickly updatable with actuals (both operating and financial) so it is up to date with the latest information. In this regard, data very quickly becomes stale. For the model to be useful, the refresh process should be easy to do so it can be done regularly.
- The model needs to be driver-based to allow quick scenarios to be produced.
- Your business is constantly changing. And therefore, the model needs to have changeability baked into it. The model needs to be able to easily support the addition of or change in products, markets, segments, costs, funding sources, reporting, etc.
- The model needs to support P&L accountability. In the early stages of your business, accountability might be shared, but as you grow, there will come a time when you want specific people to be quantifiably responsible for and accountable to specific and granular aspects of your business.
- Whilst Excel/Google Sheets might be the starting point, it is unlikely to be the solution at scale. The model, therefore, needs to support a graceful migration to more advanced tools if or when required.
- The model needs to support easy scrutiny of its accuracy between what was forecast versus what actually happened to allow the model to become increasingly robust. From a forecaster’s perspective, the delta is what you care about regardless of whether it is positive or negative. Sandbagging is not welcome here!
Each of these base requirements; in isolation and together; has large implications for how your model needs to be architected. Note however, that these base requirements are still fairly extensive. The implication being that the technique I describe in this article is designed to help with more complex scenarios. It is not designed for a “back-of-the-envelope” exercise where you have to quickly pull something together half an hour before a meeting and it will be thrown away afterward. This is because there is some base infrastructure you need to build with this technique regardless of the size or complexity. The payoff therefore only occurs above a certain level of complexity.
Key Takeaways
- The “Spaghetti Method” of building financial models pulls you into a complexity trap that creates a key-person dependency and eventually results in your model becoming redundant.
- The Software Engineering industry points to a way to decouple features from its complexity that we can use in designing our models.
- Whilst I have developed this technique specifically in relation to building a business forecast, it can be applied to other use cases where a model is likely to be used (and modified) over time, where there are multiple inputs and outputs and where a bottom-up approach is needed. That said, there are instances where this approach is overkill. If you are doing a quick “back-of-a-matchbox” model that will be thrown away, this is not the right technique to use.
The next sections go into detail on the various pieces of the technique and the rationale. This is where things get more technical. Note, the method I describe works equally well in Google Sheets as it does in Excel as the formulas work in both. Going forward, I mention Excel only but know that it applies equally to both.
Let us dive into part 2 shall we…
