You can hopefully see on the screen. My name's Phil Sparks, and this is a session on Complex LBOs. Uh, so it's a fairly busy session, so I'm not gonna spend too much time, uh, preempting and, and introducing, uh, what we do. Uh, one thing I will just do is check on participants. Um, unfortunately, as soon as I share my screen, zoom, then helpfully, rearranges the entire screen. Uh, so I then have to spend a little bit of time just rearranging where all the boxes go. Uh, but, uh, welcome to this. Uh, welcome to this session. You'll see there's lots and lots of files, and they're all about a company called Devons, which, which I'll introduce in just a a moment. Um, the one that's really important that you get hold of, um, is one that says webinar at the end. And that's the, uh, that's the spreadsheet we're actually gonna look at, uh, for the next hour or so. Uh, so if you can use that, um, use that one. Uh, I'll just put a little note. Uh, so basically, oh, uh, I'll try and type it correctly. Web in, uh, with an R at the end, though. We, our webinar. Um, so if you can grab, uh, that, uh, URL, um, uh, go to that URL you should see about, there's about five or six, um, spreadsheets there. Um, they all look at Debs and the complex Debs, LBO from slightly different perspectives. But the one that's really important that you have a look at is, uh, the one that you get, that you get is the one that says webinar at the end, um, and then one of the ones that says full at the end as well. 'cause that gives the full, uh, solution. Um, but I'd just suggest down, download all of them. Okay. I'm just gonna put the, uh, URL in the, um, in the chat box, and I'll do that once more before we actually start looking at the, uh, spreadsheet. So, uh, first things first, let's just introduce, um, what we have got here, um, and why we're using a company called Web, uh, Debenhams. Well, Debenhams, um, is, um, as, uh, anyone from the UK will, uh, hopefully remember, used to be a very big department store. So if you're in the us something like Macy's, um, perhaps not quite so big and quite so, um, um, uh, substantial. Uh, but it was a fairly big chain of department stores in a lot of uk, uh, major cities. Um, and it got into real trouble in, uh, the, uh, two thousands and the 2000 tens. Um, and the reason we use it is because there was a lot of information in the public domain. So basically there was an LBO takeover and then another attempt at a takeover and a rescue. So there's a lot of information in the public domain, uh, that we ba basically managed to get hold of and use as the basis for our models. So we've continued to use it, um, just 'cause it's a, it's a, it's a real life company, um, with quite an interesting story. Um, as I say, there was lots of information about the LBO that happened back in, uh, the late, uh, 2010. So it's 2016, something like that. Um, so that's why we are using it. And, you know, the fact that it's a handful of years old, seven or eight years since this real transaction, um, doesn't destroy the, um, uh, the kind of credibility of that transaction and the lessons we can take from, uh, that transaction. So it's a fairly complex, um, uh, uh, LBO uh, transaction. The second thing is, uh, we've actually got two versions of our LBO. You'll see that in some of the titles. There's a thing called basic, that's the one we're gonna do today. In reality, it's a very complex LBO, but there's a slightly cut down version, uh, called basic, which is what we're gonna be looking at, um, for the next, uh, uh, 45, 50 minutes. Um, there's then a full version that doesn't say basic, which is even a little more complex and has some extra overlays, like things like, um, uh, sale and, uh, leaseback. But we're gonna dive into the one that says basic. Um, which as you'll see is already a pretty meaty, a pretty complex, um, LBO transaction. First thing. The second thing I'll say on this is also that, um, we are going to, I am gonna rely on the fact that most of you are gonna know how an LBO transaction works. Um, I did one of these sessions a week ago going through LBO fundamentals, looking at a relatively basic LBO, um, this is like that, but with lots extra. Uh, so I'm assuming that pretty much everyone knows their way around the basic LBO transaction. If not, go and have a look at the recordings from a week ago. Um, and that'll basically take you, take you through a more basic, a more fundamental, a more standardized, um, LBO transaction. But we are gonna sort of move on to something much more complex, um, right now. So where are we? Um, I can see, uh, we've got plenty of people online. That's great. I'm just gonna put, um, on the, uh, chat box. And the reason I put this in a number of times is if you join a few minutes later than the start, you don't get to see, you don't see the chat box, um, from the, um, uh, from five minutes ago. Um, you only see, um, uh, what's been posted on the chat box since you, uh, joined. So I've just put another link to the URL where you can grab hold of this material. And the important one to grab is, um, there's a file, an Excel file that says webinar at the end. That's the one to take. That's the one to take. So, um, there you go. There's the URL, I just see one more person just joined. So there's the URL, uh, go and grab the, uh, spreadsheets from there. And also make sure you pick up the one that says webinar. Okay. So, uh, off I go. I'm gonna drag over onto my screen and hopefully I've got it here already. Um, I'm gonna drag, I'm gonna grab the, um, There, it's, um, oh, that's, uh, my spreadsheet has, Um, just decided to hide itself. Just let me go and, Um, grab my spreadsheet.

