Hi everybody.

We'll give everyone a few more minutes to join the session. Hope you're doing well this afternoon, this morning. Depending on where you are in the world, I hope everything is well for you at the moment.

What we're gonna be doing in this session is thinking about how dividend recapitalizations work, dividend recaps, or just recapitalizations, we can go by a number of names.

Now this is all gonna work for us within a potential LBO transaction.

So we're gonna be having a look at a model where we've got an existing LBO transaction.

So we've got a target company, we've gone through the modeling and maybe the IRR looks okay, but we're thinking about whether there's any potential for improving that if we go through this process of a dividend recapitalization or a dividend recap.

To that end, we're gonna have a lot of, most of the time this afternoon is gonna be spent looking at a model.

And if you go to the portal webpage, there's a download that you're gonna need here, the additional complex LBO components Workout Empty.

This includes, other elements in addition to just a dividend recap, the add-on addition that we looked at last time out in this Felix live session.

There will be a full file made available here in the near future, but that will just get us to the same position as we will do at the end of this next hour or so.

If you have any questions, please do drop them into the chat function or if you've got restrictions on there.

The q and a session, look, the Q&A box seems to have a little bit less restrictions than some organizations.

So please do shout if you have any questions as we go through this content.

Okay, so if you got a chance to grab the let me show my screen, show you where that file is.

So if you try a chance to grab the Excel file, down here, the additional complex LBO components work out empty. That is the Excel file that we're going to be spending a lot of time looking at through the course of this session.

But please do if you have any questions, any queries, drop that into the chat function or the Q&A section and we can pick that up as we go.

So just to get started, we've got a couple of slides, one or two slides ready just to get us into the overall process.

So what we're thinking about here is, yeah, thanks Ian. Hopefully it's sharing now this screen here.

Hopefully you can see that now that we're sharing that screen, hopefully that's all good.

In terms of this, this screen button, no, no. Thanks very much. Thanks for confirming. Okay, good stuff.

So, let's get into it. If you've got that file open, we're thinking about dividend recapitalizations and the first thing we're thinking about here is we've got an LBO transaction.

So we've got a target company, we've gone through the modeling, we're gonna exit, we're think after 4, 5, 6 years, whatever it might be.

And we've got the IRR feels like it's an okay number, but we're seeing if we can do any better.

So what we're thinking about for a dividend recapitalization or a leverage recapitalization, what we're thinking about doing is at some point during the investment horizon, let's say that five year investment window, we're gonna think about potentially looking to borrow more money to pay off the existing debt levels.

Now that might be because our borrowing criteria might be a EBITDA multiple.

So maybe if we have stronger performance in terms of generating higher EBITDA than anticipated, we might be able to borrow more money, take that money and pay off our existing debts.

Alternatively, we might have only been able to borrow at a smaller debt capacity level because of uncertainty about our cash flows in the initial investment stage.

So we might be looking to borrow at a higher capitalization level at a higher multiple because we've been able to deliver better results than maybe we thought we were gonna get on the LBO date.

So these things that might have happened might have happened through the life of the project, right? Maybe also it might be the fact that we think we can borrow a interest rates might be alternative, maybe not in current interest rate environments, but that might be else something else that is, in the mix there as well.

The point is, what we're looking to do here is not just improve the returns at the exit point, but potentially borrow more money now than the existing debts, which might be lower than what we took out at the beginning of the transaction. We might have paid off some of the debts, but then we're vering borrowing up again, borrowing more money than there is in that target company on the recapitalization date and with the excess paying that out to ourselves as the private equity investors, as the private equity fund as a dividend.

So that's money coming out to us from that private equity investment before the exit date.

So that's a way of realizing capital before we get to that exit date.

So it's a potential, well it's called an exit strategy.

We're not really exiting the investment, but we are getting cash flows coming back to us earlier.

Okay? In terms of the modeling process, what we're gonna be looking at is editing the existing model.

And when we're doing that, we've gotta be really careful to make sure that we switch off the circularity, switch that circularity calculation allowing iterative calculations before we make any edits to the model just so that if we create any circular references by mistake, we get notified about it.

Okay? So what the process is gonna look like, what's the flow diagram? What we're gonna be doing? Well, what we're gonna look at in a second in the model is we're gonna be thinking about what that recapitalization recapitalization might look like.

How much debt have we got on our target recapitalization date? How much money do we think we can borrow on that recapitalization date? What interest rates might there be on that recapitalization date and what other issues might there be? Like maybe some early repayment fees, some prepayment fees, maybe there's some arrangement fees on the new debt.

So we've gotta make sure we're bringing in all of those cash flows, okay? What we're then gonna do is calculate how much we think we can borrow and assess those penalties and then build into our existing model the impact of changing the debt levels but also potentially having to change the interest rate in the model.

This does give us some complexity and we're gonna see in a a few minutes that the most complex calculation once we edit this model is about the interest rate. That's gotta deal with a few different moving parts in terms of maybe if we do the recapitalization or maybe if we don't, having to maintain the existing interest rate on the existing debt.

