When I started in financial modelling, I worked for one of the Big 4.
As part of my induction week with other new joiners in the Corporate Finance team, we had several days of modelling training.
It wasn’t good.
Dull, irrelevant and delivered by a guy who hadn’t seen an actual transaction for decades.
What he told us about modelling was wholly detached from what was happening “on the shop floor”.
As this training was worse than useless, I had to learn on the job. I did this by reverse-engineering the deal models colleagues were using.
I tried to copy the approaches I saw as best I could.
Unfortunately, the models I was copying were also not very well built. And so, I just taught myself bad habits.
A few years later, I met the founders of FAST, John Richter and Morten Siersted. I was blown away by the thought that had gone into how to structure a model well, borne out of just the kind of experiences I’d also had.
It was the first time I became aware that there was such a thing as a modelling standard.
There are two broad categories of error in building financial models.
Commercial / conceptual errors. These kinds of errors come from not fully understanding what we’re trying to model. We have perhaps modelled our understanding correctly, but it does not reflect the reality of the business.
For example, we might model debt interest using an act/365 day count convention when an act/360 should have been used. The calculation mechanics are all correct, but we’re using an incorrect assumption leading to an incorrect result.
Formula/calculation errors. We may have understood how the business works and how we should model it, but make formula or structural errors. There are many varieties of these kinds of mistakes. For example linking to the wrong ingredient in our calculation, hard coding incorrect values into formulas, formula inconsistencies etc.
Standards do not reduce the incidence of modelling errors. Human beings will continue to make errors at a consistent rate. To quote Ray Panko:
“Humans are incapable of doing complex cognitive tasks with great accuracy. Even for simple cognitive tasks, such as flipping switches, error rates are about one in 200. For more complex cognitive tasks, such as writing lines of computer code, error rates are about one in 50 to one in 20.”
While standards do not change the error rate, they make it less likely that errors will go unnoticed.
They do this by reducing variability in the modelling approach. If the model is built to a common standard, the modeller knows what to expect from the structure. The easier a model is to review, the more likely errors will be detected.
Additionally, common to all standards is a focus on making models as simple as possible.
When we do things the same way, we get better at them. If the model is highly structured, macro tools can be introduced to do repetitive tasks and therefore cut down model build time. Code can be easily reused. Model build mechanics can more easily become part of “muscle memory”.
Having everybody on a team model in the same way makes collaboration easier. One part of the model can be built by one person and continued by another. It’s easier for modellers to review and share each’s other work. Unnecessary thinking time is reduced.
According to the 2021 Financial Modelling Survey 95% of modellers believe that standards are important. 41% of modellers work in organisations where standards are applied rigorously. In professional services firms, this figure goes up to 64%.
Of those who use a standard, the majority use an in-house code of best practice:
So while modellers believe that standards are important, and many are following a standard, outside professional services, most are not.
I’ve taught thousands of modellers from many of the world’s top companies over the years. And what I’ve seen is this: in all but a handful of dedicated modelling shops, it’s hard for companies to achieve standardisation across a team.
People have their own preferences. And these have often been built up over years of practice. It’s challenging and painful for them to change. And they may not be convinced that what they are switching to is better. And indeed it may not be.
As software developer Paul Graham said, “programming languages are not merely technologies, but habits of mind as well, and nothing changes slower.”
There are several modelling standards. Overall, they are similar in more ways than they are different. They all emphasise similar elements of good practice; the separation of inputs, calculation and outputs; consistency of structure and layout; clarity of labelling etc.
What’s important is that you pick a standard and follow it. This is more important than the particular choice of standard that you use. I’ve provided links to find out more about each of the standards mentioned at the end of the article.
The ICAEW Financial Modelling Code is a great place to start. It gives a series of recommendations for good spreadsheet practice. It’s been compiled by a broad group of practitioners and so has wide applicability to different user requirements.
The approach that I use and teach is based on FAST. FAST is highly prescriptive and certainly not everybody’s cup of tea. Some people dislike (quite strongly!) the use of links or “call ups” throughout the model and the fact that this increases the number of rows it takes to complete a calculation.
FAST stands for:
Flexible: To be effective, the structure and style of models require flexibility for both immediate usage and the long term.
Appropriate: Models must reflect key business assumptions directly and faithfully without being cluttered in unnecessary detail.
Structured: Rigorous consistency in layout and organisation is essential in retaining the model’s logical integrity over time, particularly as a model’s author may change.
Transparent: Effective models are founded upon simple, clear formulas that can be understood by other modellers and non-modellers alike.
FAST is “open-source” and maintained by a not-for-profit company called the FAST Standards Organisation. Unfortunately, not much development has gone into FAST over recent years which is somewhat of a missed opportunity.
SMART became the Mazars financial modelling standard after Mazars acquired Corality (who originated SMART).
They have a list of guiding philosophies that most professional modellers would agree with:
Operis and many other companies apply an approach that, on the surface, appears quite different but which has the same level of discipline and structure as the others, and adheres to a lot of the same principles.
Their system makes extensive use of “named ranges” in the model build. It’s a highly systematic and well thought through process for modelling. Where FAST uses calculation blocks to ensure that the precedents to formulas are visible and easily read alongside the formula, by naming everything, the “Operis” approach means that formulas can be “read” like sentences.
Like the extensive use of call-ups in FAST, extensive use of named ranges is not to everybody’s taste.
BPM was the first to systematically codify a modelling standard back in 2003. BPM is now under the Modano brand. There is an interesting tool on the Modano website which allows you to customise your own spreadsheet best practice guidelines.
Most of us don’t care what language an app is written in. We care about the user experience and whether the app does what we need. We rely on the software developer’s expertise to make sure that all the “behind the scenes” coding works.
Similarly, while modellers can get wound up about which approaches are best, in the end, many clients and users don’t care.
They rightly rely on us to ensure that the model is well built and free of material errors, and critically, that it gives them the answers they need, quickly.
This post was originally published in the Financial Modelling Handbook