Here it is. Okay, so here's the spreadsheet, uh, that I wanted to look at. Um, and I just see one more person's just joined. Um, so I am going to just do that again. Just put the, um, uh, the URL in the chat box if you've just joined. And you want to grab the, uh, spreadsheet we're working on, it's in that link. Um, and it's the one that says webinar. Okay. So let's go and have a look at this. So, um, you'll see it's a much more complex LBO than the one we did last week where we just looked at everything. Everything was in one page. This is multiple tabs 'cause it's a much more complex LBO. And you can see income statement, balance sheet and cash flow and debt. They're the real, the sort of operational, really working, um, elements, uh, tabs on the spreadsheet. And then we've got, um, LBO, which is what we're looking at at the moment, inputs, assumptions, and then calculations. So let's just walk through this, uh, briefly. You can see that I've highlighted in yellow some of the, uh, key, um, elements of the, uh, spreadsheets, um, uh, things to just have a little look at. And up at the top, what we've got is some basic assumptions up here. Um, if I just sort of highlights down here. So we've got last year's, um, uh, LTM, uh, that's actually in 20, uh, 15. It's actually in 2015 that last year's LTM. And that enables us by multiplying by seven and a half times, which we assumed, um, EV to ebitda. It gives us an enterprise value for acquisition. We then add back the net debt. We're assuming that we refinance the net debt on the target company, and that gives us an equity figure there. Um, I'm not just gonna walk through this. We actually do have some, um, uh, well, we're actually gonna complete it, particularly the parts on debt and the parts on return. Those are the parts that I'm gonna look at. The rest of it is much more standardized. So I've pre-populated this. Um, we also have over here on the right hand side, um, some basics, uh, like, uh, some fees at 3% of EV, L-I-B-O-R, uh, which was at the time, the base interest rates, um, rates on eight year Gil, it's a UK based company, eight year Gils are basically, um, us, sorry, UK government debt rates. Um, we've got an assumption that we exit in year seven, and we've got an assumption, and this is a little bit more complex, um, than the simple LBOs. We're assuming that when we generate cash, we're only gonna use 75% of that cash to make accelerated payments against the, uh, various tranches of, uh, debt. Um, so we're gonna gradually build up a little bit of a cash buffer rather than emptying out, um, the cash in its entirety. This is another way of basically keeping a minimum level of cash. Um, we could do it with just a, you know, 1% of revenue as a minimum level of cash or something like that, that would work. Um, we've then got, uh, a normal sources and uses of funds. So this, the uses of funds are basically the enterprise value I, the, the equity plus the refinance debt and some fees which are calculated there, the 3% of EV. Um, and then on the right hand side, this is where it gets much more complex, so worth pausing at this point. So rather than just having one or maybe two forms of debt, this starts to get much more realistic. Um, and this is what really happens back in 2016 when this LBO actually happens. So we've got two significant term loans, um, with first lianne and a second lianne in obviously increased, uh, sorry, decreasing, uh, seniority of 400 million pounds and 500 million, uh, pounds. We then have potentially some junior notes. We're not gonna take any out at the moment. Um, but it's there as an alternative. Um, basically this spreadsheet, the assumption is this spreadsheet was put together, this model was put together at the start of the negotiations, um, to work out whether this is a good LBO or not. Um, we've also got some mezzanine debtors. Well, um, so debts with associated warrants, um, for conversion into equity at the, um, at the exit points. And then, and again, a little bit more complex. Again, we've got two forms of equity got press, which is the majority of the, um, institutional funding. Um, and then in really small amounts of equity. And the reason why there's equity, and again, this is very typical of a an LBO transaction, is that it enables you to really motivate the management. So the management, um, and we'll look at this in just a second, basically take on 10% of the, um, common equity and the institutions have the other 90% of that common equity. And so equity, we are hoping that the rate of increase the IRR on that common equity is extremely high, which will basically increase the return to the institutions and also make it very lucrative, very motivating for the, um, uh, for the, uh, management. You'll see over here that we've got, um, rates, interest rates associated with each of those forms of dates, debts, which are basically, uh, spread on top of the, uh, the base rates. And you'll also see that the, uh, mezzanine and press are payments in kind pick notes. So basically, uh, the interest accumulates on those rather than being paid in cash as it's as, as were the other form of debt. So again, much, much more realistic, much more like a normal big LBO transaction with a complex, um, set of debts. And that's because it was, it was a real LBO. This is exactly how it was, uh, structured. There's just a little bit more over here, um, which is on entry. You can see that we've got that 90% of common equity owned by the institutions I, the PE firm, um, and 10% owned by the managements. Um, it's only 10 million pounds in total. So the management are putting in 1 million and the institutions are putting in 9 million. But the institutions are also putting in that balancing figure, which is why it's in white rather than blue, which is 622 million of press at 12%. That gives 'em the, the, the, the sort of the, the base rates, um, the kind of the low points of, um, of, of their return. But hopefully they'll make a great deal more if the common equity grows significantly. Um, at exits, um, we have pretty much the same, but not quite, because we have that 5% warrants on the mezzanine debt, um, which is basically taking a little bit of the, um, a little bit of the, uh, common equity. Um, ca can recordings be found on, on on fie? Yes, absolutely. Um, uh, Hugo, uh, recordings for today's session, uh, will be on fe. Um, it'll take a couple of days for it to be uploaded, and there should be a recording for the session I did last year on, uh, last, sorry, last year, last week on, um, the fundamentals of LBOs. Um, just one point I, I often don't see the q and as because it arrives in a separate points, um, within, um, uh, within, um, uh, zoom. So the, the chat box does arrive, uh, the, uh, q and as don't always. Okay. And then finally over here we've got a Goodwill calculation. Um, and you can see that the identifiable assets are linked to the balance sheet. We'll go and look at the balance sheet very quickly. Um, but basically that Goodwill a is just standard goodwill calculation consideration, less than less assets for the, of all the assets acquired. Um, and that enables us to construct a balance sheet that does actually balance. Now, let's go and have a look at some of the other tabs before we launch. Insert dealing with the debt. First of all, we've got some inputs. Um, so we've got normal revenue growth, sg and a cost of goods sold, um, receivable days, accounts payable days, all that kind of stuff. The normal assumptions you would have in doing any standard three statement model. Just worth making the point. Um, we've got a section up here that says management case. If I clicked on the download, there's a bank case. Um, and there's a management case. And basically you'll see that this sg NA line, uh, changes the bank are being a little bit more pessimistic, higher sg NA costs, um, than the, um, uh, than the, um, the management are being in terms of their forecast. I'm gonna leave it on management. Uh, so if you wanna follow this through, if you wanna complete parts of this at the same time as I do, then I'd leave that on at management calculations. Jump into that one. We've got our normal standard three statement model sub, uh, calculations for PP and e equity, um, and, um, uh, operating working capital. Just worth making this point. Um, uh, we don't have preference dividends in here because we haven't done the debt schedule. When we've done that and we've calculated the preference dividends, I'm gonna push them back into this section. So preference dividends here are not being treated as debts. They're being treated as e sorry, preference shares are being treated as equity, not debts, and therefore, uh, preference dividends are not within the interest section of the, um, income statement. They're separate thereafter. Net income in the, um, reserves, movements, um, income statements, all pretty straightforward, basically just you can see on the right hand side, they're all just linking into that input tab and basically got revenue growth, cost of goods sold, gross profit, all that kind of normal stuff. Nothing really, um, out of the ordinary hit it's worth just making the point up at the top. Um, we've got, uh, standard headings. We've got matrix integrity between each of the tabs, um, but we've got this sort of gap in between the last historic year, which is 2015, and the first projected year, which is 2016, to enable us to construct particularly, um, the balance sheet and the debt schedule. Um, because both of these are a little more complex. Stuff happens in between, um, that, um, in between, uh, that, uh, that those periods. Um, let's gonna look at the balance sheet. And that's, it's worth pausing a little bit here. Now, some of these just carry straight across. We say that the historic accounts receivable, the closing accounts receivable, for instance, 24.9 becomes our opening 24.9. And so what we're doing here is in this transition, we are assuming that the LBO transaction was done on the 1st of January, 2016. And so what we do is we have the closing figures at the end of 2015, and then we create a new, uh, balance sheet on the 1st of January, which reflects the transaction, and that then drives the balance sheet for the subsequent, uh, years. So some of it is fairly straightforward, so stuff like inventories and accounts, um, uh, accounts receivable doesn't change, whereas cash does. So I've basically got cash, I've got, uh, revolver and I've got existing debt. And, uh, remember I'm refinancing the debt. So I take over this business, I refinance the debt, so all of these go to zero. You can see that they all, uh, subtract the amount on the left hand side, go to zero. And then, um, underneath I've got these items, I'll put 'em in blue, which is the new debts, the new debts we're constructing to run the LBO. And these are amounts, again, you might just recognize, you know, 400 and 511. Those are the first, uh, two term loans, the first lean end debt, and the second lean end debt. Um, you'll also see those preference shares. Um, we're putting them down here within debts, but in reality, uh, we're treating them as equity. Uh, the preference dividend goes through, um, after net income. Um, and we've also got that mezzanine debt of 300 as well. That becomes the opening balance sheet at the, on the 1st of January. Now, worth mark pointing out that basically we haven't done the debt schedule. So there's two things we're gonna do over the next three quarter of an hour. The first is we're gonna do the debt schedule, and then the second element is we're gonna go and look at the returns to the, um, institutions and all of the other stakeholders. Um, so we have a link here to debt, but it's empty because we haven't constructed the debt, uh, schedule. And therefore when we get to the cash flow, the cash flow's gonna look really strange because the cash flow is assuming I'm starting with debt of 400 at the beginning of the year, and then at the end of the year, it's become zero. I have used loads of cash, I've used 400 of cash to repay that debt. So the cash flow's gonna look really weird. It will fix itself as we gradually populate the debt schedule as we work out how quickly we can repay some of these pieces of, uh, debts. Final element down here is, uh, equity. And again, what we do is we've got the 10 of new equity. Remember one of that is for the management, nine of that is for the institutions. Um, and we also have that minus 9 0 7. So we're basically getting rid of all of the old equity. Now, how that actually happens is normally you create a special purpose vehicle that sits on top of the existing business, and it's the equity of that special purpose vehicle that is the equity that we're, um, including and consolidating in here. Okay? So there we are. That basically gets our, um, equity. Now, it is just worth making the point, there's a slightly odd number in here. We've got negative equity, and that's because it's both shares and retained reserves. And the reason why it's negative is because we've basically, we've incurred those fees to actually set up the, um, LBO in the first place. And those have been parked in the income statement, gone through the income statements, and therefore, um, hit retained earnings. Okay, there we are. So that gets us a basic, uh, balance sheet cashflow. Um, again, most of this is fairly straightforward. Um, when you've seen, uh, a three statement model cashflow, it looks pretty much, uh, like this. Just a couple of things to notice. The first thing is, and you can see all of this just links into the previous schedules, um, the thi first thing to note is, as you can see in this first period, I've got an enormous, a huge cash outflow. And that reflects all of the fact that that debt that I've taken on at the beginning is currently showing a zero in the projected years. So it looks to the cash flow as if I've repaid all my debts. And therefore that's why we end up with an enormous, a huge negative cash figure at the very bottom end. It won't look like that when we've populated the, um, uh, when we've populated the, um, uh, the debt schedule. Also, one final point worth making is if I look back at the balance sheet, you know, as a, as a historic accountant, as an accountant, that's my background. Um, this one really feels unsettling. I've got a balance sheet that doesn't balance the opening combo balance sheet balances, but not anything afterwards. And the reason for this, that number is in the opening figures. So if I just simply look at the mezzanine debt and the press, you see it's 9 22 in total, they just disappear because they are not being paid in cash. Therefore, um, my balance sheet doesn't balance when I populate these items going forward. Um, it'll be okay, my balance sheet will balance. Alright, let's jump forward then to debt. And here is a very different looking schedule. Basically it's an empty schedule. So I'm gonna start off right at the top. Let's just have a little look quickly at the shape of this thing and then let's start plugging in some numbers.

