Hey guys, welcome along.

Okay, hope you're well. Uh, my name's Jonathan.

Great to have you here. Um, hopefully you can hear me.

Um, hopefully you can see me.

Hopefully you can also see my screen share.

If you jump onto this page, on the bottom right, there are three files to download.

Um, uh, there's additional complex LBO components work out, empty part, complete and full.

So we're gonna go with the empty file.

So if you wanna click on that to download it, that would be great.

Um, I'm just gonna repost the link in the chat box.

Um, and if you can't download it, honestly, I don't need to biggie, uh, you can just watch me.

So if you could download that and open it up, that would be fantastic.

I am seeing people joining and, uh, and that's why I'm repeating myself a little bit.

Uh, but we are gonna get going imminently because we've got fair bit to do in only one hour.

Okay? Right? So if you haven't got it downloaded, don't worry about it.

Just, uh, have a, uh, have a work through, uh, the stuff on my screen.

Um, if you have downloaded it, you should land in a file that looks like this.

Um, there's a welcome sheet and an info sheet, which not massively interesting to us at the moment.

There's a base case sheet and an add-on sheet.

Uh, and there's some recap stuff.

We're gonna focus on the base case in the owl that we've, uh, we're gonna focus on the, uh, add-on in the, uh, owl that we have available to us.

But before we do that, let's jump on the base case sheet and just see what we're looking at.

We have in front of us a very standard LBO model, so there's nothing fancy or flash about this, and I'll give you a little tour, but hopefully as I'm speaking through the model, you'll kind of look at it and think, oh yeah, this is, this looks like pretty standard stuff.

So it looks like, um, and I'm, maybe I'm gonna zoom in a little bit just so we, you guys can see what I'm doing.

So it looks to me like in cell F seven, uh, we have a, a, a target company and it's got an LTM EBITDA number.

And, uh, if you go down a couple of, uh, just a couple of, uh, cells, uh, it has 11 x and it says maximum entry EV to LTM EBITDA.

So it looks like we're gonna buy a business at 11 times ebitda, and we're gonna borrow, um, uh, against its cash flows to complete the acquisition, and we're gonna borrow it six times.

So there's nothing really weird going on there.

If we go over to the right hand side, we can see a pretty standard sources and uses of funds table.

So if we, if we look at the uses of funds and the enterprise value, that must be the product of the EBITDA and the multiple to give us the EV, we're gonna go in at plus some fees.

And if we look, uh, at what's financing that, that's financed by some senior debt at four times and some unsecured notes at two times.

The sum of those, of course being your six times debt to ebitda.

So we're gonna issue some debt and issue some equity to buy the business as you'd expect, really from any LBO.

Let's just zoom in a bit more. There we go.

Uh, from any LBO, if you sort of move further down, you've got an income statement and you've got a cash flow statement.

The cash flow statement, I'd say is pretty important in the LBO because the cash flow statement tells us how much cash we've got available in order to pay down the debt, which is one of the ways, not the only way, but one of the ways that we stimulate our return.

And so then we've got our, our debt schedule, and if I kind of gloss over that a little bit and get it down to the bottom, it's a very, very standard LBO and it looks like, uh, we are expecting to get a 27.8% internal rate of return.

Now, a couple of things to say that's not really a final number because we have no interest flowing through this model.

I know that because in this model interest creates a circularity.

If you look at the info sheet, the circular switch is set to zero.

It's not really a final number, but it's still a good basis for comparison.

So I'm not really gonna mess around with pulling, uh, the interest, the interest through, although you would ultimately, uh, you know, if we were, if we were gonna review, uh, and pull some final numbers, uh, but I'm gonna say without the interest coming through, we've got another I of 27.8%.

A pretty reasonable question would be can we improve on that? Now, a couple of ideas to improve on that.

How about we, we, we do a, a further LBO, an additional LBO an add-on LBO that compliments this first LBO.

So we do the, the LBO that we're talking about in the base case today.

And perhaps, you know, we buy that business today, we make some cost savings, we improve the ebitda, and then perhaps a couple of years down the line things have, you know, stabilized and we're ready to make an additional, uh, an additional LBO and that that additional LBO will complement the first, if you think about the first LBO is like a platform and we're gonna bolt on a new business.

And, uh, and we could maybe even talk about synergies and you'd say, well, that sounds a bit unusual because we usually talk about synergies in m and a, but we're gonna talk about synergies in this LBO because we're gonna have complimentary businesses.

So it'd be interesting to understand if we maybe could, uh, you know, build, build on this platform if we could enhance our IRA beyond that, beyond that 27.8, we've also got, um, some other content on dividend recaps, which are another way of enhancing returns.

And, um, we only got an hour, and I, I know we're not gonna be able to cover all of this, but there is an answer file. And so you can have a look at the answer file to trace back through the recap stuff, and there's some awesome prerecorded Felix content on that as well, which, uh, Uh, which I encourage you to have a look at, if that is of interest to you.

Okay, we're gonna have a look at this add-on then.

So the, the add-on is, uh, it is just another pretty standard LBO.

It's not actually that unusual at all, but the difficulty comes and you're trying to automate the integration of these two projects.

So what we want is we, we want to create a kind of combined sheet that pulls in the EBITDA of both of these businesses, and we want a combined sheet that somehow pulls together the debt of both of these businesses and works them into a, a ash sweep.

And it'd be really cool if we could sensitize it.

'cause we, we don't really know when the, uh, we don't really know when this add-on, uh, uh, LBO is really gonna happen, right? Could happen in year two or year three or year four.