That's the biggest issue we've got to think about here as we're building the model, is to maintain flexibility so that we can present what the transaction looks like if we don't have a recapitalization and alternatively what it would look like if we did go down the route of having that recapitalization.

So building that flexibility in is gonna be crucial to all of our modeling.

Then gonna go through and look at all of our cash flows as usual, get the cash flows coming out to the equity investor and think about that. Thus for our IRR calculations.

So there's a bit of modeling to work through, but if you have been able to open up that additional complex LBO components workout, empty file, it should look somewhat like this.

So what we've got in this scenario is on the blue tab, the LBO initial tab, this is what the standalone LBO transaction looks like and it's for a target company that we're calling talisman here.

Totally fictitious made up target.

And we're thinking about what this might look like if we go down through this model.

I'm just gonna look at it a high level to begin with.

The very bottom you can see that this is giving us an IRR of 26.1%, so maybe not awful from a investor's perspective.

But what we're gonna think about is whether this dividend or leverage recapitalization will make things better for that equity investor.

So the baseline scenario before we get into adjusting things is that we can borrow six times EBITDA, LCM EBITDA on the way in the entry and exit valuation. Enterprise value multiples are gonna be 11 times and that debt stack is gonna be four times EBITDA as the senior debt and two times EBITDA as some unsecured notes.

That's our starting point. One other thing, we're assuming that we're gonna exit after five years.

So if we just go down to the green tabs now the recapitalization tab, what you can see within this tab is the first step of that flow diagram, which was to think about what assumptions are we gonna make with regards to the recapitalization.

And that's what we've got here on the top left hand side of the first green tab.

This is to say that we think we're gonna go through this recapitalization at year three, the end of year three.

So we'll have taken on this six times EBITDA at the initial entrance to the transaction to acquire talisman.

We're then gonna have paid some debt down over the first three years and we're then going to look to pay all that debt off and take on more debt financing. Hopefully there's gonna be some extra cash left over and that can come back to us as the private equity investor realizing some cash flows coming to us.

If you like effective exit cash flows, even if it's not real exit cash flows before we get to year five.

So we're getting some cash flows coming in early.

Okay, we're assuming there's a 2% arrangement fee on the new money. We're borrowing a 1% prepayment fee on the existing debt that we are repaying and we're also assuming that anything that's left over can be paid to the investor in the fund.

The product we fund as an early exit cash flow or if you like that dividend uh, payment, we're assuming that we're gonna borrow three and a half times and one and a half times for the CNE and unsecured notes, uh, of EBITDA respectively.

So we're only borrowing five times EBITDA on this recapitalization when we started off with six times.

But the point is that that doesn't sound like we're taking on more debt, but we'll have paid off a lot of the debt over the first three years and then we're increasing from the point at the end of year three.

We've got new interest rates on these, the instruments of six and 9% respectively.

So we're gonna do a lot of calculations in this tab, but before we get there, let's just go to the second green tab, the LBO cap tab.

So this is where we're incorporating the effects of the recapitalization into the LBO model that that LBO initialed the blue term.

So everything is the same here except for a few additional rows added to enable us to build in the effects of this recapitalization. And the first thing we've got is a recapitalization switch.

Anything that is to do with the recapitalization, we're going to multiply by this cell C6 so that we can toggle it on and off to see what the differences might be like.

So that's an important cell for us and we've named the cell as well.

So uh, up on the top left, it's not C6 but rather this is called recap switch, okay? As we go down, all of the other assumptions are exactly the same for the underlying model. Nothing different here.

I've highlighted in this sort of turquoisey blue, the new elements.

So we're gonna bring their prepayment fees into the income statements.

And then further down in our standard straightforward LBO model, uh, we're going to have a look at additional cash flows either which will include repaying the existing debt and taking on the new debt in that recapitalization row. We're doing that both for the senior debt and the unsecured notes.

And then we've got a new cash flow for the fees and the dividend recapitalization itself.

So that's the baseline of the model.

We've got a question coming in, which is a really good question, which is to say, when are we thinking about doing this recapitalization? And we're presenting it here for you as doing it on the way in.

So we've thought about the possibilities of this on the way in.

Now when you are looking at modeling any LBO transaction, you will because of the illiquidity of the private markets, want to think about, well how do we get our money back? So when we're saying we're gonna exit a year five, is it gonna be some sort of trade sale? Is it gonna be some sort of IPO, whatever our target exit route might be thinking about, we'd want to model that then.

So what we're thinking about here is would a recapitalization benefit us? So we might want to incorporate that into our decision before we actually go in to say, okay, well what does our exit work look like? Is it gonna be a single sale at year five or might we try to go through this recapitalization? And the thing we've gotta be a little bit careful with here is that this would need to be updated through the life of this investment because the conditions of debt markets, we won't know what they're like until we get to year three.

So we know what the debt market looks like today on the way into this transaction, that transaction is gonna take place in the near future, but this anticipated interest rates and the debt multiples, these are only gonna be estimated up until we get to year three.

