Let’s discover more about the 10 Rules of Financial Modelling. This post looks at why being consistent with your use of colour, formulas, and the structure is vital when building financial models to avoiding confusion by your users.
Consistency is quite a broad topic. It’s one of those things that more reflects the mindset of the modeller. But it’s also manifested very clearly in the model itself. And when we talk about consistency, we’re not talking about formulas.
For financial models to be easy to understand, there needs to be a consistent approach used in all elements, including colours, formulas and structures.
What is consistency?
The financial model has several elements that we need to consider when we talk about consistency. These elements include consistency of approach, consistency of structure, consistency and formulas. There is also the use of colour and various other aspects of the spreadsheet.
Operis’s good practice
To give you an example of what we cover here at Operis, let’s consider the idea of range names. Our models are very consistent in the use of range names. However, some people dislike ranges, and much prefer to use references. Again, that is an acceptable approach.
Range name inconsistencies
What becomes problematic is inconsistency, and people mix and match their range names and cell reference formulas. Although this may seem like a short cut and you may feel comfortable with it, it has issues.
The problem is that people will look at your work and then be concerned about why you are using range names here and cell references there. This will raise questions, and you’re going to have to answer them. To avoid this, you need to be consistent – so pick one or the other.
The importance of colour
Colour is quite an important topic. There are methodologies based around the consistent use of colour to convey information. You may see a range of approaches used, but typically input cells and cells containing hardcoded values may be coloured yellow or blue to indicate the nature of what’s in that cell.
We had operators who would put them on an input sheet separately, and therefore, they don’t need to be coloured. However, it’s also an approach that has to be consistent.
The difficulty comes when there is a failure to be consistent and having a hardcoded value that hasn’t been coloured in. Now it’s on its own lurking in the spreadsheet, and it isn’t easy to find. But colour is widely used not just to mark inputs but also to indicate link formulas, such as where the calculations are blank cells and many other instances.
Generally, colour needs to be very consistently used. This makes it easy to know what the colour is being used for. It’s not a choice to make based on aesthetics. You can’t choose the colour for your inputs one day and change it the next. It has got to be consistent.
Consistency in structure
Consistency also reflects itself in structure. You’d expect your input sheet then to contain only inputs and your working sheet to contain only formulas, and your output sheets to contain only link formulas back to the workings.
There shouldn’t be a mixture.
Seeing calculations on what’s described as an input sheet would be a bit disconcerting. There would be questions as to what precisely that is being done for and where are the results of those calculations going? And similarly, nobody should be using calculations proper on the output sheets because the routine audit checks you’d expect to see in a spreadsheet would relate to the workings themselves and may not link to or may not even be performed on outputs. If your calculations on the outcomes are a risk, they might slip through the quality control process.
Consistent approach
Consistency overall is quite a broad topic, and it’s one of those things that you need to have at the forefront of your mind as you sit down to set up the spreadsheet for the first time or to use the spreadsheet.
You’ll be looking for a consistent approach, a recognisable approach. You may even need to work with colleagues in your team and your department, and in your organisation who may well have well structured, well-founded approaches to modelling. It would be best if you were consistent with that.
Without following your company’s standards, there’s a significant risk of intellectual inconsistency where you’re happily ploughing your lone furrow and building a spreadsheet according to your own principles.
Then later, you discover the cost, that it’s not how you’re supposed to do it in your department or your team. This will cause problems. So do make an effort to find out what the departmental approach is.
You could be perfectly right, you could be doing very good work, but it’s not according to what your colleagues expect. It doesn’t fit in with that, perhaps checking procedures, peer review processes or whatever else, and then you’re not going to do yourself any favours.
In summary, you cannot pursue your own approach with consistency. It needs to be consistent with what is expected. Do make sure that you’re compatible with the methodologies and techniques used and expected by your colleagues in the department.
