Welcome to, uh, this, uh, session.

Um, uh, as you can see ho hopefully, as you can see on the screen, uh, my name's Phil Sparks, and I'm a tutor, uh, a trainer at, um, at, uh, full-time trainer at, uh, financial Edge.

Um, and I'll be taking this, uh, final, um, LBO, uh, session, um, uh, for the next approximately hour, possibly a little bit less than an hour, but, um, uh, it will, there's a reason amount still to get through, so it'll take us about an hour.

Uh, yeah, I can see a good number of people online.

So, uh, welcome to, uh, this session.

Um, just kind of a little bit of preamble.

Um, this is the third of, uh, three, uh, three consecutive, uh, sessions.

Uh, Gerard took the very first one. My colleague, Jonathan, took the second one.

And my, and me, uh, Phil, I'm gonna take the, uh, third one.

Uh, it's all about building up a, um, um, a reasonably complex LBO model.

Um, uh, Gerard set up the model, uh, did the basics of the income sentence, uh, and the, uh, then, then Jonathan did the balance sheet and started the, um, the, uh, debt schedule.

I'm just gonna finish off that debt schedule.

Um, and then, uh, use the results of that to wire that back into the, um, uh, the balance sheet.

Um, and hopefully at that point we have a balance sheet that balances, um, and a full state, a full three statement model that's, uh, working, uh, correctly.

Um, once we've done that, um, we can then go and finish off the, uh, the spreadsheets and have a look at their overall returns to the, um, uh, to, to, to the, uh, various, uh, stakeholders.

And the key is, uh, there is a spreadsheets, uh, that's an Excel spreadsheet. They all kind of look, look a little bit the same, but it basically says, um, it's one that says for webinar, if you basically download the version that says, for webinar, uh, then you'll get the version that I'm gonna look at today, which is, uh, pretty much where you finished, uh, with Jonathan the last time.

If you weren't around, uh, for Jonathan and Gerard's sessions, um, I'd recommend you go back and look at the recordings.

Uh, for those, this session is gonna be recorded, or rather it is already being recorded.

Um, so, um, uh, do, um, uh, you, you, you can look back at this session. You'll be able to get it from Felix exactly the same place, uh, the same landing page that you arrived, uh, to actually get into this, uh, session.

Uh, and so you'll be able to go back over it, um, look at, look at it, stop and start it, look at it a little bit more slowly if you want to kind of catch up on what we're going to do.

Um, and I'm just going to about three minutes past, so I will just gonna pop the same link in. And I know I've done this about six or seven times.

Uh, but the problem is with Zoom, that that chat box is only available from the point where you arrive.

Um, you can't see any historic chats in that.

So if you've just arrived in the chat box, there should be a link to the Felix session.

And what you want is something called the, um, uh, for webinar Excel documents.

Um, and that's exactly what I'm gonna look at.

There is also a fully complete version as well, so don't worry, you can grab the complete version.

You don't have to kind of work through this and get the right answer.

Um, if you basically, um, uh, if, if, if you, if, if you work through, um, with me, that'd be great. You'll end up hopefully with the right answer. But if you make some mistakes on the way, um, or you want to absolutely make sure that you've got the final version, then just go and grab the full version of this from, uh, that website. And I can see just another couple of people just arrived.

So, apologies. I am gonna keep on, uh, popping this into the chat box, just so that everyone has got a, um, a link to this w uh, to, to, to this file.

And as I said, it's the four webinar Excel file that you need.

Okay. So without further ado, quite a bit to get through, um, I'm gonna open up that file, um, and somewhere here, here it is.

And you can basically say it's, you can see right at the very top, it says, for webinar.

Uh, so this is the version that I've got, um, on that you, uh, that you need.

And it's the version that I've got screen at the moment.

So just a little, very quick rattle through a recap of, um, what this spreadsheet looks like and why we're using this and where we've got to.

And then without further ado, we'll start completing the rest of the spreadsheet.

So here it goes. Um, I think it's worth just putting, putting, um, this in place.

Um, putting a little bit of background behind this.

Um, the reason why we are using this spreadsheet go something like this.

Uh, Devons was a very big, um, uh, UK based, um, department store.

Um, it actually went bust at the end of the COVID period, but the reason we're going back a little bit earlier than that is because it was a very large, um, um, um, um, department store, and it actually went through two consecutive, um, LBOs, two consecutive, uh, pe um, acquisitions within a relatively short space of, uh, time.

Um, so there's a lot of information in the public domain.

It's also fairly understandable, fairly, um, easy to to, to, to, to understand what's going on.

It was a big, uh, very big, uh, traditional department store, uh, with lots, lots of property assets.

Um, and it's, um, you know, the whole shift to online shopping sort of really, um, uh, um, sort of, uh, caused it to, to take a downturn, uh, and its sales and its profitability.

Um, and it went through, uh, two pe, um, res uh, uh, acquisitions and restructurings, uh, to try and free up a bit of cash and to try and turn it round into a profitable, uh, business.

Um, and they were, you know, uh, it's, it's, it's fair to say, uh, they, um, were mixed in their success.

Uh, the first one, uh, it did fairly well for a fairly, uh, short period afterwards.

And then the second one, uh, COVID came along and, um, basically the, uh, the PE firms, uh, lost their shirts, uh, shall we say.

Uh, they certainly didn't make very much money on it. Okay? So that's why we use this.

Um, uh, even though it's a good few years old, it's still absolutely a very valid and very, uh, good, uh, case study.

Um, so let's just have a quick little look through.

I'm just gonna walk through this spreadsheet just to remind you a little bit of, uh, what's in here.

The very first page, LBO tab basically sets up the acquisition.

It says how big the acquisition is, what's the enterprise value, and it really crucially, this section in the middle basically says, this is how the LBO was financed. So very large, um, very heavily levered or heavily, uh, debt financed acquisition.

Uh, lots and lots of, uh, amounts in here. These are all in, uh, millions.

So 400 million, uh, pounds for one, uh, one loan at 511 million pounds for another preference, shares of 622 million pounds.

So lots, lots of, uh, lots of big numbers washing, uh, around, uh, there.

