Creating a Revenue Model
February 12, 2025
Arbitrage was earlier restricted only to commodities that could be traded easily in the markets. It is a phenomenon born out of the financial markets. However, the recent developments in the world have created a situation wherein arbitrage has become possible even in the labor markets and this arbitrage is directly influenced by the Forex […]
Introduction A financial intermediary is a firm or an institution that acts an intermediary between a provider of service and the consumer. It is the institution or individual that is in between two or more parties in a financial context. In theoretical terms, a financial intermediary channels savings into investments. Financial intermediaries exist for profit […]
Infrastructure projects are most needed in developing nations. These are the countries where infrastructure projects are able to create the most growth. This is because the spillover effects of infrastructure projects are felt significantly in emerging markets. Ideally, emerging markets should create policies that attract more and more foreign investment on to their shores. However, […]
Ratio analysis, without a doubt, is amongst the most powerful tools of financial analysis. Any investor, who wants to be more efficient at their job, must devote more time towards understanding ratios and ratio analysis. However, this does not mean that it is free of limitations. Like all techniques, financial ratios have their limitations too. […]
There are many asset classes that are available to the common man. The average investor can put their money in stocks, bonds, real estate, gold, mutual funds, etc. However, there are certain asset classes which are not available to every individual. One of the asset classes are “unlisted companies” or private equity. These asset classes […]
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.
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.
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.
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.
Your email address will not be published. Required fields are marked *