Okay. Uh, good morning or, or good afternoon or, or I guess good evening, uh, depending on, uh, where you are. Um, uh, uh, welcome to, welcome to this, this, uh, this, this webinar. I can see we've got a, you're just keeping an eye on the persistent box.

I can see that we've got quite a few people sort of gradually arriving.

So I'll just pause for a couple of minutes before we actually, uh, actually, uh, kick off. Um, just one, um, one thing to, to, to, to, to note. Um, we will be using, um, a number of, uh, uh, spreadsheets. Well, we'll be using one particular spreadsheet, um, and, uh, we've got a link directly, um, uh, to that, it should be in the, uh, resources, uh, box. Um, if you, if you click on, um, if you click on the, uh, resources, um, uh, uh, icon, you should be able to grab that. Um, otherwise, uh, we've actually got a a U R L, um, in the, um, uh, we've got a U R L in the chat box. Uh, so if you grab, uh, the, uh, if you, uh, go, go to that, uh, there's actually 2 2 2 U ur URLs, um, uh, in the, in the chat box. So if you use the first one, um, and that will gra that will basically take you to our, our website. Um, and there will be three, there should be three, uh, spreadsheets. Um, and the one that we, uh, the one that we need, uh, the, the, the, uh, the key one that we're gonna look at, um, is, uh, something that says, uh, part complete. And you should be able to see my screen.

I'm just sort of waving my mouse at it. It says advanced L B O model, and then it says part completes at the end. So if you make sure you get that, uh, part completes, um, spreadsheets, then that's the one that we're going to be, uh, going through, uh, today. Uh, just give another 30 seconds or so, uh, for people to arrive. There's quite a, quite a few people just arriving at the moment, which is, which is, which is great. Um, uh, do, um, do, do, do, do feel free to, to, to, um, uh, contact me in the, in the chat box, um, uh, and, um, perhaps, uh, wa wave your hand if I don't respond, um, instantly. Um, there are quite a few people online now, and we have got quite a lot to, to, to get through. Uh, so we will be going through, uh, uh, quite a lot of material fairly quickly. Uh, but, um, if you, um, uh, if, if you've got any questions as we're going, then do feel free to to, to jump into the chat box. Um, also, the session is being recorded, uh, so if you want to catch up at a later point, um, uh, if you want to, to, to, to watch it again, uh, then, um, you, you'll be able to go, if you go into our, the, uh, uh, the same place on our, uh, websites, uh, then you'll be able to get into, in, into, into, into these web, these, uh, webinars. Okay, I make that two minutes past five. Uh, so we're gonna kick off.

Um, so just a little bit of putting this into context. Uh, what are we talking about today? Um, well over the last few weeks, um, we've run a few of these one hour webinars looking at complex LBOs.

Um, my colleagues, um, Ollie and Maria, basically took you through the structure of a complex L B O, how you basically set up the spreadsheets and what it's got in it. Um, and also Maria looked, uh, in the most recent one.

It's how you calculate your return from, uh, a reasonably complex L B O.

And we're just gonna look at one particular issue today, and that's the issue of stub periods or, or basically sort of a deal that happens in the middle of the year, um, which is obviously perhaps a, uh, um, uh, a fairly realistic scenario.

A lot of the early models that we look at, we take a fairly simplistic view, and we say that deals are gonna happen at the end of the year.

So we have a complete year, uh, of a complete year of results for a target company, um, before it's getting, before it gets taken over, uh, as part of the L B O transaction.

And then it gets taken over on the 31st of December often. Um, and then the very first year, uh, under the L B O ownership, uh, is, is a full, is a full year. But of course, that's fairly unlikely to happen. Um, a lot of the times you're gonna have a t a transaction, a deal that happens in the middle of the year, in the middle of June or July or something like that. And therefore, how do we deal with that complexity? How do we deal with that, that half year, uh, period? And also, how do we build a model that is reasonably flexible? Because we might well model a, um, a particular L B O, we might well model an L B O, um, and, uh, the deal changes a little bit.

So when we think it's gonna happen, uh, may change a little bit as we go into negotiations as we start doing some due diligence. And it might, you know, we might think it's gonna happen in June and it ends up happening in September.

So we need to build a model that has a, a degree of, uh, flexibility.

So that's really the, the, the purpose of today. We're gonna gonna have a look at that.

So I'm just gonna start with a few, uh, notes. Um, and let me move my screen around a little bit, and I hope that people can, uh, see this on the screen. Um, and I'm just gonna write, 'cause I can actually write on this one. I was just gonna write my name on this as well. Um, my name is Phil Sparks and I'm a trainer at, um, financial Edge. And I'll be just, uh, you'll be, uh, I'll be looking after, uh, this session for the remaining, uh, 55 minutes or so, uh, while we look at this, uh, situation. So this is what we've got.

We've got a situation where we buy a business, uh, but it's in the middle of the year. And we've got an example here where it's, uh, happens on the 1st of October. So we've had nine months, um, uh, um, before the transaction, and then we've got three months after the transaction within this particular, uh, year. And the reason we, we talk about sub period is we, the sub period is this little sort of end piece, um, that happens after the transaction. Uh, now we've gotta think about the income statements and the balance sheet.

What happens for the income statements? Well, in the income statements, the, all of the transactions that happen until the deal basically remain within the target. And, uh, uh, you know, any, any profits, any dividends, that kind of stuff would be paid to the previous owners.

Anything that happens after the deal is for the accounts of the new purchasers.

The, um, uh, the fund, the private equity fund, or the, basically the sponsors of the, um, L B O. Um, so we need to kind of calculate the income statements, and we need to split the income statements into two parts, uh, in this context.

Uh, three quarters parts, uh, before, and, uh, one quarter parts afterwards.

And that's gonna ha and that's gonna affect all of the income statement lines.

So things like revenue, sales, gross profits, crucially, it's also gonna impact things like interest. Um, and we know that with an L B O, um, you have a very, uh, LBOs by their nature are very heavily funded by debt.