So building this flexibility into our model gives us the possibility of saying, well, if we can get to these points at the end of year three, this is maybe what we're trying to think about.

Would it be worth doing a recapitalization by the time we get there? Maybe interest rates are higher or low and therefore it's less attractive or more attractive.

Um, so it's about updating the model as you go as well.

Okay, good stuff.

So, um, we're just adding in as you might expect, extra elements as we go through.

So a lot of work to do on the recapitalization tab.

But before we get there, one thing that we have in addition in this model as well, if right down the bottom here I'm gonna build some data tables and the reason why I wanna build these data tables is to give me this full flexibility.

What these data tables are gonna show me is what the level of debt is gonna be if I don't do a recapitalization and what the level of debt would be if I did a dividend recapitalization.

And the reason I need to do that as we'll see in a second is because I want some element that will give me what the opening debt level would be in any year if I do the recapitalization or not.

Okay? So that's really what we're gonna do here is our starting point. This is gonna be the first one we're gonna do.

Hopefully I'll make it a little bit big on the screen so we can see what's going on. But we're gonna get into that modeling now.

And before we do anything, before we edit this model, this model is essentially in a finalized state. It is giving us an IRR calculation and it is a model that has circular calculations Because the interest is going up into the income statement that gives us all the cash flows that gives us all the early debt repayment numbers.

So we do have circularity going in a moment and those interest calculations are going into the income statement.

So before we make any changes to this model, we need to go in and turn off the iterative calculations so that if we make any mistakes from now, we get told about it.

Also, whilst we're here, because I'm gonna be building some data tables right now, I'm gonna change the workbook calculation to automatic just so that if I do any data table calculations, the numbers update, excuse me, straight away.

Okay? So I'm gonna get a warning sign now because I've got circular calculations in there.

But the way that we'd normally deal with this is if you go to your, the info tab here, we've got the circular switch on that info tab, it's another named CIR switch.

If I turn that off to zero then I won't have any circular calculations, any circular references in here anywhere.

And if we go to the income statement, you can see that all the interest numbers up there are now set to zero.

Okay? So that's where we are. To begin with, what I'm gonna do is set up my data tables for the closing debt balances for the senior debt and the unsecured notes and our cash balances for the most recent historic year first.

Now if we go and look at the debt calculations here, you can see we've got a standard opening balance, new debt or debt-free repaid as we have to spare cash to give us the closing balance.

But then a new line here for the recapitalization, I want to pick up the closing debt after the recapitalization.

Because what I'm gonna do for this, any numbers that go into the recapitalization line, I'm gonna multiply them by the recap switch.

And if that set to zero then this line will just show zero.

So I'm gonna pick up the row 68 number and then I can copy that all the way to the right for all nine years that I've got here.

And then I'm gonna create a data table by selecting all of the area.

So the heart entire, let me just zoom out so we can see entirely what I've selected.

I'm gonna select all nine years of the model and the column on the left.

We're then gonna hit ALT and DT for data table and it'll give us this pop-out menu.

I don't wanna put anything into the row there. The outputs on the row in the column though, I've got zero and one, which are the off and on switches for that dividend recap switch up at the top in C6.

So if I go and grab C6 what they'll give me is it'll fill out all the data table, it'll all be the same because I haven't got any calculations yet which use that switch. But they will eventually we'll do the same thing for the unsecured notes and the cash balance.

So let's just put those all in.

Gotta be careful to make sure we pick up the number for the unsecured notes as with the debt, the senior debt after the recapitalization row. So it's row 77 here.

And again, select all of that, not quite that far to the right.

And then again, all DT have to move down to the column input cell and C6 to go and grab that recap switch again.

Again, all the numbers right on the bottom will be exactly the same.

And let me just copy everything across to the right.

Now we go, lemme do it better so that we got the output cells at the top and then what things look like if the switch is on or off.

Then no calculations in our model incorporating a switch. So it's irrelevant now but it will build it in later.

And then finally the cache number, our closing cache number.

We don't have a pre and post element here, we've just got the end in cache and again, gotta go all the way to the right and then if we select everything, alt DT somewhat repetitive, but third time is the last time we gotta do this to pick up that C6 switch.

So now we've got data tables that will give us the outcome whether we have the switch turned on or off in the main model.

This is gonna show us in the bottom what our debt number would be and cash numbers would be whether the switch was on or off.

So even if you've got the switch turned on in the model, you can see still see down here at the bottom what things would've looked like if we had the switch off and didn't do this recapitalization, these numbers are gonna be really important because if we go back to the recapitalization tab, we can now go through and use the numbers for the opening or closing debt balances.

If there is no debt repayment because that is the amount of debt that we would then need to pay off if we do the capitalization.

So we're gonna build through this and we're going to, again, let's make it a bit bigger so it's a bit clear as to what's going on.

We're gonna see how much debt we've got without any recapitalization.

Look what our debt ratios might look like. Debt to EBITDA.

We're then going to think about the actual amounts for the recapitalization, how much we're gonna repay, how much more we can borrow.

And then think about what things look like after we have that recapitalization, what those debt ratios might look like.

