So we're going to be talking about LBO.

The session is LBO: The Exit and Analysis. This session does follow a session that we ran previously. If you didn't attend that session, then you're not really disadvantaged. We've got a part complete version of the model that we're going to pick up. So, it's not essential that you attended that session.

But if you did attend that session, this is a continuation of what we have previously looked at. The idea is that we're going to focus on, I guess, working from the end and working backwards.

We're going to focus on the IRR analysis for different sorts of investors. We've got institutional investors, we've got mezzanine, and we've got management.

In order to do that, we need to have built the debt schedule, and in order to have built the debt schedule, we need to have built the cash flow statement. So that's the plan for the session.

If you scroll down to the bottom here, there are some downloads, and there's a download called Debenhams Complex LBO Basic Version Part Complete. We've got quite a lot of content to squeeze into an hour, and so I'm going to get going fairly soon. Okay, I'm going to jump into the Excel now. We'll have a very quick chat about it.

Some people may not have attended the first session.

It's about Debenhams. Debenhams is a now failed UK department store. It was quite a big name on the high street, bricks and mortar retailer, did fashion and homeware.

It probably wasn't terribly well run.

It was, I would imagine, it would be reasonable to say it was quite inefficient.

But it was also the sort of business that was in a mature market, had a lot of assets, and could probably support a decent amount of debt.

It seemed like it could be a good LBO target, and that's what this model seeks to test. With the model in the previous session, we built the LBO sheet.

We didn't build the equity returns.

We built the input sheet. There's nothing in the input sheet for us to look at, but I'm just going to draw to your attention that there is a circular switch in the input sheet that is on at the moment, and I'm going to change that to a zero. So we're going to turn that off right now.

And there's a calculation sheet, which is complete.

There's an income statement sheet, which is complete, and there's a balance sheet, which is complete. And you'd be forgiven for thinking, "Hang on a minute, Jonathan.

It looks like everything here is complete." Well, that is not the case.

We've got the cash flow statement to do, and we've got the debt schedule to do, and we've got to go back on the LBO sheet and do the equity returns.

And in an hour, that is quite tight. We've got a fair amount to do.

I know that we've had a few people that have just arrived.

So if you've just arrived, I'm going to give you a link to the materials. I ran this session earlier on today, and I had some advice that just a couple of people out of many people in the session could not access, could not download the materials.

If that's the case, I've just attached the file to the chat now.

Okay.

If you open up the file, then you'll be all good to go.

The only small thing I've done, if you go to the sheet called Input, there's a circular switch. Why don't I color that in yellow? There's a circular switch. Let's have this set off while we're building the model. Okay? This session follows a previous session.

If you didn't attend the previous session, you're not disadvantaged.

Honestly, it's not going to be a big issue.

If you did attend the previous session, welcome back.

Good to have you back in the room. Okay, Control and Page Down a few times. I'm going to go to the cash flow statement, and I'm interested in forecasting out column J, which would be the projected year, the first projected year.

I am going to show some formulas next to the work that we're doing, and I'm going to zoom in a little bit just so it's a bit easier to see what I'm doing on my screen share. Okay.

We would typically start the cash flow statement with net income, and that would be fine here, but it's not the design choice taken by one of my colleagues when they built the model. They decided to start with EBITDA.

There's actually a good reason for that.

In the model, we have a bunch of debt that has cash interest, and that means that we're going to have an interest expense in the income statement, and that's going to be cash flowing out as well, so no issue there. We're also going to have some PIK interest, payment in kind interest. It's an interest expense in the income statement, but it isn't a cash outflow. If we started the model here, if we started the cash flow statement with net income, we would need to add back the non-cash interest expense. Instead of doing that, my colleague has decided to start with EBITDA and then introduce only the cash interest expense. It's probably a slightly nicer design choice, I think. So quite happy with that.

I'm going to go into cell J5. I'm going to say equals Control and Page up to go to the income statement. On the income statement, I'm looking for the EBITDA number. Just take note that there are two EBITDA numbers.

There's 231.3, which is in row 14, but I'm not going to grab that. I'm going to grab 278.3, which is the adjusted EBITDA in cell income statement J17. That picks up the cost savings from the LBO. So we definitely want to go and grab that number.

The formulas are on the screen. I'd love to do the cash interest now, but I can't do that, unfortunately, because we haven't built the debt schedule.

I can go and grab the interest expense from the income statement.

So I'm really just going to grab things from the income statement.

On the income statement in cell J22 I've got 40.9, which is the tax expense.

Going to grab that.

And then we need to grab the movement in other operating assets and liabilities. We're going to go and grab these movements in other long-term assets and liabilities.

If they're other, by definition they are ambiguous, it is reasonable that someone building the model would flatline these numbers on the balance sheet, so there'd be no change.

This might be a shortcut, but I'd be quite happy to completely leave these out. However, when the model was built, these have been left in.

