Welcome to this session.

I've just put up, on the on, on the share screen, I've just put up a title, and this is all about LBO Fundamentals, the fundamentals of an LBO, what's an LBO, how does it work? And it's an hour long session.

So, we're going to look at a fairly substantial LBO model as well.

If you want to download the material, there are four files.

And those are the files that we're going to be looking at. There are four Excel files.

in fact, we're actually going to look at one file particularly which is the, the case in point file, which is a really substantial model, which has got an LBO section within there.

And there's both a part complete version and also a full, a full version of full complete and LBO full version.

There's also a couple of other files as well, which have got shorter exercises, shorter LBO exercises.

We're not going to look at that one, but that's a great resource.

If you want to look at some of the detail of an LBO you can download those files.

I can see a good number of people have joined.

So I'm just going to pop the, pop the link into the chat box.

Let me just check.

I don't think there's anyone else joins.

Okay, so if you've just joined, you should see a link in the chat box and in a minute or two, I'll just put it in one more time just for safetY sake.

So everyone's got access to those files.

So what we're going to do is we're going to start off with looking a little bit of theory.

I've got three or four slides that we're just going to look at that explain what an LBO is, what, and how it works.

Some of the key features of an LBO, and then we'll spend about 40 minutes or so diving into Excel and doing a really substantial LBO model based on a business buying Red Bull. So it's based on real life. It's based on a real company, and we're looking at how an LBO with a target of Red Bull could be set up.

And Red Bull's a good example because it's a, it's a privately held company, so it's some, you know, it doesn't have a share price.

So we have to create a value for it, and we have to estimate a value for it, and then go through the LBO process.

I can see a couple more people have joined.

So just as those people join, I'm just going to pop in again into the chat box, a link to our website.

So if you go to that link, if you have a look at that link, if you've just joined you'll be able to see four files.

And the file that we're going to look at is one called Part, the one that finishes with Part Complete.

It's a case study file, and it says part complete at the end.

Okay. So without further ado, I'm going to dive into our notes.

And here goes, so let me just check that we're all set up and we're sharing everything correctly.

Yes, we are. Okay, so here it goes.

So we're going to start by looking at what an LBO actually is.

Why is it called an LBO? How does it work? How does it create value? And an LBO is basically an acquisition.

It's buying another company.

But basically you buy it by funding it in part with some equity from an investor.

But in very large part, in much greater part with a big pile of debt.

So generally you set up a special purpose vehicle and that vehicle, that company buys the target company, but that company, that special purpose vehicle is partly funded by equity, but, but funded a great deal more with debt.

So here we are on the left hand side, we've got an entry valuation.

So we buy a company enterprise value, either underlying value of the business without its financing.

That's what we buy.

And then we finance that with, as you can see on the right hand side, a relatively small amount of equity and a very large amount of debt.

That's why it's called an LBO, that's why it's called a leveraged buyout. So lots and lots of debt. And then what happens? Well, we hold that company for a number of years, and the first thing that happens is hopefully the value of the underlying business increases, the enterprise value increases.

And because we're doing it on the basis of an EBITDA multiple, that's because basically the EBITDA has improved.

So we own this business for three or four years, and hopefully we'll make some operational improvements, and that improves the profitability of the business and in turn, improves its valuation, its enterprise value.

But the really important thing is as that business generates cash, we use that cash to basically repay some of the debt so that cash isn't reinvested in the business, particularly, it isn't paid out as dividends.

It's re it's basically used to reduce the amounts of debts. Now, if we reduce the amounts of debts and the enterprise value gets bigger, then the equity is the balancing figure.

And that's what we see here.

We see this growth in the equity in part because it's very heavily financed with a lot of debt.

And debt has the impact of exaggerating returns.

As long as everything goes well, of course, as long as the business stays profitable and generates cash.

So that's the fundamentals of an LBO.

You buy it with a lot of debt, you use all of the cash that it generates to repay some of that debt.

And in turn, that means that the increase in value and the reduction of debt helps the equity grow significantly.

And that's what provides the very, hopefully, hopefully very high returns to those equity investors, which very often a PE firm.

So, I'm going to dive back.

Let me just check that we are all up and running.

I'm just going to pop the notes.

I can see a couple more people have just joined.

So I'm just going to put in the chat box a link to all of the Excel workouts that we're going to have a look at later on.

So I'm going to dive back a little bit in these notes right to the very beginning and just look at, at a nice little picture.

And I sort of diagram of some of the key features of an LBO acquisition. So here it goes. So let's just walk or around, walk around this table.

So the first thing is an LBO is an acquisition transaction.

A business buys another business.

It's, you know, it's a slightly unusual one. We set up a special purpose vehicle, and that in turn buys another business.

But generally it's an acquisition and it's often, or pretty much always a 100% acquisition. This is not a joint venture. It's not an affiliate or an associate.

We want full control over this business.

We want to grab this business in its entirety and run with it for a few years.

The acquirer is a financial, sponsor.

We're buying this just for the money.

We're just buying this as a financial transaction.

The PE firm that buys a a target company isn't bringing any synergies, isn't bringing particularly any understanding of the nature of their underlying business.

It's purely a financial transaction. Of course, the PE firm might have expertise in raising funds in financing a business, but doesn't necessarily understand the trading performance of the business the actual how the business works how it operates within the markets.

Generally it's a relatively short period of investment.

We're looking for three to five years, that sort of timeframe.

As it says here, really important part, a big part is finance with debts. And as I said before, the clues in the name, it's a leverage buyout.

