So this is an intermediate model and lots of people when they come to a course like this, they will have seen an intermediate and um, you can fit the whole L B O on one sheet. And um, what's going on is we build a little model, okay, that's ed buyout post deal.

And then we run a little cash flow statement to say what's available for accelerated debt repayments.

And then we go ahead and do accelerated debt repayments in what we call a debt waterfall. Okay? Um, and then we work out how the um, the L B O plans out in terms of I R R given all the debt that was being repaid.

Now in terms of where we are in our series, okay, so we are today doing the debt schedule and um, I'm gonna show you how we can go further than the intermediate model in terms of debt schedules. Then last week I did the deal structure.

So I dunno if anybody was there last week, but I talked about who the major players are, how it works, and I introduced the advanced model. Then next week it'll be exit.

So my colleague Maria will be talking about what the LBO O exit looks like, how you can exit, what best kind of exit is.

And the week after that we've got something called stub. Okay? I won't explain all that is yet because it's pointless.

I'll explain it when I get into the model itself.

Okay? So if you were to go to the top, oh, sorry, no, to your three dots, you would see resources, okay? And in those resources is uh, where are we? Okay? In those resources is this model, okay? The advanced W model and what I'm gonna do this session as much as possible in the next four to five minutes or so is build from the intermediate model into something a bit more full, okay? And obviously watching somebody do Excel online can be challenging, especially on a small screen. So if anything's unclear, just put your hand up or chat to me in the chat box or in the q and a, everything's fine going out.

First thing I'm gonna do is just take a little tour in the model you can see we've got a model without the deal and then we've got a model with the deal, okay? And you can see the deal is gonna happen at the end of 2018.

Now in two weeks there'll be a lot more detail on this, but what's happening is the detail, excuse me, the deal is gonna happen about nine months through the the year.

And so we'll end up with a stub period 25% of the year and that's gonna cause a lot of headaches.

So we just need to keep that in mind.

Mechanics of how we build that stub period in the model is something that'll be covered in two weeks time. So if you're interested in that, come to our Felix live.

What we are gonna do is we are gonna use the input and this is what we covered last week, principally we're gonna use what we borrowed to get the deal going and we're gonna try and build an advanced debt schedule to say how and when will this be paid back.

And we're also build in a lot of extra detail on top of the intermediate model.

Now the first thing we're gonna do is I'm gonna grab that input and drag it next to the debt schedule so I don't have to keep going back and forth.

That's the only step I really wanna do with a mouse. This point, I'll switch over to using the keyboard. Okay? So here we go. We're gonna start building the debt schedule. We're gonna explain it as we go.

You're gonna ask me questions. The first thing is the interest rates.

I'm gonna pinch those from the input. Okay? So we've got a revolver. And the revolver, I'm gonna grab it from column L. That's the rate, it's a variable rate on suer. We went through how that was calculated last week.

I'm gonna take it, okay, now I'm gonna copy down, I'm just gonna see if it works. Okay, so the mezzanine is, is 10.

And then I'll look at it and say, yeah, that's copy down nicely.

'cause here the mezzanine is 10 as well. Now if I copy that to the right, it'll fall over. So I'm gonna fudge it a bit and just say, right, be the one to the left and then I'm gonna copy that all to the right.

That's the one thing we are doing in this model is we're assuming that software stays the same throughout the period.

It would be difficult to model in a change of software. You could do.

And this model gives you the means to, but we're not using those means, we're just saying, right? The software's gonna stay booked, some of them will be fixed. Okay? So the high yield and the uh, the pick fixed.

See they've got nothing to do with a margin or softer.

And so actually having them stable makes more sense and some of them it's a bit of an assumption. Okay? Zoom in a bit. So where are we going next? Alright, next we're gonna build up a little cashflow statement.

Now I'm gonna go and grab EBITDA and I'm gonna grab it from the deal model.

And this will be the starting point for the cash flows available for the debt service. You can see the EBITDA in the first year appears to be really low, but that's because it's a quarter of the year. That's the stop period, okay? The tax expense I'm gonna go and grab for them mall as well.

Okay? Now if you've done a lot of dcfs, grab my tax.