So I'm going to go and grab these numbers, but there'll be an underwhelming zero. So if I'm in J8 on the cash flow statement, I'm going to look for the other long-term assets, equals control and page up to go to the balance sheet, and I am going to grab the other long-term assets, which are in I13, which is the prior year, minus balance sheet, exclamation mark, J13. So it's an asset. So if assets go up, cash goes down, so we grab the historic number and subtract our projected number.

But these numbers are the same. You can see that on the screen.

They're both 76.1, so we don't have any movement there. And it'll be the same story for the liabilities on the cash flow statement. Let's go and grab those.

The other long-term liabilities, I'm going to invert this because if liabilities go up, cash goes up.

So I'm going to grab J27, and I'm going to subtract from that balance sheet, exclamation mark, I27. But we don't get any movement here.

I definitely want to go and get the change in operating working capital. It's a retailer.

They're going to have loads of inventory.

But it was a big, prominent retailer. They should have really good supplier relationships. The accounts payable should be so large that their operating working capital should be negative.

Let's see how well this business was really run.

If I say equals, I'm going to say control and page up to go to the calculation sheet. And in the calculation sheet, oh, dear, if you look at cell I23, you can see they had 82.9 million of positive operating working capital. That's problematic. They've not been aggressive enough with their suppliers. Walmart would not do that.

Tesco would not do that. Amazon would not do that.

For example, Carrefour would not do that.

So they didn't put themselves in a great position here.

That means that any expansion of the business, unfortunately, is going to suck cash out. What we need to do is take I23 and deduct J23. If that asset is getting slightly larger, asset goes up, cash comes down.

Now, we've got a small negative cash flow there.

The operating cash flow, alt and equals.

I'm just going to make sure we start with the EBITDA and we capture all those numbers. I've got 235.9.

Going to go and grab the CapEx now. I think if I say equals, it's back to the calc sheet. So control and page up to the calc sheet.

And if we go to the PP&E calculation, I can see we've got the CapEx is 80 million.

That's in J7. I'm going to multiply that by minus one.

We've got cash flow from investing here. Alt, equals, equals. It's just 80 million.

Right. The financing cash flow is not going to be very exciting because we haven't done the debt schedule, but we probably should complete this so that when the debt schedule's complete, all the numbers flow through properly.

So I'm going to, for the revolver, I'm going to say equals, I'm going to say control and page up to go to the balance sheet.

And for the debt, I am going to grab the projected debt in balance sheet, exclamation mark J21. And I'm going to subtract from that because this is clearly a liability. Oh, was it revolver? Sorry, it was revolver. Equals, control and page down.

Same principle, different line. Let me go and get the revolver, which is balance sheet, exclamation mark J17 in the projected year, and let's subtract balance sheet, exclamation mark I17 for the revolver.

Now we'll go down and get the debt. So if I say equals, exactly the same principle.

Let's go to the balance sheet. Let's go and grab for the debt.

Balance sheet, exclamation mark J21, minus balance sheet, exclamation mark I21. Now, before I do anything, I'm just going to make a couple of observations.

One, this debt line is kind of irrelevant because it is just a legacy number. This is the debt that Debenhams had in place before we thought about doing the LBO.

Now, an LBO is just a restructuring exercise, and we don't want to mix oil and water. So we don't want to leave the existing debt on the balance sheet and try and mix in the new debt. That won't work.

We want to get rid of the old debt and put new debt in.

So you'll notice that the numbers flatlined here.

And it will always be flatlined here.

Also noticing that below this we've got, looking at the headings, first lien, second lien, junior notes, mezzanine, and preference shares.

I'm going to hit enter. I'm going to copy this down, down through the balance sheet, and I've got first lien, second lien, junior notes.

I don't have mezzanine and preference shares on the cash flow statement.

And you may well have noticed this already, but if you haven't noticed it, I want to draw it to attention. The mezzanine financing is going to move. The preference shares are going to move because the interest on the mezzanine financing is paid in kind. It's PIC.

That means it rolls up into the mezzanine debt.

On the balance sheet, the mezzanine debt is going to get bigger.

On the cash flow statement, if we went to the balance sheet and picked this year's mezzanine debt and deducted last year's mezzanine debt, the cash flow statement would assume that the mezzanine debt was going up, and so we had a cash inflow.

Actually, that's not true. Mezzanine debt's going up because we've got interest that's accruing up into the loan.

So we don't want to include it in the cash flow statement because it's PIC Same principle applies to the preference shares.

The dividends on those preference shares are paid in kind.

The preference shares will increase in size over the term. They will accrete, and we don't want to capture that movement in the value, in the cash flow statement because it isn't a cash flow.

Control D. Let's copy this down. We've got some big movements here at the moment, but these are not the final numbers.

So it looks like first lien and second lien debt is being paid to zero. Of course it isn't. We've got a huge cash outflow.

We're just going to complete this anyway.

So the cash flow from financing is 911.0. The beginning cash is the same as the ending cash from the previous period.

Why would that be zero? Well, I partly mentioned this before, but Debenhams has some legacy debt on its balance sheet, and we're going to want to refinance that.