Ah, there's a lot of leverage, a lot of debt to make this acquisition work.

And the equity is the balancing figure.

I think this is probably the key element of all of this diagram.

The ability to generate cash flows and repay debt is absolutely key.

It only really works if that happens.

So therefore, you've gotta think of the nature of the business, the target company.

What sort of company do you want? Well, what you want is a business that throws off cash.

So what sort of business is that? It tends to be a relatively mature and stable business, if you think about it, businesses that are growing really quickly, although they might have a good prospect in the future for high profits and high cash generation often it takes cash getting there. So lots of r and d, lots of marketing absorption of working capital.

We don't want that. We basically want relative stability.

So that we can, so the business throws off cash.

We want a reasonably stable target company.

Operational improvements are integral to increasing cash flows and therefore to the success of the business and increasing profitability and final valuation.

But again, we're not talking about introducing new products, you know launching into new markets, that sort of thing.

Generally what we're looking at is efficiency gains.

Basically a lot of cost savings, a lot of squeezing out efficiency from the business so that their profitability improves.

So again, that basically really drives the improved performance, the improved ebitda, and in turn, the improved, evaluation at exit.

Let's look at how that turns into numbers.

So here goes, we've got a notional transaction here.

And we've got a business currently on entry.

When we buy it, making an EBITDA of 36.3.

We multiply that by our standard multiple, which in this case is six times.

And that gives us an enterprise value.

That's how much we're going to pay for the business, and that is funded 50 50 with debt and equity.

You can see 217 divided by two is 108.9.

So we've got the same amount of debt and equity.

So a lot of debts, a lot of equity, a lot of debts as as part of the consideration.

What happens when we exit the business? Well, we exit the business four years later.

And basically the improvements the operational performance of the business has improved.

So here we go. The profit has gone up from 36 to 62, so not quite double, but about 40% increase in, sorry, about 80% increase in the profitability of the business.

We exit the business.

And we also have, and this is really just for the terms that just for showing, demonstrating the calculation here, we're basically taking an EBITDA multiple on exit that's actually higher than the entry multiple, not that common.

Often you'd tend to take the same entry and exit multiple, but this is just for the purpose of demonstrating a calculation that in turn that improved profitability multiplied by an improved EBITDA multiple, gives a, a valuation of 438.

Again, they've almost doubled the enterprise value of the business in for years.

When we walk away from the business, when we sell the business we've used some of that profitability to reduce the debt.

So that's gone from 108.9 down to 65.3.

The debts shrunk, the enterprise value has gone up. Therefore, the balancing figure, the exit equity what we sell our stake for is 373.

So all, so more than trebles more than three times.

Equity stake that we went in with.

So a really significant, a really significant improvements as they walk away from the business.

And that's, in turn, if you calculate the IRR over the four years, gives an IRR an average rate of return to per year to the investor of 36.1. So a really healthy rate of return it's worth making.

The point that generally an IRR investor, a PE firm looking, sorry, not an IRR investor, an LBO investor, a PE firm walking into an LBO going into an LBO transaction would be looking at for a rate of return somewhere between 20 and 25% per ann which is a really hefty, a really great rate of reside.

But then these are relatively risky transactions. Okay.

I can see we've got another couple of people who have just joined, so welcome.

I'm just going to put in the chat box a link to the, a link to the websites where all of the files, are stored.

And particularly oh, I can't see.

I think, let me just, I'm going to pop it in here.

Hopefully that'll work.

So in the Q&As as well apologies.

I didn't see that Q&A I didn't see the Q&A there. It doesn't, it didn't pop up on my, on my machine. Okay.

Hopefully that, works great. Okay.

Okay. So there we go. So we've got, hopefully got all, all, all the material.

And that's a quick whistle stop tour of how an LBO works because all we're actually going to do is we're going to open up a really sizable file and rather than just look at a handful of relatively small and LBO sort of snippets, parts of an LBO, we're actually going to construct an LBO model.

And we've got, three quarters an hour, which I think is just about enough time for us to do that.

So I'm going to walk you through a pretty substantial, LBO model that we use, in a lot of our regular teaching.

And we're going to be able to, we should be able to do that within, 40, 45 minutes.

So you'll be able to see how a really quite substantial LBO actually works.

Okay? So let me just check.

We've got the same number of people. That's great.

Okay, so I just need to rearrange my screen a little bit to get the right thing on the page.

So I'm going to drag this across. Here we go.

So dragging this, Excel document across, and here we are.

We've got an LBO valuation for Red Bull, and that's what we're going to look at for the next 40 minutes.

Or so just to have a little, just make sure if you want to follow along, if you want to work with me, just make sure you're looking at the right documents. So this is, apologies, it's a fairly long code name because we use this document quite often internally.

And it says 70 50, 70 20 case in point beverages.

So it's all about, it's all about Red Bull, and we used this all the way through the last, through the last year and early into 2025.

It will, it's in the process of being updated, for, for, for the most recent financial announcements, but some of those haven't yet come out, which is why it's based on 2024.

And the important part is it says part populated.

So if you make sure you grab the version that says part populated, there's another one that's got the same title, but it says LBO full, and that's the solution. So if you just want to, want wander through the, the full version, look at the full version, then that's absolutely fine as well.

So off we go. Off we go.

So I am going to walk into this, document.

Now, if you're not on the right tab, you can see, if you go right to the very end of the tabs, right on the very right hand side, you can see there's an LBO tab. It's actually the last activity on this, on this website.

