So, just a little introduction. First of all my name's Phil Sparks and I'm an instructor at Financial Edge. And welcome to this latest in series of our Friday webinars.

This one's all about LBO fundamentals. So we'll spend just a little bit of time telling you what an LBO is and how it works.

And then we're gonna spend most of the time diving into a fairly substantial Excel based model.

That's gonna take us through a typical LBO transaction.

And in doing that, we'll look at different sorts of debts. We'll look at the absolute key to an LBO, which is the cash sweep mechanism.

And we'll also finish off by looking at what the credit ratios end up as, and also the returns to the equity holders.

So just before we do a little bit of housekeeping, my colleague Agata has put on the chat box already that a link to where you get the resources.

you should be able to, if you follow that link, you'll be able to download the two Excel spreadsheets.

One is an empty version and one's a full version.

We're basically gonna spend today converting the empty one into a full version.

So first thing I'm gonna do is I'm just gonna open up a few slides and just de describe what an LBO is, and very briefly how it's works.

And so here we've got a picture of two of our fairly standard EV to equity bridge, the enterprise value of the business financed by the debt or by equity.

So on entry let me just get a pencil so I can scribble on this.

On entry, we have a fairly sizable EV but the absolute key to an LBO and to making an LBO work is that the majority of finance to make that acquisition is by way of debt giving only a relatively small amounts of equity down here at the bottom.

And that's what the PE firm, that's what the private equity fund puts in.

It tries to finance as much as it possibly can with debt, wait two or three years.

And a number of things have happened.

Hopefully the enterprise value has grown a little bit.

Now this is not necessarily because we've invested a great deal of money.

Because we, what we really want is we want this thing to throw off cash as we trade.

So this is normally about saving costs. It's about squeezing the business a little bit, improving its profitability, improving its efficiency and by doing that, profit goes up, EBIT goes up, EBITDA goes up, and then if you're account valuing the business on an EBITDA multiple, the enterprise value has gone up.

If the enterprise value go goes up, then the equity goes up.

But important, as well as the EV going up, the debt should go down.

Basically, the profitability of the business has been largely used in paying down the debt.

And that debt shrinks.

So with the rise in equity, sorry the rise in enterprise value and the shrinking of the debt, then the equity gets much, much bigger.

And it's that equity growth that gives the PE firm.

It's return.

Let's just jump back a few slides, and they've got a nice slide at the beginning, which basically looks at what a typical LBO is.

And a few things just to bring out.

The first thing is it's a financial transaction.

Their PE firm isn't looking for synergies.

It's just purely looking for a financial return for the transaction.

They have a relatively short year, short three to five year investment horizon, and they'll be looking to make something like 20 to, 25% IRR rate of annual rate of return over that period.

As we've seen already, a lot of the transaction is financed with debts. That's, it's in the name leverage buyout, lots of leverage.

And therefore, a big part is financed with debt rather than just with equity.

And then we are hoping that the business generates a lot of cash.

So generally what we're looking for is stable, mature businesses that don't require a lot of investment, don't require a lot of capital expenditure, aren't looking for loads of growth, which will absorb working capital.

Basically, we're looking for a relatively steady business that throws off cash.

And that cash then enables the business to pay down the debt to squeeze the debt, which in turn gives them that rise in their equity investments.

And finally some operational improvements. What we're normally talking about here is cost savings.

We're looking for some cost savings which improve the profitability, generate cash, and improve the valuation at exits.

Okay, just gonna look at one more here.

And here we've got a typical analysis. Now we're gonna spend most of our time in a fairly meaty spreadsheet, so we don't wanna overplay this, but just looking very briefly at this here's our entry.

Here's us buying the business.

So we've got an EBITDA and an EBITDA multiple saying that the enterprise value of the business we're gonna buy is 217.

We financed that on entry with a 50 50 mix of debt and equity.

And then four years later, and you can see it's four years and the IRR calculation down here, four years later, we've had some operating profits improvements.

And that gives us a very significant rise in enterprise value.

We've also paid down some of the debt.

So we're subtracting less from the enterprise value to get to our equity, and therefore we find that our equity stake has hugely improved, improved by something like three and a half times, which gives us that very healthy in this case.

336.1 average IRR. Okay.

So let's jump into our spreadsheet.

So we're gonna open up our spreadsheets.

And I would suggest if you have got this, if you can download this, that you download this spreadsheet as well, just to give you a sense of the structure of this thing. So you've got something to look at as we are going.

So let's have a very quick look through this spreadsheet.

It's empty at the moment.

Let's have a quick look through this.

We're basically gonna spend the next 45 50 minutes or so constructing this spreadsheet.

So let's have a little look at it.

It's based on a real company. It's based on Tumi, the upmarket luggage producer based in the US.

It's a few years old.

These numbers were captured from to me, a little, just a little bit before the covid pandemic, something like year something like 20, 17, or 18, something like that.

And this is the share price at the time.

So we've got lots of detail, standard detail of our acquisition, so we can value it both on an equity and then an enterprise value basis.

We've got some assumptions up here in terms of interest rates and fees and so on.

Then we've got a fairly standard sources and uses of funds section down here, as we will do for most acquisitions.