So you're going to take their existing long-term debt and refinance it, their existing revolver, and refinance that.

But actually, it probably makes sense to take the cash off of their balance sheet as far as we can to pay down their existing debt, and then any surplus we'll be refinancing.

So their cash balance will go to zero.

You might say, "Well, I think they probably need some minimum cash." And I agree with that. I think in the model, we could have built in a minimum cash balance.

My colleague who designed the model didn't do it.

I think that's fine, actually. I think honestly, whether we build in a minimum cash or not, it's not materially going to move the IRR.

So I think this is absolutely fine. This makes sense.

Let's go grab the cash flow. The cash flow would be the sum of the operating cash flow, the investing cash flow, and the financing cash flow. And that means that the ending cash by, say, Alt equals, and I grab the two numbers above, is 755.1.

I know it's a negative number, but the debt schedule will sort this out. It'll be either a zero or ultimately, at some point, it might be a positive number. The debt schedule won't let it go negative because the debt schedule is going to issue revolver if it looks like we're running out of cash.

So what I'm going to do is I'm just going to...

I'm pretty sure this is in there already, but I'm just going to go back to the balance sheet and just observe that 755.1 is flowing into the balance sheet.

Does the balance sheet balance? No, it doesn't.

But that's only because we don't yet have the mezzanine pref's built in. When the mezzanine and pref's are built into the debt schedule, then these things will balance.

The mezzanine and pref's are going to go up, right? So they're actually not going to be 300 and 622.

They're going to go up with their PIK interest and dividends, respectively. So actually, once those accreted values enter the balance sheet, it's actually still not going to balance. So we need to make sure that the other side of those transactions happen. So we need to make sure that the expenses appear in the income statement, and in retained earnings in respect to the dividends. It's a little bit technical.

There's a little bit of accounting underpinning this.

We'll talk about that shortly when we get there. Okay.

Let's say Control and Page Down. I think I'm pretty happy with the cash flow statement. I think what I might do shortly is copy it out to the right.

But thinking about it, we should probably get the interest in first.

It probably makes sense to do that.

So let's say Control and Page Down and go to the debt schedule.

Some of the debt schedule's been completed for us already.

So if you look towards the top of the debt schedule, we've got some cash flow numbers, and these flow from the cash flow statement that we've just built. So, in the cash flow statement we've just built, we've got beginning cash balance, which is zero.

We've got cash from operations, and we've got investing cash flow.

And that leaves us with 155.9 million of cash that we could use to pay down debt.

Those payments in the first instance would be mandatory payments, and we're going to do a bit of work on those.

We haven't built that yet. So what I'm going to do is go down to the revolver.

Now, it's a little, I don't know, I was going to say frustrating.

Is it frustrating? I don't know, maybe it's a little frustrating, but we don't have any numbers here. So we're going to build the debt schedule, but in the first thing we're looking at is the revolver, and it was zero.

So the beginning revolver is going to be the same as the ending revolver in the previous period. The revolver issuance or repayment, I'm going to use a min function here.

So I'm going to say equals min.

So I use the minimum of the cash flow for the revolver, comma, and the beginning revolver, close bracket.

I think I'm going to multiply that by minus one, and then the ending revolver should just add these up.

It's always difficult dealing with zeros, so I'm going to do a very small amount of stress testing. Imagine that the beginning revolver was 100 million.

The revolver of the 155.9 million available, it should pay that 100 down, and it does. If the revolver balance was 200 million, then it won't be able to pay that off.

But it will be able to pay off up to, no more than 155.9 million, and that seems to work. Okay, very good. Let's just put that number back.

Quite happy with how that was built.

I want to calculate the interest expense, although we're not going to have any interest expense here because we don't at the moment have a revolver.

But perhaps in future years, if we were using the revolver, then an interest expense would flow through, and we'd want to model that properly.

So I'm going to say equals. For anyone that was here in the previous session, it was about a week ago, I think.

You may remember that on the LBO sheet over here, if I wave my cursor around furiously. On the LBO sheet, there is the sources of funds. And next to each source of funds, we have the appropriate rate, the appropriate interest rate.

So if I say Control and Page Up a few times to go to the LBO sheet.

If we scroll towards the top Ish. And on the right-hand side here, if you look at LBO!K14, you can see it says rate of 5.7%. That is the rate for the revolver.

I'm going to grab that. I'm always going to want to look at that.

When we copy this out to the right, I always want it to look at that cell.

So I'm going to press F4 to lock that, and I'm going to multiply it by AVER, average.

If you part type a function, you can press Tab on the keyboard to complete the function. And now I'm going to go back to the debt schedule, so Control and Page Down a number of times to get to the debt schedule.

