We're going to have a look at Debenhams complex LBO, basic version, empty.

We've got an hour. So we're going to build part of this file together, and then we have another session, I think it's in a week or something like that.

A session a little further forward.

I'm going to come back to the model and we're going to finish it off.

So this session is focused on setting up the LBO, looking at the deal, analyzing the deal, setting up the balance sheet and the income statement.

And then the next session we're going to run, we'll pick up on the balance sheet and the cash flow statement, and deal with the deal analysis.

We've only got an hour and so that seemed like a sensible way to structure this because it is quite a big exercise in its own right. Okay.

So I don't think anyone else has joined since I've been chatting.

Post the webpage on the chat box. You should be able to see the chat box.

And if you land on the webpage, scroll down to the bottom, Debenhams complex LBO, basic version, empty. And I didn't need to download that myself.

So if I click on that, I'm going to get that downloaded.

So I've opened up the file and I am going to ask you in the first instance to go from the welcome sheet to the LBO sheet, which is really the deal sheet.

So it sets up all the fundamental details of the deal. Let's go and have a look.

So we're going to look at an LBO from a few years ago, but it's for a UK-based company, very large company actually, very prominent business, called Debenhams, which is a high street retailer.

Not particularly high end, but not a deep discount business.

Sort of middle of the market. Always a difficult position to occupy.

It was a business that probably didn't have much growth in it, honestly, which made it interesting as an LBO, potential LBO target.

Lower amounts of growth meant low required CapEx and low increases in operating working capital or net working capital, which meant that they had really strong cash flows, and that was the kind of business that could both tolerate a decent amount of debt being loaded onto it and also service that debt, pay that debt down if it chose to. So it seemed like quite an attractive target.

It was also a business that I would say was quite inefficient, so quite badly run.

So there were opportunities here to increase the EBITDA, not through revenue growth, not through top-line growth, because of course we wouldn't want to get into having to pump loads of cash into CapEx and increase in OWC because we want that for debt repayment. But increasing EBITDA really through rationalization, cost rationalization exercises. So, it seemed like probably quite a good target.

In any event, it didn't come to pass because the business did ultimately fail.

But I think there was an opportunity at a point in time.

Still based on a real business. If we have a look, first of all, at the LBO sheet.

At the very top left, really, of the LBO sheet. If I highlight this.

Maybe we're going to go for yellow. And I'm going to show some formulas here.

We want to grab the LTM, the historic EBITDA number, because that's going to drive a lot of the models. So when we think about the entry multiple of 7.5X, we're going to pick up the LTM EBITDA with that.

The product of those two is going to give us the entry enterprise value, and then we can figure out the sources of funds and make everything align. So this is a big number.

If I give you just a quick tour of the model before we start pulling numbers in and building this.

So we've got the LBO sheet, which is where we're definitely going to start.

There's an input sheet. The input sheet is a separate sheet with all the assumptions on it. So they're all captured here.

We've got a calculation sheet, not an unusual calculation sheet at all.

We've got base analysis for PP&E and equity. We've got an OWC calculation.

We've got an income statement here.

We've got a balance sheet, a cash flow statement, and a debt schedule.

On the balance sheet, you might notice that in column F, we've got the most recent financial year based on when the information was taken. So the historic year, 31st December 2015.

And then there's some adjustment columns here, combo.

So one of the things that makes the model interesting and more complex is we're going to think about consolidation.

So we're going to bring consolidation into the model.

Just as I'm chatting, I see we've had people that have joined.

If you've just joined, on the chat box there's a link to go and grab the materials.

Materials are on the bottom right of that webpage, and the file name is at the top of my screen here. Okay.

So we're going to do a bit of consolidation, but let's not get sucked into that yet. What I want to say is that in column F for the balance sheet, we've got the most recent historic data. For column F for the income statement, we've got the most recent historic data. Let's go back to the LBO sheet, and in yellow, I'm in cell F6. I want the historic EBITDA.

So I'm going to say equals Control and Page Down.

I'm going to jump to the income statement.

And if you eyeball this, you can see that we've got the historic EBITDA number in income statement F14.

It also appears in F17, and the number's just the same because we're going to pick up some cost savings going forward in our projection, but it's irrelevant when we're looking historically. So it doesn't really matter which one you pick up.

F14, my gaze fell on that initially, so I'm going to go and grab that.

So we need that first of all. Now, to get the acquisition enterprise value, I'm going to grab the product of the EBITDA and the entry multiple.

I get 1789.5. And in many LBO models that I might build, particularly if I want to take a shortcut and I want a sort of quick and dirty model, I might leave it at that.

But we're going to go a bit deeper here.

So what I might do is I might say that the acquisition enterprise value is reflective of our uses of funds, and it is reflective of our use of funds.

But what I want to do is I want to break that down.

So if you think about the enterprise value, the enterprise value would be equal to the debt, the existing debt, which would need to be refinanced in an LBO, and the equity purchase price. So it's okay just to leave it at the enterprise value, but we're going to show the detail sitting behind that.

So the existing debt in an LBO is definitely going to be refinanced.

It's really a restructuring exercise.