Now, you can see we've got here a list of the debt that we are assuming and what would normally happen with an LBO. You'd identify a target out how much you're gonna buy that target for, and then you go to a bank or more likely a syndicate of banks and say, how much can we possibly borrow to finance this, to finance this acquisition? And what we've got here are the answer from the bank.

Basically, the bank said, you know, the bank would've said, you know, this sort of mix of debt.

And we'll talk a little bit about each of these in just a little while.

And fundamentally, what you're doing with an LBO is trying to borrow as much as you possibly can.

Because that leverages what, what's gonna give you that sort of magnifying effect when you look at your return on equity.

We'll also look at some multiples here.

When we've calculated this, when we've identified things like the EBITDA so that we can work out typical multiples that the banks would lend on, we've then got a very long section on debt financing.

And this is really looking at how the business is financed initially with a lot of debt and how quickly the business can repay that debt.

Now, inevitably with any model, and particularly something in LBO, you do quite often find yourself jumping between sections.

You need to calculate some something before you can then work out something else.

And this will be the case here, we'll have to calculate the debt.

We'll have to identify the starting point for the debt before we then later on in the spreadsheet to a cash sweep, which looks at how our cash can repay that debt.

That in turn, will enable us to look at the movements on debt Only when we've done that, can we calculate the, in the interest on those on, on the debt, and finally construct the interest within the income statements and the cash flow.

So we will find ourselves jumping around a little bit with this spreadsheet.

Look down a little bit further.

And we've got an income statement. We've got the current forecast numbers, and we also have some implied or assumed cost savings that we're going to make when we take over this business, relatively modest 10.

And let's assume these are all in millions, $10 million per annum.

We've then got an income statement, and you'll see I've got normal stuff like EBIT and revenue, but then I've got a great big load of different interest, interest on all of the different tranches of debt.

And that's a really significant factor, um, within an LBO model, simply because we borrow so much to finance the model in the first place. Then we've got our cash statements our cashflow statements, and again, it's got most of the standard stuff, but a lot of interest.

We need to be careful because some of the interest is gonna be paid in cash every year, and some of it is gonna be accrued there. Pick loans or payments in kind loans.

And then we've got this section here, and this is the real key to understanding an LBO. It's this part here. Apologies for the, the steam train sound in the background.

It's not a kids' toys. It really is a steam train a little bit away from where I'm at the moment.

And then we've got the section here, which is the cash sweep. This is the real crux of an LBO, looking at how much our cash that we generate, we can use to in turn pay down the debt.

We've then got some ratios which are already pre-populated.

So as soon as we put our accounts our financial results in those will all calculate.

And finally, this section at the bottom, the return to equity holders.

And finally at the key number we're gonna get to is this number here. We're gonna calculate an IRR of the return to the equity holders.

So let's get going. So let's start right at the top of this.

And we'll do some of our fairly standard purchase elements.

So you can see over here, I've basically got a current share price of 26.

The, the model is assuming we pay a 20% premium above that.

So let's work out the share price that we are paying.

So we're gonna pay one plus that 20%, um, and we're going to multiply that by that unaffected share price of 26.

That gives us a share price of 32.

And I'll try and put explanations or or formulate alongside everything that I do.

So you can see either, you can either copy if you want, or you can just see where the numbers come from if you're just following.

And understanding, okay, we now need to work out some dilution.

Other thing with any acquisition is we always have to look at the dilution because we have to buy out those share options held by the senior managements and executives and so on.

That is always based on the offer price not on the unaffected share price. So we need to take into account that 32 number.

So we have our fairly standard treasury function, or sorry, treasury method for this.

And we take the max of either 0,

and we need to take the number of share options multiplied by the difference in the offer price minus the strike price all divided by the offer price over, over here.

And that should give me 0.3.

And I forgot to put dollar signs in here.

So I need to dollarize the C10 figure over here just so I can copy this down. So I'll just jump back into here and hit F four on both of those C10 references.

And then I can just copy down for the performance share units and the service based options and the different tranches of options.

Add them all up at the bottom.

And that's my total dilution.

There we are total dilution of 9.9.

So therefore I can jump back over here and add that diluted number to the number of shares.

And then I'll just simply alt equal that.

And now I've got my diluted number of shares. That enables me then to calculate my total equity, simply taking that diluted number of shares multiplying by the offer price of 32.

And there we are, 2,193, now going round the EV to equity bridge, we start from equity on the right hand side.

We'd normally add on debt, and that would then enable us to get the EV, in this case, however, the net debt is actually negative 92.

I.e., they've got net cash, they've got cash on the balance sheet.

So therefore, what we need to do is to take the equity value less that 92 to give us the enterprise value.

So, but the enterprise, sorry so the cash is negative already, so we just simply need to say the equity value plus that 92, and that gives us that slightly lower enterprise value of 2,100.

Final thing we're gonna do is just look at the multiple that that represents over the LTM EBITDA.

So, you'll remember we did spot an income statement somewhere down here. So let's go and find the EBITDA in our income statement, which is about a hundred lines down.

There it is. And let's look at the LTM EBITDA. And there's that number there, 123 in cell E85.

There we are. And that enables us to calculate and EBITDA multiple simply comparing EV to that LTM EBITDA. And it gives us a multiple of 17.1 time reasonably high.