And I'm going to capture the average of debt!I25:J25 ) . And Enter. Just noting that the interest rate is an LBO!K14, and they all run in sequence. So the first lien will be in K15 on the LBO sheet. The second lien will be in K16, et cetera. I'm going to try and log that in my memory because I don't want to use my arrow keys to go and find those numbers particularly. Okay. Let's scroll down. As a kind of running total, I might ask myself, "Jonathan, how much cash do we have available for our first lien?" It's slightly off the page there, but first lien acceleration. Well, we had 155.9 million, and of that 155.9 million, we have paid zero towards our revolver, so we still have 155.9 million.

Okay. Let's go down to our first lien.

The beginning first lien is 400, which comes from our sources of funds. We've actually got a mandatory repayment here.

Now, if I say equals, and I'm going to scroll further up the sheet here, we've got a mandatory repayment of 12.4%.

If you look across these mandatory repayments, the sum of these mandatory repayments is 100%. So in fact, they're all relative to the beginning balance, which I think we just saw was 400.

I'm going to grab that 12.4% and I'm going to multiply it by the 400 beginning balance. I always want it to look at that year zero balance. So I'm going to press F4 on that, and I'm going to multiply it by minus one because I think I'd like it to come through as a negative number. Now, what I've done here is almost correct, but it's not correct, and I want to show why. If we had historically paid down some of the balance, some of the loan, so if we have a number of periods that we're going to forecast and maybe we progressively pay this down, it means that we won't be making those mandatory payments per the schedule.

So imagine that the beginning balance was only 40, not 400.

We are not going to make a mandatory repayment of 49.6, even though that's what the schedule's telling us.

So we need to be slightly smarter here.

What I'm going to do is I'm going to edit this and I'm going to say to Excel, choose the minimum of the mandatory repayment, I'm going to forget the negative for the time being, comma, and the beginning balance ) .

Now, I want the whole thing to be negative, so I'm going to multiply that by minus one. That means that if the beginning balance is 400, it'll make the scheduled repayment as scheduled.

But if the beginning balance is only 40, it's not going to be able to make that full scheduled repayment because then it would make the debt negative.

That doesn't make any sense. So it'll only pay up to the maximum for the beginning balance. Let's just put that back. I think that makes sense.

Now, what I would note is if I just grab my stylus, is that having calculated that mandatory repayment, that flows up the top here. That means that is already accounted for in the 106 cash flow for the revolver, which means it's already accounted for in the cash flow available for the accelerated repayments.

So it is in the numbers already. Now we've built that.

Let's think about the accelerated repayment.

Again, let me try and simplify it, and then I'll add in a little bit of necessary complexity. So I'm going to say to Excel, choose the minimum of the cash available for the accelerated repayment, comma, and the beginning balance ) , multiplied by minus one. That doesn't look terribly wrong.

If I add that up, that looks fairly reasonable.

But what if the beginning balance was maybe 59.6? If the beginning balance had already been paid down and was 59.6, then we're going to make a mandatory repayment of 49.6, but we're not going to make an accelerated repayment of 59.6 because we've only got 10 outstanding after the mandatory repayment. What I need to do is for the accelerated repayment, I need to look at the minimum of the cash available, and also I need a kind of running total for the beginning balance.

I need to take that beginning balance, and I need to net off the mandatory repayment, because that is the amount that's outstanding.

So that small tweak is necessary. And in this instance, it would just pay down 10. That's because I've stress-tested the numbers.

So I'm going to put the number back to 400, and we get back to the numbers we need for our analysis.

I want to now pick up the interest rate.

Now, given that the interest rate for the revolver was an LBO!K14, it's not unreasonable for me to say equals LBO$K$I think 15 for the next tranche of debt.

And I'm going to multiply that by the average balance.

I've got 18.4.

Now, what have we got for the cash available for the second lien? Well, we had cash for the accelerated repayment of 106.3. I'm going to deduct from that, or net off from that, the accelerated repayment of 106.3.

I don't need to, at this point, deduct the mandatory repayment because that's already been deducted. It's already baked into that number, and I wouldn't want to double count that Okay.

Let's keep going. Now, the good news is the second lien is really very similar to the first lien mechanically.

So the beginning balance is going to move up diagonally. It's going to be 511.

The mandatory repayment, if you look at how I did this previously, I'm going to choose the minimum of the mandatory repayment assumption of zero, multiplied by the year zero balance of the debt.

So I'm going to press F4 to lock.

So I'm going to choose the minimum of that number, comma, and the beginning balance. Close bracket, multiply by minus one.

Just a reminder, what I'm testing here is I'm saying, "Go and make that mandatory repayment," unless of course, the beginning balance has already been paid down.

If I hit Enter, nothing comes through, but if we wanted to stress test this, we can go up to the assumption and change the payment to, say, 100%, which is a ridiculous number, but will work for our purposes. So yep, if it was 100% repayment, it would repay it in that year. Unless, of course, we only had 200 million as the beginning balance, and then it would only repay 200 million.

That would override the mandatory repayment as scheduled.

Okay, let's just put these numbers back.

Going to put that mandatory repayment back to 0% as scheduled. What about the accelerated repayment? We're not going to make any, and that is not surprising at all because, in the LBO, the most senior debt at the top of the stack needs to be paid down in full before we can start distributing cash elsewhere.

