Hey guys, welcome along.

Right. Good to have you in the room. Hopefully, you can hear me.

Hopefully, you can see me. Hopefully, you can also see my screen share. My name's Jonathan.

If I point you towards the web address, that would be great if you can go there and if you can grab the materials. If you could scroll down to the bottom of the page, that'd be great. There are some files to download.

We're going to have a look at that first file, which is Debenhams Complex LBO Basic Version Empty. As I'm chatting, more people are coming in the room. If you've just arrived, on the chat box I'm posting the web address.

So if you can go to the website, and if you can scroll down to the bottom, and there's a file here called Debenhams Complex LBO Basic Version Empty.

Be great if you could grab that, please. Okay.

I'm going to click on that to download it, and then I'm going to get that open. We've got an hour. An hour is not enough time to go through the entire model.

So the plan is that we're going to build about halfway through the model.

I'm going to show you all the key points.

And then once we've done that, probably for the last 10, 15 minutes of the session, we're going to jump into the full version of the file. We're going to talk about the full version, and then we're going to delete some of it and rebuild the end. So basically, we'll skip through the more mundane part in the middle, and we'll get through all the key technical points. So that is the idea.

There is no way we are going to be able to build the entire model from beginning to end in just under an hour.

For those that have just joined, in the chat box, there's a link to a web address.

If you can click that link and scroll down to the bottom and download the file called Debenhams Complex LBO Basic Version Empty. I have that open already, so I'm going to jump to that now.

When you open up the file, you will land on a welcome sheet.

You'll land on a welcome sheet, and there are a bunch of other sheets which we're going to be looking at that follow the welcome sheet. We're going to have a quick look at them.

So we've got an LBO sheet. The LBO sheet is where all the assumptions sit. And so as you might imagine, we're going to need to talk about sources and uses of funds. And then having done that, we're going to need to project out the cash flows of the business.

And having done that, we're going to need to think about the amount of debt that we can pay down, and then we'd come back and do the IRR.

So the LBO sheet, no great surprise, has a section on sources and uses of funds. And thereafter, there's an input sheet.

Nothing for us really to do on the input sheet.

So these are the inputs to the model.

So these are all built for us already. We're going to leave that alone.

We've got a calculation sheet, an income statement sheet, a balance sheet sheet, the cash flow statement sheet, and a debt sheet. So we need to build out the income statement because in the LBO, we need to track what's going on with EBITDA.

So we need to know what EBITDA is in any given year, so we can figure out in that given year what the enterprise value might be.

And then when we've got the enterprise value, we need to go over the bridge to get from enterprise value to equity value to figure out our IRR return.

And to do that, we need to figure out what the debt is at that point in time.

And the amount of debt is contingent on the amount of cash that we generate.

If we generate loads of cash, we can pay loads of that debt down, and that enhances our returns. Okay. I'm not going to spend ages talking about the detail because we've only got 55 minutes remaining of the session.

So I'm going to get into the LBO sheet and start building it.

As I've been chatting, I think we've had a couple of people that have joined.

If you've joined, in the chat box, there's a link to the web address down the bottom that contains the materials.

You can see here the file name that I'm working on.

Okay, guys. So as I'm working through this, I'm going to make it hopefully super clear what I'm building. I'm going to show you formulas, and we're going to be zoomed in on things and coloring cells in, so you know what I'm doing.

Right. So what do I need to know initially? Well, at the very beginning of the model, I need to know what the acquisition enterprise value is for the business.

And to get that, I need two things. So I would need to know at entry what the EBITDA number is, and I'd also need to know an appropriate multiple. If you look at the information we've got here, I'm focused on the yellow cell, which is cell F6. So F6 is the historic EBITDA number, noting that this file was built a little while ago, and so the historic year here is 31st of December 2015.

So that's the current year, if you will.

We need to have a multiple to figure out what the EV is, and we have that multiple already. It's 7.5X.

So we can assume that that's come from comps.

So it's come from precedent transactions to get us to that number.

The product of the historic EBITDA and that multiple will give us our EV. So what we need to do is get the historic EBITDA. I'm in F6. I'm going to strike equals.

I don't have that number immediately at my fingertips.

But if you look at the different sheets we've got down here, we have an income statement sheet. So you'd imagine the EBITDA would be on there.

I'm going to click on that. And if we have a look at the income statement, we sort of scroll down. We start with sales.

We get COGS, gross profit, SG&A, EBIT, DNA, and we get down to EBITDA. So EBITDA is on income statement, exclamation mark F14. The number is 238.6. I'm going to grab that, and you can see the reference there on my screen. Okay. What I'd like to do is to get the EV.

It's an important number, so I'm going to put it in bold.

So the EV would be the product of the historic EBITDA and the 7.5X multiple.So the problem is that the EV is not what I'm going to pay to acquire the business.

What I need is the equity purchase price.

So I need to go over the bridge to figure out what that number is, and that requires me to know what the net debt is.

So I'm going to go down one cell to cell F9, which is net debt.

I'm going to say equals, and I'm going to go back to the financial statements of the business. Now, if we're looking for net debt, we're definitely not interested in the income statement. We want the balance sheet.

So if we jump to the balance sheet, again, we're in column F, which is the historic year. I'm going to go and grab the revolving credit facility, which is 155.4. That's in cell F17. I'm going to add to that the long-term debt of 197.1. And what we've got now is the total debt.

But what we really need is the net debt.