And so we need to be really careful about the splits of interest before and after the, um, uh, the, the, the, the, the, the deal.

What about the balance sheets? Well, if we move to the balance sheet, well, again, we basically, uh, we, we have things that impact the balance sheet before the deal and the things that impact the balance sheet after the deal. So things like depreciation, we'll make a fairly big impact on the, uh, balance sheets during that first, in this case, three quarters of the year.

So what we need to do is we need to calculate a balance sheet as at the deal dates, because that's the balance sheet that's basically gonna get taken over that forms the opening balance sheets for the accounts of the new owners.

Um, so we'll be having, we'll be looking at lots of things like, as I say, depreciation, retained earnings, all of those things that, that get impacted by, uh, the income statements, uh, being split into a three quarter period and a one quarter period.

The second thing that has a very significant impact on the balance sheet, obviously, is how the business is financed. And basically, you will bring in the new debt that's the, that funds the L B O, uh, um, as at the deal date as at, in this case, the 1st of October.

So the co calculating the balance sheet is relatively complex.

We need to bring in elements that are a function of the income statement being split into a three quarters and a one quarter, uh, uh, uh, divide.

And we also need to bring in the new structure of the business.

So there's gonna be things like new debt, there's gonna be things like goodwill, um, which are a function of the purchase price, um, less the balance sheet that you actually acquire at that point in time.

So that's, um, how the, uh, that, that, that, that's how we're going to look at, uh, the, uh, the, the, the transaction. And that's what we're going to do. Now. We're going to look at a fairly substantial spreadsheet, uh, and we're going to look at what happens within this, uh, one year period, which is split into a three quarter period and a one quarter period in this particular example.

Now I'm just going to look at one calculation, um, for one particular issue, because it's worth just pausing with the maths and thinking about, uh, how this actually works. Now, in this particular instance, we've got some assets, and it almost doesn't really matter what the assets are, but we've got some assets that could be, uh, pp and e um, that basically, uh, move during the year and in the middle of the year, three quarters of the way through the year, um, the deal happens.

And so what we need to do is we need to calculate those assets at that point in time. Now, this is a very, uh, very easy example in this context.

We basically have, uh, opening assets out of 112 months later, we have closing assets of 112. Now, I'm not, uh, perhaps I'm not a very good mathematician, but even I can work out that that means it's gone up by one every month.

And therefore, um, just simply by ratios. Um, you could get to, oops, oh, I'm sorry, I'm, I was leaning on my leaning on my, um, uh, screen, uh, just by, just, just by ra just by looking at the ratios or the number of months. Uh, you could, you could work out that the assets at, uh, at the end of the ninth month must be 109. Or alternatively, you would say, well, look, I've got 112. Um, if I, so, so I've got 12 that get split into, uh, one quarter and three quarters. Um, well, each quarter is basically going to be three.

So I've got three lots of three before the deal and one lot of three after the deal. Uh, so 109, um, is, is fairly, fairly, fairly easy to, to, to calculate.

Um, but if we actually do the maths, um, of this, and I'm not gonna derive this, but if you actually do the maths of this calculation, then the calculation actually ends up being, uh, this calculation in the middle here that I'm just sort of, uh, a fairly scruffy, uh, highlighting. It says take the prior year balance.

So the prior year balance would be the one hundreds, and then multiply, and this seems slightly counterintuitive, multiply, multiply by the proportion of the year that is the stub. So in this case, that's 25%. Now. So the, the, the very first time I did the maths on this, and I sort of worked, and I calculated this, it just sort of felt intuitively slightly odd. 'cause I would've thought, it just feels like, uh, you're starting with the, the previous year balance.

I would've thought you'd be multiplying that by three quarters. Um, and then the, um, the, the, the, the closing balance, you'd be multiplying by one quarter, but it actually, and the math works like this, you actually multiply the opening balance by the one quarter, the stub period, the period after the deal, and then you take the next year's balance. Um, and again, I just seem to be leaning on my screen. Uh, then you take the next year's balance, uh, which is the 112, and multiply, uh, by that, uh, by the 75%. Um, really sorry about this. My, my pen just seems to be, uh, lurching on my screen. There we are. And if you do this, if you work this out a hundred times, uh, 25% is at 20 is is, is is 25, uh, 112 by 70, uh, multiply by 75% is 84.

Add those together and you do get the, uh, 109. Now, the reason that I'm just pausing on this and just saying, just remember, this is when we get to construct our balance sheet, we are going to use exactly this formula to construct our opening balance sheet to the balance sheet as at the deal date.

So next thing we're going to do is we are going to, um, we are going to go to Excel, um, and hopefully, uh, you've managed to open this. Um, uh, this is the advanced L B O L B O model part complete. Now, what we've got on this, I will take about two or three minutes just rattling through the spreadsheet, just giving you a reminder of what's in this spreadsheet.

But we're gonna concentrate on just one tab, uh, for this, uh, for, for, for this, for this spreadsheet. So what we've got here, and this is the same spreadsheet that you were looking at with Maria. Um, if you went, if you came to the, uh, last, uh, webinar where we were looking at returns, um, it's the same relatively complex spreadsheet. So it's got some, you know, it's a fairly big spreadsheet. It's a fairly big transaction.

It's very loosely based on, um, uh, a transaction, um, called, uh, Debenhams, which is the department store in the uk. Um, and, uh, it's got, uh, a lots of different forms of debt. It's got some mezzanine debt, it's got some preference shares, as well as a number of different, um, uh, different, different pieces of debt. Um, and it's also got a transaction that happens in the middle of the year.

So we've got one of these stub periods that we need to deal with, and that's what we're going to do. Um, and the, uh, stub period, uh, the model for that is in, um, this, uh, tab here, which has deal dates. So that's where we're gonna spend our time. We're gonna look at deal date, um, uh, in, um, in, in, in, in, in, uh, in, in this session. So, um, I'm just gonna rattle through very quickly what's on each of the tabs, and then we'll go, then we'll dive straight in and we'll start doing some numbers.

