My name's Jonathan. We're going to be going through some LBO content.

Hopefully, you can hear me, hopefully, you can see me, and hopefully, you can also see my screen. Now, it would be useful if you guys could grab some material so we can work through some content together.

It's not absolutely essential because you could just watch me do it, but it's probably more interesting to you if you work along with the content. So there's loads of files here, but there's one particular file I want to look at for the session.

And if I zoom in, it is midcap LBO modeling empty.

So if you can click on that, that would be great.

That's a whole bunch of files, but if you can click on midcap LBO modeling empty, that would be great.

And for those of you that have just joined, I'm just going to repost that link.

And when you click on the link, if you can go down to the bottom right-hand corner and go for midcap LBO modeling empty.

Okay, I'm going to click on that to download it.

I do have it open already, so I'm going to jump into the file.

You should land on something that looks like this.

We're going to build an LBO model together for a real company.

And before we do that, I just want to think a little bit about the big picture. Okay. So we're going to build an LBO model.

But before we do that, I want to think about big picture.

I want to do something very visual with you.

So I'm going to grab just a blank Excel. You don't really need to do this.

Just want to kind of lay out what we're trying to achieve here.

So with an LBO, we're going to buy a company predominantly with a significant amount of debt finance.

And so we're going to put a relatively small, smaller amount of equity in.

And we're going to hold it for a period of time, three years, four years, five years, six, seven years, maybe. We're going to hold it for a period of time.

In that period of time, we're going to try and make some improvements to it.

So we're going to make some operational improvements, which will increase the value of the EV, and we're going to direct the cash flow towards paying down debt.

Certainly, in the traditional sense, we would do that.

So I'm interested in what a model would look like.

Before that, I want to take a step back and just do something relatively big picture. Okay, so I'm going to just chuck a few thoughts down.

I'm going to say entry, and I'm going to say asset equals liability plus equity. Let's make that bold.

And I'm going to just create some boxes here.

So we're going to have a green box.

These are not really to scale, but we're going to have a blue box, and we're going to have a kind of peachy colored, orangey kind of colored box. I'm going to say EV, debt, and equity.

We'll have a total. We'll chuck some numbers in here, so we're going to have a total for these. Right. At entry, we're going to buy a business predominantly with debt, and debt is certainly initially sized off of EBITDA, so that's how the market thinks about debt. So let's say EBITDA number.

I'm going to leave a bit of space here. I'm going to say EBITDA.

Let's make it easy. Imagine this business has got EBITDA of 100.

The debt to EBITDA, I mean, it depends on the company, it depends on the industry, it depends on market factors, but let's go for six.

It's not a completely unreasonable number. And we're going to have EV to EBITDA.

So what multiple are we going to buy the business on? Now, if we're going to buy it on a multiple of, say, 60, then that would not, in the traditional sense, be an LBO, right? Because if you're financing it six times debt to EBITDA and you valued the EV at 60 times, then most of your sources of funds would need to be an equity issuance. So maybe let's not do that.

Let's just make this fairly traditional. EV to EBITDA, I'm going to have as 10.

The calculations here are really straightforward. A, what's the value of the EV? Well, that's the product of the EBITDA and the entry multiple.

What's the cost in the debt? Well, it's the multiple of EBITDA and leverage.

And what's the size of equity? Well, that's the enterprise value minus the debt.

So the equity value is what we put in.

So if I'm running a fund and I want to buy this business, then I would get people to invest in my fund and use the investment to go and buy into this business. So this is our initial for our fund.

It's our initial investment. For those people that have just arrived, there's a link in the chat box. I've just posted that again.

If you jump down to the bottom right, you'll be able to grab the file that we're going to work on shortly. We've not started working through the file yet. Okay.

So let me grab those headings and all of those items. Let's paste it over here.

And I'm going to say exit.

So the idea is we're going to hold the business, we're going to buy it, and we're going to hold it for a period of time, and the holding period can vary.

Holding period. But I'm going to go for three years.

And in that time, I'm going to try and improve the EBITDA.

So we'll have an EBITDA compound annual growth rate.

Let me put it like... Let's be relatively conservative. We're going to say 5%.

So we're going to uplift the EBITDA, and there are lots of ways you can uplift EBITDA. But what I'm immediately not that interested in doing is massively increasing revenues. Because if we massively increase revenues, if that is our business plan, then we will have an increase in operating working capital, and we would have an increase in CapEx. If you didn't do that, if the revenues and the earnings go up and the invested capital doesn't go up, your return on invested capital would kind of infinitely explode. So it doesn't make sense.

So if we were going to grow revenues and grow EBITDA, then we would need to pump money into CapEx and change in AWC, and that would suck cash out of the business that might otherwise be used for paying down debt.

And so my EBITDA growth is more likely going to come from cost savings, headcount rationalization. I've got a question, and I'll just read it out as I'm glancing at it. So when you buy a company, you buy its equity rather than EV, so why would you pay the EV? That is a very reasonable question.

So if I go backwards a couple of steps. I'm going to grab that diagram and let's maybe chuck that.