So the old legacy debt is going to have all sorts of covenants and structuring that we don't want to inherit. So we want to wipe the slate clean, and we want to load a load more debt onto this. So we're certainly going to pay that down.

I need to go and find that. So I'm going to say equals, I'm going to say Control and Page Down one, two, three, four times to go to the balance sheet.

And if I look at the balance sheet, I should be able to figure out how much debt they have right now. I'll use my mouse really just for clarity so you can see what I'm doing. So they've got their legacy long-term debt in F21, which is 197.1. We're going to pay that down.

Plus, we've also got the revolving credit facility.

So they currently have 155.4 million revolving credit facility.

I want to pay that down as well. But wait a minute, because they've got 50.1 million of cash on their balance sheet.

I said they'd have a decent amount of cash in the business.

We could use 50.1 million to pay down about a third of their revolver, and then we'd only need to refinance the remainder. So I'm going to do that.

I'm going to deduct from that the cash.

And you might say, well, maybe they should hold some debt back.

It'd be good to have some kind of minimum cash balance for operational purposes.

And that is realistically probably true, but it won't really make a big difference to our IRR calculation. So that feels like a complexity that doesn't really give us a kind of a good return on our extra effort.

So I'm not going to worry about that.

The net debt of the business is 302.4 million.

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

If you've just arrived, in the chat box You're going to find a...

Oh, and again, I think somebody else has arrived.

In the chat box, you're going to find a link to the webpage where you can download the materials that I'm looking at. The file name is here at the very top of Excel.

Okay, so what is the acquisition equity value? If I take the enterprise value and I deduct the net debt, I get an equity value of 1487.1. I might call that the equity purchase price, and here it's called the acquisition equity value. That's going to be pretty useful because we're going to feed that into below. We're going to feed that into our sources of funds.

So the question is, what are you intending to spend your money on? Well, we're going to have to go and buy the shares of the business at our offer price, 1487.1, and we're going to have to refinance the net debt.

Just accepting in the passing that the combination of those two numbers will be the acquisition EV. Nevertheless, we've broken them down. We've also got some fees.

Now, the reality is that the fees are going to be fairly complicated.

So you would certainly have some advisory fees. You'd have some debt issuance fees.

You maybe have other fees that accrue during your due diligence process.

But it probably doesn't make much sense to go to the effort of breaking it down into their constituent parts. I mean, what we could do is we could capitalize the debt fees and amortize them over the life of the debt, but it's just not really worth doing it. So what we're going to do, we do want to represent the fees. If we go to J6, we've got 3% and it says broadly they're the fees for the entire deal.

Let's look at those relative to the enterprise value.

So the enterprise value being reflective of the equity and the debt being raised.

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

I've got fifty-three point seven million, and that will be our total use of funds.

If we scroll down to the right-hand side, we can see our total source of funds, total sources of funds, and these numbers need to balance.

So if I go down to cell G21 and say alt equals, and I just arrow up to the revolver firstly and secondly, junior notes, mezzanine, prefs, common equity. The sum of the financing, the sources of funds needs to equal the uses of funds. And it doesn't at the moment because what we do need to do is put some preference shares in. So if I take the total use of funds and I subtract the sum of all the debt and the common equity, then my plug number will be preference shares.

I get six hundred and twenty-two point two.

But let's just talk a little bit about the financing.

So to buy the business, we're going to take on some debt, and it'll be probably somewhere between about five to six times LTM or historic EBITDA, somewhere around there. And then the remainder is going to be put into the deal as equity.

So the equity is going to be at entry, it's going to be divided by the management and divided by the sponsor.

And the sponsor themself are slightly ahead of the management, albeit subordinate to the debt. And so they do that by issuing preference shares.

What it does is it allows them to lock in some kind of return because the preference shares will accrue dividends over the life of the deal.

But also, it allows them, in the worst case scenario, in liquidation, to be just slightly ahead of the common equity.

Let's look at the debt to EBITDA, because I said it would be about five to six times. If I take the revolver, well, there's not really anything there, and I divide it by the EBITDA. Of course, I don't get anything.

If I go down to the first lien and I could take the first lien debt and divide it by the EBITDA. I think I'm going to lock the EBITDA with F4.

And then if I add that to the prior multiple, I should be able to accumulate this.

And if I copy this all the way down, I get to five point one X.

So say it's quite important that the deal is at seven and a half X.

It's the kind of business that doesn't have a lot of growth and is maybe not very exciting to acquirers. And so it's trading at a low multiple.

You really want the multiple to be close to the debt to EBITDA multiple for it to be a traditional LBO.

In the traditional sense, a traditional LBO is financed with a significant amount of debt. So if you'd identified a business that was trading at, say, 40 times EV to EBITDA, and you could only load on five point one times debt to EBITDA, then most of the acquisition would be funded by equity.

It doesn't mean it couldn't be an attractive deal.

Maybe it would be an attractive deal, but it wouldn't in a traditional sense be an LBO. Okay. So, now we've got the uses and the sources of funds sorted. Let's scroll down.

Below this, we've got something on ownership. We don't really need to do this now.