So I'm going to deduct from that the cash.

And if you look in cell F5, we've got 50.1, which is the cash position. I've deducted that. Yep, you can see I've subtracted that.

If I hit Enter, I've got 302.4 as the net debt. Noting that I'm building this in column F, and from the financial statements, I've grabbed these numbers again from column F.

Now I'm going to go for bold again. Let's bold the acquisition equity value. So this number's really important because that's what we're actually going to pay to buy the equity of the business, to buy the shares off the target shareholders. So if we grab the enterprise value, and thinking about going over the bridge, we're going to deduct the net debt.

I get to the equity value. Now, that number is going to feed directly into our sources and uses of funds table.

It is a significant use of funds. That's going to go down here.

Let's do that now. So I'm going to go down to the acquisition equity value in my uses of funds. I'm going to chuck in formula text next to that so you can see what I'm up to.

So let's just move that number down.

If I'm doing an LBO and I'm buying the business, then I absolutely want to wash away their existing debt.

So whatever terms and conditions exist in their existing debt are not going to be appropriate for the LBO.

We want to load on a load of debt to the business to try and maximize our returns. So it is a given that we're going to refinance the target's net debt, and that gets us down, or almost gets us down, to our total use of funds. But for any of you guys that are already working as investment bankers, you're maybe analysts, if you do any work on this deal at all, then you're going to want to get paid. And so we need to think about fees.

So I'm going to say equals, and there must be an assumption here on fees.

If you arrow up to cell J6, I have 3%. Now, it does say fees as a percentage of EV.

The reality is that the fees break down into different buckets. So you're going to have advisory fees, and that'll be relating to the valuation of that target business.

We're going to have debt issuance fees because we're issuing debt. And generally, in a deal, you might have equity issuance fees.

The way that the fees are treated from an accounting point of view varies a little bit. So the advisory fees get expensed straight away.

And you can see that if you look at real company financials, and you're scrubbing operating earnings or net income to get to recurring numbers.

You'll definitely see those non-recurring transaction fees sitting in the income statement.

We've also got equity issuance fees, and they don't get expensed, but they do get charged directly to equity.

So they usually find their way to share premium or additional paid-in capital.

We've also got debt issuance fees.

They do get expensed, but they don't get expensed immediately.

They get capitalized, and then they get amortized over the life of the debt. But ultimately, all of this stuff finds its way to the income statement.

And we could break this down in the model, but doing so isn't going to materially impact the IRR that we come out with.

So I think it's a very reasonable simplification just to say equals, grab those fees, and multiply them by the EV.

So the EV kind of representing the size of the business that we're taking advice on.

Also representing, I guess, the debt and the equity that's issued.

So it's a reasonable simplification to do that.

I'm going to multiply that not by the equity value, but by the enterprise value.

Okay, I get 53.7. Now, that total use of funds needs to be matched by the total source of funds. These numbers need to be the same.

And already we have financing in place.

So there's a whole piece of work which would involve sculpting that debt around the cash flows of the business.

But that's not the focus of what we're looking at today. So that work's been done.

Those numbers sit there already. Unfortunately, if I add up the financing that we have in the model, it doesn't match. So the sources do not match the uses.

And we really need these numbers to be the same. So let's just highlight these.

So they have to be the same number. We're going to use preference shares as our plug. So in terms of financing, typically, the sponsor is going to put equity in because the sponsor's going to want to generate a return through the expansion of that equity.

But they'll want to be ahead of other equity holders, like management and mezzanine, to a great extent. So they'll often inject preferred equity, and we're going to use that as our plug.

So if I take the total uses and I subtract from that the sum of all of the debt financing, including the mezzanine and the common equity, then we get-622.2.

622.2 being the amount of preference shares. As I'm chatting away, I see we've had a few people that have jumped into the room, so welcome along.

In the chat box, I put a link to the web address.

Down the bottom of the web address, you'll find the materials.

If you look at the very top of the file here for the file name, we're looking at Debenhams Complex LBO Basic Version Empty.

So you could grab that. You haven't missed very much.

I'll zoom out just a little bit just so that anyone that's catching up can see everything that we've done so far.

Okay, so we've done the sources and use of funds, which puts us in a good place.

There's a bit of analysis here to the side, but I said at the beginning, we will not get this file complete. So we're not going to do that analysis here.

We don't really need it. It's not a core part of what we're doing.

If you wanted a look at that, you can go and look at the full version of the file after the session. Let's move down.

So there's a little bit more to do on this sheet before we leave it and get into the financial statements. I'm interested in ownership.

So if you look at the ownership table down here, we've got mezzanine, which apparently at exit is going to own 0%, but at entry... Sorry, own 5%, but at entry own 0%.

So how does that work? Well, what I want to do in the LBO is I want to load on as much debt as possible, and the more debt we load onto the business, the more difficult it's going to be to incrementally increase the leverage. So at some point, we're going to have a conversation with probably a hedge fund about mezzanine financing. So we're going to say, "Right, I want to go and borrow more money, and I'm keen to borrow money from you." And the mezzanine lender would say, "Well, that's okay. We can maybe talk about this.

We can lend you a certain amount of money at a certain rate," quite a high rate.

The interest on that will probably be PIC, so payment in kind.

So it'll accrue until the end of the loan, but they're going to want a sweetener, and the sweetener is we're going to give them, and I'll get a focus on the word give, we are going to give them some equity at exit.

So at entry, they lend money.

