Solve circular references

  • Tagged with:
  • Algebra,
  • Circular references,
  • Design,
  • Errors,
  • Excel,
  • Spreadsheets,
  • Subscriber equation,

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:

L=P(2.O+A)/(2+P).

So now we can rewrite our formula in cell B4 to be:

=-B7*(2*B2+B3)/(2+B7).

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.

5 Comments so far Join the Discussion

  1. Rickard Warnelid

    22nd August 2009 at about 2pm

    Hi Darren,

    I fully share your view that we should always avoid circular references. I also like your example of how one can backsolve the algebra to get a non-circular solution to the described problem.

    The example is pretty similar to an example I used to present when I was delivering financial modelling training courses for Navigator Project Finance – ‘Solution to Circular Interest – Calculate Interest on Average Balances’.

    http://www.navigatorpf.com/training/tutorials/solution-to-circular-interest-calculate-interest-on-average-balances

    Your example is probably more interested for a wider audience so well done!

    Cheers,
    Rickard
    http://blog.corality.com

  2. John Tjia

    13th October 2009 at about 3pm

    There is a place for circular references, typically in integrated financial statement modeling (where the income statement, balance sheet and cash flow statement work are interlinked). The use is for the calculation of interest expense on the shortfall of debt (typically called the “revolver”) that the model calculates, or the excess cash on the balance sheet that is also produced by the model. The interest expense as calculated is the average of the beginning and ending revolver. Because the ending revolver has as part of its calculation the interest expense from the income statement, a circular reference is produced. (The same is true for the interest income calculation on the excess cash.)

    This kind of deliberate circular reference produces a converging calculation. Typically, you would need no more than 10 iterations to reach the solutions. Given the typical size of these financial models, today’s computer CPU speeds and the efficiency of Excel, 10 iterations so fast as to be imperceptible.

    There is a danger of error messages caught in the circular reference loop, but you can stop the propagation of such error messages by putting an ISERROR test within an IF statement–e.g., =IF(ISERROR(C100),0,C100)–in one location within the loop, such that if there is an ERROR, the formula works as a 0 and thus breaks the loop. This allows any error messages, assuming the source has been cleared up, to be flushed out.

    As far as having the iteration setting masking any UNintended circular references, you can get around this by having a macro that 1) uses an IF statement that can turn off the intended reference and 2) turns off the iteration setting. Any unintended circular reference will then reveal itself. Another macro can reset these settings.

    The algebraic solution is elegant. In some cases where such an approach is not possible, the deliberate circular reference has its place as one of the tools to use in advanced financial modeling.

  3. Rickard Warnelid – Corality

    15th October 2009 at about 10am

    John,

    I would like to add a couple of reflection to your comment.

    1. I personally don’t mind circular references, but there are certain industries (in particular senior debt banks) where this is regarded as completely unacceptable. If one is to have an intentional circular reference in there then your method of using as IF-statement to turn it off is neat.
    2. Isn’t there a risk that the ISERROR can cover up issues of non-convergence? I am thinking of a situation where a start guess of ‘0’ does not actually achieve a converging solution. Clearly this wouldn’t be a problem as long as the model behaves nicely and converges, but what happens when someone else has tinkered with the model and this is no longer the case?

    Regards,
    Rickard
    http://www.fimodo.com

  4. John Tjia

    21st October 2009 at about 4pm

    ISERROR traps errors. A non-converging calculation is not an error per se, so the IF(ISERROR formula that flips to a 0 and breaks the loop is not triggered by it. The non-converging calculation would not be “covered up” in any way, and thus can continue to be visible for closer examination and, hopefully, a solution.

  5. Rickard Warnelid – Corality

    23rd October 2009 at about 9am

    In the majority of cases where an error (of the type described above) is created in a financial model it is due to a division by zero. In these cases my typical approach would be to have a formula of the type (if(denominator = 0, output =0, denomitator) rather than a if(ISERROR(output, 0,output) simply to avoid the ISERROR function. There may be different perspectives here so I would be interested in your views on this, John?

Join the discussion

Popup

Register your Interest

Please register to keep up to date with our developments.

Register your Interest

Please register to keep up to date with our developments.

Сейчас акция, искусственные елки http://xmas.kiev.ua дешевле, чем в декабре.