BUILDING BETTER FINANCIAL MODELS
How to Stop Building Financial Models that Suck (Part 3)
Reasons why the established model-building methods don’t work and how to do it better.
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. To avoid falling into that trap we need a way to decouple the features of a system from its complexity.
In part 2 we saw how historical and future, financial and operating data can all be handled in the same way in your model if you abstract them out to their common, fundamental form. By doing that, the friction from combining them disappears because they all talk the same language. We also looked out how using the MVC pattern (a much-used pattern from Software Engineering) can be used to separate the model into distinct pieces that makes modifying and updating much simpler and less risky.
In this third part, we go through a simplified practical example of how this can work in practice.
Implementing MVC
I made a statement in part 2 that probably seemed implausible when you read it. But clearly I had enough of your attention to keep you reading. I now need to demystify that statement
“all I have to do is add the new market and associated drivers for the customer segments and calculations to the driver tab and that is it. How can it be that simple? The secret is in using Excel as a database, by leveraging the consistent data frame pattern in the “Model”, View and Controller components. But instead of using SQL to get or send data between the components, we use the SUMIFS()* formula to achieve the same outcome.
I will use an artificially simple example model to illustrate my point. It uses “number of customers” and “ARPU” to drive revenue on the P&L (I.e. number of customers x ARPU = Revenue). You can access it here so you can follow the formulas.
If we wanted to add another country to this, the model would change like this (note, the lines in blue are all the lines I had to change in order to incorporate the new Country):
As you can see, I have had to propagate the change through the whole model.
In contrast, this is how the single-country version would look in the MVC pattern. I break it up into sections for clarity:
The Drivers section contains the various drivers for each Country/Customer Type combination. You will notice two things stand out in contrast to the Spaghetti Method:
- The information has been split out into distinct columns and
- the “CoAID” column contains the unique ID from the Chart of Accounts we introduced earlier. It contains both financial and operating metrics. ID 511110060 represents the “Number of Active Customers” operating metric and 521110060 represents the ARPU operating metric.
The calculations that use the Drivers are also contained in the “Model”:
You will notice in the Calculations section, that the format of the columns is identical to the Drivers section.
For each Country/Customer Type combination, we calculate the revenue, by multiplying the Number of Active Customers by ARPU as before. However, the way we do the calculation is not simply by pointing to the appropriate cells as one would do in the traditional way. Instead, we use a SUMIFS formula that looks up the appropriate values in the Drivers section, using the Chart of Account IDs (CoAID), Country and Customer Type as the criteria. This might seem overly complicated compared to just pointing to the appropriate cell like you would in the normal method. But you will see shortly why I do this and the benefit it brings.
The hard work is in writing the first formula. Thereafter, you just drag the formula across the row. And to create the “Corporate” Customer Type line, you simply copy and paste the “SME line” below and change the Customer Type to “Corporate” and the formula will produce the correct result. You, therefore, do not need to write the formula more than once.
Next, we look at the Controller:
Once again, you can see that the columns are identical to the columns we saw in the Drivers and Calculations sections. The difference is that we now only have the rows for revenue. The ledger uses the identical SUMIFS methodology to get the appropriate information from the Calculations section. Once again, it uses the variables from its own line to find the appropriate values in the Calculations section. And the same applies here, where creating the first formula is the hard work. Thereafter you only need to copy and paste the line below and apply the correct coding to the line (I.e in this example it just means changing the “Customer Type” in the second row from “SME” to “Corporate”).
Finally, we look at the View.
Once again, you will see that the columns are consistent. The difference is we only have one row, representing the consolidated revenue.
You will notice the use of the “*” in the Country and Customer Type cells. The “*” represents a wildcard search in the SUMIFS formula. By using the “*” it is telling the SUMIFS formula to sum cells from the Ledger where Country is anything and Customer Type is anything. I.e. return ALL Advisory Services revenue from the Ledger, regardless of which Country or Customer Type it comes from. Meaning if we add a Country or Customer Type to the Ledger, it will automatically pick it up without having to make any changes.
We now have the same answer on revenue we got to using the Spaghetti Method. I am sure by now you are thinking Yes, but with a lot more complexity. How is this possibly better?” The answer starts becoming clearer when we try to add a Country and its associated Customer Types.
Below are all the sections from what I described above, with the required changes made when we add a Country. Once again, the cells in blue font are where changes/additions were made to incorporate the change:
When adding the extra rows in the Calculations and Ledger sections, I simply did a copy and paste and changed the coding. Very quick to do. You will notice something interesting though. Even though the figures for revenue have changed to incorporate the additional Country, I have not had to change the P&L at all. The formula has stayed the same. The wildcard search in the SUMIFS formula (using the “*”) brings through the correct answer without having to change it.
This is great, but you likely already see a hole in this approach. As we add more dimensions and metrics, the ledger will quickly become excessively big. In this instance, we have 2 countries, 2 Customer Types and 1 Metric, meaning we need 2 x 2 x 1 = 4 rows to capture all the permutations. Just by adding one more Metric, we would go from needing 4 rows to needing 8 rows (2 x 2 x 2). As you can imagine with a full model that has hundreds of different metrics and a multitude of dimensions, this quickly becomes massive. We, therefore, need one more trick to solve this problem.
The trick is to add selectable filters to the View layer and have the Country and Customer Type cells in both the View (P&L) and the Controller (Ledger) reference the filters. I will not show the “Model” component (Drivers and Calculations section) again as they are identical to what I showed above. But as you can see, we have now added selectable filters that filter the data in the P&L and Ledger. Choosing an item from the filter updates the corresponding column in the Ledger and the P&L:
In the below screenshot, I have selected “X” from the Country dropdown list filter and “SME” from the Country Type dropdown list filter. Notice how, when I change the filters, it updates the corresponding values in the Country and Customer Type columns in the Ledger and the P&L:
By choosing Country “X” and the “SME” Customer Type from the filters, the P&L is now only showing revenue for SMEs in Country X. From a P&L perspective this is powerful because now with a single line, I can see the revenue in a consolidated way. Or just for SMEs. Or just for Corporates. Simply by changing my selection in the filters. Meaning instead of having to create multiple revenue lines in the P&L to see these different perspectives of my revenue, that single line in the P&L gives me all perspectives possible.
You will also notice that the Ledger now only has one line rather than the four it had before.
Now let us add another country to this modified model as we did before to see what changes are required. Once again, cells in blue are cells that were added or changed as a result of the addition:
You will see that by adding the extra Country, we have had to add the lines to the “Model” components as before, but nothing else has changed. Neither the View or the Controller have been touched
I come back to where I started at the beginning of this section where I asked the question “…all I have to do is add the new market and associated drivers for the customer segments and calculations to the driver tab and that is it… One set of changes in one place. How can that be?”
The answer is in the separation of concerns via the MVC pattern, the SUMIFS formulas and the filters. If I had used a different example such as changing the category of an expense in my P&L, the outcome would have been the same. One change in one place.
In a contrived example like this, the benefit might seem trivial. But if you aggregate this benefit across a whole model that likely has hundreds of metrics and many reports, you can quickly see that the upfront investment in time quickly pays off — and will continue to pay off for the life of the model.
Key Takeaways
- By separating out concerns, when we need to make a change to the model, we likely only have to make a change in one layer of the model (I.e. either the Model OR the View OR the Controller). This significantly reduces the time, risk and complexity of making changes.
- Although switching to using SUMIFS formulas, splitting out information into defined and consistent columns and using the MVC pattern might seem intimidating, once you have created a single metric running all the way through the components the hard work is done. Thereafter, it mostly becomes a copy & paste exercise. Something even somebody with a basic level of Excel can do.
- Using consistent data frames turns your ledger into a pivotable data cube that allows you to view your historical, future, financial and operating data from as many perspectives as you have dimensions. Without having to create a plethora of additional reports.
Conclusion
At the beginning of this article, I made the argument that the traditional method of building models creates an inevitably fatal (from the models’ perspective) trade-off between features and complexity. My hope is that I have shown you an alternative way to model building that; whilst it may not completely remove this correlation; it certainly can materially “flatten the curve”. And in so doing allow you to reap the benefits of your model for longer.
Although I have refined this technique over time and it is working well for me, I am sure there are areas it can be improved. I am eager to hear from you what resonates and what does not and ideas for improvement. Please leave a comment below.
This article has turned out to be far longer than I intended when I first sat down to write it. There are therefore a lot of topics I have had to leave out. If you are interested, I can go into more detail with follow-up articles. So please leave comments if you are interested in hearing more about any of the below topics or anything else you think I have missed.
Potential additional topics for advanced discussion:
*The new FILTER() function in Excel and Google Sheets looks to be an interesting alternative to using the SUMIFS function. But the reason I haven’t suggested it is because they are implemented differently in the two platforms. Meaning if you had to port from one platform to the other, these formulas would break. Whereas the SUMIFS formula is identical in both. So you can simply copy a model done in Excel to Google Sheets or vice versa and it will work the same. If you are working in an environment where you know you will only use one platform, then it is definitely worth investigating.