Some of the calculations, just getting the, getting your ducks in a row pretty much so we can see what we've got going on, helping with the analysis.

The middle chunk there.

In terms of the actual repayments, it's a bit more fiddly but not too bad really.

So what we're gonna think about here is how much debt do we have? So the closing senior debt balance, if there's no recapitalization we can get from the data table at the bottom here.

I need to pick row 132 here.

That is gonna be what debt we would have if there was no dividend recapitalization and it won't change even, even if I turn the switch on later.

So I'm just gonna grab that row F 132 to begin with.

You do the same thing with the unsecured notes. Now the unseed notes in the next row down, so sorry, they're not the next row down, they're a little bit better way from that me.

So we need to go and grab them from the data table for the unsecured notes. They're on row 136 And then the same thing.

We can do total debt, we can just add those up.

Dash is another one that we've got within our data tables.

So the data tables at the bottom of the LBO recap tab, we've got row one 40, that's the cash we're gonna have if there is no dividend recapitalization.

These numbers will all change when I put the switch back on later on and get interesting into the income statement, but they'll all be updated as a result of that.

And then from that we can then figure out our net debts by taking cash away from the total debt number.

What wended from here is build through, let's take this all the way across to the right so that we've got this all populated for all nine years in our model.

So by the end of the ninth year of this model, we end up with a positive cash balance, all of the debt being paid off if there's no interest as there isn't yet.

And a positive cash balance. So negative net debt, what we're now gonna do is look at our multiples, our debt multiples Now because when we acquire this company, our model suggests we're gonna get some, if we jump right back up the top, our model suggests we're gonna get some cost savings built in.

We then need to use the adjusted ebitda.

We don't have an historical adjusted ebitda.

So for these multiples, I'm only gonna look at it uh, for the forecast period.

So I'm gonna take, it's a debt to EBITDA multiple we're looking for for the senior debt.

I'm gonna divide that by from the LBO recap tab, the adjusted ebitda, which is on row 31.

I'm gonna want to keep using that adjusted EBITDA on row 31 as I go down through the multiple for the unsecured notes, total debt and then for net debt.

So to achieve that what I'm gonna do is go back into the formula and for the LBO recap, I wanna always look at row 31.

Even if I use unsecured debt or total debt, it's always the adjusted EBITDA on row 31 of that tab we wanna use.

So if I hit F4 twice, so I've got a dollar sign just in front of the 31, this will now mean that if I copy this down CTRL D, then we still look at row 31.

I copy down again, still look at row 31, but for our total debt multiple as we've gone down twice and if I go down one final time, well this is wrong because it's looking at cash, but it's easier to adjust the G22 here to G23 for the net debt to get those multiples.

So locking onto just row 31 of VLBO recap tab makes that hopefully a relatively straightforward process to get all of those debt multiples in place.

And then I can copy them to the right for all of the nine years of the model.

So hopefully so far not so bad just putting in all of the kind of data and statistics as the model would look, these numbers are never gonna change as I build the full recap model on the next tab, because I'm using the data tables, I always use the closing deck numbers if there was no dividend recapitalization.

Okay, next chunk is to now build the numbers that we would have.

We gotta be careful here because the recapitalization we're assuming or modeling, excuse me, is gonna happen at the end of the third year.

But it's an input assumption.

So I want the model to be flexible so that if I change this number, the model updates correctly.

So I'm gonna need to use some if statements the whole way through this to say, well if the year that we're in is the recapitalization year, then everything happens.

Otherwise we just want zero.

So I'm gonna say if the year that we're in up there on row 16 and because I'm gonna use this in a number of places and I'll keep looking at row 16, I'm gonna lock onto row 16 F4 twice if that is the same as my named cell of the recap year.

So I've got a named cell for the recap year and the recap switch.

Okay? And that is in C7 of this page, C7, C6.

So if my year, the year I'm in is the same as the recap year, then what I want to do is for the recapitalization amount say well how much can I borrow? I'm gonna be able to borrow for the senior debt three and a half times, always three and a half times.

So F four to lock on that.

And I'm gonna multiply that by, well the LTM EBITDA, well that is the row 31 that we looked at from the pre, from the LBO recap tab. And again F4 twice, I always wanna look at row 31 in.

That's if the year that we're in is the recap tab, then calculate how much extra we can borrow.

And if we're not, just get a zero.

'cause it's not the recap year.

Okay? So that's saying correctly that in year one it's not the recap year, so just give a zero.

We should be able to just hit control D copy this down.

And we should still look at the row 16 here. The number of the year, we should still be looking at the recap year named cell. That's not, they're on C7 and we're still looking at the multiple of 3.5 times.

Now that multiple of 3.5 times is where we've got a bit of a problem because really we want to be looking at not C13 there, but rather C14 because we're now looking at how much money we'd we would be able to borrow on the senior debt.

Okay? So we've gotta adjust that down to C14. What we could have done is rather than locking onto C14 with a dollar in sum of front of the C and the 13, if we hit F4 three times, then you lock onto just the column.

So if we copy this down, we would then automatically go from row 13 to row 14.