Interest accrues throughout the term.

At the exit, at the end of the deal, we pay them the accrued interest, we pay back the principal, and we're also going to give them a share of the business as an incentive for them. Now, you can't just magic that out of thin air.

It's got to come from somewhere. So let's try and track that.

Well, at entry, clearly the total equity needs to be 100%, and that must mean that the institutional investor has 100% minus the mezzanine, minus the management. These are really easy numbers.

So they must have 90%. That's just our plug.

Now, at exit you could say, right, at exit the institutions are going to have 90% and the management are going to have 10%. That just doesn't work, does it? Because then we get 105%. So what we need to do is we need to think about the institutional investors and the management proportionally giving up some of their equity and giving it over to the mezzanine investors.

This is a good deal for them because it means they can get more leverage at entry, which means they can boost their IRR.

So from an institutional investor point of view, we're going to take that 90, and we could deduct the 5%, but they're going to say, "We are not giving up the full 5% from our share. This needs to be broken down proportionally." So that 5% we're deducting, I'm going to multiply that by 90%. So they're going to have 85.5% of the equity.

And we'll do the same thing for the management.

We're going to take their 10% and we're going to deduct 5% from that.

But they're not going to take the full 5% and give it over.

It's going to be weighted proportionally. So we'll multiply that by 10%.

So it means at entry, the institutions go in at 90%, but they come out at 85.5%, and the management have the same situation. And we need to think about how to track that when we're calculating the IRR. But let's not think about that yet.

Let's go to the side. We're going to do just a little bit more work on goodwill. Now, broadly, the goodwill is always the price you paid for something against the value of the thing that you just purchased. And so if I show some formulas here, the price I paid is definitely the... It's called acquisition equity value. I often call it the equity purchase price.

But nevertheless, it's an F10, and it's 1487.1.

Now, what we need to do is deduct from that the, well, I'm going to use the word fair, or the term fair value.

The fair value of the net assets that we've acquired.

Now, this model doesn't quite go that far, but let's talk about that a little bit.

So if I say equals, how do I establish the value of the net assets that I've acquired? Well, I've got to go back to the financial statements and the target.

I'm going to go to the target's balance sheet, and on the target's balance sheet, I'm interested in column F, which is the historic year.

I'm going to grab their total assets, and I'm going to subtract from that the total liabilities. Now, 2,142 minus 1,289 is about 800. Okay? So it's about 2.1 minus 1.3, so it's about 800. And if you look down here, the equity is 853.3. So actually we know what that is because, of course, the net assets is the same as the equity value. And you could grab it either way.

Now, what I want to do is go back to the balance sheet and just eyeball it.

What we are supposed to do is line by line, we are supposed to appraise the balance sheet, all of the assets, and also all of the liabilities. So we're supposed to look line by line and say, "Are these values fair?"Are these fair values? We're really marking these to market, and we may well mark assets like PP&E up. Maybe assets might get marked down. Liabilities might get marked up.

They might get marked down, et cetera.

So we're making adjustments here.

One thing we should also do, if you think about the phrase fair value, and I should also fall under goodwill in the target company. If there's any pre-existing goodwill, what value does that have? It has no real fair value, so we always step that down to zero. But in this model, we don't need to do those things.

I wanted to talk about them, but there's nothing to process there.

So now we can calculate the goodwill.

And the goodwill is equal to the equity purchase price minus the net identifiable assets. I got 633.8. So that's important because one of the complexities we want to think about is how to consolidate the balance sheet of the target into what will effectively be a special purpose vehicle, so an SPV, a shell company.

And to do that, I'm going to look at the financial statements.

Now, I'm actually going to skip over the input sheet and the calc sheet and the income statement. I'm going to go straight to the balance sheet. So let's click on the balance sheet here.

Now, what you notice is that we've got a combo column, and I might just grab that combo column here and just highlight that in a color.

Let's just highlight that in green.

So in green, we've got a combo column, and then we've got projections thereafter. Now, in terms of projections, I could go to the cash row in the first projected year, and I could say equals and link it into the historic number. That's definitely wrong, okay? For a bunch of reasons, but not least, didn't we, in our sources and uses of funds, when we think about our uses of funds, didn't we refinance the target's net debt? And that means that their cash is going to disappear.

It also means that their revolving credit facility is going to disappear, and the legacy debt number is going to disappear.

So actually, it's not right for me to start projecting off of these historic numbers. We need to play around with them a little bit.

Now, let's just think about how we might play around with them.

So focusing in on that green column here.

Very straightforward. I'm going to go and grab the historic number in 2015 and the adjustments for accounting and financing. We probably could have some more adjustment columns here to break it out a bit more, but we've got a couple, so we're going to deal with that. Now, I'm not suggesting that the debt, the combo debt, is going to be 50.1 because we're going to do some adjustments. But initially, I just want to get these subtotals in here.

So I'm summing across horizontally, and then when we get down to total current assets, I'm going to alt equals, equals that to sum that up. We're going to do exactly the same when we get to the PP&E. So we're going to sum across horizontally, and then we're ultimately going to get down to the total assets.

And when we get to total assets, we're going to grab the total current assets, comma, and all of those non-current assets to give us our total asset number.

Now we're going to get down to the non-current liabilities.

We're going to go and grab the prior year and those adjustment columns and copy that down. And let's just get through this because I know you know I know you know what we're doing. Okay, so let's just copy that down to our total liabilities.

