I previously posted a circular reference survey: https://camillaculley.com/2021/11/please-join-in-the-circular-reference-survey/ to find out the attitudes to circular references and the likelihood of modellers either switching to iteration (not recommended as then all circularities are accepted) or writing copy paste iterative macros (not recommended as these are “painful to use with constant button pressing involved” – as quoted by one of the respondents).
Thank you to those who filled in this survey. It gave interesting results, as expected, with a small minority agreeing with my viewpoint that no circular reference is acceptable or needed in any form. Don’t forget that modellers would only switch to iteration or write a copy paste iterative macro if they come across a circular reference; neither method really solves the underlying circularity.
However, other respondents quoted the normal areas which supposedly cause circular references. For example, interest, taxes, sculpting and pre-funded debt reserve accounts. The trouble is that there is a circularity echo chamber out there and certain modellers truly believe (and have been told) that their models are so complex that circular references are necessary.
Of course, it can be difficult to get around the problem of circular references and this is the main challenge of financial modelling. But don’t give up. Remember that circularities are errors and come in essentially three types:
- Type 1 circulars: unintentional errors
For example, SUM (A1:A4) in cell A4. These errors are usually obvious and easy to solve.
- Type 2 circulars: logic errors
These circular references are probably the most common and are usually caused by errors of timing, causing a fundamental problem with the logic in the model. Interest and tax are classic examples of this. Modellers need to be clear what happens at different points in time in a period and also need to stop indulging in crystal ball gazing. Yes, all models are based on projections and look into the future. However, there is a subtle difference between projecting the future and knowing the future and this is one of the common circular traps.
- Type 3 circulars: formula circulars
These circulars references occur when there is nothing intrinsically wrong with a set of formulae, but a cell refers to itself, either directly or indirectly.
Equations need to be solved, for example A = B + C, when B = A * D. Sometimes computers need a little bit of human help. The trouble is that many modellers fail to see the connection between algebra and Excel. Don’t throw away a pencil and paper; they are crucial modelling tools.
No more excuses, please. And no, we don’t need complex switches and new functionality in Excel. Financial modelling requires clear thinking, not justification of the illogical. Learning to fix circular references through logical thought is the most crucial modelling skill and is too often ignored.