I'm going to say equals choose the minimum of the cash available for the second lien, comma, against the beginning balance, plus what would be a negative mandatory repayment.

Close bracket, multiply by minus one.

The formula I've built here is exactly the same as the formula I built for the first lien, so I'm not going to dwell on that.

If I add this up, it gives me the ending balance, which is exactly the same, and the interest expense is going to be LBO$K$, I think 16 would be the right reference here, 16.

Not close bracket, multiplied by AVER, Tab, and I'm going to take the average of the numbers above.

I've got 49.6 as my number.

Hopefully, this all makes sense, and this all looks good.

I'm choosing to show these as positive numbers, and we'll see if that is consistent with how the income statement should look. We'll figure that out in a minute. Okay.

I'm going to go down to the junior notes.

I'm going to do actually the same kind of thing.

The junior notes are actually zero here.

For the repayment, this is the mandatory repayment.

There is no accelerated repayment.

If you've got those first and second lien notes, they're term loan, but debt amounts, debt balances there.

There'll be loans, like private debt, and so you'll be dealing with a financial institution, and you'll be able to accelerate the repayment of those.

But with notes, I'm selling notes to thousands of different investors, and it's not like I can start paying some of it off. It's a kind of all or nothing scenario we'll assume here. So there is a repayment, which is the mandatory repayment. I'm going to go and grab that.

That's 0%, and I'm going to multiply that by the beginning balance, which I'm going to lock using F4, and I'm going to multiply by minus one.

There's no need to use the min function here because there's no accelerated repayment, so it'll only get paid down as scheduled.

So I think we're probably good with that. And then I'm going to say...

Oh, equals, equals. But it's a bit of a non-event because there's nothing going on here. There's no balance there. If we did have something sitting in there, then the interest would be an LBO$K$, I don't know, 17, $17 if you look. There we go.

It was 16 previously. Multiplied by the average balance in the year in which it's paid down.

We don't exactly know when it's going to get paid down.

Okay. So we get zero there.

Okay. Now we're going to talk about the mezzanine.

So the mezzanine is genuinely different.

If I go to the right-hand side and do a small amount of accounting here, asset equals liability plus equity, and let's put that in bold.

For the most part, we're talking about debt, and we're thinking about interest.

You'd have cash would go down, and you'd have retained earnings would go down by interest spent or finance charge, if you want to be a bit broader in that.

Now, if we've got a PIC instrument, payment in kind, for interest, then the cash is just not going to go down at all.

But we still need to recognize the expense because we've still, in general terms, borrowed money, which has helped us to buy assets, which has helped us to generate revenue. It's important to match the revenue to the costs associated with generating that revenue.

So we'd want to recognize the interest in an income statement on a periodic basis. We can't say cash going down because we're not actually paying for it yet. It's accruing up into the loan.

So instead of that, we say mezzanine up by whatever the value is, and that is what I'm seeking to replicate here. So for the mezzanine debt, apparently the beginning amount is 300. The interest rate, which we looked at in the previous session, I'm sure was about 12%. So if I say equals LBO$K$18 multiplied by, I'm just going to multiply that by the beginning balance, and hit Enter. 36. Well, 36 over 300 is 12%. Okay. So let's grab the total there. So you can see that the mezzanine debt has gone up. It is not a cash inflow. Okay? Usually, if debt goes up, we'd say, well, it's a cash inflow.

You've issued more debt. We've not issued more debt, it's going up because of the interest. So we need to make sure that we capture this in our model. Our model has definitely captured the increase in the mezzanine, but we also need the interest expense coming through.

So we need to go back to the income statement and make sure in the income statement, the interest expense looks as we'd expect.

Now, I'm probably going to slightly change the way that this works, just so we can reconcile what we're doing.

But it's a useful exercise. I kind of left it so that I could amend it, so I could explain some of the logic behind what we're doing.

Let's go back to this debt sheet because we haven't quite finished yet.

I'm going to look at the preference shares, and they just behave exactly like the mezzanine. You can reference this accounting equation here for how this is working. So the beginning preference shares of 622. The accrued dividends would be equal to LBO$K$19, I think it is, multiplied by the beginning balance.

I got 74.7. There we go.

So in that instance, if we're talking about the dividends on these preference shares, you'd have the prefs, which I think they're shown as equity.

Oh, no, actually, maybe they are shown as a liability.

So the prefs are going up and retained earnings going down.

It isn't an interest expense, it is a dividend, so it's charged directly to retained earnings.

So actually, you won't find it in the income statement.

We've got the increase in the dividend here, but I've got to say to myself, "My goodness, Jonathan, do we have the retained earnings going down?" Well, if I go back to the calculations, I don't want to disorientate you too much, so I'm just hovering over that. Just think about what I'm going to do. I'm going to look at the calculation section.

I'm going to look at the base analysis for retained earnings or for equity. I'm just going to make sure that there's a formula that pulls it down by the preference dividend.