We could do this further down the line.

So as I mentioned, there is a session that follows this session.

And we won't get the entire model done in this session.

So there's another session coming up.

I think it's in about a week or something like that.

And we will look at-- In that session, we're going to look at equity returns.

So we probably will take a look at this when we get there.

But what I would like to do is at least do the calculation now.

So at entry, let's just add this up.

The institution's going to put some equity in, common equity, and the management's going to put some equity in. And you might say, "Well, why would that be?" In the LBO, we're going to buy this business and we're going to try and turn it around, and the return that we make, the IRR that we generate will depend in part on the speed at which we can achieve that turnaround.

So an ideal situation would be to acquire the business, improve its EBITDA, pay down some of the debt, and offload it, sell it in three years. Now, that is a tall order. That is tricky to do.

So we need to hit the ground running.

The way we're going to do that is we're going to say to the management team, Devon's management team, their senior management will have had stock options.

They'll have ownership in the business.

So we're going to say to them, "We're going to go buy the business, and we're going to buy some of your shares from you." And that obviously will put a smile on their face, but not all of them. We want you to roll some of your equity into the deal. It's called the management roll.

And what that will do is it will keep them in the business, and it will incentivize them to make the business worth a lot more money over the next three years.

So they're basically aligning their interest with that of the sponsor rather than just buying them out and seeing them go.

So the suggestion here is that the management are going to be rolling 10% of their equity, and that means of the common equity, one minus the sum of these numbers. That means that the sponsor's going to put 90% in.

Now, when we think about the source of funds, we are going to try and load as much debt as we can onto the business. And looking at that threshold of three point eight X, we can do more than that. We can load on some mezzanine finance.

So what's that all about? Well, the word mezzanine means in between.

In real estate, go into a fancy office building, you'll find the ground floor or the first floor, depending on if you're from the UK or if you're from the UK or Europe or from the US.

But that initial floor that you walk into.

And then there'll be maybe a floor above that.

But sometimes in between, you have a big balcony.

In a fancy office building, you might look up and there's a very large balcony, and that is referred to as the mezzanine floor.

So a mezzanine in real estate is a kind of between two floors, and it's exactly the same premise in finance.

Mezzanine financing is partly between debt and between equity.

And so what we're going to say to the mezzanine investor or the mezzanine lender is, "If you lend us some principal, we will pay that back at the end." And the interest on that mezzanine financing, typically, we won't pay that in cash through the term, but we'll have that Pay back at the end as well.

But as a further incentive, we will give you some equity.

So you don't have to contribute any equity at entry, but at exit, we will give up some equity. And there are sophisticated ways we could model this if we were really trying to take a deep dive into exactly what the mezzanine returns are.

But when we're looking at the IRR for the deal as a whole, it doesn't really make sense to dive into that in lots of detail.

So what the model's assuming, and it seems like a very sensible way of doing it to me, is that the mezzanine has no equity at entry, but it has 5% at exit, and that 5% is going to be given up by both the institution and the management proportional to their initial investment.

So if I said, well, why don't we say equals open bracket 100% minus 5%, so 95%.

And why don't we take that ninety-five percent and multiply it by the institution's initial investment of ninety percent.

So they get 90% of the ninety-five percent shared between the institution and the management. So they get eighty-five point five percent.

And that means that the management would get at exit a 100% minus 5%. So there's ninety-five percent to share around, and the management are going to get 10% of that.

That means that if we add that up, we still come to our 100%.

So they're going to give up some of their equity and management are going to give up some of their equity. They're going to give it to the mezzanine investor.

But it's worth doing it because it means that less equity needs to go in in the first instance. And when I say it's worth doing it, of course, when we've completed the model, you could easily get rid of that, get rid of that, and then analyze whether or not the deal really is worth doing by bringing the mezzanine financing or perhaps we wouldn't.

Perhaps we don't give up any of our equity at exit, but then we have to put more equity in in the first instance. So we can do that analysis at the end.

Okay, what else do I want to do? Well, I also want to have a look at goodwill here.

So as I mentioned near the beginning, when we look at the financial statements, we're going to consolidate them properly.

We're going to do a proper consolidation here.

And as part of that consolidation, it's going to be necessary to talk about goodwill. So let's go and grab some numbers here for the goodwill. Maybe I'm just going to show the fullness as well for...

No worry. We're good here. It's a little off-screen.

There we go. Okay. So for the goodwill, I'm basically saying, what did we pay for something against the accountant's view of what it's worth? So if I say equals the equity purchase price we have calculated.

It's not the enterprise value; it's the value of the equity that we acquired.

And then we want to compare that to the net identifiable assets.

If I say equals, I'm going to use my mouse for clarity, just so you can see where I'm going. I'm going to go to the balance sheet here, and to get the net assets, we can take the total assets and we can subtract from that the total liabilities, or we can get the shareholders' equity because it's going to be the same number. So the total assets minus the total liability should be eight five three point three, and it is. Now, what we're supposed to do is do a fair value review of the target balance sheet. It isn't required here because we're not concerning ourselves too much with the accounting treatment because it's not a session on accounting, it's a session on LBO modeling. But goodwill is not a nice number.