So it'd be great to able to, to be able to sensitize that, but also really good to be able to just turn it off and say, what would it look like if we didn't do this, this add-on LBO.

So the automation, um, and the kind of slightly heavy Excel work in this is gonna be where the complications are introduced.

And, and we're not just doing that for fun.

If we didn't make this nice and flexible, then we couldn't sensitize the using data tables and, and really explore what's going on.

So, um, let, let me sort of get into it a little bit.

Um, we're gonna build this add on LVO.

Now, if we go down the bottom, what I really wanna do in this sheet is get all the numbers, the appropriate numbers for the income statement and the cash flow statement and flow those into the combined model.

And the combined model is in the sheet called base case add-on. So it kind of base case and add-on it kind of glues them together.

So we wanna kind of get that to get that going when we do this base case add-on sheet.

Wanna, if you look here, we wanna go and grab the income statement numbers.

And if I scroll down, we wanna grab the cash flow statement numbers for both businesses, but we also wanna do something on the debt and then calculate the IRR.

Now it'll be probably, you know, a, a, a little bit, uh, opaque if we, you know, if we just looking big picture.

So let's zoom in a bit and, uh, and figure out what's, what's going going on.

First thing we're gonna do is we're gonna look at the add-on sheet.

You guys will probably notice in my version, I have created some kind of moy purpley cells.

So anything moy or purpley in color is the stuff that we're gonna go and populate.

Um, that color coordination isn't added to your file.

I added that just before the session.

I thought I wanna be able to draw everyone's eyes to the, the numbers I'm looking at.

So if it's not mobile purpley, then it's already completed.

Okay? What's the first thing we're gonna look at then? Well, uh, we've got, uh, this add-on LBO and I'm just gonna grab my pencil tool.

And I just wanna draw your attention to this cell at the top here.

I'm putting a ring around, it says acquisition year.

So at the moment, we are assuming that we do the, undertake the acquisition two years after the, what do we call it? The base case LBO happens.

And a lot of numbers are gonna hang off of that point in time.

And so to make the model run better, if I can draw your attention to row 25, we're, we're simply gonna put in, uh, an ownership switch.

Now, this is gonna be series of zeros or ones.

So for example, if they acquire a business, uh, uh, in year two, then what we want is we wanna say, well, and we, I'm thinking about earnings, we won't be pulling any earnings from year one if we acquire a business in year two, if we acquire the business, if we acquire a business in year two, if check out the narrative here at the end of year two, then we won't be pulling any earnings numbers for year two either.

But for year 3, 4, 5, 6, 7, you know, eight et cetera, uh, we, we'll wanna pull earnings post acquisition.

So if you, I dunno what the, yeah, it's December.

So if we buy the business on the 31st of December, year two, then starting for year three.

In year three, we'll have a full 12 months of earnings.

Now, I don't wanna just hard code this in, that's not a very nice thing to do.

So I'm gonna do a bit of formula work.

It's quite, it's quite a formula heavy.

This, uh, this, this entire model.

So if we go into, let me zoom in a bit, make it easier for you guys to see if you're going into G uh, 25, I'm gonna try and put in a one or a zero depending on whether or not, uh, the business has been acquired or otherwise.

I'm gonna say I, because if open bracket, now I want go and figure out the which cell the year count is in.

Can you guys see that in cell G 14 we have the year count.

So if G 14 is greater than, and I'm gonna, I use my mouse so you can see what I'm doing.

The add-on year. So we're not buying the business in year one, we're buying it in B in year two, and then the earnings will be received thereafter.

So if G 14 is greater than, and if I click on sell, I reckon maybe that's F seven, F seven, you'll notice it's been given a name, it's called add on year when I click on it.

So if G 14 is greater than add-on year comma, put a one, otherwise put a zero.

And if I hit enter, you can see the formula on the left. Nothing very exciting happens there, to be honest.

Uh, let's just copy this out to the right.

Let's, so if I control r that out, copy it out to the right, uh, and you'll see 0, 0 1, 1, 1, 1, 1, 1 1 thereafter.

So all the ones say go and grab the earnings, um, from, uh, uh, the, the numbers below.

Okay, the next thing we're gonna do is probably the most difficult bit of Excel work in the entire model.

Let me just explain what we're trying to achieve with a few hard codes and, and then we'll chuck in some formulas.

So if, as is suggested at the top, we acquire the business in year two, so if we acquire the business here in year two, we'll get earnings 'cause it's at the end of that year, we'll receive earnings as we said, beyond year two.

Let's think about synergies.

Now, if you look at the, uh, numbers in row 24, apparently we, in the first year of acquisition, we get synergies of 3%.

And, and then after that the synergies go up to 5% and then they go up to 7.5%, which you might refer to as their run rate.

Okay? When they hit seven point half percent, you could say, why don't we get seven and a half percent straight away because it takes time, you know, we need experience working through the business to achieve these synergies. You know, you need to headcount savings, you know, uh, uh, break clauses on rents and things like that.

So what this is trying to say to you is whenever you do the acquisition, which here is in year two, the first year after the acquisition, we only get 3% synergies.

So this would be wrong, okay? If I go to, uh, if I go to, uh, year two and we do the acquisition, first year of earnings that occurs is year three, if I say equals and go and grab that 7.5% and grab all of these 7.5% numbers, that is wrong.

Now, the reason it's wrong to do that is the, if we acquire a business in year two, we get the earnings in the next year. In year three, we're not immediately gonna hit as synergy run rates.