So the first thing is the input tab gives you all of the assumptions for the, uh, for the, for the spreadsheets, uh, for, for, for, for the, the L B O transaction. So when it's gonna happen, um, and this is really key, particularly for the, for the stub, this is the only time that we've put these numbers in, sorry, the, these dates in, uh, 30th, 30th of September, um, uh, 2018. And we have a first fiscal year, end of the 31st of December, uh, 2018.

And that's where our stub period is basically going to go.

So we are gonna be like our little example that we're gonna be 75% of the way through the year when the deal, um, happens. Um, and we're gonna aim to, uh, sell out after five years. And we've got lots of other assumptions in here, different forms of debts, uh, which pieces of debts we've got, um, how much the, um, interest is going to be. Um, you can see a calculation over here on each of those pieces of debt.

If we go to the next tab, the model tab.

Now what we have here is basically we have the normal three statement model, uh, for the targets, uh, company.

And you can see if you go over here, you've got about 11 years worth of, and these are full year figures. So we've got lots of assumptions at the top, uh, things like revenue growth and so on. Uh, normal calculations for things like p p E and retained earnings. And then as you go down, you've got, uh, sales, ebitda, depreciation, amortization, et cetera. You've got the balance sheet, and then down at the bottom you've got a cashflow.

So it's a normal three statement model built on those, um, built on those, um, uh, those assumptions. Uh, we've then got the deal dates. I'll come back to the deal dates in a bit, but this basically calculates what happens within that transaction year, what happens between the end of December, 2018 and December, 2018, sorry, 2017 and 2018. Um, and it splits that, uh, those, it splits that year into, um, uh, a number of columns. Um, and in the middle of that, we're gonna calculate the balance sheets, uh, the balance sheet that we actually are going to buy.

Then we have the deal model. This is the result.

So this is a combination of what happens in that three month period from September, 2018 until the end of the, uh, end of 2018, and then the remaining years, uh, sort of flow out. Um, and it also includes, uh, things like movements on, uh, debt.

If I go down to the bottom, you can see there's, I've got divs div zeros all over the place because I've, I, I've got empty, um, empty things within my deal, um, deal date tab. Um, but you can see that I've got lots and lots of, uh, um, references, uh, in here to the debt schedule. So basically, um, as of normal L B O, you calculate the debt, you calculate what's gonna happen to the debts, how much of debt are you gonna repay, and that then feeds back into the, uh, balance sheet. And then we have our debt schedule again. Um, it starts at the top with your anticipated or your planned exit, uh, point, there is just one point I'm gonna make up here. Just highlight this in, uh, yellow, we need to be really careful if we have these stub periods. Um, think about the knock on effect of this STU period.

And what happens in this one is that, um, in when we're calculating interest for on, on, on our, on our debts, um, in that, uh, first period, that first period is only gonna, the period gonna be the period of October, November, December. Um, and it's basically gonna take just three months. So that, that's not 0.25 means 25% of a year.

And basically we need to make sure that our debt schedule incorporates, um, a, a factor to reduce the amount of interest because we've got significantly less than a year. So, and then the final, uh, tab is the, the returns again, uh, Maria went through this in the previous session where she looked at how would you calculate an I R R, uh, based on, um, uh, based on, um, um, uh, based, based, based on, on, uh, this, uh, transaction.

So we're gonna go back to the deal date tab, and we're basically just gonna start working, uh, down this now entirely happy if you just want to sit back and watch. Um, but I would recommend you download the, um, the, the, the spreadsheets download both the, uh, parts complete one, um, which is basically what we're, we've got on the screen right now. And also, uh, the final, uh, version, the, the one that says full, 'cause that's it, that's, that, that's got all of this complete. So that is the solution file.

So we're gonna go back, um, to this, and I'm just going to move my bits of paper around and find, uh, the spreadsheet, just so I've got my right notes in the right place.

Just bear with me for a second.

Okay. Alright. So we're gonna start at the top Now, um, because we only have about 40 minutes, uh, left of this session, I've pre-populated quite a lot of this. Uh, I've pre-populated the stuff that's relatively straightforward, which isn't really, uh, doing anything new. And what I'm doing, um, is we're just going to look at the stuff that is different as a result of the, of the, of the, of the, um, uh, the stub period.

So we're gonna look at the income statements, and we're gonna look at the construction of the balance sheet as well.

The cash flow, um, is fairly mechanical. There's nothing different in the cash flow from a normal three, three statement model. So what we're gonna do is we're gonna dive into the income statements, um, and then we'll look at the balance sheet and we'll look at how we construct that deal balance sheet in the middle. Uh, the thing that we actually, uh, that we actually, uh, take, take, take, take over. So here goes, now what I've actually done, the missing figures, I've put in this sort of pale gray color. Now, you might not be able to see it with on my, on my shared screen, but if you open up the version of the spreadsheet, you should be able to see it on a local, uh, copy.

So I've got this pale gray color, those are the cells that we're gonna ca that we're gonna calculate. Those are the cells that I'm gonna go through. Now, right at the top, I've basically got links that come from the input, uh, cell that, sorry, come from the input tab. And they basically refer to the dates. Um, and you can see that I've got 30th to September as the transaction date.

And the very first year end, um, is December 18, and the previous year end is December 17.

And these just basically come from the inputs, uh, date. Um, now what I've then got is something that works out the fraction of the year that we are through, um, uh, it actually year frac, depending on which of the dates we point to, it actually gives me the 25% of the year that is left i e between the end of September, um, and the end of December. And then I, and this, uh, figure here, uh, 75% is really key.

I only calculate this once in my entire spreadsheet and everything else relates to this. Um, and then obviously the, uh, the, the, the, the period, um, until September, the end of September, 2018 is one minus that 25%, and that gives me 75%.

