First of all, my name's, uh, Phil, Phil Sparks, and I'm a trainer at Financial Edge.

Um, this session is all about mergers and acquisitions, and particularly about some of the complex areas around, um, uh, around that subject, around buying, uh, a company, about integrating a company.

Um, so, um, there's a list of items on the right hand side that we're gonna deal with, uh, briefly.

So just to give you a little bit of a flavor of what we're gonna do, we're gonna spend about two, gonna spend about, um, 10 or so minutes just looking through some slides, looking through, through some theory about, um, how to deal with some of these more complex areas, um, uh, within an acquisition.

And then we're gonna sort of, uh, move on to looking at a, um, a, a download. We're gonna look at a, a real model, um, and that model incorporates some of these complex areas.

So we're gonna show you how you can deal with, uh, some of these complex areas in an acquisition model, and also how you can impact, how you can basically put in a level of flexibility into, uh, that model.

You should see there, um, seven spreadsheets.

Um, uh, there are basically, uh, three pers of spreadsheets, um, which are basically an empty and a full version of each spreadsheet.

And those are basically good exercises, good introductions, good examples of how to deal with some of these more complex areas.

You should also see within, uh, those, that list of seven spreadsheets, one that says part complete, and that's the important one.

Uh, that's the one that we're gonna look at later, uh, today, um, in about 10 minutes or quarter of an hour or so. And we're basically gonna go through that.

And it is, as, as the name will imply that it is a part complete car, part, complete model of an acquisition.

So some elements are, are already keyed in, some of the basics are there. And I'm gonna show you how you can use Excel to model and hopefully model flexibly some of these, uh, more complex, um, areas.

So I'm gonna open up, I'm hoping that my machine will actually work.

Um, I'm gonna open up, come on There. It's

A set of, uh, notes.

Um, I'm hoping this works. Apol again, apologies.

Just everything's very sluggish at the moment as my machine reboots itself.

Okay, so we're gonna just jump into, uh, this, and I'm just gonna deal with some of the basic issues with a merger and an acquisition. Some of the things that become a little more, um, complex.

Um, there's an assumption here that you know how to do the basics of an acquisition.

You know how to buy, uh, buy a company and consolidate it, and you know how to buy a, um, uh, an investment in a company, um, uh, and equity account for it.

If you're holding it, say 30 or 40%, um, there's an assumption that you know the basics.

If, if not, then look back over our previous webinars. There's plenty of webinars that take you through the basics of acquisitions.

What we're dealing here with is the, is the fringe stuff, the, the, the slightly more niche areas.

So here's, uh, a good one to start with.

You know that if you're gonna go and buy a company, then what you need is the diluted market capitalization that basically gives you how much you're gonna have to spend to buy, uh, this company.

And the key word in there for this subject is dilution.

Basically, if you have some dilution, particularly because of options that senior executives hold, then they all crystallize on acquisition. They, they, they be that you have to buy out, uh, those, uh, shareholders, uh, or sorry, those option, um, holders.

Um, this is a, a little sort of niche area, a little fringe area in relation to that.

Of course, the dilution itself, um, the, um, you have to buy the, the extra shares you have to buy to satisfy those option holders is incorporated within the diluted market capitalization.

But what about tax? Well, the tax man is often a little bit, um, uh, sort of, uh, almost old fashioned, uh, basically in relation to options.

Uh, tax only gets triggered in most jurisdictions when those options get exercised and they basically caught turn into cash.

Um, and it's the same, uh, uh, on an acquisition.

Basically, if a senior exec exercises those options gets bought out at a higher, uh, price, um, and those options, uh, trigger those options crystallized, then he as an individual will get taxed on the gain he has made.

But likewise, um, the company can claim a tax deduction on the gain that it, that on the same gain.

Um, and therefore it's relevant for an acquiring company because, uh, they will basically get a tax deduction as a result of, uh, those options being exercised.

Uh, so you calculate this by simply looking at the profits on those shares, the difference between the offer price and the exercise price, um, multiplying by the tax rates, and that is that, and, and then you adjust the market capitalization by that amount.

That's what this slide says next.

Um, what about if you don't buy a hundred percent of the company? What if you, uh, buy say 80% of the, of, of the company? Now, hopefully, uh, most of you're familiar with what happens in this case.

Uh, there's a non-controlling interest.

And the reason for this is if you go and buy 80% of a company, or in fact any amount, um, over 51%, um, then basically you get control of the assets of the company.

Therefore, the con the accounting standards, whether it's IFRS or US Gap, say that you need to consolidate each line item of, uh, the balance sheet, each line item of the income statements, uh, from the point of acquisition.

Um, and therefore you are showing the company in full in the balance sheet and the income statements.

And therefore, what about the bit that you don't own? So if you own 51% of a business, you are incorporating a hundred percent of that target of each line item of its balance sheet and its income statements, but somebody else owns 49%, and you deal with that by a non-controlling interest, an amount in equity in the balance sheet, and a deduction from your net income in the income statements.

