Excel - Circular References

In this tutorial, you'll learn how and why circular references come up in Excel and what to do about them when you see them. By http://breakingintowallstreet.com/ "Financial Modeling Training And Career Resources For Aspiring Investment Bankers"
What are circular references?

They crop up when a cell's INPUT depends on a cell's OUTPUT - meaning that Excel runs in an endless loop!

Most common example: Interest expense on debt.

Question: How do you calculate interest expense, anyway?

Should you use the beginning balance each year, the ending balance, or the average balance?

The beginning balance is OK... but you have a problem if you use the
ending or average balance.

PROBLEM: Then, the interest expense depends on how much debt is
repaid in a given year...

...but the amount of debt repaid in a given year also depends on
the interest expense!

So Excel doesn't know what to do and can never calculate the
number.

Why bother calculating interest this way?

Mostly to be more accurate - better to use the average debt balance over the course of the year because that's closer to what the company actually pays.

How do you get around this calculation problem?

Easiest solution: Just check "Enable Iterative Calculations" under the Options menu (Formulas) (Alt + T + O on PC or CMD + , on Mac)

Better Solution: Build in the option to use the average debt balance or the beginning debt balance.

Some groups / firms / industries won't even accept financial models that include circular references - so if you do it this way, you can remove circular references more easily later on.

To build in this option, create an input cell that only allows a 1 or 0.

Then, in the interest expense formulas, use the average debt balance if that input cell is set to "1" and use the beginning debt balance if it's set to "0" and circular references are therefore disabled.

You can check this by looking for the "Calculate" label in the bottom-left window of Excel. It should be displayed if circular references are enabled, but it should NOT be there if circular references are disabled.

http://www.mergersandinquisitions.com/