But not particularly stellar, not kind of, you know, some of the, you know, the very high levels that we get with some of the, IT firms and technology firms with all that sort of fluff and frorth in their valuation.

What we're also gonna do while we're here is say, well, if that's the entry multiple, I'm gonna make the same number.

My exits multiple.

That'll be very careful with exit multiples.

I have seen a couple of times LBOs where the exit multiple is higher than the entry multiple. That's very unusual though, very, you know, very bold to do that.

Because at the end of the day, you think, you know, the EV to EBITDA multiple reflects your expectation of the future.

If the business at the moment has got some cost savings we can make in the future, then perhaps that 17.1 assumes that someone's going to come in and make, you know, some improvements.

Once they've then been made, you can't really make them again.

So I wouldn't expect the 17.1 to do anything other than either the stayed the same or even perhaps go down. Certainly wouldn't expect to it to go up.

Okay, let's jump down and do our sources and uses of funds.

So here we go. Uh, so we've got our sources and uses of funds.

First thing we're gonna have to do is buy the equity.

And we calculated that to before. Here's the equity.

2193. There it is.

What about the debt financing fees? So debt financing fees.

You can see there's a 1% number 1% assumption just up here.

So we're gonna apply that 1% to all of the debts that we are going to take on.

So we're gonna say equals 1% that financing fee multiplied by some.

And then go and point to all of the little, all of the different tranches of debts, all the way from revolver all the way down to senior unsecured notes.

And there we are. And that's a little bit soon.

Let's make that just a little bit smaller if we can.

Just so that, I know it's a little bit small, but you might just about be able to see that formula there over on the right hand side.

And then finally, the advisory fees says the advisory fees are 1% multiplied by the EV.

So let's go and find our EV. We did that just up here. There it is, 2,100.

So that's 21 million total uses of funds, therefore equals some of all those amounts.

And there we're just let me check that.

I believe that I've got a pre-prepared version, just yes, that, that's the right number.

Okay? Now, what we often have on the left hand side is the cost of refinancing their existing debts.

But we know they don't have any net debt. They've actually got net cash, so that's why we don't have debt.

The cost of refinancing the refinancing of their existing debts on the left hand side of this.

But what we do have is we can take advantage of that cash that they have on their balance sheet.

So we're gonna jump over here and we're gonna say, well, actually that cash on their balance sheet, once we buy the business, we can basically dividend that cash out. We can get that cash out of the business, and we can actually use that as part of our part of our funds to pay off to buy the shares from the existing the existing shareholders.

So I'm gonna go and grab that 92 number of net cash, which is somewhere up here. There, it says 92, but I want this to be positive, so I'm going to multiply by minus one, and that gives me that 92.3 there.

And then the only final piece in the, um, use in the sources of funds, sorry, is the equity.

This what always happens. We basically find as much debts as we possibly can.

We lever up as much as we can.

Um, and then the equity is the balancing figure.

So we say equals total uses of funds over here, minus the sum of all of the other pieces of finance.

All of that little lock there.

And the PE firm has to basically put in that 1,389, and I'll just put a little formula alongside that number so you can see where it comes from.

Okay. So the final little section is over here at the interest rates.

And what you can see is that they're basically they're quoted as a spread above a base rate.

Now, you can tell it's a slightly a few years old.

You can tell it's a few years old because it's using LIBOR as its as its base.

LIBOR was stopped about two years ago.

And replaced, so you can say it's a few years old, but it shows the methodology.

So what we're gonna do is we're gonna go and find that three month LIBOR.

I'm gonna hit F4 to lock that, and then add on the spread for the revolver.

And that gives me a revolver interest rate. Now, I'm gonna copy this all the way down, except I'm not gonna copy it to the, I'm not gonna copy it to the final line.

And the idea being is that as we go down this set of debt, we go down in terms of seniority and flexibility.

So I've got a revolver here at the top, not actually anything in it at the moment, but we've got that just in case we need to borrow very short term borrowing to cover any, to cover any mandatory debt repayments or any, oh, we run out of cash in one particular year.

So it's always good form to have a revolver. In here.

We've then got term loan A, this would normally be an amortizing loan, relatively short term amortizing loan, maybe five years or something like that.

Probably secured term loan B.

This is probably a bullet loan, slightly less senior, perhaps not secured maybe a, you know, six or seven year bullet loan, something like that.

We've then got a mezzanine loan basically halfway between debt and equity.

And this could represent things like convertible bonds.

And this is a pick loan payments in kind.

So the interest will be simply accrued and built up over the period.

And then finally, some senior unsecured notes, longer term, Pro longer term notes, longer term bonds that we're issuing, almost certainly unsecured.

And that's why they have a higher spread because these are longer term. What we're actually gonna do is we're gonna go to a longer term interest rate, not the three month LBIOR we're gonna pick up the benchmark government bonds.

Bonds tend to be longer periods than bills in the us so we're gonna add that to the spread of nine.

And that gives us the interest rates for the senior unsecured notes.

Good form at the bottom, even though we know it's gonna add up, it's the simply alt equals and put some, oops, doing that.

Try, try again. There we are.

Put some at the very bottom, add it equals the same, is the same amount as the use of funds.

Now we get down to the meat of this really important part where we're going to look at the debt financing.