There's my tax. If you've done a lot of dcfs, you might be thinking, hold on, shouldn't we be taxing the EBITDA a bit like a notepad or something? Um, no, we, we want the cash tax here. And you might think well hold on, doesn't have an interaction with interest.

Yes it does and that interaction will be circular. So we are dealing with it, but it's circular and because we're not building the whole model, that's hard to see. Take it from me, it's there.

I'm gonna drop my formulate in.

Got this is moving like a snail. Um, I'm just gonna close a couple more things and and see if it helps. Okay? My PC has got itself all and it tears wondering what's happening.

It's just become deeply unhappy. Apologies for that, but hopefully it will help in the longer term And it's, yeah, that does seem to free up something. So it was really annoyed with that intermediate model probably 'cause it had a bunch of data tables in it. Okay, so where are we next? We're gonna bulk out our cashflow statement with the things that would inhibit us from paying off debt. And we'll go and pinch those.

Our cashflow statement from the deal model.

And I think we're starting with increase of decrease in long-term assets and we just look at it and can you see the increase in decrease in other current liabilities is 2.5 under there? I'm gonna copy down and hope for the best. Yeah, that seems to be working just fine.

So I think I'd want to check that a bit more carefully if I was doing a big job.

But in the interest of speed, let's just move on.

Now we're then gonna grab CapEx again, I'm looking at statement and then I've got my cashflow available for debt service.

So there's 30.3, it's available to service debt. Okay? What's gonna happen next? Now, because the starting point for our cash flows available were ebitda, okay? Cash interest will impact our ability to service debt. Okay? Now if you've seen any intermediate models, you might see that actually interest is kind of excluded at this point.

And that's because in intermediate models the starting point for this is often net income. Whereas this one is started with ebitda.

Okay? That means that to get to the cashflow available for debt service, we would've to deduct all of these interests.

The thing is we don't know them yet. And so I'm gonna defer until way later.

Okay? I, I don't really wanna point those at cells which don't exist yet.

Okay? And I could do that as well for the mandatory payments.

Now it's tempting to say with the mandatory payments, well we've got the mandatory payments up here, can't we model them straight away? That would actually be quite bad idea. Okay? It, it would probably be fine with a CapEx, but can you see over here, I'll highlight this one term. Loan B has a mandatory bullet repayment in year. It's probably year seven.

If we then said blindly right term loan B is going to have a mandatory repayment here, it would fail to recognize that by the time we get to year seven, the cash sweep may have swept into term loan B as part of the accelerated payment.

So we can't do the mandatory payment until this point. And so we're actually gonna link this stuff with what's under here.

We'll just do it later when we've got some figures to point at.

Now we do need to continue things. So here we go.

Cashflow before R C F, well we started with 30.3, we would then have to pay our cash interest and we've to pay all mandatories and then let's not change anything right now.

But when we got those going, it would inhibit our ability to do accelerated repayments or it would drive us into some sort of cash deficit.

Now it says they're beginning cash. I, I like to work things as a um, a base. So uh, a kind of working or calculation and um, I want this to be pointed at last year's ending. That's nice, neat modeling.

So last year's ending, I'm looking for cash, it says cash there but it's not gonna close in cash.

Uh, finally right at the bottom there's our cash and then the ending cash from last year will be the starting cash from this year.

And I know it's a bit higgledy piggly but it makes me a little uncomfortable to have a zero there as in kind of pointing at nothing.

So I'm gonna go and actually do that. So I'm gonna go into my deal model and I'm gonna find my balance sheet and I'm gonna say the closing cash in the proforma is 30. Okay? And then that 30 it's gonna populate beginning cash up here.

Okay? And then those two together will be the amount of cash initially available for the R C F, the refinancing and the sweep, which we haven't discussed yet. And that means that, let's just simplify, let's start, say we start with an RCF of 60.

We could pay that off, couldn't we? Except there's one more thing before we get there.

This business in the assumptions, okay? So I'm in the input tab now they're just telling me we reserve 30 of cash, okay? So they're reserving 30 of cash, which means although we've got 60, we don't really have 60, we've got 30.3 to play with.

Okay? So that's a preamble really. We're now about to start the cash sweep.