I'm going to attempt to grab that diagram, and I'm going to chuck that over here.

So what does the business look like before we buy it? Maybe like pre-acquisition.

Well, before acquisition...

We don't really need these numbers.

So before acquisition, there'll be a certain enterprise value.

I'm going to make a number up, okay? Let's just say it's 900.

And there'll be a certain amount of debt.

Let's say it's got 400 million of debt, and there'll be a certain amount of equity.

Okay. So let's do that. Something like that. Okay? I am making these numbers up. Right.

So before we acquire the business, we find this business, and it looks like this.

We rock up to the shareholders, and we say, "We really want to buy the business from you." The market cap at the moment for the business is 500 million.

And the shareholders say, "Yeah. Okay. We're interested.

We might sell this to you, but we'd want you to pay a premium." So I don't want to pay a huge premium, but let's say I pay a premium of 20%.

So if I maybe just grab a slightly darker shade just so you can see there.

I'm not sure how that comes through on your screen sharing from my screen.

But let's say that the equity value, because this is premium, including the premium, is actually 600. And that means that to be more specific, the acquisition enterprise value...

And let's grab a slightly darker shade as well. There we go. Is actually 1,000.

Now, I've done that because that aligns with our entry.

Now, if you look at the business, this is their existing debt, right? And this is the equity purchase price.

And we're going to refinance it with new debt and refinance it with new equity. Now, if we were doing a model, well, we are going to do a model.

So if we're going to do a model, then I would probably produce a table that looks something like this. I would say equity purchase and refinance target net debt And then I'd call that something like total uses of funds. And then over on the right-hand side, I'd say something like debt issuance and equity issuance, and I'd call that total sources of funds.

Probably have those in bold, like so. Right.

So, if I look at these diagrams that I've drawn above, the equity purchase price is 600 million, so I'm going to go and grab that.

And the net debt of the target, which I will definitely need to refinance because an LBO is like a restructuring exercise, right? You take their old debt, you get rid of it, and you insert a new structure, a new debt. So it would be reasonable for me to write here something like, uses up there.

These are the uses of funds, and let's add that together. And then on the right-hand side, I'd say, "Okay, that's what we're going to pay for.

Where's the money coming from?" And I would say, "Well, we figured we could borrow six times debt to EBITDA, so we're going to lever up." Probably as the corporate was structured previously, we felt uncomfortable with that amount of debt, but we're going to take a large amount of debt on at entry, and we've also got some equity issuance. So this is our total sources of funds.

It wouldn't be unreasonable for me to write sources above here.

So we've got uses of funds, and we've got sources of funds, and they should tally.

Now, I'm going to read that question again.

It said, "When you buy a company, you buy its equity rather than enterprise value, so why don't you pay the enterprise value?" Well, if you look at this, the enterprise value is reflective of the equity that you're buying and the net debt that you're obliged to refinance.

So that being the case, the enterprise value is reflective overall of what you have to pay, right? And that's why we size it in that way.

I hope that's okay. I hope that makes sense. Feel free to chuck stuff on the chat.

I've got it on a separate screen, and I'm happy to answer questions as they come in. Okay, brilliant. Okay, great stuff. Always good to have questions.

So I'm quite focused on what's going on here. Okay? What's happening here. So when we hold the business for three years and we grow the EBITDA by making cost savings of 3%, and that means that the...

Let me show the formula here, and I'm going to zoom in a little bit.

That means that at exit, the EBITDA is 100 multiplied by open bracket, one plus 5%, close bracket, raised to the power of three years.

So it's compounded up. Put it here.

It's compounded up to 115.8. What I'm going to do is I'm going to say that the exit multiple is going to be the same as the entry multiple.

I'm going to assume that they're going to be the same number.

And you might say, "Well, it might not be.

The multiple might go down." Okay, let's deal with that in order.

The exit multiple might go up, but I wouldn't base my investment case on the assumption that the multiple is going to go up because, to a great extent, you don't have control over that. It's kind of a macro factor, right? You could be blown backwards and forwards by the market.

You can have headwinds, and you can have tailwinds.

I wouldn't base an investment case on that.

It seems prudent to suggest that the multiple will remain constant.

That seems like a good basis for your base case analysis.

And you might say, "Well, the market might move against you.

The multiple might contract." It might do, and I think there's an argument that perhaps we should do some scenario analysis to see if we can still make a good return even if the market moves against us. But I'm not going to do that at the moment.

It's just my base case. So I think that the exit enterprise value is going to be the product of our uplifted EBITDA and our exit multiple. And if you don't mind, what I'm going to do is going to grab these cells and just move them down a bit because visually, we had 1,000 of EV at entry. We've got 1,157.6 at exit. So I just want to make it slightly larger. We're also going to try and pay down the debt.

So the debt was originally 600. We're going to pay some of that, and all we're going to look at is the-- requires to do a bit of work here, and I'm going to massively simplify this. So ignore the fact that you'd have to do a cash flow forecast, and you have to do a detailed debt repayment schedule-- detailed debt schedule.