Now we'll come back and do most of this later on.

But before we go and calculate our interest our income statements and before we calculate our cash flow, I want to just put some opening balances on each of these tranches of debt.

Reason for doing that is, if I don't do this, then when I get to my cash sweep, it's gonna look really unusual.

It's gonna look a little bit strange because I'm gonna constantly have zeros on everything.

'cause I don't have, it appears that I don't have any debt.

So I wanna put some opening balances on here.

So I'm gonna say my revolver, I'm gonna start with what's the opening revolver after the transaction? And it's this number up here in the sources of funds.

So I'm gonna pick that up as the closing figure from the last historic year which becomes the opening figure for next year.

And then I'm not gonna have anything here in the issuance or repayments for now.

I'll populate that at the end.

At the moment, I just want an opening and a closing balance.

So I've got something that's gonna flow through into the rest of my financial statements.

So I'm gonna go down to the bottom, do an alt equals, and that gives me a sum there.

I'm happy with that. I'm just gonna copy that all the way to the very right hand side.

And when I get that, I'm just gonna put formulae along pretty much all of my spreadsheets on that right hand side, just so you can see all the way down.

Okay, let me go up and back to where we have, were with the revolver now. I'm gonna do it exactly the same with the opening with the term loan.

So term loan, a, the ending balance comes from our sources of funds, and it's that number there.

The 425, I'm gonna pick that number up.

That's how much we finance the business with just after this transaction.

And that then becomes oh, I can't, oh, someone asks, why do we use the max function? Oh, that's a long time ago. Sorry about that.

I didn't, unfortunately. I've got two big spreadsheets and a little chat box at the side.

But basically the max function up here, we use that so that if the options are out of the money, so the strike price was lower than the offer price sorry, the strike price was higher than the offer price, then no one would ever exercise those options.

And so they have no intrinsic value, and the max basically stops us doing that.

There's loads of stuff on Felix.

If you look at Felix on our websites, and search for search for options and how to calculate options, you'll find lots and lots of detail on the, okay, let's jump down again, let's go back to our term loans.

And here, sorry. And yeah, sorry, sorry about only, just, only just seeing that.

Then, let's go and find the same, same things with our term loans.

We've got the opening 425, and I want exactly the same structure up here, so I'm just gonna be a little bit lazy, copy all of those all of that.

And you'll see I end up with the same 425 rippling all the way along.

Gonna do the same thing with the tip the term loan B.

Let's pick up the opening term loan B amounts.

How much have we borrowed to finance this transaction? The answer is 165.

So let me again copy the entire row.

And you should see that 165 ripple all the way along.

Same thing with a mezzanine. Let's go and kick up our mezzanine figure.

So the mezzanine figure here is 75.

I'm gonna grab the same very simple structure.

Copy that all the way across.

Now the final piece with this my unsecured notes, I'm gonna be, again, a little bit lazy with this.

My unsecured notes are 75.

I'm gonna assume that there's no payments or issuance of them.

They're just gonna stay at the same flat level all the way along.

So I'm just gonna copy that to the right. There I am.

So I know it doesn't appear that I've done very much here, but it gives me the structure of my debt schedule.

It gives me the structure of the debts area. Okay? One last thing we're gonna do before we go to our our income statement is we're just gonna deal with this.

Financing fees. Now, you might remember, seems a long time ago already, but we had a financing fee.

I'm just gonna go and pick it up from up here somewhere. There we go. 7.4, it's that number there. It was the debt financing fee 1% of all of the debts, that we were borrowing, all of the amounts of debt that we were borrowing.

Now, what basically happens with this is you pay it in cash to the bank when you draw down those when you draw down those loans, but rather than pay the fee directly to the bank and then take it straight to the income statements as we as soon as we get the, as soon as we draw down the loans what the accounting standards say is that you can spread that fee over the life of the loan.

Now, we've got a number of different loans here, and we've got one single fee.

So we're gonna take a relatively simple view.

What we're going to do is we're gonna basically take that we've got an assumption up here.

We've got an assumption that the debt financing fee, we're gonna amortize it over five years. We're gonna amortize this thing over five years.

So we're just gonna have a little bit, that's almost like depreciation.

We're gonna take that 7.4 and we're gonna knock off one a fifth of that every year.

Now, that's fine. We could do that.

We could simply point towards the 7.4 divide by 5, and that would be a negative item, and we'd have that for five years and then stop.

But we wanna give ourselves a little bit of flexibility.

So I'm gonna be a bit more sophisticated.

I'm gonna say I want a min function here.

Now, let me just move my spreadsheet out of the way.

Okay? Oops.

So what I'm gonna, what I'm gonna have is I'm gonna have a min function, and I'm gonna take the minimum of and I'm gonna make it a minus min.

So I want my amortization to be negative.

I'm gonna take the lower of either the brought forward balance or the original 7.4 divided by five.

Uh, but I'll formula drive this so that if I change any of my amounts or my assumptions, then this will update.

So I'm gonna point it at that 7.4 and lock it.

I'm then gonna divide by that five year period, and it goes somewhere up here. There we are, there's the five year period.

Hit F four to lock that in, hit return, and well, that number looks like it works to me.

So let's just do a little alt at the bottom to add those up.

That gives me 5.9.

