Our Top 3 Ways to Ensure Formula Consistency

We’ve all been there. A formula changes somewhere along a row or column and isn’t marked in any way to make you aware of it.

There are many instances where the calculation does need to be altered to account for a change in circumstances particular to an organisation or project, but we strongly encourage that you build your workings in a way that helps you to guarantee formula consistency, so that any irregularities are easily spotted.

Methods for Ensuring Formula Consistency

1.      Maps.

2.      Repeated dragging of formulae across columns.

3.      User Defined Functions.

Our Example

Throughout this blog, we’ll be using a simplified Profit & Loss Account for our examples. It’s a calculation sheet, so there should be no inputs and, at a glance, there doesn’t appear to be anything wrong.

1.  Maps

What are Maps?

Spreadsheet maps use colours and symbols to visually represent the formula structure of any given sheet.

There are several commercially available spreadsheet audit tools out there, many of which create spreadsheet maps, and the colours and symbols used can vary between each tool.

How do they work?

Using our Profit & Loss Account example we’ve created a spreadsheet map, which you can see below.

We’ve also broken down the symbols in this table.

Thanks to our map, we can see that rows 11 and 15 have a different structure in comparison with other rows.

Cell H11 is a hardcode value rather than a formula, and J15 has a formula that’s different to the preceding cells in the row. K15 can be yet another function, or it might be consistent with that in F15. This is because the map only compares each cell to those directly surrounding it, rather than the first cell in a block.

When should I use Maps?

Maps are extremely useful, however, they do require having access to a tool that can create them, or the knowledge to generate them with VBA by ourselves, and for large models, they require a lot of scrolling and investigation. Although it can be tempting, when under time pressure, to skip running map reports, doing so could result in inconsistencies slipping through the net. We recommend using maps at key stages during model development and review.

2.  Dragging Formulae Across

This method is exactly as it sounds and is a very simple way to resolve inconsistencies by selecting the first column of calculations and dragging it across.

It will ensure consistency by getting rid of issues through overwriting them, but what if the previous numbers had been correct?

If calculations are not structured in a conventional way, it is always best to understand why, rather than just overwrite them, and then to rebuild them according to modelling best practices.

3.  User Defined Functions

What if there was a dynamic way to check formula consistency as we make our changes? A method that did not require running and checking reports? What if our model could flag consistency issues immediately?

Luckily for us, there is, and while it does require some basic VBA knowledge, you should be able to apply this method by the time you finish reading this article.

Before we get into the detail, let’s just run through the basics of what we need. We won’t get too bogged down in the nitty-gritty of VBA, as that is a whole topic in itself.

What is VBA?

Visual Basic for Applications, or VBA, allows us to, among many other things, create custom formulae for use in our models.

In order to use them, you’ll need to ‘enable macros’ within the relevant workbooks and then save the files with extensions that allow the retention of macros. (eg. .xlsm or .xlsb).

If you don’t already have it enabled, you will need to add the Developer tab to your Excel ribbon, which can be done within Options>Customise Ribbon, and checking the Developer checkbox in the right list box as per the below screenshot.

Now we are ready to go.

Next, you can go to the Developer tab on your ribbon and select the Visual Basic option.

Within the project window, right click on the Modules folder within the open workbook which you wish to store the function in.

Select the Insert option and click on Module. 

You will now have a new blank white space on your screen in which we can work our magic.

We’ve attached a screenshot of the relevant code along with comments in green. The comments explain, at a high level, what we are doing at each step.

And that’s it. We’ve created what is known as a UDF (User Defined Function). Lets put it into practice.

Note that we have deployed our check in column A of each row where we have calculations.

It has thrown up the same two errors which our map did, but it did so in real time.

Now that we know where the issues are, we can investigate the errors and update our calculations accordingly.

Conclusion

A lack of formula consistency is the bane of many models. Now you have three weapons in your arsenal with which you can combat it and instil more confidence in the accuracy of your work.

For more on how you can ensure that your models pass reviews with flying colours, why not consider our Model Review Course.

We also provide bespoke VBA training so that you can build custom code to make your work more efficient.

If you’d like to learn more about our financial modelling training courses and services, contact us today at info@pps.financial.