To me the most challenging (and interesting) aspect of financial modelling is the eradication of circular references. A circular reference is caused when a cell is calculated by looking at itself, either directly or indirectly. It is very easy to introduce circular references into models and, in most cases, they are caused by the modeller introducing an illogical argument.
If a circular reference is introduced, Excel does not calculate. Financial modellers therefore often attempt to solve this issue either by switching to iteration or by writing iterative (or copy paste) macros. However both of these methods cause problems in models and I recommend neither for the reasons set out here:
1. Models where the calculation method is set to iteration.
Changing the calculation method to iteration means that any circular reference is accepted and Excel keeps recalculating for a specified number of times (or until it judges that a solution has been reached). Switching the calculation option to iteration means that circular reference errors can be introduced at any time without warning. Iterative models are therefore dangerous as they could include many illogical arguments and errors, giving inaccurate results.
I therefore do not recommend this approach.
2. Models which include iterative macros
Financial modellers often attempt to solve circular references individually by writing iterative (or copy paste) macros (using code in order to recalculate each individual circular reference). The iterative macros therefore need to be run whenever certain changes are made to the model, as the model will no longer automatically update.
Iterative macros may or may not give the correct result, depending on why and how they were written. However, even if iterative macros give the correct results, models containing them are difficult to understand, hard to audit and inflexible. Sensitivity analysis also becomes very time consuming as the macros need to be continually rerun.
I therefore do not recommend this approach either.
Models without circular references or iterative macros.
My recommendation is that financial modellers should always aim to create models with neither circular references nor iterative macros. A model free of circular references and iterative macros is easy to run and gives instant results, enabling sensitivity analysis and optimisation to be performed with ease.
It can be a challenging task to produce a model with neither circular references nor iterative macros. It is essential that financial modellers understand why circular references indicate illogical arguments. They must consider timings within the model and also the formula structure. They must learn to think that circulars are errors, not inevitabilities.
What causes circular references?
Most circular references can generally be solved either by reconsidering timing or by rewriting formulae. Circular references can broadly be divided into two categories:
- timing circulars (illogical timings have been assumed or there is an element of crystal ball gazing)
- formula circulars (formula with the same variable on both sides)
How can circular references be eradicated?
My approach is to train modellers to think about why a circular reference has appeared in the first place. My courses provide an in-depth analysis of the causes of circular references and their eradication. To me this is the most important element of financial modelling and an area which I think is generally insufficiently understood.
It seems to be accepted practice that it is not possible to write a complex financial model (for example including debt sculpting and pre-funded debt service reserve accounts) without iterative macros. I can prove that this is not the case, as long as the model and formulae are structured correctly.
Please contact camilla@camillaculley.com for further information.