Actually, what it should look like is we should grab 3%, 5%, seven and a half percent, et cetera.

That is what the numbers should look like.

What I want is an automated way of going to go and select the appropriate synergy percentage.

So for example, let me change the acquisition year to year four.

If we acquire the business in year four, then we're acquiring the business there in year four. The earnings come in year five, and I would need that to read three per 3%, yeah. And then 5% and then seven and a half percent thereafter.

So it's not really working properly at the moment.

I'm gonna put that back to year two because I've just hardcoded it. But we can definitely do better than that.

Now, the formula work we're about to do is gonna be a little bit complicated.

I think the formula's gonna end up being quite quite large.

So I'm gonna just put my formula text here so you can see what I'm doing.

Um, in fact, I'm gonna do it, I'm gonna do it here.

I'm gonna build it here. Okay? So I'm gonna build it in year three just 'cause as I'm building it, you'll see some interesting stuff happening.

Uh, so I guess the first thing I'm gonna say is, uh, uh, equals, uh, yeah, I'll do it here equals if open bracket.

So we're gonna do an if statement and, um, we're basically in the if statement, gonna say, look, if the, um, uh, if the cell, uh, G 25, okay, above, if the cell G 25, these switches or the, you might call them flags.

If the cell, uh, above I 25 for us, sorry, uh, is one, then I want you to go and do a calculation.

But if it's not one, I don't, I want you to do nothing.

So you could say equals, if the seller above equals one, which it does comma, then do some kind of calculation, comma do zero.

Now let's think about the calculation.

I'm gonna to use an index function, okay? I'm gonna say IND and if you part type in any function I've typed in IND Excel uses predictive text and you can see it thinks I'm probably wanna use the index function.

You can hit tab and it always complete the function and insert the bracket for you and it gives you the syntax.

So, uh, we're gonna say to excel, look, uh, what I really want you to return here, right? Because if we do the acquisition in year two, and this is the first year post acquisition, I really want you to return 3%.

So I'm gonna use the index function to try and do that.

If I say index and look at the prompt here, look at the syntax, it wants an array, which is a range, I'm gonna go and grab the synergy numbers, which is what I wanna return, okay? So I mean, I'm doing this in column I, I've said if I 25 equals one, then do index of G 24 to O 24.

Now I'm gonna copy this left and right, this formula, and I always wanted to look at that array.

So I'm gonna press F four to lock that.

So do the index of that range, comma.

Now just think about this slowly.

What do you want this to return? You want this, uh, to return, uh, it says row number, uh, a row of zero.

You don't wanna go down any rows, you know, just row there, there are no rows, there's only, you know, there's only one, uh, one row in the whole array. So I'm gonna put a zero in here, comma.

And then can you see in bold it says column number, uh, I want column one actually gonna hard code in a one there completely unsatisfactory thing to do to hard code in a one, um, close bracket.

So, uh, we'll, we'll fix that hard coded one in a minute.

So if I 25 equals one, go and do index G 24 to O 24, um, no in the index, don't check, move down the rows, uh, uh, just go for a zero and pick the first, uh, pick the first column.

Now if, if you look at the if statement, if I 25 equal one, if it doesn't equals one comma zero, so at the moment it's gonna return 3%, which is actually what you want, right? So it's looking at this index function, this array, and it's choosing the first column.

If we copy it out to the right, we need it to choose the second column.

So this is a horrible thing to do, but I could hard code that to a two and it would pick 5%.

So, but I know that hard coding is not nice, but I'm just trying to show you what I'm trying to achieve.

And, and obviously the problem that we have here is if we, um, if we change the, um, if we change the exit year, this isn't gonna work very well, right? You know, if I change the exit year from a two to a four and everything moves to the side, it's not really gonna, it's not really gonna work properly.

So I need, uh, I need some way in the formula of arriving at a one.

If you are in year three, I need some way in the formula of arriving at a two for the column number.

If you're in year four, I need some way of arriving at a three in the formula if you're in year five.

Now, let's try and make the connection here.

Look at those year count numbers, 3, 4, 5.

And in the formula you require a one, you require, oh, sorry, A one there, A two and a three.

How can I go from three to return a one? Look at the, look at the entry here, it's a two, three minus two is one, four minus two is two, which is the number we'd be looking for.

Five minus two is three.

So I'm gonna make an amendment here rather than that a hard have a hard coded one.

I'm gonna delete that and I'm gonna go and grab the year count number minus, and I'm okay for that not to be locked at the moment.

Minus, excuse me, the acquisition year.

So the add-on year, so I 14 minus add-on year is one.

So if you, if I, if I look at that formula, CTRL Z and control y, if I toggle that controls it, look at what's changing.

Actually, nothing's changing. It's just, rather than have a hard coded one, I've substituted in this formula.

Now if I copy this out to the right control R we get the same numbers.

What if I copy it to the left? Is this gonna screw up Control C and control V? Oh no, this looks all right. Just return 0%.

Why does it return as 0%? 'cause it says if I 25 equals one, which is false.

Yeah, because the, the, the, the switch isn't a one, it's a zero.

So therefore that's why it's returning 0%. Okay? That is, uh, maybe this is gonna be a relief to you. That is the heaviest formula work we have to do in the entire model.

And actually some of the rest of the model is really straightforward and some of it's a bit more involved.

But that's, that's really the most work we've got do. But it's a very, very cool piece of formula work, I reckon.

Uh, and I'm not being narcissistic because I didn't write the model.

One of my colleagues wrote it, okay? Um, so we've done some work on, um, the synergies.