Now, what this is basically gonna do is as I copy it to the right, it'll keep on taking 1.5 off while that, while that is lower than the brought forward amount, but as soon as the brought forward amount becomes less than 1.5, then it'll basically take off the 1.5 sorry, it'll take off that, that the brought forward amount.

And basically bring this back down to zero.

Once it's zero, then obviously zero is gonna be less than 7.4 divided by 5.

And it's gonna stay at zero thereafter.

So let's see if this works.

Let's copy this over to the right and yes, it does. It takes 1.5 off every year until it's extinguished all of that fee, and then it's zero.

Okay? So we're in a good place.

Now, let's go down to our income statements and let's start populating our income statements. So the first thing we've got is we have got, oops, sorry, moved a little bit too far.

We've got the original numbers here.

So we've got revenue, we've got ebit, and we've got some ebitda.

It's a very simplistic income statement.

And we're not doing anything clever with it.

We're not, there's no cross selling or anything like that.

We're simply taking an extra 10 of cost savings.

Now, it could be a little bit more, subtle with this.

You could have that cost saving building up.

But you know, the, remember the nature of an LBO is it's a financial transaction.

We're not looking for synergies here.

Basically the company is gonna trade largely as is once the PE firm buys in.

So we're gonna go down to our income statements, and our income statements is just gonna pick up, first of all, the revenues as listed above.

But then when I get down to EBIT and EBITDA, so here's EBITDA, I'm gonna basically add on those cost savings to improve the EBITDA slightly and then do exactly the same with EBIT is EBIT of 97.7 plus the 10 of cost savings.

There we are.

And if I'm happy with that, let me just check that I am 126, 107.

Yep, I agree with those numbers. Okay? If I'm happy with those numbers, I can simply drag them over to the right hand side and populates that top section of my income statement.

Then here comes the rest of the real body of my income statements.

So I'm gonna start with EBIT and then I'm basically, we're gonna work my way down and I'm going to, excuse me, I'm going to look at all the things that I've got after EBIT well, the first thing I've got is that amortization of debt issuance fees, which I've already calculated that was the 1.5 every year, and it's the right direction.

1.5, it's negative. That's good.

Now I'm gonna leave all of the interest amounts just for now.

We'll do those when we get to the very end.

That's the sort of complicated bit and interest. They can't really do interest until we've worked out how much debt we're repaying with our cash sweep.

That then gives us a PBT.

So I'm gonna leave a lot of lines blank, but I'm gonna add up all of those lines from EBIT downwards.

Just puts the formulas alongside these.

There you go. Okay.

Of course that'll change when I've calculated my interest. It'll all go into my income statements.

But for now we're going to keep on going, right? Tax expense. I've got profit before tax of 106.

I've got a tax rate, an effective tax rate of 36.

I want this to be negative, so I multiply by minus 1 and there I have it.

So my PBT plus my tax basically gives me a net income figure of 68.

Okay? So,really pretty simple, income statements.

I'm happy with it. I copy it all over to the right hand side and you can see the revenue is gradually building up.

EBIT is gradually building up.

Therefore so is tax and so is my net income. Okay? I can now go down to my cash flow.

Now again, I don't have a lot in here.

I've got an awful lot of those interest items that are gonna be missing for now.

But, we'll come back to the deal with those once we've constructed our deck schedule.

So we're gonna start our cash flow with EBITDA not EBIT because obviously D&A is a non-cash item.

So we're gonna point at the EBIT adjusted EBITDA after the cost savings, nothing on interest at all.

We'll deal with that later.

We're gonna assume that our tax expenses paid in cash in full, and I will just grab those two amounts and copy them over to the rights for the full year or the full 10, 10 years.

All those other items are all basically already there, already assumed.

So then I can simply say equals sum, I'm gonna add up the entire, the entirety of that, all the way up to EBITDA, hit return and I get 50.9 okay of cash.

Okay? Now we know we had no cash brought forward, no cash, in the bank because we cleared everything out.

We used all the, all the targets cash, to do the transaction.

And we raise loads of debts. So we know we haven't got any cash yet.

So we can simply say, I've generated I've gone from cash of zero to cash of 50.9.

How much of that can I use to repay? We're gonna assume we use all of it, and we are then gonna start looking down the debt, the pieces of debt and saying which ones do we pay? Which ones should we try and repay? Well, the first thing we're gonna look at is the revolver.

I'm gonna say we should repay.

If we've got a revolver balance, we should use our cash balance to repay.

And what we're basically doing is we are repaying in order of both flexibility and seniority.

So it's very easy to take some cash out of the bank and repay a revolver.

And that's a sensible thing to do that saves you interest.

It's staffed to run with both cash and a revolver at the same time.

Then what we're gonna do is we're gonna say, well, the next one down, the next one that's assuming it's gonna get paid fairly quickly is the term a loan.

Remember we said that's probably something like a five year amortizing loan or something like that.

So it's gonna be relatively easy to repay that.

And then term B loan a little bit less senior more likely to be a bullet loan, more likely to be a longer period.

And therefore we'll be able to pay that, but perhaps not at the, you know, without fees perhaps not.

Early on we're gonna have to wait a little while to repay that.

So we'll do that second. So we're gonna use a revolver.