So again, if we have an acquisition that we're going through, uh, we need ideally to create some flexibility in case we don't owe, in case we don't take out the entire company in case we agree that we're only gonna buy, say, 75% of the company.

So we need a flexibility in our model to deal with that.

Next, and apologies, I'm jumping forward a little bit.

I'm just identifying some of these, uh, really key items.

What about pension benefits? What about post-retirement benefits? What about pension funds? Now, these are particularly relevant if you are taking over a company that has got a defined benefit pension scheme and a lot of sort of traditional, slightly older fashioned manufacturing companies, excuse me, um, often have these now, um, because they were very generous when they were established, and they often go back many, many years.

Um, there often can be a mismatch between the assets of the pension funds and the li future liabilities of the pension funds.

So basically, if you've committed to pay your, um, existing or past employees a certain amount, um, and you know, for the, the remainder of their lives, that liability can be very onerous and be, can be in excess of the assets you've put away for that particular, uh, pension fund.

Now, even if that entire pension fund is off, balance sheet is held in trust, uh, for the, uh, the pension holders, uh, the companies often have a responsibility.

Um, the pension fund can often, um, come to the company for any shortfalls, any, um, any underfunded, um, or deficit, uh, position that they may be in.

And therefore, you'll see you'll, so, so if the company is, if the pension fund is overfunded, if it's satisfactorily funded, there's enough way in the way of assets for its future liabilities, then there's not an issue.

But the, if the opposite is the case, if the pension funds liabilities are greater than the pension funds, um, assets, even if that's not actually on the balance sheet of the, um, excuse me, of the, uh, the company, then you need to include a pension deficit, um, over here, um, a pension deficit in your valuation because it's a future obligation of the company.

The company is going to be obliged to top up that pension fund over the next few years, and therefore it sits on the right hand side of your EV to equity bridge.

So again, we need to incorporate that within any acquisition, within any, um, equity price, um, of the, um, of, of, of the, uh, business.

Um, and a couple of other, uh, relatively small items.

The first one is, uh, working capital, excuse me, working capital.

So working capital. Um, if you basically buy a company, often the valuation is on the basis of a relatively, um, steady or typical level of working capital.

And basically the way, what, what happens is, if in between exchange and completion, I agree in the deal and the deal actually going through, there is a movement in working capital.

Often the terms and conditions of the, um, sale and purchase agreements, um, basically mean that the purchaser has to pay, the purchaser has to pay for any movements in the, uh, working capital.

And the rationale here is that the management of the target company, um, shouldn't be, have any incentive not to just run the company on a normal ongoing basis.

They need to pay their suppliers, chase their customers for payments and so on, and not kind of play with the, the working capital position for the, um, uh, for the, for the benefits of the, the selling, uh, company.

And so, although will almost certainly be some sort of working capital adjustment in the final transaction, in the final, uh, price, uh, the final amount that is paid, uh, for the company.

Now, the often the other side of that is that it's financed because it's on a short term basis with a revolving credit facility.

So you often see that within your sources and, uh, uses of funds.

Um, I'm not gonna deal with, uh, calendarization that's hopefully familiar with, uh, from previous, uh, versions, previous, uh, webinars that you have, uh, seen.

Final thing I just want to look at is towards the, um, end here and it relates to, uh, deferred tax, um, and asset step up.

Now, again, hopefully most of you are familiar that if you basically buy a company, then the accounting standards, whether it's US Gap or IFRS, obliges you to fur value the assets that are acquired.

And that obviously has an impact on the goodwill because the, the net assets of the business you are acquiring can go up or down.

So for instance, if you buy a business, it's got some, it's got a building a property that's in its books and has been there for many years at a historic value.

Uh, you, you, you, you're obliged to fair value that, uh, to bring it up to its current, uh, market value, which if the assets go up and the consideration doesn't change, that means the goodwill goes down.

But what happens in relation to tax? So the argument goes something like this.

Um, if you basically buy a business and you fur value the property upwards, um, but you buy shares in that business, you buy shares in the company, um, rather than actually buying the assets individually, then according to the tax authorities, from their perspective, the ownership of those assets hasn't changed.

It's still the company that, but that owns those assets.

So the tax treatment of those assets also doesn't change.

So you might find that the tax authorities are assuming, uh, a value of the business, which is based on its his, uh, value of the building, which is based on its historic value that you bought maybe 20 years ago. Whereas you have further valued that business up that that building up as the acquirer and therefore your depreciation of that business is significantly higher than the tax authorities are assuming.

Um, and therefore that gives rise to a deferred tax liability, basically, um, uh, a deferred tax item, deferred tax balance, which reflects the timing difference between the tax treatments of depreciation and the accounting treatments of depreciation.

Um, so that's another thing that we need to bring in here.