Um, the, we then have some relatively sta uh, relatively, uh, tabs, uh, which look to some extent, just like a fairly standard three statement model.

Um, some income status, income statements and balance sheets, um, assumptions.

So revenue rise costs as a proportion of revenue, um, uh, working capital as a proportion of either revenue or, um, cost of sales and, uh, tax rates and so on.

Um, uh, we've got standard calculations in here.

Um, so our standard base calculations for pp and e plus CapEx minus depreciation and so on.

Um, it's worth just pausing and looking at the equity section.

Um, the equity section has got obviously, uh, net income going in, um, and then it's got preference dividends coming out, and it's worth just pausing a little bit at that point.

Um, so you'll remember from the very first tab, there's a big amount of preference shares 622 million pounds worth of preference shares. They're pick notes, IE there's no preference dividends.

Um, but the preference dividends arrive, uh, sorry, sorry.

The, um, uh, the preference dividend, um, isn't actually paid.

It bills up. Um, but that preference dividend goes through the books.

It doesn't go through hitting cash 'cause it's not paid.

Um, it goes through hitting retained earnings.

And this is how it does it, it goes through this equity, uh, section.

Um, here. Um, you'll, uh, um, hope, hopefully be aware that you can use, uh, preference dividends. You can treat preference shares as quasi debt.

Uh, so the, the preference shares sit on the balance sheet within the debt section, and in that case, the preference dividends goes through the interest line.

Um, this isn't doing that.

It's treating preference share actually as part of equity.

And it's then basically saying that the preference dividends just hits retained earnings directly, uh, rather than going through the income statements as interest.

Um, we've got fairly standard income statements and balance sheets, uh, sections.

Um, the income statements is pretty, pretty straightforward.

Um, and then we've got the balance sheet. Now the balance sheet's a little bit interesting.

I'm sure that, um, Gerard took you through this and described in a lot of detail how the balance sheet works.

Um, the reason why these sheets are a little bit unusual, why they've got this sort of gap in the middle, um, where the years jump from 2015 to 2016 with this three column of gap in the middle is because that's when the transaction happened as at the end of December 15.

Um, and so we've got lots and lots of entries in the balance sheet, basically reflecting the transaction.

So refinancing the debt, zeroing out the existing equity, creating the new debt, creating the new equity, creating goodwill.

All of those things basically happen in, uh, these two columns, um, in the middle.

And we also use the same layout for the income statement, the balance sheet, and the cash flow.

So we have that matrix integrity, that sort of natural check that where whenever we're in, say, column K, then we are likely to be pointing to another column K somewhere else in the spreadsheet. And it just gives us that natural check, less likely to make errors if we are consistent amongst the different tabs.

Uh, cashflow exactly the same.

Um, uh, we only start from the projected years. There's no point trying to reinvent the wheel to try and restate the cashflow, um, for two reasons. First of all, there's no value in doing that.

Second thing is that a real cashflow is much more strai, much more complex, um, than even this relatively straightforward, um, approach here.

You just simply, even if you put your cash flow, all of the entries in, you won't match the real cash flow in the statutory accounts for this entity.

Um, in history, there's always a little bit more going on than even, um, something like this, a 20 odd line, uh, cash flow.

Um, also worth just pointing, pointing out that this cashflow looks a little bit odd, it looks a little bit odd, basically because you end up with some very, very big negative numbers at the bottom, you end up with negative cash of 755.

And you can see that if you look at the balance sheet. And again, I'm sure that, um, uh, Gerard and Jonathan explained why this was the case.

Basically what's going on here is we haven't yet completed the debt schedule, so we don't have debt in the balance sheet.

If I just go and look at the balance sheet, you can see I've got a big sort of missing section in here.

I've got a big missing section here, which is where all the debt items are gonna go.

So what's basically happening is the cash flow for this, this year, for the first projected year is saying, you used to have all of this debt and now you haven't got any, basically, 'cause the cells are empty, we haven't completed it yet.

So therefore the cash flow is doing that logical thing of saying you must have repaid all of the debts.

Um, and that's why we end up with an enormous negative number for, uh, the ca the, uh, cash number up here. Because the cash flow is assuming that you've used all of your cash and more to basically repay that debt.

Once we populate to that section of the balance sheet, once we populate the debt section of the balance sheet, hopefully this will disappear and we'll end up with a normal, sensible, uh, positive cash number.

Also, when we look at the balance sheet, while final thing to point out is our balance sheet doesn't quite, um, doesn't quite, um, uh, balance, well, actually, it's not that it doesn't quite balance, it doesn't balance by a huge amount or what can I see? One person's just joined.

I, I've just put, uh, a link in the chat box if you've just joined, um, which will take you to the, uh, material for today.

We are looking at a spreadsheet that's got four webinar in the title.

So if you download that spreadsheet, so we've got this, um, mismatched balance sheet here, 922 out.

Why is that? Um, well, it's always, um, a little bit more comforting if I've got a mismatched balance sheet and it's the same number.

I know it must be an opening balance issue, and it's easy to spot with this one. Basically, it's something to do with these two lines here.

It's the mezzanine and the press.

Um, in total they add up to 922.

So for some reason they're just not appearing, um, and not going through the cash.

And, and the reason why is because they're pick notes.

Um, they don't appear in the cash flow. The cash flow is not saying that we've repaid these, it's just not picking up that, um, entry at all. Once we settle these, once we put these amounts in, um, basically the balance should hopefully, fingers crossed, the balance sheet should balance.

Okay, so we're gonna go to our final, uh, page.

Um, and there's really two, uh, sorry, three things that we're gonna do over the next, um, uh, 40 minutes or so.

The first thing is we're going to go to the debt schedule and we're gonna complete the debt schedule.

Um, uh, I know that j um, uh, Jonathan did the majority of the debt schedule, but not the final couple of entries.

He did the basic loans, but not the pres and not the mezzanine debt.

So we're gonna complete those, we're gonna calculate the interest, and then we're gonna wire all of those results back into the balance sheets and back into the income statements.

