In calculating cash available for the cash sweep we firstly have to calculate our debt and our leverage levels. If we can calculate them, then according to those leverage levels we'll calculate the percentage of cash that goes into the sweep. So we need to start off with our total net debt at the beginning of the period and then we'll make some changes to that. I'm at the end of the three month period ending 31st, December '18. I need to find the net debt at the beginning of that period and I can find that on the Deal_Date tab. I go to the Deal_Date tab and on the balance sheets I scroll down until I find the debt items, and here they are. Now here's the three month period but that's at the end of that three month period, I want the beginning of that period so I need to take the previous column. So I'm going to sum up historical long-term debt all the way down to the mezzanine debt. But we also had a revolver, so if I scroll up, I find the revolver, and then lastly, I'm going to subtract off the cash and that will get me the net debt, getting me to 1,290.

Now we do need to be careful because I can't just copy that cell to the right. We've colored it gray to try and signify that. Instead, in J93, I need to do basically the same thing but I need to do it now on the Deal_Model tab. So again, I go down to their balance sheets, find their long-term debt, and because I'm doing this for the year end, December '19 but I want the beginning net debt for that period, I need to take the previous column. So sum up all of those items all the way down to the mezzanine, add on the revolver, and then subtract out the cash. (mouse clicking) Cash at the moment is showing as a negative, that will change later on.

Great, so I now need to add in any mandatory repayments. Luckily they're already done. They're up in route 52, so if we scroll up, there we find them, they're gonna be shown as a negative, so I can just link straight to that figure.

That will then take beginning net debt, subtract the mandatories to get my total net debt at the end of the period, but before we do the sweep, thus, it says excluding sweep. Now I need the period EBITDA. And again, we can see we've got this gray cell here. So this is going to be a one off cell. I need to go to that Deal_Date tab again, and I go up to the income statements and here it is. Now I do want my EBITDA to the end of the period here so it's 56.1, but we're gonna be calculating leverage ratio. That means I need the debt over 12 months of EBITDA. Here, I've only got three months. So to gross it up, I'm going to divide it by the post deal percentage. Only 25% of the year, three months, was post deal, so if I divide this by 25 cents, it'll gross it up to 100%. So I get to a figure of 224.5. (mouse clicking) Now, just so we know what to do in the next column let's go to J96, let's find out the correct figure there. Here I need to go to the Deal_Model tab. And again, scroll up the income statements, find EBITDA, but I have to be careful now, I'm not going for column I, it's column J that I want. I want the full year figure up to December '19 in column J.

The mandatory repayments I can just copy them to the right and the total net debt, I can copy that to the right. Now I can calculate my total net debt divided by EBITDA, and I get ratios of 5.7 and 5. So what does that do to our cash sweep? Well, I can see that because that leverage ratio is so high it's above this 3.5 times, I'm gonna have to use at least 50% of my cash in the sweep, ie, accelerated repayments of debt. In order to get those percentages I'm going to use a couple of If formulas. So we'll start at the top. I want to say if our leverage ratio is greater than the 3.5 and I'm going to lock that 3.5, then give me the 50%. And again, I'm gonna lock that as well. But if that isn't the case then just give me a big fat 0. We do something similar, just ever so slightly more complicated in the cell below. I want to say if our 5.7 is greater than or equal to 2.5 times, and I'm gonna lock that, but that it's also less than 3.5, lock that, then give me 25%, lock that as well. Otherwise, give me a hard code of 0. Great, in this case I've got a 0. Let's just check. If I were to put in a figure of 2.6, fantastic, that's worked. The final one, very similar again, I want to say, if row 97 is less than 2.5, lock that, then give me the 20%, lock that again, or otherwise gimme a 0. So again, let's just change that. Let's change that to a 2.

And indeed that goes to 0, that goes to 0, but because we have leverage below the 2.5 we are now choosing 20% sweep. Fantastic, let's just have a look at those formulas for a second. Great, now that we know that I can work out how much I want to include of my cash in the sweep, and I'm just gonna sum up those three figures there. I need to remember to put my net debt back to what it should have been, 5.7, there we go.

So how much of our cash is not going to be used in the sweep? It's going to be our cash and the cash we had was 10.3, always sums up by 1, minus the percentage, so 5.1, and then the cash available for the sweep is going to be the 10.3 that we had minus the cash that's been excluded.