I would say it's not a particularly nice number.

Maybe that's more of a personal view, but I could say to you, what does it mean? What is it? What do you think goodwill actually is? When you buy a company, if the company you're buying had goodwill on it in its books already, pre-existing goodwill, maybe because it had done some acquisitions in past, what are you really buying there? Well, the answer is nothing, really.

If I bought a company and on its balance sheet, it had PP&E, and I didn't want that PP&E, I could sell that to someone else. We'd talk about the pricing.

I can sell it. If I bought a company and it had inventory on its balance sheet, I didn't want the inventory, I could sell it to someone else.

If I bought a company that had pre-existing goodwill on its balance sheet, if I approached you and said, "I bought this company, but it has goodwill on its balance sheet. I wonder if you'd like to buy it from me." You'd frown at me.

You'd wrinkle up your nose. You'd say, "This is silly. It makes any sense.

There's nothing there. It's just an accounting adjustment." So goodwill is not a particularly nice number. And I guess what would be appropriate would be for us to have more certainty over the size of the goodwill.

And there are only two components we need to think about.

The equity purchase price, the acquisition equity value, and the net assets of the target. Now, I think the equity purchase price is kind of irrefutable.

You are paying... What are we paying? One four eight seven point one.

You're paying that.

Difficult to argue with that. But the assets, now they could be out of date.

What we're supposed to do is we're supposed to look at the balance sheet and line by line, we are supposed to have a look at the assets and the liabilities of the target and say, "Do I agree with these numbers?" The kind of classic thing would be to look at the property, plant, and equipment. So the business may have bought the property, plant, equipment 15 years ago and been systematically depreciating that over 15 years, and the market value of that property, plant, equipment might be quite different to the value sitting in the books. And in that instance, we would step up those assets.

We could also step down assets. We can step up liabilities and step down liabilities. We're supposed to do a fair value review of the entire balance sheet.

And that might mean going back to the LBO sheet.

It might mean that the goodwill moves a bit because these identifiable net assets might be stepped up to their fair value. But we don't have to do that here.

That's not required. Really just because this is not accounting exercise as such.

It's a modeling exercise. So I've got goodwill of six three three point eight.

What does goodwill mean? Well, if you buy a business and the price you pay for it is greater than the assets that you're bringing onto your balance sheet, then we would need goodwill to bridge that gap. And that's exactly what we're about to do.

If I say Control and Page Down one, two, three, four times...

Oh, let's zoom in a bit here. I'm going to get to the balance sheet.

Now, if you look on the right-hand side at column I, it says combo. And we see this description when we're thinking about doing M&A, when we're thinking about consolidating accounts. So you usually have a buyer and a target, and you consolidate them together, and then you do some adjustments.

And that is what we're doing here, except it doesn't really look like that's what we're doing here. We've got the target. Where's the buyer? Well, in an LBO, the buyers be really a special purpose vehicle, SPV, and so the assets and liabilities are going to be zero. It's a shell company.

So we are combining a buyer and a target, but it's just there's nothing in the buyer. They're just zeros. So that means that when we think about the combo, we only really need to grab the target numbers.

And I'm going to pick up some adjustments here as well.

Let me show you the formula there. Okay.

If I copy this all the way down, I'm going to do a small amount of housekeeping here. So my combo column will sum across the target and some adjustments horizontally. And I like that. I think that's great.

But I don't want that for the subtotals.

So when we look at the total current assets, I want to Control R that out.

Okay. I want the total current assets to sum vertically.

I think that would be a reasonable thing to do.

I'm going to do the same with the total assets.

I'm going to just Control R out the total.

I'm going to do the same with the total current liabilities and the total liabilities and the overall total for equity, liabilities and equity I'm going to get rid of these little subtotals in the middle because I find them a little bit fussy. We don't need those in there. Okay.

So all I've done is a small amount of housekeeping.

Okay. I've done a small amount of housekeeping. Yeah.

Right.

Now, if we go down to the very bottom of the combo balance sheet and I look at cell I33, I'm going to color that in green.

We're in a fantastic position because I33, the balance sheet balances.

So the combo balance sheet balances. That's great.

But a very quick review of the numbers confirms that the numbers are not correct, and we need to do a bit of work. So the first line item we're going to get to is the cash. How can we have 50 million of cash? I thought that they were going to use the cash to refinance, to repay some of the revolver, and then they were going to refinance the remaining revolver and the long-term debt. So we have to adjust this.

And come to think of it, we probably need to also get rid of the legacy revolver and the legacy debt and buy out the legacy equity, replace it with a new capital structure. And the new capital structure would be a whole load of additional debt. Let's do that, I think.

We've only got a couple of columns here, so some of the adjustments we might need to put into the same cell. But in terms of the financing, I'm definitely going to take the target cash and multiply it by minus one.

I'm definitely going to take the target's revolver and multiply it by minus one, and I'm definitely going to take the target's debt and multiply it by minus one because that's gone. I'm also going to go down to the target's equity, which we bought out, and I think I'm going to do this in the accounting column, so I'm going to go to the left. I'm going to take the 83.3, and I'm going to multiply that by minus one. Now, the problem is that what we've done now is we've brought together all of the assets and the liabilities of the business.

