Interest and circular references.

Putting net interest expense into our income statement creates a circular reference in a financial model. Let's have a look why starting with net interest expense. Net interest expense means that your net income goes down, as we're including more expenses in our income statement. Now, net income is also the first line in our cash flow statement. So if net income has gone down that will mean that our operating cash flow and therefore, our net cash flow will go down as well. If your net cash flow has gone down, that means that your ending cash net of revolver balance will also go down. If your cash net of revolver is a negative balance, this means that your revolver balance will go up. Or if your cash net of revolver is positive, your cash balance will go down. If your revolver balance at the end of the year has gone up, this means that the average revolver balance also goes up. Or if your cash balance at the end of the year has gone down, this means that the average cash balance has also gone down. If your average revolver balance has gone up, this means that your interest expense has gone up. And if your average cash balance has gone down, this means that your interest income has gone down. Either way, this leads to an increase in your net interest expense which would then further reduce your net income. But if net income has gone down, we repeat the cycle, our cash flow goes down, and our revolver goes up, our average revolver goes up, and our interest expense goes up, and then net income goes down again, and we have a circular reference. So that's why interest expense causes a circular reference in our model. This is a legitimate circular reference. It's not a result of an error. It's simply the result of using average balances in our interest calculations. It's important to note that if your interest was based on your beginning balances for cash and debt, then we wouldn't have a circular reference in our model. When we use average cash and debt balance in our model, we create a legitimate circular reference, but we do need to be careful how we manage them as they can cause trouble for us. They can lead to your model being unstable and cause it to blow up. Although this sounds really dramatic, it effectively means that any error in our circular reference will spread throughout our model. This means that #REF, #VALUE, or #NAME error messages will appear throughout your model because the error is being spread from year to year and from line to line by the circular reference. What's happening here is that you have an error in your circular formula. Let's say, for example, someone has accidentally typed some text into the interest rate assumption instead of a number, this creates an error in the interest calculation. And then this error affects all of the line items included in your circular reference. Net income, net cash flow, revolver balances, and interest. But this also affects your ending balances, so the error will also affect the line items in the next forecast year and so on. Also, even when you delete out the text, that error message itself is now stuck in the circular formula, creating more error messages in your model. So what can we do? Well, what we need to do is turn our circular formula into a linear one momentarily. Effectively, we need something to act as a circuit breaker. The easiest way to do this, this is fix number one, is just to select and delete interest from the income statement so that there's no circular formula in your model. You would then need to fix your error and rebuild your error-free interest calculation and recreate your circular formula. However, deleting formulas from your model is not ideal. A much better way to do this is fix number two which is to use a circular switch. A circular switch is a toggle cell which allows us to remove interest from our model if we find any error messages appearing. The switch cell can have a zero or a one in it and we pop that into the IF function shown on the screen so that when the switch cell is set to zero, no interest is shown in the income statement. When the SWITCH cell is set to one, interest is shown in the income statement. So if you see any error messages, you can simply turn your switch to zero, which removes interest from the model, fix the error, and then turn the switch back to one to put interest back into your model. Fix number two allows us to fix errors without deleting any formulas. So it's considered best practice to use a circular switch when putting a circular reference into your model.