We're gonna use a function to work out how much of this we can repay.

And we've basically got 50 of cash available.

And if we look up at the revolver balance, we have got all the way up here.

We've basically got zero, we've got zero.

But if we had a positive balance here, maybe of 20, then how much should we repay? And the answer is, we should borrow, we should repay the 20 of the, of the revolver balance.

What if we had a revolver balances revolver balance of 200? Well, the answer would be we can't pay all 200 or we can pay it's, well, I've gone too far, or we can basically pay is the 50.9 that we've got.

So we're gonna have a mi another one of those minus min formulas, and we're gonna say equals minus min.

So I can repay the lower of either the cash I've got available, or let's go all the way to the revolver balance, which is up here and it's the opening balance.

That number there, hit return and it's an unexciting zero.

And that's basically because the the revolver balance is currently zero.

We're gonna do the same thing with the loan term term a loan.

We're gonna say I want a minus min formula, but the minus min formula would be the lower of either the 50 that of cash that we had available, less any repayment for the revolver.

So we're gonna say equals the min of 50 plus, that revolver repayments or the balance on the term a loan.

So we're gonna go jumping all the way up here and find the opening balance on the term a loan.

And it's that 425.

So the lower of 425 or 50.9 is obviously 50.9.

So we're gonna use all of the 50.9 to repay that 425.

And then we do the same thing with the term B loan.

We say I'm gonna do a minus min function, and it's the lower of the 50.9 plus the amount that we've used in repaying any revolver, plus the amount we've used in repaying, um, the term a loan.

If there's anything left, I want the lower of that amount or whatever is on the term b loan as an opening balance.

And of course, what's gonna happen here, it's gonna be zero because we've used all the cash in paying down the term a loan, get to the bottom.

And I can basically say, my, so my cash flow is basically all of that that I used to repay the loan, plus that cash available for debt repayment.

And the answer should be zero.

And it is, and that's normally what we find in the first six or seven years, is that basically the bottom line cash is zero.

That's because we're basically using all of our cash to repay the loans.

Okay? So we're almost there. We're almost there.

Just gonna do one more little element here.

We can now say what about the cash balance? How much is the cash balance? Well, the cash balance is basically the brought forward cash balance, which is zero because we cleared everything out when we set up the LBO plus that net cash flow just above.

And of course, again, it's gonna stay at zero or pretty much close to zero all the way through.

Okay, so we're almost there.

We are almost there let's go up to our debt schedule up here.

Because you'll remember we set this thing up and we said it's a little bit complicated, but we want the opening balances here, just so that as we calculate though that cash sweep, we've got something to work with.

And that's what we found. But we actually now need to put those repayments into our debt financing section up here.

So I'm just gonna link each of these items, each of these middle lines down to that cash sweep at the bottom of the cash flow.

So revolver there, it's, so I said I, and I know that this is all zero, so it's far, it's fairly unremarkable.

But basically, this one is saying that I started with zero and that's line, sorry, cell F124 was what we worked out. We wanted to repay. It's important that we still have this here because if we'd made significant repayments of the debt and then one year we had a really low cash amount or we had a large amount of CapEx we might find ourselves running out of cash and having to draw down on the revolver.

What about the next one? This should start get a little bit more interesting term a loan.

Let's link this one down right to the bottom of the cash flow.

Oops, I've gone past it.

There is this term a loan repayment down to F125.

And now it does look like something's happening.

I started with a term a loan of 425 and I've made that repayment of 50.

And so in my first year, it's knocked down the balance.

Now let's see if it does something interesting. If we copy it all the way to the right and it does look, you can see the balance is gradually falling and then I get to here, I get to year seven, it pays off the brought forward balance in full and then there's nothing else happens.

Okay, so what about the next one? Term B loan. See if this one works. And this, again, will be a bit more interesting. So we're gonna go all the way down to the bottom of the cash flow.

There's my term B repayment.

And if you just look to the right hand side, you can see what's happening When I get to year seven I've repaid all of the term A loan and now I'm starting to use the cash to repay the term B loan.

So let's link that in.

Again, we've got nothing in the first few years.

If I copy this all the way to the right, you can see that basically what happens is when I get to year seven and eight, I basically start to pay off the loan.

And it's actually all gone by year 10 now for don't get too excited because at the moment, remember we haven't done any interest calculations.

And the interest calculations will end up getting pushed back into the income statements.

We'll, basically we'll pay them in cash.

So my cash performance is actually not gonna be quite as good as it looks at the moment.

So it's fairly likely we might end up just paying off the term B loan, either paying it off right at the very end of year 10, or possibly just, you know, starting to repay it in year nine or something like that.

Okay, so we're almost there. What about the mezzanine loan? Let's deal with the mezzanine loan.

So we're gonna say equals the opening at balance and we're basically gonna go up to interest and I'll come back and do the interest on the other items here.

So remember the mezzanine loan is a payment in kind loan.

So basically it is a gradually accumulating at this 8.6 level.

So I'm gonna hit F4 on the interest rates, hit return, and I, you'll should see that if I copy this to the right, then my mezzanine loan gradually increases, stop paying in cash, this is gradually just increasing away.

And then finally, I've got my unsecured notes.

So I'm gonna basically do the same thing here.