Okay? So that's how much we can borrow three and a half times or one and a half times the LTM EBITDA and add that up to get the total amount we can borrow.

And then if we take those formulas and copy 'em out for the right all nine years, this will then tell us that we can borrow money at the end of the third year.

This is three and a half times the EBITDA adjusted EBITDA for the third year of the model.

That would be the LTM or most recent time period, historic time period if we're at the end of year three. So there's recapitalizations happening at the end of year three, okay? Same for the senior debt to allow us to borrow 2,187 2,187 at the end of year three under this assumption.

That's great how much, that's how much money we can borrow.

The next thing to look at is what we can do with that money.

Okay, well what we're gonna do with that money is pay off the existing debt.

So again, we need another if statement.

And again, it's gonna be if the year that we're in, I'm gonna do this a couple of times.

So again I'm gonna F4 twice to lock onto that row.

If the year that we're in is the same as the recap year, then we wanna say okay, well all that senior debt needs to be repaid, otherwise we get a zero.

So again, no debt gets repaid at the end of year one 'cause the recapitalization doesn't happen there.

But if I copy this to the right to year three, then you can see that this formula calculates for us this 212 closing debt balance at the end of year three needs to be repaid.

If we're doing the recapitalization exactly the same thing, I copy this down and then to the right we should get the 562.6 that we have as the closing debt on the unsecured notes.

So setting up those dollar signs makes it much more straightforward to copy all of these across to other rows.

Okay, so that's the debt getting repaid.

So we borrowed this 2,186, we're using just over 500 to repay the senior debt just over five 60 to pay back the unsecured notes.

Okay, what else have we gotta pay? Well we made an assumption that there was gonna be some arrangement fees, 2% on any new debt.

So it's gonna be 2% off any money that we've borrowed and the money we borrowed is gonna be the recapitalization amounts.

Don't even need to add anything up.

Just that money that we've borrowed there on row 34.

So 2%, that 2% is gonna stay fixed.

So I do wanna lock onto C8 and then copy that to the right to see what that fee would need to be on the new money we're borrowing both senior and unsecured net.

So that's a fee to pay on the new debt.

We're also saying that there's gonna be a fee to pay on the repayment of some of our debt balances.

Now we've got some switches over here and the switches here are saying that the senior debt doesn't have any early prepayment fees, whereas the unsecured notes do.

This is not the most uncommon setup because the senior debt probably wants to get paid back first.

The unsecured notes are maybe quite happy earning the higher interest rates on those unsecured notes and paying that off early might not be exactly what they want.

So we're gonna build those in to give again, more flexibility.

So for the prepayment fees, the assumption is that it's 1% F four to lock onto that of the amount of money we are repaying in the amount of money we are repaying, we've got in this use of recapitalization proceeds higher up. So they need to have an if statement here, okay? But we also want to make sure that we incorporate the switch on the right hand on the left hand side to see whether there are any prepayment fees or not.

That C40, I always want to look at column C.

So F4 three times the lock onto that column.

We should be in a position now without formula to hit control D to copy down, which is now looking at the unsecured debt that's being repaid.

It's looking at the one for the prepayment fee switch but still looking at the 1% prepayment fee rate up there on C nine.

Okay? So again, those dollar signs help us just apply in the same formula in the numerous places.

If we copy this across to the year when the recapitalizations gonna happen, we can now see that there's no prepayment fee on the senior debt.

There would be one if we turn the switch on that gives us the 5.1, but we have that switch off so there's no prepayment fee here on the senior debt.

But we do have a prepayment fee on the unsecured notes.

And then if we add up all of these uses, we can see that we're gonna take the money that we're borrowing the 1,200, 2,187 and we're gonna need to spend 1,124 of that at the moment before we build an interest in our calculations, uh, to repay the debt and any associated bids with new debt or old debt, right? What that then gives us is if we take the proceeds minus the use of cash, that will give us the amount of spare money that we've then got just over 1000.

And the final point is that, well how much of that spare money could we use from the recapitalization after we paid off everything else that we need to pay off? How much of that could we use to make a dividend payment? And our assumption here, we don't wanna build up a cash balance just saying take all of the cash, all of the spare cash you've got and just use that as a dividend payment.

So again, lock onto that 100% number, but it's saying we can use all of that money to make some debt payments.

Okay, hope that loss but not too bad.

All of the rest is gonna be zero to give us this outcome.

Good. Scott, a question saying if there were two recaps, we just need to build in two switches.

So recap one, switch and recap two switch and year.

So you could build those in and you just need to make sure that the recap two is after year one, but then you just add in the complexity for model.

Just kind of redo all these calculations again.

So there's nothing, no more complicated formulas needed other than just replicating the process.

Okay, good.

Final thing we're gonna do here, don't really need it right now, but just is gonna be complete for later.

Let's just put all the data in for what things would looked like after the recapitalization.

These are gonna be exactly the same as we had for the top chunk.

So what the closing debt number looks like after recapitalization.

We can get from the bottom table, the data table down here, just row 133 for the senior debts row 137 for the unsecured notes.