Now, sorry, on this spreadsheet.

Now I'm just going to show you very briefly where a lot of the information comes from.

So we've basically used this, spreadsheet and we use it in a lot of different circumstances.

We use it for, teaching valuations.

How would you value a business like Red Bull? How would you put together a three statement model for Red Bull? How would you do maybe a training comps valuation of Red Bull, DCF of Red Bull, and so on.

What we're going to focus on is this tab here, the one that says Red Bull Model.

And this is basically a three statement model for Red Bull. So it's got assumptions about revenue growth, it's got an income statement, a balance sheet, and a cash flow.

And that's forecasting the future.

For Red Bull, again, as I mentioned before, this is based on a closing year, end of December 23 and a projected year, end of December 24.

And part of the issue is that, red Bull is a privately held company, so actually getting their results, the full results, the actual results for the end of 2024 sometimes takes a little while, which is why this is still based on the 2023, numbers.

So I'm going to hit control and page down, which takes me all the way to the LBO tab.

Let's just have a little look at the shape of this LBO tab before we start completing it.

And it goes something like this.

Oh, and I can see participants, I can see we've got a couple of extra people.

So I'm just going to paste, I know I've done this a number of times.

I'm just going to paste in the chat box, the location of the files we're looking at.

So if you've just joined, and you want to go into that, go into the chat box, go into the file, go into the URL, and you should be able to download this file.

It's the case in point, and it's the one that finishes part populated, but you probably want to grab, grab all four of the files that are there.

So let's just have a quick little look.

It starts at the top of this, spreadsheet with some assumptions, on how much we're going to pay for the, for the business, how much the our entry and exit multiples, things like interest rates are on debt and the fees that we're going to pay.

We've got a sources, a standard sources and uses of funds, up here.

Remember, it's an acquisition transaction, so we're going to separate those sources of funds and the uses of funds.

We then have a key number section where we're going to extract from our normal three seven model for Red Bull, key lines like revenue and profitability.

And that enables us in turn to build up an income statement for Red Bull.

We then have important parts. Basically this is a, if you've done a three step model with a cash sweep, this should look and feel quite a lot like a cash sweep.

So we calculate the, cash flows available to service the debt.

And then what we have is underneath each of the two big tranches of debt that, we structure this transaction with, we put in some, some senior, debt and some unsecured notes.

And what we do is, as an LBO, we look to pay off that debt as quickly as we can.

All of our cash goes to repaying that debt as quickly as we can.

So on that EV to equity bridge, the debt bit shrinks and the equity bits, grows.

And then right at the very end, and it's almost the, the last little section, it's really quite small.

Okay, so what does that mean for the business? What's the business worth? If you enter the business, with some equity and then you exit the business, with some equity by selling your equity stake, how much does that make you in terms of a rate of return? So we're going to start right at the top.

Let's start completing some of these, these sections.

So we're going to start right at the top and we're going to say and I'll just show you where some of this stuff comes from.

We're going to say, how much is the business worth? Well, we're going to value the business on the way in and on the way out as a multiple of its ebitda.

So what we've got here is you can see a link, it says Red Bull Model F60 by double click on that, just to show you where it comes from.

It comes from, here, it's the EBITDA of Red Bull from the Red Bull model, for the year to December, 2023. So we're doing this as if it's the beginning of 2024, I hit function F5 and that enables me to go back where I came from.

Now I've got the LTM ebitda so I can actually work out how much I'm going to pay for the business.

So the first thing I'm going to do, and I'll make sure I'm in the right tab so it ties up to my answer.

So the first thing I'm going to do, as I'm going to say the enterprise value of the business when I buy Red Bull is 3134.

The LTM EBITDA multiplied by my entry multiple of 25 so EV to EBITDA of 2025.

And that gives me an entry, enterprise value of 78.3 billion euros.

Now, just before I leave this section, I'm going to make the exit multiple equal to the entry multiple.

That's a fairly good rule of thumb.

It's a fairly good approach to for an LBO, it's a good starting point.

Now you might decide you're going to change the exit multiple, depending on your view of what the world looks like, four or five or six years out.

But a good rule of thumb is to take the same entry and exit multiple 20 fives a fairly high multiple because Red Bull is, is very profitable, highly profitable, growing very quickly, and is, and therefore our, our assessment is that it's worth a lot of money. That's why we've got that high entry multiple.

Okay, let's look at some other elements of our, uses of funds first. Next thing we've got fees. So it tells us down here that we've got fees, which are 1% of ebitda.

So this is an easy calculation. We said that 1% we multiplied by the enterprise, value, and that gives us fees of 783.5.

Now, it is just worth making the point, and it makes it in this little note in this section here.

Of course, when you go and buy a business, you don't buy the enterprise value.

What you buy is you buy the shares.

But if you think about that, if you buy the shares in a business, and then you refinance that business, well you're buying that EV to equity bridge. If you think about the, that in your mind's eye, you buy the equity on the right hand side of the EV to equity bridge, and then you refinance the debt.

Well, if I pay for the equity and I pay for the net debt, then it's it's the same as paying for the enterprise value.

So actually saying that we buy the enterprise value here is really financial shorthand for saying we buy the equity and we refinance their debt all in one sort of calculation. So it's okay to do this. Alright, so our total of uses of funds is basically the enterprise value plus the fees, and that gives me 79,134.

Over on the debt side, A fairly common limit to the amount you are able to borrow for an LBO is six times, EBIT six times, LTM EBITDA.