Quite like that in bold. So I'm going to add up the current liabilities and all the non-current liabilities.

So I'm not doing anything fancy here at all.

This is really straightforward stuff and just adding things together.

The equity would be the same idea.

And then we're going to add up. What is this? The liability.

We're going to add up the liability and equity. And hopefully, unless I screwed this up, hopefully, we can go and take the liability and equity and subtract the assets, and it balances. So I'd say that's absolutely critical. If I highlight that in yellow, that cell in yellow is really important that everything balances here.

Now, I think we'd already established that these numbers need to be updated because we need to get rid of the target's cash and revolver and long-term debt. We also need to see all the deal financing appearing in the balance sheet as well. We don't have any of that.

So let's go and do some adjustments here.

We've got a couple of columns, accounting and financing.

As I said, probably I might have even had some more to break it out a bit more, but this is the template that we have at our fingertips.

So what I'm going to do from an accounting point of view, I'm going to recognize in goodwill, which is in cell G14 equals, that if we do this deal, we are definitely going to create goodwill.

And that goodwill we've built together, that's on the LBO sheet, and it was 633.8. So if you go to LBO!H26, you've got a number 633.8, which is the goodwill that is being created in this deal. That's going to find its way into cell G14. So now we've got this goodwill.

I've got a problem. So now I've made that change.

This doesn't balance. So what we need to do is process all the changes to get this to balance. Really, we need to put in place all of the financing. Now, the financing is used to pay off the existing debt, and the financing is used to also buy the business.

But the value of the assets that we're about to consolidate into the new company are going to be less than the financing we put in place.So that means that we need to recognize goodwill. So that's really sort of at a high level how it works.

What else might we do in the accounting column? Well, the equity equals. The equity of the target company.

We've just bought them, so their equity, we've just bought that.

That's no longer there anymore. That no longer exists.

They're part of a group, they're part of a combo.

So I'm going to eliminate that because we've acquired that.

We are very much out of balance now, unfortunately.

Let's go to the financing column. I'm going to get rid of the cash, and I'm going to get rid of their existing revolving credit facility, and I'm going to get rid of their existing debt.

Now, what I want to do is I want to put the new financing in place.

And they do actually have a revolver in there, but it's zero, so I'm just going to ignore it just for ease, really, and for time purposes.

If I go to the first lien row, so the senior debt that we're going to put in place, I'm in the financing adjustment column, so in column H, I'm going to say equals.

And all of the financing we have is shown in the LBO sheet. I'm going to use my mouse a bit more than the keyboard just so you can see what I'm doing, because it's just a bit easier to see when I'm working at some speed.

I'm going to go and grab the first lien debt, which is in LBO!G15. It's 400. So I'm going to go and grab that. Let's hit Enter. And I'm going to copy this all the way down, because running in sequence, we've got second lien, junior notes, mezzanine, and pref, so I'm going to Control + D that all the way down.

We've also got equity that we're issuing, and I haven't really captured that yet.

So I'm still in the financing column.

I'm going to go to the equity row. I'm going to say equals, back to the LBO sheet, back to the source of funds.

We've got common equity of 10.

Okay? Common equity of 10.

Right.

So it should balance. Oh, no, it doesn't balance. It's out.

Now, what I'm going to do is I'm going to go to the accounting column, and I'm going to go to the equity row, and I'm going to jump into that, and I'm going to say minus, and then we're going to go back to the LBO sheet.

Now, I'm sort of pausing there because I'm just giving you a chance to think about it. You quite possibly know what I'm about to do.

If you think about that number, 53.7, we've seen that exact number already. If I go back to the LBO sheet and I look at the uses of funds, that's the fees. So the fees we're going to charge to equity. If they're advisory fees, they get expensed immediately, so go to retained earnings. If they are equity issuance fees, which may be not, but if they were equity issuance fees, then they would go directly to additional paid in capital, share premium.

And if they're debt issuance fees, they'd get capitalized, and they'd ultimately get amortized as expense into retained earnings.

So I think it's reasonable just to say ultimately, this stuff will always find its way into retained earnings. It is an expense.

These fees, it's a use of funds, so it's an expense for us. So if I make that minus 53.7, then we now see that this balances. Okay. So hopefully that's good.

Sort of part of the complexity stuff I wanted to talk about was getting through the sources and use of funds with the different tranches of debt, thinking about the ownership structure, thinking about the goodwill, but doing this consolidation. So this is quite important.

What we're going to do now is we're going to go back to the income statement, and we're going to start to build out the income statement, and then we'll build out some of the balance sheet. I said this at the beginning, there is no way we're going to get this model finished if we try and work through it from beginning to end in a linear fashion. So we're going to do about 15 minutes more building the model, and then with about 25% of the session remaining, we're going to open up the answer file, the full file.

We're going to go and have a look at it.

We're going to remove some of the details and delete some of the numbers, and we're going to rebuild the bit at the very end, because I really want to show you the breakdown of the IRR and how mezzanine management and institutional investors get their returns. I think it's an important takeaway.

So what I'm going to do, guys, I'm going to go to the income statement now, and I think I'm going to zoom in a little bit.

So I noticed in the income statement, we've also got a combo column.

I'm going to show you my formulas here.

Now actually, from a formatting point of view, it's kind of useful for the first projected year to be in column J, not just in the income statement, but also in column J in the balance sheet.

So in order to maintain that matrix integrity, we kind of need to have these columns, extra columns here. But I am actually going to fill them in.