And at that point, we should have a working model at that point, we can then work out, um, what are the equity holders, what are the fund, what are the pe, what are the various stakeholders, get outta this on exit and does it give them a nice healthy IRR or not? Okay? Um, so that's where we are.

Uh, again, I could just see one more person has arrived here.

I'm gonna paste in, uh, the, um, uh, link. There's a link in the chat box that will take you through the, uh, material and you want to download the four webinar, Excel, um, documents.

Okay, so let's go. So let's go and have a look at the debt schedule.

What we've basically got at the top is a standard, um, debt, um, elements, uh, standard debts, uh, cashflow available for debt service.

And basically we take the existing cashflow and we have everything in the cashflow except for the, um, interactions with our debt providers. So we don't have any either repayments of debt or drawdowns of debt.

Um, and then what we do, like a normal cash sweep, which I'm sure you're all familiar with, is we basically take that money that, uh, cash generated and we go through each of the tranches of debt and work out whether we can repay, uh, those.

And obviously with an LBO, what we're trying to do is repay the debt as quickly as we possibly can.

Now, I know that j uh, Jonathan, uh, went through the basics of this.

Got you down to, um, the, got you down through the, uh, the first, um, two chunks of debt, the first leann and the second leann, uh, debt.

Um, and I'll, but I'll just recap very briefly what's going on here.

Um, with each of these, uh, these are the amounts that we're picking up from that very first tab, the LBO tab, uh, from basically the amount of debts that we, uh, took on to, uh, do the LBO transaction.

And then what we do is we first of all take off a mandatory repayment.

And that 49.6 is basically taking the opening 400.

And I scroll up here and I've got 12.4 is a mandatory repayments in the very first year.

Interestingly, if you highlight all of this wrote, you get a hundred percent.

So all of those percentages are basically saying we gradually repay the debts over those, um, uh, nine or so years.

Um, we then look at this next line here, which has got that typical cash sweep minus min formula.

And what it does is it says, if you've still got any cash left over, um, and we have, we've got that 79.8 and that's subtotal just above.

Um, if you've still got any cash left over, then what we need to do is we basically need to use that to pay down the debt as quickly as we possibly can.

Um, and that's why we have this particular debt, uh, basically falling, um, over the 1, 2, 3, 4, 5, the first five years.

Interestingly, um, it said the mandatory payments expected to be paid over nine years.

We've paid it off in five 'cause we're using all the cash we're generating to pay down this debt.

Um, there's a couple of other subtle points.

The first is, um, that there's, in this, uh, line here, there's a link to this LBO, um, uh, entry and back on the LBON, back on the LBO tab.

Uh, there is a number here of 75%.

What it basically says is that we are gonna generate cash, but we're only gonna use 75% of that cash to make accelerated payments against the existing debt.

And what that hopefully means is that we'll end up with a cash balance gradually increasing, um, over the years.

And for a retailer that's really useful.

You know, just imagine, you know, and certainly kind of when this happened, when this deal happened sort of nearly, nearly 10 years ago, um, if you went in, uh, with a 50 pound note and tried to pay for something, you'd expect them to have cash in the till to give you some change.

And really, that's why they need ready cash. That's why they need some cash, and that's why we're not necessarily using all of the cash.

So we run a zero cash balance, um, for the course of the LBO transaction.

So that's a little recap of where we've got to.

Um, so, uh, we're down at the bottom.

We've gone through our firstly end. Secondly, end and junior notes, uh, junior notes is empty. There's nothing in here.

Um, so, um, we can now get a little bit further.

Um, and these two items here that I'm gonna, uh, deal with, but I, I don't think John Jonathan did.

Um, um, at the end of his, uh, session, um, I'm just gonna add these onto the bottom and then we'll have our, um, schedule, uh, debt schedule largely, uh, done.

So these are pick notes, the mezzanine, um, LO loan and the, uh, preference, uh, shares. The mezzanine loan is basically a loan that we took on and it's, uh, I think it's 300.

We'll go and pick up that number in just a second.

Um, so the mezzanine loan is 300, and it's a pick note. So what that means is, uh, we get charged interest, the interest hits our income statements, it's an entry into the income statements, but we don't actually make the payments.

What happens is the mezzanine loan gradually builds up over the course of the, um, of the, of the, uh, schedule here of the forecast.

Um, and basically it gets to a slightly bigger and bigger number.

And then when we exit the LBO, that amount gets paid out.

Um, it's called mezzanine because it's a hybrid of equity and debt.

So basically the majority of it, 300 of this is debt, but, um, the, uh, holders of that have the, um, ability to buy shares or rather, they're actually in this case gonna be given 5% of the shares, um, on, um, exits, uh, by way of, um, warrants.

Um, and therefore, um, they will take a very small, uh, share of the rise in equity value, assuming there is, assuming the equity value does actually go up.

Okay, so we start here.

Um, and let me just get my, uh, notes in the right place so I can just check my numbers are are correct as I, uh, do this.

Okay, so we've got mezzanine loan here.

Uh, the first thing we're going to do is we're gonna go and pick up the, uh, the closing, uh, balance, um, or rather the opening balance.

And so what we do is we get to the ending balance and we're gonna go to the balance sheets to find that, um, to actually find that number. So we're gonna say equals uh, control page up.

It'll jump to the balance sheet and we're gonna go down to the, um, uh, the combo, which is the, effectively the opening balance, it's the closing balance of, of the balance sheet, um, when we've just done our deal, and we're gonna go down to the Mez line here, which is that 300 that I mentioned.

Uh, we hit return and I will just put an equals formula text alongside this so you can see where the entry comes from.

So we grab that from um, I 25 on the, uh, balance sheet.

So there we are. Is that right? So I agree with that, yes, I do. Okay.

Um, and that then becomes the beginning balance.

At the beginning of the following year.

We get to the end of the year and we say how that you've had that 300 all year.

Um, how much has the interest, we're gonna add on some interest for this.

So we're gonna say equals, and then we need to go all the way back to the LBO tab, which is here, and find the interest rates on the mezzanine debt, which I seem to remember is 12%, which is somewhere out here.

Yes, it is there. It's, um, so it's in k um, 18.

