To Name or Not to Name?
Last month, Operis Head of Modelling, Michael Jarman, sat down with the co-founder of Full-Stack Modeller, Giles Male, to discuss the use of Named Ranges within calculation logic. Rather surprisingly, Named Ranges has been a contentious issue in the past, with two distinct camps in the modelling world: those who love it and those who hate it. Operis is part of the aforementioned group, having championed Named Ranges in modelling for many years.
This blog explores the pros and cons of the FAST approach to model formulas, the Operis approach (using Named Ranges), as well as what you should NOT be doing.
What your formula shouldn’t look like
Take this example model of a high street shop. The idea is that several people walk past the shop; of that, a percentage of people go into the shop; and of those, there is an average spend per person. So, working out revenues is a straightforward exercise of multiplying all of these inputs together, along with a couple of items pertaining to timing.
What is wrong with the above formula? It doesn’t appear controversial; all that’s happening is its multiplying six numbers together, the formula is relatively short and there are no nested functions. The problem is when you look at the formula, you can’t immediately tell what it does, as there is no context as to what [insert cell reference here] does. To make sense of the formula, you would have to trace back each reference, which can take a long time – and constructing a formula like this is prone to mislinking, which is difficult to diagnose.
Both examples below remove the confusion of what the formula represents, making it much easier to understand and explain what is happening in a model without needing to dedicate time to investigating the formula.
Using a calculation block in your models
As you can see in the above image, this approach to model building includes a calculation block, the chosen formulaic method used by the FAST standard. The idea behind this is that in any block, you should only have one calculation, and it should be on the bottom row. Then any ingredient that you use in that calculation, you should bring through as a line and format that in a blue font, in the rows just above the calculation.
The advantage of using this method is that you can clearly see everything you are doing. However, the disadvantage is that every time you want to refer to something, you have got to repeat it. The formula is not complicated in this example, yet we have had to use seven lines. Whilst this might not be problematic in a small model, as your model continues to grow, it makes everything much bigger than it needs to be.
The Operis Approach: Named Ranges
One of the common arguments against using named ranges in models is that you end up with thousands of them which can make using the Excel name manager a chore. However, Operis don’t advocate for naming every single line as some critics may think. At Operis, we follow the rule of thumb that you should name anything you will repeatedly use in your calculations so that you can then call this range from elsewhere without having to repeat it in the calculation block above. If something is only used once, we instead are happy to use cell references in that instance, which seriously cuts down on the amount of unnecessary named ranges in a model.
For anyone new to models, we would encourage people to consider starting by identifying what they will use or be referring to a lot. The answer is usually things to do with dates, days and timings. If you then name those, it makes formula building much easier. With Excel, you can start typing in names and referring to them. If you apply named ranges in a consistent way, you can easily and quickly find the ranges like ‘days in x’ without having to go hunting for them throughout the spreadsheet.
The other common criticism of named ranges is that formulas end up with lots of ‘+’ and ‘@’ symbols in their formula, as people don’t understand them. All these symbols do is tell Excel to refer to the specific column of the range the formula is in rather than the entire row. Whilst people may criticise this as being ugly, Excel has many other symbols people use in formulas: ‘:’ for the intersection operation, ‘$’ for absolute references and most recently, ‘#’ for dynamic arrays. These are simply a couple of other symbols that any modeller should be able to understand and add to their arsenal.
Advice for people beginning their financial modelling journey
It’s not about knowing what to do; it’s about knowing what not to do. You need to have a framework that you follow consistently, whether it is the FAST approach, Operis Named Ranges or another. Modellers need to have sound modelling principles and understand why they do that.
By following a standard or a framework, you reduce the chances of errors creeping into your model. Therefore, you can spend more time modelling and less time staring blankly at a spreadsheet, wondering why it isn’t working. Only when you understand why the rules are there, can you know when it might be appropriate not to follow them as much.
If you are interested in learning more about financial modelling best practices, check our training courses here.
