Hey there guys.

Welcome along. Now we're gonna do some work on an LBO model, and it would be advantageous if you had that model open in front of you once you are logged in.

If you could look at, uh, if you could grab that file, which is Dems complex, LBO basic version part, complete empty.

I don't think this version is basic, but uh, my colleague labeled it as such. You can make your own, uh, you can make your own assessment of that when we get through it. It's quite, quite a lot going on, I think, in the model.

Okay? So if you could jump onto the webpage, click on the link to download it, that would be great.

We're gonna build the model together, and I'm gonna take my time tell you what cell references I'm using.

I'm gonna call out the numbers so that if you wish to, you can follow along.

We won't get the entire model built.

We're definitely not gonna to gonna be able to do that in the hour.

But this session, um, is focusing on the, uh, debt schedule specifically.

So we'll do a little bit of work in advance of the debt schedule to get us into that, uh, into that right place. And then we'll, we'll get the, we'll get the debt debt schedule done.

Okay, so the file you land on, uh, should look like this.

So you'll, uh, you'll open up on a welcome page and there are a bunch of other, uh, sheets in the model.

We, we'll, we'll just talk about them briefly.

So there's an LBO sheet.

The LBO sheet basically sets up the model, so it has the sources and uses of funds.

Well, we're gonna need that, right? I mean, you're gonna need to know what the sources of funds are.

You're gonna need to know what debt we're gonna issue in the deal if you're gonna build the debt schedule.

So, um, we'll, we'll take some time on that.

Then we've got, um, an input sheet.

So the input's, the assumptions, there's absolutely never gonna be any work to do there.

So those assumptions are, are already in the model.

Um, and we'd use those to build the model.

We've got a calc sheet, which will have some base analysis on it, and, uh, that's probably not a great value add for this exercise.

It doesn't really connect the debt schedule.

So there's nothing to do on the calc sheet, on the income statement. There's a very small bit of work to do on the income statement.

There's a bit of work to do on the balance sheet because we just, we just cannot build the debt schedule unless we have the balance sheet set up correctly.

And then we definitely have to build the cash flow statement.

So if you're gonna build a debt schedule, how could you figure out how much debt you could pay down if you hadn't built the cashflow statement? So we're gonna spend a little bit of time on some of these sheets, but most of our focus is gonna be on the last two.

Let's have a quick look then. So if we jump into the LBO sheet, this is what we have.

So if I could draw your attention really to the middle of the sheet here.

We've got the, the sources and uses of funds.

Now it looks like, uh, you know, the first thing we'd really need is we need to know from a kind of use point of view, what is it we are buying? How much is it gonna cost us? So what I'm interested in is the acquisition equity value.

Often, I'd call that the, the equity purchase price.

I'm gonna highlight that in yellow. And, um, there's a bit of space above just to calculate that.

So I'm gonna just gonna move, here we go.

Just gonna move, um, up to the right.

And if I can apply the same color, then we'll can, uh, make the associate association between the two.

So what I wanna do is figure out the equity purchase price, the acquisition equity value, and then I wanna feed that into the uses of funds table.

And once you've got that, we're gonna try and figure out the sources of funds.

But let's not get ahead of ourselves. Let's, let's try and figure this out. First of all.

So we're, we're building, um, a, an LBO for a now defunct, UK based, um, retailer.

Very large national retailer called Debenhams. That definitely fell on hard times.

It was, uh, it was trading at a, a depressed price. And at one point in time it made it look like it might be, um, an attractive LBO target, not least because, uh, it was pretty inefficiently run.

So there was, there was potential upside there.

And that's where we find ourselves now.

So, we'll, we're gonna figure out the enterprise value for Debenhams.

And to get to the enterprise value, we need a multiple.

So we, we've got a transaction multiple assumed here, and we need the historic ebitda.

You might notice in the formula bar that this references the income statement.

So we're going to go and look at the income statement and grab the historic EBITDA number.

We're gonna multiply it by the, uh, EBITDA, appropriate EBITDA multiple.

And that will give us, the product of those two will give us our enterprise value.

What I'd like to do is figure out what I'm gonna offer the shareholders to buy the business.

And so we're gonna go over the bridge by adjusting for the net debt.

Just noting, and this will come up later on, that the net debt is 302.4, and it appears to grab from the balance sheet.

I would imagine that's the balance sheet.

Uh, I guess maybe short-term debt revolving credit facility, the balance sheet, long-term debt, and then deducting from that, the cash.

So that would give you the net debt. Okay? We, uh, we're gonna, uh, certainly wanna wipe out that existing capital structure and replace it with something new that suits our purposes.

I'm gonna say, because I'm gonna grab the EV and I'm gonna subtract the net debt to get to the acquisition equity value.

I'm gonna show you my formulas there.

And then once we've got that, we're gonna just move that across into our uses of funds.

Okay? So in terms of use of funds, we are definitely going to want to go and buy the company.

And we're gonna be required to refinance the existing debt.

Existing debt is given to us as 302.4.

When we're buying the business, we are gonna have some fees.

So we are gonna seek the advice, uh, uh, we are gonna seek the help of some advisor, uh, some, uh, investment bank, no doubt on the valuation.

Um, and we might end up issuing some financing as well. We're gonna issue some debt, certainly.

So we'll have some fees.

And the assumption here is here that the fees are 3%.

If you look in J six, it says 3% of EV.

So I'm gonna, I'm not gonna multiply that by the equity value.

I'm gonna multiply that by the enterprise value.

So let's multiply that by the EV.

I've got 53.7, meaning that the total use of funds is 1 8 4, 3 0.2.

What about the source of funds? It has to be the same number.

If we add up the funding, we're gonna put in place, it has to equal 1 8 4, 3 0.2.

Now it looks like we've got some numbers in here already.

So we've got some first lien, second lien and mezzanine debt.

What we might do in a, in a separate exercise, if we had, you know, uh, a, a slightly different focus here is we might go and have a look at the cash flows of the business and we might size that debt.