So there's nothing going on in the accounting or the financing.

Like, absolutely nothing. But just for consistency, I'm going to go to the combo column. I'm going to say Alt + equals, and I will grab the historic numbers, the accounting, and the financing section, and I'm going to copy that. Actually, it'll be faster if I just copy it all the way down, Control + D. And then when we think about the individual profit numbers that appear, what I'm going to do is rather than build...

Oops. Rather than build these subtotals myself, I'm just going to copy these out to the right.

So we're just going to copy them out to the right like this.

You'll be able to see if I Control R these out, you'll be able to see in the formula text, you'll be able to see that they're summing vertically.

So it's just a little bit faster building it out like this.

I don't really like all those zeros being in the adjustment columns, because this is only visual, but I just find that quite messy.

So I'm just going to get rid of those. Okay.

So that's me fairly quickly building out the combo income statement. You'll see that this just sums horizontally until we get to a total, and then it sums vertically.

So the SG&A sums horizontally, the EBIT sums vertically. Same with depreciation, same with EBIT, same with EBITDA, et cetera. Okay, you don't need me to show you all of them.

Right. If we were going to build out the modelThen really, we need to think about building out the sales.

The sales drives loads of the model, right? So you could say, "What do you think my inventory is?" You could say, "What do you think my COGS are going to be?" Well, it depends how much you're going to sell.

You could say, "What do you think my tax is going to be?" Well, it depends how much you're going to sell. What about my inventories? It depends how much you're going to sell. What about my CapEx? It's all dependent on sales. So sales is a big driver of a model for a corporate. So I'm going to go into cell J5. So we are in column J. I'm going to say equals.

Now, I know that in the input sheet, which is where all the assumptions are, there'll be a revenue growth assumption.

So it's going to grow by a certain percentage.

Probably not a very large percentage.

And I say that because if this business was forecast to grow very aggressively, then that growth would need to be matched by an increase in net working capital and an increase in PP&E, so extra CapEx. And if it was a business that was growing aggressively and spending a lot on OWC, and spending a lot on CapEx, then it wouldn't have much cash left at the end.

And if it didn't have much cash available, then it wouldn't be able to borrow lots of money, and we wouldn't be able to get a good IRR.

So I'm hoping that if I say equals open bracket one plus, if I go to the input sheet, I'm going to use my mouse so you can see what I'm doing here.

If we have a look at the sales growth rate, it is in fact a fairly anemic 1.3%. So it's a really low growth rate. I'm going to close the bracket on that and multiply it by, back to the income statement sheet, and I'm going to click on the prior year.

So I'm going to multiply it by the prior year.

So we've not got fantastic growth, and almost that's a relief for me because that means it's the kind of business that can borrow loads of money because it should have strong cash flows.

Let's think about the COGS. So if I say equals, again, back to the input sheet, and I'm going to do this fairly quickly because this is just a standard model build. The COGS is in input J12.

It's 87.3%. It does say it's a percentage of sales.

I'm going to multiply that out by the sales there, and then I can Control + R out the gross profit.

I'm also interested in SG&A, so I'm going to say equals.

I'm going to go back to the input sheet.

Now, there is an opportunity in the input sheet to do a little bit of work in Excel, maybe using the choose function or the index function or the offset function to build in some sensitivity for management case or bank case. Because we don't have huge time on this, and I don't think it's a very important takeaway, I'm not going to do that. What I am going to do is I'm going to select one of these, and I'm going to be optimistic. So when you think about the business, we're going to go into the business, and we're hoping that the EBITDA is going to uplift, and part of that uplift is through control of SG&A costs. So I'm not going to take the pessimistic bank case that assumes that they are 7% of revenues.

I'm going to take the optimistic management case.

I'm bringing the management on board.

They've got equity, so if they think this is what they can do, we're going to have to place some faith in that. I'm going to grab that number, which is the 6%, and it does say here as a percentage of sales.

So I'm going to multiply that by, back to the income statement, I'm going to multiply that by the sales. Okay.

And then Control R gets us EBIT.

I now want to get to EBITDA, and this is a bit of a pain, actually, because to get to EBITDA, I need DNA, and to get DNA, I need to do a little bit of work on this. So if I go now, I'll show you with my mouse. I'm going to go to the calcs sheet, which is the sheet to the left.

And in the calcs sheet, if I highlight the main cells in green, in the calcs sheet, we have a working for property, plant, and equipment.

I notice that the projected year is still quite sensibly in column J, and I guess I want the ending PP&E in column J at some point.

And if we take a step back from that, I also want the DNA in that period.

Now, to maintain the consistency of the spreadsheet, what we're going to want to do is get the ending balance here for the PP&E. So I'm going to actually go back to column F, and in column F, I'm going to say equals, and I'm going to go and grab from column F in the balance sheet. So I'm going to go to the balance sheet column F, I'm going to go and grab the PP&E.

In balance sheet exclamation mark F11, I've got 675.3.

675.3 is the ending PP&E in the historic year. I'm going to grab that.

Now, I'm going to sum across, kind of as we did previously.

I'm going to pick up that historic number and sum across these adjustments because we haven't had to do it here, but when we did the goodwill calculation, I talked about potentially doing a fair value adjustment, and we might step up the PP&E, and if we did that, then we'd need some way of accounting for that.

So we're not doing that here, but I just kind of want to flag that that's probably how I'd build it in the model. It'd be a clean way to build the model.