And so, and that's the U-S-S-E-C often refers to that as a sort of a not exactly a statutory maxim but basically an indication of the amount that they would expect, the maximum amount they would expect people to borrow, in setting up an LBO.

So we are going to say that in total we're going to borrow six times EBITDA.

Now, we've already, in our, in our assumptions said that we're going to make four, we're going to borrow four times EBITDA as senior debt.

So therefore the balancing figure, to bring it up to six times is basically six minus four is two times, that EBIT two times EBITDA.

And so we can then work out how much we borrow.

And this is the way that you would normally construct an LBO transaction.

You work out how much you are going to borrow.

And then the equity stake that the PE firm puts in is the balancing, figure.

Okay? So, I'm going to say senior debt equals, four times, LTM ebitda and I'm going to make that function F four, I'm going to lock that one in.

So hopefully if I copy this down, then I basically get, six times and four timeS the EBITDA as an amount of debts. And then the equity is the balancing figure.

So we simply say equals total uses of funds minus the sum of what's just above, and that's a cool 60.3 billion euros. Get down to the bottom alt equals, and I end up with the same figure at the bottom line sources and uses of funds.

All good stuff, okay? Even at this stage you might be thinking this doesn't look like a particularly good LBO and you'd be right, it's a slightly unusual LBO in that the amount of borrowing is not that high.

And the rationale for that is it's really highly priced.

At 25 times EBITDA as a multiple.

There's a lot of equity of course, and we're only borrowing six times ebitda, as debt, but there might be reasons, compensating that that mean that the LBO does actually work.

Okay, next thing we're going to do is we're just going to look at the cost of debt.

Now we've got an assumption here for the cost of the unsecured notes.

We don't have a cost for the senior notes, but what we do have is a figure up here, for spread for these, typically, typical senior debts in a credit worthy company in Europe of 1.57 above the base rate.

So we're just going to simply add those two up and get a cost of debt for the senior debts.

And that's 3.99%, obviously much, lower than for the, for the more junior debt for the unsecured notes.

Now we move on to the actual scheduling, the actual forecasting of what Red Bull is actually going to do.

So let's move down a little bit and we end up with these key numbers.

Now remember what I said about this section, this is really just some calculations and extracting numbers.

So we can in turn, can then construct an income statement, which in turn is going to enable us to construct, a cash flow, generator that's going to generate the cash flow available for debt servicing.

And in turn we can then work out how much of the debt we can pay off and how quickly.

So we're going to capture, some of the key information.

And you can see that I've already done that. I've pre-populated some of these first lines, where I've picked up the first year's revenue and I'm just going to double click on this one just to show you where it comes from.

There's the revenue figure, being built up in the three statement model for Red Bull, for December 24.

So we're picking these things up just simply from the operational forecast for Red's Red Bull.

Okay? So, but what we're also then assuming, and this is why it's not exactly in line with the forecast, we then assuming that we can squeeze things a little bit, we can improve the efficiency of Red Bull, we can make some efficiency savings, to, to improve the profitability, which in turn is going to grow, help grow our EBIT start of the business.

And our assumption here, and you can tell it's an assumption because it's dark blue on pale blue, fairly common code, for hard coded assumptions in a model is 1% of revenue.

So we're going to say our cost savings are 1% of revenue.

So I'm simply going to multiply those two things together and I get 115 million euros per annum of cost savings.

Now it's a very simple assumption.

We're assuming you can make those instantly in the first year, and they carry on as cost savings all the way through.

That gives us an EBIT, we pick up the EBITDA so we can then multi, add those two things together.

115 plus 3,402, that gives us an adjusted, EBITDA of three five at one seven.

It's worth just doing a little margin check.

So I'm going to take that adjusted ebitda, I'm going to divide it by the revenue, and it gives me 30.5.

And this is a really good, way of just checking that your assumptions are not creating real nonsensical numbers.

You know, if you ended up with, EBITDA margin of 60%, you'd sort of look at that and a little ance and just say, that's pretty unlikely.

But you know, this has gone from, you know, 25 and 5% up to 30.5 percent's not sort of too extreme, so therefore doesn't look too, too crazy.

So if we're happy with those figures, we can just copy those all the way to the right using Control R.

And we've now got all of those figures populated.

And what I'm going to do is I'm just going to go over to this very right hand side, go all the way to the bottom of my spreadsheet and just put some, just put some, formulas in here just so as we populate this thing, you can basically see, how these figures, how these calculations are drawn up.

Okay? Now let's go and construct our income statement. So this is the first step really, of us modeling out, the results, the performance of red Bull and basically looking at how quickly it's going to turn into cash.

So we're going to put together, an income statement.

So again, we start, where's my mouse? That's my mouse.

We start from the revenue figure that's exactly the same as the figure we just brought in.

But this time, instead of EBITDA, we're going to grab EBIT from the Red Bull model.

Now, there's a couple of, you know, gotta be a little bit careful with this.

You might sort of ask, haven't we already got EBITDA? And why do we need EBITDA? If we are going to pick up EBIT? Well, EBIT is the figure we need to drive the rest of the income statements.

EBITDA is nice to know, but that's not what's in the income statement. Basically we start we have EBIT, not EBITDA, but the EBITDA number up here.

This figure here, is important because when we look at exiting the transaction, when we look at selling our stake in Red Bull, and basically the enterprise value at that point is going to be driven by the EBITDA at that point.

So this is important for the valuation when we finally come to the very end when we exit the transaction.

