Circular references clarified

[wstguest]

Full Question:
My model calculated, but I had a question about the final (key) point surrounding the iterations. I understand that we entered a choose function which will alternate using the average cash balances (2) or the beginning value (1). I also understand how to set up and run the iterations – my questions were: 1) how the iterations make the model equate such that the diffs are $0. Is it changing anything besides the cash value (I know the choose function was directed towards the type of cash bal to use but I wanted to confirm that nothing else was being modified when going through the iterations 2) when to use the beginning (1) verse the average (2) cash balances. I thought I heard you say that it should be set to the beginning balance initially and than turned to 2 such that it uses the avg balance for the iterations and than turned back to 1 once the iterations are complete and there are no differences remaining in the model…

[wstexpert]

Your question can be summarized as follows:
What is a circular reference, how do you avoid circular references in a financial model and once you have a circular reference, what does the Iterations option in Excel do?

The logic runs as follows:
In general, there is NO good reason to have a circular reference ("circ")- a logic that relies on itself to calcuate itself. The only legitimate reason to have a circ is in the case of getting a better, more precise financial model based on average debt and cash balances (see our FREE RESOURCES section and click on Circular Reference for more information).

I need to note that for certain complex calculations and situations, there are a handful of reasons why a circ is required. However, aside from those very limited reasons (certain return calculations, certain share repurchase estimates, self-referencing cells for souped up sensitivity analyses, etc), circs should be avoided at almost all costs.

Incorporating a circ for the purposes of calculating average balance is fine; however, once you do so, if a new circ is created (again, circs are bad and should be avoided), you will never tell that the new, bad, circ is there. Hence, my strong recommendation to build a simple choose statement switch that toggles between Beginning Balance (no circ since based off last year's figures) and Average Balance (yes circs, since requires this year's figure to calculate this year's figure). You should update and modify the financial model under the Beginning Balance scenario - this way, if a circ is accidentally created, you will either be warned by Excel or it will show in the Status Bar, indicating you have a circ. Unless you are on Average Balance, you should not have a circ!

Then, when you are ready to finalize your model (or updates finished), then switch the toggle to Average Balance for a more precise calculation. Whenever you toggle back to Beginning Balance, no circs should exist. If there is still a circ, you have a "Fatal Error" in Excel.

Whenever you have a legitimate circular reference, you need to tell Excel what to do - that is, turn on the Iterations option so Excel can calculate the numbers for you until there is no change in any number on the model. For more information on what Iterations in Excel does, see our FREE RESOURCES section and click on Excel Iterations Logic.