So we might sculpt that debt around the business' cash flows and arrive at those numbers.

But those numbers have been given to us already, so we're gonna use those.

Um, it looks like there's a, a, a space missing here for preference shares.

And so the funding is not complete. What I'm gonna do is I'm gonna take the total use of funds and I'm gonna subtract the sum of the debt mentioned above and the, uh, equity mentioned below. The common equity mentioned below.

So we're gonna issue 622.2 million of preference shares, and it'll be the sponsor that will issue, uh, likely, uh, be issuing that equity.

So it'll be the sponsor, uh, that will take that position.

Okay? So now we're in a, in a fantastic place, particularly with the, with the sources of funds to start to maybe build a balance sheet and think about the cash flows available and think about the debt schedule, which is our focus.

But just before we leave this sheet, I noticed we've got a goodwill calculation below. So we might as well have a go at that goodwill calculation.

Um, we're gonna go and grab the equity purchase price.

So that's, well, let's just keep that color coded in yellow.

Let's grab yellow for that Suffice sake calls. We've got the acquisition purchase price.

The acquisition equity value is 1 4 8 7, and then we've got the net identifiable assets of the business.

So there's a couple of ways we could do this, but what we want to do is we want to go and grab a look at Debenham's balance sheet as it exists right now and figure out what the net assets are worth, which would be the same as the shareholder's equity.

So I'm gonna say equals, I'm gonna say control and page down.

So just to be clear, I'm going to the balance sheet.

It looks like we've got some projections up the top.

Can you see there that says projections, but actually I wanna go into the most recent historic year.

I'm gonna go and grab the net assets.

So if I get the total assets, so that's 2 1 4 2 0.6, and I'm gonna subtract from that, the total liabilities I've got, uh, uh, total liabilities of 1, 2, 8, 9 0.3.

Now before I do anything, uh, it would typically be appropriate to work line by line through the balance sheet and do a fair value review at the point of acquisition that is required under US gap and under IFRS, you might, if you don't recognize that, that terminology, you might call it mark to market.

So a mark to market review of the balance sheet.

And so we might talk about step ups.

We really wanna have some comfort in that goodwill number.

Like am I, am I happy with the number we've arrived at? And the equity purchase price is kind of irrefutable 'cause that's what you're paying.

But then you might look at the balance sheet and say, well, these numbers are pretty old, you know, they're historic book values.

This is maybe a good opportunity for us to review those and maybe revise them.

So we don't, in, in this example, we don't have any work to do on that, but it's, it's quite possible that we would have some work to do on that.

One of the things that we would always do in that fair value review is we would always, I'm gonna do it, although it's kind of pointless, we would always step down any pre-existing goodwill because the accountants would say that that's not separately identifiable.

Um, there isn't anything there.

So whether we do or don't include that in our formula is pretty much irrelevant, but technically that would be correct.

Let's hit enter on that.

So we've got net identifiable assets of 8 5 3 0.3, and that means we've got goodwill of one minus the other.

I've got goodwill of 6 3, 3 0.8.

Now it's probably worth remembering that number.

It's probably worth remembering that number, um, because it will come up a little bit, uh, a little bit later on in the model.

Okay, I think we can probably leave this sheet alone.

Now we've, we've pretty much got to the end of that sheet.

As I said, if I go onto the input sheet, click on the input sheet.

It's a bunch of assumptions that drive the operating model and we don't need to do any work on that, then that's, that's all already existing in the model and, and complete.

And the same with the calculations.

Under normal circumstances, we might build some base analysis for pp and e and some base analysis for equity and, um, an operating working capital calculation.

But we don't need to do that here. It's been done for us.

It is probably not very interesting, um, and not a huge value add anyway, because our focus is on the debt schedule.

I'm gonna go to the income statement. Now. For the most part, the income statement is complete.

So you can see we've got the projected income statement, which we're gonna need in the, uh, we're gonna need to calculate the internal rate of return, uh, if we got to the end of the model.

So it's almost all done except that if I, uh, grab a look here, I'm just gonna highlight these cells in yellow, there is something that is missing.

I'm gonna zoom in a little bit.

Let's zoom in a little bit over here and show some formulas.

So one of only one, but one of the reasons, um, for us entering into a, um, a leveraged buyout, uh, acquisition is to generate returns through operational improvements.

So the sponsor would need to believe that the business is in some way inefficient and it could be rationalized.

So that's what we're looking at here.

So we're saying, okay, we think we can reduce costs and uplift ebitda.

Now we never really know exactly, you know, how this is gonna work out, but you typically, the management team of the target company would be incentivized to come along for the ride and they could be very helpful in getting us in, helping us to get up the curve pretty quickly on this.

So anyway, we need to figure out what these EBIT dime improvements are.

I'm gonna say course, um, in cell day 16.

And I think, I don't wanna be too disorientated too much, but I think you'll find that if you go to the LBO sheet here, um, I'm pretty sure somewhere here we've got, and it's there.

So if you look on the LBO sheet in F 11, uh, it says 2%.

And if you look at the heading, it says EBIT D improvement as a percentage of sales.

So given that hos and, and particularly sg and a is typically calculator or forecast as a percentage of sales to think about savings in probably their sg a savings as a percentage of sales seems like a, a kind of reasonable way to approach this.

So I'm gonna grab that number and I think I'm gonna lock it by pressing F four.

Pressing F four. And I'm gonna multiply that by, and now I'm gonna go back to the income statement, the appropriate sales number at the top there.

Okay, so I'm not gonna lock that 'cause we'll copy that out to the right in a minute.

So I've got LBO exclamation mark, dollar F dollar 11 multiplied by is exclamation mark J five.

And that gives us our EBIT D improvements. They're pretty big, it's quite a big uplift in ebitda and we're gonna copy that out to the right.

And just for completeness, I'll show my formula there.