Okay, so let's go and have a look at our debt debt, uh, schedule. So here it goes. Um, we've got, um, normal, um, uh, calculation of cash available for debt service. Um, and we've got mandatory, uh, debt payments, but we've got mandatory debt payments, which are expressed in a slightly unusual way. Um, rather than just saying these are the absolute amounts in pounds or dollars, it's basically saying that our mandatory debt repayment is in a percentage of the opening debt and that's just another way of, uh, of doing it. Um, so let's start with the cash flow. Um, let's start with our cash flow. So the very first thing we're gonna do is we're gonna basically pick up last year's brought forward cash because that is then available this year to make use of, to service our, our debts. So I'm gonna start in this top left hand side and I'm gonna say equals and I'm gonna go to my balance sheet and it's one of the very few times when I don't look at the same column, I look a column to the left. So I'm gonna go and look at column I and I need to find the cash balance in column I. So here it goes, I hit equals, I hit control and page up and I go back to my balance sheet. Where's my cache? It's somewhere up here. There it is. It's the opening cache. So we were in column J when pointing at column I the last year, and it's zero, no surprise because basically we're, um, no surprise because basically we're refinancing all the debt. Um, uh, we're using all the cash we possibly got to refinance the debt. We're not gonna start with a bundle of cash, uh, left in the business. So that's our opening cash. We are then gonna point at the cash flow. And you'll remember in constructing cash flow available for debt service, what we do is we say, what did we generate before any interactions with any of the finance providers? So what's our operating cash? What's our investing cash? Add all of that up and that gives us the cash we've generated at the end of the year before we can then make repayments against the existing debt. So I'm gonna say equals go and find the, uh, cash flow, which is here. Go and find the operating cash flow, which is that a hundred and at 98, um, do I believe that? Um, just let me check. That's not the number debt service. Yep, it's, I agree with that. Okay, so I've got 198, um, here. Um, and then, uh, I do exactly the same with the investing equals age up. Let's go and find that 80, um, which is the investing cashflow. And then you can see underneath that, that's all of the interactions we've got with our, um, finance providers paying off the debt, drawing down the debt and so on. Add those up. And that gives me, um, uh, a cashflow available for debt services of 118. Okay? Um, now what I'm gonna do now is I'm gonna point to some empty cells. I'm gonna point to some empty cells. And this is always a little bit unsettling and it, the, there are different ways to do this. You could either not do this and then when you've calculated the mandatory debt repayments lower down, push them back into this, um, area here. Or alternatively, I'm gonna point to some empty cells. Um, and you'll remember from uh, three statement modeling with iterations and three statement models with a cash sweep that if we have mandatory debt repayments, we have to pay those, they have to be paid before we then look at whether we can accelerate any other payments to any other of the, uh, debt uh, providers. So we've got to do this, we've gotta take these off first, but we have to do the mandatory calculation within each tranche of debt to work out what that's going to be. So here it goes. I'm gonna say my first Leann mandatory debt repayments. I'm gonna scroll down to the first Leann calculation and I'm gonna make that equal to 32 to I sorry, J 32. Then I'm gonna do the same with second Leanne and junior notes equals, let's go and find second Leanne at debt. Um, and it's that one there. It's at 41 J 41.