Just add that all up as before cash comes from row 141 of that data table.

Always give us the number if we have the recapitalization and then debt minus cash will give us the net debt number.

So nothing new or complicated there, just picking up the relevant numbers. They'll all the same for now, but they'll change once we put all these data num data points that we've just calculated into the model that we'll do in a second.

And then all of our multiples. And that's, we're just gonna start, sorry, let's get these all in the right place.

We have one column over here.

So let's start off with the historic time periods here so that this can then pick up year, year nine as well.

But we don't want to use the historic time period for the multiples because we want use the adjusted EBITDA number.

So again, senior debt and divide that by the row 31 value adjusted EBITDA for the model.

And again, F4 twice to lock onto row 31.

So again, numbers look exactly the same.

So I've only locked onto the row.

I can now copy this down for the first three and then for the fourth one and then adjust it down to G52 to get the net debts by locking onto row 31. It makes that a nice quick process.

And then again, we can copy this across for all nine years.

Again, as we mentioned, this looks exactly the same as the chunk at the top, free the recapitalization because we haven't modeled the recapitalization yet.

Okay, so from now what we've got, we've got all the data we need.

All we've gotta do is put this into the model.

So incorporating the effects of this proposed prospective dividend recapitalization into our model.

Okay? So all we're gonna need to do is slot this in as in where it's necessary.

First place it's gonna be necessary.

I go just a little bit bigger so you can see what's going on here.

The first place we're gonna need to include something is in the income statement.

Now for our interest, the interest cash flows are based on how much cash you've got and therefore the closing balance and therefore how much debt you can repay that will impact on your interest number. So we've got circularity here in that circular switch.

The prepayment fees work in the same way pretty much.

So the more a prepayment fee you've gotta make, the less cash is left over, the less debt you can repay.

So I'm gonna just pull the prepayment fees through and since I've got more than one of them, let's add up those two prepayment fees, senior debt and unsecured debts.

Okay, if we just pull those in, I can copy those across to the right.

Okay? We are working, fine at the moment because I haven't built the whole model yet.

But this will create a circularity eventually.

So first things first, is the sign the right way rounds? Well this is adding onto my profits. Well it's a fee. So that needs to be deducting from my profit.

So I'm gonna multiply by minus one. First of all, copy that to the right.

So now this looks better that after these fees, my profits are smaller, okay? And that aligns with the tax expense being negative. So that's in the same direction it would look like.

But we then need to build the switch around this.

So if the circular switch is on, then we've gotta do all this otherwise a zero.

So the same format as the switch we have per interest, but just around the prepayment fees, copy that to the right. It's gonna gimme a zero now because the switch is off.

But we can see that without the switch. It's doing the calculation right? In terms of pulling this number through from the income state or from my calculations on the recapitalization tab.

Okay, I'll put the formulas in for the bits we calculate. So if you want me to go back and have a look at those, they'll be there for us.

Essentially it's just pulling the pre-payment fee numbers through from the calculations did in the previous tab.

Okay? Nothing changed in the cash flows because the interest goes through into this eventually as will the prepayments.

So the next adjustment we've gotta make is on the debt servicing.

And what we've gotta bring in here is to say, well if we do the recap capitalization all the debt we have, otherwise we'll be repaid and we take a whole bunch of new debt.

So for the senior debt, the two things that we need to bring into this row are, sorry, just stop myself for a second.

The reason I'm stopping myself here is 'cause I haven't done here. The one thing that I said that we do need to do, which is to multiply this by the switch, anything that's in a blue row here, you've gotta multiply by the recapitalization switch so that if the recapitalization doesn't happen, you haven't gotta pay the fees, you don't need to pay the repayment fee if we don't do the recapitalization.

So I'm multiplying by the recap switch.

It's up here on C6.

If that was off, we'd get no numbers coming through here.

Anything that we've got highlighted in this TUR blue line needs to be multiplied by the recap switch cell so that if the recap switch is off, you don't get anything coming through to any of these ropes.

Okay, so coming down to the recapitalization row for the senior debts, we're gonna need to say here in some brackets about switch in a second.

Well, if the recapitalization happens, then the existing debts or we're gonna take on some new debts, but we're also gonna need to deduct from this closing debt balance the existing debts because that won't exist anymore because it would've been repaid.

Okay, I copy this across to the right for the three rows.

What you can see here is that it looks a bit weird, it looks like it's not working.

And the reason for this is because the data table's getting confused.

Now the data table doesn't incorporate the effect of a switch yet because I haven't built it in yet.

And the opening balance on the previous tab, the recapitalization tab, is working off this new closing balance, which means everything gets outlined a bit. What we've gotta do though, to fix this all up is multiply by the recap switch because now the data tables, if I copy this to the right do then pick up all of the relevant numbers.

Okay, let me just put that formula here so you can see what's going on without messing up our model.

Okay? So we are multiplying by that recapitalization switch that's maybe not so clear.

C multiplying buy the recap switch so that the data table now gets things uh, right.

