What are Circular References in Financial Modeling

What are Circular References?

Circular references occur in Excel financial models when a formula refers to its own cell directly or indirectly. This can be a deliberate formula, such as ones used to calculate interest expense, or it can be an error or miscalculation in the model.

In a 3-statement model, analysts will often forecast interest expense using a circular reference to allocate the cash throughout the company model. Interest expense is a use of cash and reduces net income, which in turn reduces net cash flow. As the model links all 3 financial statements, this can become a circular reference: lower net income can increase the revolver or short-term borrowings, which then increases the average revolver or short-term borrowings. This increase leads to a rise in interest expense, which again reduces net income, creating the circular reference. If incorrectly managed, this can become an issue for analysts.

Why You Should Avoid Circular References

Circular references are useful for some aspects of financial modelling such as forecasting cash flow and debt repayments so when used correctly can be a great analytical tool. Unwanted circular references should be avoided because they can cause issues in financial models and mean they become unworkable until fixed. The model can become unstable and circular references could potentially cause it to fail. They can also result in error messages such as #REF, #VALUE, and #NAME appearing throughout the model, where analysts should expect to have perfectly reasonable numbers. Any errors in a circular reference will spread throughout the model.

How to Find Circular References in Excel File

Circular references can be identified by error messages such as #REF, #VALUE, and #NAME that appear unexpectedly throughout the model. These errors are a result of a circular formula and indicate that something has gone wrong in the circular reference. For example, if someone has accidentally typed some text into a cell which should have a number in it, this creates an error and affects all the line items included in the circular reference.

Excel will show when a circular reference has been created.

It will also be flagged at the bottom of the Excel screen as shown here.

How is a Circular Reference Error Managed?

Circular reference errors can be managed by turning the circular formula into a linear one momentarily. This can be done by deleting the element causing the circular reference from the model, fixing the error, and then rebuilding the error-free calculation to recreate the circular formula.

Circular Switches

An alternative approach to solving circular interest is to use a circular switch. A circular switch is a toggle cell that allows the removal of the interest calculation from the model (an intentional circular reference) if any error messages appear. The switch cell usually has a “0” or “1” in it. When the switch cell is set to zero, no interest is shown in the income statement. When the switch cell is set to one, interest is shown in the income statement as the circular switch has been activated and the model will calculate interest.

If any error messages appear, the switch can be returned to zero, which removes the interest calculation from the model, allowing the error to be fixed. The switch can then be turned back to one to put interest back into the model. This method allows errors to be fixed without deleting any formulas and is considered best practice when dealing with circular references in a model.

6 Steps to Create a Circular Switch

1. 1. Identify the Formulas with Circular References: identify the formulas in your model that contain circular references. These are formulas that depend on their own output.
   2. Create a Circular Switch Cell: create a cell in your model that will act as the circular switch. This cell will control whether the circular calculation is active or not.

1. 1. Modify the Formulas: modify the formulas with circular references to include a condition based on the circular switch cell. The formula should be something like this: =IF([CircularSwitchCell]=1, [OriginalFormula], 0). This means that if the circular switch cell equals 1, the original formula will be calculated. If the circular switch cell is not 1 (for example, it’s 0), the formula will return 0.

1. 1. Control the Circularity with the Switch: you can now control the circularity in your model using the circular switch. If you set the circular switch cell to 1, the circular calculations will be active. If you set it to 0, the circular calculations will be turned off, and all cells with circular references will return 0.
   2. Check the Status Bar: always check your status bar to see if you’ve done this correctly. If you see the “Circular References” message, this means that iterations are off but there is a circular reference present. If you see the “Calculate” message, this means you have a circular reference present and iterations are switched on.

1. Turn Off Iteration Setting: when editing a model, it’s best practice to have any switches set to zero so that the output is zero and have the iteration setting switched off. This way, if you accidentally create a circularity again, you’ll get an error message straight away, so you know to fix it.

Remember, the goal of the circular switch is to handle formulas that contain deliberate circular references in your model, allowing you to control when these circular calculations are active and when they are not. This helps prevent accidental circularities and makes your model more robust and easier to manage.

Conclusion

Circular references, which occur when a formula refers to its own cell either directly or indirectly, can cause instability and potential failure in the model. However, these can be managed by using a circular switch, a toggle cell that controls whether the circular calculation is active or not. This switch allows for the temporary removal of elements causing circular references, enabling errors to be fixed without deleting any formulas. Therefore, the use of a circular switch is considered best practice in dealing with circular references, as it enhances the robustness and manageability of the model.