And the same thing with the junior notes. Um, and I know these are zero, um, at the moment, but if we do and put some junior notes in, then again we might need to point to these. And so that's J 48. And then what I can do is add everything up and I can say equals some of the items above, um, plus the, um, cashflow available for debt servicing up here. And I've obviously, uh, I put a full stop, uh, rather than a comma. There we are. And that gives me 118. And of course this will change as I do each of my calculations and do some of those mandatory debt repayments. Uh, this will change. Okay, so let's go. Uh, so the first thing we're going to look at is we're going to look at the revolver balance. Now, we don't have any revolver balance, but basically we, it's important that we do this. It's important that we consider the revolver because, excuse me, because basically if we have to make a mandatory debt repayment and we haven't got enough cash, then basically if we've got a sufficient revolving credit facility, we can draw down against that. Um, so it's important this works. And you'll see, um, this will actually populate a little bit and you'll see us drawing down on the revolver as we're halfway do through doing this, um, debt calculation. So I'm gonna start with the revolver, the opening revolver, and therefore I need to go back to my balance sheet and I need to go and find basically the, um, last, uh, the, the, the opening, um, um, uh, revolver balance. So I've got an equals I need to hit page up, go to the balance sheet, let's go and find our revolver balance, which is down here, which is zero in, um, I 17. And that gives me that number there. And I'll just put that, um, up there. I'll just put that, um, alongside there just so you can see that. Okay. And then what we do is we bring that one forwards and we say our beginning revolver balance is the same as the closing revolver balance. And then we would say if we had any cache, uh, do we wanna get rid of the re revolver? Will we wanna clo, we wanna shut down the revolver or empty the, uh, revolver. Um, but what we're also going to do is that gives us the option to issue or draw down on the revolver as well. So we're gonna do that in our standard way with a minus min formula equals minus min. And we want the lower of either the brought forward balance or the cash, uh, flow available for revolver. So there we are.