Now, I think that this adjusted EBITDA number, I'm gonna put that in bold if I'm gonna color that in as well.

Let's make that I don't know, purple.

That is definitely going to be a useful number to us further forward when we're building out the rest of the model, particularly when we're thinking about the debt schedule, we're gonna think about how much EBITDA is the business generating and therefore how able is it to aggressively pay down the debt going forward.

Okay, so we'll come back to that.

I'm gonna go to the balance sheet. Now.

For the most part, the balance sheet looks to be built.

So we've got a lot of projected numbers here in the balance sheet.

Um, but uh, there's a couple of distinct areas that are missing.

The first one is that, um, when we go forward and do the debt schedule, we need to do the, so we do the debt schedule, we need to do the cash flow statement.

And the cash flow statement is gonna think about how it works.

Mechanically, it's gonna require us to compare the, um, forecast, balance sheet numbers to the prior year.

'cause the movement, the change gives you the cashflow statement.

So we need these prior year numbers here and unfortunately they're not complete.

That's okay. We're gonna complete them ourselves.

So lemme just grab a color just so you can see what I'm doing.

Um, and here as well.

So we're gonna go and fill in this combo column.

I might, you know, I might call this like the opening balance sheet or the beginning balance sheet.

And you could say, well, why, why bother? I mean we've, we've got the balance sheet already in 2015 here.

The first year of projection is 2016. Okay? But look carefully at the historic balance sheet.

Let me get my stylus here. Look carefully at what we've got.

The historic balance sheet has got revolver of 1 5, 5 0.4 and it's got debt of 1 97 0.1 and it's got cash of 50.1.

If I just find a bit of blank space somewhere, it doesn't really matter where, it's just a bit of space over here.

If I grab the revolver plus the long-term debt minus the cash, I get to 302.4.

And I know then I've seen that number before.

I've definitely, I've, I know, I know that was in the model somewhere.

If we go back to the LBO sheet and we look at our use of funds, it looks like we are ref, there's the same number, 302.4, we are refinancing that debt.

So back to the balance sheet, it can't be the case that this debt, you know, it can't be the case that the this debt is gonna exist going forward and the structure, we're gonna get rid of it, right? Obviously it doesn't mean we're gonna be debt free 'cause they're gonna replace that with a load of other debt.

So before we kind of build a debt schedule, we need to take a breather and say, where am I? Like where am I at right now? Uh, what is my, what is my, my balance sheet look like before I figure out how aggressively we can start to dele.

So, um, that's what we're gonna do now in the combo sheet.

This is in the combo column. This is not complicated.

I'm gonna say alt and equals.

And actually what I wanna do is I want to, I do want to grab the historic balance sheet in 2015 and then I wanna suck up some adjustments.

And so, um, certainly one of these adjustments is gonna be to get rid of the cash.

I'm gonna back out the cash and I'm gonna back out the revolver.

I'm gonna back out the debt.

And then we're going to insert a load of new structure from the sources of funds, which is the whole reason we really did that.

So this is not, uh, uh, this is not complex.

All I'm doing is I'm just gonna sum across.

So if I sum across, I'm gonna copy down to the total current assets.

I think I might put that in bold just so it pops off the page for you guys.

Let's al equals equals that.

And then I'm gonna do the same for the pp and e. I'm gonna say alt and equals.

I'm gonna grab the 2015 year and the adjustments that we, we haven't yet processed those, but we'll have some adjustments and I think I'm gonna copy that down.

And then just to put that in bold, it pops off the page.

I'm gonna get total assets.

So total assets, definitely the total current assets, comma, and then all of the non-current assets as well.

Comma, to separate the sum function, let's say using a European keyboard, then it'll be semicolon.

Okay? And I'll do the same with the revolver and, uh, the, the accounts payable alt equals and grab 2015 and these adjustment columns, copy that down, make the next row bold and all equals equals.

And I'll do it with the liabilities.

I think you get the idea, you know what I'm doing here.

So we're just gonna copy this all the way down.

Looks like we've got a bit of blank space here because we're gonna insert, we're gonna ultimately insert the new debt balances in here so we can get that debt schedule going.

Um, I'm now down to total liabilities.

So the total liabilities must be the sum of the total current liabilities and all the non-current liabilities that exist there.

And then we've got equity.

And so, uh, I'm gonna do the same thing.

I'm just gonna go and grab the historic equity and uh, some across. And you might, so I thought the historic equity was gonna, I thought we were gonna buy the company, we're gonna get rid of the historic equity.

We are, we are okay, but let's not get ahead of ourselves. We'll process that in a minute.

And then, uh, finally what I'm gonna do is I'm gonna grab the total liability and equity.

So I'd say that's the total liability comma equity, okay? I tend not to sum across blank space because then someone might put a number in there and it would start to screw up my model.

Uh, does it balance? I really, really hope it does and it does.

Okay? So that, that balances at the moment.

Now what we're gonna do is we're gonna go through and uh, line by line and process some adjustments.

Now we've got accounting and we've got financing.

Probably if I was creating this and I didn't write the model, but if I was creating a, I might have created a few more columns just so we could really atomize out what we're doing.

But we're gonna bunch these into a couple of columns.

Um, so I hope you can still see what we're doing from an accounting point of view.

When you buy a company, you always zap out the target equity.

So we have bought their equity that is gone, okay? And we are gonna issue new equity to replace that.

So we're always gonna grab that.

I'm gonna multiply it by minus one.

So we're always gonna back that out.

Oh no, look, control Z, this thing balanced control y.

Now I've done that adjustment, it doesn't balance anymore.

That is problematic.

I'm gonna make that red 'cause I'm a bit worried about that.

So I think what we're gonna need to do is go through all of the adjustments and get this fixed.

Um, so let's keep, let's all keep an eye on that.

Now what we should, uh, what we should also do when we're um, when we're doing these adjustments, um, is think about goodwill.