So I'm going to grab that number. Now, I want to go into my projected year, so that ending PP&E from the combo becomes the beginning PP&E in the projected year.

I need to know what the CapEx is. There will definitely be an assumption for that.

So I'm going to go to the input sheet.

I'm going to eyeball the input sheet.

Can you guys see that in the balance sheet assumptions in row 22, we've got CapEx? It says CapEx as a percentage of sales.

So I'm going to grab from J22, I'm going to grab that assumption, which is 3.4%, and I'm going to multiply it by, let's go to the income statement. I'm going to multiply it by the sales in the income statement.

So I've got input exclamation mark J22 multiplied by income statement exclamation mark I5.J5.

We definitely want to make sure we've got matrix integrity here.

Okay.

And, now we want the depreciation.

So, I'm going to say equals, back to the input sheet.

There must be an assumption here. I can see it.

If we look in row 13, it says depreciation as a percentage of last year's net PP&E.

So, if we take the assumption in J13, and I multiply it by, back to the calc sheet, our beginning balance in J6, and hit Enter.

We've got 73.6. Now, just a quick pause on that.

They've got CapEx of 80, and they've got depreciation of 73.6. That means that the PP&E is going up slightly, and it's not going up aggressively, but it's going up a little bit, and that seems consistent with the idea that sales are growing, but only growing very modestly. So you'd expect to see that small uplift.

Let's add those together. I get 681.7, and we don't really need that number yet.

What we needed was the depreciation.

But we can now go back to the income statement, and I'm in cell J12. I'm going to say equals. I can go back to the calculation sheet and grab the depreciation in J8.

I'm going to leave that as a negative number because that seems to be how it's been presented previously.

I also want the amortization. Now, there isn't a complex working or calculation for intangibles. If you look at it, previously, the amortization has been zero, minus 11.4, zero, zero.

So they haven't really had much amortization.

So we don't need to build out anything very complicated here.

If I say equals, I can go back to the input sheet, and this caught my eye earlier.

We've got here in J14, we've got zero. So there is... Whoever's built the model, which wasn't me, actually, but one of my colleagues built this. So whoever's built the model, they are assuming that there will be no amortization here. That's cool. Going to leave that in there.

And then EBITDA.

The EBITDA number. Oh, look at this. I need to make that a negative, don't I? Sorry.

So EBITDA number should be about 200, and clearly, I haven't been paying attention because it's massive.

And if I track back, clearly, the gross profit looks utterly ridiculous. The number looks a bit off, and it's not the right sign.

Let's just do that again. So if I say equals, probably because I was using my mouse, which is often a bad idea.

Let's go back to grab that assumption.

I've got 87.3%, and I'm going to multiply it by the sales. Okay. That looks way more sensible, and I get an EBITDA of 231.3.

Okay. In addition to the discussion we've had on the bank case and the management case for the SG&A numbers, there's also potentially some belief that we might be able to make further EBITDA improvements, and there's no deep dive in this particular model, but we're building them in. So if I say equals, I didn't actually see any assumptions on this at all in the input sheet.

But what I did notice is if we go back to the LBO sheet, and I just wave my mouse around here.

Can you see in LBO!F11, we've got 2%? So that's the EBITDA improvement as a percentage of sales. I'm going to take that.

I'm going to lock that with F4, and then I'm going to multiply that by, in the income statement, the sales.

And I've got 47.1. I'm going to copy that out to the right.

Okay. Interest tax expense, I would love to do that now, but we can't do that now because to know the interest tax expense, I'd need to know what the debt is, and we've got all the debt sitting in the sources of funds. But what we haven't done is done our cash flow statement forecast. So we haven't figured out how much the debt's going to get paid down in any respective year. So we just can't go forward with that.

But what we can do to complete the income statement is we can pick up the tax expense. So if I say equals, I go back to the calc sheet, back to the input sheet. There should be a tax assumption, and it looks like in J16, I've got 20%, which appears to be the forecast effective tax rate.

So I'm going to grab that, and I'm going to multiply it by, back to the income statement, the profit before tax.

And then I'm going to copy that out to the right.

Okay.

So the tax seems a bit high. The profit seems a bit high.

The income before tax all seems a bit high relative to prior years.

But of course, what we don't have here is we don't have any of the interest. So they're going to put loads of debt on this.

I mean, the debt's going to be a massive increase.

So the interest is going to go up a lot.

I don't know what, but it's probably going to go up to about 100 or something like that to bring this back into line. So we haven't got that number. I can't just hard code that in.

But when the model's built, that number will be there.

We've got a little bit of time. I do want to have a look at the answer file.

But before that, we're going to do a little bit on the balance sheet.

So let's go to the balance sheet because it's exactly the same kind of deal.

So if I look at the balance sheet here, let's just clear out this formula text and zoom in on the balance sheet a little bit. We can show some formulas here. I think we might have about five minutes on the balance sheet.

Okay. I don't know the cash. I haven't done the cash flow statement yet, so I can't know the cash. We're going to leave that alone.

We're now going to do the accounts receivable.

Now, I know because it caught my eye that when we look at the input sheet, we've got accounts receivable days.

I can show you that. So if I go back to the input sheet and let's zoom in... a little bit. If we look at the balance sheet, we've got receivable days of 3.9.

What I want to do is I want to figure out what the receivables are.

So if I go to the side, we can maybe say, well, look, the accounts receivable days is equal to accounts receivable over sales multiplied by 365. And if you think about what I know, I know the...

