In the base case recap tab, we now need to include the effects of the recapitalization in the debt servicing. Here we've got our senior debt balance. It's got its normal issuance or repayment, that's the old debt being repaid here, and then the ending senior debt balance. But we want to include the effect of the recapitalization here in row 67. So what we're gonna do is we're gonna go and take two figures from the recap tab. Let's go have a look. So we go to the recap tab. I'm gonna scroll down and the first thing I want to take is I want to take in the new debt that's being added, the 1531.4, but remember we're in column G, so I'll just take that 0. However, I also want to include the fact that the old debt will now be paid off. So I need to go down to row 37. And here we've got the senior debt repayment. We can see that by the time we get to column I the third year, we'll have about an extra 1,000 of debts coming in. Now I want to put brackets around that, and I only want this to happen if the recap switch is turned on. So I'm going to multiply that by the recap switch press enter. A 0 comes through at the moment, but if I copy this through to the third year, fantastic. There's that extra 1,000 just over a 1,000 that's come through. I want to do exactly the same thing for the unsecured notes underneath. So I'll press equals, go to the recap tab, find the unsecured notes, recap minus the unsecured old notes repayments, and multiply that by the recap. So it's the same thing again. Copy it through to year three. And there we've got the extra amount, extra 93.7 come through. Great. So the extra thing we need to change now is the interest expense. And this is one of the more trickier items. What we need is we need an old interest rates if we are looking at the years prior to the recapitalization. And I need a new interest rate if we are looking at the years after. Okay, so we're gonna have to use an IF function here to decide if we're prior to the recap or after the recap. But we'll also have to include the fact that whether we've got the recap switch turned on or off. So we're gonna need an IF, but we're gonna have to have two things we're testing there. So we'll need an IF and so I'm gonna go into this current formula. This current formula is taking the average of the debts there multiplied by the old interest rates. We'll come back to that old interest rate in F16 second. But I'm gonna start off with IF open bracket and, and I now need to put my two tests in. My first test is gonna ask me are we after the recap year? So let's go up to our year row 22, and I'm gonna say, is G22 is that after the recap year? And again, we've got that already named, but comma, I now want to include the fact whether we've got the recap switch turned on or not. So I'm gonna type in recap switch equals 1 close brackets. So if we've got both of those things happening, what do I want? Well, I want the new interest rate to come through. That new interest rate is on the recap tab. It's up near the top. And because we're looking at senior debt, it's that 6% there and I'm going to lock onto that comma. So if both of our tests occur, we're going to go with our new interest rates. Otherwise we'll go with the old interest rate, which we had an F16 close bracket and then multiply that by their debt figure. Great. Now that doesn't make any difference in year one or two. I go to year three. It makes no difference either. Let me copy this to the right. But once we get past that recap here, watch that 41.9 as I copy to the right we go up, we use that higher interest rates. Great. Let's copy all of this to the rights and then we're going to need to do exactly the same thing for our unsecured notes interest rate down here. So remember, two things I want to hit equals IF and is the year that we're in in row 22, is that greater than the recap year? And is the recap switch turned on? So if both of those things happen, we want the new interest rate that's on the recap tab. It's 9% gonna lock onto that.

Otherwise I'll go the old interest rate in F17 on the other tab and multiply that by the average of the debt balances. Great press enter again, the first three years. It makes no difference as I copy to the right, but as I now go to year four, that 45.9 changes up because we're now using a higher interest rate. Again, I'm going to copy all of that to the right.

So our debt figures have now updated. The interest figures have now updated as well. We just now need to work out our ending cash. Now the ending cash figure, we've already got the beginnings of this started. We've got cashflow available for debt repayments. You then pay off some debt. But then what then happens is we're going to add in the dividend recapitalization here. So let's go find that. Press equals. Let's go to the recap tab.

I'm gonna scroll down, find that recap funds available for dividend distribution. There it is. That's that 0.

I just need to make sure that I multiply that by the recap switch so it only comes through if the recap switch is on. I'll just need to make sure that that's a negative. So we see as cash going out As a dividend being paid, and as I copy it into year three there we see that money going out. Great. But this is weird. We've got ending cash now of 49.4. Where's that come from? Well, what's happening is that we're paying out a dividend of 1,063, but we actually had more cash than that available at the moment. This says that we've got some cash left over, but it's the fees. The fees we need to pay them out. We had them going out to the income statements and that did affect the cash available for debt repayment. But now that we've put this dividend recap in, we need to include the fees a second time. So let's just link up to the income statements and we'll link to these fees here. Now, at the moment, there's 0 because the circular isn't turned on. We'll turn it on right now. So I'm going to go to file options, enable iterative calculations.

I'll then go to the info tab, turn that circular switch on, and there we go. The fees have come through. My ending cash goes down to 0. So this shows that we are using all of our cash to either pay out as a dividend or pay off the old debt or pay the fees. Before we finish, I'm gonna make sure that I turn those iterations off and I turn the switch off as well.