So lemme know if you have any questions, Timo or Curie, um, or if you want me to go through anything again, otherwise we'll start the actual um, sweep element with the R C F.

And I'm obviously making assumptions about what you know from kind of last week let's say. So if there's anything that's unfamiliar in terms of terminology, let me know and I'll explain it.

Okay? So the R C F. Now for reasons that will become clear later, I'm actually gonna uh, populate the closing balance for all of my debts and we can just sort through the, okay, the closing balance on the R c s is gonna be the revolving credit facility on the deal date.

Okay? So that's the short term debt to match uh, working capital.

It's gonna have a look at the rest. Now the refinance facility, you may not have seen this before.

This is kind of like the lender of last resort for the L B O.

So we haven't got any that makes sense 'cause there's no refinancing risk just yet. But the uh, the L B O structure has negotiated a facility just in case they kind of run out of the ability to refinance.

So this is a kind of mega R C F.

It's also mega expensive and it'll become important later.

But right now we'll say right, we haven't got anything, okay, term loan B, right? How much term loan B have we got? Well, on deal date we've got a mighty 800.

And if you hear last week you would've hear me said LBOs are generally driven by term loan Bs now and other things term loan A has orbit disappeared from the structure of any LBOs. Okay, let's keep going. Got a second lien. So some sort of subordination, uh, lower ranking debt and there's not so much.

Okay? So those are institutional investors. Then high yield, so through to public debt.

So this deal has been able to tap the bond market and they've been able to tap a hundred.

And then probably just to show how it works 'cause it's not that realistic, we've got Mezz. Um, now mezzanine debt would be, you know, some sort of preference shares or very subordinated debt and it says it's payment in kind.

It's not actually that common to see mezzanine and bonds being used.

And that's 'cause you know, big players, if they can access the bond market, it's probably cheaper to do that as opposed to using very expensive mezzanine to plug the gaps in their funding. Okay? Now we've looked at all of the debts, there's a bit of an aside, uh, I'll show you why I wanted to do that a bit later. Helps us right now, let's get back to the R C F and start modeling it. And this is where it starts to get pretty intense. So again, just stop me if I'm not making any sense. Okay, now in intermediate models, the drawdown and the repayment are done with, with one min function.

Okay? So if you studied with us before, you would've seen this done a bit like this, you would've seen right? Equals min, either we take the cash available if it's lower or we take the balance if it's lower.

And that's what we're gonna repay. Okay? So that makes sense.

So we've got 30 available, we'll repay 20 because we only need to repay 20.

The thing is, this doesn't make any sense for this model.

It says draw down there. So actually it makes more sense having that here.

Yeah. And so we've got a bit of a problem there basically.

Now you might think, well why have a drawdown separate from a repayment? The thing is, what's gonna happen here is we've got a bit more of a sophisticated advancement, okay? In intermediate models there's no limit to the r c. So in intermediate or simple models, you'll see on LBOs you won't see any limit to the rcf, which means there's no ceiling, which means any funding requirement can be met by the rcf, which is not that realistic, okay? What we need to do is say, well there's an UNDRAWN portion and can you see the R C F facility has a maximum of hundred.

So this would be with a bank they've negotiated we can access a hundred maximum and oh, I need to lock that.

And at this point they have drawn 20 and undrawn 80.

Now this means that the drawdown can be a maximum of 80.

And this is tricky 'cause now we've gotta say if this thing is negative, then take the max, okay of this thing or this thing turned into a minus figure. Ugh, horrible that, okay? And if not, then don't worry about it.

Okay? Now you're probably looking at that and thinking, gosh, how did you come up with that? The best way of coming up with these things is bang your head against it and then stress test it. Okay? Obviously I, I know what I'm doing here because I've done it a bunch of times. But you know, if you were struggling your way through building a model like this, you would want to build it, look at it, satisfied with logic, satisfied with the design, but then stress that the hell out.

So let's see if it does what it needs to, if this is higher, nothing should happen and that's because we still don't need to need to borrow to plug a gap.

If this is negative, yeah we should borrow. But can you see I, I've revealed a mistake there. Okay, we need what comes out the back of this to be a positive.