I'm going to make sure we've got both sides of the equation.

I know we've got the increase in the preference share value. I just want to make sure that we've got the reduction in retained earnings.

So if we go back to the calculation sheet here, I actually do have that. I've got that here. Okay? So I know we've got both sides of this.

It's not going to cause me a big problem at the moment, hopefully. Why don't we have a quick look at the...

Well, actually, why don't we have a quick look at the debt schedule before we leave this? Because I just wanted to make sure I'd got the sign the right way around, and I don't think I have. Now, here's a clue.

You've got the total cash interest expense is 67.9, and that cash interest expense here should be picking up what? The interest on the revolver, the interest on the first lien, and the second lien, and the junior notes. That makes sense.

Not the mezzanine, of course. And the total interest expense, more broadly, should be doing the same thing, picking up the same numbers, but also including the mezzanine.

How can it be lower? Well, the solution here is that what I should have done is I should have made the interest amount negative. It's very easily fixed.

If I go into J26, I'm going to multiply by minus one.

Just do much. If I go into J35, I'm going to press F2 and multiply by minus one. We're just making these interest amounts negative.

If we go down to the second lien, I'm going to press F2 on the interest and multiply by minus one.

If we go down to the junior notes, I'm going to press F2 and multiply by minus one.

I don't think I can multiply the mezzanine by minus one because I need it to increase the value, and I don't think I can do for that for the dividends either.

But it doesn't matter because I'm sure when we looked at the equity, it was multiplied by minus one at that point, so we're all good.

Now these numbers look more sensible, i.e., the total interest expense is greater than the cash interest expense. So you always want to be sense-checking what you're doing.

I think if we go to the balance sheet.

Now, we've got a little bit of work to do maybe in the balance sheet to make it balance. So the balance sheet doesn't balance, it's out by the interest, okay? It's out by the interest here.

Now, that is because at the moment, the mezzanine debt is going up by 36, and we don't have the income statement going down by 36.

I was going to make a very small amendment to this model.

So if we go to the income statement, because I think this is a good idea.

If you look at the interest expense, the interest expense goes to debt J62, and it grabs the entire interest expense, including the mezzanine.

But the mezzanine, I calculated that based on a beginning balance, so it wouldn't create a circular reference.

What I'm going to do is I'm going to delete that reference.

I'm going to go back to the debt schedule.

I'm actually going to pick up the cash interest expense, and then outside of that circular switch, I'm going to say minus, and I'm going to grab the mezzanine accrued interest. Now, if I hit Enter, you can look down the bottom here. It's not going to create a circularity because that's based on the beginning balance. But what it will do So enter on that, is it just brings through that small amount of interest.

We now go to the balance sheet, then the balance sheet balances.

It's probably a better way of us modeling this.

You don't have to do that if you don't want to, because when you build the model and flick the switch, everything will flow through anyway.

For consistency, I'm just going to control R that out.

Now, we've got about 20 minutes left, and we're not doing too badly here. But I would suggest if you go to the cash flow statement, we want to bring in the interest. I hadn't realized that we hadn't linked that in already. So in the cash flow statement, let's zoom in.

I've highlighted this in yellow to draw my eye to it.

So if I go to cell J6, I'm going to say equals, I'm going to go to the debt sheet, and I'm going to go and grab the cash interest.

Shouldn't really use my mouse, should I? It's all about using the keyboard.

And I think Microsoft are keen on me avoiding using the mouse because you probably have this in Excel as well. If I click on the spreadsheet, you can see where I'm clicking. It's kind of not aligned properly.

You need to flick onto different sheet to fix that. All right.

Anyway, I'll use my keyboard. That's a better way of doing it.

I'm going to link this into J63. Now, the problem is, as soon as I do this, the cash is going to go down, and if the cash goes down, then that means that we can pay off less debt, and that means we've got more interest, which then would flow back into this.

So if I do that, it didn't give me a warning, a circular reference warning. But you can see down the bottom it says calculate.

If I go into File, Options and Formulas and disable iterative calculation, it will give me that circular reference warning.

What I'm going to do, we'll fix this.

What I'm going to do is I'm just going to put the circ switch in front of it.

I'm going to say equals if open bracket, maybe just one equals. If open bracket switch.

Now, there are a couple of switches in the model, and the other part of the model uses switch two I noticed. So I'm going to say if switch two, I can hit Tab to complete that, equals one, comma, go and grab the cash interest expense, comma zero.

Otherwise do zero.

Okay, and that was already going to flow through as a negative number, so we are all good.

All right. I think now we're going to do a bit of tidying up.

We can copy the projected numbers out to the right for the cash flow statement. I think we can do the same for the debt schedule. I hope you won't mind me massively zooming out. I know you can't see what I'm doing here.

I can barely see what I'm doing either.

But the point is that I can just grab the entire load of data and just copy this out to the right.

Control + R to copy it out to the right. Let's just zoom in there.

So you can see here that the debt is being paid down. The first lien debt is being paid down.