I know, there we go. I know the receivables days.

We've got that on the input sheet. I know the sales because we've done the income statement, and obviously I know the constant 365.

So I could rearrange that to solve for, let's have a different color. I could rearrange that to solve for accounts receivable. And to do that, I would say, I guess that accounts receivable days multiplied by sales divided by 365 equals accounts receivable. So I'm going to do that now.

If I go to the accounts receivable cell, I'm going to use that formula. So I'm going to say equals, I'm going to grab the accounts receivable days on the input sheet. I have 3.9. I'm going to multiply it by the sales.

I'm going to go to the income statement and grab the sales number here.

It's in column J.

I've got 2352.9, and I'm going to divide the whole lot by 365. Now, if I've screwed this up, this won't look sensible. But previously, I had 24.9, now I've got 25.1. That seems quite reasonable to me, so I think I'm good with that. I'm going to do exactly the same with inventories.

I'm going to say equals, I'm going to go to the input sheet, and I'm going to go and grab the inventory days. Now, I don't really know how this has been calculated, but I think it's probably based on COGS. It could be based on COGS or COGS and SG&A.

It's probably not just SG&A, but I think it's based on COGS.

So I'm going to take that number and I'm going to multiply that out by, in the income statement, the cost of goods sold.

I'm going to divide it by 365 in the same way that I have done for accounts receivable. And I do have a very sensible number there.

I want other current assets. Other current assets, there'll be an assumption for that, an input assumption.

And you can see here I've got 2.3%. If you look at the heading, it says, "Other current assets as a percentage of." That's a bit disappointing, but I know it's a percentage of sales. So I'm going to multiply that out by the sales in the income statement.

And that looks like a reasonable number.

I've now got my total current assets.

I now need my PP&E, my intangibles, et cetera.

Well, the good news is that I know the PP&E, because if I say equals in the calc sheet, I already have that. It's in calc!J9.

And the intangible assets is going to be quite easy because we had no amortization. That means that the intangible assets are equal to the previous year plus, if we go to the income statement, going to add the amortization number. Now, I know it's a zero, but if we had a number come through as we have previously, it would be a negative.

So if you add the amortization, it would reduce the intangibles.

So that's good.

The other long-term assets, I don't know at all, but there must be an assumption here in the input sheet.

So I'm going to go to the input sheet, and I've got in cell input J23, I've got 76.1.

76.1. The other current assets just look like they're going to be the same every year. So I'm going to grab that. And finally, this is the final thing we'll do before we go and have a look at the answer file and build out the IRR together.

The goodwill. So I'm going to make the goodwill equal to the prior year.

Goodwill, in the real world, is definitely going to go up.

At some point, a company's going to make an acquisition, and it's going to go up.

It might go down as well. But I can't meaningfully know either of those things. To know how much goodwill is going to go up by, I'd need to know if a company or when a company is going to make an acquisition.

I'd need to know the equity purchase price.

I'd need to know the fair value of the target's assets, and that's crazy. I can't know any of that.

So you can never forecast it. It would go down if they impair the goodwill, but that's not forecastable either.

So I'm going to make it the same as the prior year, and then we're going to copy that out.

Now, we've done quite a big chunk of the model.

We are pretty much exactly halfway.

So we've got a bit more balance sheet to do and cash flow statement and the debt schedule to look at, but we just don't have the time to do that.

And what I want to do is kind of look at the analysis of the model. I'm going to delete some of the full version and rebuild it with you.

So let's leave this model where it is.

Let's go back to the website, and down the bottom, you've got Debenhams Complex LBO Basic Version Full. So let's click on that and open it up. And we've got 11 minutes to look at this. Now, as we open up the full file, we're going to see a load of stuff that we're already familiar with.

We land on the welcome sheet. If we go to the LBO sheet, you can see the historic EBITDA of 238.6 that we pulled in. You can see the acquisition EV and the acquisition equity value that we calculated. You can see the sources of funds, including the fees of 53.7 that we calculated.

You can see the exit percentage for the institutional equity.

You can see the goodwill number of 633.8.

If we go to the balance sheet, you can see all the adjustments that we made on the balance sheet to get to the combo column.

And we built all the income statement and half of this balance sheet.

So we basically just left our file here. We got to that total asset number.

So everything we've builtGoing forward, the model has a built cash flow statement, and it has a built debt schedule. And from that, we've been able to establish what the ending debt is at any point in time, which means that we can calculate the IRR. Now, what I'd like to do with you guys is go...

Let's do this together. If we go back to the LBO sheet.

So on the LBO sheet, we've got a section for equity returns.

I'm just going to highlight a few rows in green for us, and we've also got returns for institutional investors.

I'm going to highlight these in green.

Now, what I'd like to do is delete these numbers. So I'm going to get rid of these green cells, and we're going to rebuild this together so that we can really understand how the IRR works for an institutional investor.

Okay, what do we need? Well, let's show some formulas. And we're going to, first of all, just think about generally the equity value that will flow to all investors.

So we're going to start with the adjusted EBITDA.

If I say equals, you know because we built this, that the adjusted EBITDA is in the income statement.

My eye has fallen initially in the projected year in column J, in J14, which is the EBITDA number of 2,31.3. However, we want the adjusted EBITDA.

So I'm going to move down a few cells to J17.

So if we can pick up 278.3, which is in J17.

I think I'm going to zoom in a bit here so it's easier to see.