K 18 is the 12%, uh, mezzanine loan, uh, pick.

And we need to do an F four to lock that in with dollars.

So as we copy it to the right, it's uh, it stays there.

And then we go all the way back to the debt schedule pointed at the opening 300, and we end up with 36 of, um, debts, sorry, 36, 36 of interest added, added onto the debts. That's what I meant to say. Okay. So, uh, that gets us 336. We're gonna do exactly the same with the preference shares.

So we're gonna go and find the ending preference shares at that, um, uh, that, uh, combo, uh, stage, in fact the opening, uh, level of preference shares.

And we basically go back to the balance sheet. Let's go find those preference shares. There's 600 and something million.

Yep, there's the number there.

Um, I, uh, 26 hit return 600.

There we go. Uh, copy that formula text down.

There we are. So we end up with 622 and then we do exactly the same process again.

Uh, they're basically pick notes.

So we pick up the opening 622, and then we're gonna go and work out our interest, which I'm pretty sure is exactly the same interest, uh, exactly the same 12%.

But let's go and grab that from that LBO tab equals page up.

Let's go and find LBO, which is somewhere down here.

There it is, 12% on the prefs.

And again, they're cumulative prefs that builds up over time.

They're not actually paid out. It's a payment in kind.

So we take that 12% multiplied by, oops, and I forgot to put F fours.

Then we multiply by, and let's go back to our opening number there. It says, I think that's right. It's got dollars. Yep.

Um, and then alt equals at the bottom and I end up with 600, um, and 96.8.

Um, nothing really, uh, super complex in that.

So let's grab all of those, copy them all the way to the right.

Uh, they are, and as you can see, basically what's happening is these are gradually building up and it just shows you the power of compounding, doesn't it? You know, with both of them, you're getting interest on interest.

Um, so they more than double in size over the course of the, um, the, uh, eight or so years. 1, 2, 3, 4, 5, 6, 7, 8, 8 years of, uh, forecast. Okay? Alright. So we are getting towards the end of our debt schedule.

Um, what we need to do is we now need to pick up the interest, um, and then wire the debt balances and make sure the interest balances go back into the income statements and um, uh, and the cash flow as well.

So the first thing we're gonna do is we're gonna pick up the interest amounts.

So this is fairly easy except for a couple of wrinkles.

So I'm gonna go equals, and this is one of those few times we're actually using the mouse rather than using your keyboard is actually a little bit easier.

So I'm gonna use my mouse to scroll up and down and I'm just gonna point at each of the interest lines.

So I'm gonna point at the interest on the revolver, which is, um, uh, zero in that first year.

But you can see it builds up, um, due to that odd cash flow we've got at the moment where we haven't wired the cash, the debt balances back in.

So we're gonna pick up the revolver interest, we're gonna pick up the interest expense on the first Leann.

Um, as I say, it's easier actually to do this with your ma a combination of your mouse and your keyboards.

Um, so we end up with, um, the revolver, the first Leann, the second Leann, um, the junior notes interest as well.

And I'm just gonna be just gonna pause a little bit.

I need to also pick up the mezzanine interest, the interest on the mezzanine debt.

But all of the other items are negative.

They are an expense and that's okay. That's all right.

I've got that negative expense on that.

Um, on that previous, um, uh, I've got that, uh, negative expense on those other forms of debt basically 'cause it's a cash outflow because we're building up this mezzanine debt.

We've got the interest actually shown as a positive amount, but when it goes into the income statements, it's gonna be negative.

So what I need to do is just hit minus or a negative sign before I point at that 36.

That's one of the wrinkles i I mentioned. There's another wrinkle. It's very tempting to then click on this number here, the preference share dividend.

But remember, I, that's why I, I explained that fairly carefully before in that sub calculation on equity, the preference dividend is treated as part of equity.

It doesn't go through the interest line in the income statements.

Okay? So we need to be really careful.

We are not gonna pick it up here because these two amounts are gonna go into the income statements, which in turn goes into um, the, um, in, into retained earnings.

If we did this, if we included this amount within the income statements, we'd be double counting that interest.

It already hits the incomes it, sorry, it already hits retained earnings within that equity calculation.

So we're gonna gonna ignore it.

So I hit return and I get that number there, 104.7 and it's negative.

A very big negative number.

Now the other thing I need to do is alongside this, I need to actually work out which of this is paid in cash and which of it isn't.

Um, so we're gonna have a subtotal here, which is basically gonna be the same number as above the 104.7 less any elements of it which are not paid in cash.

And the element that isn't paid in cash is the mezzanine interest.

So this cash interest line is gonna go into the cash flow.

We don't wanna put this number here, this 104.7 into the cash flow because it's not paid in cash, it's only the interest on all of the other loans that is paid in cash.

So what we're gonna do here is we're gonna say equals that 1 0 4 0.7, but I want to exclude the mezzanine interest.

So what I actually need to do is I just need to add back that 36.

So remember the 36 we flipped the sign.

So to reverse that minus 36, I need to add 36 and I end up with that number there, 68.7.

There I am. So I copy this all the way to the right and actually nothing yet will happen in the income statements or the cash flow or the balance sheet.

So the first thing we need to do is we need to go and wire their cash flow, sorry, wire the balance sheet into um, this debt schedule.

So we're gonna go back, oh, I see one more person has arrived.

Um, I'm actually just going to do what I did before and if you've just arrived, Oops, that wasn't what I wanted to do.

Just let me copy that.

If you just arrived, I've just put a link into the, uh, Felix website, which will enable you to download the um, document that we're currently looking at. It's the one that says four webinar. Okay? So we're right at the bottom here.

What we now need to do is to why at the balance sheet into those closing, um, amounts, those closing amounts.

Uh, so I'm gonna start up with the revolver and I'm gonna point the revolver line to the closing revolver balance in the, um, debt schedule.

So I just go to the debt schedule, I go and finds my revolver closing balance, which is somewhere up here, cashflow for revolver.

Issuance repayment. There I am.

It's line 25, it's the closing revolver balance.

Um, it is um, j at 25 and it ends up being zero.