We've stripped out their existing financing, and suddenly it doesn't really balance. Now, of course, it doesn't balance.

What we need to do is we need to put the new financing in place to make this work.

So the new financing was in the LBO sheet.

I'm going to go to that and just very quickly review it.

So the new financing, we're going to have revolver, first lien, second lien, junior notes, mezzanine pref's and common equity.

That needs to go into the balance sheet.

So Control and page down a few times to get back to the balance sheet.

And still in the financing column, I suppose, for completeness, I should go to the revolver. I should go and have a look at the revolver cell, which is H17.

And I should add to that from the LBO sheet the revolver in the source of funds, except that it is actually zero in this instance.

Should probably also go down to not the legacy debt, but the new debt lines.

So the first lien, second lien, junior notes, mezz pref's, et cetera.

So I'm going to start with first lien. I'm going to sequels back to the LBO sheet.

Again, click on the first lien. I'm appreciating that the debt now runs in order. So it runs below.

So I should be able to just Control D this all the way down. And of course, we've also got equity we're going to issue.

It's not much equity. Previously, the structure was weighted quite heavily in favor of equity. And now the tables are going to turn here.

So the equity we're going to issue again comes from the source of funds.

I can go to the LBO sheet and I've got common equity of 10.

Now, at this point, I feel like it should balance, but it doesn't balance, and we need to think about why. So what we've done is we've brought onto our balance sheet all of the net assets of the target company.

And we've raised some financing to buy those assets.

We've also raised some financing to refinance the target debt.

But they just match off, right? Whatever the target net debt was, 300 and something like that. Whatever, 322, maybe 321.

Whatever the target's net debt was, we raised financing, and they just go together.

Whatever the target's net assets are, we raised financing, and the financing was a lot higher because we paid a premium.

I hesitate to say the word, but the word, of course, is goodwill.

So if we go perhaps in the accounting column and the goodwill row.

If I say equals, I can go back to the LBO sheet and go and grab the goodwill wherever it is. It's here.

So it's 633.8, and it's in LBO!H26.

I'm thinking that when I do this, this should close the gap.

So if I enter, hopefully the balance sheet balances.

And it doesn't balance, and that's annoying. But I know I've seen that number.

And I'm sure you guys have that same feeling deep inside. That 53.7.

We've seen that number. What we've forgotten to account for.

Well, if we go to the LBO sheet and we stare at the uses of funds, you can see that we haven't yet accounted for the fees.

Now, in general, just... I mean, it doesn't have to be in an acquisition, but just in general in accounting, if you have a fee to pay, then you are going to have cash going down, and you're going to expense it.

So you're going to have retained earnings going down.

You're mostly going to expense it.

And we've dealt with the cash element of this already because we've raised the financing to pay for everything, including the fees.

But we haven't got the kind of reduction in the retained earnings.

We haven't got the reduction in the equity from those fees.

So that's what I'm going to do. I'm going to go back to the balance sheet, and I think maybe in the accounting column, in the equity row, I'm going to click on that. I'm going to say minus, and we're going to go back to the LBO sheet and grab those fees. And as soon as we feed those fees into the consolidated balance sheet, it balances.

So that is our complete balance sheet, and that is part of the complexity really of the model.

Okay. What do we need to do now? Well, we're going to want to think about forecasting out the balance sheet. But let's not get ahead of ourselves.

We also need to forecast out the income statement.

So I'm going to go to the income statement, and if we look at the income statement, really just to maintain matrix integrity in the model, we have these blank columns here. So in the balance sheet, we want to make a lot of adjustments for accounting and financing, but there's nothing that we need to adjust for on the income statement. It's just not necessary.

But what we do want is we want the combo column to be in column I because in the balance sheet the combo column is in column I.

We want to make sure we have this matrix integrity and everything aligns.

It would be a sort of bad modeling practice to not do that.

So if we go back to the income statement, what that means is that I really just want to pull all the 2015 numbers and just pull them into column I.

I'm going to show my formulas here. All I'm going to do, Alt equals.

I'm going to grab the historic number, and I'm just going to grab the accounting and financing adjustments. But that's just for consistency really because there are no accounting and financing adjustments here.

What it means is I can copy this all the way down, and I'm just going to reinstate the subtotals. This is exactly what I did on the balance sheet.

I'm just going to Control R out the subtotals.

Like so.

Okay. I'm not usually a fan of having the subtotals in the middle.

I just think it's a bit messy. So I'm going to delete those and just tidy this up a bit in terms of my formula text.

Okay. So nothing fancy going on there at all.

We just really moved the 2015 number across to the combo sheet.

What I now want to do is I want to do a bit of forecasting here.

We're going to forecast out the income statement.

I really, really want to get the income statement done.

And if we do anything on the balance sheet, I would say that would be a bonus.

Okay. So In cell J5, column J, cell J5, we have the projected sales number, and this is not going to feel any different to just doing a regular three-statement model.