Now, you'll also see up in the left hand corner that I've named these as sales, which is really useful. If you can con, if you basically have a pre-deal percent and a post deal percent, um, as named ranges, then you can refer to them anywhere in your spreadsheet.

And this gives you some flexibility in case those dates actually change.

It means your model still, uh, will work even if the date gets pushed out to the end of October rather than the end of September. So we're gonna go down, um, and we're gonna start to look, um, and this is gonna feel a little bit like a normal three settlement model. We're gonna gradually build up our three settlement model.

So I'm gonna start off, um, uh, with these subsidiary calculations at the top, I'm gonna start off with the, uh, pp and e calculation.

So we take the opening p p e, uh, uh, and then we add on, uh, depreciation and we add on capital.

So we add on capital expenditure and we take off depreciation.

But what we're gonna do with these is we're gonna time apportion them.

So my opening pp and e and I'll put formulas alongside so you can see them my opening PP and E 591. Now, where does that come from? 591 comes from the balance sheet, which is listed down here, and you can see that I've got an opening pp and e figure here. Now, where does that figure in turn come from? That basically comes from the model tab. Remember, the model tab is basically the entirety of the business, um, before and after the transaction.

And therefore this is just the closing figure from the end of, uh, December, uh, 2017, sort of in the normal way.

And we'd normally do this exactly the same when we've got a three seven model, we just pick up the closing, uh, pp and e from the balance sheet, so we can take that figure, that's 591.

And next what we're going to do is we're basically going to say I need to time a portion my capital expenditure and my depreciation. So, uh, I'm gonna go to the model, uh, tabs, I'm gonna say equals I do control page, oops, sorry, I pressed function control page up. Um, and I go to my model tab, and what I need is I need my, uh, capital expenditure, which is somewhere down here. Uh, there's my first year of capital expenditure, um, December, 2018. So what we're gonna say is we're gonna take that dec, that capital expenditure of 79, which is for a full year, and then we're going to multiply that by, um, the proportion of the year until the transaction, until the deal.

So that is going to be multiply by, and I'm gonna, I start typing pre, and it says pre deal percent comes up as a suggested named range.

I hit tab to expect to accept that I hit return, and that gives me the capital expenditure, um, for the nine months until the end of September. Now, of course, what we're doing here is we're assuming that all of the transactions across the year are relatively smooth. So capital expenditure, you know, is, is, you know, happens in a, you know, in a reasonably similar way every month. Um, then we're gonna do the same thing with depreciation. Now, the, what I can do with depreciation is basically, I know it's the next line down in that model tab, so I just copy down one and that gives me depreciation of, um, 82. Um, just checking my notes. Yeah, I agree with that.

I get to the end and I hit alt equals, and that sums those amounts up. So I've, now, that's my sort of process for doing this. And I've now got my, uh, my ca my pp and e calculated. Let's do the same thing with intangibles.

So my opening intangibles is the same as my closing intangibles.

I basically say equals control page up to go to my previous, uh, model the full year. And I've got intangibles, um, here. Um, and I've just got some amortization. I'm not basically adding to my intangibles, I'm just gonna amortize them. But this time, again, what I'm gonna do is gonna take that 4.5 multiplied by the pre-deal percent hit tab to accept it. And that gives me, uh, a relatively small, um, a relatively small, uh, amounts of, uh, amortization.

And then that's, uh, falls, my, my, my, my intangible. That gives me my closing intangible, uh, figure. Um, so we're next going to, and this is always a little bit odd with these three statement models, think well, do I point to about to an income statement that's currently empty? Um, uh, or do I go into my income statement first and then come back to here? Uh, I am gonna complete this just so that we can work our way down. So, uh, we've got exactly the same subsidiary calculation for, uh, retained earnings.

So I'm gonna do the same thing, uh, here. Uh, I'm going to take, I've got the ending, um, equity comes from the model tab, uh, flows into my balance sheet. And so I'm gonna start with my ending figure. So that's the beginning of, uh, January, 2018.

And then I'm going to add on, uh, net income.

So I'm just gonna go and grab the net income figure, uh, which is somewhere down here at the moment, it looks rather odd because my, I haven't completed my income statements. I hit return, pop that back up there, and then I need to do exactly the same with, uh, dividends.

And I've got dividends, which are also listed down in my income statements, uh, somewhere. So like that, again, it's an empty figure at the moment. Uh, it's what, it's a gray one. So we're gonna complete that later on.

And of course, that doesn't make a lot of sense at the moment.

It's just literally, uh, um, it's, it's, it's, it's, it's all fairly empty.

But when I populate my, um, when I populate my, um, my balance, my, my income statements, my balance sheet, this will all sorts of updates. Uh, so now we need to do exactly the same, but with the key lines of the income statements.

So I can say exactly the same for sales. I can say equals page up, go to my pre my full year, full year sales figure. And there it is. So again, this is the forecast of sales for all of 2018, and we're saying, well, how much of that is pre-deal? So how much of that is basically for the accounts, for the benefit of the previous owners? And then what's left is obviously for, for, for me, uh, as the, as the, as the acquirer. So I'm gonna say equals 29, uh, 2, 2, 9, 5 3, multiplied by, again, the pre-deal percent.

And you can hopefully see how valuable it is to have that, uh, that pre-deal calculation done and have it as a named range because I just pop it into all of these, all of these, uh, calculations.

It also means if I'm reviewing the spreadsheet, um, at a later point and I look into one of these, uh, sales, then it's really obvious at what the calculation is actually doing.

It's taking the full year of sales multiplied by the pre-deal, uh, pre-deal percent of the year. Again, I know that my sales and my EBIT star are one after the other, so I just do, um, and I'll just pop notes at the ends of here.