Ignore the complexity and just let me suggest that the debt paydown will be -100, okay? So if that's the case, the exit debt would be the 600 we started with, accounting for the paydown of 100.

And just visually, that means that the debt, which was 600, is just going to be that little bit smaller. Now, given that equity is our plug number, if we know what the enterprise value is, we know what the debt's going to be at exit, or we assume what it's going to be, then we can calculate the equity value, and it's uplifted. So big picture, this is what I asked you guys to invest as a fund, and this is what I think we're going to produce for you at the end. So if we could lay that out below.

We could say year, and we could say cash flow, and we've got year zero, n equals year zero plus one.

If I copy that out to the right, we've got three years there.

The cash flow initially was -400. We had no cash flow for our investors in year one and year two, but in year three, they sell the business.

We have a cash inflow of 657.6.

Is that good? I don't know. It sounds pretty good.

Sounds like a pretty decent return, but we've got to think about time here as well.

So we're going to use a function in Excel called IRR. Zoom in on this.

Really, really simple. If I say equals IRR tab, and I grab the year zero investment, which needs to be a negative number, and then I grab some zeros in the middle. It needs zeros to kind of count the years or count the periods.

And then we've got 657.6, and it gives me the IRR. That is a reasonable 15%.

So we're targeting somewhere around maybe 15 to 25%, so that seems reasonable.

Now, what we need to do next is we need to jump into the file I asked you guys to download, and we need to replicate this, but in the context of a real company.

For those of you that weren't here at the very beginning, let me just go into the chat box.

Oh, let me go into the chat box. There.

And just grab the web address. Sorry, there's the chat box. Right.

So anyone that wasn't here at the beginning, jump into the chat box.

Jump into the web link. It will take you to this page.

Down the bottom of this page, there are some downloads, and we're going to look at midcat LBO modeling empty. I'm going to jump into that now.

We've got about 40 minutes, and that is a good amount of time to work through a model. So we've got four models here. I built the file.

I built the file about a year ago, and my objective was to try and find some European midcap businesses that I thought might be LBO targets.

I'll let you have a look through the other LBOs if you want to after the session, but we're going to have a look at LBO, which is Pets at Home. You may be unfamiliar with Pets at Home, but I'll bring you up to speed on that very quickly. So in the UK, in the pet supplies market, there are supermarkets like Tesco's and Sainsbury's and Asda and Morrisons, and they'll have dedicated aisles selling pet supplies. And there are also a great number of very small, often independent pet shops. But there aren't that many large format pet shops.

And one of the very few chains that exists in the UK is Pets at Home.

It's a market that I thought probably wasn't growing very aggressively, so it doesn't need much CapEx or increase in OWC.

And so I thought that probably made it possibly attractive for a sponsor.

I also looked at it and realized it was trading on a lower multiple, and a very kind of quick test would be the multiple was not too far away from maybe the maximum leverage a business like this might be able to take on, which means we could predominantly finance it there.

Which suggested to me it might generate a good IRR.

My job here in this particular model was to say, "Jonathan, use real numbers, make it realistic, build an LBO model that is not unnecessarily complex but has enough complexity to be realistic." So it's not convoluted. It's a good efficient model for us to use.

Now- If I ask you to have a quick look around the model, you're going to see some assumptions. We're going to talk about things like net-to multiples.

You can see sources and uses. I had a good question on sources and uses five minutes ago, so we did a little bit on that.

And then we've got some assumptions and an income statement.

And why would we need an income statement? Well, you've got to build a cash flow statement.

So it'd be useful for net income, maybe. You don't have to, but it would be useful.

You're going to need to know what EBITDAs is as well at exit or various points throughout the model, so you can figure out the enterprise value.

So there are some good reasons for forecasting this out.

We've got an income statement. We've got some key numbers in the cash flow statement. I tell you what we are missing here by design is not a full three-statement model. We do not have a big fully blown balance sheet and cash flow statement here because we don't need that.

What I've done is I've stripped it back to give us only the essentials.

No one's going to build a full three-statement model here.

And then we've got a debt schedule below.

So you might say, well, I'm quite excited about the debt schedule.

You might say, "Why is Jonathan quite excited about the debt schedule?" Well, excited about the debt schedule because that's when we figure out what the exit debt is going to be, and that is one of the ways we can figure out the exit equity and get to the IRR.

So it's kind of we need to get that bit done, right, to get to the end and get to the result to see if it's a good target. So let's build this.

I'm going to build it with you. I'll show my formulas as I go along, and I'll call out references, numbers, so you guys can follow along with me. A lot of this is going to be driven by EBITDA.

So let me just color this in. I'm going to need the last 12 months EBITDA number.

If we say equals, I can get that from further down.

So if we scroll down here in either F25 or just go for 40, no matter which cell you use. I'm going to go for F43.

We've got the EBITDAR, the ultimate EBITDAR Q47.

So that is the number that came from their historics.

I have got some forecasts here, and I took some numbers from Felix, from Facts, actually, for this analysis. So I used three years of consensus, and thereafter, I created my own assumptions. This is a good model. Okay? These are good input numbers. So first things first, let's just get the EBITDA number, and we can calculate or we have here the maximum entry EV/EBITDA multiple, which also links into the exit EV/EBITDA multiple.