So I had to add and minus one again. So there's like two minus ones in there.

Okay, now let's stress test some more. So if I've got a big gap, I should have a big borrowing, but if I have a huge gap, I should hit the ceiling of the R C F now. Oops.

And there we go. I've hit the, the, the ceiling.

So what I'm saying is if we have a deficit, then borrow against the deficit or borrow as much as you can.

And because of that ceiling we couldn't do our normal intermediate model approach there.

Okay? Repayment. So this one's gonna be a bit simpler.

So we're gonna say if this thing is positive, then use it if it's smaller or the remaining balance, if it's smaller and that's a payment and otherwise register is zero. Okay? All right, so let's stress test that. Yeah, at the moment it's working.

What if we don't have enough to repay it? Yeah, what about if we've got a deficit that should disappear? Yeah, all things are working.

So can you see that's surprisingly complex but now we've got the R C F and copy across the uh UNDERRUN portion and we can move on.

Okay, I'm just pausing there.

I'm gonna see if there's any questions in the q and a, any questions in the chat? I can't see any.

Alright, so um, a lot of this stuff down here we can just shortcut because the interest rate, we can just pinch it from up here.

So we don't need to go through the whole kind of margin floor thing that we did last week. The interest expense, I'm gonna do a classic bridge on the closing balance times the interest rate.

But now again with an advanced model you need to take into account how long the year is.

And this year is actually more like a quarter of the year because it's the STU period.

Okay, that's a negative. So there we go. Now the commitment fee, it wants us to drop the sentence percentage terms so we go and find it on the input lock it.

Now that means that on any undrawn portions of the R C F we'll be paying 1.5% and this is a fee that the bank would charge for reserving.

Um, and for funding 'cause they need equity 'cause of their um, capital ratios. Um, and they need to do a bunch of admin.

So even if the company ne, oh sorry, the L B O never use the R C F, they will still owe the bank money.

And that is again modeled on an average basis.

Now instead of doing the drawn portion, we're more interested in the undrawn portion.

It's gonna attract a 1.5 fee again for a quarter of a year.

And there we go, we go, our first interest done and I won't go up and populate the interest 'cause I'll do that all at once, right at the end, if we have time, which we probably won't, okay, so we've done the R C F, we had 30.3, but we've just decided to pay down 20. But in another year we may have bolstered or shored up our deficit by drawing down and now it's tempting to put in the interest, but when I've completed everything, the interest will already have been reflected in that 30.3.

So I can just hit enter now.

Okay, so next the refinancing facility, we're gonna start with zero.

So nothing much is gonna happen, but we'll model it anyway. Now the drawdown, all the repayment, we can just do this as one overall min, okay? And we can say either take that if it's lower or that if it's lower, okay? And then times of I minus one and again let's stress test it particularly because the zero has come out, we're not very happy with that.

So let's say we had some refinancing facility, well we, we repay it to the point where we're able to, okay, let's say we don't have very much, well we'll repay it up to where we need to, but what about if we have an awful year but we'll draw down on it.

Now the reason that you can get away with doing that all in one min is because there's no ceiling here, okay? Some part of the mold needs to be limitless or it'll break. Okay? So if we programmed the ceiling on the refinancing, which in the real world there would be, then if you hit that ceiling and you you needed more cash, you would end up with weird things in a model like negative cash, which is impossible.

So you need a part of the model to just keep on giving and giving and giving.

And our assumption here is that it's not the RCF but the refinancing facility, which is a better assumption.

Now in actual fact the refinancing facilities probably attached to the other debt somehow or maybe all of them or one of them. And there probably is a limit.

It's probably quite complex, but that is a ing factor we're putting in here.

Okay? Now I'm gonna skip the interest for the refinancing because I just wanna get on with it. Okay? I'm gonna say we had 10.3 and we did nothing but were we to have done something here, it would've influenced that 10.3 and now we've arrived at a cash sweep.

Okay? Now in intermediate models we sweep all the cash.

So in intermediate models we just, anything we have goes into any debt which will accept it until all that debt's gone. And then we start building up cash.

Now what's gonna happen in more advanced models is we'll have demands baked into the contracts of the debt that if there's certain kind of leverage levels, typically there'll be a certain percentage expectation of free cash flows reserved for, uh, debt pay down.