If we scroll down, we've got add-on financials, and this looks great.

We've got the sales and the ebitda.

If you've, we've got depreciation, we've got CapEx, we've got, uh, we'll have change in o WC probably occurs to you that you've got really everything you need to be able to calculate flow, which you would want so that you can figure out the ending debt, right? In any given year. Uh, but the problem is that, uh, thinking again that the acquisition is happening in year two, these numbers can't be right.

We, we can't have sales in year one or year two.

We'd have to have, if the acquisition happens at the end of year two, we'd have to have sales coming in post year two.

Now, the good news is that a lot of the work here has been done for us.

If you cast your eye down to row 38, row 38 and down, it's really the same as row 29 and down.

They, you know, they're really the same numbers, but there's a little bit of extra formula work that's been added in.

So for example, for the sales, what we do is we take the sales amount and we multiply it by the add-on switch.

So before the add-ons happened, the add-on switch is zero.

And so that brings through sales of zero for the first couple of years because we hadn't acquired the company.

And thereafter we've got, uh, we've got more sales, uh, well, we've got the sales coming in from the appropriate year.

So, um, we've also got EBIT dull, okay, coming in the same way.

And we've got, um, we've got depreciation and we've got e we've uh, we've got depreciation and EBIT all, all kind of flowing down.

So that's all good. We are missing the synergies.

And I just wanna say that you could potentially go and grab, uh, you could potentially go and grab the synergies numbers that we've just calculated and multiply it by the sales number above, like, so there's only one caveat.

It's, it's maybe a slight error or just the way this has been presented.

I just wanna improve that a bit.

So I'm kind of cool with that.

Uh, I'm kind of good with that.

But let's just look carefully at the synergy. Something's caught my eye here.

Uh, it says synergy percentage of prior year sales.

So that's quite a typical thing to do.

Look at what the sales are in the previous year, maybe the LTM sales, and then figure out what you think, uh, the, uh, synergy would be going forward.

The cost saving would be going forward.

So it's, it's almost correct, but it just needs to be, I guess something like that.

Well, that's not gonna work, is it? Uh, unfortunately it's not gonna work like that because the cells aren't being pulled through.

Uh, so what we could do to correct that is we could say, well, why don't you grab the synergy and multiply it by the prior year here, and then if you copy that across, okay, uh, you know something, I dunno, something like that.

I dunno, not, it's not quite working, is it? So we're gonna do a little bit of formula work here to fix this.

It won't be a massive formula, okay? But we're gonna use, uh, uh, to make this work, we're gonna use the index function.

So I'm gonna start in, um, column G and I'm gonna say equals IND index.

And uh, Excel's gonna say, well, what are you looking for? Well, I'm gonna go to G 29, which is the cells from above, and I'm gonna go grab G 29 to, it looks like O 29. So I'm gonna grab all the cells, I'm gonna copy this formula out to the right and I always wanna look at that array.

So I'm gonna press F four to lock that, okay? Uh, now comma Excel says, great, we've got this array. What row number do you want? I'm just gonna put a zero in there. Ignore that. We don't worry about the row number.

Comma, what column number do you want? Well, I wanna pick the previous year's sales.

So I want to go and grab, I guess numbers from here, numbers from here, you know, if I'm in 2054, I wanna pick the numbers from here and, and so on.

So what I'm gonna do, um, is I'm gonna go up and grab the add-on year and then close bracket multiplied by, and I'm gonna go and grab G 26, which is my syner.

So I'm not gonna get anything at all coming through in the first year because you know, we have, uh, no switch, no happening there.

So there's no synergy percentage coming through.

If we copy this out to the right, like so, so in the third year we've got synergies of 3.9 and that's three, that's 3% of there of the prior year, sales of a hundred and of 130.

So we can test that because if we look at the add-on year and you highlight that, you can see that there's a two there.

So in this array, this index array, it's choosing the second item, which is the, uh, and in fact, as you select it there, you can kind of tell, uh, the second item, which is 130, okay? Uh, so now we've got all the numbers pulled through.

What we now need to do is to uh, go to the base case, add-on.

Now the base case add-on is, uh, the combination.

So it's where everything kind of comes, uh, comes together.

If I click on that base case, add-on, uh, and let's just have a look at what we're trying to achieve here.

I'm gonna go back to the top, okay? It's worth drawing to your attention that at the very top of the model there is another switch and it's called the acquisition add-on switch.

So in the previous sheet we had a switch that sensitized when the add-on was gonna happen, was it gonna happen in year two or year three? Uh, and that is appropriate so that we could see how that might impact the internal rate of return.

But it strikes me that we should also have the ability to totally flick off the add-on.

So it would be good if we could, doesn't matter when you think the, what year you think the add-on might happen, let's have a switch or we can totally disable it so it just never, uh, gets pulled through into the numbers and that's what that switch does. And if I, if I click on that cell, it's actually got a, uh, it's actually got A-A-A-A-A label there.

Um, it's actually called, uh, it's called add-on year.

You can't quite see it 'cause it's slightly obs good, but it's called uh, that's what add-on switch it's called.

Okay? So um, another side, we've got a pretty standard LBO here.

We've got an income statement and we've got a cash flow statement to help us figure out how much debt we can pay down.

And then we've got a debt schedule and at the bottom we've got the IRR.

So that's what we're gonna try and fill out now over the next sort of half an hour-ish.

Okay? So if we look at the income statement, you could say look at the net income at the bottom and uh, or you could look at the uh, EBITDA down here or EBIT, sorry down here.