And then if we basically had a, an, an opening revolver balance and we've got some cash, then we would use as much of that cash as we possibly could to draw down on the revolver balance. If this number ends up being negative, then basically the revolver will basically draw down on the revolver, um, to, uh, to, to pop, to, to, to finance that, that that gap als equals at the bottom. And there we are. Now I'm just gonna copy those, uh, forwards 'cause we've now got quite a few, uh, lines and I'm just gonna copy them all to the right. And there we are. And it's a little bit odd, unsurprisingly, because, uh, we don't at the moment have an opening at cash, at balance. Um, so, um, uh, Is that, why is that, let me just check balance sheet J five, lemme just go and check my Balance sheet J Five, if that makes sense. Balance sheet JF five, And I think this is, let me just turn off the, um, circular Switch.

Oh, okay. There we are. There we are. So, um, I'm not gonna worry about this in a moment. When we populate this thing, it'll work, it'll all, uh, work out. It'll be, uh, fine. Um, just checking that This does make a little bit Of sense.

Uh, that's why, that's why it's the, uh, the revolver is basically, uh, linking in to, uh, something that the moment has got, um, just a title in its, uh, debt, H 25. Let me just check that. Um, that's why, let me just, um, There we go. Sorry, I just, in dragging that around in moving that, uh, that title around, I just managed to inadvertently, uh, point, uh, to somewhere else. So it's okay, I'm now, okay. Now what's interesting with, uh, this is that, um, my cash flow, um, uh, because, uh, here you can see, uh, that my cash balance, because at the moment I don't have a debt schedule populated. My cash balance is going very negative because it's assuming I need to make those mandatory debt repayments. And it's assuming that I need to repay all of the debt down to zero because that's what sh my balance sheet is showing. So what's actually happening, um, when I get to the balance sheet, uh, when I get to the debts, um, the, the debt service, it's saying my last year's cash is actually negative. It's gone very negative, hence that minus 700. And, uh, therefore what's happening is the, the moment the revolver is saying you need to draw down on the revolver to be able to satisfy that, um, that's not the case. We're okay with that, just don't worry about it. Um, but it just proves that the revolver logic is working. Now, let's go and have a look at the first Leanne. So the first Leanne debt, um, if I scroll down, um, here is the first thing I need is I need a running total as we normally would do in a cash, um, sweep. So, excuse me, I'm gonna go to, um, uh, this, uh, line, uh, here, excuse me. Um, and I won't do interest. I'll show you interest in a little moment. Um, but I just wanna go through the logic of the way that the cash sweep works within this complex LBO. So let's go to this line here, the cash flow at four first Leanne Acceleration. What is that? That's my running total for the cash I've got available for paying down the first Leanne revolver. Sorry, first Leanne, uh, uh, loan. So I'm gonna say equals it's the cash flow for the revolver, that 118 plus any that I used in repaying the revolver, and it gives me that 118 number. The, so now I'm gonna look, go and look at my first Leanne, uh, debt and the first leanne, uh, debt. I'm gonna, again, pick up the opening balance from that combo column when we've just gone into the first year. So I'm gonna, so I'm gonna go to, uh, the balance sheet, I'm gonna say equals control page up. Let's go and find the balance sheet. Where is my first Leann debt? And it's that one, it's that 400 number. Uh, the from line, uh, from column I that gives me 400.

I'll just put alongside equals formula text there. Balance sheet 2020, uh, balance sheet 22. Okay, that becomes the opening balance at the start of the first projected year. And now we have to be a little bit careful because we've basically got two items. We've got the mandatory debt repayments that we have to pay. And you might remember we have this table up here that says we've gotta pay off 12% of that 400 in the first year. And then we're gonna say if we've got anything left in our cash pot for all the year in that 118, then we basically need to use that to accelerate the payments. So we're paying off our debts as quickly as we possibly can. So what we have in the first, uh, 400, what we have, um, uh, is, uh, we need a minus min formula. And so what we actually need is we need to say we are going to pay off the lower of either that mandatory repayment or the balance on the debts if it's lower, because obviously we don't wanna carry on paying the mandatory repayments if we've accelerated payments and extinguish debt. So we need an equals minus min formula. Um, and this is exactly the same as when we were doing three statement modeling with a cash sweep. So we need, i, we need a minus min formula, and that is either the, um, 400 opening position, so function F four, um, multiplied by, let's go and find the 12.4% up here. Uh, and it's the lower of either that or the opening debts just above, uh, it's a minus min, so it says 12% of 400 is 40 at nine. Um, and that we have that number. And interestingly, if you were keeping an eye on this, that was 100, that's, um, that's, um, uh, uh, was, um, sorry, that was 118 in that cell. It's dropped 68.7 because the mandatory repayments has now gone up to this section here, and it's therefore been subtracted before we go through our process of looking at how much debt we've got left over to make accelerated, uh, payments. And we do the same thing here. So now we're gonna say what's the lower of either that debt we've got brought, sorry, that cash available for debt services we've got brought forward, or the net of the balance on the loan at this particular point. And again, just the same as if we had done a normal three statement model with a cash sweep. So I'm gonna say equals minus min, and it's either the lower of, excuse me, the cash available for debts at service just before or 400 plus the minus 49, whatever that is about 350. What's the lower of both of those? And it's basically using up all of that 68.7.

And then we're do an all sequels at the bottom, which adds it all up and says that our closing debt is now 281. And of course I get all of these values because we're now pointing at things that have got, uh, titles in them. They've got, um, um, calculate, uh, uh, formula text, uh, um, functions in them. I'm gonna copy all of that to the right and hopefully those values disappear. And they do. Now let's just have a little look at what's happening. Um, basically we are making these payments and we're extinguishing the debt. Now, one of the things that's happening is we don't have any cash. We don't have any cash. Um, so therefore, um, uh, what's basically happening is it's only the mandatory repayments that are paying, paying down. And the reason we don't have any cash is at the moment the cash flow is looking at the other pieces of debt. So the secondly end debt and saying we're gonna repay all of that in that very first year, which extinguishes all of the, uh, cash, which gets rid of all of the cash. And that's why we don't, um, have anything in these subsequent years to make accelerated payments against. But you can see the logic of this. Now, um, we've got about, uh, 20, 20, 23 minutes to go. So I'm gonna stop on the debt schedule for now, and we're gonna go and have a look at the, um, uh, the outputs. We're gonna have a look at, uh, the returns to the providers. Now what I'm gonna do is I'm just gonna show you what this debt schedule looks like when it's, uh, populated. I'm hoping I've got one of these, um, available. Here we are. Um, just give me a moment. Yes, I have, here's the one.