I'm gonna E take the previous year, the 75, and I'm gonna multiply that by the interest rates.

The interest rates on the unsecured notes.

Remember that long-term interest rate of 10.8, I'm gonna hit F4 to lock that in.

I'm gonna get 8.1. So what I'm basically assuming here is that I pay this 8.1 interest in cash on the unsecured notes every year, and that maintains the balance at 75.

Now the other thing I need to do is then look at the interest on the variable amounts.

So I'm gonna do this on the revolver and on the cash, and on the term A and B loans as well.

So I'll do it on the revolver, even though there's nothing there at the moment.

So I'm gonna take the average of the opening and closing balances.

I'm gonna multiply that by the revolver interest rates, which is up there, which is 2.6 F4 to lock that.

And that gives me an interest amount.

Okay, copy that to the right.

It should be zero all the way along, which it is.

Let's do the same thing with the term.

A loan, I'm gonna say equals the average of the opening and closing balances.

Multiply that by our interest rates.

Lock that in with an F4 and that's 10.5.

And cut just a couple of points.

First of all, you'll see that it falls gradually as we pay off the loan, which makes sense.

Second thing is, it is, if you look at the movements in this loan, this loan is moving by a lot.

This, the move loan is moving by 50 or $60 million every year, which is why calculating the interest using an average function is important.

Because you know, the loan moves an awful lot between the value at the beginning of the year and the value at the end of the year as we gradually pay it off.

Okay? And then we'll do exactly the same with the term B loan.

So we take the average and we take the average, oops, sorry, take the average of the opening and closing balance as we multiply by the term B loan.

Hit F4 to lock that in.

And again, what we'll see is this gonna, is gonna be relatively steady until we get all the way along.

And then as we gradually pay off that loan, it falls.

So we're almost there. We're almost there.

I'm just gonna complete this section by putting in the cash balance, which is right down at the bottom of my cash flow statement down here.

Zero. Copy that to the right.

And you can see I'm now building up a little bit of cash at the end.

I'm gonna do an average calculation of the cash balance and again, multiply that by cash interest rates, which I've got in one of my assumptions a little bit further up.

I think there's not 0.5% from memory.

Yes, there it is not 0.5%.

Log that in with an F4 hit return.

And that's my interest on cash, that's interest income.

So we're almost done. Two things to do. First of all, we need to deal with interest in the income statements and then we need to work out our returns to efficacy holders.

Now we need to be a little bit careful here.

We've got lots and lots of interest and in the income statements and the same in the cash flow.

Now if you've looked at the if you've done a three statement model before, you know, one of the dangers is circular references and we get round circular references by having a circular switch, which we turn on and off.

And we also get round by having, it iterative calculations turned on within the Excel settings.

If that's not familiar, then you need to go and have a look at some of the basic three statement modeling, with a cache sweep and that'll explain that.

So we're gonna use the same approach here.

So we're gonna say an if function with a switch and as I type switch, you can see it comes up. So I've got a name sale called switch which we'll use to turn the interest on and off.

So I'm going to say if switch equals one, then I need to go and grab the revolver interest that I've just calculated somewhere up here and there it says that one.

And I need to multiply by minus one because I want it to be an expense. Otherwise, if the switch is zero, I want it to bring back zero.

And then I'm gonna do exactly the same for the term A and B loan. And the cash balance. So I'm gonna say equals if, if switch equals one, then bring back the interest on the term a loan, which is there times minus one, otherwise zero.

And you can see my switch must be turned off.

Because it's not bringing anything back at the moment.

So we'll just populate these and then we'll go turn the switch on.

So we're gonna say again, if switch equals one, go and get the term B interest, which is there multiplied by minus one, otherwise zero.

There I am mezzanine loan pick loan. So I don't need to worry about that.

Because it's paid in. It's paid in kind.

So where's my pick interest there? 6.5 multiplied by minus one.

And then exactly the same with the unsecured notes.

Again, I don't need to worry about this because it's not based on an average calculation and it's that number there, 8.1 times minus one and the final one for cash equals if switch equals one and go and get the cash interest.

And this time I don't need to multiply by minus one because it's gonna be income, otherwise zero hit return.

So I'm gonna copy those all the way. Sorry.

Yeah, all the way to the right and they're all zero zero at the moment.

Let me just go find the switch and turn it on.

There's my switch, turn it on, go back to the LBO tab and you can see interest and it looks like it's doing the right sort of thing.

I've got interest on the term loan rippling sorry, on term a loan gradually declining interest on the term B loan.

Same thing. You know, it stays at the same level until we start repaying the the loan interest on my mezzanine loan. It's pick interest. So it's gradually going up and the interest on the unsecured notes is stable because that balance isn't changing.

And then just right at the very end, I start having a little bit of interest on cash.

Now I need to just jump that into the cash flow.

So I'm gonna say equals um, my revolver interest is just gonna be exactly the same.

I'm assuming that it's paid in cash as the amounts, in the income statements and I could just copy that down to the term A and term B loan.

I need to skip the mezzanine loan because it's not paid in cash.

It's a pick loan. Grab the, unsecured the interest on the unsecured notes and then finally include the interest income on cash copy to the right.

And now I've got a model that works and you can see basically let's just go and have a very quick look at what's happening to the debt.