What we need is I want to go to the...

Let me grab a stylus just so you can see what I'm doing.

I'm going to use the EBITDA number, and I'm also going to use the entry multiple.

Now, I think you can do your own research if you want to on Claude or something or ChatGPT, but I think the multiple I'm applying here might now be a little high.

I think it's trading on a lower multiple, but I haven't looked at it for maybe a month or something. So I might even be being pessimistic with these numbers.

The enterprise value will be the product of those two.

It's a very reasonable question earlier. But that's not what you're paying, right? No, it's representative of the equity value and the debt that we'll need to refinance. So kind of overall, it's what we're going to have to pay out.

There will be some fees as well, which are not taken into account in that number.

And if we say equals, it looks like fees are in cell F17.

They appear to be 2% of enterprise value.

The fees will be a little bit more complicated.

We'd have debt issuance fees and advisory fees.

But you could break that down if you wanted to.

It is not going to materially move your IRR in any way.

So it would be a waste of time. This is perfectly acceptable to do it like this.

Total use of funds would be the sum of those. I've got 2015.5.

If you go to the right, I'd like to figure out what the total source of funds, what the total sources of funds are going to be.

And it looks like we're going to have some senior debt, unsecured notes, and some equity. Now, if I go to the right, I know... Let's just get rid of these arrows.

If I go to the right, I know that the maximum debt to EBITDA is six. That was an assumption of mine.

I don't think that was an unreasonable assumption at all.

And so if you look to the right, that means the debt is going to be six times.

Now, in the source and use of funds table, I picked up the amount of senior debt I thought we could pursue, which was four times.

And so anything else will be sucked up by the unsecured notes.

So you can see the unsecured notes pick up six times debt-to-EBITDA for the deal and deduct from that four times for the senior debt. Okay.

And the remainder would be equity. But we need some numbers.

So the senior debt will be the product of the EBITDA and four.

The unsecured notes will be the product of the EBITDA and two.

And the equity, if you'll just permit me to update the heading, will be the plug number. So let's show some formulas here.

The equity would be equal to the total use of funds minus the debt, the two types of debt we're going to raise. That is a very important number.

So the equity that we have raised in your IRR calculation is your cash outflow in year zero. So I'm saying to you guys as the fund, I've done the sources and use of funds table. I need 533.5 million to buy the business.

And you would say, "Right. Is that the price of the equity?" Nope.

The price of the equity isn't broken out here, but it's within the enterprise value. What that is, is that's the issuance of equity, that we're going to issue that to buy this company. The more debt we could put in place, if we could have probably would be sort of ludicrous to maybe... Well, I don't know.

It feels like it's getting high, but if we had seven times debt-to-EBITDA, then we'd need to put less equity in. If we had 7.5 times debt-to-EBITDA, that probably is pretty high. We put less equity in. But that isn't the case.

We're going to go for six times. So very useful.

These three cells on the screen are very, very useful.

We've got our year zero equity, our cash outflow, and then in year zero, we've got our debt numbers.

We're going to try and get those debt numbers paid down. So let's scroll down.

Let's go down the model. Already grabbed a load of assumptions here.

Let's build our income statement.

There's nothing weird about the income statement at all.

It is a very standard income statement.

For the revenue, I'm going to say equals open bracket one plus, and I'm going to go and grab the revenue growth rate of 1.2%.

You do have some consensus estimates for revenue, but I've got three years of those as absolute numbers, and then you kind of run out of runway at that point, and I converted it to a percentage. So it's probably useful if we just work with percentages all the way through because it makes the model more consistent.

Let's hit Enter. Oh, let's multiply by the previous year and hit Enter. I just noticed, I think we've got someone else that's entered the room. If you've just entered the room, I'm just giving you a link there in the chat box to the materials. Materials are on the bottom right.

Okay. Let's do EBIT. We don't need to mess around with COGS or SG&A.

We don't need that complexity. But I quite like to have EBIT.

So I'm going to say equals.

It looks like in the first year, I've got an EBIT margin of 9.8.

I built this model. That is not my forecast EBIT margin.

I've taken that from consensus. So there's a bunch of super smart people that work at brokers, equity research analysts, and they live and breathe Pets at Home, and they reckon that the margin the year after I built this was going to be 9.8%. So it would seem difficult to argue with that level of authority. I'm going to multiply that by the revenue. Okay.

And then we're going to do the cost savings.

Now, I will concede that this is the number that I have not put much thought into.

So if you say equals and go and grab from G27 3%.

The reality is that if we had a management team at Pets at Home and we're buying it, we would be buying them out. So they would almost certainly have some equity in it, and we'd buy them out. But we would ask them, require them to roll over a small proportion of their equity and And that could be financially transformative for them.

They'd make a lot of money ultimately when we exit.

But the reason we're doing that is we're saying to management, we want an equity role because we want to keep them in the business.

We want to improve the EBITDA in the shortest possible time.

So the IRR is flattered if we can get in and out very quickly.