So just gonna drag this across, um, and open up the debts, uh, schedule. So here we've got the debt schedule is what we were just doing, but now it looks very different because what we've now got is we've got all of the first leanne, uh, populated, and you can see that because my balance sheet now is populated with that debt gradually falling, then I'm not repaying all of the debts, therefore I've got more cash available. Um, and you can see that I'm gradually paying off my, uh, first leann, uh, debt, partly with mandatory and partly with accelerated. You can see also that when I get to my second leann, then, um, excuse me, um, then you can also see that I make some accelerated payments towards, to the right hand ends because I now have some cash left over having extinguished, having got rid of all of the firstly end debts. Now it's worth just a couple of points. First is, um, underneath each of these I've got an interest calculation, which is just grabbing that interest rate from the very first tab, um, and applying it to the average of the debt in the normal way. Um, but then you can see these two pieces of debt underneath and these two pieces of debt underneath, uh, the mezzanine debts, both them are pick notes and the preference share. Also pick notes. So basically in this case, what happens is the interest is actually building up the debt. It's increasing the debt for, uh, both of, uh, these, um, uh, right until the very end, right until the very end. These only actually get repaid at the point, um, of exits. Um, so we need to kind of keep this going and then we'll deal with the exit points, um, on the very first tab. And the same with the preference shares. Again, they're pick notes, they're paid in kind. So therefore the interest, that 12% rate of return on the preference shares is just adding to the, uh, preference share, uh, balance. I've also just got here the total of all of the interest, and you'll also see that there's a difference between the two. The interest in total picks up all of the pieces of debt, um, whereas the cash interest doesn't include the interest on the pick notes on the mezzanine loan 'cause that's just accruing. Um, therefore there's no cash expense. So the cash interest expense goes into the income, into the cash flow. Um, the, um, total interest expense goes into the income statements. This difference between the, uh, two. Now, does that mean that I've got a balance sheet that balances? Yes, it does. I've now got a balance sheet that balances principally because you can see that this, uh, mezzanine and press gradually go up over the course of the, um, of the, of, of the period. Okay, so we've got a balance sheet that balances a debt schedule that works. Um, one other points, um, I've got here, I, I meant I remember making this yellow. So here is my preference, dividends that we're pay, that we are charging. Um, the, um, uh, we, we we're, we're charging the retained earnings with, even though we're not paying these. Um, so these are basically going against retained earnings and in the, and building up on the preference share balance as well. Okay, last step, then let's go and see whether it makes any money for, uh, the investors. So we're gonna go back to our LBO, uh, here, and just let me get my notes in the right place. I'm dealing with the right thing. So the first thing we have got is we've got a, uh, year count, and that's in the right years. It's in that year, um, 16, 17, 18, and so on. And remember we said it was going to be the seventh year that we exit this, um, uh, exit this, uh, uh, this, this business, this transaction. So the first thing we do is we basically go off and we calculate the enterprise value. So the first thing I'm gonna do is I'm gonna go up to the multiple, it was a seven and a half times multiple. I'm gonna lock it with an F four, and then I'm gonna go and find the EBITDA in the income statement. So let's go and find that. There it is in the income statement. I've got an EBITDA figure down here. Um, j is my very first one, uh, my year, um, year 2016. And that gives me a, um, an ebitda, um, oh, actually didn't need the, i, I need, I should have done the EBITDA and then the multiple shouldn't I. Uh, so let me just do that, just get rid of that bit too hasty. There's my 231. Let's go and do the enterprise value, which is the 231 multiplied by the seven and a half, uh, multiple. And I can lock that in with an F four hit return. And there I have the same, uh, the same, uh, figure. I will just put all of these, uh, down. Um, and then we go to the debt schedule and we basically just pick up all of the, uh, all of the pieces of debt. So the first thing I'm gonna do is I'm actually gonna go and add the cash. So I'm gonna say equals, let's go and find that from the balance sheet. Let's go and find the cash balance closing. Cash balance is somewhere up here. There we are. Zero, oops, not that it's the, uh, it was actually J five. Just gonna manually change that. J five gives me 4.9. Let's then go and find the total debt. I'm actually gonna pick that up from the bottom of the debt schedule. So I'm gonna say equals, let's go and find the debt schedule. Closing debt figure, um, is somewhere down here. Closing debt figure is, uh, here, I'm actually gonna make that negative 'cause I, we're going around the EV to equity bridge, so I'm gonna take off that debt, but we don't want the mezzanine. I want to separately, uh, pluck out the mezzanine. So I'm then gonna basically say, what's my, me closing mezzanine debt's 336. Um, and that gives me 846. Yep, I like that number. Um, then I'm gonna pick up the mezzanine. Um, and I'm gonna be, I'm just being really a little bit lazy here. I know that that's the mezzanine element, that debt J 55. So I'm just gonna say equals debt. J 55, I'll put an equals minus at the beginning, and that gives me 336. That's my mezzanine, uh, debt. Uh, what about the preference share? So that's gonna do the same thing. So let's go and find the preference shares at equals. Uh, let's go and find the balance sheet. Here it is. Where's the balance sheet at the end of the first year, somewhere down here. And the preference shares 600 and at 96. Um, so I have a negative, I'm gonna multiply by minus one to make it negative 696. And I think if I add all of that up, then basically that gives me an equity value. 140, yes, I like that number. Okay. So that gives me an, uh, that gives me the things to work, everything to work with, um, for working out the value of, uh, this, uh, business. I'm gonna copy that all the way to the right, and hopefully I've put dollar signs around everything I needed. Yes. Do I? Yes, I like that. So really good stuff. I like all of that. And then I can start working out what's the return to the, each of the, um, investors is. So the first thing I'm gonna look at is I'm gonna look at the mezzanine, uh, holders. So remember, what the mezzanine holders gets is they get this, uh, mezzanine debts, 12% payments in kind every year, accrues 12% interest every year, adds onto that mezzanine debt. They're gonna get that back, that's gonna get paid out, but they're also gonna get 5% of the equity, 5% of the equity, uh, when it gets sold. Um, and we see that from this little table up here. So it's amazing in holds get 5%. So we're gonna link to this 5% just in case we change that assumption in our negotiations. So the first thing we're gonna say is the equity value of the warrants, um, equals, let's go and find that 5% up, lock that in with an F four, uh, multiply by that, by the equity value just above. And that will give us a slightly odd number. And I'm just gonna copy this to the right now, it's okay in the following year. But of course, um, if, if the, if the, if the, if the warrants gave you the rights to some shares and those shares were actually negative in value, you'd say, no, I'm not gonna have that. So I'm just gonna tweak this a little bit. I'm just gonna put a max formula around it and I'm gonna say max of zero or that thing that I just calculated. And what that basically does is it basically means that I only have this, if it's a positive number only have this positive number. And then what's the mezzanine debt? The mezzanine debt is just above that 336 that we pointed to before. Multiply by minus one positive 336. Just copy that to the right, make sure it's working. Yes it is. And then add all of that up.