I'm going to say equals open bracket one plus.

I'm going to say Control and Page Up to go to the input sheet, and I'm going to go and grab the sales growth. It's pretty low, one point three percent sales growth in J11. Close bracket, multiply by.

Let's go back, so Control and Page Down a couple of times to the income statement, arrow out. Okay, I've got two three five two point nine would be the forecast sales number. I want to do the same for the cost of sales, which is probably in J12, but I'm going to go and find it. So if I say equals, Control and Page Up.

If you look at the input sheet and you look in cell J12, it says eighty-seven point three percent.

And if you look at the heading, it says cost of sales.

It says cost of sales as a percentage of sales.

That seems like a reasonable way to model that.

If the sales was going to go up, then our cost of sales would be dragged along with it. If our sales go down, then our cost of sales would reduce accordingly.

I'm going to grab that number. I'm going to multiply it by Control and Page Down a couple of times, and I'm going to arrow up to the sales, the revenue. This all seems okay.

For gross profit, I can copy out to the right.

Now, there is a little bit of work to do on SG&A. So when we buy this business, we're going to want to increase EBITDA, and there are a few ways we can do that. One of the things we would likely look at would be cost savings, but probably not COGS. So COGS costs relate to production.

They relate to supply chains and the kind of things that, although there might be cost savings there, they can be painful to achieve, and they can take quite a while to kind of dismantle your supply chain, dismantle your production process, and rebuild it in different ways. And that is not consistent with the idea that for the LBO, we want to get in and out reasonably quickly.

However, SG&A is an area we might think about making cost savings.

I'm not going to build it just yet. I'm going to say Control and Page Down.

I'm going to go to the input sheet, and on the input sheet, we've got at the very top, it says management case, and then it says seven percent and six percent.

And if you look to the side, it says SG&A as a percentage of sales.

Now, what is going on here? Let's grab a stylus.

If you look at SG&A as a percentage of sales in the assumptions, they've been a bit noisy. We've had five point six percent, six point two percent, six point four percent, and then seven percent. Now, the management, sorry, the bank being relatively pessimistic, the bank are going to work on the basis that our SG&A is going to continue at seven percent indefinitely, okay, across the forecast.

Whereas the management take a much more optimistic approach to this, I suppose. Maybe they would, and they assume that it might get to six percent. It would be really nice if we could somehow sensitize the model for different SG&A numbers because there's going to be a bit of uncertainty there.

So if I go down to... I'm going to highlight this in yellow.

Highlight this in yellow, sorry. I go down to row 15. This has been built for us already, but I'm going to delete this, and then I'm going to build it up from scratch.

So if we want to look at the SG&A as a percentage of sales, we've got two options.

It could be seven percent in green, or it could be six percent with a red circle around it. And to be able to sensitize between the two, I'm going to use the choose function. Now, the choose function is going to need a switch.

So just above really the input numbers here in cell J5, I'm just going to put a one in there.

Let's color that in yellow just to draw our eye to that. I'm going to put a one.

Now, if I go back down to row 15, I'm going to say equals C-H-O, and Excel says, "Hey, Jonathan, this chooses a value or action to perform from a list of values based on an index number." Basically, Excel is saying, "Give me a list, tell me which item the list you want, and I'll return that for you." So what I'm going to do is I'm going to hit Tab, which will complete the function and open the bracket.

And in the syntax, Excel says, "Jonathan, give me an index number." I'm going to arrow up to cell J5, which has got a one in it.

So it's going to choose the first item of whatever list I give it.

And then if I hit comma, it says value one. In open square brackets, value two.

In square brackets, value three, et cetera. They're optional.

You, at the very least, need to give it one value, but you could give it more than one, and we're going to give it two values.

So I'm going to arrow up to the seven percent, comma, and arrow up to the six percent, and then I'm going to close the bracket and hit Enter.

And it doesn't do anything that radical.

I've got one selected in yellow at the top here, so it chooses the first item in the list. If I change it to a two, it chooses the second item in the list.

So it's not too radical. But what would be really nice is, below where it says in the cell, it says management case, it'd be really nice to be able to click that and select bank case or management case and kind of have it like change the choose function, and we can do that, but we need to use another function, and the function we're going to use in the yellow cell above is match. So what the match function will do is if you give it a list of characters, doesn't have to be numbers, but if you give it a list and you say, "Go and look for this item in that list," it will tell you where it appears.

If it's the first item, the second item, the third item, et cetera.

So I'm going to go into J5. I'm going to say equals M-A-T, for match, and I'm going to hit Tab. And Excel says, "Okay, Jonathan, give me a lookup value." Like, what am I looking for? Well, why don't you...

If I go down to the cell below, why don't you look for bank case, comma. And then in bold, Excel says, "Where's my lookup array?" Array is just a range. Like where am I supposed to be looking for that? I can look for bank case, but where? And so I'm going to go and click on B7 to B8. So go and look in B7 to B8. I will press F4 because I kind of always want it to look at that list. So go and find bank case in that list.

Now, if I press comma, finally it says, "Do you want it to be less than or greater than?" Don't be silly. Not for a text string.