You can see that what's basically happening with the debt, it's now a little bit slower.

It's a little bit slower to repay.

This was previously, being paid by year six.

It's now drifting out to year eight.

And that's basically because of the interest in the income statements and the interest we're paying in cash is reducing amount, the amount of cash that we've got to make repayments to the loan.

That in turn means that it takes us all the way until year 10 to repay the term B loan.

And so we only end up with cash in the bank right at the very end.

You might remember if we just scroll down a little bit, our cash flow statements.

This figure here, you might remember we had just over 50 million.

And now because we've got all that interest that we're paying is now down to 37.4.

But we've got a model that works, a model that hangs together.

Final little section to deal with right at the end is the return to the equity holders.

So first thing I'm gonna do put a little year counter at the top, there's our year counter and now we need to calculate our enterprise value.

Now seems a long time since we were doing this but we, you might remember, we basically put a multiple exit multiple right at the top when we said we wanted that exit multiple to be the same as the entry multiple.

And it's 17.1. So we need to take that 17.1.

I need to lock that in with an F4 and we need to multiply that by the EBITDA figure, for the business. And there's the EBITDA, it's just at the top of those debt ratios. I'm gonna pick it up there. I could pick it up from the cashflow.

Lots of different places. So there we are. We've got the EBITDA figure sorry.

The EBITDA figure multiplied by that 17 gives us an enterprise value of the business, assuming we sold it at the end of year one.

Next thing we do is we need to say what's the net debt at exits? Okay, so I'm just gonna ju jump up here and we're gonna collect all of those items of debt in the debt schedule right up at the top.

So I'm gonna say my debt is the revolver balance plus the term A loan balance plus the term B loan balance, plus the mezzanine loan balance.

Plus the unsecured notes balance less the cash balance.

And we hit return and we get something like 709.

We're calculating our equity, we've got our enterprise value, we've got our net debt.

So we simply say enterprise value less net debt gives us the closing equity value.

So we say equals EV minus net debt.

This is a net debt figure of 1,280.

Oh, okay, we've gotta copy this to the right and you can see, basically what we've got now here really is the crux of an LBO.

First of all, we've got the enterprise value gradually growing from 1.9 billion up to 3.2 billion gradual.

What's, what's causing that? Really just the improving profitability of the business over that time.

But I think you'd have to say, although it's pretty good, it's not stellar.

It's taking 10 years to add 50% to the enterprise value.

What about the debts? The debts gradually falling from 709 to 164.

So we're paid, we by that stage, we've repaid all of the term eight. We've repaid all of the term B but we have got the mezzanine loan building up and we've got the unsecured notes still as well.

That therefore means that my ordinary equity rises pretty substantially from 1280 to 3.1, 1.2 billion, up to 3.1 billion.

Okay, so let's then final stage.

Let's go and have a look at what that looks like to the institutions.

Well, how much did they put in? Let's go and find the equity value rise up at the sources and uses of funds.

So basically, the PE firm put in that balancing figure that we calculated a long time ago of 1,389.

I'm gonna multiply that by minus one 'cause they basically put that into the business.

And then if they decide, uh, to sell the business, what will they get out? Well, they'll basically get this line here, but they'll only get it in the year that they decide to exit the LBO.

So we're gonna do a little, if statements here equals, if I'm gonna say, if that's year counter is equal to the exit year in our assumptions, and you might remember a long time ago we had that exit year assumption there.

Year five, gonna again, lock that in with an F4.

If that year counter is five, then what we need to do is we need to bring down that equity figure there.

Otherwise it needs to be zero.

Now, if this works, and I've done this correctly, if I copy this off to the right, then we should only get, and yes, we do.

We just get a positive amount in year five.

That's our exits year. But we're formula driving this.

So we could adjust in our assumptions.

We could say, what would it look like if we exited after three years, after four years, after, five years, after six years, and so on.

What tends to happen is the earlier you exit, the better your IRR.

So let's work out our IRR equals IRR.

Just gonna use the IRR function in Excel.

We'll copy that all the way off to the right.

And that tells us that we've got an IRR for this transaction of 10.9.

And I think unfortunately very few PE firms would be persuaded by that.

I think that's a little bit lower than they would anticipate.

So, it's worth thinking about why that's the case.

Why is this the case? Well, the answer is the EV and therefore the equity value is not actually growing that quickly.

It's a relatively modest amount. There's no sort of real sort of step changes in the profitability and therefore the enterprise value of the of the business.

Secondly, although the debt is falling quite nicely it's not actually that much, that it isn't very highly levered.

Basically we had about one third debt and two thirds equity.

Normally if you borrow even more, then you tend to find that the rate of the returns to the equity providers are magnified.

So those are two things you could play with.

Do have a look at the, the final version of this.

It's got a data table at the bottom show, looking at some of those some of those sensitivities.

So you can look at that.

And do have a look at the various credits ratios as well.

Okay, as if I magic. That's exactly six o'clock my time.

So I've spent just exactly an hour.

So I hope that's been useful.

Do have a look at the, the final version.

Do make sure you've downloaded both the empty and the full version of the spreadsheets.

Hope that's been useful and hope to see you at another one of our webinars very soon.

Hope you have a great weekends.

Take care everyone, and thanks for joining.