Uh, alts equals, there we are.

Um, so that would work, but what I'm actually gonna do is I'm now gonna do an if statement. So again, what you might remember is that the, um, so when we're working out our returns, um, we don't actually get these amounts. We don't get 17.1 at the end of the second year, plus 319 at the end of the third year, plus 338. If we walk away, when we exit the transaction, we just get the one year, the one column, which kind of appears in, uh, at the, uh, when we actually sell, when we exit the transaction. So what we want is we want a little if statement that says, if it's the year end, if it's the right year end, if it's the year when we're going to exit, we want this number here, this 336 or 377, we want to bring that number down. Alternatively, we want zero. So I'm gonna put just a little if statement at the beginning of that. So I'm gonna say if the year counter, uh, and apologies, I need to do this with F two. So if the, uh, year counter is equal to my exit year assumption, which was that number seven up there, lock that in with an F four, then do that little calculation that I've just done. Um, sum J four, otherwise make it zero, close parentheses, and hopefully we get something there. Now, just going to, um, just going to copy this all the way to the right and let's hope, fingers crossed that we get some sensible numbers in, uh, year seven and we do in year seven. And it basically, uh, brings that down, the 680, which is the combination of the 6, 6 8 mezzanine debts plus the 17.6 of equity value, but only in year seven. Now, to do an IRR calculation, what we need is the entry amounts. What do these mezzanine holders tip into the transaction versus what they get out in year seven? And the answer is, we just go up to that sources of funds, it's the 300, but because it was a payment in it's minus 300, it's minus 300. And then finally we can do a little IRR calculation equals IRR of all of those columns. And that gives us at 12.4, which if you think about it, is pretty expected because basically they're getting a, a, a minimum level of 12% return just on the equity because it's a pick note. Um, but they're also getting that extra little value from the, um, uh, from the equity right at the um, end. Um, and, uh, that's that extra 17.6, it's not a great deal. And I think you've got to say, if you actually look at the equity, um, the equity rises, but not by a great deal, not by a great deal, the rationale for that is actually the EBIT and the EBITDA barely, well, I'm not gonna say barely changes, but really doesn't change very much, does it? It really doesn't change very much at all. Um, and I think particularly if you look at these later years, so 5, 6, 7, and eight, the, um, rate of increase in EBITDA is really pretty small. So I think what we'll do just as we walk away from this is we'll probably say, what would this look like if we walked away and say, year five, would it look better for all of the institutions? And I'm pretty sure the answer is gonna be yes. Okay, let's look at the institutions and then let's look at the management and see what the management gets. So the management, um, uh, what do they get? Sorry, no, the ma the the p the PE firm. What do they get? Let's just jump down here. So first of all, they get their preference shares. That's their 12% pick notes. Uh, this is pretty easy. So we just simply say, what's the value of the preference shares? Uh, multiply by minus one 'cause it's now positive they at receiving this money. That's, uh, that number, uh, that that's pretty easy.

