What is a circular reference?
A circular reference is when a cell refers to itself directly or indirectly.
Are circular references bad?
In most cases, a circular reference should and can be avoided with some planning. However, in a complex financial, I found it easier to just use circular references in certain areas.
Circular References in Financial Models
Circular references are used to help calculate cash balances. Let’s walk through two typical cases.
The cash sitting in the bank generates interest. The interest income is taxed and lowers the net income. More cash -> more interest -> more tax -> lowers net income -> effects cash.
See the example below. To determine the amount of interst, we use an average of the forecasted beginning and ending cash balances. It’s not fair to use just the beginning or the ending cash balances to calculate interest because over the time period that balance will change. [Read more…]