And if I just copy this earlier, the right for now, what we can see is that this 1000 number looks a bit weird.

This is not the same as the money that we've borrowed, but that 1531 is what we get to at the end of the year.

So we had five 12 that's getting paid off.

This recapitalization row is now picking up the new extra debt that we've got to leave us with the net effective new debt to leave us. This with the 1531 that is the all new debt.

So we're paying this off borrowing 1531, which will leave us with 1531. The net of those two movements is this 1000.

We're multiplying by the recapitalization switch.

So if we turn that switch to zero, there'll just be nothing here at all.

So that's great. We've caught this in for the senior debt.

The next component is the most complicated thing we've got to do.

Once we've got it done though, everything else will be fine after that and we can wrap it up almost in two minutes after we get this next cell done.

Okay, so this next cell is the most complicated.

So what we've got is, and we're gonna build it up slowly so you can see the steps step by step.

What we've got at the moment is the interest rates multiplied by the opening balance and the closing balance on average.

Now this interest rate is coming from the ex initial senior debt 4.92%.

Problem we've got is that the new debts after the recapitalizations got a different interest rate of 6%.

So what we're gonna need to do is to incorporate that in here.

And the first way we can do this is to use as always another if statement.

So we're gonna say if we are after the recapitalization, so if the year that we're in, so if the year that we're in up here is on row 22, if the year that we're in is greater than the recapitalization year, that's a named sell.

So that's our recap year.

So if the year we're in is greater than the recap year, then we have done the recapitalization.

So we want to use the interest rate from the recapitalization. The 6% gonna lock onto that F4.

Otherwise just use the interest rate from the LBO recap tab.

Where's my brackets? And that works for us.

So what I should be able to do now is as, as I copy this across to the right, you'll see that the interest rate should jump in the fourth year after the recapitalization.

So I copy that to the right. You'll see there's more interest now in that fourth year.

Okay? So I'll copy that all the way across to the right and I'll put the formula down the bottom here and make this a little bit bigger so we can see it. Hopefully I'll put the formula down here.

So what we're saying at the moment is if we are after the recapitalization year, then we want to use the interest rate from the new debt.

But if we are before the recapitalization year, then we want to use the interest rates, for the fundamental transaction.

But we've still got a problem.

And the problem is that if I want to see what would happen if this recapitalization didn't happen, turn my switch off.

Well if I come down to the debt numbers, what we'll see is that now there's no recapitalization that happens at the end of year three.

But the interest rate that we're using here, so I'll go and grab the select all of the interest rate elements so that whole if statement and then hit F9, which calculates things for me, it's given me 6% and the 6% is the interest rates on the new debt if the recapitalization happens.

So that interest calculation from year four onwards is still using the interest rate as if the recapitalization happened when it hasn't happened because I've turned the switch off.

So what I've gotta do is add another element, told you it's gonna be the most complicated formula.

Add another element here to the IF statement.

So we don't just need to be beyond the recap year, we also need to have the recapitalization switch on as well.

So the way we do that is to use an and statement upfront.

So here it says if the, what this is telling us if the recapitalization switch or if the recapitalization has already happened, if we're after the recapitalization year.

But also if the recap switch is on, both of those two things need to happen for us then to use the interest rate from the recapitalization tab, sell D13. Otherwise, if they're not both on both happening both true, then just use the interest rate from this tab.

And if I copy that across to the right, you'll see nothing changes except for year four is now back down to the interest rate based on the 4.92% in the LBO recap tab 4.9178%.

So two things need to happen.

We need to be beyond the recap year and the recap switch needs to be on for us to pick up the new interest rates.

Lemme just turn that recap, switch back on again and you can see in this calculation that formula is picking up both of those two factors for us.

So now we've got the recapitalization happening and we have a higher interest rate being applied in the next year.

Okay, that's pretty much it for complicated stuff.

We've gotta replicate all of this for the unsecured notes, but let's go ahead and do that.

So we can do that without too much trouble.

Just gotta be careful that when we look at the recapitalization of the unsecured notes, we put our brackets in, we look at the unsecured note cash flow.

So there should be the new unsecured notes that we get minus the repayment of the closing balance, but multiply this by the recap switch.

So we only show a number here if that switch is on.

So It throws all our numbers off just 'cause we've got that value in there.

Let me get rid of it. Everything updates in a second when I get the values back in, everything should be fine.

So let's put the numbers in. All those values will go away.

I'm hopefully as I copy this across, I'm pretty sure we could.

So if I go back and copy all this across the right, then we should find once I get these numbers sorted out will be okay.

Yes. Because the closing cash numbers is coming through. That's all. Got text in it. We'll pick that in a second.

We'll be good. I'm pretty sure. Yeah.

Okay, we'll be given a second.

So what we can now see is that the recapitalization values coming through, okay here.

But we've got that recap switch in there as well.

Let's just see if we can't get rid of all those value numbers, just pretend that switch on and off again.

Gotta get the cash number in and I'm pretty sure solve everything for us.

So to get the final cash number to work, I'll come back and fix this interest in a second.