So I just simply take the, uh, this, the, the, uh, I take the, the adjusted sales figure, copy it down by one line, and I know that that's gonna give me my EBIT star number, um, depreciation, uh, depreciation's already, hopefully already done, because basically it's linking, it's just simply linking up to that subsidiary calculation, um, that I did up here. That's 82, and you can see that it's popping down, uh, to, to this figure here, 82, that gives me an EBIT figure. Um, so I'm on my way to constructing a, um, on, on my way to constructing a good, uh, income statement for that, for, for, for, for that period. Okay. What next? What next? Um, we're gonna go down a little bit further on the income statements. Uh, we're gonna take the historical finance charge. So again, this is interest that the company, the target company, was basically, um, accruing or, or, or, or being or charging on its existing debt.

So assuming that that didn't change in the period between the end of December and the deal, I basically need to take three quarters of that figure. So again, I go equals page up, let's go and find that historic finance charge.

It's somewhere down here. Um, it's, uh, it's actually, actually actually zero. Um, but if there had, if there was any interest, um, if there had been any interest, uh, then it would pop through there. And again, I multiply that by pre-deal percent hit return, um, and that gives me, uh, a zero, uh, for that.

And then if I work my way down, just got a couple more things to do, I've now got a relatively, uh, small amount of profit.

So I've got profit rather than loss, um, that I had when I fir first started populating this. Um, and so what I'm gonna do is I'm going to go and grab the tax, uh, figure.

So again, what we can do with the tax, um, because, um, we, we, I guess, I guess we could do it, um, uh, in a number of ways. We, we could just simply take the, um, uh, effective tax rates and multiply by that, uh, profits, uh, before tax. I'm just gonna simply take the, as I've done most, oops, excuse me. Um, I'm gonna, I was pressing the wrong key.

I'm gonna take the actual tax in the income statements for the full year.

And again, just time a portion, uh, that, um, and just checking on the reference, it's somewhere here, 50, there we go. 55, uh, 55 again multiplied by the pre-deal percent, and that gives me a figure of 17.4. That makes sense.

And again, uh, I'm gonna assume that the dividends are time apportioned. Now, this is perhaps a slightly, uh, more, more difficult, uh, assumption.

So if I go back to my dividends, uh, here, you can see that I'm assuming that I'm paying 8.3 every year.

So I'm gonna assume that that's time portioned. Uh, so the, uh, the previous owners got three quarters of that, of that, of that figure.

Now whether that really hap, whether that would really happen, I think is, is, is open, open to, uh, debate, but I think that's a fairly reasonable assumption that basically they've recognized three quarters of the profits and therefore they're taking three quarters of the dividends. So again, multiply that by pre deal percent hit return, that gives me a dividend of 6.2.

And that's pretty much my income statements at done.

That's pretty much my income statement done. Now, uh, let's just have a look at, um, let's have a look at, uh, uh, what we would do for the, um, the income statements for the next year. Um, and I'm gonna go back up to the top and then we'll do our balance sheet, uh, later on, we'll do our balance sheet, uh, late, late, late later on. So, um, we, now, what we need to do is we need to do exactly the same calculation, exactly the same calculation, um, but we need to do that for the three months after the deal. Now again, what we're gonna find here is that there's certain aspects that are gonna populate themselves, um, once we fill in the rest of the three statement model.

Uh, but they're pointing to empty cells at the moment. So things like the, uh, pp and e um, at the moment we haven't done a balance sheet, uh, but I am gonna do this.

I'm gonna point to sort of the empty cells and do the pp and e notes so that when I then populate my income statements, I can point to the depreciation, um, and so on. So, uh, I've got, um, just to describe what we have in terms of the columns.

So the first column that we've just done, that covers the nine months until the deal. Um, what we then have, um, and this doesn't really make a lot of sense for the income statement, but what we then have is for the balance sheet, we have deal adjustments.

And this is gonna be things like zeroing out existing debts, re repaying existing debts, and, uh, taking on new debts, incorporation of the new debts, um, issuing of, um, new equity and so on.

And then what that's gonna give us in the next, um, column, in the next column here, um, that's gonna basically give us a balance sheet, which is the balance sheet just on the day of the transaction, effectively our opening balance sheet. And then, uh, the next column gives us the position as at the end of December, 2018. So that's the way that we're building this up. That's the way that we're spreading this thing. So I'm basically gonna do the, um, pp and e notes, uh, here.

So I'll start exactly as I've done previously. Start with making that, uh, my o my opening figure, the same as my closing figure, uh, the capital expenditure. Um, I'm basically going to go to my capital ex, I'm gonna go to the capital expenditure figure in my model, which we saw before, somewhere up here.

So that's the CapEx. We've taken three quarters of that, that already. So what we're gonna do now is we're gonna say multiply by the post deal, the post deal percent hit return. Uh, and just remember, uh, I've got CapEx of 79, so I hit, um, uh, return and I end up with 19.9 in my post deal period, 59.8 in my pre-deal period.

And if I look down just at the tiny little sum down at the bottom, 79.7, um, in this, um, in total, so I am basically splitting that movement capital expenditure across the three quarters and one quarter period.

I then can just copy that down to capture the depreciation alt equals, adds everything up. And again, I'll just put some, just put some headings. Uh, so, so, so some, uh, uh, the, the formulas, uh, uh, put, put the formulas, um, up alongside. So you can see exactly the same with my intangible assets.

And I know it's zero at the moment 'cause we haven't populated the opening balance sheet, but as soon as we do that, as soon as we populate the opening balance sheet, all of this will, uh, ripple through. So I'm gonna grab those nets, intangibles, the opening figure, and again, I go, I think if I just copy because there's a level, uh, is that the right figure? 1.1? Yep. Um, I can just copy this, uh, down because it's in the right sort of order. Um, on my, um, it's in the right, it's in the same order of lines on my, uh, on my on, on my calculations here. And on the, uh, model tab as well. I hit alt equals, and that gives me, uh, my closing intangibles again, with the, um, with the, uh, retained earnings. I can just do what I've done previously.