And this is almost like a covenant and it it creates safety or assurance for the lenders. You can see here in the real world it much, it may be much more complicated than this, but here we're saying, okay, as a whole the um, the average that the uh, debt demands in all of their agreements is that if the leverage is above 3.5 and you can see the leverage is being defined here as net debt on ebitda and if you've got high leverage then half available cash needs to go.

Whereas if it's more modest, you can get away with retaining more cash.

Okay? More modest still you can retain even more cash.

Okay? Now to do that we need the total net debt over ebitda and to do that we need a total net debt. And you can see they're doing us a favor here. They're saying total net debt at the beginning of the period.

Okay? So it says total net debt at the beginning of the period. Now to do that, hold on.

Yeah, sorry, I got myself in a bit of a model there.

So to do that we've got a helpful area down here for total debt, okay? And now you can maybe see why I did all of those closings in the proforma.

Okay, so the first one it wants is it once the R C F, oh I just pointed it at the uh, refinance facility, that's the R C F. Then it wants the refinancing CapEx, which is right here at the bottom I think, oh I didn't grab it, that's not so good. So I'll go and grab it now.

Okay, term loan B, majority of the debt second lien lien or leann high yield mezzanine.

And then we got our total debt and that's the opening total debt, okay? Uh, for the current period.

So that's the closing current to total debt at the end of the current period. But our debt sweep doesn't want that, it wants the beginning of the period.

So we're gonna point column I at the bottom of column H because the ending from the pro forma will be the beginning of the first year of the L B O new ownership.

Now it wants net debt. So I'm then gonna minor softer, pro forma cash and that was quite complex. But in the end we got our net debt.

Now they've decided that the net debt will be net of mandatory payments.

We don't have any mandatory payments now, but we will do.

Okay, then we can add those together.

We can go and grab the ebitda.

Okay? Now it's tempting to press entera and in fact I will just down here, but you see it gives us a bit of a nudge here. It says period ebitda, okay, we've gotta be a little careful that represents a quarter of a year 'cause of the stop. So to gross that up, I'd better divide it by the percentage of the year.

And and effectively what I'm doing there is I'm saying I would need to multiply that by four to get a year's worth of ebitda. Otherwise it's, it's a very flawed thing we're doing there.

Okay, so then net debt over ebitda, we've got 5.7.

And then what we need to do is we need to say, well what will that mean in terms of the suite? Well you can look at it with I and say half the money's gonna have to go in, but we don't want to do that every period, do we? So we're gonna have to make a little program here and we're gonna have to say, okay, if this thing is greater than this thing, then we're taking 50%. If not, just gimme a zero. Okay? Now that one is relatively straightforward because the next one is, is really hard. Okay, if now I'm gonna have to drop an and in this one it's less than this one.

Okay, but this one greater than this one, then go and grab that. If not, gimme a zero. Okay? So that, that one was really tough because it's a band as opposed to just a simple one-off. If this one's a bit simpler, we can say if oh this thing, oh sorry, is smaller than okay this band here.

And, and by making it smaller then we're actually covering negative net debt as well, which is nice. Then Go and get me the 20%, otherwise gimme a zero. Now let's stress tested.

Okay, so what happens if I have a three? Yep, I'm in the band.

What happens if I have two? Yep, I fall down. What happens if I'm at minus one? I'm still at the bottom all good. Okay, really, really tough that.

But what we've got then is we've got the means to say, well just gimme the sum of that and it'll gimme the one that's the relevant one let's say.

And then we're ready to do our sweep again.

I'm just gonna have a look around the Q and a, the chat, I can't see anything there q and a, can't see anything, feel free to excuse me, ask questions. Okay? So I think what we'll do is we'll aim to at least do term loan B and then I'll maybe open up for questions. Okay? 'cause our schedule finished time is about 15 minutes or so. So what we're gonna do is we're gonna say, well they wanna know how much isn't in the suite.

Well that will be the other side of that 50% worth of apps that we add available for the suite and they wanna know what's in the suite or it'll be the 50% of what's available for the suite.

Okay? Now that'll become important later.

