Financial Modeling

Home

Forum

Financial Modeling Topics

Financial Modeling
- Financial Modeling

Ask your modeling questions here! Feel free to copy/paste specific sections from filings as appropriate.

Questions/Discussions

Sort by Date ▼ / Top Rated

Circular references clarified

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... Read More

Show All 1 Replies Login to Reply

by Guest 1.

added 11 years ago

RE: Circular references clarified

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 circu... 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. Read More

by WST Expert 1.

added 11 years ago

Show More Show Less