So I'm gonna go into still the accounting column and the goodwill, um, uh, uh, Goodwill Row and I'm gonna say equals and was there any goodwill for this deal? Yeah, there was. If we go to the LBO sheet, we work that out down the bottom.

It was in LBO exclamation mark H 26, the number is 633.8 million and that is the goodwill we're creating on the deal.

So we're gonna bring that in.

Okay, so I think that probably looks good.

Anything else that we um, potentially need to think about? Well there is something that I would do from an accounting point of view and it's in my mind, but I'm not gonna do it because I've kind of, I want to, I want to uh, I wanna sort of get to the end of this. We'll get to the end of this little exercise here and then realize it doesn't balance and then just get, go sort of figure it out.

So I'm gonna move forward, but there's something, something in my head that I know we need to address now. We're gonna go to the financing column and uh, hopefully everyone is good with the idea that the targets revolver is gonna be refinanced and that the targets long-term debt is gonna be refinanced.

And rather than US issue, rather than us issue debt to repay their revolver, we would use the cash that exists on their balance sheet first and foremost.

So the cash is also gonna disappear as well, which is why we are refinancing the net debt.

Lemme just have a look 'cause does it balance this? This is a long way, a long way from balancing unfortunately at the moment.

So what do we need to do? Well if I go back to the LBO sheet, you might remember that we've got all of this financing here that we've raised and that needs to be reflected in the adjust in the adjustments.

In fact, it's the whole reason I'm really doing the adjustments 'cause it feeds most of these feed into the debt schedule.

Maybe not the press or the common equity of course.

So what I'm gonna do is I'm gonna go to the back to the balance sheet.

I'm just gonna work through those line items.

So the first lien debt, if I say equals, I'm gonna go back to the LBO sheet and grab that first lien debt.

The 400 I notice it says second lien junior notes, mezzanine, prefs below that.

So I think I can copy that all the way down to the prefs.

Yeah, we're gonna copy that all the way down and down there.

Okay, um, what else? Because it's still out.

Yeah, that doesn't pick up all of the financing.

Now if I go backwards to the revolver, it's not gonna make a difference.

But to be technically correct, if I go to that adjustment and I say plus technically if we go to the LBO sheet, we should pick up the revolver issuance and I know it's zero, but someone might come into the model and change some of the financing assumptions and we'd need that to flow through correctly.

So we'll grab that. It makes no difference at all.

And the other thing we need to do is go to the equity.

So if you go to equity and say equals back to the LBO sheet, we are issuing 10 of equity.

Now I'm hoping that at this point it should balance, but remember I said there was something that I had ignored that I needed to think about.

So if I hit enter and we look at this, it, it doesn't balance but it's out by 53.7 and I know I've seen that number somewhere 53.7 somewhere in the model.

If we go back to the LBO sheet and we look at the uses of funds, the fees are 53.7.

So there are different sorts of fees.

I mean broadly there are about three flavors of fee that we might talk about.

So we might have um, some advisory fees and if you have advisory fees, they get expensed in the year in which they're incurred you.

Um, you might have some equity issuance fees and if you have equity issuance fees, then they get charged directly to share premium.

So don't go into the income statement, they're not in retained earnings, but they do reduce equity or you might have debt issuance fees and technically they get capitalized and then they get amortized over the life of the loan.

So they will affect equity but they just won't affect it straight away. They'll affect it in a staggered way as they get amortized out.

Um, I don't necessarily think that accounting for that, um, would add a great deal to our IRR analysis.

I don't think we're gonna say, oh actually this deal does look good.

Now we've accounted for the amortization of the debt correctly.

This is very much on the periphery, so I'm not very interested in that.

I think what what I'm saying is that advisory fees go reduce equity 'cause they go into retained earnings.

Equity issuance fees reduce equity because they uh, get charged a share premium and um, debt issuance fees reduce equity 'cause ultimately they get amortized, um, through the income statement.

So whichever way you look at this, the fees reduce equity.

What I'm going to do is I'm gonna go back to the balance sheet.

I'm gonna go to the um, maybe the accounting line here.

We've got this zapping out the targets equity.

I'm also gonna say minus and I'm gonna go and grab from the LBO sheet those fees because they reduce the equity number and if we do that this balances, we probably don't need that to be read anymore because it now works.

So I'd say that that was pretty important to get that right.

How would you build a debt schedule if you didn't have the beginning balance sheet in place? Okay, we are missing some stuff though.

I'm gonna highlight it in yellow so that I remember.

We haven't got any cash kind of annoying.

Uh, we haven't got any revolver. I know. I think that's fine 'cause we have to do the debt schedule anyway.

Uh, we haven't got any debt here.

This debt's just a legacy debt. So I'm just gonna make that equal to the previous year and just that that's gonna be zero.

So that is the, just a placeholder for the old debt they had that's now been refinanced and will be replaced.

But we haven't got any of these debt or pref numbers.

Okay? So, um, we're gonna need to populate that.

Uh, and we're gonna need to build a debt schedule.

And I really want to do the debt schedule now 'cause it's really the focus of what we're doing.

But I can't quite do that because we need to know how much cash we've got.

So we're now gonna go onto the cash flow statement.

It is blank, it's not gonna take us a huge amount of time to build it.

I'm gonna just show you my formulas in here so you can see what I'm doing.

And just gonna put in bold the main headings just so you've not got a complete sea of numbers and you can broadly see what I'm doing.

Okay? So just, it'll just organize it a little bit better, right? Let's zoom in on this a little bit.

Um, typically a cashflow statement starts with net income and it doesn't have to, but that's often how a, um, uh, the accountants will present it. If you look at a real set of financial accounts, and I don't have a problem with that at all.

The only thing to be aware of is that we have, um, some debt which is payment in kind.

And you might say, well what do you mean? Well, if we go back to the LBO sheet and on the LBO sheet here, if you look at the source of funds, we've got revolver.

Um, and if, if we had a revolver, we'd be paying interest on that in cash.

We've got first lien debt and second lien debt and we've got junior notes and they'd be paying interest in cash.