I'm just gonna check 'cause uh, this is one of those moments where if I point, if I'm pointing at the wrong column, pointing at the wrong place, it's all gonna go horribly wrong.

And it's JJ 25. Yeah, so I like that.

Um, so just put a little subtotal, a little, uh, title there.

So I'm pointing at the debt schedule J 25 for the revolver and I know it's zero, uh, but let's not worry too much about that.

Uh, for the moment, I'm gonna copy that all the way to the right and you can see that I gradually get this revolver balance building up.

Now that's because that's basically because we don't yet have debt elements in, um, and therefore it's assuming that my revolver is having to draw down on my revolver to repay those debts amounts.

Let's go and plug in the, um, the elements of debt.

So first, Leanne, I do exactly the same thing I say equals use control page.

Um, down, let's go and find the debt schedule and let's find the closing first Leanne debt.

And it's that number there again, it's J column J and this time it's J 34 and it gets me 270.

Um, and interestingly you might see as I do that then my, um, cash has gradually fallen.

It was something like minus 755 and now it's 484.

It's not quite so bad.

And that's basically because we are now starting to look at the movements on the debt properly and therefore the cash flow is starting to work correctly, uh, assuming that, uh, we're not now paying all of that debt down in that first year.

Next we say equals and do exactly the same with the second Leanne.

And again, uh, find the ending balance column J 511.

And of course everything goes a little bit haywire when I put formula text in, in because it's pointing at the next line.

And that's ripping through into the cash flow and at, so on.

Uh, but again, let's just have a little look.

I put that second chunk of debt in on what happens to the cash.

Now the cash has gone positive.

Um, so, uh, doing this is basically starting to set, uh, starting to create a much more plausible, uh, balance sheet.

Same thing with the junior notes. Let's go and plug those in. There's zero anyway, but let's do it for completeness.

Junior notes J uh, 49 and the same thing with the mezzanine debt equals 336, our closing figure.

And then finally our pres equals, let's go and find the pres.

My preference shows are down here, 696 and we end up with that number the, okay, uh, so we've plugged our numbers in. Let's just have a little look at what's happening before we copy all of this to the right.

Always a good idea to do this.

So make sure that you are comfortable.

You, you know, it's doing what the, the spreadsheet is doing, what you anticipate.

Um, the first thing is we end up with a very small amount of cash at the end.

That's probably okay.

'cause remember, we're only using 70, 75% of the existing cash generated to make repayments against the, um, against the, the debt.

And it should leave a small cash balance.

And that's exactly what we can see here.

Secondly, if I go down to the very bottom, I was hoping that my balance sheet would totally balance.

It almost does. It almost does, but not quite.

It's 36 out. Um, but there is a reason for this.

Um, and the reason for this is if we go to the debt schedule, I know that there's a 36 number and it's that number there.

It's the mezzanine interest, it's the mezzanine interest.

Um, so I think this is gonna be okay, I'm gonna copy all these numbers to the right and then we're gonna have a think about that, um, that mezzanine interest and why.

Um, it's basically, excuse me, uh, why this is basically causing my balance sheets to not quite work.

So I go back to my balance sheet, I copy all of this to the right across, it goes, all of those values disappear.

Um, what does my cash do? Let's go and look at my cash.

My cash does something sensible.

It stays at a relatively modest level all the way out to here and then gradually goes up.

So I'm guessing that's because in those early years it's using all of the cash to repay the debt.

And only in the later years, um, are we starting to build up some cash balances once we have repaid all of the debts.

Now let's just check that that makes sense.

Let's go and look at the debt elements.

So the very big, firstly end debt 400, uh, is my starting point.

It gradually falls and by year, 1, 2, 3 year by year three, we've repaid all of that and we start repaying the secondary end debt.

We don't have any junior notes.

Um, so basically until, um, uh, we get to that point there, um, which is year 1, 2, 3, 4, 5, at the end of year five, we've repaid all of our debts.

Okay? So it's at that point where our cash balance starts to gradually increase.

So it makes sense, it's doing something vaguely sensible.

Now what about this mismatch? Um, I've got 36 at the bottom, 76.3 the following year. Let's go and have a look at the, um, interest on the mezzanine, uh, debts and see if that makes a little bit of sense.

Remember 76.3.

Okay, so I've got 36 in the first year, and then if I add up the first year and the second year of interest, I basically get at 76.3.

So it's definitely this that's causing my balance sheets to not quite work.

Now let's have a think about interest.

What's happening with interest? Well, the first thing that's happening with interest is when I go and look at interest, the answer is, ah, nothing, nothing's happening to interest.

Interest is not going into my income statements.

And when I go and look at my cash flow, it's also not going in.

And that's because I've got a switch.

And again, I'm sure, I hope that, um, uh, Gerard or Jonathan took you through the switch.

Um, basically this is because we have got a crc, a potential circular reference.

The interest calculations are on, um, for the loans and the revolver are based on opening and closing balances, taking an average of those.

And then, um, calculating that interest and that creates a circular reference.

So we have a switch that goes on and off.

So we can break that link, we can break the circular reference.

Uh, 'cause problem is with circular references is they create an inherently unstable spreadsheet.

So my guess is that the switch is off and the switch is actually, um, somewhere there on the input tab.

There it is circular switch and it's set to zero at the moment.

What that means is my interest, the cash interest at the moment is actually working.

Um, and I mean that sort of advisedly, um, it's zero in both cases.

So because the switch is off, the interest that we've calculated on the cash elements is going into the income statements, but it's zero.

It's going into the cash flow, but it's zero.

So they're in line, the cash is changing by nothing and the income statements is changing by nothing as well.

But the problem is the interest on the mezzanine debt is not changing.

So, um, it is not being handled consistently.

It's basically adding up in the debt section.

So the mezzanine debt in the balance sheet is going up, but the other side of that is not going into the income statements. That extra 36.

So if we turn on the circular switch, all of our interest starts hopefully to work.

Let's check, I go to my income statements and I've got an interest expense now 105.7.

That's sort of what I remember in that very first year, gradually falling as we pay off the debts.

If I look at my cash flow again, I've got cash interest, 60 something in the first year.