Maybe for a number, but not for the data I've got here.

Of course, I want it to be an exact match, so I'm going to put a zero in and I'm going to close the bracket. Now it's going to go and look for bank case in that list. I would say that's the first item in the list, so it's going to return a one.

If it returned one in this cell, then choose we'll look at the options it's got, the bank case and the management case, and it's going to choose that first item. If you keep your eye on the SG&A as a percentage of sales, it means if I go further up and I click that drop-down and change from bank case to management case, it means that it selects the appropriate number down here.

For the most part, I really like this as an approach, but I think we can make this a little tidier. In the choose function it references cell J5. And why don't I go into J5 and grab the entire function I've written out there, not including the equal sign, and copy it. You can right-click on that to copy it or just Control + C and then press Escape to come out of that.

Why don't I now go down to the choose function and instead of referencing J5, why don't we just paste the J5 formula in there, Control and V to paste that in there.

Now what that means is we can now entirely do away with that cell above, and this will still work because we've nested one formula into the other. We can go to the management case and click on the drop-down and go to bank case, and it will return seven percent.

Back to management case, it will return six percent.

I'm going to leave it at management case.

But if you did want to change it without using your mouse, you could hold down the left Alt key and tap arrow down Hold down the left Alt key and tap arrow down.

Let go of everything, and it brings up that list, and you can select the case that you want. But just to be absolutely clear, I'm going to leave this at management case. Now, if I've locked these things appropriately, we should be able to Control-R this out, and I'd say that looks pretty good.

Let's go back. So if we go back to the income statement, we can now build the SG&A. I can say equals, I can go Control and Page Up, I can go back to the input sheet and go and grab the SG&A's percentage of sales row, which is subject to sensitization, so it can be dynamic.

It's not a fixed number necessarily.

I'm going to multiply that by the income statement sales, and I get the SG&A number. I've got 141.2, depending on what case you've selected.

EBIT, I can just copy that out to the right.

Now I need to get depreciation and amortization to come down to EBITDA.

So for the depreciation, I'm going to need to build a quick base analysis.

Control and Page Up to go to the base analysis.

And if we look at rows six, seven, eight, and nine, just color those in, we've got the base analysis for PP&E.

What I'd like to do is I'd like to say...

I think I'm going to do the whole working, so I'd like to say, what is the ending PP&E in projected year? I don't know. But I think maybe a good place to start would be the beginning amount. And I do know that, because if I go down to row nine and column I, I can get the historic PP&E number.

I'm going to say equals, I'm going to go to the balance sheet.

I'm going to do it with my mouse for clarity, and then I'm going to scroll around a bit, and I'm going to find in I11 the property, plant, and equipment, and let's hit Enter.

I'll move that up diagonally.

And now we need to deal with the CapEx. So for the CapEx, I can say equals.

There has to be an assumption here.

Control and Page Up to go to the input sheet, and I can see in row 22, in fact in J22, input !J22, I can see 3.4%. And then ahead is Jonathan.

This is the CapEx relative to the sales. A very sensible way of modeling this.

So multiply by Control and Page Down to the income statement.

We're going to go and grab the sales. So it's income statement !J5.

Gives me the CapEx. Now the depreciation. I'm going to say equals.

Again, I'm going to go to the input sheet.

So Control and Page Up to go to the input sheet.

I can see in row 13, I can see a depreciation assumption.

It says depreciation as a percentage of last year, only just off slightly off the column there and obscured, but it will be as a percentage of last year's property, plant, and equipment, beginning property, plant, and equipment.

So I'm going to grab that 10.9%. I'm going to multiply that out by the beginning PP&E. That requires me to go back to the calc sheet and just arrow up a couple of cells to go and grab that beginning PP&E.

It looks like the CapEx is slightly ahead of the depreciation, so I'd say that business is growing, although not very aggressively.

The ending property, plant, and equipment will be the sum of those numbers.

And just for clarity, let's show the formulas there. Okay, great. So now I've got the depreciation.

What I can do is Control and Page Down. I can go to the income statement.

I could, in the depreciation cell, which is J12, I can say equals, and I can go back and grab from the calc sheet the depreciation number.

I also want the amortization. And I noticed that there wasn't a base analysis there because it looks like they haven't really had much amortization in the last few years. They did have some three years ago, but we're thinking that the intangible assets they have are for the most part not subject to any amortization.

So it's not something we're going to worry about too much.

There is an assumption there in case we change our mind.

I'm going to say equals, Control and Page Up to go to the input sheet, and we've got amortization amount. I noticed that historically, although these are zeros, it's difficult to know if this is going to be a positive or a negative.

When they had amortization previously, it was shown as a negative number.

So I presume anyone that was going to forecast it would include it as a negative number to be consistent. So I'm not going to multiply it by minus one.

I'm going to just go and grab input J4.

Now we can Control-R out the total for EBITDA, and we can think about improvements.

So when we do the deal, we need to uplift EBITDA.

We partly talked about that through SG&A, but there might be other EBITDA improvements that are available to us.

And I did see an assumption for this earlier.

