Modeling Case Study - Circular Reference in the Model
- 04:38
How to create a circularity in a financial model using a circuit breaker switch and an iterative calculation.
Transcript
Now in the model when we create a circularity, we always want to link it to a circuit breaker switch. And it's either at the very top of the model or sometimes it's on another sheet. And in this model we have it on the info sheet. So if I go to the info sheet, you can see that we have a circular switch that's been set up in this cell here. It doesn't need to be set up or named a circ switch here. It can just be a 1 or 0 in a cell. It doesn't matter. It just needs to be a central place where somebody can turn on or off the circularity. So then I'm gonna go back to the model and I'm gonna come down to my income statement to where we have interest income. And I'm going to use an if statement if, and I'm gonna go back to my switch. If the switch absolute reference it, in fact, I don't need to absolute reference it because it's been named. You don't need to use the name switch. If that equals 1, then we want to pull in the interest calculation. So I'm gonna go to my model and I'm gonna go down to the interest income. And if it's 1, pull it in, if not 0. And the - will allow us to break the circularity. Then I'm gonna do exactly the same thing with interest expense. And I as well just do control D because I've got most of the formula there. But what I'm going to do here is instead of referencing interest income, I'm then gonna go down and reference the two interest expense lines, the interest on the revolver, plus the interest on the long-term debt. And that will pull in the interest expense. Now you can see at the moment we have no circularity. And the reason we have no circularity is that our circuit breaker is turned on. When I go to my circuit breaker, I'll hit one in the cell. I get the error message. I like to do it this way 'cause I want to be certain that my interest calculation is truly generating circularity. So then what I'm gonna do is click on, okay, and when you click on, okay, I have a circular in this model. And you can see on the status bar on the bottom left, it says circular references G120. It has arrows in the cell and in the cells that I have the interest lines, it's showing 0. So I need to do alt FT. Then I need to go down to the formulas, activate the circularity, and the model calculates. The big problem is that if you create an error in the model, let's say in my cost of good sold, I type some letters, obviously Excel is going to see that as a letter or text where it's expecting a value. So we'll get a value error and That runs right through the whole circular calculation. And if I do control Z to undo, should it be okay? No, that error has stayed there. And the reason for that is it's kind of got locked in Excel's brain. It's like Excel taking a big bite of ice cream and having brain freeze. The only way we can fix it is by going to our circuit breaker typing 0 and then typing 1 and hitting enter. And that will break that error. And you can see the model now works. Now we've done the iterative calculation. The next step is to carefully copy right. So I'm gonna come down and I'm going to select my base calculations and I can select my income statement. Just be careful not to copy across any hard numbers. So I suggest you do this really steadily and carefully and I'm gonna go it all the way down to the interest lines and the calculations are already done for us. And I'm gonna copy that to the end of the forecast just to control R. And you can see that that now is complete. I would always, I eyeball the trend in the numbers just to make sure that it seems a reasonable trend in the forecast model. But this is our Red Bull forecast complete. Do a control home to do a beauty save. And then what I'm also going to do is I'm gonna go to the info sheet and turn the circular switch off as I've got additional elements of the model to complete.