If we keep the management in the business, and we incentivize them appropriately, running. Okay? So that's really, really important.

Now, I'm saying that because the management will give me a view on what they think the cost savings could be because they know the business. I didn't have that.

I just picked 3%. It's probably a bit high, to be honest.

But if you wanted to, you could do some research on this.

So you can go and have a look at Claude and see or Chat GPT or something, and see what it thinks. Anyway, this was 3% of revenues.

Cost savings of forty-four point nine. It is quite a lot relative to EBIT.

I mean, it's quite a big kind of-- We're saying we could uplift the EBIT by 30.5%.

It is quite a lot. And also just coming in full force in the first year is probably not very realistic. But anyway, we're going to go with it.

Interest. I would love to calculate the interest now.

I can't, because I don't know what the debt balances are yet, and I don't know how much cash we've got. So we can't do that. I can pick up the profit for tax.

I can go for the tax expense we've got, I believe, somewhere up here.

There we go. We've got the long-term tax rate, it's in G28.

I'm going to multiply that by minus one, and then we can get the net income.

Okay, I've got 150. Right. Let's go and grab some key numbers.

So the idea is I do not want to build a full balance sheet. It's a waste of time.

No one's really going to do that here when you're doing an LBO model.

But there are numbers I need. I need to understand PP&E, and I need to understand operating working capital, which you might call net working capital.

I need DNA, and I need CapEx. And you might say, "Right, DNA, let's go and build a base analysis for PP&E." I just think we're getting sucked into doing more work than we want to there. Let me think about this rationally.

If I want to get some numbers for PP&E, I'm going to go and grab the EBITDA from above.

That EBITDA number...

So to get that EBITDA number, I'm not going to go and grab G25 because I'll run out of runway if I do that. I'm going to go and grab the EBITDA margin.

I'm going to multiply by the revenues. Okay? That means when I copy it across, it'll always be pulling that number.

I now want to get the DNA because I want to do some work on PP&E.

And I don't want to do a schedule. Wait a minute.

If I know the EBITDA minus and I know the EBIT, the difference between the EBITDA and the EBIT would be the DNA. So I can infer it from that. Pretty useful.

And that's built from consensus, which makes me feel quite confident.

We're also going to want the EBITDA after the cost savings because when we get to the end of the model, we're going to want to take the exit multiple against the exit EBITDA. And these consensus numbers are ignorant of the deal.

They are ignorant of the cost savings.

And so we wouldn't want to use that ultimately.

What we want to do is take the exit multiple and multiply it by the adjusted EBITDA number after the cost savings. Let's go and get the OWC. Now, wait a minute.

This surprises me. They're a retailer. They have large store format.

Carry a lot of inventory. And I'm seeing negative operating or net working capital here, which seems a bit odd. But then I think, well, probably they do have a lot of inventory, but they are the main player in this space.

So when they're dealing with their suppliers, companies that manufacture pet food and other pet products, they're probably in a relatively powerful position. That probably means they negotiate great payment terms.

So I bet their accounts payable is way ahead of their inventory numbers.

So that's why we've got negative operating working capital. So to calculate it, if I look at the assumptions, it's a very fair way to calculate it. I've got 7.3% of revenues.

The number's not been that noisy. It's been relatively stable. Okay? So 7.3% multiplied by the revenue.

Seven point three percent multiplied by the revenue number above.

And then the CapEx is 4.3% of revenues mentioned there. Okay? Hopefully, that looks okay.

Now, hopefully, I've kind of copied everything out appropriately.

I'm going to do a little test. So let's just control all this out.

These numbers look okay. Put that back for now. All right.

Now we can do a cash flow statement, and it's not a full-on operating, investing, financing cash flow. It's far more abridged than that.

I'm going to start with net income, which is fine because we've done the income statement above. I'm going to go and grab the DNA, 107.

I'm going to get the change in OWC. So if I say equals operating working capital in the previous year was negative. That means it provided funding to the business.

And it's become even more negative. It means it's providing even more funding.

So this has to be a positive cash inflow, and it is 8.3.

The CapEx is 64.3, but I've got to be careful to make sure that I multiply that by minus one because it's a cash outflow. And then alt equals, equals.

The cash flow available for debt repayment is 201.8.

Now, it is not the cash flow available for debt service.

You might hear people go about CFADS. It isn't that.

Because the cash flow available for debt service would deal with the interest and the repayment. But we've already dealt with the interest by the time we get here because it starts with net income.

And of course, net income sucks up the interest when we populate that.

So to be pedantic about it, cash flow available for debt service, debt repayment rather than debt service. Okay.

Let's do what I would consider to be really the main event, and that is the debt schedule. It's a big output from this model. Let's just copy this all the way down.

Okay. So the first thing is we're dealing first with the senior debt because it's senior, we have to pay this down first.

It might be a lower interest rate, but that's kind of the point.

It's a lower interest rate because from the lender's perspective, it's less risky for them. And it's less risky for them because we're obliged to pay them back first. So what I want is I want to know the ending debt balance in my projected year.

And I don't know that number, which is a problem.

But it occurs to me the right place to start would be the beginning balance.