I'm not sure if you've noticed it, but if I say equals, I'll use my mouse.

If we go back to the LBO sheet here, if I click on the LBO sheet here, and if we scroll up towards the top, I've got in cell LBO! F11, I've got 2%, and that is EBITDA improvement as a percentage of sales. I am going to copy this out to the right at some point, so I'm going to lock this with F4, and I'm going to multiply this by the sales number.

So that means going back to the income statement and going to click on the sales.

We'll leave that as a positive. One thing I would say is this is pretty bullish.

I mean, Debenhams would have to be pretty inefficient.

If you look at the EBITDA improvement, the EBITDA improvement represents about 20.3% of the EBITDA. That is a lot. I mean, that is quite a big improvement that they're going to make.

But we're going to go with that. I mean, there's an argument that they probably wouldn't hit that full improvement in the first year, and it would phase in over a few years, so maybe we could build some more detail into this.

But let's not not there at the moment. Now I've got adjusted EBITDA.

I might not use adjusted EBITDA for anything right now in this session.

But when we come back and we do more work, at exit, it's the exit multiple multiplied by the adjusted EBITDA that I want.

So this is going to be a very, very important number to us.

We now want the interest expense. I wish I could do the interest expense now, but I've got no prospect whatsoever of being able to do that because we need to do the debt schedule, which we're going to do in the next session.

So I can copy out income before tax. I can calculate the tax expense.

Equals, I need a tax rate. We go back to the input sheet. There is a tax rate.

The effective tax rate is given in J16. It's 20%.

I'm going to multiply that by the earnings. Income before tax it's called here.

Okay, seems like a big jump actually, but then I guess we haven't got the interest in here yet. Once the interest is in here, then these numbers will reduce.

Yeah. Okay. And then we can copy out net income.

Now, what I really wanted to do, we've got five minutes left, but what I really wanted to do was to get the income statement done today, and we've got a bit of time left. So why don't we just go that little bit further as a bit of a bonus.

Control and Page Down. We're going to go onto the balance sheet.

We have kind of built the balance sheet for the combo column, but I think we can just take a little bit of time. We wouldn't do all of it.

We wouldn't be able to do all the balance sheets because we haven't done the debt schedule yet. But on the assets, we can just do a little bit on the assets.

I can't do the cash because we haven't yet done the cash flow statement, so that's got to wait till next time. But the accounts receivable, maybe I can do the accounts receivable. If I say equals, Control and Page Up to go to the input sheet, there is an assumption here for receivable days.

Now if you think about how receivable days is calculated, receivable days would be accounts receivable divided by sales multiplied by 365.

And what we want to do is reverse that. So we've got the days already.

So if we reverse that, we're going to multiply it by the sales and divide by 365. Let's do that. So if we take the 3.9, I'm going to multiply it by the sales on the income statement, and I'm going to divide it by 365.

And I get 12. That looks reasonable. I'm going to do the same with the inventories.

So I'm going to say equals, Control and Page Up.

I'm going to go to the assumption sheet, the input sheet.

I'm going to grab the inventories. Now they're usually calculated relative to COGS.

So you take inventories divided by COGS times by 365. Let's rearrange that.

Let's take the inventory days and multiply it by COGS and divide it by 365.

So I'm going to multiply that out by the COGS, and I'm going to divide it by 365. And again, I get a reasonable number.

It's a very much an inventory-heavy business, big retailer.

And then other current assets, maybe we can just get this done.

So for the other current assets, I could say Control and Page Up, and on the input sheet it says other current assets as a percentage of.

But I'll say to me that's slightly cut off, but I'm pretty confident it would be as a percentage of sales. So I'm going to multiply that out by Control and Page Down on the income statement, the sales number. That looks reasonable. That gives me the total current assets.

We could leave it there, but we have got the PP&E already.

Just I can't help myself. I'm going to go to the calculation sheet and go and grab... We've got a couple of minutes left. I'm going to go and grab the PP&E.

The intangible assets are going to be the same as last year.

There is a little bit of amortization, so perhaps we should add, if I go to the income statement, the amortization on the income statement because it would come through negatively. The other long-term assets, there would be an assumption for that on the input sheet, and actually 76.1 million. And the goodwill is going to be the same as the prior year.

Goodwill will definitely go up if they acquire new companies.

But we don't have any meaningful basis to forecast what that might look like.

And it would go down if they were going to impair their goodwill.

But who can forecast an impairment? So that gives us our total assets. Right.

I would say we should leave it there because we can't really do much more on the balance sheet. And if you think about it, I don't know what the revolver's going to look like until I've done the debt schedule.

I don't know what the other debt items are going to look like until I've done the debt schedule. We have got another session, and in the other session, we're going to pick up where we've left off. So in the other session, we're going to build the cash flow statement, build the debt schedule, complete the balance sheet, and go and do the analysis. Guys, I hope that was useful.

Thanks ever so much. I can't believe how many people we've got on the session.

Thanks so much for everyone dialing in. Really great to have big numbers.

It's Friday. Have a fantastic remainder of the day, and I look forward to seeing you guys on the next one. Okay.