And you could say that is the EBIT from the original acquisition, the base case and the add-on.

And I would say, well actually it isn't.

It should be, but it isn't.

And the reason it isn't is because we've got a few things we need to work on in the file.

Let's show some formulas here.

So I've got the base case sales, they actually have all been already been populated for us and they come from the base case tab.

I now need the add-on sales.

So if I say equals, this is really, really easy. Okay? If I say equals, 'cause we've done all the work already.

If we go to the add-on sheet and uh, the sales appears in either row 29 or row 38, but we definitely wanna grab row 38, okay? So if I go and grab sell G 38, which actually is a zero because the add-on isn't happening until year two. So we're not getting the earnings until year three, but that's fine if I go and grab G 38.

Now I can't really press enter because if I press enter it will start pulling through sales in year three.

But I don't want it to do that if we're not gonna go ahead with the add-on acquisition to be able to control that.

I'm gonna multiply by, and I'm gonna go back to the base case add-on sheet and remember at the top I highlighted to you there was an add-on switch.

So I'm gonna multiply it by the add-on switch and it's called add-on switch and then press enter.

So what that allows us to do, if I copied it out to the right we, it allows us to kind of see the base case sales and also the add-on sales.

But if we, if you said, look, I wanna look at some data table analysis, I wanna look at IRR, what would it look like without the add-on in the model? You could change the add-on switch to a zero and then the add-on sales would never come through.

Doesn't matter what year you think the add-on might happen.

If ultimately you say the add-on won't happen, it's not gonna happen, then we want it to come through as zero, let's just put that back to a one and have them pull through equally, you can go back to the add-on sheet and change the uh, acquisition year and then it'll acquire in whatever the appropriate year is. So we've got quite a decent amount of control there.

We're gonna do exactly the same for EBIT. So I'm gonna say eCourse, I'm gonna go back to the add-on sheet here.

And in the add-on sheet, uh, I'm gonna go to column G and I want to get to EBIT, which I think is G.

It looks to me like it's G 43.

I'm also gonna multiply that by the add on switch.

You can just type in a D and Excel will guess that you wanted to use the add-on switch cell reference.

And so you can hit tab and that's a good way of calling that cell.

Nothing very radical happens, but if we copy that out to the right, then we can see EBIT flowing through in year three for two reasons.

One, because we're doing the add-on in year two, so the earnings were received a year after that.

And the second reason is the add-on switch is set to one.

Okay? Um, so that's really the income statement done. It means that when you look at the net income, you can be supremely confident, but that net income includes both LBO deals, okay? It's pulling through both, which is kind of useful because if we look at the cash flow to service debt calculation, it starts with net income.

So we started from a, a good place, but you guys know that net income and cash flow are not the same thing.

They are somewhere apart.

And so in order to move from one to the other, there are various things we'd need to do.

For example, pick up CapEx and changes in O WC and add back depreciation.

And that work has mostly been done for us.

But if you look at the purple rows that I've highlighted and perhaps I show you some formulas, we are missing the depreciation from the add-on and we are also missing the CapEx from the add-on. Now that's easily fixed for the depreciation.

It causes exactly the same idea.

We're gonna go back to the add-on sheet and we're gonna go and grab the depreciation, which I think is in G 42.

And we're gonna multiply it by add-on switch and I'm gonna copy that out to the right formula is on the screen for the CapEx, we're gonna do the same, just noting that the there is CapEx on the base case in the row above already and that CapEx on the base case is shown negatively.

I, I guess of course it is because of flow statement, it's cash outflow, but just noting it's negative.

So if I say equals for the CapEx, I'm gonna go back to the add-on sheet, I'm gonna grab the CapEx, which is a zero.

Not very clear is it if it's a zero, but look, in year three we start to get some CapEx and it's shown positively.

So I'm gonna grab that number, I'm gonna multiply it by minus one and I'm gonna multiply it by add-on switch and nothing really happens.

But if I copy it out to the right, I'm really hoping it comes through as a negative number and indeed it does.

So now we can be supremely confident that the net income includes both organizations.

So that's both LBOs, so that's good.

And that is flowing down correctly for the cashflow available for debt repayment, which also includes both LBOs, they're, they're all pulled together.

So if we go further down to road 46, which I'm gonna put in bold, we've got cash available for debt repayment.

So that is the cash available, uh, from both these companies that we've done an LBO for. They're spitting out cash, both of them together and we need to compare that to the debt schedule.

But both those deals, now it's worth just slowing down on this bit a little bit.

Let's just slow it down and have a think about what we're looking at here.

So I am seeing in the first four lines of the debt servicing section, I'm seeing base case senior debt, which looks to be 1, 1 2, 5 0.2. That's an important number. 1 1 2, 5 0.2.

And if you scroll down a bit, miss out those purple rows, I'm then seeing the beginning unsecured notes, which is 5 6 2 0.656 2.6.

So 1 1 2 5 and 5 6 2. Where do those numbers come from? If I can take you way, way, way back to the base case, these must come from the sources of funds in the base case.

So let's go back to the base case.

Let's check out the sources of funds, which is on the right hand side.

Look, the senior debt is 1 1 2, 5 0.2 and the unsecured notes to 5 6 2 0.6.

So that's where those numbers come from.

Let's go back to the add-on.

That's where they uh, where they come from.

But what, what do we have hanging out in the middle here? It says add-on beginning senior debt.

So here's the situation.

We do the first LBO, we get some senior debt and we get some unsecured notes done.

We then go forward in time a couple of years and do the add-on LBO.