So we're going to start with the EBIT s construct our income statements, and we're going to pick up those same cost savings 115.

And that's going to give us, an adjusted EBIT number.

Now we're going to have some interest expense and interest income, but I'm not going to do these at this point, okay? If you've been through a three statement model, construction with us, you'll recognize that we often do interest right at the very end.

Because it has, it often creates a circular reference and we can only calculate interest right at the very end when we work out how much cash and how much debt we've got.

So we're going to leave these, for now, we'll come back and fill them in later on.

So now I can basically take EBIT my cost savings, those two blank lines for interest.

And I can add them all up.

Alt equals is a really great keyboard shortcuts.

Knowing that basically when I get the, that's the figure I'm going to end up with.

Now I'm going to go with tax. What's my tax expense? Well, I've got up here straight from the Red Bull model.

The effective tax rates, 25.5%.

I'm going to multiply that by the profit before tax, multiply by minus one. So I get a tax expense and I get 784 minus 784.

And again, just use al equals to populate, those figures.

And there we are. So we've got most of our income statement pretty much done, except for interest, which we'll complete later on.

So I'm going to copy that all the way to the right.

And again, just a little pause, a little worth, thinking about it, what's actually happening to this.

Well, you can see that I've got really strong revenue growth forecast for Red Bull from 11 and a half to 19.1 billion euros over that.

What's that three, six, nine year period.

And then I've got PBT, therefore all also growing very strongly, almost doubling over the same period.

And even net income, again, almost doubling over that, period.

So a lot of growth here, which hopefully is going to enable us to throw off a lot of cash and in turn repay a lot of the debt.

So then we move on to our cash flows to service debt.

And you might remember this is a little bit like a full cash flow except it's missing the financing section.

So what we do is work out how much cash we generate, generate from the operating activities we work out.

We add back depreciation, we look at movements on working capital.

We go to the investing section of the cash flow. So we take off capital expenditure and we get down to the investing section and say, we don't want any of that.

So we're not going to pay, we're not going to either draw down or repay debt in this calculation.

The interest is already calculated within the net income up here.

So, sorry, up, up here.

So this is fairly standard for a cash sweep.

This is the approach for a cash which is really why understanding the way CRE restatement model works is pretty key for understanding how an LBO works.

So off we go.

So we start with net income, we've just calculated that.

So we simply put a link to that just to the top.

Then we've got depreciation, and I won't dive into these, but you can see that they all refer back to the Red Bull model.

These are just extracted straight from the cash flow of the Red Bull model.

So we've got depreciation being added back, which gives us effectively EBITDA.

Then we've got movement on working capital.

The business is growing, so it's no surprise that their working capital is also absorbing cash.

We've got some CapEx as well.

Again, the business is growing, so you'd expect a fair amount of capital expenditure.

And you can also see, just looking at those two numbers, CapEx is greater than depreciation, which is often a feature of a growing business.

Get to the bottom and we can add all of these things up and we end up with our cashflow from debt, re cashflow available for debt repayments.

Copy that all to the right. And there we are.

Just let me check that I've got the same numbers. Yes I do.

Okay, excuse me.

So, now we can move on to the real sort of fun bits of, this, which is, excuse me, just going to take a Small drink to hopefully clear my clear my clear my cough.

Okay, so now what we're going to do is we're going to start looking at the debt section.

And this is where we get to the really interesting parts of the afternoon.

This is where it gets a little bit complex.

And again, if you've ever done a cash sweep model, then, you'll recognize this process hopefully.

And that's why we teach cash suite models, within our modeling section because they're really part a very valuable part of an LBO. You have to be able to work, work out. They, they, they operate on the same principles.

So we're going to start, and what we do in this section is we are going to look at the two different chunks, the two different parts of debt.

Now you might remember we borrowed, we had some senior debt and we had some unsecured notes as well.

So what we want to do is we want to work out how much debt we've got and then from this cash that we are generating up here, how quickly we can use that cash generation to repay those pieces of debt.

So our starting point has to be how much debt do we start with? So I'm going to start at this point and I'm going to say, where does our, what's our opening at senior debt balance? So I need to go up to that sources of funds right up at the very top.

Here's our opening senior debt, that's how much we borrowed at the start of the transaction, 12.5 billion euros.

So I'm going to pop that in there.

And while I'm here, I'm just going to do exactly the same for the more junior debt for the unsecured notes.

So I'm going to say equals and go all the way up to the line just below 6.268.

And just pop that in there as well.

So I don't need to dive up to the top.

Any, okay. Okay.

So I start with 12 point at 536 of senior debt.

That becomes my opening balance this year.

And then I wanted to think, I want to think, how much can I pay off of that? Well, the answer is 1,910.

And what I'm doing here, and it's a fairly big assumption here, and this is really the key to the way that an LBO works is I'm saying I've generated some cash during the year, that's the 1,910 and I'm going to use all of that cash to repay some of that opening debt.

And I do it in order of senior seniority.

So the senior debt, will have a shorter period, a shorter tenor.

And basically it's, it'll be almost certainly secured and therefore it's going to be easier.

It's going to entail less fees if I repay that early.

So this is the more flexible debt.

This is the one that I want to repay, quickly.

So I could just say equals 1.910, but if I do that, then what I find is I copy this to the right hand side is that at a certain point my debt is going to get paid off and I'm going to carry on paying debt down.

And all my debts, my senior debt will effectively go negative.

So I need a mechanism of stopping that happening.

And I could of course do this with an if statement, but I'm not going to do that. What I'm actually going to do is I'm going to use something called a minus min.