Again, it's the cash bit, it's the interest without the mezzanine debt.

And that's going into my, uh, cash flow so they're consistent.

Um, if I look at my balance sheet, my balance sheet now balances.

Okay? So it's, again, it's always when these models are really substantial, um, they're always a little bit unsettling because you often have this thing where you really don't have a working model until the very end because you've got so much relying on so much else, okay? But it's important that you can explain and understand what's going on.

If you can do that, then you've got a fighting chance of actually correcting any errors as you go.

It's the, the model you're demonstrating that the model is behaving in the way that you expect it to behave.

Okay, so we're there, we've now got an income statement that works.

We've got a, um, excuse me, we've got a balance sheet that works. We've got a cashflow statement that works.

We've got a debt schedule that works where we're gradually paying down the debts during the course of the, of the LBO.

And again, it's just worth just pausing.

Here's the real key. This is what you need to understand.

Um, in terms of this, um, in terms of this, um, LBO model, you've got the main pieces of debt, um, the 400 and that 511 gradually falling.

Um, and that's really gonna help with the overall level of debt and therefore the EV to equity bridge that drives the valuation, um, of the equity as we exit this LBO.

Um, but then you on the other side, you've got the preference shares and the, um, mezzanine debt gradually going up, um, because they're pick notes.

So those will need to take account of those, um, during, uh, when we work out whether, whether this is worthwhile for the, um, stakeholders.

So off we go, we're gonna go right to the beginning, back to the LBO, and you can see there's a section down here, which is empty.

This is the last thing we're going to do.

Um, we're gonna work out whether or not this is a good LBO, what the returns are to the individual, uh, stakeholders.

So we start here and we're gonna do a standard EV um, multiple, um, calculation.

So the first thing we need to actually pick up is the ebitda.

So we're gonna say equals, let's go and find the income statements.

Here it is. Let's go and find the adjusted ebitda, which is down there, 278.

We're gonna grab that number in the first year.

Just put the sum, just put the formula alongside it.

And then what we're gonna do is multiply that by the same number that we've got for the opening and closing EV to EBITDA multiple.

So we're gonna say equals, and I think it's seven and a half times.

Yep, there it is. Might remember that from when we set up the spreadsheet with Gerard a couple, a few weeks ago, seven and a half times.

Um, and I'm gonna lock that in with an F four multiplied by the e, uh, the EBITDA just above.

And that gives me an enterprise value of the business at the end of year one. And we will copy all this to the right, but for now, what I'm gonna do is just, um, populate this first column.

Um, so what do we do next? Um, we now go around the EV to equity bridge. We've calculated the enterprise value.

So what we now need to do is to add on cash, subtract debt that will give us an e um, an equity value for the business.

So here it goes. Uh, we're gonna go, where is my mouse? I've lost my mouse there. It's um, I'm gonna go and find cash. So this is easy. We're gonna find lots of these. We're just gonna be pointing to the balance sheet.

So we say equals um, control page down all the way to the balance sheet.

Let's go and find that cash balance, which is somewhere up here, end of the first year, 14.4, um, J five.

There it is. And now what we need to do is to do the debts.

Um, now it says, um, total debt excluding mezzanine.

So I'm gonna do an equals.

Um, and I'm done gonna do, uh, minus sum because I know I want these as negative items.

I know there's gonna be a handful of items.

So I'm gonna go to my balance sheet again and I'm gonna add up, uh, the revolver, which I know is zero at the moment.

Um, and also add up the debts.

I think I pointed at the wrong column.

I'll need to adjust that.

Um, so the first Leanne is at j uh, 22 second Leanne.

Um, junior notes, but not including the mezzanine. So I don't want the mezzanine, um, uh, debts. We're gonna deal with that separately.

I hit return and I get a number of minor, my 818, and I did make an error in there. You can see, uh, I'm pointing at J for the main chunks of debts and I pointed at I for the revolver.

So I'm just gonna manually change that to pick up the right column.

There I am. Okay, uh, next, uh, I just need to pick up the mezzanine and the pref separately.

Reason why is we need to deal with these, um, separately in both cases.

Um, they're both represent different stakeholders.

So I'm gonna say equals again, points this at the, uh, balance sheet.

Go and find the mezzanine debts, which is on row 25.

End of, um, the year is column J.

And again, I moved to naked negative.

So multiply by minus one I know with minus 300, minus 36.

Um, just checking there. R all pointing at J. That's good. Yeah. And then the same thing with the press equals, where's the balance sheet points at the press? That's 600 and something number 698 at the end of the first projected year.

Multiply by minus one to make it negative. And there I am.

And now the great thing is because I've done these in positives and negatives and I know which way I'm going, I could just do an alt equals to add all of this lot up.

Um, oh, uh, Abraham basically saying you get circular reference error. There's a really easy way of, uh, fixing that.

Um, basically you need to go to options.

So alt ft, if you hit alt, then f then t you get the Excel options.

And if you go to formula, you need to make sure this thing here enable iterative calculations is turned on.

Um, and that will fingers crossed, that will hopefully get rid of that circular error for you.

Sorry, I I didn't see your, didn't see your notes. It was hidden behind the big spreadsheets. Okay? So alt ft, you go into options, um, you go into formulate and then you turn on enable iterative calculations and that should get rid of that circular error.

Now it doesn't mean there isn't still a circular reference, but what it means is, um, Excel is working through it goes around that circular, um, reference, uh, a hundred times and tries to settle on a particular value.

Okay? Um, that's, I'm guessing that's what it is.

If it's something else, um, uh, probably a little bit difficult to solve that, um, from a distance in the next, uh, 10 minutes or so, but hopefully that, uh, solves it.

Okay? So we end up, we have an equity value at the end of that first, uh, year.

Now, what I'm gonna do, sorry if that was a little bit slow me reacting to that, um, uh, that era a, uh, Abraham, sorry about that.

Um, okay, so I'm gonna go to, um, uh, this, uh, block that I've just calculated and I'm gonna copyright and we get, um, and it is worth just pausing at this point.

Um, I get a really significant rise in the value of the equity investment in the business.