We'll need to borrow money to make that happen 'cause it's leveraged buyout.

And so when we uh, take on debt to do that deal, we take on senior debt.

Where does that senior debt sit in the capital stack? Well, uh, it's gonna be subordinate to the existing senior debt. There's no way the existing senior debt is gonna allow 'em to leapfrog over the top of them.

So it's gonna be subordinate to them, but it will be senior to the unsecured notes.

And so it sits here for, for very good reason.

Now let's figure out how much we've got and uh, and how much we can pay down.

Curiously, if you look at the ending senior debt balance in the historic year, it's a hard coded zero.

That didn't seem right to me.

I thought if you go to the add-on sheet and you look at their sources of funds, I thought it was 146.

So a decent question for us to ask ourselves is if the debt you're gonna issue for that second deal is 146.6, when you go back to the base case add-on why is this ending debt number from the prior year, not 146.6? And then I'm reminded that of course they're not doing this second deal in year zero.

In fact they're doing it in year two.

So you'd kind of imagine perhaps at the end of the, not the first year, but the second year you'd have ending 1 4, 6 0.6.

It's no good hard coding that in that's not gonna work.

Um, so let's get rid of that and let's try and build this a bit.

I'm gonna do a few formulas down here.

Let's copy this down.

Okay, so I'm in row 54. This is super, super easy.

What we're saying is what is the cash available? What is the cash available for the senior debt? Now, uh, uh, senior add-on debt, if we take the cash available for debt repayment of 1 76 0.1, which is in row in cell G 46.

And and then we pick up the repayment of that senior debt, which is in LG 50 and we get zero.

Now I'm not that surprised we get zero because this is not the, the most senior ranking debt. It sits below the existing senior debt.

In fact, if I copied that out to the right, it's gonna be zero in many many of the years until, if you look in 2057, we've almost paid down that senior debt and we get to 2058, we're able, we've got enough cash flow to fully pay it down and it hit zero.

Then we've got some extra cash and that will be directed towards the next layer of debt.

Next tranche of debt, which we're about to build.

Okay, so we want to now pick up the debt for the add-on and the first line is row 56 and it's super easy.

What's the beginning debt? It's zero.

Now the next thing we're gonna do is just a little, little bit more involved.

Not massively difficult but a bit more involved.

Um, it says issuance.

Now in year one the issuance would be zero and repayment is gonna be zero.

And so the ending amount would be zero.

We should kind of flow up and I know the issuance in year two would be 1 4, 6 0.6.

I don't think we'd be repaying anything of that straight away 'cause the essence would happen at the end of the year, not at the beginning of the year.

So I'm gonna put a zero in there and then the ending amount would be 1 4 6 0.6.

It's horrible for me to be hard coding these numbers in.

So let's try a bit better by delete this.

Uh, in the first projected year, the beginning debt is gonna be the same as the ending debt in the prior year.

The issuance, I'm gonna say equals and this is probably another fairly tough formula, maybe not as hard as the other one we looked at.

I'm gonna say equals if open bracket.

Now I only wanna pull through this debt issuance if and when and for one time only actually when the year count is equal to the entry year for that add-on LBO.

So I'm gonna say if I want to go and find a year count, if we scroll up here and we go to it's in cell G 19, can you see that G 19 has got year count one? So if the year count for the year we're in is equal to now I'm gonna go back to the add-on sheet and I'm gonna go and grab at the very top of the add-on sheet the number two, which is in cell F seven, which is the add-on year.

So if the year in is the same as the add-on year, what I want you to go and do Excel is I want you to go and grab the senior debt balance for the add-on.

So if G 19, which is the year count is the same as the add-on year, which happens to be two and how we've got it set up, then please go and grab the senior debt balance.

I think we better lock that with F four 'cause we're gonna with F four 'cause we're gonna end up copying this out to the right.

Uh, and one other thing, I don't necessarily want it to go and grab that debt issuance just because the years are the same as we've said before.

What if we decide just not to do the add-on LBO at all? Well then you wouldn't want it to pull through anything.

So I'm gonna multiply it by, I'm gonna go back to the base case add-on.

I'm gonna multiply it by the addon switch.

Um, it is an if statement, if those two things, if G nine is equal to the add-on year, go and grab basically the, the debt in oh seven for the sources of funds multiplied by the add-on switch, comma, otherwise just don't do anything.

And so it doesn't do anything actually, uh, let's just copy those out a few years.

So we can now see the issuances happening in the second year, which is that exactly what I want.

The repayment is really easy.

I'm gonna say equals min, choose the minimum of the cash available for the debt repayment and the beginning balance.

I'm not gonna include the issuance because uh, the issuant, if we're issuing debt, the add-on happens at the very end of the year.

I just don't think it would be realistic to issue debt on the last day of the year and then also pay it back on the last day of the year.

I don't think that would be sensible. So we're gonna leave it as it's close bracket, multiply by minus one all equals equals to sum it up.

Now if I copy the whole lot out, right, so this looks pretty cool, what you can see is that the debt starts at zero and then in the first year it's still zero.

'cause we haven't done the add-on acquisition in year two.

We've won, got the add-on switch at one, so we're doing the add-on acquisition and two, we've got the add-on year sector two.

So it's coming through in year two as an issuance.

We don't repay any of it in the year it's issued of course, or, or in the next year or the year after that.

And you'd say why? Well, because you haven't got any cash.

'cause all the cash is being diverted to the senior debt from the base case acquisition.

But then as soon as we get into the, the following year, which is 2058, I'm losing my track of where the year counts are, but it's 2058.

