The purpose of this video (and the one that follows) is to help avoid extreme frustration as you learn to build models. This video focuses on circular reference errors, which can cause a model to “ref out.” The saying is used because Excel will display “#REF!” for invalid cell references. If an error occurs Excel might also display #DIV/0! or #VALUE! depending on the kind of error.
To demonstrate what happens when your model “refs out” on account of a circular reference error the video starts out by creating one in the model:
As a first-time modeler this can be pretty terrifying, but it is so common that it is actually used to play pranks on analysts (or I just had evil superiors…). Fortunately it is also easy to fix.
Fixing the error requires two steps. First you must undo what caused the error, and in this example the error was caused by deleting revenue in period 20X2.
You will notice that replacing revenue is an improvement, but your model still shows multiple errors. Step two is to delete the source of the circularity, which in this case is interest expense.
With interest expense deleted you have fixed the error and the model will calculate properly. At that point you can link the model back to interest expense and keep working.
Having fixed the error the video explores the cause of the circular reference error. In other words, what happens in the model that causes so many cells to display an error message?
Lets take a look at the full model on the following page for an explanation.
The circular reference is created as follows:
- Interest Expense is used to calculate Net Income
- Net Income is used to calculate Cash Flow
- Cash Flow is used to calculate the Revolver / Line of Credit
- Revolver / Line of Credit impacts the amount of Interest Expense: More (or less) debt requires greater (or smaller) interest payments
As you can see the calculation creates a circular reference (or in this case an oddly shaped loop).
The video demonstrates how this occurs in a couple models to provide more visuals.
The video then demonstrates how to Enable Iterative Calculation in Excel. Without this functionality enabled, Excel will not properly calculate any circular references.
To turn on iterative calculation select the File menu > Options > Formulas and check the box for “Enable iterative calculation” (circled red below).
The video concludes by offering a more elegant solution to fix circular reference errors. Rather than delete and relink interest expense every time your model refs out, you can use a formula to work around it.
The “=IF” formula checks whether a condition is met, and then returns one value if TRUE, and another value if FALSE (definition of =IF formula taken from Excel).
Condition: B21 = “ON”
Value if TRUE: Links to Interest Expense (cell E128)
Value if FALSE: 0
In this way, you can input “OFF” in cell B21while you are working on the model, and work without creating circular reference errors. Once you’re done with the model input “ON” to run interest expense through the model.