Circular references are very common in financial models. Here's an example with interest expense. Interest expense makes your net income go down, but net income going down, means your net cash flow goes down on your cash flow statements. Net cash flow going down can make your revolver go up, or your short-term borrowings go up or your cash go down. If my revolver or short-term borrows have gone up, that means my average revolver or average short-term borrowings has gone up as well. Alternatively, your average cash might have gone down. If my average revolver has gone up, that means my interest expense will go up as well, which again makes my net income go down. That's the real world. That's how things work. So we do need circulars to be in our models, but they can cause trouble. They can lead to your model being unstable and it can blow up. Things that you may see are error messages such as #REF, #VALUE, and #NAME, where you should have perfectly reasonable numbers. What's happening here is that it's your circular formula and something's gone wrong in the circular. A good example is interest in the income statements, but someone has accidentally typed some text into the cell which should have a number in it. That text is now flying around your circular, and all of the circular cells are saying, what's going on here? There shouldn't be text here. There should be a number and so you get error messages. Even when you delete out the text, that error message itself is now creating text and it's going around, around, around the circular, so what can we do? Well, firstly, you need to turn the circular formula into a linear one, momentarily. What does this mean? Well, the easiest way to do this is just delete interest from the income statement. There's now no circular in your model. All those error messages, pop outs. There's now no text in your model in those cells, which should have numbers. You can then put the circular back in by putting the interest back in, but your best practice is to use a circular switch, so you don't need to select interest and delete it and then undo the deletion. Instead, you have interest being toggled on and off by a switch. The switch will have a zero or a one in it. When it's a zero, it's off and no interest is in the income statements. When the switch is a one or on, interest does go in the income statements. So imagine you've now got that error again. You've accidentally put some text into your interest. Oops, all you need to do is delete the text and then turn the interest switch to a zero, the circular disappears. Turn the interest switch back to a one, and you have the circular on, interest is flowing and no errors. So how do we actually put interest into our models income statements? Well, the first thing we have to do is remember to have iterative calculations set two off. We're then going to add the interest, income or expense as the last step in the model build. That's where we are now, and as we put the interest, income or expense into the income statement, an error message will appear. It says there are one or more circular references, et cetera, et cetera and it feels very dramatic, but that's okay. What we've just done is we've said to Excel "Do not allow iterative calculations," but by putting interest into the income statement, we create a circular. So Excel is in a bit of a bind at the moment and we'll let it out of that bind in just a second. So now that I've got interest in the income statement, the model will return 0.0 instead of the interest amount. This is because Excel is saying "I am not going to do an iterative calculation. You told me not to do it." So it is not doing the circular, it freezes all of the numbers and it won't update them, and that's okay. That's exact how Excel's meant to behave right now.

Also, a circular error message will appear in the status bar, often in the bottom left hand corner of your screen. It'll say "circular references" or "circular," or on occasion it'll say the word "calculate," if you didn't have your iterative calculation set to off. What we want to do now is allow interest to go around the circular, get Excel allowing this to happen. So you turn the iterative calculation on to solve this. So you go into your File, Options, then the Formulas tab and you put a tick next to enable iterative calculation. If you're on a Mac instead, you'll go to Excel preferences and tick the box there. All of your numbers should start flowing through. The only thing you may have done in addition to this is when you put interest, income, and expense in your income statement, you may have used a switch and if you've used a circular switch, you just need to turn the interest on in that switch. In lots of our models, we have a cell that we name and we name it Switch or Circ switch. We'll just need to turn that to a one and that will now return interest in the income statements. If it was off, it would just return a zero.