So the, the interest would get paid every period in cash. Cash would go down. We've got mezzanine and prefs.

Now the mezzanine finance has got pick.

In fact, both of these have got pick interest and dividends respectively.

So, um, the, the mezzanine finance has got pick interest and what we're basically saying is, uh, we, we are layering on more and more debt onto the business and we wanna borrow as much as we can, but we can't really, once we kind of break the second lien layer, we can't really afford to service the interest in cash on any more debt, but we probably have got more borrowing capacity, but we'll just struggle to service it.

And so the mezzanine lender says, don't worry about it.

And this will be a hedge fund, um, probably a hedge fund.

Um, it says don't, don't worry about it.

Um, you know, like you could, you could pay us interest.

I said, I can't pay you interest. And they'd say, no, no, you can pay us interest, but you can just pay it at the end of the loan.

So the interest could roll up into the loan, it could accrue, accrue into the loan if you could pay us in kind at the end of the, at the end of the end of the term.

And so that's pick interest.

Now that still gets expensed, right? Like if you think about, let's just go to the side and we think about just some really basic accounting here.

Asset equals liability plus equity here.

So under normal circumstances, if you have cash interest in any of these loans here, the non pick loans, then you'd have cash down by whatever the interest amount is and you'd have retained earnings down and that would be an interest expense.

So it would look something like, yeah, something like this.

Um, but when we have pick interest, when we've got that mezzanine loan, it's a very similar setup.

But for the pick loan, the cash cannot go down because we're not actually paying interest in cash, it's rolling up into the loan.

So I guess what I'm gonna do is I'm just gonna delete, I'm just gonna delete cash down.

Retained earnings is still gonna go down and we're still gonna expense the, uh, interest.

So it's gonna still gonna find its way into the income statement as an expense, but we're not having cash going down.

We're having mezzanine debt going up.

So mezzanine debt up. Um, and so it rolls up into the loan.

Now that mean, what does that mean? That means that in the income statement, if you look at retained earnings down interest expense in the income statement, you're gonna have an expense, but it's not gonna be a cash outflow.

So I see from a design choice to possible solutions to this problem, either you start your, um, cash flow statement with net income and then you add back DNA and you add back the non-cash interest expense on the mezzanine loan.

You could do that alternatively. And what my colleague Alistair's done who built the model, he's gonna start with EBITDA and then he is gonna recognize the, uh, taxes and the cash interest, the cash interest and the taxes.

So you could hit that for me, the direction to give you the same result.

So he started with EBITDA, I think that's fine.

Um, he, he has said adjusted EBITDA because going forward, how able are we to pay the debt down? Let's start that discussion with EBITDA.

Or don't forget, we're gonna make some cost savings here. So EBITDA is gonna jump up a little bit, right? So if we go back to the income statement and in purple, I said we'd use it later, we've got a number for adjusted EBITDA.

So we're gonna go and grab that number.

Now we've got cash interest. I, I can't do that.

I just, uh, I I can't fill that in yet because we, I don't know what the interest is 'cause you haven't in the debt schedule, we've got tax expense.

I could do that. I can go to the income statement and I can go and grab on the income statement, the provision for income taxes, which is in J 22.

So I can definitely go and get, definitely go and get that.

Um, if we go down and we, we now just want some pretty basic cash flow statement stuff. We wanna get the movement in long-term assets and liabilities know wc.

So I do this fairly quickly.

If I see calls back to the balance sheet, I need to know from the balance sheet what the, uh, other long-term assets were in the previous year minus my foot, my projected year.

So I 13 minus J 13 and my colleague, when he built the model, uh, he's modeled in no change in these numbers.

So there's nothing happening there.

We're gonna do exactly the same for the other long-term liabilities.

So I'm gonna go back to the balance sheet and I'm gonna go and find the other long-term liabilities which are down here in row 27 this time 'cause it's a liability.

I'm gonna grab the projected year and subtract the prior year.

So it's gonna be J 27 minus I 27, but there's no movement so it kind of doesn't really matter.

And then I want the change in operating working capital.

So if I say, because we don't have to do a working for this, if I go, if I go to the calc sheet, this working's been done for us already.

Can you see down the bottom here it says operating working capital on the calc sheet row 23, you've got the o WC line.

I'm always gonna grab the prior year minus the projected year. It doesn't matter if it's an asset or a liability, it doesn't matter if it's going up or down.

You're always gonna grab the prior year minus the projected year for o wc.

Okay? And I've got 1.4 if I ultimate equals.

Now I wanna make sure I get all of this. So if I add that up, I'm saying that the operating business is generating 235.9 of, uh, cash flow.

Now what I haven't included in there is the interest yet. And when the interest flows through, that number's gonna come down quite a lot.

Yeah, I mean it's, it's quite a, it's gonna be quite big.

The interest amount, let's go down. It won't take long just to uh, put these, uh, numbers together.

So I want the CapEx, I'm gonna say equals gonna go to the calc sheet and I'm gonna go and grab from the calc sheet, the CapEx number that's in J seven.

I'm gonna multiply that by minus one.

Um, that's all we need.

And then I'm gonna go to the, um, financing section.

And this is kind of awkward because I, I want to, I, I wanna get this done, it's convenient to do this now, but I'm gonna pick up the movement in revolver and the movement in first lien, second lien, et cetera.

We are gonna have, from the cashflow statements perspective, some absolutely ginormous movements here, some huge cash outflows.

It, the, the cash flow statement is gonna think that we've paid all of this stuff off in full.

That's not right, okay? Uh, but it just so happens we haven't completed the balance sheet yet. You'll see what I mean. So if we go to the revolver, so if I say equals back to the balance sheet and I'm gonna take the projector revolver minus the historic revolver.

Now there's nothing, it's not gonna give us a weird number because it was zero in the first place, but this hasn't been completed yet. So Excel will assume all these yellow numbers as zero.