And again, because you are establishing a deferred tax liability on the acquisition of the business, that's another fair value adjustment.

So that's something that you need to bring in to your net assets that they are when they're acquired, and therefore, in turn, that will impact the goodwill that you calculate on acquisition.

So lots and lots of complexity, uh, to deal with.

Remember, this session is recorded, so if you want to go back through these slides, want to look at them a little more slowly, then uh, feel free to do that from the recording, um, at the end.

Okay, so we're gonna dive now into an Excel spreadsheet.

Now, as I mentioned, um, in the chat box, you should find a link to, uh, our website.

Um, and that should show you seven spreadsheets and the one that you want to download, the one I would actually suggest you download all of them.

But the key one that we're gonna look at for the next sort of 40 minutes or so is one that says parts complete.

So if you can open up the parts complete spreadsheets, then you'll have the same thing on your screen, uh, that I have got as well.

And I'm hoping that I can manage to get this on the screen as well.

Uh, okay, just there we're, give Me a little moment so I can just make sure this is on the screen and I'll drag it across.

Here it comes.

Oh, okay. So here we go.

Um, so again, just, I'll just give you a whistle stop tour of this, um, of this, uh, model.

And then, uh, we'll go through and start completing all of the tricky bits on this.

So, uh, it's a fairly standard, uh, model.

Um, we're just gonna look at this first tab here, the one that says m and a model with complexities.

Uh, there's another one on the right hand side, which, um, has got a real company, uh, Kellogg and Pringles, um, and it's got a real, uh, acquisition here, real transaction, um, here as well.

But, um, this one is the one that we're gonna look at.

So on the left hand side, we've got some deal assumptions.

So how much the share price of the target company and the, uh, acquiring company, uh, were, um, what's the mix of, uh, debt and equity to finance that, uh, thing, uh, this is a real key one in, uh, the middle 80%.

Uh, so we're acquiring 80% of the, uh, business, but we can adjust that.

So we could acquire a hundred percent of the business, 80% of the business or even, uh, less and equity, uh, account for it.

Um, and you'll see lots of various, uh, lots of other items down here.

So things like fees and tax rates, barely standard stuff up here on the top right hand side, we've got a fairly typical sources and uses of funds, um, which unsurprisingly they balance.

Um, and I've also put alongside most of these items, uh, the, um, the formulas of where these, uh, where these entries, uh, come from.

And then finally over here on the right hand side, um, this, this is the real sort of key to driving this, uh, spreadsheet and making it a little bit, uh, flexible.

What we've got basically here are, um, three kinda switches.

Um, and only one can be open at any, uh, one, uh, time.

And so, excuse me.

Um, uh, so what we've basically got here is a switch for full consolidation.

And if you look at the, um, the formula there, it says if C 14, which is our ownership stake acquired is greater than or equal to 50%, then make that switch one.

So basically, if you need to fully consolidate the target company, uh, because you've got control, you've bought more than 50% of the business, um, then, uh, you basically, uh, um, then you, you, there's a one here, um, eh, um, cost accounting. What it basically means is we don't need to equity account for it, it just goes onto the balance sheet at the cost of acquisition.

It's just a long term investment, uh, almost a home for our money.

Uh, if we say bought 10% or something like that at stake in the business, and we have no significant influence.

So this one basically says, um, uh, if C 14 is less than 20%, then we put a one here.

Uh, so basically if it's, if we've acquired a relatively small stake, then we get a one in this cost toggle in this cost, um, uh, switch.

And then finally in the middle is equity accounting. So it basically says, well, if it's between 20% and 50%, put a one here. And it's just done in a very simple way.

It just basically says one minus the number above or the number below.

So basically if they're both zero, uh, this will be one, otherwise this will be zero.

So this is a fairly, um, a fairly, um, uh, uh, uh, fairly, um, uh, key element to this, uh, to to, to the understanding of this spreadsheet. Now, just a couple of other specific items.

Um, I've got normal dilution calculation here on the left hand side. I'll make this a little bit bigger.

So you can see I've got a normal dilution calculation here, um, for net new shares from exercising options, um, and, uh, a diluted number of shares outstanding.

So basically that's the 31 original outstanding shares plus, uh, two from dilution.

But I've also got here a tax deduction relating to exercising those options, which is basically the five of, uh, purchase, uh, price of each of those shares, less the, um, strike price of three multiplied by the five of, uh, options, um, all multiplied by the tax rates.

And this is the tax benefit of exercising those options of people of basically buying out, uh, the cost, uh, the cost of buying out those, um, options.

And therefore, when we get to this section here, the equity value, the equity value actually is the normal, um, market capitalization based on the diluted number of shares.

But you'll see that it says minus C 12, so minus that tax benefit, that tax deduction.

So this is basically reducing slightly the cost of acquiring this company.

Okay. So, um, we've then got a little bit further down.