So we're gonna have our final recapitalization that's gonna come from the recap tab.

That's the amount that we're paying again times by the recap switch.

Okay? And the cash flows, this is all of our cash flows.

We wanna pick up all of those fee amounts, okay? And again, multiply that by the recap switch.

I then copy that all across the right.

We should be in a place where our cash cashflow numbers are all sorting themselves out.

We've got some text in here somewhere, which is what's messing this all up for us.

The easiest way to fix all of this, I'm just gonna get this across to the right.

What I'm then gonna go back and do is anywhere we've got any value numbers, let's hope we don't have 'em all at the top.

I'll just copy everything across the right just to get rid of everything, recap it, copy it across to the right, it's working in the first row so we can be pretty comfortable after that.

Okay? So the reason it's not working is because you have to gonna have to read all the data tables to get rid of those values.

So having the data tables in here does make it somewhat more Volatile.

But fingers crossed by I update all these, we should be in a better place.

Fingers crossed at least. Anyway, okay, so that looks like we're in a better place now.

We've got all of those values looking a little bit better for us. We got rid of all the text. You're stuck.

So where do we get to? We got to the interest rate calculation. We've gotta replicate what we've gotta do for the interest calculations here.

So two things we've gotta do is an if statement to say that if the year that we're in up on row 22, if the year that we're in is greater than the recap year, then we'd pick up the interest rate, the 9% for the unsecured notes.

Otherwise we do everything here as normal and then close off the if statement.

But as we did for the senior debt, we've also got to put the and statement in here to say we don't just need to be beyond the recap year.

We also need to have the recap switch being turned on.

So both of those characteristics in place, let's get rid of the formatting, copy that across the right and that will work for us.

Okay? So now what you can see is that the interest stays flat for the first two years because the unsecured notes haven't been repaid.

And then we get a jump in interest as we take on more debt and then we get higher interest rates being applied in the years that, okay? Now I did already put in here the numbers for the, or the formulas at least for the recapitalization and also the fees.

Okay? So they're all in there, but it's giving us a weird outcome.

It's giving us a closing cash balance that isn't zero.

And that's because the formulas are just adding up all the cash flows, which means we need to make, make this cash flow here negative, okay? And the cash flow on the fees negative as well.

And what that leaves us with is a cash balance in the end that is actually zero.

So cash balance at the end absolutely zero.

So we're good with that one and everything's flowing through nicely.

Last thing to for us to do is just to build this all into the cash flows for our IRR calculation and then we're done.

So how much of a cash flow have we got on the recapitalization? Well at the moment we're saying that we can pay ourselves just over that 1000.

Do wanna flip the sign around those so it's a positive cash flow to us.

Okay? And then for our IRR calculation, all we've gotta do is add onto this, that cashflow from row 100.

Doesn't matter what our, what our ending exit year is, we're always gonna get that dividend recap if the switch is on.

So let's just copy that across to the right as well. Okay? And that's it. All our modeling sorted out.

All I've now gotta do is turn my switch on.

So if we go oh ft, put on the iterative calculations and if you hit F5 and tab, we can go to the circular switch and turn that on.

And F5 again will jump us back to where we came from.

And this will, after all of that, show us that if we do the dividend recapitalization, we'll get not a thousand coming back in at the end of year three but a bit lower.

Because now we've got interest payments and 'cause of interest payments. There's less than the debt paid off.

So a bit less cash coming back to us. More cash.

The existing debt needs to be paid off with that recapitalized debt. Cash flows we also receive less in, let's just show you what the numbers would look like with the switch off.

If I turn the switch off, we should get back to that 26.1% IRR that we had to begin with.

Okay? So 1400 out to begin with, 4,500 in in year five.

If I turn the switch on, we get the same cash app at time zero the same initial investment cash flow.

But then we get in this dividend coming to us at the end of year three, that's for 815 a bit more and in year five we get a lot less coming back to us as the equity investor.

And that's because, well there's more debt within the company on the exit date.

A year five enterprise value hasn't changed here.

That's still the 55,500 almost, but there's just a lot more debt left in the company and as a result we get less coming to us as the equity investor.

But even despite getting less at the end of year five, we still end up with a higher IRR 28% rather than the 26.1% because we're getting a whole bunch of cash coming in two years earlier.

It's less cash, but the time value of money concept helps us out here getting that, losing 1000 on the year five cashflow, but making four 50 on the year three, cashflow gives us a slightly higher, high, slightly higher IRR Overall, the data tables are now showing us the flexibility here.

They're showing us what we have with the recapitalization, what we'd have without the recapitalization.

And also on the recapitalization tab down the bottom here, you can now see that all of the multiples reflect this increase in how much debt there is after the recapitalization.

Okay, great stuff.

Hopefully that all I all makes sense for you, all happy and, and makes sense.

If you've got any questions, please drop them into the, chat function or the Q&A function.

There will be a solution file available where you've got the empty file on by the end of the day.

Otherwise, that's it from me.

Maybe she's another one of these biggest live sessions in the near future and hope you have a great weekend.

Thanks very much.