Once it's paid down, we've got surplus cash, and at that point, we start making accelerated repayments on the second lien debt.

Once that's paid down, if we go further down, we start to accumulate cash, ultimately. Okay. So, let's just go back. Quick check on the balance sheet. Fantastic.

It all balances, so that looks good, but I had some confidence in that already because it balanced previously for that first year.

I don't think there's anything left for us to copy out to the right.

I think we are all good. So let's all go to the equity return section of the LBO sheet.

And the complexity here, the interesting item here, is that we're going to break the returns out into different kinds of investors, mezzanine, institutions, and management.

We can't really break down those returns unless we know what the equity value is.

So we need to do a little bit of work on this.

Let me just get my active cell down here.

Okay, so this is fairly standard stuff.

I'm going to go into year one.

Excuse me.

I'm going to show you my formulas.

So if we go into year one, I want the adjusted EBITDA. That's going to come from the income statement.

So Control and Page Up, Page Down a few times to get to the income statement, go and grab the adjusted EBITDA.

It's in income statement J17.

I want to calculate the enterprise value.

That's the product of the exit multiple and that EBITDA number.

If I say equals, anyone who was here in the first session will remember that at the very top of this sheet, we had an entry multiple of 7.5X. I'm going to treat that also as my exit multiple.

Let's multiply that by the EBITDA.

Now we need to go over the bridge. There's nothing weird about what we're doing here. It's very, very standard. I'm going to go and grab the cash and the debt and then the mezzanine and the prefs and get to the equity value. Where's that going to come from? Well, I'd say the balance sheet would be a great place.

So let's go over to the balance sheet.

And in the first projected year, I can grab the cash, which is zero.

It's not always zero. We do end up with some cash here at some point in the future.

We're going to go and get the total debt excluding the mezzanine.

I think Alt and equals. I'm going to say Control and Page Down to get to the balance sheet.

And I would say that that total debt includes the projected revolver in balance sheet, exclamation mark, J17, comma.

It also should include... Well, maybe for completeness, it should include the legacy debt in J21. And if I hold down Shift and tap Arrow Down, I'm going to get the first lien, second lien and junior notes. The mezzanine is part of the debt, but I'm not going to include it in the numbers I'm about to grab.

I'm going to get it separately because I want to separately calculate the return for the mezzanine investors. Let's hit Enter on that.

The mezzanine equals is back to the balance sheet, Control and Page Down, the ending mezzanine ... is, well, let me get the right column there, is in J25.

Let's make sure I've got the right column for the others.

Yeah, J. Good. And I think the preference shares, I can just Control + D to go and grab those. What's the ending equity value? Well, we're just going over the bridge.

If we start with the enterprise value, add the cash, and subtract the sum of the debt, then we get to the equity value, 306.6. It's not really because we haven't turned on the interest yet, and that's going to be material.

So we'll turn that on at the end to see this working.

I think probably those numbers are good, so I feel like I can grab those and copy those out to the right.

It looks like we've got eight years here.

Well, that doesn't look right at all, does it? Because it's becoming negative, and I think it's pretty obvious here what I've done. If you look at the enterprise value, I have failed to lock the multiple above. Let me F2 that in the first year, and in the formula where it says F7, let's F4 that. And if we copy that out to the right, that looks a whole lot better. Okay? I think that makes more sense. Cool.

Always sense-checking what we're doing, looking for silly mistakes.

Everyone makes silly mistakes. Okay, so let's calculate the returns for the... Maybe I'm going to do the returns for the institutions first.

I think that would be sensible to do them first.

So the institutions have an initial investment. Let's just audit what this model is doing.

So the initial investment for the institutions would be from the sources of funds, the preference shares.

So they invest in these preference shares and the common equity.

Not all of the common equity, because at entry, they only invest 90%. The management roll some of their equity, so they have 10%.

Okay.

That's their initial investment.

The question is, what are they going to come out with? Well, I'm looking at these two rows here. Let me color these in so we can see them. I'll color them in light yellow and show my formulas here, so you can see what I'm doing. And actually, maybe the formula for the row below as well. Okay, the preference share value equals, is going to be 698.8. So what the institutional investors have done is they've locked in a return. So even if the business doesn't do quite as well, and the common equity is not quite where they want it to be, the preference shares are going to accrete at, I think it was 12%. They're going to accrete at 12%.

So that helps to secure a return for them.

Let's go and grab that number. The equity value is here, 306.6, but they're not going to get all of that.

I'm going to grab that number, and I'm going to multiply that by, if we go just to the left and above a little bit, somewhere.

A bit further up, maybe. Here.

At entry, the institutions put in 90%, management put in 10%. At exit, they both give a bit of breathing space to the mezzanine investors.

They're going to give them 5% of the equity.

That means that the amount of equity at exit for the institutions and the management is going to reduce slightly.

So, I'm going to grab D24, which is 85.5.

I'm going to press F4 to lock that. We don't want to forget that.