What we're gonna do now is we're gonna do our first suite and we'll definitely do turn nine B and then we'll see where we're, so we started with 800 and the mandatory payment, it's very tempting to do this and say we have a mandatory repayment of term loan B 0% of the initial balance. Okay, now let's stress test that and we'll reveal why that's a problem.

Okay? Now if I put an immediate and total mandatory repayment there, then you can see it's already revealed one problem, which is that that needed to be a negative. Okay? Now on the whole, that still doesn't look like a problem, but let's imagine that this isn't year one, it's actually like year five or something. And by the time we've got here, we've already paid off a bunch of our debt via accelerated payments.

And can you see that the way I programmed that, it wasn't sensitive to that fact. The mandatory payment, the way I've done it is just whatever it says up there, we're gonna pay.

And really it's the same reason that um, you, you couldn't do the mandatory's higher up in that whole kinda schedule of mandatory payments. What you've gotta do is be much more clever about this.

So you've actually gotta say, okay, we're gonna take the minimum of whatever the mandatory payment is up in the schedule or we're gonna take the remaining balance if it's lower. Okay? You see, uh uh, I haven't unwound that.

Yeah, so that's a 100%.

Now let's leave it like that just for a minute and I'll show you how this works.

Okay? So now the way I've done it, if the what's left in the bank is only 400, then although the mandatory repayment is a hundred percent, we're not gonna do that because why would we pay a hundred percent? And if the mandatory payment is lower the way it should be, then that's gonna take over the min and we're not gonna pay anything.

So that mandatory's actually quite complex. Yeah, the mandatory needs to be able to deal with any proceeding, accelerated repayments and then the accelerated repayment needs to be able to deal with the mandatory payment. So that one's quite tough too.

What we've gotta say is take whatever you've got or take whatever's left and pay it.

And there we go. We've got our first cash sweep going.

Let's stress test the cash sweep. So let's say we've got more money, so we've got 900, well we're only gonna pay 800.

Well what about if the mandatory payment deals with virtually everything, right? So we've got a lot of mins all working in unison to create, you know, good feeling here and a good debt waterfall and a much more complex debt waterfall. And I drag the solution file in, which is also in download.

You can see that if I go to the total debt, you can see that waterfall effect happening.

Okay? So you can see we're starting to chip away at term loan B.

Then when we're done with term loan B, we start to chip away.

What's next at the second lien? It's weird that we keep paying 300. Oh no, this is a balance system.

You're being silly. Yeah, we start to chip away at the second lien and we actually get rid of it in one year. Okay? And then basically nothing happens because we're not allowed to repay the high yield okay? Until the mandatory payment there.

And we're not allowed to pay back the mezzanine until the mandatory payment there.

And this is a debt waterfall that you're seeing here and it's an advanced debt waterfall because it's taken into account all of the different maturities.

The fact that whether you can or cannot repay in the first place, the R C F, the refinancing, okay? It's got a bit of everything in it, it's really good.

Which you expecting to see a refinancing facility.

So if I switch on interest, which you're wondering what I'm doing, I'm turning on circularity.

So the whole thing works. So without this all turned on, the interest actually just doesn't work at all.

Oh wow. I'm actually looking at an error here.

Oh, the shame. See I've got a negative refinancing facility.

Oh dear. Let's have a look at the full file. Oh no, not that one.

No, I won't try and open it up 'cause it'll take forever.

But can you see that that little check there has has revealed a problem? Okay. So I'd have to go through and figure it out. But the idea is that the, uh, the debt was full, um, ends up looking the way it should do, which is, you know, debt going away and staying away. Okay, I'm gonna open up to questions now and then, uh, I'll try and figure out what went wrong with that debt.

Debt was full after the session. Hopefully that was useful and um, you've been able to take something away from it. Um, if you wanted to reinforce that learning, you can go to Felix to private equity and to the advanced L B O model where you could, uh, pick up the debt schedule and run with it.

Um, that's the end of the session. As such, if you wanted to dial off, you can if you around and ask questions, feel free to do that. If dialing off, have a nice morning, afternoon, evening, uh, wherever you are, uh, if you're sticking around, I look forward to talking to you.