We've basically got, um, our purchase price, um, uh, minus the Nest assets acquired, and another little wrinkle in here, another little, um, elements in here.

We've got the Nest assets acquired, but if we look down at the balance sheet of the target company, we've got this D 60 minus D 51, let's go and have a look at what D 60 and D 51 say.

Well, D 60 is the shareholder's equity at down, um, here.

Um, and basically that is, uh, 115, that's a net asset of the business, but you'll see that we've also added back the existing fair value of the, sorry, the existing goodwill on the balance sheet. So we're effectively zeroing that out.

Uh, and you might, and you'll remember hopefully that when you consolidate, you get rid of any goal goodwill that's on the balance sheet of the target company, and that in turn increases the goodwill of, um, on consolidation, uh, for the, uh, the parent, uh, company.

Okay? So what we're gonna do with this is we're gonna work our way down.

Uh, we're basically gonna, uh, construct the balance sheet, the pro forma balance sheet, um, on acquisition.

And we're also gonna construct over here on the right hand side the pro forma income statement for the first three years, um, afterwards.

So we're gonna try and do this in as flexible a way as possible so that if we make some changes between the debt and equity on acquisition, uh, on the, uh, percentage of the acquisition, uh, then, uh, basically our, uh, all of our calculations, all of our spreadsheet will, will continue to work correctly.

So we're gonna start over here. We're gonna look at the, um, uh, oh, sorry. No, we're not, I've jumped a little bit too far. I've jumped the gun a little bit. Uh, I'm just gonna do, uh, the net income calculations just so that I can work my way down the spreadsheet. So the first thing I'm gonna do, I've got the acquirer's net income and the target companies, uh, net income.

So first thing I'm gonna do is I'm gonna say the acquirer's net income is the earnings per share, uh, which would often come from the consensus forecast for earnings per share.

And we're gonna multiply that by the way, that average share is outstanding.

And, uh, there we are.

Um, and I will, um, put, uh, formula at the end of each of these, uh, items.

Then we're gonna do exactly the same for the, um, uh, for the target company.

Just gonna copy that, uh, calculation down.

And you can see that the current company is around 10 times the size of the, uh, of the targets company.

I'm gonna move down now, and we're gonna start looking down our pro forma balance sheets.

So, uh, here goes, so the first thing that we are going to do is we've got these various adjustments, uh, columns, uh, in here.

Now, be a little bit careful.

Um, you would normally imagine that you're just gonna add up across, and this combo common column would just be the sum of everything on the left hand side.

Um, we, with our combo adjust, our combo column needs to be a little bit more sophisticated than that.

So if we are fully consolidating, um, all of the, um, line items, so we do a full consolidation because we own more than 50% of the business, um, then we're just gonna add up the acquirer and the target for each line item and those adjusted, um, uh, that adjustment column as well.

But what we're gonna do is we're gonna, um, we're gonna have a clever, um, sum function in the combo column, which is gonna say if we equity accounts, or if it's, if we are accounting for the business just as a, a, a, a straight sort of 10%, um, investment, then our combo column is only gonna pick up the acquirer, um, column, not the acquirer plus target column.

But let's go and look at the adjustment column first.

So first thing we're gonna look at is the cost, uh, the cost of, in the, the, the cost investments here.

So this is basically saying if we bought the business and we only bought say, 10% of the business, then our entry would simply be, um, a reduction in cash and then increase in the on balance sheet investments. And it would stay pretty much there.

We wouldn't adjust that, we wouldn't change the value of that investment, uh, with the account, with the, um, with the company's profitability.

It just stays at that original cost, um, of investment.

Um, so what we need to do is we need to calculate, um, that if it's, um, uh, if we actually do have, um, if we do actually, uh, have that, that that case, that situation.

So I'm gonna go into this, uh, sale here, cost investments.

And what I'm gonna do is I'm gonna use that switch that we've got.

So I'm gonna go up to g oops, I'm gonna say equals, and I'm gonna go up to G 17 up here.

And G 17 basically says, this is my switch for what sort of consolidation, what sort of, uh, com combination, uh, we're gonna have.

And I'm gonna say, if I have le bought less than 20%, then this cost switch here, this is gonna be, is gonna be one.

Uh, so what I'm gonna do is I'm gonna multiply one by, um, the, uh, cost of the investment and I get that from somewhere a little bit further up. And apologies again, my machine is very sluggish as it rebuilds itself.

Um, so I go all the way up to the, um, the price of the acquisition, which is up here, which is that, um, acquisition price, um, up there I hit return.

And of course it basically, it's zero at the moment because at the moment I'm saying that we've only bought 80% of the business.

So if I go up and I change this stake acquired to say 15%, let's just see what happens. Well, the first thing that happens is this switch changes to be a cost of investments, and that's all it's going to include.

I go down to my combo balance sheet, and now you can see that I've got the value of that equity investment in that target company at 15% of the, of the, of the shares.

And it's in the 24.3.