And I think, again, I could just copy, um, this, um, these, these, these, these amounts over to here because I've got common, uh, way a common setup, a common, uh, set of lines. Um, so I don't need to do anything clever.

It's just referencing movements on the equity is just referencing both my dividend figure and also my, um, uh, my net, my my net income as well. So income, um, get down to the income statements. So let's calculate, let's, uh, populate the income statements. So I'm gonna go equals page up back to my model, my full year model, grab the sales figure for all of 2018, multiplied by my post deal percent.

I hit return and it gives me, uh, sales for the final quarter of the year of 738.

Uh, just looking down to check that that works. Um, and now a lot of this is gonna populate fairly automatically. Um, depreciation and amortization are just linking up to the top. Um, now not gonna go into this a lot because this is a function of the, uh, the debt schedule, but, uh, the debt schedule already being populated, uh, for the new forms of debt. Um, and you can see that in the income statements for this three month period.

We're basically pointing to the debt schedule now, as it normally is, we're to, to, to, to make sure we don't get circular references. Uh, there is a switch in here as well. Um, at the moment that switch is off, so we don't have anything coming through in terms of interest. Now, let's go and look at the balance sheets. This is a little bit more complex, so, uh, we're gonna go and grab, uh, the, uh, cash, uh, figure. Now, this is just a normal three statement model, so I can start with my cash figure, um, at, uh, the, uh, at the, um, at the bottom.

And just going to, sorry, just let me move my notes around so that I have got the right thing in front of me.

So I'm gonna just go and grab the cash figure from the bottom of the cash flow. And I told you already, the cash flow is already populated and everything is rippling through into this.

So this represents the, uh, cash as at, uh, the, as at the, um, uh, the end of the first nine months. Now, it looks slightly odd at the moment because we haven't populated the balance sheet. So effectively what that's assuming is you're paying off all of the debts, um, uh, with your, with your, with your cash flows as we populate. Um, as we populate the balance sheet, this figure will change because those movements and operating working capital movements in debt, uh, movements in pp and e and so on, will all ripple through into the, um, into the cash flow. So, but I'm gonna, I'm gonna basically point to the, uh, cash flow, the bottom of the cash flow figure there.

And I'm just gonna put over here a reference equals formula text, so you can see that.

And then what are we gonna do with, uh, receivables? Now this is our moment where it's a little bit complex. Um, now you might remember that our, um, our balance sheet amounts, they basically, um, we, we, we need to bridge the position and we've basically got already a link to the closing balance sheet at the end of December, 2018.

And the closing balance sheet at the end of, uh, sorry that, that one was 2017, that one is 2018.

So we need to prorate that movement from 298 to 312 in a three quarters and a one quarter piece.

And we need the balance that's somewhere in between. Now, you might remember what we did in, uh, those notes, in those, in those slides was we said, what we're going to do is we're going to take the opening cash, uh, uh, receivables position.

So I go off to my balance sheet and I find my balance sheet, my receivables figure.

So I'm gonna take the opening figure of 298, um, and I'm going to multiply that one. Is that right? E 62? Yep. I'm gonna multiply that one by the post deal percent, which seems slightly on, slightly odd, a little bit unintuitive.

So we're gonna multiply this one by the 75% and then we're going to take the, uh, closing receivables of 312 and we're going to multiply that by the pre-deal percent. And that gives me 308, which intuitively feels somewhere sensible between 298 and 312.

It feels like it's the right sort of proportion, um, across.

Now I can basically, once I've done that, I can copy that, um, down to other current assets. Um, I can copy it down to, um, uh, somewhere, lemme just scroll down a little bit.

I'm gonna copy it to the revolving credit facility, but I need to just pause a little bit because I've got an extra goodwill line in here. So this one is pointing to line 72.

If I go back to my model, let's just have a look where that revolver is.

Revolving credit facility is actually on line 71, so I'm just going to manually adjust the 72 to 71. Um, and I know it's zero, but basically that I now know is doing the right calculation on the revolver.

Um, I can do the same thing on accounts at payable. Again, I'm just gonna, and that, that, and look, that makes, that makes a bit of sense. It goes from 129 to 135 and somewhere, uh, three quarters of the way across is 133. So that makes sense. Um, I can also do it on other current liabilities, um, as well, which gimme that 219, uh, number as well. Okay, I think that's, um, uh, just going to seven, just checking. I think I can use the same formula. Um, and I'm basically going to assume that my historic long-term debt, sorry about this, I just need to, um, move, move to a, move to another, another page just gonna copy. It's to that, um, historic long-term debt figure as well.

And just one other line, um, other long-term liabilities, which is somewhere down here. And again, I just need to be a little bit careful of that one. Other long-term liabilities in the, uh, deal in the model is, uh, in line 77. So I'm just gonna tweak that. Um, it's a little bit manual, this process. Um, so I'm just gonna tweak that, put that, point that to line 77. And there we are. We've got 104, so that makes, that makes sense. And this one isn't actually changing at all.

So it makes sense, uh, where it ends up. Let's go back up our balance sheets and let's populate the rest of our balance sheet. Um, we've got a total, um, just gonna populate, just gonna put the formulas over here. So you can see formulas as I, um, oops, control Z. So you can see formulas as I, uh, complete them. There we are, of course, there's lots of sort of empties and lots of nas, but you can hopefully just see, uh, some of those right pp and e nice and easy.

We've already done our pp and e uh, calculation up here. Uh, we can go and grab that figure. That's the closing 569, uh, net intangibles. We can do the same thing from our notes there. We are other long-term assets. Uh, look, other long-term assets, I think, I think I just need to grab one of those, uh, pro rating formulas and just make sure that it's pointing to the right. One.

Other long-term assets is line 68, and I think I just need to tweak my line there to 68 there. I'm, that makes much more sense. And then I can end up with total assets, which equals some of everything above.

And also the current assets as well.