If we look at the long term deck, we honestly don't need this in here, I don't think.

But just for completeness, I'm gonna grab the uh, J 21 minus um, I 21.

Um, it, that's the legacy debt number that got refinanced.

So there's not much to say on that, but these numbers are gonna be weird.

If we go to first lien debt and we go to the balance sheet, we go and get J 22 minus I 22, we've got a massive cash outflow.

So the cashflow statement thinks that we've borrowed 400 million and immediately in the first year it's going to, it's gonna be paying that off.

We're not gonna do that, it's just we haven't completed the model yet.

What I can do is copy this all the way down so I can copy it down to second lien and junior notes. Well hang on a minute. I think we're probably missing something here.

If we go back to the balance sheet, we've dealt with the, uh, let me grab my stylus here.

So there we go. So we've dealt with the first lien.

We've dealt with the second lien.

We've dealt with the notes, but on the cashflow statement, we don't have the mezzanine in the press.

And the reason for that is that there would not be within the LBO timeframe with on, on the inside of that timeframe.

There is no cash flow for these either of these.

If you think about the mezzanine, the mezzanine we will assume will be paid off when we exit the deal.

So the mezzanine, mezzanine means in between, right? If you think about real estate, think, just think, think back to a time when you were in a, maybe, maybe this morning, I don't know, but you're in a really nice office building, really nice like lobby reception area and often a really fancy office might have a huge balcony.

Now that balcony is called the mezzanine floor in real estate and in finance.

So it's a mezzanine floor because it's in between the, the ground floor and the first floor or if in the us the first floor and the second floor, um, depending on how you describe it, but it, the mezzanine is is in between.

And in finance it's the same.

The mezzanine financing is uh, financial liability.

It's debt, it is debt, but it's also got warrants attached to it.

So the lender will uh, benefit from um, receiving equity when, when we exit, when we sell the business at the end of the LBO.

And so, uh, then we're not gonna redeem that before the end of the LBO in, uh, in reality.

So there'll be no repayment of that.

And you could say, but what about the interest? Okay, well we're not gonna pay the interest 'cause it's pick the mezzanine loan's gonna go up because of the interest, but that's not a cash flow. It's not, you know, it's not a cash inflow, um, that happening.

So there should be no cash flow implications from the um, the mezzanine financing. And it's the same for the preference shares.

The sponsor will own those preference shares.

They won't receive a dividend dividend on them.

The dividend is payment in kind, they'll get it at the end when they exit.

There's no cash flows there.

So because of that, we've dropped that out of the cash flow statement.

It is actually gonna screw us up a bit 'cause it's gonna mean the model won't balance.

And we'll just have to think about that quite carefully, okay, when we get there.

So I'm gonna add these up.

So that's my cash flow from financing. It looks horrifically large, a huge negative number, but it's only 'cause it isn't yet complete.

Uh, I'm gonna go and grab the beginning cash and net cash flow being the sum of the operating, investing and financing cash flows.

And if I say oh and equal, then I get 7 5, 5 0.1.

Now it doesn't matter if this is positive or negative from a, from a mechanical point of view, but that number, because it's cash should really be positive and it will be once we've kind of worked through, worked through our debt schedule.

But it doesn't matter if it's positive or negative.

Ideally I would like to go to the balance sheet.

I'd like to go to the cash line in the balance sheet, say equals I would like to go and grab that cash flow statement number and that should make the balance sheet balance.

However it doesn't.

So the balance sheet does not at the moment balance.

And that is because we've got this issue with the mezzanine and we've got the pick interest and we've got the, the, the preference shares and we've got the, uh, the, the dividend on those preference shares.

So it's okay, I'm, I'm, I'm not worried about that at all.

This is seems very reasonable that it, that it doesn't yet balance.

We'll get there. Okay, how are we doing? We've got about 15 minutes left and finally we've done the cash flow statement. We can get onto the debt schedule.

So we'll have, we'll now have a look through the debt schedule.

I'm gonna just go into column K, I'm gonna show some formulas here just so you can see what I'm doing.

Okay? And I'm gonna zoom in a bit so it's a bit easier to keep track of what I'm up to.

Okay, what do we have? Well, uh, I wanna figure out how much debt we can pay down.

And so for that I'm gonna need to do a little cash calculation.

The good news is that we've just done the cash flow statement.

So this is really easy. First thing is, first thing is I wanna figure out what the beginning cash is. So go to the cash flow statement or the balance sheet doesn't matter and go and get the beginning cash, which unfortunately happens to be zero.

Then I'm gonna go and get the operating cash flow back to the cash flow statement to get the operating cash flow.

So I'm getting excited saying well great, let's use the operating cash flow to pay down the debt but that we, you know, we are borrow money over a period of time and the lender expects to be paid back over a period of time.

We can't use our operating cash flow in its entirety to service the debt today because then we wouldn't be able to undertake CapEx, we wouldn't be able to perpetuate the business.

That should be bad for the lender for us to do that.

So we're gonna think about our investing cashflow. Next, let's take account of that.

So back to our cashflow statement and grab our investing cashflow of minus 80.

Now what this does is it gives us the cash flow for debt repayment. Maybe I'm gonna zoom out just slightly so you can maybe just see, see that, well we can just about read that.

We can move that out a bit I suppose.

Okay, there we go. So cashflow available for debt, uh, for debt repayment of 1 5 5 0.9.

Now we've got some mandatory repayments.

We've got mandatory repayments of first lien, second lien and junior notes.

And it mentions those above. Yep.

So 12% of the original amount you borrowed in the source of funds, 12% of the first lien is gonna get paid off in the first year. It's an amortizing loan.

We don't have any mandatory payments for the second lien or the junior notes.

Um, they're, they're bullet.

The other ones are, you know, is is a note, but I'm not really ready to do this calculation yet.

So I'm gonna color these in red.

And this is just saying Jonathan, you have to do these, you have to sort this out but I'm just not ready to do that yet.