And why is that? It's really a couple of things driving this.

The first thing, and it's quite obvious when you look at it, is that the EBITDA number is rising pretty, pretty significantly.

Not a huge amount, you know, it's going from 280 to nearly 400 over the course of eight years, but it's a nice sort of steady rise in that EBITDA.

Um, and the, but the mode, the, the, the, the, the key really is that we've basically paid off, um, uh, great big chunks of debt.

We've gone from 800 of debts here to, uh, zero of debt over six years.

So we've basically paid off, um, a really sizable chunk of debt.

It is also worth noting, um, however, that the prefs and the mezzanine have gone up, uh, significantly as well. So if I just highlight this, I start with 1.8 billion of prefs mezzanine and the rest of the debt.

And by the time I get to this point here where I've paid off the debt, um, I'm at 1.8.

So we have paid off some of the basic debt, but we haven't necessarily shifted the debt that, uh, that, that, that much.

Okay? Okay.

Uh, so let's go and have a look at, at the returns, uh, to each of the parties.

So first thing is a mezzanine debt.

Um, uh, and we should be able to gradually do all of this just on our, uh, just on this, on the face of this uh, spreadsheet.

So first thing we're gonna do is we're gonna take, uh, remember the mezzanine holders have not just the mezzanine debt, but also, uh, they basically have um, uh, 5% of the business they were given those 5% warrants, um, that they will basically cash in at the point where they exit.

So I'm gonna say equals, um, I'm go and find the 5% that the, uh, mezzanine holders gets 5% up here.

I lock it in with an F four and then I multiply by that equity value just above.

So very small amounts in the early years, but as the equity grows, clearly that number will grow as well.

Uh, what about the mezzanine loan, which we're gonna repay on exits as well.

We point at the 336 up there.

Now basically I'm gonna make this positive because this is what the mezzanine holders are gonna extract in value from the business.

Um, so I'm gonna flip the sign, multiply by minus one and I end up with 336.

Now it's very tempting to just add those two numbers up and get a total, but what I'm gonna do is I'm gonna be a bit cuter than that and I'm gonna say they will only get this money at the point where they exit.

And if we're gonna calculate an IRR to the mezzanine holders, we only want 'em to get one loss of money in at the point where they exit. So I'm gonna do a little if statements here. So I'm gonna say equals if the year, the closing year counter is equal to, and I'm sure again Gerard mentioned this way back at year seven, our current assumption is that we exit in year seven. So I need to lock that with an F four, so I copy it, right? If we, if we're exiting, if it's the exit year, then at that point we basically need to add up the value of both those warrants and the mezzanine debt.

Otherwise it's zero and let's just copy all of that to the right and see if it works.

So yes, it builds up.

You can see the equity value building up, you can see the mezzanine, um, uh, loan building up, but it only appears in cash at this point here in year seven.

And that's my exit year.

I've also then, if I'm gonna do an IRR calculation, I need to compare that to what they put in to the business.

And so that's just simply the value of the opening mezzanine debt at minus 300 up there.

And I am gonna flip it to negative 'cause they put this money in, that was a cost to them.

And then they're getting out to the value of the mezzanine uh, loan with the pick interest increasing and also the value of the 5% warrants.

So I flip the sign, multiply by minus one, and I get minus 300 and I can now do an IRR calculation, which is equal to IRR of that whole lot of cash flows and it gives me 13.4%.

Okay, so there we are. Um, it is again, just worth pausing.

Does this make sense? Well, if you think about it, the um, mezzanine debt was a pick note with 12% of interest, um, uh, 12% of interest cu accumulating.

So if there was no warrants at all, then the IRR would be 12%.

Um, so it's basically saying the warrants are worth the difference between 12% and 13.4.

And again, that makes a little bit of sense. If you look out here at this yellow cell, very, very roughly, we're getting about 10% of the loan value in addition to the loan for, from the, from the warrants. You know, 60 over 666 is about 10%, a little bit less than 10%.

So therefore add on 10% to a 12% IRR 10% is another 1.2 gets you up to 13 point something.

Um, so, uh, that makes sense, that makes sense to me.

So these mezzanine holders should be reasonably happy, guaranteed 12%, um, and they're getting just a little bit more 13.4%.

Now what about the PE institutions, IE the, um, the, um, the PE firm? Uh, what are they getting out of it? Well, they are basically getting, uh, two things.

The first is, uh, that they're getting the preference shares.

That's the bulk of their return, the bulk of their investments.

Again, I'm gonna make that, uh, minus one.

So they're getting that 696 and that will gradually increase and that will also again give them a baseline 12% rate of return.

'cause it's a pick notes. Um, and that's what's the, that's the rate inheritance within that, uh, pick notes.

But they're also getting the equity, and this is gonna be the kicker. This is gonna be the thing that really if the equity grows, which it has done, is really going to give them lots and lots of extra, uh, value.

So how much do they get? We need to go up to our split of ownership.

And so here are the institutions, 85.5% is what they basically get.

Um, I need to lock that in with an F four. They get 85.5% multiplied by the equity value up here of 250.

And that's a fairly sizable amounts.

And then we do exactly the same thing. We have that little if statements at the bottom equals, if so, they only, um, get this amount in the exit year.

So if the current year counter is equal to that exit year of year seven, lock it with an F four.

Then they basically get the preference share value plus the equity.

Otherwise they get zero. Let's just see if this works.

Copy it off to the right.

And I will just put formula all the way down this right hand side just so you can see.

And you can see exactly the same thing happens.

We basically get 2.4 outs, but here you can see that the equity component is a much more significant element, um, than, uh, for the mezzanine holders, they're getting 95, sorry, 85% of the equity.

Um, it's almost doubling their return 1.3 from the, uh, preference, uh, 1.0 from the equity.

So very significant.

So I'm hoping that my IRR looks an awful lot better than the, um, uh, an awful lot better, uh, than, uh, 12 or 13%.

What do they put in? Uh, the majority of what they put in was the 600.

So I'm gonna go minus six hundreds, which was the, um, uh, the press that they put in 622.