So it seems to be working okay, I'm gonna change this.

I'm gonna change it now to 40%.

'cause what I want to do next is to deal with the same calculation, but on the line underneath for equity accounting. And you can see I've changed it to 40%.

My cost of investment, my cost of investments, um, has gone down to uh, zero.

So I do exactly the same here.

I basically say equals, and I go and find that switch for equity accounting, which is the, in, uh, G 19, and again, I multiply G 19 by the same figure G six, which is our cost of investment.

Somewhere up here, there we are, acquisition price.

And just before I hit return, you might wonder why I'm just pointing at the acquisition price.

I what I paid for the shares in the target company.

Why am I not including all of my uses of funds? I, why am I not including the fees? And you might remember, fees are often based on the enterprise value of that target company.

Well, the reason for that is, although we'll have paid the fees in terms of cash, because you are acquiring equity, the accounting standards say that that fees figure doesn't go to the cost of investment.

It doesn't go to the income statements, it basically goes to equity.

It goes, you can park it straight at equity.

So basically that's why, um, we're gonna park this down.

We we're not gonna include this within our cost of investment.

It's just gonna go straight down to equity.

So, uh, I hit return and I've now got that 64.8.

So I keep on going, uh, what about goodwill or what about my goodwill calculation? So this is a, now this is the case where only if you acquire more than 50% do you create a goodwill, uh, balance.

So again, we're gonna have an item in here, and I'm gonna say equals.

And my first entry is gonna be to point now at the consolidation switch.

So I'm gonna go up to here, I'm gonna say G 19.

So that's, if this switch is one, if I'm doing a full consolidation, uh, then what should I include here? And the answer is I include, um, my goodwill calculation, which is at somewhere up here, there, it's, so there's my goodwill cal my goodwill, uh, calculation there. It's, so again, in this case, I don't have any goodwill because I'm equity accounting for this, uh, business.

But if I go back and change my percentage ownership from 40% to 80%, then first of all, my consolidation switch changes to one over there.

And then I go down and you can see that both of these items and now zero 'cause I'm gonna consolidate each line item of the, um, of the balance sheet.

Um, but my goodwill has now, uh, popped into here it because, uh, that consolidation switch is now on.

Okay? Um, next, uh, let's look at the financing of this.

So I'm gonna go down to debt.

And we know that this business, this business was acquired with a mix of debts that we issued and equity that we issued.

So we simply need to bring in those two items.

So the first thing we're going to do, and this is the easiest of all of these adjustments, is I just go up to my, um, sources of funds somewhere up here.

And in the normal way, I'm just gonna grab that figure up there, the sources of funds, which is G 11, and that's new debt that I have raised to finance the acquisition.

And then pretty much down at the bottom, um, I've got two items.

The first item I've got is non controlling interest interest.

Now, this gets a little bit more complex.

So non-controlling interests.

What we've got down here is this is only if we are fully consolidating and we have less than a hundred percent stake in the acquired, uh, business.

So what I'm gonna do is build this up gradually. So the first thing I'm gonna point to is I'm gonna point to that consolidation switch G 19.

And this is one of those spreadsheets where once you've sort of done it a few times, um, you start to kind of recognize some of these, uh, references.

So that's the first thing that I point to.

I point to G 19, which says, this is a full consolidation.

I were buying more than 50% of the, uh, target, uh, company. So that's the first thing, the first component.

The next thing I do is I say multiplied, uh, by, um, uh, and I need to work out the stake that the non-controlling interest has in the business.

So the first thing I, so the next thing I do is I go to, uh, I say one minus, and I go to that 80%, I think I still have it at 80%, uh, somewhat up here.

That real sort of driver to our spreadsheet where we're basically saying, um, how much did we take over? Well, if we took over 80%, there's the number, there it is, there's the number, 80%.

Well, if we took over 80%, then the other side, the non-controlling interest must still have a 20% stake in the business.

So that's the next term.

And then we're gonna multiply by, um, the, um, uh, the net assets that we acquired, um, for the, uh, the business.

And that's very similar to the goodwill calculation.

Uh, we, um, we did before.

So first thing I'm gonna do is I'm gonna point, um, at, um, the target company.

So I've got the target company here.

Their net assets is 115, that's the shareholders, um, that's the shareholder shareholders equity of the, uh, the business.

So that's D 60. But remember we have this thing, this goodwill on the balance sheet, and we're gonna fair value that down to zero.

We've done, we've fair, valued that down to zero on our goodwill calculation.

So we need to do the same thing for the NCI stake.

So we need to do the same thing for the NCI stake. So I wanna say, uh, D 60 minus, um, I'm gonna point at that one there, the goodwill on the Targets company balance sheet.

I hit return and I get an NCI stake of 13.

Um, and again, if I basically change the ownership stake to the ownership stake to 40%, this one would disappear because of, that's G 19 that I've got, um, in here.

