What are macros and are they a hindrance or a help in financial modelling?
A macro is code which automatically performs a sequence of steps. Essentially there are two different types: simple macros and copy-paste (or iterative) macros.
Simple Macros
Macros can be useful for simple repetitive actions. For example, if there is an area of the model which is often printed out then there is no harm in setting up a macro to do this automatically.
Copy-Paste (Iterative) Macros
Copy-paste (or iterative) macros however come with a serious health warning. Particularly in project finance modelling there seems to be a common belief that an advanced model requires copy-paste macros. These macros are used as a method of “solving” circular references and admittedly can end up giving the correct result (although that is not always guaranteed). However, their inclusion means that:
- The model no longer gives automatic results (because macros need to be rerun as assumptions change).
- The model becomes a “black box” of complexity and therefore time consuming to run and analyse.
- Data Tables, which are a powerful analytical tool, cannot be used effectively.
A financial modeller will only write a copy-paste macro to get around a circular reference. However, no circular reference is logical. Therefore, it follows that, if a model is logical and structured correctly, no copy-paste macro is necessary.
The ultimate challenge of financial modelling is to understand circular references: why they appear, why they are illogical and why there is no place for any circular reference in any financial model. However, it is a difficult task to spot and solve circular references and modellers are often working late at night in a pressurised environment: not an ideal place for the brain to think clearly. Financial modellers therefore need proper training and proper breaks.
No more excuses please: there is no justification for circular references anywhere. Ever. And that means no copy-paste macros either.