Intro to Financial Modelling - Part 6: Formula blocks and consistency

In this instalment of the blog series, I will be talking around the principles covered in page 8 of the Financial Modelling Code, "Use consistent formulas".

When we speak about formula consistency in the modelling world, the first thing that usually comes to mind is the concept of having the same formula used across a whole row, and sometimes up and down a block. This is a well-known golden rule of modelling – when you are working with a timeline of calculations, you need to make sure one formula can cover the whole timeline, under a variety of input assumptions.

However, formula consistency could also mean writing your formula in a similar style throughout the model. We should remember that models are generally very large documents with many sources of information contained within one file, but as they are usually portraying a single entity, they need to feel like one uniform piece throughout.

There are a few reasons why consistency is important for both model builders and users.

Quicker for you to build

From a build perspective, the process of creating the model will be quicker and more efficient if you take a consistent approach to formula writing. It enables you to take shortcuts and stop repeating work unnecessarily. A generally efficient model build process will prevent tedium for all involved and potentially save on model build cost.

Easier for reviewers

A thoughtfully built model with consistency born in mind will result in a model with fewer unique formulas for model reviewers to audit and keep track of. When there are fewer formulas to interrogate and understand, the model is easier to check for robustness. Again, this could mean a lower build cost and a smoother model audit process.

User friendliness

A logical and consistent approach to building also improves the user experience by making it easier to follow and intuitively grasp. If an inconsistent approach is used throughout the model, it will result in a confused collection of calculations which are time-consuming and painful to digest and follow. However, if similar styles of formulas are used throughout, a user can more easily get into the mindset of how the model is working. This will make it straightforward to share with other colleagues and third parties, which is particularly important when working on a transaction.

Risk reduction

The more formulas a model has, the more chances there are for some to break or be incorrect. In the case of more than one formula used across a row or block, there is more possibility that a formula could be inadvertently overwritten by another user when updating and re-pasting a calculation. Ensuring consistency from the beginning will help to maintain the model’s accuracy in the longer term.

In case we need more persuading of why things should be kept consistent, here are some examples of what happens if you don’t follow this principle.

Firstly, here are the outputs of a simple rent calculation - there is a different payment amount in March and September each year.

But here are the underlying formulas – there is a new one in each column of the row:

If we now need to update the rent calculation to incorporate another element, for example, an inflation factor, we would have to update each column one by one. This would be time-consuming and is definitely not robust.

Secondly, here is an example of a cost calculation being achieved in three different ways, when the same one applying to all three costs would have sufficed.

We have Staff costs using the INDEX function, Administration costs using SUMIF and Office costs using a particularly long-winded nested IF function. That is three different formulas that are effectively doing the same thing (transferring a yearly input into a quarterly timeline), so it will take three times as long to examine and understand, not to mention writing them in the first place.

As a model user, I would not be reassured seeing the above two examples, and would likely come to the conclusion that the model hasn’t been planned very well!

So, we now know that consistency is very important, but what practical things can we do to make sure we live by these principles?

Here are my top tips.

Tip #1 Cell anchoring

The Code helpfully reminds us of cell anchoring in this section, which is vital when writing formulas that you need to copy and paste across a row or block. For a recap on cell anchoring, see this Excel Tip of the Week post.

In general, whenever you are writing a formula, think ahead to what it might be reused for in future and write it in such a way that it has anchored references, even if it doesn’t need them at that specific moment. For example, with a formula that references the date at the top of the sheet, anchor the row of the date’s cell reference now, because you may later want to copy this formula to use in another section further down the sheet.

The aim is to write as few formulas as possible in a model – always be asking yourself how you can use anchoring to make formulas more flexible and efficient. This will make it quicker to build and reduce the chance of cell reference errors.

Tip #2 Formula style

a. Use the same functions and writing style

Pick your favourite functions and approach to writing formulas and stick to them wherever you can. For example, if you like to use IF statements when working with flags, then use this style throughout:

=IF(flag=1, 2, 0)

If you prefer Boolean logic, use this throughout instead:

=2*(flag=1)

Try not to swap between the two approaches.

b. SUMIF and SUMIFS

Those who are familiar with SUMIF and its plural SUMIFS will know that the functions are written in a different order to each other:

=SUMIF(range, criteria, sumrange)

=SUMIFS(sumrange, criteria1range, criteria1, criteria2range, criteria2…)

To make it less confusing for yourself and other model users, sticking to using just SUMIFS, even when there is only one criteria, will reduce the number of functions in the model by one - every little helps when it comes to making models easier to follow!

Tip #3 sensible sign reversing

Another important element that will aid your audience’s understanding is being consistent with your positive and negative signs in your revenues/costs, and assets/liabilities. On the financial statements of the model, avoid using a variety of signs in your formulas when linking through to the calculations.

In the example below, there is no logic or pattern on which way around the sign is when pulling calculations through to the financial statements. You might wonder whether the VAT debtor/creditor is displaying the correct way around, as this could be both positive or negative.

To avoid this ambiguity, making sure all calculations are done in their final intended sign will enable them to be pulled through to the statements without needing to flip the sign. This will help to check for debit and credit errors more easily.

Look out for a future article on sign conventions later in this series.

Tip #4 Duplicate coding or sheets

At the model design and planning stage, predict which areas of the model will be similar and approach the build process with consistency in mind. If your model is going to contain more than one company or asset, then think about the nature of their calculations and consider creating one block of coding that can be applied to all of them. Build the first company first, test it and finalise, then duplicate the block to the other companies. If you have been clever about your cell anchoring in the formulas, this will make creating a new company almost instantaneous and prevent you reinventing the wheel each time.

You could even do this with entire sheets – get the first one ready and then roll out to more when needed.

Once you have done this, if you retrospectively update one of the sheets during the model build process, be sure to make the same change to the others as well, so that the consistency is maintained.

See part 2 of this blog series for more ideas on setting up the sheet infrastructure.

Tip #5 Adopt the same style when updating someone else’s model

There are occasions where you will need to update an existing model that someone else has built, to add in new functionality. It is tempting to do things your own way, but try to adapt your style to match the existing precedent, to prevent a Frankenstein’s monster of a model. For example, if it already uses SUMIF to aggregate things, you should also use SUMIF - don’t introduce another method for the sake of it if the same thing can be achieved using the current formula style. The same applies for the formatting presentation and style used in the model.

If you already have a model and would like to check the formulas for consistency, you may be pleased to know that there is an Excel shortcut for doing this. Once you have highlighted a row, use Ctrl + \ and Excel will select all cells in that row that are not consistent with the one to their left. The screen will take you to the first one. If all are consistent you will get a message saying “no cells were found”.

This also works for formula consistency down a block, just add a shift into the shortcut – Ctrl + Shift + \ to check a column of formulas.

For every rule there can be an exception and the code mentions the possibility of inconsistent formulas used “for a good reason”. However, you should mark them clearly with prominent formatting.

There are formula inconsistencies that sometimes occur in the modelling world and an example of this is the beginning of a control account.

This subject does remain controversial amongst the modelling community and there are some who would not approve of any formula inconsistencies for any reason. However, what we can all agree on is that typed-over formulas with hard-coded variables in the middle of a block should always be avoided!

Even if you aren’t building a large complicated model, I would recommend using these rules of consistency in all your spreadsheets to make them more efficient, robust and user friendly. Ultimately, regardless of your personal style, if you stick with a consistent approach throughout it will help to minimise errors in models and improve their clarity for your audience.

Next in the session

Part 7: Consistency - Timeline