And I kind of think I do know the beginning balance because the ending balance in the prior year would become the beginning balance in this year.

And I do know in year zero the ending balance.

Because if I scroll up slowly and look at the sources of funds, it's really like a linchpin for the whole model, the sources of funds, right? So if we look at the sources of funds, I know that 988.0 was the senior debt balance initially. So that's going to become my beginning balance.

Can we repay any of that? Yeah. I mean, loads of it we can repay at the moment.

Admittedly, we don't have interest flowing through the model yet, but it looks like a lot. You've got loads of cash. You've got 201.8.

So why don't you choose the minimum of cash available for debt repayment comma and beginning balance? It will choose the lower number.

And I'm going to multiply that by minus one.

Okay? So if the cash available for debt repayment was, say, 1,000, then it would repay all of the debt. If it's only 201, it'll only pay 201 because it chooses the minimum of those two. Probably useful to calculate the interest expense now.

So if we're going to sell G60 and say equals, we're going to need an interest rate.

If we scroll up towards the top of the model, we've got some assumptions on the left-hand side. And if you look in F14, I've got 5.92%, which is the cost of senior debt, the interest rate on senior debt. I'm going to press F4 to lock that, and then I'm going to multiply it by the average. A-V-E-R Tab.

The average of the beginning and ending balance.

And I've got 52.5. What is the cash available For debt repayment. Really is like a running total, I guess.

Well, we had 201.8, so I guess, do we have 201.8 cash available? Nope, because we've paid 201.8.

So if you grab those two numbers, we've got nothing left, and that's cool. You wouldn't expect in the first year to fully pay down all of that senior debt and then start chipping at the unsecured notes as well.

These things take time. Okay, I'm going to do exactly the same for the unsecured notes. I'm going to go and grab a look at the prior year-ending balance. Now, if you look above for the senior notes, I had 988.0. That was from the sources of funds at the top, and that was in 07. So I feel fairly confident that if I type in 08, 08 will give me 494.0, and we can go have a look at that.

So in our sources of funds, it's 494.0, and that is our unsecured notes as being one of our sources of funds. I'm going to move to that diagonal league.

For the repayment, I'm going to say equals choose the minimum of the cash available, which is zero, and the beginning balance.

And if I close the bracket and hit Enter now, I made an error, but it's not obvious that I've made an error. It's dangerous to do this maybe.

So I'm going to do a bit of stress testing.

I'm going to say, what if you had 100 million of cash available? You would pay...

Oh, no. You'd pay back 100 million, but the repayment is supposed to be a negative number. And of course, what I've forgotten to do is press F2, multiply by minus one. I need to make it a negative number.

And I'll just highlight to you that when we're modeling, it's always dangerous if we're dealing with zeros, because you don't really know at first glance if it's been forced to be a negative number or forced to be a positive number.

Anyway, that is just me messing around. I think we're probably all good with that.

And now on the interest rate, and I'm going to multiply this by the average balance.

So if I say equals, and we scroll further up the model, the interest rate on the unsecured notes is 8.25%. It is in F15, 8.25%. I'm going to multiply that by the average, AVER tab, of the two ending balances.

I've got 40.8. Okay, do we have any cash? No.

This is strange. If you look left, I've hard-coded in a zero there.

Why have I done that? Well, when you think about the target company, Pets at Home, before we bought that company, it had some debt which we're going to refinance, and it may well have had cash on its balance sheet.

So if it had cash on its balance sheet, I'm going to assume that we take all of that cash to pay down as much of the debt as possible, and then we just refinance any of the surplus debt over and above the amount we've paid down from the cash on their balance sheet. And you could say, well, you wouldn't want to take all of the cash off the balance sheet. You'd want to leave some for operational purposes.

And that is true. But building that level of detail in, it's not going to move the IRR in any meaningful way. So I'm fine just saying that the ending cash would be zero. What would the ending cash be for this year that we're modeling? Well, didn't we have cash available, after the senior debt repayment of zero, plus an unsecured note repayment of zero.

So I should-- Sorry. I'm going to take from the prior year, and I'm going to add the cash flow available for this year, which I might actually just pop in brackets only from a kind of visual point of view. So you can see in F69, I'm taking last year's cash balance of zero, and I'm adding zero to it. But we might have a situation as we copy this out to the right, where they have cash available after the senior debt repayment and the unsecured note value might be lower.

So we might have cash remaining at the end of that. Not the case yet. Okay.

We should calculate the interest rate. So if I say equals, let's go up the model.

We're going to grab... The interest rate is in F16.

It looks to me like it's 3%. I'm going to lock that with F4 and multiply it by the average balance, which is here.

I don't need to multiply that by minus one. I've locked that with F4.

I just noticed that as I was chatting away, the interest rate on the unsecured notes, I haven't locked. So I'm just going to do that. Okay. Right.

Now, there's a section here that says beginning debt repaid, senior debt repaid, et cetera. And there's a note down here that I've created.

Let's go and have a look at this note.

If I say Shift and F2 to show the note, it'll say 50% pay down minimum required within seven years for credit committee to approve.