And I'm going to press Enter.

Now, I want their cash flows. Well, simply, the cash flows would be those numbers above. Except that what I want is I want the cash flows to only come through if this year is equal to the assumed exit year. So, let's modify that slightly. Equals, IF, open bracket, and Excel says, "Give me a logical test." I'm going to go up the model, and towards the top of this sheet, you're going to find an exit year assumption.

I can't remember what year it was. It might be year five, it might be year seven, I forget. Let's arrow up, up, up, up, up, up, up, up.

Directly above us in J9, we've got exit year seven. I'm going to lock that with F4.

So if J9 equals the year that we're in.

I'll use my mouse so you can see what I'm doing.

So it equals the year that we're in. We're looking in year one at the moment.

And I don't need to lock that because we're going to copy this out to the right and test each individual year. So if those two numbers are the same, then definitely go and grab the preference share value and the equity value.

But if they're not the same, which in fact they aren't at the moment, comma, just return a zero. Close bracket.

Okay, that gives us a zero, but if we copy this out to the right, then in year seven, which is there, Control + R, we'll see that equity value dropping down, or the cash flow value dropping down, and we get a return of 22.4%. Don't get too excited about that.

We haven't turned on the interest rate yet.

Let's do the same now for the mezzanine.

So we're going to go backwards and do the mezzanine. Same sort of idea.

What is the value... I'm going to do the mezzanine loan first.

So what is the value of the mezzanine loan? I'm going to just highlight these in yellow as well. I liked doing that earlier.

It led my eye to what we were doing. What's the value of the mezzanine loan? It's 336.0. What is the value of the warrants? So the mezzanine investors get given equity on exit. They get given common equity. Not all of it, of course.

So if I grab the common equity of 306.6, I'm going to multiply that by the amount of equity we're going to give them on exit, which is 5%.

It's in D25. I'm going to lock that with F4.

Okay.

And again, I could say alt equals, equals and add that up, but actually, we only want the number to drop down if it's the exit year.

So I'm going to say, what was the reference previously? It was J9. Okay. So I'm going to say equals IF Dollar J, dollar nine equals, and I'm going to go and grab the year that we're in, which is in J31, comma, go and grab the equity value of the warrants and the value of the mezzanine loan, comma, otherwise do nothing, close bracket. And it does nothing, and that's not very exciting.

But if we copy this down to the right, now, over to the right.

Now, the mezzanine had interest at 12%, so it was accreting at 12%, but they get a little bit more than that because they're also going to get some equity. So if we control R this out... Oh my goodness, have we not got those numbers dropping down? What have I not tested? Maybe it wasn't J... It wasn't J8, it was J9. Apologies.

That's me taking shortcuts.

Let's drag that down.

There we go. So, I was hoping for a bit of a dramatic climax to this.

So are they going to get 12% or are they going to get more? Well, given they've got some equity, they get a little bit of equity in there as well, and so they get 13.6% IRR.

All we need now do are the management numbers.

The management don't have any claim on the preference shares because they didn't invest those. They don't have any claim on the mezzanine.

They just have a claim on the equity.

I'm going to say equals if open bracket, dollar J, dollar nine, I think I've got that right this time, equals this year. So if the year that we're in is equal to the exit year, comma, go and grab the ordinary equity, but multiply it by the management share of that at exit. The management share of that at exit is less than 10%, wasn't it? It was 9.5%.

Let's lock that using F4.

What if the years aren't the same? We'll just return a zero, right? Close bracket.

And it returns a zero.

In fact, I've realized I could have done that in the cell below.

Maybe I should have done that in the cell below.

Let's just cut and paste that down here.

I think the management line there was just actually a heading.

Okay. So, hopefully, that hasn't screwed up my... No, that's cool, my IRR function.

So now if I copy this out to the right, their returns are going to be huge.

And I think you're going to have some crazy numbers here.

If we copy it out to the right, we get 135 and you get 101.3. We want to incentivize these guys, and in absolute terms it's not loads of money, but that is a bit large.

Now, I don't want to draw any conclusions yet because we haven't got any interest flowing through the model. All I would want to do now is go back to the input sheet and that circular switch we highlighted in yellow at the very beginning of this session, I'm going to turn that to a one.

Excel will blow up in my face and say, "You've got a circular reference," but that's cool. I'm going to go to File, I'm going to go to Options.

I'm going to go to Formulas, Enable Iterative Calculation, and I'm going to click OK. And then that will iterate through.

We'll go back to the LBO sheet, and we can see the returns here. For the sponsor, they get 21.2%.

It wasn't a bad deal. It could have been a good deal, but Debenhams ultimately, in the real world, did fail, unfortunately, as a business. Okay, guys, that brings us to the end of the session. So all I want to say is a big thanks for being... I can't believe how many people we've got on the call here.

We got tons of people dialed in. So thanks so much for all the people that have joined the session. I hope you found that useful.

And I look forward to seeing you in future sessions. Thanks so much, guys.

Have a great Friday. Cheers.