Let's calculate the um, cashflow for the revolver.

So if I say alt equals, I'm gonna go and grab the cash available for debt repayment comma, and I'm gonna grab these mandatory repayments.

I know there's nothing in there yet, but there will be shortly.

Let's hit enter on that. I've still got 155.9.

Okay, let's go down now and do our revolver calculation in our debt schedule.

So this is really the main event.

The next few sections, um, the beginning revolver is zero.

That came from our opening balance sheet, which ultimately came from our sources of funds.

So these are numbers that we've originated ourselves.

Are we gonna be able to pay any of it down? Well it's kind of a silly question because we actually have, don't have anything there, but nevertheless we'd use a min function. So I'm gonna say it course choose the minimum of the cash available and the beginning balance close bracket multiplied by minus one.

Uh, if we did have a beginning revolver of say a hundred, then it would pay down a hundred.

If we had a beginning revolver of 200, then it wouldn't pay down 200.

It wouldn't do that because we don't have enough cash available.

It would only pay down 155.9 and that's the beauty of the min function.

Let's just put that back. Okay, I'm gonna add that up and make that bold and I'm gonna calculate next the interest expense.

If I say, cause I think I noticed the, in the interest rates earlier in the sources of funds on the LBO sheet.

So if we go back to the LBO sheet over here and we go and have a look at the sources of funds, which is here, if you look in cell LBO exclamation mark K 14, you've got a rate and the rate is 5.7% on the revolver.

I am going to hit F four to lock that 'cause we'd copy this out to the right and I always wanna look at that number and I'm gonna multiply it by the average A VER tab to complete the function.

And then I'm gonna go back.

So control and page down a few times to go back to the debt sheet and I'm gonna go and grab the average revolver balance.

And I know this isn't very exciting 'cause there's nothing there, but it's, you know, technically this is the right way for us to calculate this.

Now where I'm at is I've got the cashflow for the, firstly an accelerated repayment.

So if I say alt equals, I'm gonna go and grab the cashflow for the revolver comma and I'm gonna go and grab the um, I'm gonna go and grab the essence and repayment of that revolver, which is zero.

So I've still got 155.9.

Now we're gonna look at the first lien debt.

Okay, so we've got the first lien debt number here.

We've got the first lien debt number here already.

Can you see that? If you look at the formula bar, that's come from our opening balance sheet that I highlighted in yellow in a kind of light yellow, which itself fed off of some adjustments we made, which came from the work we did on the source of funds.

So there's a direct line of work that traces through to this.

I'm gonna make that my beginning balance, I'm gonna move that up diagonally.

Now we've got a mandatory repayment.

So the mandatory repayment is 12% of what we originally borrowed.

So if I said equals now, well okay if I said equals and I'd go and grab that 12% and I'm gonna multiply that by the amount we originally borrowed, which is 400.

I'm just gonna lock onto that 'cause if we copy it out to the right, I always wanted to look at that 400.

So I'm gonna press F four and multiple that by minus one.

There's nothing majorly wrong with that except that if we'd already paid some of this down and the balance was only 40 million, we wouldn't be making a mandatory payment of 49.

We'd only be paying the outstanding 40.

So we just need to tweak this slightly.

What I'm going to do is rather than just take that calculation, I'm gonna say Excel, take the minimum open bracket of that calculation comma and the beginning balance closed bracket multiplied by minus one.

So that just means if the beginning balance was lower, say it was 40 as we had had a moment ago, it would repay the 40 if it was due to make a a, a mandatory repayment in excess of the beginning balance.

Uh, otherwise it'll make that mandatory repayment if it exists.

We now need to make the accelerated repayment. We're gonna do the same thing we're gonna say cause min choose the minimum of the cash available for the accelerated repayment comm up and the beginning balance.

We, we know like if, if we had say 400 million of cash available, I wouldn't be paying 400 million of this loan down because we are making a mandatory repayment in advance of that.

So what I need to do is I need to take account of that mandatory repayment as a kind of running total.

The amount of outstanding debt before we make the accelerated repayment is 400 minus 49.6.

And I'm gonna add this up now what I need to do, and I'm gonna zoom out a little bit here, is I um, have, I have calculated the mandatory repayment on this debt and so now I'm ready to kind of feed that up.

Now just look at the numbers carefully.

If I go into sell J 17 and I say equals, I can go and grab that mandatory repayment. Sorry, before I do that, I dunno why I didn't multiply that by minus one.

Uh, I can go and grab that mandatory repayment of 49.6.

Look, look what's gonna happen as soon as we wire that in, it's gonna reduce the cashflow available for the revolver, which if we were paying revolver off would mean we could, we would be paying less of the revolver off. But that just would flow through, um, because there's no revolve being paid.

So it's gonna reduce the cash available for the accelerated repayment here of this debt.

And so that 1 5 5 0.9 accelerated repayment is about to reduce to 106.3.

Um, let's keep going then I'm gonna zoom back in so it's easier to see what I'm doing now.

So running total, I've now got the cash available for the second lien acceleration.

So I'm gonna say alt and equals gonna go and grab the cash available, uh, for the first lien and I'm gonna deduct the accelerated repayment.

Well wait a minute, why didn't you deduct the mandatory repayment as well? Don't wanna double count that already have mandatory repayment is deducted further up.

What what about the interest? You know, oh we haven't calculated it but we'll calculate it in a second. What, what about the interest? No, no, because the interest is gonna flow into the cash interest, um, line, uh, on the cashflow calculation that we've done above. So that will also be already counted.

So we'll be fine with that. We'll do the interest expense.

Now I can do the interest really quickly because can you see that, um, for the revolver, the interest sits in LBO exclamation mark dollar K dollar 14, that's for the revolver on the sources of funds.

The next row down was the first lien debt.

So I could say equals LBO exclamation mark dollar K dollar 15, multiplied by a VER tab and grab these numbers.

