Intro to Financial Modelling - Part 15: Calculation Techniques - Basic Good Practice

Financial models in their simplest form are a series of calculations used to forecast a variety of outputs. They can be extremely sophisticated and perform multiple functions, making them extremely useful tools; however, sophistication often comes hand-in-hand with complication, and this leads to a risk of the model being difficult to understand and/or use. In some models, there is a necessary trade-off between usefulness and usability, for example, in the case of functionality vs file size and speed; however, both can normally be achieved if basic good practice is followed to ensure that calculations are robust, easy to use and easy to understand.

Useful – Generating Reliable Outputs

For those who have watched the Great British Bake Off, you will know that although all bakers start with the same ingredients in the technical challenge, the quality of the final bakes is often a far cry from what the judges had in mind. In the same vein, many of us have come across models that contain all the right inputs but produce questionable results. The inability to produce reliable, or edible, outputs reduces the model’s value as well as the end user’s confidence in the model, or baker. Furthermore, as discussed in the previous post, the principles for Error Reduction should drive good practice and look to minimise the risk of model forecast errors.

Below are some basic tips to keep in mind during a model build to generate reliable outputs and improve the robustness of your model. Avoid Circular References A circular reference is when an Excel formula directly or indirectly refers to itself. These references are usually unintentional and often arise when dealing with items such as debt sculpting in project finance transactions. Intentional circularities, achieved by enabling iterative calculations, are not recommended as these increase the risk of errors going unnoticed. When this setting is disabled, Excel will give a circular reference warning and point to the offending row(s).

I should note, in some situations (for example if the formulae in question are particularly complex or very interlinked), Excel can flag that there is an issue but cannot detect where it exists, and in others it may fail to calculate but be unable to identify the issue.

Where there is a perceived circularity, it is worth considering if an algebraic solution is available, or if a compromise in precision is acceptable to stakeholders. This can help avoid the use of VBA. The best way to manage calculations that inevitably require self-reference is to use a “circularity breaker” macro (i.e., where the key calculation does not flow through to any code, but a copy/paste of its output does). These options achieve an iterative approach without causing a circularity, but it is important to signpost if current values are not ‘live’. In the example below, a simple alert message is displayed in red if the copy row is not equal to the values pasted.

Avoid Hardcoding

The use of hardcoding in formulae depends on context and requires judgement. If a value is subject to change (e.g., tax or FX rates), using a separate input value would be preferable to “*25%” in the formula. Hardcoded values in formulae are difficult to spot for users, who may not use review software, and can be the cause of the inaccurate outputs if they are overlooked when a model is updated. For widely recognised unit conversions (e.g., kW to MW or monthly to annual), I consider additional inputs unnecessary, and using /1000 or *12 is fine, so long as appropriate labels are used!

Avoid Unnecessary Rounding

The code also recommends not to round inputs/workings in calculations as this can compromise the accuracy of a model, e.g., rounding an interest calculation to 2 decimal places. It is recommended to have all aspects of the calculations unrounded and to present any final outputs that need to be rounded by using simple formatting.

Usable – Easily Operated by the End-user

The usability of a model is crucial, and so thought should be dedicated to who is using the model and their level of competence with Excel and models. General users may not have the same level of Excel knowledge as the author, but still need to be able to analyse the models. If they need to dig into why a model is producing certain outputs, the inner workings of a model cannot be a mysterious black box. Additionally, models often change hands as job roles change and so the following considerations are important:

(i) Minimise Calculation Complexity

Keep formulae short and simple by breaking calculations into several smaller parts. The Financial Modelling Code encourages us to split timing and logic calculations separately and flags prove particularly useful in this regard – more detail on flags will be given in an upcoming post. By splitting code across several rows, it makes it easier to review by reviewing its output, as opposed to diving into the formula itself. While some modellers meticulously follow recipes or rules such as the “rule of thumb” (whereby a formula should not be longer than the length of your thumb), I prefer a framework approach and take “short and simple” as a guideline. Although it depends on who the model’s ultimate end user is, I would retain a slightly longer formula if it significantly improves the end user’s ability to use the model. If in doubt whether a calculation in a single cell is too long or complex, see how much time it takes a less experienced colleague to understand it. This litmus test can help prevent the use of overly complicated formulae – plus if you manage to eat more than three biscuits in the time it takes, a simpler approach might be preferred!

(ii) Build Traceable References

Step-by-step calculations help users to understand the model logic. This complements the advice detailed in earlier posts highlighting the code’s call for Clarity. In the example below, you can see that although prices are initially calculated on a separate page, a direct reference in row 5 has been used to bring the relevant inputs/working cells together in one place. This approach avoids “cross-sheet references” which increases the time it takes to review a formula.

The close placement of precedent cells allows anyone inspecting the model to easily understand the formula without moving away from the formula cell. This makes it clearer for the model user and quicker for a reviewer.

Also, at this point, the code mentions “array formulae”. Array formulae are those that can perform multiple calculations on one or more items in an array – for example, a row or column of values, or a combination of rows and columns of values using Ctrl-Shift-Enter (you may have heard these referred to as “CSE” formulae). They are slow, difficult to edit, and their abstract nature makes them hard to understand by a non-technical user. Array formulae can also be prone to error, therefore at odds with the intended robustness of models, so it is recommended to avoid them.

(iii) Use VBA and Macros Sparingly

As mentioned previously, VBA is usually hidden in the background and contradicts the idea that models should be easy to read. As such, the Code recommends limiting its usage to when it is absolutely necessary. More information regarding this can be found in here.

Final thoughts

As a modeller, if you focus on the above points, then your model will be easier to review with minimal errors. Of course, there are other good practice points, such as implementing checks or using data validation, and this post is not an exhaustive list. Nevertheless, keeping some of these simple concepts in mind when building a model will help to ensure the calculations in your financial models are written with your end-user in mind and so deliver real value.

About the author

Jonelle is Senior Consultant at Amberside Advisors Ltd, a firm of financial advisors specialising in providing consultancy and advisory services in the low carbon, renewables and infrastructure sectors. She has experience in developing models for infrastructure and energy projects, as well as in providing training for finance professionals.