And then the final entry, the final entry in here is the shareholders, um, equity.

So what have we got here? We've basically got the extra equity that we bought, sorry, the, sorry, the extra equity that we raised, uh, to finance the, uh, transaction.

So the first part of this is fairly straightforward.

We just go up to our sources of funds and we find in our sources of funds up here, the equity finance, the 77.8, but we haven't quite finished.

Remember I said that basically when you raise equity, if you've got some fees associated, then you can net those off against, um, um, uh, against your equity.

Even if you raised money, even if you basically, uh, had to pay some fees, um, that had to go through the income statement, then again, word as that go, it eventually goes into retained earnings. So in either case, what we're gonna find is that our equity, our total equity, including retained earnings, goes down by those, uh, fees.

So I'm just gonna simply say minus I'm gonna stay in the top half of this balance sheet.

I'm gonna say minus the fees up there, minus at G seven, I hit return.

And hopefully I've got a very elite sensible number.

So now we can do the adding up. We can do the adding up of our, uh, balance sheet.

And this again is where we need to be a little bit careful.

This is where we have to be a little bit, uh, careful.

So at first it seems like you would just simply say equals the acquirers, um, cash in this case, um, plus the targets, uh, cash, but we need to be careful.

We're only going to incorporate that targets cash if it's a full consolidation, if it's less, if it's less than 50, 50% and we're equity accounting, or we are just accounting for, its as an, uh, uh, an investment.

Um, then we don't consolidate, we don't bring in the target company's, uh, balance sheet.

So we're gonna multiply that target, uh, entry by G 19 by the consolidation switch.

So this will only bring in that target company asset if we have, um, a full consolidation.

So we're gonna multiply by, uh, G 19 and again told you that we'd start to remember some of, uh, these items.

Um, we're, uh, we are then going to say, um, do we have, uh, anything else? Do we have, um, uh, anything else in the adjustment column? Uh, and so I'm gonna add e uh, 46 as well.

I hit return and there's one thing I need to do.

So I hit function F two, go into my, um, into my, uh, formula.

I need to, I need to make the G 19 absolutes. I've only got one switch, so I need to make sure it stays pointing at that G 19 throughout.

So I hit F four and it locks that.

And then I can copy this down.

Now I'm just gonna put, uh, a little formula text, um, along, uh, side to that equals formula text just to show you what's in, just show you what's in, uh, that formula.

And then I'm gonna copy this down.

Now I'm just gonna get rid of all of those existing formula down here so you can see what we're doing.

Okay. And now I can copy most of this down most of the way, but there are a couple of entries where I need to be a little bit careful.

So I'm gonna copy this down all the way down to PP and E with control D.

And you can see that that's adding up quite nicely along.

I'm also gonna copy it to, uh, short-term debt, other short-term liabilities, long-term debt with Control V.

And that hopefully works, just adds up nicely all the way along.

And I'm also gonna copy it to the NCI line as well.

Now there's just a couple that I need to be a little bit careful of, um, uh, down, uh, here.

So the first thing is, um, goodwill.

Now, you might remember that all the time when we've been looking at the net assets of the target company, we've basically saying, we've been saying the net assets not including the goodwill fur value, the goodwill down to zero.

So if we're taking on board this business, basically we need to fur value, uh, the goodwill down to zero.

So that's what we're gonna do here. We're just simply gonna say equals the goodwill of the parents company, plus any adjustments.

And that adjustment will just create the new goodwill on acquisition if we, uh, fully consolidate the business.

And if we less than fully consolidate the business, then it'll end up at zero.

So we're just gonna look at those.

Um, just gonna look at, uh, the acquirer plus the adjustments, and we're gonna zero out the target.

And then we're gonna do the same thing for shares.

So you might remember, um, oops, went too far.

Um, you might remember that when you take over a company, um, even if you fully consolidate, um, then you don't bring in the shares of the target company, you zero those out 'cause they don't exist as, uh, on the outside markets anymore.

It's just as parent companies shares.

So I'm hoping now that all of this balances, so I'm gonna go to the top, the assets, add them all up, I get 912, let's do the same or for total liabilities, alt equals.

And let's go down and look at total liabilities and equity.

So some of the two lines above, plus the total liabilities up here, hit return and it works.

My balance sheet, uh, balances and that's flexible as well. So this will balance, it will change, it'll still work, uh, whether we own 20% of the business, 10% of the business, 40% of the business, or in this case 80% of the business.

Now let's go and have a look at the income statements.

Let's go and look at the income statements and see how that bill, that's bill up.

And again, we can be cur, we need to be a little bit careful with this to make sure this will still work, even if, um, uh, even if we acquire less than, um, a hundred percent of the business.

So let's go up to the acquire at net income up here.

So this one, no complexity here.

We can simply point at the acquiring companies.

Net earnings, net income, sorry, which is up here.

That's the 138. And there's no complexity here.