And again, if you've done a three segment model, particularly a three segment model with a cash sweep, you'll be familiar with this. So I'm going to do equals minus min and I'm going to go minus min.

Either the cash that I've got available for debt repayments or that senior debt balance, whichever one is the lower of those I'm going to bring in.

Now at this point you can see that's the lower of either the debts, which is 12.5 or the cash generated during the year of 1.9.

The lower of those is 1.9, so that's the maximum we can actually repay.

But as we go to the right hand side and the debt gets repaid, that will become the lower number and it'll stop us paying off, overpaying that debt.

So if I do a little alt equals at the bottom, so add up the value to work out the closing balance, I get that number 10.6, I can just grab all of that and copy it to the right and then we'll check that it does actually work.

So there we are, each of these years we're using all the cash available for debt servicing to repay the debts. That makes sense. Until here, until here we only have 57.9 brought forward and the lower of 57.9 or the cash generated at 3,332 is 57.

So basically we repay the 57 and that's it. After that becomes zero.

And just think about it in the later, in the late in the next year, the lower of 3,659 and zero is zero.

So we just include zero in that line. So it's a really great technique.

Now while I'm here, I'm also going to calculate the interest.

So I'm going to go and grab the interest rate on that senior debt.

And it's somewhere up here.

There it is, it's the 3.99% we calculated a little while ago.

I'm going to lock that in with dollars.

So I picked F four and then I multiply by, but what I'm actually going to do is I'm going to take the average of the opening and the closing debt.

I want it to be negative.

So it calculates the interest as that number.

And it's just worth making a little pause here.

You could of course calculate the interest just on the opening balance, but that would be a little bit unfair because the opening balance, the opening cap, the opening debt balance is 12.5, but by the end of the year we've paid off nearly $2 billion.

So unsurprisingly, we'd go to the bank and we'd say, of course, we want you to calculate your interest on a, you know, a weekly or a monthly basis, not just on the opening figure.

And that's why it's important we use an average, calculation here. The average of the opening, the closing.

So we're assuming it gradually gets paid over, paid back over during the course of the year.

And that's why that average function is really important for LBOs because we often have very big, very big and very significant swings in the debt balance.

And that's what we've got here. Okay, I'm happy with that.

I copy it all the way to the right and you can see, exactly the right things happening.

Basically as I repay the debts, the interest is gradually shrinking as I go until I get to this final year out here once I've paid off the debt. And of course interest is at that 0.0, as well.

Okay, this should be fairly easy to do for the, for the unsecured notes because it's going to operate in exactly the same way.

So the first thing I need to do is I just need to bring down how much cash I've got left and the answer in the first year is going to be nothing at all, but still need to do it so that we know when we get to the tail end, when we get to the right hand end, how much cash we've got a, after paying down the senior debt.

So I'm going to grab that 1,910 of cash generated add on how much we've used to repay debts, which is that figure there. And the answer is zero.

But if I copy this to the right hand side, you can see that by the time I get to here, I've only used 57 of that 3,332 that we generate in the year. Therefore I've got plenty of cash left.

I can now at that point start repaying the unsecured notes and then we do exactly the same calculation.

So our beginning unsecured notes is the same as the closing from the previous year.

We do a minus min formula to work out how much we're going to repay, which is the lower of the cash available for debt service or the opening balance.

Cause it's zero in this year, alt equals add them down.

Now let's just see if it works.

If we copy that to the right, yes it does.

We pay nothing at all until we get to this year here where we finally finish paying off the senior debt and then we're using our cash to repay the unsecured notes and as soon as they're gone, no more repayments. And that makes sense. Let's do the interest calculation.

So again, we're going to go and grab the interest rate on, these, which is somewhere up here.

It's the 7.7, there we go. 7.25% locked up with an F4 multiplied by the average of the opening and closing balances multiplied by minus one because we want it to be an interest payment and it's 545.

And if we copy this to the right, what we'll see is the interest is exactly the same each year.

And basically, the interest is exactly the same each year until we eventually start paying off the debt.

And once we're paying off the debt unsurprising, the interest falls to zero.

Okay? So we're almost there. We've got our interest.

The only thing we now need to do is to do exactly the same but with cash.

Now interestingly, we don't have any cash and for the first, for the majority of this, we're not going to have any cash anyway because we're going to use all of our surplus cash to repay the debt that we have, brought forward.

But when we get to the very end of this, we've paid off all of our debt at that point on the cash that we're generating, we'll start to build up and therefore we're going to calculate A, the cash balance and b, interest on cash.

So that's what we do. So we say what's the cash balance? The cash balance is equal.

So we do a very simple base calculation here.

Um, it, we start with the cash from the last year and then we say we've generated, 1,910 and we, but we've used certain amounts to repay it.

So use that cash to re make repayments on the debt.

So we've used 1,910 and we've also used, when we get to the tail end, when we get to the right hand end, we've also used some of that cash to repay the unsecured notes, add all of those things up and I end up with zero.

So again, the the the, the proof of this is if I copy that to the right, do we end up with a cash balance gradually building up at the end? And we do, we can do the same interest calculations. So we're going to say equals, let's go and find the interest rate.

Oops, something slightly odd there. Let's do that again equals let's go and find the interest rate, which is, up here.

Interest rate on cash is 3%.

Lock that in with an F four multiplied by that, that by the average again, of the opening and closing cash balance.

And I'm going to leave this positive now because, this is interest income.