There we are. So we've got a balance sheet that's on its way.

Total liabilities. Simply add up my total liabilities.

I've got equity and I've already got an equity calculation done at the top.

Here I am. Here's my equity calculation. 710, I must be pretty close.

So total liabilities is equity plus total liabilities.

Um, and do I have a balance sheet that balances very nearly, um, balance sheet that nearly balances, uh, 1, 4, 3 7. Oh, I don't have current. Um, I don't have current liabilities.

I've got that some there.

Um, got a balance sheet, doesn't balance, uh, which as an accountant, um, always makes me feel, um, rather unsettled.

Got liabilities of 352. I've got long-term liabilities and total liabilities here.

Equity of seven 10, and my total liabilities and equity. I think, uh, I think I'm missing. I think my total liabilities, uh, figure yes, I'm missing, missing the current liabilities from that.

There you go.

So a little bit of a real time error checking and I get balance sheet. The balance is okay, so we're nearly done, we're nearly done.

All we need now is to deal with these unusual items. Um, the deal items. So just again, recap, where are we up to? We've basically got an income statement that we spread into a three quarter period and a one quarter period. Um, we were really careful about things that were sort of moving, um, uh, in a, in, in, in, in, in a smooth way. So things like capital expenditure, depreciation, sales, um, cost of goods sold and so on. Um, and we also were a little bit careful with interest as well.

So the interest until the point of sale, we prorated that based on the historic debt of the business, but the new interest is gonna be driven by our debt schedule.

And we're time apportioning that we're just charging interest for 25% of the year, um, driven by our dates. So we've got our, our balance sheet now we've taken our balance sheet, we've constructed our balance sheet, which is really relatively straightforward.

We time apportioned most of the movements. Um, so things like receivables and inventory and so on. Um, and we've also then used that to drive our cash flow and our cash flow feeds back into our balance sheet and it all balances. We've got that. Nice, okay, at the bottom. Now, what we need now is just to incorporate the unusual items, uh, the results, the, the, the, the things that are gonna impact the balance sheet that we buy as at the point of the transaction.

And this a few more sort of slightly complex calculations in here.

So we're gonna start at the top. We're gonna start with, uh, cash, and I just want to go back to the inputs tab and have a little look at, uh, cash. Um, and if we go back, uh, to, let me just check exactly where it is. Um, so C 31. So if we go back to our, uh, input tab, there's an assumption at the very bottom of the input tab that we're gonna try and maintain a balance, a minimum cash balance of 30.

So we're gonna maintain a minimum cash balance of 30. Now, with an L B O, what you want to do is all of the cash that you generate, all the cash that the business throws off, you want to use that to retain, to basically, uh, pay down debt. Um, but we're basically saying that we don't want to use all of our cash. We want to maintain a balance of 30. Now we have basically bought, um, sorry, back to my deal dates.

We've basically bought a business and it has a fairly large cash balance on the, um, on, on, on the balance sheet.

So what we're gonna do is we're gonna use some of that cash to basically repay the existing debt. We're gonna basically use that as a source of finance.

So what we want to do is we are starting with 269.

What we want to do is we basically wanna take off 239 to basically leave us with the 30 of net cash. Um, and the funding schedule is basically constructed in a way that it makes use of that, um, cash. Uh, that's, uh, uh, bringing it down to 30. Uh, it makes use of it as a source of financing. So I need an if statement in here. So I'm gonna say if, and I'm gonna take that opening, uh, figure.

I'm basically gonna say if F 61. So the cash balance, um, is greater than, and I jump back control page up to the input tab, and I go back to here. I'm my minimum cash balance.

So if the 269 is greater than the 30, which it is, what I need to do is I basically need to say minus the page deal dates. So minus, um, uh, 269, um, plus that, um, 30 of, uh, opening of, of minimum cash. So, uh, here I am 30, um, otherwise zero. And that should give us yes, minus 239, um, an adjustment. Bring us down to that 30 at balance.

Next, I've got some goodwill. So I've bought the business. I, I've paid a lot of money for this business, which has got relatively modest assets. I did a goodwill calculation on the, um, input tab. So I just simply go and pick up that goodwill, uh, calculation.

Um, and my goodwill calculation is, um, is basically, um, where am I C 15? So the, uh, equity value, the 1 7 2 0 that I paid, I, this is the amounts of money that I bought.

And then I say, uh, minus I go back to my deal date and I grab the net assets that I have bought, and it's somewhere up here. Um, f um, it's my, where are my Nest assets? Uh, there we go.

So my Nest assets, I'm taking equity as a proxy for next nest assets.

'cause it's a balance sheet. So a balance sheet balances.

So the equity figure is exactly the same as the nest assets. Um, and that should give me a figure. Yep, a figure of 10. 10. So that's my goodwill, uh, calculation. What else do I need to do, uh, as a, as a, um, um, as a a deal transaction? So first thing, uh, then I go, go down to my, uh, uh, revolving, uh, credits, uh, facility and go down to, uh, here.

And I'm going to, um, say if I go back, I say equals, I go back to my input tab, and you can see that as part of the financing somewhere. Uh, here, it's part of my financing.

I'm assuming that I draw down a 20 of revolver. Um, so I need to bring that 20 of revolver in. I hit return.

I've got 20 of revolver coming in there. Uh, anything else, um, just now need to go and find, uh, my debt.

Gonna go and find my, uh, debt. Uh, so the first thing, uh, I'm gonna assume that I repay all of the existing debts.

So I just simply have an equals minus F 77.

So I get rid of all of my debts that disappears. Um, but then what do I do? I replace that with all of my new forms of financing.

So I say equals, um, I'm gonna go to my, uh, refinancing at facility. Um, and again, just make sure that I'm picking up the right items.

Um, I'm gonna go back to the input tab and I just need, need to, is it I 16? I think I six. Um, I've already brought in my revolver.

So I'm gonna grab the first piece of debt here, um, in line at six.

