Revolver
- 05:52
LBO modeling revolver
Glossary
Cash Sweep Debt LBO LBO modeling Private Equity RevolverTranscript
We'll now model the revolver. We have the cash flow that is available for the revolver and we need to see if we have a need for an additional revolver or if we're gonna have excess cash, in which case, we can then apply it to the subsequent tranches of debt. So the first thing we'll do is we'll take our ending revolver balance. And this is going to be coming again from the jumping off balance sheet point, which is the combo or proforma balance sheet. So I will take my revolver balance from here, which is column I of the balance sheet page I17. And that becomes my beginning balance in terms of whether I need to issue revolver or whether I can pay down existing revolver or whether I will have cash to go beyond the revolver and apply it to other tranches of debt. That will simply be a min formula based on what we owe and what we have. So it's simply going to be equals minus min because I want it to be a negative. If it's in fact showing me that I need to repay the revolver. So first of all, I have to see whether I have positive cash or negative cash. If I have positive cash, then I will not need to borrow additional revolver funds. I will either pay down the revolver or I will, if I don't have any revolver, I will take that cash and apply it to other tranches of debt, which is the case here. If I have negative cash available for the revolver, then in that case what I would be doing is I would be issuing funds via the revolver to, to fund that gap. So we'll just put the formulas in and then we'll test to make sure that we've got this covered. So in this situation, I'm gonna use a minus min formula. It's gonna look at my beginning balance and compare it to my cash available. So what it's basically saying is that I don't have a beginning balance, therefore do not apply any repayment to this amount. So if I wanted to test this, for example, let's just say that I wanted to make this negative 100. What would happen then is this formula would read this and see that I needed cash, I had a funding shortfall, and it would issue revolver funds to fill that shortfall. If for example, I had, let's say a revolver balance of 200, what would happen here is it would take that one 18 and it would apply as much of that as it could to pay down this beginning revolver balance. If I only had a 75 revolver balance, then it would only apply up to that amount. So that's what this min formula here is doing. Now I always wanna make sure I undo my dummy assumption so that I don't run into the problem of hard coding over my model, in which case it would be totally and completely useless. So now what I want to do is calculate my interest expense on the revolver and the interest expense on the revolver has to be tied to whether or not I'm in the capital structure that Requires the revolver. So that would be, in this case, structure one, the standard and structure three, the sale lease back. So the way we've set this up, if we go back to the LBO page, if we look down at our calculations of the interest rate on these components, what we have here is these interest rates are reading that switch. And if the switch is indicating that it wants that particular debt product, it shows us a rate. If our switch is indicating that we don't need that particular debt product, for example in the unitranche, we don't need the revolver first or second lien or getting the N/A. So we could go back and link to the toggle again. But I think what we'll do here is we'll just put a formula in that basically just reads that, you know that formula and it basically says that, look, if there's a rate there, let's calculate interest because we've already done that work. If there's a rate showing, that means I need that product, calculate the interest. If there's an N/A showing, put a 0 here. And we want to do that because we don't want N/As showing on this page because they will get uh, into our totals and our sum totals and they'll show up as values. So we don't wanna do that. So I'm gonna say equals if my revolver interest rate on the LBO page, which is right here equals and N/A, then put a 0 here. If not, show me that actual cell. And I'm gonna anchor this because we're gonna copy this to the right. So technically actually I need to anchor both of them. So I'm just gonna go into the first part of the formula and anchor that as well. Once I anchor that first cell, I now need to take the interest rate and apply it to the average of my beginning and my ending balance and that will create an interest calculation. So if I just change this to 100, what's going to happen here is I'm not getting any interest calculation because I'm in currently the unitranche structure. So let me undo that. Let me go over to my LBO page. Let me get outta unitranche. Let me go back to my debt page. Let me go ahead and type in a beginning revolver balance of 200 which flows up to the top and we calculate interest on the average of 8. So that seems to be working. And then once again, if I were to actually go back to the assumption and turn that off, we know for a fact that it would not calculate interest here. So I'll go back to debt and now it's zeroing it out. So that's good. So let me just undo that twice to get back to where we want to be. And we've completed the revolver calculation.