Solve circular references
Most people who use spreadsheets for financial modelling will have encountered the dreaded problem of circular references. In this post, we’ll show you how to remove circular references from your spreadsheet model using basic algebra.
(If you don’t know what a circular reference is, then this post is not for you – at least not yet. Perhaps bookmark this page and come back one day when Excel barfs at you for writing a formula which refers to itself.)
Circular references are the financial modeling equivalent of the old philosophical causality dilemma, typically stated as “which came first, the chicken or the egg?”
Circular references in spreadsheets can be either a) intended, or b) unintended. If they are the latter, then there is a mistake in your model and the circular reference should be eliminated as soon as possible.
The longer it persists the harder it is to find and fix. I could write a whole post on clever techniques you can use to find a rogue circular reference – but that’s not what this post is concerned with.
Today, we’re concerned with the problem of “intended” circular references. These are still problems for a number of reasons. Firstly, they require that you turn iteration on (Tools>Options>Calculation) so that Excel can hopefully hone in on an answer. If iteration is not turned on then Excel will not be able to calculate an answer and will display an error, pop up various windows telling you all about circular references, and even display a toolbar dedicated to helping you fix it.
It’s pretty annoying when it happens, but okay, we turn iteration on and our problem is solved. Well, not quite. Leaving iteration on slows everything down. Now your model recalculates multiple times every time a recalculation event occurs. If you’re like me and prefer having your model recalculate automatically, then this verbose recalculation happens frequently. I have seen large models grind to a halt because of intended circular references.
The next, more serious, problem, is that if you ever get a #REF, #VALUE, #NAME, or other similar error in your spreadsheet, this error propagates throughout your model and does not go away even when you fix the (often innocuous) cause. For example you may want to use a defined name in a formula, but if you make a spelling mistake you’ll get #NAME errors throughout your spreadsheet. Even if you fix the spelling mistake, the #NAME errors will remain as they are trapped in the circular reference vortex that you have created. Short of reverting to a saved version without this error in it, the only solution I can think of is to delete the contents of the cells that are involved in the circular reference and then reinstate them. It’s a bizarre solution to a bizarre problem.
Finally, the worst problem is that leaving iteration turned on for intended circular references can sometimes mask unintended circular references. With iteration turned on, Excel is happy again, and won’t pop up annoying dialogs telling you that you have a circular reference. This is terrific until you or someone else mistakenly introduces another circular reference into your model. Now Excel won’t tell you about it, and you may never know you have an error in your model.
So, what to do? Well, you may find that you can eliminate your intended circular references from your model using basic algebra. This is best explained using a simple example. Let’s say your’e modelling subscribers to a service such as mobile phone customers, or subscribers to a DVD home delivery service. The general equation describing your subscriber forecast is: closing subscribers = opening subscribers + additions – losses. Often losses are forecast as an assumed percentage of average subscribers for the period i.e. losses = churn % x (opening subscribers + closing subscribers) / 2.
So here is our circular reference − closing subscribers depends on losses, which depends on average subscribers, which in turn depends on closing subscribers.
However, if we take our two equations:
1. C=O+A-L; and
2. L=P(O+C)/2 … where P = churn %.
We can rewrite these equations to eliminate closing subscribers (C) and thereby break the circular reference. Our rewritten equation 2 would be:
So now we can rewrite our formula in cell B4 to be:
This gives the correct result but this time without any circular references.
You can see that the formulae to calculate losses now refers only to cells which are not themselves reliant on closing subscribers or average subscribers.
It may not be possible to solve all of your circular reference problems in this way, but the more you can avoid building circular references into your models, the better off you’ll be.
If you’ve got any interesting or twisted circular reference examples please share them with us.