If you are a financial modeller who uses copy paste iterative macros to calculate, then please read on.
I am always coming across modellers who insist that it is impossible to build a complex financial model, particularly in the project finance sector, without using copy paste iterative macros. Indeed, the competence to build such macros is often viewed as the mark of an advanced modeller.
What is a copy paste iterative macro?
If a modeller has created a circular reference, they sometimes will “solve” this by rewriting a formula so that the link which originally caused the circular reference is broken by referring instead to a value, hence “removing” the circular reference. However, this value needs to be continually updated until a solution is reached. The modeller therefore writes a macro to automate this process. This type of macro is a copy paste iterative macro.
Why would anyone write a copy paste iterative macro?
When modellers cannot solve their circular references and do not wish to switch their calculation method to iteration (see last month’s blog on why switching to iteration is bad practice), they write a copy paste iterative macro in order to circumvent their circular reference.
What are the implications of using copy paste iterative macros?
Copy paste iterative macros are capable of calculating the correct answer for a circular reference (although there is no guarantee that this is the case). However, the model will no longer automatically update if other numbers change. Imagine how complex a model can become if there are multiple copy paste iterative macros and hence multiple areas of values no longer linked to formulae.
Why do circular references occur and can they be solved?
Circular references are caused by poor logic and therefore bring a very high risk of inaccuracies into a model.
There are three reasons for circular references:
- A simple formula error (easy to solve)
- An error in logic or timing (very common and solvable if sufficient logical thought is employed)
- Formula circulars (when, for example, simultaneous equations need to be solved through the use of algebra)
What is wrong with using copy paste iterative macros to solve circular references?
Admittedly, copy paste iterative macros can calculate some circular references accurately (but only the occasional ones that fall into category 3, formula circulars above). However,
- the model will no longer be completely automatic as there will be areas with hard-wired numbers which will only be updated when the relevant macro is run;
- powerful analysis tools (such as data tables) become useless; and
- writing a copy paste iterative macro may not necessarily give the correct solution to the original problem.
Is it hard to solve circular references?
Yes, it can be hard to solve circular references. However, they can all be solved by applying logic.
Is the problem of using copy paste iterative macros widespread?
I would love to know the percentage of models which include copy paste iterative macros; it seems to be very common in certain business areas.
Why do modellers make excuses for needing copy paste iterative macros for certain circular references?
I cannot answer this question, except to say that it is time for modellers to stop making excuses, to start using logic and to build circular reference-free models.
And yes, this applies to every model, yours too. Remember, circular references are warning you that you have a problem in your model, not that the model is particularly advanced. Don’t over-complicate your models by using copy paste iterative macros when you need to sort out your formulae instead.