I can go up to G 19 to have a look. It's year five.

So as soon as you get into year five, we've got some cash kicking around and then we can start to pay it down and then it gets paid down and paid down and then we've got some surplus cash, uh, subsequent to that. And that will be used to pay down the unsecured notes, but we don't have to do any work on that because well, that's been filled out for us already.

Okay, so this is pretty cool.

We've, uh, in the LBO, we've got an exit multiple.

We forecasted out the, uh, exit EBITDA number by combining these two, the, these two deals and we've got the exit debt.

But, but let's just do a bit more work on this.

I really love this section here.

I didn't write this, uh, model, one of my colleagues wrote this and I think it's brilliant.

So rather than us just say, yeah, let's just go and borrow more money and you know, we'll this will all be okay and we'll just, you know, we'll see what return we make.

We're gonna put the brakes on a bit and we're gonna say, uh, typically, uh, it might be in real answer, it might be something like 50% of the debt needs to be paid down, uh, by the time you get to year seven.

And that is likely, uh, a metric that the credit committee would uh, apply before these loans were approved.

So I wanna do this LBO, we're gonna borrow loads of money and someone's gonna say, well, hang on a minute. You can't just, you can't just arbitrarily borrow loads of money.

You know, we need to test stuff like we need to look at leverage coverage and we also need to be comfortable that can actually pay a decent amount of this debt down.

So that's what this does. This is very, very cool the way this works, I think.

So we've got, um, here, uh, this section on debt repaid.

So in the first year we were repaying some of the senior, um, debt from the, the, the base, uh, base case acquisition that's paid down.

And then that kind of accumulates up. So every year you can kind of see accumulating the accumulation of the debt we've paid down.

Now we would want to compare that to the total debt we've drawn down.

So if you look, uh, uh, in row 78, we can see that the, at the moment we can see the, uh, the, the base case senior debt we've drawn down, uh, uh, and it must be the, it's the senior debt and it's the unsecured notes that's drawn down.

And so we haven't really updated this for our new LBO deal or our add-on, but as it stands, you can see that in the first year 10% of the debt is paid off.

And by the time you get to, I dunno what year that is, year 1, 2, 3, 4, 5, 6, 7, 7, maybe it's seven, it's paying down a hundred percent, which looks pretty, pretty safe to me. Pretty good. But we haven't added in the uh, uh, we haven't added in the add-on, uh, LBO debt numbers.

So we may be better review that.

Uh, if I wanna pick up the add-on debt repayment, it's really, really easy, okay, to do that.

So I'm gonna go to sell G 74.

I'm gonna say equals I'm gonna go up and grab from the debt servicing sheet.

Can you see, let me pause over it.

If you look at G 58, you can see that this is the repayment. And just notice the repayment comes through negatively.

I want it to display positively. So I'm gonna multiply it by minus one.

So if we copied it out to the right, let's just make sure that works.

And I'm gonna just show you the formula.

So in row 74 it's equal to G 78 and you can start to see some of that debt repayment happening there.

Uh, if we scroll down and have a look at the percentage of debt repaid, which is row one, 10.4%, 22.8%.

And if we keep going and looking at that, this can't be right.

Look, eventually it goes to 108%, 108.7%, you can't be paying back more debt than you issued.

And that's because we haven't dealt with the, uh, drawdown of the add-on debt. We need to kind of fill that in.

So, um, what we want, if you look at what happens to the, uh, base case drawdown is uh, what we want is, uh, we want as soon as the debt's issued, which is in year two, we want it to appear and every year thereafter.

So because we want every year thereafter we're gonna use nif statement, we're gonna say equals if, and we're gonna go and grab the year count number, which is G 19.

I remember that from earlier. Let's go and find it.

So if the year count G 19 is greater than or equal to the add on year, I'm gonna just type it in a DD add on year.

So if the account is greater than or equal to the add, add on year comma value if true, what I want you to do is I want you to go to the add-on sheet.

I want you to go to the sources of funds and I want to go and grab the senior debt balance, which I'm gonna press F four on 'cause we're gonna copy it out to the right and the left.

I'm gonna multiply that by the add-on switch comma So if G 19 the year count is greater than or equal to the add-on year, then go to the, uh, add-on acquisitions, sources of funds and grab their debt issuance and multiply that by the add-on switch.

So if we don't do the add-on at all, it just comes through a zero, but if it's not greater than, than than the add-on year, then just return a zero.

And if we hit enter, that's exactly what Excel does.

Might just move that formula up here so you can see not all of it.

Okay, so presumably if we copy this out to the right it, it comes through with the debt balance and if we copy it out to the right, again, it comes through with the debt balance.

So as soon as we issue that debt, it just kind of sits there and that's now nice. So you can see that it's a hundred percent paid off by 2060.

Um, is the year count we're looking at, it doesn't go beyond a hundred percent.

So that's nice. So I think that probably passes that test.

We've got eight minutes left, which isn't that much time.

Uh, and all we really need to do is the the returns.

So if I can ask you guys to, uh, cast your eye down to row 88 and below, we need to go and grab the a number of numbers starting with the total, oops, EBIT.

Okay, so total EBIT d I'm gonna say equals if we go up further up the model, we go and have a look at, uh, the income statement.

I haven't got total EBIT D in here, but I've got EBIT.

So if I've got EBIT and that look at, look at it, it includes the, uh, EBIT, um, from the base case and also the EBIT from the add-on and any cost savings on that base case. So that's the entire EBIT I'm gonna add to that.

