I was going to write a detailed guide about using flags in financial modelling. However, there’s already a really good one here, as part of the ICAEW’s financial modelling good practice guide(s).
So instead I’ll just write something more simple to explain why you might want to use flags in financial modelling. And add a specific point about dynamic arrays, which are a newish development in Excel.
What is a flag in financial modelling?
This is where you use a helper column or row (often hidden) to switch a driver on or off.
For example, you might want an expenditure line to increase at a particular interval. You can use a helper row to identify the months when the increase kicks in.
Why are flags used in financial modelling?
Firstly, using flags reduces the complexity of formulas.
A very common tool in financial modelling is to use “IF” statements to relate to a period reference to trigger a start or end date for an expense.
You can put your “IF” statements in a flag row which means that the actual calculation in the model can be much simpler.
A related point is that this makes your inputs clearer to check. It’s usually easier to spot where a row of dashes changes into a “1” than scanning the calculations looking for a change in final number. You can even add conditional formatting to really highlight this.
It’s usually a little easier to fix bugs in the formulas, and to add more functionality if required.
Flags and dynamic arrays
The article I linked to doesn’t mention dynamic arrays, possibly because they were not widely released at the point that this guide was written.
However, their introduction makes use of flags in financial modelling even more desirable.
This is because you can create dynamic time series using the SEQUENCE function. That is, you can build a model that will extend or contract time periods based on an input.
Furthermore, you can create dynamic array formulas by referencing another dynamic array formula. Here is a really simple example:
In the example above, I have created a dynamic array sequence formula in row 5. In Row 7, I have created a dynamic formula simply by referencing the dynamic array in row 5.
So this means that my calculations will automatically expand or contract based on the time period in the model. No more dragging formulas or adding formulas to hide the numbers. Neat, right?
I create financial models as part of my Excel Consultancy work. Contact me if you’d like some advice – an initial chat is always free.
If you want a quarterly newsletter summarising latest developments in Excel and other things I've found interesting or useful, please sign up here. I won't use your data for anything else.