So we might have bank examiners looking at this, and our credit committee might say, "You just can't lend into this deal if they can't, within seven years, pay at least 50% down," which is a pretty typical rule in the US.

So we're going to proceed on that basis.

We're going to try and figure out how much they paid down.

Of course, in year zero, the pay down is nothing.

So the beginning debt pay down is zero.

But we're going to capture every year the repayment of the senior debt, which we're going to multiply by minus one, and the repayment of the unsecured notes, which we're going to multiply by minus one.

So kind of cumulatively, we'll get the repayment to date building up. If we go further down, what is the... I think I'm going to do this here. I'm going to just go to the left here. What is the total debt at acquisition? Well, it's 988 of senior debt, F4. It might doesn't really matter, actually. I don't need to lock it.

Plus 494 of unsecured notes. So the debt is 1,482.0. What is the pay down? Well, it's 201.8 over 1,482.0, which I am going to lock using F4.

We've repaid 13.6. Yeah, we probably haven't, because one of the things that we definitely want to think about is in the income statement, we haven't filled out the interest yet. Now, if I fill out the interest, particularly the interest expense, well, in both of them, but the interest expense, then the net position is going to drag down net income.

Net income goes down, then that means that the cash available for debt repayment goes down, which means that the debt repayment goes down.

If the debt repayments goes down, we're probably going to have a bit more interest, and that kind of creates a circularity.

So the focus of this session is not to explain the circularity, but there's some cool content in Felix if you wanted to explore that further, perhaps if you're not that familiar with it. I'm going to build this using a circular switch.

So I'm going to say equals if, I'm going to hit Tab, and then I'll use my mouse.

If I go to the info sheet, and there's a cell here which has been named Circular Switch when I click on it.

If that equals one, if it's got a one in an Excel, comma, value if true.

Let's go back to the LBO one sheet. Go and get the interest in G60, the interest expense in G67, comma. What if it doesn't have a one in there? Just return a zero, because that will kill the circularity.

It's like a circuit breaker for us. Close bracket, multiply by minus one.

Nothing profound happens because the switch is not set to one.

What about the interest income? I'm going to do the same thing.

I'm going to say equals if, Tab, C, I, and it guesses the name, so I can hit Tab again. The circ switch equals one, comma, and then go and get the interest income.

Where have I put that? My goodness, where's it gone? There it is.

In G70, go and get the interest income, comma, zero. Okay.

Hopefully, that is all good. Hopefully, everyone's happy with that.

I wonder if I can go into the chat box and post pictures. Yeah. Okay.

I can post that into the chat for your reference if anyone wants to have a look at that.

Okay. Right. I don't know if that displays well, but I put a screen grab of that in the chat box. Okay, if I scroll down...

We are so unbelievably close to get this model finished now.

What I would like to do, if I go out, kind of zoom out, I would like to... Yeah, I would like to grab the model And Control R this out. And it is absolutely possible that as I've been chatting away to you guys, I might have forgotten to lock something or picked up the wrong cell or something like that.

So I'm going to have just a very quick eyeball of these numbers.

I don't have anything particularly to compare them to because I don't have an answer in front of me, but I'm just seeing if they look reasonable.

It's a pretty aggressive paying that debt down.

Look, I had an idea that Pets at Home would be a good LBO target, and it probably is. But I'm being a little bit enthusiastic here because we don't have interest flowing through yet because the switch is at zero.

But I think these numbers look okay.

If anyone thinks otherwise, then I will absolutely concede that.

It would be a good time to jump onto the chat if there's anything, any questions.

But while you're thinking about that, I'm going to press on with the equity returns, which is the last thing I'm going to do. So if you go down to row 81.

If you go to F81, I'm going to hard code in the zero, and to the right-hand side, I'm going to grab that zero and add one.

I will show you my formulas here.

Okay.

I'm now going to get the enterprise value.

I want to track the enterprise value exit.

I really want to pull the number through or the correct number through if the year that I'm in marries up to the exit year, and we can talk about that in a couple of minutes. But I'm at least going to figure it out.

So if I say equals, if we were to exit in year one, what might the enterprise value look like? Well, that would be the product of an exit multiple, which is right at the very top of the model. Got an exit multiple in F10, eight, we press four to lock that. Multiply by whatever the EBITDA is, and I've got to be careful here.

Could make an error here quite easily.

If you look at the key numbers in G43, we've got EBITDA of 254, but that comes from consensus that is ignorant of the benefits, cost-saving benefits of this deal. So if we exit, I would hope that we would be picking up the EBITDA after the cost savings. I hope I picked up the right number there.

Let's just double-check that, shall we? So let's say Control and open square bracket. Let's... Yeah, there we go.

Let's jump down here. Now where did I get that from? Does that EBITDA number pick up? No, that is totally not right. Yeah.

So that is a genuine-- I'm not doing this for dramatic effect, although sometimes I do things for dramatic effect.

Genuinely done something silly there. Yeah.

So if you look, I picked up the EBITDA margin.

I picked up the DNA, and then what have I done here? EBITDA after cost savings.

