Advanced financial modelling course extract: circular references

Circular references arise in a financial model when a first cell feeds into the result of a second cell, but the first cell also depends on the result of the second.  Both cells depend on each other.  A circular reference results.

Manufacturing a circular reference in a financial model

It's easy to create circular references by mistake in a model.  In case you haven't worked with circular references before, here is a very simple exercise that sees you create a circular reference.  Open a new spreadsheet.  In say A2 type "=A3".  Now in cell A3 type "=A2".  Ordinarily an error message will pop up in Excel.  If you wish, you can download this Excel spreadsheet: advanced financial modelling course circular references, which contains an example of a circular reference.

Dealing with circular reference error messages

You can click on "OK" to make the circular reference error go away, but when you've created a circular reference by mistake what you should also do is click "undo" in your spreadsheet.  If you have created the circular reference by accident, most likely your financial model will stop delivering the results you expect it should.  You need to go back a step and find a different way of solving the problem.

Circular references in financial modelling

Circular references can often arise around debt and cash balances in financial modelling.  For example, cash flow in the model is going to to depend on interest income on cash balances.  But cash balances are going to depend on cash flow for the year.  Hence a circular reference.  To break the circular reference in this case, what we could do is set the model up so that interest income is a function of last year's rather than this year's cash balances.  That means interest income is going to be modelled slightly less accurately, but for many models interest income is a small component of cash flow.  If you wanted more accurate modelling what you could do is turn your annual model into a monthly model.  That would reduce the size of any error.

Advanced financial modelling: using deliberate circular references

So far the message has been: "circular references are bad".  You need to go back a step and find a way around them.  However, advanced financial modellers know that you can actually work with deliberate circular references in a model.  Using deliberate circular references in a financial model comes with a serious health warning, but first let's make sure you know how to work with a deliberate circular reference.

Financial modelling with deliberate circular references: an example

In this example, we're going to pretend that we want interest income to be a function of the average of last year's and this year's cash balances.  With this year's cash flow and cash balance a function of this year's interest income, and with interest income a function of this year's cash balance, a circular reference is going to result.  Previously we suggested breaking the circular reference by making interest income a function of last year's cash balance, but it results in slightly less accurate modelling of interest income.  Now we've decided that it's important that we model interest income more accurately.  As a trade off, we're going to create and live with a deliberate circular reference.

Financial modelling with iteration "on"

This Excel spreadsheet: advanced financial modelling course circular references, contains the example above.  To get the financial model working with a deliberate circular reference, you need to turn iteration "on".  In Excel 2003 go "Tools", "Options", "Calculation" and turn Iteration on by marking the check box next to "Iteration".  In Excel 2007 go the Office button (top LHS) "Excel Options", "Formulas" and tick "Enable iterative calculation".  In Excel 2010 you can achieve the same thing from the "File" menu: "Options", "Formulas", and tick "Enable iterative calculation".

Some thoughts about iteration

With the deliberate circular reference, and iteration on, the model's results are no longer garbage.  You can check the results manually if you like (e.g. on a calculator or in another spreadsheet).  The value for interest income really is the average of the opening and closing cash balances.  But cash balances are a function of interest income and the circular reference remains.  So what's happening?  Iteration is like a turbo-charged version of Excel's "Goal Seek" function.  What iteration is doing is very quickly trying lots of different values in the model, and finding something that works.

Iteration - the health warning for financial modelling

We're not necessarily expecting you to agree with us, but our advice is not to use iteration.  Our advice is not to work with deliberate circular references in your model (although we know the advanced financial modellers are not going to listen to us).  What's the problem with deliberate circular references?  The problem occurs when you have a deliberate circular reference in your model (e.g. around debt or cash balances) and you've turned iteration on, and then you create a second circular reference in your model by accident without realising it.  With iteration on, no circular reference warning will pop up.  You could go on modelling for hours before you realise that you've created that second circular reference by accident and your model is spitting out garbage (that's the voice of bitter experience).  For that reason, our advice is to remove circular references from your model (but we know the advanced financial modellers out there aren't going to listen to us!).

Deliberate circular references + accidental circular reference: tracking the error in a financial model

If you are working in a financial model that has a deliberate circular reference, and you eventually work out that there may be another accidental circular reference in your model, here's how to track it.  Save the financial model as a new version.  Find the place in the model where you have the deliberate circular references.  Delete those out of the model completely.  Turn iteration off.  Close down all spreadsheets.  Open up the new spreadsheet without the deliberate circular references.  With iteration turned off, if there's an accidental circular reference in your financial model, Excel's circular reference warning should now alert you to its presence.

Advanced financial modelling course tips: circular references

You can download some accompanying notes here: advanced financial modelling course: circular references.  But here are our advanced course tips for working with circular references in a financial model:

• Don't do it!  Undo any circular references in a model.  Find another way around the problem.  If you leave them there it's a recipe for unhappiness (see the financial modelling health warning above)
• If you do decide to work with a deliberate circular reference, turning iteration on will help your model deliver results.  To be safe what you should really do in a structured model build up, is add that deliberate circular reference in and turn iteration on at the very last step of your modelling.  If you can do most of your modelling without the circular reference, and without iteration on, then Excel's circular reference warning will help you should you create a circular reference by mistake.

Return to the advanced financial modelling training course extract

More advanced modelling training course material is available.  If you would like more information regarding our taught program, please see the full outline for our advanced modelling course.