The DNA, if we scroll down to the cashflow statement, can you see I've got 71.3 in G 37, which is the depreciation and base case plus I've got zero in G 38, which is the depreciation on the add-on.

And that's just because the add-on year is further forward.

So that gives us the total EBITDA number.

We now wanna calculate the enterprise value, that's really easy.

So the enterprise value would be the product of the exit multiple, which is somewhere up here I think. There we go. So can you guys see in F 10 we've got 11 x and that is the maximum X at E-V-L-T-M EBITDA multiple.

I'm gonna press F four to lock that and I'm gonna multiply it by the EBITDA directly above.

I've got 4 0 8, 8 0.6.

Um, I now want the net debt, which I wanna show negatively. If you look at the heading there. So I'm gonna say equals and I'm gonna go and find the cash some, oh, there it is, it's in row 69.

So if we grab the cash, which is zero in row 69, and we subtract the debt, which is in G 83.

So if we take G 69, which is zero minus G 83, which is 1 5 1 1 0.7, we get debt coming through negatively.

And if you wanted to get to the equity value, we'd add up the enterprise value and the net debt and that gets us the equity value.

That looks pretty good. Now I'm gonna copy this out to the right and the next thing I'm gonna do is totally, totally standard.

Uh, there is a line below that I'm gonna ignore at my peril because it's actually quite important, but I'm going to ignore it for now.

And I'm, I'm just gonna go grab the cashflow to equity holders in a very standard way as I would do for any LBO I'm gonna say equals if open bracket.

Now we're gonna do a logical test and I wanna say if the, uh, X year in the entire deal is equal to this year, go and grab down that equity number.

So if, let's go, let's go. Whoops, grab that.

There we go. So if, let's go up and grab I the exit year.

Can you see an F 16? I've got an exit year five, I'm gonna lock that with F four.

So if that is equal to, I think it was G 19, which is the, the year we're in.

So G 19. So if the exit year in F 16 is equal to G 19 comma, go and grab the equity number from above, comma, otherwise value of false do zero.

So if I copy that out to the right, that is a totally, totally standard formula.

Gonna show you my formula text for an LBO and that means that we actually can just go and calculate the IRR.

I'm gonna calculate the IRR now because IRR of all of these numbers done, 29.7 sounds pretty good to me, but I'm suddenly thinking I'm, the way I've modeled this, I'm getting money for nothing because I'm assuming in year zero we're gonna pay out some equity to buy the base case LBO.

And then in year five we're going to get some equity coming back from the base case LBO and the add-on LBO.

Hang on a minute. Shouldn't we be modeling in the equity we spending on the add-on LBO? And the answer is, well, yeah, we should really, so what I'm gonna do is little bit of work in this.

Um, let's just show my formula text.

Yeah, or maybe it's already set up.

So little bit of work in this.

So I want to go and get the equity investment add-on.

I'm gonna say equals if open bracket now G 19, which is the year count if G 19 equals the add on year.

So if, uh, this is year one, if that's the add on year, it isn't right. The add on year was year two, but if they were the same comma, then I'd want you to go to the add-on sheet.

I'd want you to go to the sources of funds and go and grab the equity value, which I'm gonna lock with F four and multiply by minus one.

So if we just pause on that for a minute, I'm saying that if this given year is the same as our add-on year, then you, you must really go and grab the equity investment of 34.6 and show it as a negative and Excel will say what? So I should just, as long as those years are same, I'm sure we show it, right? Well, actually thinking about it, no, I wouldn't want you to pull the equity through if we weren't doing the add-on acquisition at all.

So I'm gonna multiply it by the add on switch.

Now what if they're not the same? Uh, well just do nothing on a zero.

So if we now copy that out to the right, hopefully in the second year, you can see that 34.6 coming through.

What I wanna do is I want, I need to add that onto my cashflow to equity holders so it flows into my IRR calculation.

I'm just gonna bolt it onto the end of that calculation.

I don't need to test for if we're doing the add-on or we're not doing the add-on or the year.

'cause I've done all that in row 92 already.

If I add that in and copy that out, now that that comes through and it updates our IR to 29.4.

Now the question is, and we've got two minutes to go, the question is, is it worth doing the, uh, the add-on acquisition? Then if you go back to the base case, I know we haven't got interest flown through, but we we're just gonna compare them without that.

If you look at the base case, you can see it was 27.8.

If you look at the base case add-on, it's 29.4.

So that suggests that we should do it because we've got a better rr but I've got a caution here.

Uh, the base case business was bought at 11 x and we exited at 11 x.

The add-on business was bought at six x and we are assuming we're gonna exit at, let's go and find an 11 x.

So we're gonna bootstrap the add-on acquisition up to the multiple of the broader company Now on the desk, that is quite a typical way for it to be modeled, but a word of caution, what you are doing is you, are, you, you are potentially generating your enhanced IRR simply from a multiple expansion.

Let's just test something before we finish.

If I change that multiple to 11 x for the add-on acquisition, and then we go and look at the re review, the IRR, it's at 27.8 and previously it was at 27.8.

So in fact, what I'm gonna conclude might sort of summing up is for these numbers, the add-on acquisition doesn't get you any extra return, unless of course you are under the view that you, you can, you can, uh, expand the multiple, uh, by integrating that one business into the other business.

So it's through multiple expansion, okay? We've gone right up to the limit on that.

So guys, thanks so much for being dialed in.

I really appreciate it.

I hope that's been interesting, uh, for you and uh, I really look forward to seeing you in another session.

Cheers guys. Take care. I see you soon.