MSG Team's other articles

11856 What are Demergers: Its Pros and Cons

Mergers and acquisitions are often used by conglomerates to create value. However, in some cases, demergers have also been effectively used. While the workings of mergers and acquisitions are well known to many people, demerger is still considered somewhat of a mystery. In this article, we will have a closer look at what a demerger […]

11338 The Socialization of Losses

Joseph Stiglitz is one of the most renowned economists of our time. He once criticized the current economic system after the great recession of 2008. He said that the current system is skewed in favor of the rich. This economic system is no longer capitalism. It is a strange combination of capitalism and socialism wherein […]

11553 Three Layers of Banking as a Service

The concept of banking as a service is revolutionary. This is because banking as a service is a mechanism using which commercial banks can now collaborate with other non-banking entities in order to expand the coverage of their services. Hence, this model has led to a decomposition of the commercial banking value chain. Earlier, all […]

9525 Hedge Funds vs. Mutual Funds

Hedge funds are both exclusive as well as elusive. They differ from other funds like mutual funds in a large number of ways. In this article, we have listed down the differences between a hedge fund and a mutual fund. Availability Funds like mutual funds are available to every investor. There is no regulation that […]

10350 Mergers and Acquisitions: A New Perspective

The nature of mergers and acquisition activity has drastically changed over the past few years. Earlier, most of the mergers and acquisitions happened because companies wanted to do horizontal or vertical integration. They either wanted to team up with their competitors and enjoy economies of scale, or they wanted to team up with their suppliers […]

Search with tags

  • No tags available.

A lot of financial modeling takes place in Microsoft Excel. One of the errors that financial modelers come across during the financial modeling process is called the “Circular Reference” error. This error can affect many values in any model. To an untrained financial modeler, this could be the source of a lot of panic. However, fortunately, every experienced financial modeler has navigated this error. Hence, they know that even though the effects of the circular model error are widespread, the error is relatively easy to manage.

In this article, we will have a closer look at what circular reference error is and how it can be managed to ensure minimal impact on the model.

What is the Circular Reference Error?

In simple words, circular references mean when the output of an equation is also a part of the input. In excel terms, this means that the formula in a cell points to itself, either directly or indirectly. Financial modelers never really directly link the input cell to the output cell. Generally, the input cell is linked to the output cell through a chain of complex formulas. This chain is difficult to recognize. In many cases, the error may be because of incorrect modeling. However, in many cases, the circular reference would actually be appropriate and an important part of the model.

Let us use an example to understand how circular references can inadvertently get built in a financial model. It is a known fact that interest expense is a part of the profit and loss statement. Hence, interest expense is used to calculate the net profit after tax.

However, this net profit after tax is then used as an input to the cash flow statement. This cash flow statement then projects the amount of cash that the company is expected to have on hand. The cash balance then helps determine the amount of loan or overdraft that a company would have to take to meet its working capital requirements. Now, the amount of loan taken is the determining factor in the amount of interest expense that will be incurred.

It is clear from the above example that the interest expense in the profit and loss statement ends up being an input for the interest expense in the cash flow statement. In such cases, MS Excel will throw a circular reference error. Many circular references like interest expense follow well known and well-documented patterns. However, there are many others which are very difficult to trace. Financial modelers end up spending a lot of time trying to understand the root of the problem and whether the circular reference is valid.

How is Circular Reference Error Managed?

In finance, many calculations are indeed circular. The Microsoft Excel tool has been built to recognize this issue. Hence, once the financial modelers recognize the source of the circular reference and consider it to be valid, he/she can easily correct the error. The two most common ways of correcting the error have been listed below.

  • Microsoft Excel has created a functionality called Iterative Calculation. If this functionality is enabled, the system no longer throws the circular reference calculation. This functionality can be activated by following the path mentioned below:

  • Menu--->Options--->Formulas--->Enable Iterative Calculations

  • Even after the circular reference is activated, the model throws an error every time a downstream calculation takes place. Financial modelers often use the “IF” condition formula to avoid these errors. This means that they have a cell where they pass the values “ON” or “OFF” to the model. The “IF” formula is designed to forward the actual value if the model is set to ON. However, if the model is set to OFF, the value zero is passed on. This is done to avoid too many errors during the building phase of the model.

  • Circular reference errors can be incredibly complex. For instance, there is a possibility that an erroneous value may be passed into the cell as an input. To prevent this, Microsoft Excel has created a functionality called Jumpstart. Jumpstart is a two-part formula. It checks the correctness of the value before passing it into the model.

The bottom line is that a financial modeler needs to have the skill required to understand which circular references are a part of the model and which have come in due to calculation errors. Once the correct circular references have been identified, the modeler must use standard Excel solutions to get rid of the problem. There are many solutions which can be found by conducting a google search. However, many of these solutions cause a problem with other calculations down the road. Hence, it is better only to implement methods which have been tried and tested.

Article Written by

MSG Team

An insightful writer passionate about sharing expertise, trends, and tips, dedicated to inspiring and informing readers through engaging and thoughtful content.

Leave a reply

Your email address will not be published. Required fields are marked *

Related Articles

Creating a Revenue Model

MSG Team

What is Cost Modelling?

MSG Team

Merger Modelling: The Accretion/Dilution Analysis

MSG Team