Very small tip, if you are typing in a reference, uh, always do it in lowercase because if Excel recognizes it, it will automatically capitalize it.

So that's really useful when using named ranges.

So if you name ranges or name cells in Excel, make sure you capitalize at least one letter when you name it and then when you are referring to it, always do it in lowercase because then it will, if it will capitalize it when it recognizes it. So you can kind of see that you've got the name correct.

Um, just uh, something I always do and that's important for VBA as well as much as Excel.

Okay, anyway, I digress. We've got to 18.4, right? Um, so we haven't got any cash left unfortunately, but, but that's all right. We can still do some work on the second lien.

And this is gonna be basically completely repetitive.

So I'm gonna say calls, I'm gonna go and grab the beginning balance.

The mandatory repayment is gonna be the minimum of the mandatory repayment assumption of 0% multiplied by the beginning ba uh, the original balance, I'm gonna lock that with therefore, and I'm gonna compare that to the beginning balance bracket.

MULTIPLI minus one, there is no payment there.

The accelerated repayment is gonna be, the lower of is gonna be equal to minimum the lower of the cash available for the accelerated repayment and the beginning balance netting off any mandatory payment if that should be made multiplied, but minus one and there's nothing going on there.

So I'm doing it fairly quickly.

The interest, again, if you look at the LBO reference up here, which is K 15, for the second lien debt, it would be equal to LBO, exclamation mark, dollar, K, dollar, I think 60, multiplied by the average A VER tab and the average of these.

So we can do that fairly quickly.

And now we've got the, um, junior notes, so there's no acceleration on the junior notes.

So if you think about the first lien debt and the second lien debt, we've borrowed money from an institution.

So we can go back to that institution and say, I would like to accelerate the repayment of that debt.

Please for the notes.

We've sold those to many, many investors.

It's not quite the same way in which the relationship would be managed.

So it's difficult for us to make these accelerated repayments.

I've assumed. Well, Alistair, when you built the model, let's assume we're not gonna do that.

So we've only got mandatory repayment, which is equal to the minimum of the mandatory repayment assumption, which is zero multiplied by the beginning balance.

I'm gonna lock that using F four and the beginning balance for this year.

Closed bracket multiplied by minus one.

Now what we've done is we've calculated for both the junior notes and the second lien, I'm just gonna grab a stylus here.

We've calculated the mandatory repayment and so we should bring these mandatory repayments up to this working above.

I'm quite zoomed out but I'm, I'm just going to grab that 'cause it's just convenient to be able to see everything on the screen.

So I'm gonna grab the mandatory payment of zero for the second lien and the mandatory payment of zero for the notes.

And I'm just gonna all hh end that so that's no longer red. We're gonna make that white now.

Okay, we've just got, all I really wanted to do is just this last bit and we've got about three minutes left.

So I reckon we'll squeeze this in.

I just wanna get the mezzanine done 'cause that's the kind of like the interesting, uh, uh, f in interesting sort of final piece of the puzzle for the debt.

So I'm gonna calculate the interest on the junior notes.

I'm gonna say it equals L-B-O-L-B-O exclamation mark dollar K dollar 17 it looks like to me.

Uh, and I'm gonna multiply that by the average balance there.

Uh, and uh, now I'm gonna go and do the mezzanine. So the beginning mezzanine is 300.

The, uh, the interest increases the loan.

So if we go back to the LBO sheet, I did this calculation and I tried to show you that the interest will send the loan value up 'cause it gets rolled up into the loan.

So that means that we need to reflect that in our model.

So I'm gonna say equals I think the interest is probably in lb LBO exclamation mark K dollar 18 I think.

And I'm gonna multiply that by the beginning balance and that gives me 36.

Okay, so it's gonna be 3, 3 6.

Alright, so, um, what we would now do is, and I've got two minutes to do this, is we go back to the balance sheet and we'd go and feed these numbers in.

So if I go to the balance sheet, to the debt part of the balance sheet, we can now complete the final piece of our debt schedule, which is our goal for the session.

So if I go to the revolver and the balance sheet back to the debt sheet and go and grab the ending, uh, the uh, ending revolver of zero.

If I go to the first lien, I'm gonna say it calls back to the debt sheet and I'm gonna go and grab the ending first lien of 2 4 4 0.1, the second lien.

I'm gonna go and back to the debt sheet and grab the second lien of 511.

The junior notes, well, there's nothing there is there.

I'm gonna go and grab that, that was zero.

And the mezzanine, I'm gonna go and grab the debt sheet for the mezzanine of 3, 3 6.

Now it doesn't quite balance at the moment, it doesn't balance because we haven't completed the preference, um, amount.

But also because, um, if you look at the, um, the debt, the debt was 300 and it's gone up to 336.

So the debt has gone up by 36.

It's the other side of that I need to remind myself, if we look at the accounting equation, the mezzanine loan goes up by 36, retained earnings needs to go down.

So we haven't done the preference stuff, so it won't quite balance yet.

But to move it in that direction, what I would need to do is go to the income statement.

And on the interest expense line, I will need to include all the other interest expense numbers.

But we don't have to do that to make this balance.

But what I do need to do to make this balance is say, because go to the debt sheet, go and grab the accrued interest.

The accrued interest is an expense, so it needs to, it's gonna be treated as an expense, so it needs to reduce the profit.

So we need to be reflect that there.

Now the balance sheet's still not quite balanced because we haven't finished everything yet, but we, we've moved it in the right direction.

Okay, I think we're gonna leave, leave that there.

So the session was focused on, um, the debt schedule, which we, we completed, uh, and fed into the balance sheet.

If you wanna pick up and complete that model.

Then we have got the full version that you can download and the full version has the IRR calculation, et cetera in there.

We've pushed right up against the end.

So guys, thanks ever so much for your patience.

Uh, really nice. There's absolutely loads of people on this call. It's really nice to have so many people on the call.

Um, hope you enjoyed it. Look forward to seeing you in future sessions. Thanks very much guys. Cheers.