Picture the scene, it is late at night the day before an important board presentation. Your last task is to update your budget and forecast numbers. You open up your model and are met with the following warning.
Most finance and accounting professionals are aware that a circular reference warning in Excel is not a good thing, but many are not aware of the options available to avoid this problem. This is where a financial modeller can assist; we face the challenge of circularity on a regular basis and know how to build a robust solution.
So where should a CFO look for the issue in the model? Well, Excel will show you in most cases. Follow the loop of arrows and formulas and you are likely to reach the interest calculation section. Why is this?
The interest paid on debt is calculated as the interest rate multiplied by the average debt balance in the period; but the average balance includes the interest paid, hence the circularity.
There are several methods available to work around the problem of circularity, some more robust than others. Here we discuss the pros and cons of the most common solutions.
| Method | Issues |
| 1. The “Enable Iterative Calculation” function in Excel Options. | This method causes models to become unstable, goal seek does not work, data tables become very slow to calculate and errors cannot be undone. Also, if a model is shared and the next user doesn’t have iterative calculations switched on then the model results will be incorrect. |
| 2. Input fixed values in cells that cause the circular problem and use a goal seek to find the answer. | This method means that whenever you change anything in a model you must re-run a goal seek which is usually not practical. Another problem with this method is that it does not solve multiple circular reference problems. |
| 3. Use an iterative copy and paste macro to find fixed values. | This is a common method used in many models. You compute the item causing the circular refence and then copy and paste values to a fixed cell multiple times until target is reached. This method solves the problem with goal seek where multiple circular references cannot be solved, however the macro must be run whenever an assumption is changed. It also requires VBA macro writing skills and is prone to breaking if the macro is not written in a robust fashion. |
| 4. Algebraic solution. E.g. solve for an expression for interest that does not rely on closing balance. | This is an elegant way to solve circularity. It requires no iteration and calculates automatically for any changing assumptions. The problem with this method is that defining the algebraic solution can be difficult for all but the simplest circular reference. |
| 5. Apply simplified logic and accept small inaccuracies. | This is not a perfect solution; however, it can provide a result to a materially accurate level. For example, calculate interest on the opening balance only. With an annual calculation this could be materially inaccurate in periods with large movements between the opening and closing balances. However, with a monthly or daily periodicity, this method can be materially accurate. |
| 6. Break the circular formula into components to avoid the including offending variable. | This takes some extra effort but can provide the most accurate and robust approach to the circular issue. As an example, you can calculate ‘gross interest’ on the opening balance plus 50% of the loan movements excluding interest. Then calculate interest on the interest to get to the total interest for the period. This approach gets more complex when there are multiple levels of circularity, however, it is often the preferred modelling approach to the circular reference problem. |
This isn’t the type of problem that you want to be faced with in a last-minute rush. Our advice is to ensure that all key financial models and spreadsheet tools are prepared in line with best practice standards and have been developed or reviewed by professional financial modellers.
At Forecast, we strongly advise our clients to adhere to best practice standards wherever possible. It is important that the right solution to circularity is identified and implemented in the development of the model. Seeking advice from the financial modellers at Forecast will allow our team of experts to understand your requirements and identify the most appropriate way to approach circularity in your model, providing you with peace of mind that your model is robust and the forecasts in your board presentation have been calculated correctly.
Get in touch with us here to discuss your challenges or reach out to your existing Forecast contact.