I, we'll make this slightly small, smaller just so I can actually see my way around the spreadsheets. There we are. And what about the target companies at net income? Well, the target companies, net income we're going to include, but we're only going to include this if we have more than, um, uh, more than 50% of the, uh, business.

So again, we're gonna multiply the target company's net income and we're gonna bring that in in full as if we own a hundred percent of it, even if we don't own a hundred.

Um, and then we'll take out a non controlling interest if we own between 50 and a hundred percent, but we're only gonna bring this in if we're fully consolidating.

If we own less than 50%, we just bring in simply, um, an element, uh, the equity accounting element, which is our share of the net income.

If we, uh, uh, only, uh, account for it as a, uh, as an investment at cost, uh, then we'd only bring in the dividends, uh, received.

So we're gonna bring in the target company's net income.

But again, what we're going to do is we're gonna go and find that G 19.

So if we basically have that, uh, full consolidation switch on hit F four to lock that in, and then we're gonna multiply that by the target company's, um, net income, which is duh, which is um, uh, in D 40.

Uh, hit return copy to the right now I am now, I'm just gonna put these labels over on the right hand side just so that we'll be able to see them as we work our way down the income statements.

And then we've got some synergies.

Um, again, synergies only gonna exist if we basically take over the business and we've got control over the business so we can influence the business.

So we're just gonna point at those, uh, synergies, which I think was five per annum.

Nice and simple assumption here.

Um, there they are cost savings.

And again, what we're going to do is we're gonna multiply those by that G 19, which is the consolidation switch.

So we're only gonna get these synergies if we've got control over the business and we can influence it and actually change the way that the two businesses work together.

Okay? Function F four to lock that in.

And those are positive numbers, that's good news.

But of course all these things are tax deductible, um, or taxable.

So I'm gonna go to my tax rate, which is somewhere way over in my assumptions in the top left hand side of the, um, spreadsheet.

And there it is. In, uh, C 19, I've got tax rates of 30%.

So I'm gonna say, um, 30% multiplied by the number just above those synergies multiplied by minus one.

'cause obviously tax goes in the opposite direction.

So I'm being taxed at one and a half.

Um, and of course I've forgot to put, I forgot to lock in my, uh, tax rates, got to put dollars around it.

So I hit function F four on C 19, copy it to the right and that now works.

Um, okay, I've also got some interest, which I will see that that will come through.

Um, uh, basically, um, that will, um, uh, that will come through.

Um, uh, whether however I account for this business, if I've raised some money through debt, then I'm gonna have some extra interest.

So what I do is I go up to find the debts that I raised from my sources of funds.

And I don't need to multiply by any accounting methodology.

I've just paid for this.

I've drawn down this debt, I've financed the acquisition with debt.

So I just need to take that 52.9.

I'm gonna lock it, which is F four.

Um, and then I'm gonna multiply by the interest rate, which is somewhere near my tax rate.

There we are cost of debts in C 19 5%.

And again, I lock that in. Um, F four.

Um, and I'm gonna multiply by minus one 'cause it's an interest expense.

And that's 2.6 and that shouldn't change.

And then I'm gonna be a bit lazy.

I know that the tax on synergies calculation, um, works by simply taking the tax rates and applying it, uh, um, to the line above. So I'm just gonna steal that line there and paste it down here.

And that then gives me 30% of that interest on the acquisition debt.

Um, I've then got some equity income.

So what I'm gonna do here is I'm gonna say, well, if I own less than 50%, but more than 20%, then what I need to do is I basically need to incorporate the, um, percentage ownership times the net income of that target company.

So I'm gonna have a number of, uh, multiples, um, here.

So the first thing I'm going to have is I'm gonna go up to that switch for equity.

Um, that's, um, is in g about 18 or 19. There it says G 18, which is the equity accounting.

So if I'm equity accounting for the acquisition, then this will be one.

So I hit F four, um, and then I multiply by the net income of the target company, which is here's the target company, and then I need to multiply by my percentage ownership.

'cause this is for the business, this is for the target company in full or 100%.

So I need to go and find that percentage ownership.

And again, it's up here in one of those assumptions, right at the very top, it's currently saying 80% apologies.

So sluggish, there we are 80% and hits F four to lock that in in terms of the ownership rate.

I hit return and of course it's zero at uh, this point, but if I was equity accounting, if I made that 40%, um, then this number, uh, would update that would be incorporated correctly.

And then the final thing I need to do in getting to my pro forma net income, um, is to take out the NCI share of earnings.

So you'll remember that, uh, this target company's net income, um, is, um, basically, um, that target company's net income, um, is the, uh, the, the, the value of the target company's, uh, net income in full all 100%, um, if we fully consolidate.

But if we don't own a hundred percent, then what we need to do is we need to take out the minority interest, the non-controlling interests, sure of that net income.

So that's what we need to do.

So we basically have, in here again, we have the first thing which says, are we fully consolidating? So we need to go and point to our consolidation switch, which is G 19.

