BUILDING BETTER FINANCIAL MODELS

How to Stop Building Financial Models that Suck (Part 2)

Reasons why the established model-building methods don’t work and how to do it better.

Photo by Lukas Blazek on Unsplash

In Part 1 I explained how the traditional method of building models (what I call the “Spaghetti Method”) pulls you into an inescapable complexity trap that first creates a key-person dependency and eventually results in your model becoming redundant. We can look to Software Engineering for lessons on how to decouple the features of a system from its complexity to avoid falling into that trap.

In this second part, we dive into some of these techniques, before, finally, in part 3, we go through a practical example.

It is all Just Data

Your financial model is unique in that there is likely no other place in your business that intersects data from so many different places and time horizons:

However traditional methods of building models seldom acknowledge and account for the fact that it, therefore, needs to have an embedded “babel fish” that maps and translates these disparate sources. Failure to do so causes much of the complexity and consequently, errors in models.

Each of the elements in the above diagram is handled differently in Spaghetti models. For example, historical financial data is usually extracted from the management account reports and either fed directly into the forecast financial reports (arghhhhh!) or wired into the back-end formulas (better, but still argh!). Future financial data coming from the model on the other hand then feeds into the pro-forma reports via its own set of formulas. These two sources of data are treated in completely different ways even though the only difference between them is whether the information references an historical event or a future event. That does not make any sense! By way of another example the only difference between operating data and financial data is whether it is accounted for in your accounting software or not. Again, it makes no sense to think of them differently.

If you have spent time updating your model with actuals, you will know that splicing historical information into your forecast is one of the most painstaking and error-prone parts of the job. And the worst thing is that it needs to happen repeatedly with each passing month to keep your model up to date. The underlying cause of this pain is because you treat these different sources of data differently. But if you zoom out, all data in your business model share three common features:

• The underlying value
• The date to which it applies
• Meta-data that describes what the piece of data relates to including (but not limited to) things like whether it is a financial metric or an operating metric, which entity it relates to, the currency of the underlying metric (if applicable), which product it relates to (if applicable), etc

Instead of treating them differently, what you instead need is a common identifier and template for all data that allows them to speak the same “language”, regardless of whether they relate to the past or the future, or are financial or operating metrics. If they speak the same language, you can bring them together seamlessly. In fact, you already have a part-example of this running in your business in the form of the Chart of Accounts (or CoA) and ledger in your accounting system. The CoA is a list of all the unique accounts in your accounting system. It typically includes a unique identifier, a name/brief description of the account and what kind of account it is. The ledger then handles the “date” element of the above list whereby the underlying value for a (historical) period is allocated to the appropriate time, account and cost-code. This is a well understood and robust pattern that accounting systems have used for a long time. The solution I am proposing is simply modifying your perspective of the ledger and CoA as purely a historical financial repository that sits in your accounting system, to also include forecast data. And the CoA to not only contain financial metrics, but also operating metrics.

You already have half of this via your CoA in your accounting system. So, once you import the CoA into your model, the only real change you need to make is to add unique codes and descriptors for your operating metrics. Below is a fictitious example extract of a model CoA to give you a sense of how this might look:

Model Chart of Accounts

Here is an example ledger extract:

Model Ledger

Do not worry to understand this yet as I will go into details later. But notice that the CoA contains both financial and operating metrics, the ID is the link between them, and the ledger contains historical (T-1) and forecast data.

Key Takeaways

• Historical and future, financial and operating data can all be handled in the same way if you abstract them out to their common, fundamental form represented as a data frame, anchored by an ID. By doing that, the friction from combining them disappears because they all talk the same language.
• Your current Chart of Accounts from your accounting system can be used as the starting point. All you need to do is add a unique ID for the operating metrics. You can then use these accounts in your model ledger — a relatively small change that has a big payoff, as you will see later.

Separation of Concerns

One of the primary complaints I have with the Spaghetti method is that the various pieces of the model are tightly coupled, which means that a change in one piece of the model inevitably has a knock-on effect throughout the model. What do I mean by “tightly coupled”?

I recently had to update a Spaghetti model I built some time back by adding a new market (Country). It was a single-market, two-customer-segment model. The drivers on one tab, fed into calculations directly in the reporting tabs to produce the income statement, balance sheet, etc.

In order to add the additional market (and its two customer-segments), I needed to:

1. Add the drivers and associated calcs to the driver tab as you would expect.
2. Go through each line in the reports to bring through the new drivers where required.
3. Several calculations on the driver tabs used information from the report tabs, meaning changes on the report tabs immediately impacted the drivers and vice versa, creating a seemingly endless update circle.
4. For each line I had to laboriously audit the formula to understand whether it needed to change, understanding its dependencies, make the change if necessary and then ensure it pulled through in all the places it needed to.
5. Inevitably, adding the new market also required changing the format of the financial and operating statements in terms of adding additional rows. The model had several duplicated reports to show different views of the data. For example, it had three income statements. One for each of the two customer segments and a consolidated version. Which meant I needed to go through all the existing reports and modify them one-by-one and then split them out per market. Resulting in an explosion of reporting tabs.
6. I then had to go through the long process of reconciling to ensure I had correctly pulled through all drivers and all my calculations were correct.

This took me two full days of work. It was slow, boring and error-prone. It would have been extremely difficult for anybody else but me to do it because I had built the model originally and therefore had the bespoke structure in my head. For somebody else to work it out would have taken a long time with a high likelihood they would have missed something along the way.

The frustrating thing is that this was just to make what should have been a simple change. Adding a new market to a model should be very straightforward. We are not talking about something out of left field here. But in a tightly coupled model, with the spaghetti formulas it creates, it becomes a high-risk maneuver. Unacceptable!

The underlying problem is that the traditional way models are built makes them tightly coupled by design. Creating the Excel equivalent of a “big ball of mud”. Software engineers worked out a long time ago that this is a bad outcome. But for some reason in the financial modeling space, it is accepted as “the way things are done”. Anybody who has built and run a traditional model will likely have felt the pain of tight coupling, but simply accepted it. The good news is there is another option.

When building software, one of the key design principles is that of “separation of concerns”. In a nutshell, it means separating out the pieces of the software into distinct sections addressing specific concerns. I.e. Loose coupling — the opposite of tight coupling. There are many reasons why this is considered a good way to do things. But that is beyond the scope of this article. Feel free to read up about it yourself. Failing that, If you don’t have time, I will ask you to just trust me on this.

There is a very well-established software design pattern called the MVC (Model-View-Controller) pattern that was created exactly to decouple the elements of a system and therefore separate concerns. Note, do not get confused by the term “model” in this regard with model in terms of a “financial model”. “Model” in terms of the pattern is a component of the system. To avoid confusion, going forward, when I am referring to “model” as it relates to the MVC pattern, I will put it in quotation marks.

As a simplified diagram, the related parts of the pattern look like this (to be 100% accurate, I actually use a derivative of the MVC pattern called the MVP Pattern):

MVP Pattern

Within a financial model context, the components are:

MVP Pattern as it Relates to a Model

Honestly, it is not that much different from what you already know. You already likely have the report tab(s) in the form of your financial statements and operating metrics. You likely also have drivers as inputs/variables. The only real change, therefore, is the introduction of the ledger tab (which we touched on briefly earlier) and the way the components interact with each other.

This seemingly small change has big implications though; none better described than by revisiting our original problem of having to update an existing model with a new market and associated customer segments. I showed how in a Spaghetti Model this change impacted every area of the model. In contrast, what changes would be required if the model used an MVC architecture?

Well, as a result of the loose coupling, all I must do is add the new market and associated drivers for the customer segments and calculations to the driver tab and that is it. Once the data is in the “Model” in the correct format, the ledger will read it and the reports will pick it up from there without any other changes. One set of changes in one place. In making that change, I also know that I have not broken anything from what I had before. Because I have not touched it. So, I do not have to test that either. What took me two days now takes me 20 minutes!

Key Takeaways

• Without realising it, tight coupling is the underlying cause of much of the model pain you feel and why your Spaghetti Models quickly become complex and hard to change.
• We can take learnings from the software development industry and instead, use loose coupling to solve this problem. Within an Excel/Google Sheets model, that means having your drivers and associated calcs in one tab, putting a ledger in place and having the reports read from the ledger. Doing this makes your model simpler and easier to change.
• Using a consistent data structure and a universal ID supports much easier integration with other systems in your business. Both as sources of data into your model, as well as a feed of data into other systems. Amongst many benefits, it allows us to incorporate actuals easily, create accountability, reduce key-person dependency and improve the accuracy of your forecasting.
• Applying the principle of separation of concerns allows you to gracefully migrate if/when Excel is no longer the right tool for your business.

With the theory out the way, next, I will walk you through a practical example of implementing an MVC architecture and compare the process of making a change to an MVC-based model by contrasting it with making the same change in a Spaghetti-style model.

On to part 3…