Copy that to the right and we hopefully see, yes, we see at the very right hand end we see that there is some interest income.

So we're almost done.

First thing I'm going to do is I'm just going to push these interest amounts back into my income statements.

So if I go up to my income statements, now, again, I don't want to spend too long talking about this, but you might remember if you've done a cash sweep before, this is where we need to be a little bit careful because if we do this, if we push our interest back into the income statements, it's likely we're going to end up with a circular reference because the interest goes into the income statements and then the interest that, and then that income statement will include that interest then influences the of cash we've got available, which therefore influences the amount of debt.

So we have a circular reference, so what we're going to use is something called a circular switch and we already have a circular switch set up.

So I just start typing equals and I need an if statements.

Then I'm going to say if the circular switch is turned on, then link interest to the interest calculation.

If it's turned off, then make it zero.

So if I start with start typing circ switch, you can see that I've already got a named range set up.

So I'm going to say if circ switch is equal to one, then I want to take the interest expenses that I've just calculated from the senior debt.

Plus lemme scroll down the unsecured notes.

Otherwise, if the circ switch is off, I want it to be zero, I hit return and you can see that my circ switch must be turned off at the moment.

I'm going to do exactly the same for the cash interest equals if circ switch is equal to one, then make this equal to the interest on the cash, which is there.

I've gone well past it, otherwise make it zero hit return.

And I've now got, both of those working.

Going to copy them to the right and I know my search switches off, so therefore the first thing I'm going to do is actually going to turn it on.

So, it's on and I need to do control and page up jump all the way back to my info tab, which is here.

And there's my circ switch.

It's zero, I changed that to one and my interest should now flow into my income statements.

And it does, you can see I've got interest flowing into my income statements.

And it's doing an iterative calculation to get a stable rate of interest.

I've now got my last little section and we've got about 10 minutes to go. So we're just about on track to be able to finish this full model. So here it goes.

So I'm going to, first I'm just going to do a little tracker of the amount of debts that I've repaid.

Now you can see, you can see from our calculations here, you can see that I've paid off all of the debts, by basically by, oh, where am I? Unsecured notes. Basically by the last year I paid off all of the debts. So I'm okay. But what I want to do is I want to do a little percentage calculation to work out how quickly we repay the debt.

Because that's a key metric, a key measure for deciding whether an LBO transaction is a good transaction, whether it's going to get approval from the credit committee.

So I'm going to start with this little base calculation, which is just going to accumulate the total of debts that we have repaid.

So the first thing is it's zero.

So we haven't repaid any debt on at time zero at the beginning of the transaction because obviously we've just taken out that debt.

But when I get to the end of the first year, I want to accumulate that.

So I'm going to say the beginning opening debt repaid is zero.

And then I'm going to just simply add on the debts that we have repaid.

First of all from the senior, debt balance and it's there. It's 1217. I'm going to flip the sign.

I'm going to multiply it by minus one.

Oops, that was the wrong keyboard shortcut.

And then I'm going to do the same with the unsecured notes.

So I'm going to say unsecured notes, repaid, first year is going to be zero.

Flip the sign to make it positive, get to the bottom, add everything up.

And that basically says in the first year, I've paid at 1,217.

If I copy this all to the right.

And basically you can see that we gradually repay all of the senior debts and then we repay the unsecured notes.

And then we get to the very end and we've repaid all of the debts that we took out, both the senior and the unsecured notes.

18,804. I'm now going to do a little calculation to basically work out a percentage that we've, repaid.

So I'm just going to pop the total in here.

I'm going to say my total debt is, oops, I don't want to do that. In this sale here, my total of debt that I took on is simply the total of those two amounts.

18,804. And you can see that's what we've repaid.

Just going to do a little percentage calculation, which says equals that number there, the amount we've repaid, divided by that opening debt, lock that in with an F4, that gives me 6.5 percent.

Is that right? Yes, that looks about right.

And if I copy this all the way to the right, then you can see by the final year, by the eighth, year we've repaid a hundred percent.

Now, the reason this is important is because the credit committee won't approve this.

For any bank, for any lender, if you are projecting that less than 50% of the debt will be repaid within seven years, it's a really good rule of thumb.

So let's just have a little look.

One, two, 3, 4, 5, 6, 7, get to seven years out and we've repaid 84.4% of the debt.

So we're absolutely fine. We're well above that 50% level.

In reality, of course, we actually didn't borrow that much for this transaction.

So it's not really surprising, that we're able to do that.

Okay, now our final section then is this any good for our equity holders? How do we work out how much they're going to get back? Whether that gives them a good IRR, a good rate of return.

So the starting point is we need to value the business, the exit value of the business.

So I'm going to just put a year counter up here.

So this is just a simple, you know, start at one, copy it to the right, add one every year, and I get a year counter here. And the reason I'm going to do that is because if you remember right at the very top, we are assuming that we walk away, we sell this business in year four, that's one of our assumptions.

But, therefore we need to value the exits as at year four.

But if we make that a variable, we can actually see what happens if we walked away, if we sold at the end of year three or the end of year five, or any other, any other year.

So we're going to say at the end of the year one, how much is the business worth? Now you'll remember I mentioned it's a very long time ago.

We calculate EBITDA for this business solely so that we can work out how much they're going to sell the business for, how much it's worth.

And what we're going to do is we're going to go up to that exit multiple of 25 times.

I'm going to lock that with an F four and then I'm going to multiply that by that EBITDA figure. And I think I might have even made it yellow.