So that's my, uh, uh, drawn down, uh, rev, uh, refinancing facility. Now, I haven't drawn, there's nothing in here. It's gonna be zero.

But as I copy and paste this, um, this sell downwards, I'm gonna pick up all of those new forms of debt.

I hit return and I just copy that all the way down.

And I think I need to go all the way down to, um, all the way down to the mezzanine, which should get me down to, I hit control D. That gets me all the way down to, uh, the mezzanine debts.

And there's just one other little piece here. Uh, there's a thing called the un, the, the amortized, um, debt issuance fee or, um, original issue discounts. Just need to pick that one up. And that's a few lines lower down. So I go back to my input tab, and that is, um, somewhere here. See, there I go. Original issuer discounts of 12. Now, to be a little bit careful, so this is what happens with this. You might recall if you raise some debt, but there's a discount on that, then you, you flip then, then basically that offsets the debt, offsets the liability on the balance sheet, but it gradually gets amortized through the income statements over the period of the, um, of, of the loan. Uh, so this is actually a negative debt. This is a, a positive, this is a debit this's an asset on the balance sheet.

So I need to basically multiply that by minus one to give me a minus 12, uh, figure. Um, there, now I'm almost done. I'm almost done. Just a couple more lines to go. Um, and you can basically see, um, I'm not far off. My balance sheet is almost balancing.

So it's just a couple of other items, uh, that I need to, uh, to pick up. Uh, there's one item I'm going to assume that, um, I pay off the, um, I make a contribution to the, um, the pension fund. The pension pension fund is underfunded.

So I'm gonna say equals, um, page up. And I go and grab the, uh, pension fund, the underfunded pension funds, which is here multiplied by minus one. So I'm assuming that that's, um, disappears. And then one final item, uh, equity.

So the equity I've, I've incorporated all of the debt that I've raised. Um, what I need to do now is just incorporate the new equity that we raise to finance the L B O. And I say equals.

And what I basically need to do is I need to zero out my existing equity.

So remember, basically you'll have a small holding company, which will basically acquire this particular business, um, acquire the equity in this business, and it will issue new equity to finance that acquisition. Uh, and so therefore, what I need to do is to get rid of the existing equity that will disappear on consolidation, um, and replace it with the new equity that we have raised to acquire, uh, this business. So I say minus the, uh, existing equity.

And then I say, uh, plus, and I go page up and I say, I've got brand new equity of 579, but offsetting that, I've got some transaction fees over here.

Uh, where's my, there's my mouse, um, Ian C 17, I've got some transaction fees.

And you'll remember that you can offset, um, equity issuance fees against the equity. Um, so that's what I'm gonna do. Uh, here I hit return. I hope this works. And it does. It works. Exactly.

And now I've basically got, um, and I've got a balanced, uh, balance sheet.

And now basically my spreadsheet works.

So what I've got just recapping, I've got an income statement that basically is split nicely, um, between the income statements, pre-transaction and the income statements after the transaction.

And just looking at the bottom line, 65 and and 19, it feels reasonable for a three quarters, um, uh, one quarter, uh, split.

But then the complex bit is that basically I've put all of those deal transactions through.

I've basically pushed forward my balance sheet for three quarters of the year.

I've made all of the adjustments for the acquisition, and that basically gives me an opening balance sheet, um, here.

And that's really important.

That's the balance sheet that basically drives the goodwill calculation.

That's the balance sheet that basically we start with. Um, and then the, uh, we end up with the, uh, the balance sheet, um, at the, um, the balance sheets at the end, um, of the year.

And most of this you'll see for things like receivables and inventory and that kind of stuff. Just basically linked back to the year end balance sheet as at the end of 2018.

So we now have a spreadsheets that works and it covers that complex. Um, a complex, uh, it covers that complex, um, uh, uh, uh, uh, stub, uh, period. Okay, so we're done, we're done on that. So, uh, well done if you've kind of followed that through. Um, so, uh, just in recap, what's the sort of best practice for doing this? Well, I would suggest, uh, that what you should do if you're interested in this is download the full version of this and work your way through this particular tab, this particular, um, uh, this particular, um, uh, calculation.

Um, and I think it's, uh, really pretty complex, but I think it's, um, it's, it's, it's um, it's, it's it's work. It's, it's really it useful to understand the complexity of this transaction. Best, best practice in terms of producing one of these things is that you need to do everything with a single assumption. And if you take a single, and what I mean by that is something like the deal date, you need to input that just once in your spreadsheets. And you need then to create flexible track flexible formula.

So using some of those date formulas, uh, like, um, um, uh, EO month and year frack, um, that will basically help you look at proportions of years before and after a particular date. Really useful. Remembering that the, that it's fairly likely that you will have a set of assumptions for a particular, uh, for a particular, uh, transaction. But it's fairly likely that some of those assumptions are gonna change.

And one of the key ones that's bound to change is the dates.

It's extremely likely that the date will get pushed a little bit to the right.

So that's why it's important to have a flexible, uh, spreadsheet. Secondly, um, try and keep the calculations around that construction of that deal year separate from the rest of your model. It's complex.

There are a number of steps in getting there, and you don't really want this to, uh, get, uh, to confuse your normal, um, spreadsheets, the normal, um, output from your spreadsheet. So it's a good practice to basically do this transaction, the deal, uh, as we've done here, the deal date transaction. It's good practice to basically do that, then keep that isolated. Um, and then think about things that may be impacted by that split of the year.

So things like interest, make sure that your interest calculations are flexible enough to respond to part years. Um, so things like interest, things like goodwill, things like depreciation, things like capital expenditure.

Make sure that the calculations of those are flexible enough to respond to those, uh, different periods. Okay. So we're done. And okay. Thank you very much, uh, for joining us. I hope it's been useful, um, and look forward to seeing you on a later webinar. Thank you very much. Okay, cheers then. Bye-Bye.