That's ridiculous. What I should have done is, I should say equals, I should pick up the EBITDA, and I should go and grab the cost savings. Okay? That would be a much better number.

It wouldn't be sensible for it to jump up so much. All right.

So I'm glad I'm actually thinking about this. We're not being too robotic about it.

Oops. Not being too robotic about it. Okay. All right.

What's the net debt? If I hadn't picked that error up, the IRR would look ludicrous, and hopefully, that would be very obvious. Okay. Net debt.

Now, the whole of this debt schedule we built earlier was for us to be able to go and grab the ending debt from the senior debt and the unsecured notes.

And it is the net debt, so I'm going to subtract the cash from that. Okay.

And that should give us the equity value.

Enterprise value minus the debt gives us the equity value.

And if we scroll down, the question is, what do we invest initially, and what equity falls out of this? Well, I'm going to copy. If you don't mind, I'm going to copy this out to the right. Let's copy this out to the right. So we've got various equity values.

And then if I can color in purple in year zero and show you... Where am I going to do it? Maybe over here.

Let me show you my formula. Hey, what did we invest in year zero? Equals.

Well, we talked about it. If we scroll up and we go to the source of funds. Our equity issuance was 533.5.

We're going to multiply that by minus one.

These are going to be big numbers. We're going to need to turn our interest on to do some proper analysis. Now I only want the equity now to drop down for the year that matches the exit year assumption. So you might say, "What do you mean?" Well, if I say equals in the cash flow to equity holder cell, if open bracket.

Now, if I scroll up to the top of the model here and I click on F18.

Let's pause on that for a minute. F18 is the exit year assumption.

I'm going to press F4 to lock that. So if F18 is equal to G81, which is the year I'm in. If they're the same number, comma, go and grab the equity value.

Otherwise, grab nothing. Otherwise, grab zero.

And we should be able to copy this out.

I'm going to show the formula above so you guys can see that.

And I'm also going to just grab... Let me just grab that formula, and we can just chuck that here in the chat box as well. Okay. So I'm going to copy that out to the right.

Right. So what this is doing is it's saying, "Hey, there's no equity coming through in year one or year two or year three or year four." There's none.

Sorry, year three, but there is in year four because year four is our exit year.

Now we're going to calculate our IRR, and I hesitate to interpret this because we don't have interest on yet. But I'm going to go into cell F87, say equals IRR, and grab all of those numbers. Yeah, all of those equity numbers. And I get 44.1.

It seems a lot to me.

Let's turn our interest on because we're going to have a lot of debts.

We're going to have a lot of interest.

So if we go to the info sheet and the circular switch, we turn it into a one there and then go back, we get 39.8. Still seems a bit high to me, but never mind. You might say, "Well, Jonathan, didn't you make up the number on the cost savings?" Yeah, I did. So we can go back to the cost savings and say, look, 3% of revenue seems fairly high. What if it was more like 1% of revenue? Well, if it was 1% of revenue, it still looks like a pretty good deal. Okay.

We're going to sensitize a couple of things. We've got three minutes left.

So we're going to build a data table.

Data table is Alt, A, W, and T for data table.

And on the very top left of the data table, I'm going to bring down the IRR number. I'm going to look at the IRR here. We're going to grab the entire table and say Alt, A, W, T.

And Excel will say, "Ah, you want to sensitize, Jonathan?" "Yes, I do." And it'll say, "What do you want to show?" And I'll say, "That's crazy.

You know what I want to show. I want to show the IRRs on the top left." And Excel would say, "Ah, right, okay." So what have we got here? Let's just do that again. What have we got up here? And I'd say, "Well, these are the exit years.

I want to take these numbers and substitute them into the model and rerun the IRR." So Excel will say, "Well, what cell do you want me to put that in?" Well, if you go up to, say, the exit year is in cell F18. So take the three and put it in F18.

Take the four, take the five, and rerun the IRR.

And the same idea for this column of inputs here.

Take these entry multiples, whatever these are, and substitute them into the entry multiple assumption in F9. And if we press okay, ugh, it doesn't do anything for us. And that is because in Excel's options, we need to turn on automatic calculation. So if I click File and I click Options and I click Formulas and I click Automatic Workbook, and then click OK, that should recalculate various IRRs for us.

I think that IRR looks a bit high, and so as I'm chatting away, quite probably there are maybe... Let me just check. Yeah, there's probably an...

Oh, I know why. I know exactly why. I don't think I updated that. There we go.

Let me just copy this out to the right. If I copy that out to the right.

There, cool. I've updated the EBITDA number, and it's 29.8.

It's still a great deal. So I challenged myself to find some good IRRs on UK European mid-caps, and I came up with a few different companies. You can look through the rest of the file if you want to see, to explore the other companies that I selected.

With the work that we've just done, you'll be able to replicate that across the other models. Guys, we hit the hour, so I'm going to say thanks ever so much for being dialed in. Really great to have you on.

We've got loads of people on this call. Really good.

Hope you enjoyed the session and look forward to seeing you in the future.

Cheers, guys. Have a nice weekend. Cheers.