Yes I did adjusted EBITDA in yellow, I hit return and it says, at the end of the first year, the business is worth a cool 87.9 billion euros.

That's a pretty sizable number, isn't it? And then what we do is we work walk around the EV to equity bridge. So we say if the enterprise value of the business is 87, subtract the debts that we've got at that point, that's going to leave the equity as the balancing figure.

And that's another reason for calculating this total debt.

That we have repaid.

It kinda helps with this calculation.

So we could say what's the 18 minus that number? So we could say equals 18.804, which is our total of debt.

F4 minus that total of debt that we have repaid gives us 17.85 of debt.

We have repaid.

And that if I just subtract that debt from the enterprise value, 87 minus 17 gives me an equity value of 70.3.

That's the number. Okay, copy that all the way off to the right and hopefully we will see that the debt falls.

Yes, the debt falls by this stage and we've got that really substantial increase in equity as we go along.

Now, gotta be careful with this because we're not in any way adding up all of these equity amounts.

All we're doing is saying if we left at the end of year one, that would be the equity that we would get.

If we left at the end of year two, that would be the equity we'd get and so on.

So we just want to bring down the equity in our exit year, which is in year four.

So we're going to do that with the an if function.

Now I'm just therefore going to put in, so I can do an IRR calculation.

The amount of equity that we tipped in right at the beginning, which is that 60.3, multiply by minus one gives me that opening equity, figure there.

Okay? Just actually going to put, move the formula over to that side.

And then what we do is we say if it's the exit year, I want to bring down that equity value.

So I'm going to do that with a little if statement. So I'm going to say equals if, if the year counter is equal to my assumption for exit, sorry, I just hit another key equals if, if the year counter is equal to the exit year, which is somewhere up here in our assumptions, there it is year four, then bring down the equity figure, otherwise bring down zero, I hit return.

And of course in my first year I get nothing.

Now if I just copy this to the, ah, I didn't, I didn't lock in E18, that's my problem there.

Let me just do that, function F$4 on that and then copy that to the right. There we are. So you can see that in year four I bring down the equity figure and then the rest of them it's zero.

So I'm just going to copy that all the way to the right and now we can do an IRR calculation.

So our IRR calculation simply says IRR using the normal excel function of that stream there and it gives me an IRR of 16.6%.

And that's pretty good.

It's not amazing but it's pretty good.

Now why is it pretty good? It's basically pretty good, not because of the debt that we're repaying, which is significant, but it's not really that material for the size of the acquisition.

I would argue that the thing that's really driving this to give it a fairly good IRR is actually that increase in the enterprise value.

You can see it's a very rapid growth in the enterprise value and therefore that's driven by that rapid growth in revenue.

And that's what's really making this fly as a reasonable IRR.

Now, just to add one extra bit to the end, we're just going to do a little data table right at the end just to sensitize a couple of the assumptions.

So I'm going to put in the top corner of my data table the IRR, which is let me just say equals formula text, which is just F 77 in that top left hand corner of our data table.

And then we just highlight the data table.

Oops, there we are. So we highlight all the headings and we've got basically exit year, years three, four, and five as one heading.

And then we've got that entry multiple, you remember we go in at 25 per 25 times.

So we're basically doing a tweak that we're going to adjust that, and see what happens to our irs.

So I highlight the entirety of the data table and it's a great keyboard shortcut, which is alt dt, which is a great keyboard shortcut alt data table.

And then we're just going to plug in the numbers.

So what do we have in a row? We have the exit year.

So what we need to do in this cell here, in this box here, we basically need to point it to that assumption on the exit year.

And it's the four that was up here. Click, there we are.

And then in a column, what's in a column? Well, in the column is the entry multiple.

So in that little box there, the column input cell, I need to point towards that 25 times entry multiple.

I click okay, cross my fingers. And there we are.

We've got a populated data table.

Now the first thing to do with any data table is just check that in the middle of it, you actually have it matching the results for your, for your calculation. And we do here, we've got 25 times entry in year, exiting in year four and it gives me an IRR of 16%.

Now I think what's interesting is, and this often happens with an LBO because you get a rapid rise in value if you exit quicker.

So in this case you exit in three years, not four, you get a better return if you exit slower, you exit in five years, not four, you get a lower level of return.

Now the, the other one is a little bit more intuitive.

If you pay less for this business, I, your entry multiple is lower, unsurprisingly, your IRR goes up, you know, and that's, you know, that's fairly obvious, isn't it? You just basically get a better deal on buying Red Bull, and therefore you pay less for it.

The debt becomes a greater proportion of the consideration and you pay down, a greater proportion of the total consideration and therefore when you walk away, when you exit, you get a better rate of return.

So that all of that makes sense.

So, and as if by magic it's exactly a couple of minutes to the hour.

So I hope that that's been useful.

We've had a really significant sort of, almost a whistle stop tort of a very significant, LBO model here.

So we have gone through this pretty quickly.

I know, but this is a whole LBO model.

So hopefully you've found out, hopefully you've, got a little bit of an understanding of what an LBO model is, how an LBO works and an understanding of how you would actually try and model an LBO using Excel if you've got an already or existing, operational model.

Okay? So thanks for your attention.

I can see that almost everyone that joined is still here, so that's brilliant.

Thank you very much for sticking around.

Hope that's been useful.

Remember the recording of this will be, on our website in the next couple of days.

Catch up if you need to there and I hope to see you on another one of these very soon.

Okay, thanks very much and have a great weekend. Cheers. Thanks a lot. Bye-bye.