But they also put in just a very small element that common equity they put in, uh, 90% of the common equity management put in 10% of the common equity.

So I'm gonna say, uh, minus, um, that common equity of uh, 10, there we are multiplied by, oh not mar times, um, multiplied by the proportion of the, um, equity that the institutions put in that 90%.

Um, and I'm hoping those are both negative. Let's hit yep.

631. I like that number.

So again, we can do a little IRR calculation and we're gonna say equals IRR of all of that little block.

And we end up with a rate of return of 21.1%, much, much more healthy, um, than the basic, uh, 12%, uh, pick notes and that's absolutely driven by that very healthy return on the, um, common equity.

Remember they put only 9 million in and they're getting, what's the number? 1 0 3, 3 a billion out, 9 million turns into a billion.

That's an incredible rate of return on their equity, but of course, incredibly risky as well.

It may not happen. Uh, and in this, in this case, it actually didn't happen, but here it goes.

And then what about the management? Let's look at the management, see what happens to the management.

Well, we'll do the same thing. Um, here.

We'll basically work out what they put in.

So we'll say equals.

Um, so the management put in at 10% of that common equity of 10, which is obviously just a million multiply by minus one.

So the management put in that.

And then let's do another one of those, um, if statements, I'll just put this underneath.

Um, so let's work out. So let's do another one of those if statements. This is actually much more straightforward, so we can just simply say equals if, um, the year counter is equal to the assumed exit year, which is somewhere up here, I've missed it. There it is. Seven. Uh, lock it in with an F four.

Um, then they basically get the equity value multiplied by the size of their stake, which I think is nine and a half percenter exits once there's been a bit of dilution from the mezzanine holders.

Um, otherwise zero. Let's hit return.

And of course we get zero in the, uh, first year, but, uh, need a dollar.

I must have put, I've missed a dollar there, haven't I? J 38 is the equity.

D 26 is the multiplier. So that's the thing I need to lock with an F four, control R.

And there I am. Um, I will just put a formula alongside this equals formula text, just that one underneath it.

And I basically get 114, um, 0.8.

Now, I don't think you need, you don't need to be very clever to work out that, um, um, putting in a million dollars, uh, sorry, million pounds, um, and eight years get, eight years later, sorry, seven years later, getting $114.8 million, uh, pounds is a very, very good rate of return.

But let's actually do the IRR and see, um, what this actually results in.

So my IRR calculation is equal to equals IRR, all this little lot here.

Hit return alt HP 96.9%.

There we are. So we get a 96.9% return, um, for the, uh, managers, which is absolutely life changing.

Um, and that really is the point.

So I'm just gonna, we're just got, um, uh, a minute to go.

Just gonna set, just gonna, um, set, um, just, uh, just pause at this point.

Uh, why this, this is a really good example of how the mix of financing, um, is considered when you're setting up an LBO.

So think about an LBO, it's a financial sponsor.

Uh, the p firm has no expertise in running, uh, department stores.

So they need the buy-in of the senior management. They need the senior management to make this work and to do their bidding and to do exactly what they want.

So what they need to do is the management to be incredibly incentivized to make this work.

So that's why, um, they basically set up this common equity, uh, with the, uh, the management getting a reasonably high stake, 10% of the common equity.

Um, uh, and they can't make it too big and they can't make the common equity number too big because these are just normal managers, you know, earning sort of normal, you know, reasonably high salaries.

But they can't get the managers to put in like 10 million or 20 million, uh, because they simply wouldn't be able to raise those sort of funds.

Um, so we've got a a million, um, uh, pounds being put in here by, you know, let's guess maybe 10 or 20 managers, 50,000 each or a hundred thousand each.

The sort of amount that you should be able to raise as an indi, a reasonably well paid individual.

Maybe you remortgage your house, something like that.

Um, but for that, say 100,000 pound that an individual manager puts in, he's gonna get, if this all comes good, about 10 million pounds at the end of year seven, absolutely life-changing stuff.

Um, so he's gonna be enormously incentivized to make this work.

And then the other side of that, the 90% of the common equity, um, is where the institution gets its real significant return from, but it can't be an enormous amount because that would basically mean that the managers would have to put in too much or the manager's stake would be too small.

Um, so it's a balance between that mix of the managers having a stake enough for to be, for it to be incredibly lucrative.

Um, the proportion of the common equity owned by the institutions being big enough to really drive their return.

And then the preference share, um, be basically being the money, the significant amount of money that they put in, um, that enables them to do the transaction and buy the company in the first place.

And they get a reasonable rate of return, you know, a nice guaranteed hurdle rate of 12%, but certainly not the sort of overall returns that they will be looking for.

So it's a matter of balance amongst those three components.

Um, the size of the preference share, the size of the common stock, and the proportion of the common stock that's split between the institutions and the management.

And it's really that balance, um, that is, um, the real sort of, uh, big decision to make as they're setting up the structure of this, um, of this LBO.

Okay, so that's done. Just gonna mention one other thing.

The solution file to this file is in the downloads, exactly the same place you picks up this file, but there's also another more complex version as well, which is basically your extra debt pieces.

It's got a unitranche loan and it's got a sale and leaseback as well. They are very, very complex spreadsheets, even more complex than this.

And if I was teaching those in class, it would be a whole day to actually get through, um, that, uh, slightly more complex, um, model.

Do have a look through this. If this is your area, if you are interested in LBOs, do have a look through, through those more complex, uh, models.

Um, and basically, um, uh, see, see, see if you can work out how they, how, how, how they work.

Okay. Uh, thanks for your attention just about everyone still here, which is great.

Good news. Um, uh, so, uh, I hope that's useful.

I hope that kind of finishes off what we've done over the last three, uh, weeks, uh, successfully, and you understand where we've got to with this and look forward to seeing you on another one of these, um, uh, very, very soon.

Remember that this is, this is recorded.

So if you wanna go back over this and recap what we've done, um, you'll be able to pick up the recording, uh, by, normally by close, apply by by, um, uh, tomorrow.

Okay. Thanks a lot. Hope that all makes sense.

Um, I look forward to seeing you on another one again. Thanks. Thanks, Abraham. Thank you.