I'm definitely getting familiar with this reference now, G 19 F four to lock it in.

Uh, then I need to multiply, um, by my target company at net income.

And I'm actually gonna be a little bit lazy here, I'm just gonna point at that figure up there at 12.5.

And of course if this was zero, um, then uh, it wouldn't appear, um, here, um, at all.

Um, then I'm gonna multiply by one minus my ownership stake.

So if I own 8%, then this needs to be one minus the 80%, either 20% owned by the non-controlling interest.

There we are one minus 80%. And again, I need to f for that.

So lock it in and I'm just gonna say multiply by minus one at the end.

And that makes a, that makes a reasonable amount of sense.

It's two point a half out of the 12.5 that I have got.

And you can see it's going up a little bit as well.

Get to the bottom of that, add it all up.

And that gives me my pro forma net income.

Um, if I to, if I've taken over this business, taken over 80% of this business, just a couple of last little bits work out accretion. So I'm gonna look at my acquirer weighted average shares outstanding.

Um, and I'm gonna go all the way up to, um, uh, where am I? Somewhere up here. There we're, um, I'm gonna go up to here.

Um, weighted have shares outstanding for the acquirer. It's the same 110 all the way along.

So what would my new shares issued be? Well, I need to go to the amount of equity that I raised from, um, my use, my sources of funds, uh, equity finance function, F four to lock that in, divide by the share price, which is right up at the very top left hand side.

I've got the, uh, share price of the acquiring company of the parent company, right? Almost at the very top.

There it is. Acquire a share price. 18.

Again, I need to f for that to lock it in.

And that gives me 4.3 of extra shares, um, acquired alt equals, add them up, gives me my new pro forma shares, and then I can work out, um, whether we have accretion or dilution. So the first thing to do is to point at the acquirers earnings per share, the existing earnings per share, which we used before to calculate the, um, net income.

So there we are, there's my acquirer's earnings per share at 1.26.

Copy that to the right.

And what's my new, my new pro forma earnings per share is basically that bottom line, pro forma net income divided by the new pro forma, weighted average shares outstanding.

And you can see it's gone up ever so slightly.

So how much is the accretion? The accretion is the new pro forma earnings per share divided by the previous, um, as is or standalone acquirer earnings per share, um, or minus one. So we get a percentage E increase, and I get 4.3% of, um, accretion or dilution and falling ever so slightly.

Um, as the, uh, the co the contribution, uh, from the new company, um, sort of doesn't go quite as quickly as my existing, uh, business.

And just to kind of demonstrate that it really does work, let's go and change our ownership percentage.

So if I change this to say 45%, then first of all, you can see that we equity accounts for this, uh, business, you can see if I look at the balance sheet, my balance sheet still balances.

If I go and look at my, uh, pro forma income statement, then you can see that the only thing that I've now got is a little bit of extra, uh, debt and I've got the equity income, um, here.

Um, and then what does that do? Basically, I've got nice extra bits of, um, of, um, of, uh, uh, accretion going on here.

And let's again just look at it.

If I change that to, um, say a 15% ownership, if I own, uh, it's at 15%, first of all, does, does my balance sheet balance, um, first and yes, my balance sheet does balance.

The only thing that I've got now is that, um, that cost investments here being held at the original price of the investment.

And here's, unfortunately, the income, uh, statement doesn't look quite so good.

And the rationale for that is that, um, the only thing that will go through our income statements if this is the case is basically any dividends that we receive and we don't know the, uh, dividend policy and we don't have any control over the dividend policy of, uh, the target company, uh, now.

So we haven't got any increase in the income statements. We're at the mercy of them, uh, the target company deciding when to pay dividends, but we have still got a little bit of extra interest, uh, netted off by the tax shield, on the interest on that debt.

And therefore that's why our earnings per share is actually diluting rather than, uh, showing any accretion.

So there we go. We've got, hopefully a model that's actually really flexible, um, covers all those different eventualities in terms of the ownership structure, um, in terms of the ownership and accounting methodology.

Um, and it all, uh, works.

So, uh, make sure if you're still online, uh, make sure that you've downloaded both, uh, this spreadsheet and also the solution to this.

Um, and also the other, uh, spreadsheets, uh, as well.

Uh, so you can see there are different exercises, uh, one's looking at things like deferred tax that you can have a look at.

Um, if you want to, uh, delve into this, uh, a little bit more deeply.

Okay. So that's it. We've caught up a little bit of time.

It's exactly on the hour, so apologies for the sort of the sluggish it at the very, uh, beginning, uh, but hopefully, um, that didn't, um, that didn't cause you too, too, too, too much, uh, hassle.

Uh, and we still got that by the end of the hour.

Okay, thanks very much, uh, for your attention and look forward to seeing you on another one of these, uh, very soon.

Okay, thanks a lot. Bye-Bye.