Okay, now we need the enterprise value.

Now, I know that the enterprise value exit will be the product of the exit EBITDA and an appropriate multiple.

If we scroll up, I do not have an exit multiple, but I do have an entry multiple of 7.5, and I'm going to assume that the multiple doesn't expand or contract.

I mean, it might do, right? So you could maybe look at that, but I wouldn't want to build an investment case on the multiple expanding because that's a kind of macro event, that the market inflects upwards and it brings the valuations up with it.

And I can't control that directly.

So I might well benefit from that, or it might go against me even, but I don't want to build an investment case on that.

So I'm going to assume no expansion or contraction.

What I want to do, if I've got the enterprise value, I want to go over the bridge to get the equity value, and to do that, I need to know in any given year what the cash balance is, et cetera.

If I go to the balance sheet, I'm not really expecting to see a cash balance, but there is a small cash balance. I think in the model they have a small amount of minimum cash that we need to pick up.

And I want to pick up all the debt excluding mezzanine.

So I'm going to say, in fact, I'm going to say alt and equals to pick up the sum function. Back to the balance sheet, and let's go and grab, in the projected year, let's go and grab all of the debt numbers.

But I'm not going to include the mezzanine.

So I've got J21 to J24. Separately, I'm going to go and grab the mezzanine.

So I'm going to say equals and grab the mezzanine loan.

The reason that's broken out is because we want to be able to calculate returns separately for mezzanine investors.

And then the preference shares, I can Control + D that. That sits directly below.

Now, we've got six minutes left and we've almost done this.

To get to the equity value, I'm going to take the enterprise value, I'm going to add the cash, small amount of cash, and I'm going to subtract the debt, I'm going to subtract the mezz, and I'm going to subtract the pref's.

And that gets me 250.8. Now, there's some calculations already for the mezzanine and the management, which we won't focus on. But if I copy these out to the right, these numbers out to the right. But before I do that, I just noticed as I copy these out to the right, I want that link to the exit multiple to be locked. I'm glad I picked that up before I copied this out.

So let me copy that out to the right, like so. There we go. And we've got the equity value.

Now, we need to be a little bit careful here because that equity value is not all going to flow to the institution investor.

Remember, they shared some of the equity with management at entry, but at exit, they shared with management and mezzanine.

So we need to pick that up and that's important to model.

We also need to consider that the institutional investors also get the preference shares. So they've invested the preference shares.

Well, let's not get ahead of ourselves.

Let's first of all think about year zero.

So in year zero, they invested the preference shares and they invested their share of common equity. So for the preference shares, I'm going to say equals just a little bit further up to the sources of funds, going to go and grab the pref's of 622.2.

I think I want that to be negative, so I'm going to multiply it by minus one.

Let's show the formulas here.

I'm going to do the same for the equity value. I'm going to say equals, and I'm going to go and grab equity and the sources of funds. It's in G50 of 10.

Now, wait a minute. They didn't invest all of that because I'm going to multiply that by the ownership table. At entry, the institutions put in 90% of the common equity and management put in 10%. So I'm going to multiply it by 90% multiplied by minus one.

And that will give me my institutional cash outflows. What about their inflows? Well, equals. We've got the preference share broken out separately above, so I can go and grab that from above. They would get that.

And the equity value, we've got the equity value above as well.

I'm going to grab that. But wait, I'm going to grab that and I'm going to multiply it by the percentage share they come out at.

So it's an important part of the model to do that.

So if I go up, the institutions at exit get 85.5% of the equity. I think I'm going to lock that using F4.

Now, the question is, okay, so what are-their cash flows in year one? Well, they're probably nothing because we're only going to have one cash inflow when they exit. And if I say equals, I'm going to say if, open bracket, if this year, which is year one, is equal to the exit year. And if we go up to the assumptions, there's an exit year assumption in J9.

So if J31 is equal to J9, comma, so if that's true, go and grab the preference shares and the equity value, comma. If it isn't true, just grab nothing.

And that means that it's not very exciting, but we just get nothing coming through.

If we copy this out to the right, if we control R this out now, then you can see in year one, two, three, four, five, six, seven, you can see that if statement pulling through the equity.

Now, investing 631.2 in year zero and getting 2408.6 in year seven, in absolute terms, sounds fantastic, but we need to look at that on an IRR basis. So we've got an IRR function in here.

I didn't bother deleting it. The IRR function picks up the initial investment and six years of nothing, and then it picks up the cash coming in in year seven.

So the IRR for that is 21.1%, which is okay. It's not going to set the world on fire, but it's okay. It's not a bad return.

Okay, guys, so I think we're going to bring this to a close.

As I said right at the beginning, we couldn't get the entire model done.

It's just not feasible. So what I tried to do is trim the more mundane parts of the model out of the middle.

We did all the sources and uses of funds.

We looked at the ownership percentages.

We looked at the goodwill calculation.

We did the consolidation of the balance sheet, which I thought was an important thing to do. We started to build some of the financial statements so you can get a feel for them, and then we skipped to the end.

We deleted out the stuff on the IRR returns because I really wanted to show you how we'd integrate preference shares and ownership percentages into that IRR calculation. And that leaves us with probably only seconds to the end.

My local time, it's one minute to the hour.

So, guys, thanks ever so much for being on the call. I really appreciate it.

We're running loads of Felix Live sessions, so I hope to see you in the future on some more. Have a great Friday. Thanks very much, guys.