What about the equity value? What is their equity worth? So I'm gonna say equals, um, was the equity, the equity is that minus 140, uh, multiplied by, and this, well, we've gotta be a little bit careful. Um, so you will see on this end, in this ownership, uh, section they put in 90%, but because of the warrants on the mezzanine debt, they basically, their stake has been diluted a little bit. They're ending up with 85.5%. I need to lock that in with an F four. And that's the 85 points. Uh, and again, you know, there's no way they'd actually do this. Um, if there were, if it was, if it was worth negative, negative money, um, there'd no way they'd do it. So I could put a max in, in front of this, but in reality, let's not worry too much about it in this first year. Um, and then, um, there we have, we've got our, um, cash, uh, flows if we add them all up. But what I'm going to do is I'm just gonna do that if statements, uh, again, so I'm gonna say equals if the year counter equals the year seven assumption, which is somewhere up here, there we are, year seven function F four, then add up the two lines just above the 6 96 plus the 120. And I'll give a negative figure. Um, otherwise zero, hoping this looks like zero. Yes, it does. Um, and then let's copy this all the way to the right and hopefully we will see that in year seven, yes it does. They're in year seven. It gives us a nice healthy 1.6, uh, billion. But how much did they put in? So to uh, get the opening position, I need to basically say equals, um, the, uh, equity. Uh, so, uh, they put in the, uh, pres there of 622 plus the, um, 90% of the 10. So I just need to say 10 times the 90% that they put in somewhere over here, there it is, 90%. And that hopefully gives me a fairly sensible number, 631. But of course, that's all positive. What I really could do with is making that negative, I'm just gonna put some parentheses around it and multiply by minus one. And that gives me an opening 6, 3, 1. Let's work out the IRR, uh, to our, um, uh, institutions equals IRR, all the way to the very right closed brackets. And we end up with, uh, 15 uh, percent, which isn't too bad, but it's not really sparkling, is it? Now let's have a look at the, uh, lucky old management. Uh, what happens with the management? Um, let's do the same thing. Uh, management basically gets, um, uh, uh, um, the 5%. So, um, I'm gonna say equals, um, management stake, um, which is the, oh, it's actually slightly less than 5% because of the dilution of the, uh, warrants. Sorry, it's not 5%, it's 9.5%, 10% diluted to 9.5% because of the warrants. So I'm gonna take that 9.5% locked up with an F four, multiply that by the, um, equity value up here. And that hopefully gives me something vaguely sensible. But what I need to do is put an if statements, and I could do it as a separate line or I can just be a little bit, um, do a little bit of a shortcut on this. I'm just gonna put an if statements around that I'm gonna say equals if. And so let's take the, uh, year counts and let's say if the year counts equals that exit year of year seven, lock it with an F four, then add up those two, uh, numbers. Uh, sorry, take that, uh, nine, 9.5% times the equity value or otherwise make that zero. Um, and close parenthesis. There we are and we get zero all the way along here. Let's just copy that to the right. And yes, they get 33.4 in year seven. Let's work out some IRR for these guys. Well, how much did they put in? They basically put in, um, a million, wasn't it? They basically put their common equity of 10 multiplied by, um, the, uh, 10%, which is over here, all gonna multiply by minus one. So it's a negative. That gives me minus one. Let's work out an IRR to the management equals IRR hit return and the management gets a stoning, a fantastic 65.1%. Um, and that's exactly as designed. Um, basically the intention is that the, um, is that the, um, the ordinary shares, um, investing in the ordinary shares is extremely lucrative. Uh, for the, uh, management, they put in a million at the time, zero. And after seven years, they're getting this fantastic 33.4. Um, that'd be enough to make everyone work. Any managers work very hard, uh, towards the success of, uh, this operation. Now, I think it's just worth having a little look because although the, the managements are doing extremely well outta this, uh, the institutions perhaps aren't, um, at 15%. Now, again, remember this is in the days of very, very low interest. Um, so perhaps 15% IRR actually wasn't quite so bad, uh, but I was just gonna tweak. I'm just gonna have a little look at one of these assumptions. Um, my exit year here is exit year seven. Let's just have a look what happens if we change that to year five. Um, so down to year five and it goes, it's a little bit better up to 17.2 for the PE institutions and a little bit up again, I think that was 12.4. Uh, for the mezzanine debt holders, let's even just change it to year four, see what happens if we change it to year four. Of course, we could do this with a data table. That would be a very nice way of, uh, doing it, um, up to 19.1. So really the secret to this is getting in, paying off some of the debt fairly quickly, seeing that initial fairly good rise in their results and getting out before it starts to kind of tail off a little bit. And of course, that's one of the dangers with a relatively stable business. Um, if you're not investing a great deal in it, you know, that initial, um, increase from making a little bit more efficient, um, comes good very quickly. But it's difficult to sustain that. Um, and it's, it tends to stabilize, um, over, um, the sort of medium term, which for in terms of IRR values, uh, really sort of dampens down those IRR values. Okay, that's it. We are done. Um, hope that's useful. There is a full version of this. Um, we've had a real whistle stop tour through this. It's very complex. Um, it's a very complex model. Work your way through it. Have a look at this, see if you can understand the way that it's working. But I do think that debt schedule and the returns are the really important parts to pick up this. Those are the bits that are on top of the basic, um, LBO transaction that we looked at a week ago. Um, and if you really wanna knock yourself out, go and have a look at the full complex version of this, which has got a unitranche loan and it's got a sale on leaseback as well. Um, and that'll really, um, that'll really get your, uh, get your interest. That'll really show you how, uh, complex these can, these can be. And I think it's just worth making the point. It's a final entry that this is a real transaction. This really happens. Uh, this was the starting point for the negotiations. Um, uh, unfortunately a handful of years later in Covid, uh, Debenhams didn't do so well. Its sales and revenue didn't. Its sales and profitability didn't go up. It really struggled. Um, and eventually it went into liquidation. So, um, but that's again why there's plenty of information in the public domain about this business. Okay, hope that's been useful. Um, thanks for your attention. Uh, so pretty much everyone's still there, which is great. Um, you've got the recording for this. I'll record this. Uh, I've recorded this session and this recording will go up onto the website for you to have a look at, um, in your, uh, in your own time. And you can stop and start it as well. Okay. Thanks a lot. Thanks for your attention. Hope to see you on another one of these very soon. Okay. Thanks a lot. Bye-bye.