Welcome to this session. We've got an hour from now should we should be okay for time.

So this session is a series of four sessions on more complex M&A modeling. And it follows on from the session I did a week ago where I basically looked at some of theories, some of the technicalities, some of the more complex areas of an acquisition model. And I also set up the spreadsheets set up the model that we're going to be using over the next three weeks.

And I went through the, broad structure of the model and also the assumptions tab, all of that, very first tab with the assumptions on which set up the model. But we didn't do any actual real modeling last week.

So this week and the next two weeks basically is where we're gonna start the modeling, where we're gonna look at the same spreadsheet. So that's why you need to grab the three models that I indicated in the resources button or in the use the, use the link in the chat box.

And we're basically gonna be looking through the, we're gonna spend most of our time today looking through one of the tabs of the model, the more complex of the tabs of the model.

So just a tiny little bit of introduction. if you weren't here last week, my name's Phil, Phil Sparks, and I'm a trainer at FE. I've been a trainer here for about two years. And prior to that, I've done a lot of financial training for about the 13 or 14 years, before that see another couple of people joining. So welcome, to you. So I'll be doing today.

I did last week, my next week, I'm involved in something else. So my colleague Maria, is going to be taking over the next two, sessions on the same spreadsheets, and she'll be moving on to the more complex areas, the calculations and the forecasts and the construction of the new combined, company as a result of this acquisition. So before, just before we get started, I want to go back and I'm just gonna look at a couple of the tabs, a couple of the, the slides that we actually looked at last week to give you a little bit of an introduction to today's session.

And the first one I want to look at is this one here, the nature of an acquisition. And there are broadly two ways that you can deal with going out and buying a company.

And I've just looked at what sort of opposite quadrants oh where's my, where's my pen? Here it is two opposite quadrants.

Either you can go and buy assets, and often that is accomplished using when you are looking to acquire, part of a business from an already privately held company.

And the motivation to do that is partly because you want to cherry pick the assets. You want the assets, certain of the assets, but you don't want other elements.

I that you don't want all of the business. Or alternatively, there are certain liabilities, certain slightly scary things that you don't want historic warranties, hence historic tax liabilities.

And you just sort of cherry pick your way, uh, through the assets.

Now in terms of accounting for that, if a business simply goes and buys a secondhand factory, a secondhand machine, a bundle of accounts receivable, then the entry is just as if they were buying those from scratch. So you would buy the machine at its fair value and you pay a hundred thousand dollars for the machine, and your entry is PP&E goes up by a hundred thousand dollars and cash goes down by a hundred thousand dollars. And really, that's all you are doing in buying those assets.

The complexity comes if there's any goodwill if you pay more than the value of those assets for those assets.

If you pay more than, if you pay 120,000 for the, for the machine and it's only really worth a hundred thousand then what do you do? Because if your goodwill only arises on, consolidation on an acquisition, a business combination, so what happens is you have to bump up or inflate the value of some of those assets that you buy. Now, you need to be very careful, you know, presumably you are buying those assets, you are buying those machines because you think they're gonna generate very healthy profits in the future. However, you need to be a little bit careful if you bought the assets for a very high price that you don't face enormous immediate impairments in the value of those assets because you can't justify the carrying value of them. So that's a little bit tricky, but that's, I guess the flip side of the fact that if you step up the value of those assets, then you get a tax deduction going forward.

And basically the fact that you've paid a hundred thousand dollars for those assets means that the value of those assets is a hundred thousand dollars, because that's what you paid for them. And then the alternative, the more complex scenario is this bottom right hand side one here where you basically buy the shares of a particular business, you buy the business outright. And in that case, there are lots and lots of accounting rules. And fundamentally, what we do is we consolidate, we consolidate the assets of the target company.

So we bolt on, we combine the assets of the target company, with the assets of the existing company. So to a certain, to a certain extent.

In terms of the balance sheet, the end result is largely the same.

You've still got the new assets that you have bought, added to the existing assets of the original acquiring business.

But the complexity here is a little bit more involved in terms of the accounting. So what would happen is you consolidate all of those and assets, you do, and I'll just jot some notes to the side here.

You do step up the value of any assets.

So if you thought that the machine was worth 120,000, not the not a hundred thousand, then you basically fair value those assets up to 120,000, not a hundred thousand. That in turn, increases the depreciation going forward exactly the same as if you just had an asset deal. But in this case, the downside is that because the ownership of those assets hasn't changed, the, the, the assets are still owned by the original target company, according to the local tax authorities, there's no tax deduction, there's no tax benefit, no increased writing down allowance allowed by the tax authorities on those assets. And therefore, you often end up with a deferred tax liability as a result.

Basically, your tax that you are going to pay will be lower than you would would be higher than you'd imagine because you're not getting that extra deduction until you basically sell the assets, and gets what you know, an amount that is related to its actual value rather than its historic value. Secondly, you're gonna end up with goodwill.

So you are likely to buy this business for more than the sum of its nest assets, and that will create goodwill.

Remember that goodwill only exists on consolidation.

You don't have goodwill in an individual standalone company.

You only have goodwill when you've basically bolted two businesses together. When you've gone through the pro, when you're going through the process of consolidation you'd also need to zero certain items out. So you'll need to zero out the equity of the target. So basically, if you buy the shares of a target company, then to all intents and purposes on consolidation from the outside world's perspective, those shares no longer exist. Those the only shares are the shares in the top company in the holding company.

So as you go through the process of consolidation, you're gonna zero out those target company shares.

You might well zero out the debt in the target company as well. If you're going to refinance the debts. If the banks of the target company, because there's a change of control, insist that you refinance that debt, refinance those loans, then it's likely that you're gonna have to repay those loans.

And that would be an entry when you take over that company.

And that's it. That's it.

And you basically bolt bolt everything together as a result of that acquisition, and you end up with a set of consolidated accounts. Now, that's what we are going to do today. That's all we're going to do, is we're basically going to look at the combination of the acquiring company and the target company. You'll remember that we were looking at Bayer by the, a European company buying Monsanto, a US company.

And we're basically gonna bolt those two businesses together, and we're gonna create an opening balance sheet, and it'll take us a little while. It'll take us a little while. This is probably the most complex set of transactions in this model.

And then I'm gonna leave my colleague, Maria, to deal with the forecasting of the future forecasting of the combined business, and then analyze in the last session, analyze that combination and decide whether it's a good deal for Bayer, whether it's earnings accretive or dilutive. Okay, we're gonna look at one other slide again just before we leave off. And again, we looked at this, we looked at this last week when we looked at the two businesses and we looked at bolting the two businesses together.

And basically I took you through the first couple of steps of this.

So we had, in our model last year, last week, we basically have a forecast for Bayer, and we had a forecast for Monsanto. And we also we needed to do some adjustments because we basically had, Monsanto in dollars, and we needed to convert that into euros.

Also, Monsanto's year end was August, whereas the Bayer, the year end is December.

So in bolting the two companies together in forecasting the results of those two companies, we needed to calendarize Monsanto to get its numbers to a December year ends. And again, I showed you briefly how to, how to do that.

We also spent quite a lot of time last week looking through the acquisition assumptions.

We basically went through that fairly substantial tab on the spreadsheet where we looked at things like how we value the company on acquisition.

Oh, my, my screen just jumped around a little bit, I'm hoping, hoping that's okay. Yes that's rather strange. That's rather strange. So my, my screen just jumped around a little bit it looked like I'd lost everybody, but everyone's still there, so that's good.

So we looked at lots of assumptions on the setup of the deal.

So how much debts, how much equity, the split of debts and equity you might remember, there's some complex debt as well. There was a convertible bonds as well as straight normal debts, and also some equity as well. There was lots of fees involved.

And FX rates and step ups and brand valuations, all that kind of good stuff, all that complex stuff. And we looked briefly through all of that. So what are we going to do today? We're basically going to do this thing here. We're gonna do this thing here.

We're basically going to look at the proforma balance sheet where we're gonna take the two balance sheets, the acquirer's balance sheet, the targets balance sheet. We're gonna bolt them together, and we're gonna go through all of those balance sheet entries.

We're gonna go through all of those unusual balance sheet entries to create a consolidated balance sheets that we're going to set up, which in turn will drive the balance sheet for the next something like 10 years of forecast for the combined business.

So, without further ado, we're going to go and open our spreadsheet. Now, there were three spreadsheets for download. There was an empty and a full, and then one in the middle, which if you are looking at the size should be in between.

And the title of that is Advanced m and a Modeling Bay at Monsanto, opening bss. So opening BS for opening balance sheet. So that's the spreadsheet that I want you to to open to download. If you want to follow this, if you want to complete this while we're doing it.

So as you can say, at the top it says Advanced M&A Modeling Bay at Monsanto, opening bss.

And that's basically because we've got an empty tab here, which represents the opening balance sheet that we are going to construct today. So we're gonna go through this tab and basically for complete the complete this tab. Now the good news is it's just one tab.

The bad news is there's some reasonably complex entries that we need to go through. Now, you'll see if you actually look on either side of this tab that we've just deleted the numbers on this particular section.

So when we get to the end of this section, if you're following it, you'll end up with a model that works in its entirety. Now, the rationale for that is there's a lot of forwards and backwards referencing this spreadsheet. So, for instance, the goodwill calculation early in the spreadsheet relies on this particular, balance sheet that we're gonna put together in this particular section.

So when you get these reasonably complex spreadsheets, you do have, references that go a little bit forwards and backwards.

And so it's not always possible to do, to produce this in a purely linear way.

And you will remember that, you know, when you're doing basic three statement modeling, exactly the same things, happens, you construct your income statements, then you construct your balance sheet.

You can do, you can work out your your retained earnings from the movements, from the net income, from the income statements.

But the last thing you do is when you've done your balance sheet, you don't do cash, and then you do your cashflow, and then you need to push your cash back into the balance sheet.

It's exactly the same with this. There's an element of reliance on later tabs and sometimes an element of reliance on earlier tabs.

So we've just sort of blanked out this one sale, sorry this one tab we haven't got rid of everything further on the right hand side.

So the objective of all of these webinars is not to kind of mechanically take you through each of these, each of the construction of this, this complex model.

But it's really just to stop and pause on the hard bits, on the complex bits. And the construction of this opening balance sheet is absolutely one of those hard bits. So just a tiny little reminder.

We went through the assumptions to have, and I've highlighted the assumptions to have just to just a reminder of this. We went through this. It told us, about Bayer the acquiring company their market capitalization, their shall price.

Then we had the target company, Monsanto, and you might remember this number here, 56 billion euros. It's your, sorry, that's dollars. You've got to be careful with this. The flip between dollars and euros, uh, $56 billion for the equity to acquire the equity in this company.

You then had a long section down here, which basically talks about the financing, talks about the splits of debts and equity.

And you can see three big numbers in there, $19 billion of equity, um, which include $4 billion of a convertible bonds.

And then the balance is made up of the $37 billion of debt, that they need to raise. Now remember, that's just for the acquisition.

We're also going to raise 37 billion, sorry. We're also going to raise the debt, for of the target company. Now that's, this is one of those sort of backwards and forwards items. Here.

You can see that it says target debt, net debt refinancing up here, and it's zero.

And the reason why it's zero is because we haven't yet constructed the opening balance sheet.

So this figure here is relying on the balance sheet that we are going to construct in a little while. So then we also have a little bit further down.

We've got a little bit in terms of step ups. So we've got brand value, we've got PP&E step up and the depreciation calculations that go with that.

We've then got a standard sources and uses of funds. And as I say, part of this relies on elements that we haven't yet completed because we're gonna complete those, uh, today.

And also you'll remember that we've got sources and use of funds in US dollars because we're buying a US dollar denominated company, but we also have on the right hand side of that the equivalence of every single line. It just translated at the FX rates, which is the North Point nine five over here on the left hand side. because obviously when we get to construct our combined business, our opening balance sheet, that's for Bayer, which is a European country, company, it's a euro denominated company. So we need a lot of these transactions a lot of these elements in Euros, not in dollars. And therefore we've got a Euro equivalent for things like, the targets equity, the debts raised, the equity issued, and so on.

so as I say, you'll see that the sum of these things like the net debts needing to be refinanced and also the book value and existing good goodwill of Bayer, or a lot of those numbers are not there because they are reliance on the opening balance sheet. And you can see in the reference in the formula bar that it says equals opening balance sheet to D28.

And that basically means that there's nothing there yet.

Because that's what we're about to produce. Just a final other thing, there are a couple of switches over here. So there's an element here that says, is it a secondary issue? So are we issuing to issuing shares to the market as a whole, or are we issuing them directly to Monsanto's shareholders? We're also, we also have a switch down here for amortizing debts. And also here again are we going to refinance the targets, net debts? Now this one says yes, but because we don't yet have the net debt in Monsanto, then at the moment it looks like we're not refinancing that in the sources and users of funds.

So we're gonna go off to our opening balance sheet tab, and we're gonna start putting together our consolidated balance sheet.

Now you'll basically see that there's two items here right at the beginning.

We've got the acquirer's balance sheet, and we've got the targets balance sheet. Now, just again, a little reminder the acquirer's balance sheet is here. This is Bayer's balance sheets, and we're basically buying the company as at the end of December 2016 and therefore this column here column F is the one that we're going to reference to.

And obviously I'm pointing at the income statement, but if you go down a little bit, you can then see that we've got the balance sheet of Bayer, the acquiring company in Euros. So we're gonna be pointing towards a lot of these items here, which are already in Euros. That's okay.

And you can see just a tiny little sort of subtle point here.

You can see that I've got cash in row 53 and column F, and that's therefore the first thing that I'm gonna pick up. Now, if I go and look at Monsanto, first of all, you can see that I've got Monsanto's balance sheet, but this is first of all in US dollars, and secondly, it's as at August 2016.

So if I want the December 2016 numbers, I'm gonna need a number.

That is, if I go answer cash somewhere between these two figures here, 1,736, and 1,026, which is assuming that it moves smoothly it feels to me like about 1200.

Sorry about 1500 US dollars, somewhere between 1,736 and 1,026.

It's gonna be closer to the August number than the, than the next August number in 2017. But I then need to multiple, I buy 0.95, which is my FX rate, to convert it from dollars into into euros. Now, if I go to the next tab here, which is calendar, target acquisition, fx, and scroll down, you'll see that in F53, I've basically got exactly the right number.

Now it says it's 1,400. Now, if you remember, it was something like 1700, or about 1200.

So we thought it was gonna be about 1500 in US dollars multiplied by 0.95. It fill, that fills about rights 1,400. It's the right sort of, it's the right sort of ballpark and that's as at December 16.

And the clever thing is with this, it's in column F53.

So there's a level of what you call matrix integrity. Here.

We basically have the two balance sheets structured in exactly the same way, the same items in the same order.

So once we've basically constructed the very, once we've basically pointed at the very top line, it should just be a matter of copying down and we'll, we'll check as we go along, we'll make sure it works, and we'll certainly make sure that our balance sheet actually balances, but we should be in a fairly good position where we can simply point at the top one and then copy down for each of these two tabs, and hopefully end up with the with picking up all of the correct figures and getting a balance sheet that actually balances. So that's what we're going to do. We're gonna go to opening balance sheet.

We're gonna go to the very top line here. We're gonna go to cash.

And we'll just do the assets to start with.

We'll just do the assets to start with.

So here goes equals hold down control.

And page up will jump me across the tabs, and I go to acquirers balance sheets, and we're acquiring this business as at the end of December, 2016. I go down to F53, I click, I just make, just before I hit return, I'm just gonna make sure that is the right number. Yes, it is the right number, 910. And that's the, the figure we get there and it says acquirer F53. Now, just because I'm there in the right place I'm gonna do the same, but for the targets balance sheet in Euros, at the how, having been calendarized to get to the right year ends. So I go equal, I don't, I say equal and I go control page up.

and now I'd only only have to go one to the left.

I scroll down again, F53 gives me that 1,427 number.

I hit return. And I'll just to show you formula text, I'll put the two references just alongside so you can see where these amounts come from. So there we are.

So we've got acquire F53 and we've got calendar targets F53, and we don't need to do any other calculations, no other adjustments don't need to adjust by for FX because it's already there within that calendar.

Target acquisition fx tab. And if I'm comfortable with both of those, then I can, and I'll do this for all of these, I can simply copy those down all the way to other long-term assets.

And I'm just gonna jump back and make sure that I have that, does get me down to the same place in the targets balance sheets, let's just go and have a look.

So I get to calendar targets, fx, I've got other long-term assets, and that's line 60, and I just need to remember this number here.

18,681. So I'm gonna jump back.

Oops, just one row. I'm gonna put an alt equals at the bottom Copied. That's copy one at the right. And what was the number? 18,681. So I've done a sum at the bottom, and I'm pretty sure that that's adding up the correct numbers.

And then I can do the same thing with short term debt. Now, I might, if I'm very lucky, if I've got the right number of spaces, I might simply be able to grab one of these sales up here, hit copy oops, I'll do it for two.

Do it for two. I'll hit copy and paste.

And I get short term debt for the acquirer and 1,008 for the target. Let's go and have a look and just check. First of all, they're pointing at F63.

Let's just go and see that zero and 1008 is the right numbers from those source tabs, well, what's the number? 1008 here for Monsanto in Euros, and it's row 63. Let's go and have a look at the acquirer, um, short term debt zero.

And that's row 63. So again, looks like I can be comfortable with that.

Just gonna scroll down, what have I got in the left hand side of my balance sheet? I don't have any subtotals, basically, I've got all the way down. I've got, I don't have any subtotals for non-current assets or current assets, so I'm not gonna have assets on the liability side of the balance sheet. Sorry about that. I don't have any subtotals for current liabilities and non-current liabilities and total liabilities or equity.

I've just got all of the individual line items with a single total at the bottom, 734,408 for the acquirer.

And if I go down to the equivalent figure here 18,681 for Monsanto.

So let's go back to our balance sheet, our opening balance sheet. Let's copy all the way down.

And then let's put a total at the bottom, alt equals.

I think I've managed to add, Think I've managed, I think I've managed to, yeah, I think the non controlling interest is plainly not right, so I need to be a little bit careful with this. I'm obviously, it obviously made, uh, jump jumped a little bit ahead too quickly. So I think I've probably got some subtotals in here. Let's just go and have a look at what we've done wrong.

And this is kind of real life, isn't it? You know, you do a, you do a spreadsheet. So I basically have got it starts at F63 and goes all the way down to F74. Let's go and have a look at our acquirer.

It starts at F63 and goes all the way down to I just need down to 72, go down to at 72.

That's all I need. So I think I've gone a little bit too far.

So let's see, what have we got? What do we what have we, what have we not got? So I need to be a little bit careful of that and I can see, I can see my, I can see where I'm going a little bit wrong.

If I go back to my opening balance sheet. I've got some, extra lines in here. I've got some extra lines in here. I've got a convertible, bonds I've got some debt fees.

And I've also got convertible bonds, debt fees. I've got convertible bonds and debt fees, which are new items.

These are items that are in my combined balance sheet that aren't in my source balance sheets.

So what I need to do is I need to basically get rid of all of this.

I'm fine up until long-term debt, but then what I need to do is I need to go and find the deferred taxes line in the source balance sheets. So I'm going from F67, um, which is long-term debt, and now I need to go and find deferred taxes.

I'm gonna say equals page up acquirer.

So I've got long-term debt, and then I need to go to F68 deferred taxes column, column F that's 811.

I hit return and I'm probably gonna be okay. Now I need to do the same thing for Monsanto.

So I need to go to row F68 equals F68. There I am F68, 88.

And now I think if I copy this down and then add everything up at the bottom, I should hopefully get a number that adds up. Yes, it does, it does. My balance sheet now balances.

So just to kind of put some little titles alongside there, here we are. See, these don't have anything at the moment. So now I've got a balance sheet, the balances, and I'm comfortable with it. And again, I, you know, it was a little bit intentional there, wasn't it? I was actually pointing these down just copying down as we would do.

And then forgetting that in my sub, in my, combined balance sheets, here I've opened up a couple of extra lines for new things that are a result of the are a result of the transaction. First of all, we got those convertible bonds, that's a new item, a new form of debts that we didn't have listed out in the targets and the acquirer's balance sheets.

And we also have the debt fees alongside those that are basically where we amortize the fees over the course of the loan. Okay? So we have a balance sheet that opens that, that, that we have pointed at our two target balance sheet to some extent. All we need now to do is the adjustments and then add it all up at the end. So here we are. So we're gonna start going out, going down. Now we've got some elements here.

So the first thing we're going to do, just describing what we're doing here first thing we're going to do is we're going to zero out certain things on consolidation.

And the most obvious ones that we're going to zero out are the equity of the targets. The second thing is the goodwill on the targets.

And the third thing we're going to do is we're going to zero out the debt on the target if we are going to refinance the business, if we're going to refinance the targets debt.

So first thing we're going to do, is we're gonna start right at the top and we're basically going to say, we're going to zero out the cash if we are going to refinance, if we're going to refinance the opening, if we're going to re, sorry, if we're going to refinance the debt of the target business. So to some extent what we could do is just take this 1,427 and say, equals 1,427 times minus one. And if we're gonna refinance, that's absolutely fine, but we've got a refinance flag somewhere on the assumptions tab.

So let's just go back to the assumptions tab and somewhere, it's actually the last place I was. We've basically got a tab here, sorry, a sale here that says refinance. And it's either one or zero, one for refinance, zero for not refinancing. And basically, if you look up here you can see that it says let's take the opening balance sheets lots and lots of items on here.

And then we're going to refinance that.

and we're pointing at a refinance flag. Now, why that's turned into refinance, a word refinance rather than just a reference to a particular cell, it's because if we click on this cell here, cell C35, you can see up here in the very top left hand side, it doesn't say C35, it says refinance.

So refinance is a named cell. That cell can be one or zero.

So this is a really easy flag, isn't it? We basically to just say, if we are, if the refinance flag is one, then we're going to zero out the cash.

If not, we're not gonna zero out the cash. So this is very easy to do.

So we simply say we don't even need to do an if statement because it's one or zero. We're simply going to multiply the cash balance by that refinance flag. If it's one, then it'll pop up with the cash balance.

If it's zero, then it'll be zero.

So we can say equals cash times refinance.

And you can see just up here it basically pops up and says, do you mean this name? And the answer is, yes, I do.

So I can hit tab to accept that, and then I want it to be negative.

So I say times minus one, and that gives me 1427. Now, just to kind of make sure that that is actually working, let's just go back to that assumptions.

Let's change that cell to zero, which is acceptable.

Let's go back to our opening balance sheet, and you can see that it's gone back to zero. So that's, okay.

So now we've got that one named range. This is really easy.

I'm gonna make it back to one so I can see that it's actually working.

This is actually really easy because all I do now is I simply go down on all of the pieces of debt in the target company.

I just basically use the same entry here, the same cell.

So I copy that and I go down and I say, do I have any short-term debt? And I do have some short-term debt, so I'm gonna say copy, thats, and then you can see that's zeros out.

And you can also see if I go down to the long-term debt of 7,087, I do exactly the same. And I'll just put some references alongside, just so you can see.

And that works. Okay.

The next thing we are going to look at is we're basically going to look at where am I Two, three.

Okay, the next thing I'm going to do is I'm basically going to get rid of the existing, uh, goodwill. I'm going to get rid of the existing goodwill.

So remember what we do here, we always, when we're calculating the new goodwill for an acquisition is we zero out any existing goodwill on the targets, balance sheets. And that has the impact of basically lifting that goodwill from the targets balance sheet up to the consolidation entry.

And the subtle reason why we do that is that when we are doing a goodwill impermanent review at the very top level, it forces us to look at the entirety of the ballot of the business that sits underneath, and we basically look at the entirety of the goodwill that's, represented by that business. And it forces us to do, an impairment review right at the very top level.

So we are just simply going to zero out this goodwill here.

So I'll go equals 3,823, multiply by minus one.

And just to sort of complete the circle here to complete this, let's just go back and have a look at that assumption.

If I go back to the assumption here, you can see that within the equity within the goodwill calculation on the assumptions tab that we looked at last time, you can see that I've now got that existing goodwill reducing the net assets that I have bought. So it's correct, it's doing the right thing.

You'll also see that I highlighted these two amounts here. These in blue, because initially they were zero, and that's because we didn't have an opening balance sheet for one Santo.

Now we've just created that.

You can see that these both linked to the opening balance sheet. So column D, which is the Monsanto balance sheet.

Let's go back to that opening goods opening balance sheet.

So we've done most of these zeroing out aspects. There's just one more to do, which is just simply looking at the shareholders' equity.

And again, what do we do on consolidation? We always, always, always zero out the equity of the targets, because from the outside world's perspective, Bayer shareholders own both the Bayer business and the Monsanto business. There are no now external shareholders in Monsanto, Bayer bought all of those shares. Now, it might have financed it with more Bayer shareholders, sorry, with more Bayer shares, but still they are the external shareholders.

The acquisition of the existing Monsanto shares by Bayer means that those are now not in the outside world. They don't exist outside the consolidated business.

So we simply say equals shareholders equity multiplied by minus one.

And that dissipates. Now, of course, this won't add up to zero. We won't, we can't sort of check that our, our entries and our balance sheets will add up to zero. It's not gonna work at this point.

Because we've still gotta basically pay for all of these things that we've just zeroed out.

So I'm just gonna put a tight a reference in there.

And now we're gonna start going through the financing adjustments.

So we're gonna go and look through the signup financing adjustments. Now, most of these are basically gonna come from the assumptions tab.

So this is us raising debt and raising equity to, accomplish the acquisition. So this is fairly straightforward stuff. Now, most of this is basically gonna come from the sources of funds.

Need to make sure we point at the Euro version of the sources of funds.

So the first thing cash you might remember and I'll point at it in just a second. When we get to the assumptions tab, you might remember that we used a little bit of bay's existing cash to pay for all of the fees in the acquisition.

So we're gonna point at that number. We're gonna say equals, and then go back using our control page up back to the assumptions tab. And we're gonna go to somewhere up here. Where is it? Excess cash. It's this cell here.

So you can see, I'm just sort of pointing at it. It's the top line of the, uses of, sorry, sources of funds.

And basically what this is, is this was us using some of the existing cash that's Bayer has got to pay for all of the fees. You might remember, if you add up all of these fees here that I'm just waving my mouse over basically if you add all of those up, it ends up being equal to that 543. So it's these, it's the euro equivalent, not the US dollar equivalent.

but this is negative. This is a reduction in Bayer's cash.

So I need to say multiply by minus one, I hit return.

And we've got that there. And I will get, I'll get rid of the explanations of the formulas as we gradually go down this. But I'm just showing you as we go. Okay, next thing we're gonna go down to the, long term debts. Lemme just check that I'm pointing at the right one. Okay? So I'll go down to the long term debts. I will get rid of these two.

So I'll get rid down to, I'll go down to the long-term debt.

And basically we are going to go off and grab the, all of the new debt that we have issued.

So we go equal control page up, we go to our assumptions up here, and it's again, from, where's my mouse? I've lost my mouse.

it's over here from the sources of funds.

It's this 42. This was basically the balancing figure, wasn't it? It was the balancing figure. How much debts do we need to raise to balance out the cost of equity the total consideration less any new equity that we have, raised by way of the convertible bonds and the new shares.

So this was the balancing figure. 42,722.

And this is in euros. So this is a cool 42 billion euros.

It's a positive number. And so it goes in here.

We then need to go and get that convertible bond, which is also a debt issue. You might remember that was $4 billion.

So we're gonna go and find the euro equivalent of that.

So we go equals page up back to our assumptions and we go and grab that convertible bond issue, which is the next 3804, a lot in euros alongside the 4 billion euros of the 4 billion dollars equivalents.

And then our final piece is equity raising, new equity.

So I will tidy this up in a little second and get rid of all of those references. So here's where we go and buy where we go and go look at the new equity that we have raised.

We go equals page up again to the assumptions number and it's somewhere over here. There we go.

Equity issuance. Again, just making sure I'm pointing the right one. Yeah, 14,265.

so this is the remaining piece of the equity that we have issued.

Okay, next thing we've got two lines, two columns to go, fees and, goodwill. So this shouldn't be too tricky, and I will just get rid of these titles just to tidy it up a little bit. Okay? So there we go. Let's go and find our fees. Now, there were basically two types of fees. So the first fee is that, that relates to just to the debt.

And then we've got fees that relates to equity.

So first thing we're gonna go and find is the fees on debt.

So I go down to here just making sure I'm in the right column.

And it's, we've gotta be a little bit careful with this.

so this is equal to, I've got two pieces of debt fees.

But remember what we do with this, we basically park it on the balance sheet and it's a negative amount, a size alongside the debts that we have issued.

And basically you amortize that through the interest line during the course of the life of the debt.

So basically if you borrowed a hundred and you paid five of fees, what's gonna appear on your balance sheet? Basically 95.

And that five of either discounts or fees gets amortized gradually reduces, through the interest line as you basically go through the life of the debt. So I'm gonna say I equals, and I'm gonna put a bracket because I'm gonna grab two amounts.

I'm gonna go page up back to my assumptions. I'm gonna go and find the fees.

I've gotta be a little bit careful they're the fees that are in euros.

And basically you can see I've got fees here of two items.

This I might I'm just point, I'm just checking that I'm reading. Along correctly, it is the debt fees and also the convertible bond fees. So they're both debt fees and I need to make sure I'm in the Euro column, not the US column. So it's eight eight plus, and I'm just gonna use my mouse to point to click the next one, H9 and then I'm gonna multiply by minus one at the end so that I get 250.9 and I recognize that number.

Now the other item is in equity, and there are two sorts of fees here.

Basically we're gonna post both of these fees here, but they are different sorts of fees.

The M&A fees is just expense through the income statements, expense through the income statements today, during the course of basically building up to the acquisition, it just gets expense through the income statements.

The second item is the fees relating to equity issuance, and they also get part to equity, but to different parts of equity, the F the the E, the equity issuance goes to equity, the M&A fees goes to retained earnings, but that does in turn, ripple through into retained earnings and therefore ends up in the same equity line. So same thing we're basically going to say equals grab an opening bracket, go page up to my assumptions, go and find those fees.

And I've basically got two items, 94.7 plus the equity fees.

And again, I'm gonna use my mouse plus 199 point seven hit return, hit multiply by minus one because they're negative amounts and I get 294 and that's my fees.

And then I've just got one final piece, one final piece, and it's the goodwill adjustments. But there's a few elements here.

This is not just the goodwill, it's things like the step ups as well.

So we're basically just again, gonna point at various elements in that assumptions tab.

So the first thing we are going to do is we're gonna go down to PP&E and we're gonna go and find the, find the step up on the PP&E. Now, you might remember from last week, this was a billion dollars, so we're looking for a number of around 950.

Let's go and find that equals control page up to assumptions. And what I really should have done really was just drag the assumptions tab alongside this tab really just for the purpose of while I'm doing my while I'm doing my construction of my spreadsheets. And here's my, um, PP&E step up.

It's here, it's in G 27.

now that is just to be a little bit clear about that, that is the 1000 down dollars down here, billion dollars of step up.

When you multiply that by the FX rate of oh 0.95, you end up with 951 of PP&E step up, that's in the right direction. That's an increase in my PP&E.

Now what else have I got? I've got the goodwill as well. The total goodwill, again, that's gonna come from my assumptions tab equals page up back to the assumptions tab.

I go off to my goodwill and I'm gonna grab that bottom line figure.

That's huge number of 43.4 billion euros of goodwill. And I think just kind of pause a little bit here, pause a little bit here. We might remember, if you look up here, this business cost us $56.9 billion to buy.

And 43 billion of that is goodwill.

And not only that of that good getting to that goodwill figure is a brand of 14.2.

43 plus 14 is about, is pretty close to the $56 billion that of consideration that we're actually paying.

Basically we are getting very little in the way of net assets for this business. The vast majority of what we're paying for is brands, is intellectual property, is know-how, is reputation is the staff.

It's basically the future earnings potential of this business.

We are paying for very little in by way of PP&E and real assets on the balance sheet. Anyway, there we are, 43.4.

We hit that number and we get that cool enormous number there.

And I've got one more item. Remember I just mentioned brand, we're gonna grab the brand here as well.

So we say equals page up number of times, where's the brand? It was somewhere over here, brand value.

There I am. It's this at 14,265.

Brand value. So again, this is, this is, these are the big numbers.

These are the really big numbers in constructing this opening balance sheet. That's not very much else. Now also you might remember, and I'll just again, get rid of the CLU on the screen, you might also remember that fur valuing those assets.

So fur valuing the PP&E up fur valuing the brand up creates a deferred tax liability.

So we need to put that deferred tax liability in here somewhere down the bottom and we've got a line for it here.

It's in this line here in line 24 deferred taxes. So again, I'm just gonna go and grab that number, there we are, and somewhere here we've got a deferred tax liability on step up. And again, I just think it's interesting. Look, we've got a deferred tax liability of 5.3 billion euros, which is actually more than the book value of all of Monsanto's assets.

So we are creating a deferred tax liability that swamps or dwarfs the the book value of their assets this is a negative amount on the goodwill calculation because it's a liability, but in terms of putting it into the balance sheet, because it's on the liability side of the balance sheet, we need to multiply by minus one and that goes into the bottom half of the balance sheet. And that's it. We're done.

All we need now to do is moment of truth is basically add everything up to the right hand side. And hopefully when we do that, um, we will basically have a balance sheet that balances. I copy to the, I i I select all the way to the right. I hit alt equals at the end, and I get a total, I go down to here, copy down, and then I'm gonna grab one of those totals over there and I get a hundred and forty nine, two five moments of truth.

If we do the same thing on the bottom half of the balance sheet do we get the the same total down to the bottom alt equals a hundred forty four nine two five? I've got a balance sheet that balances excellent and as if by magic, it's about two minutes to three.

So that's just about my hour up. So there we are, we now have a consolidated balance sheet that works. It balances.

And you can see that all of those complex elements that we've been through in constructing that opening consolidated balance sheet, I'm not gonna say that what my colleague Maria's gonna do next week is an, in any sense, easy. There's still some complexity to come.

Someone just saying it's nearly three o'clock, could you go over the tax differences of assets versus equity purchases and the implications on deferred tax again please. Okay, very, very quickly I'll do that. If I go back to my, one of my tabs, basically this one here. So in the last two minutes, just before we do, sorry, I didn't see that Q&A it wasn't in the chat box.

I didn't have the Q&A box open as well, so I'm sorry about that.

I will just mention this very briefly. So you take an asset purchase, if you buy assets out from underneath a business from an existing business, then those assets, if you, let's think of a sim simple example. If the assets were on the balance sheet of the target company, it was PP&E and you bought them for a hundred, then the selling company gets taxed on the tax on a gain.

But you as the acquirer bring those assets in at a hundred, on to your own balance sheet, and then in your own accounts, you depreciate those based on that higher value.

So if you said they had 10 years, 10 year life, you'd say a hundred divided by 10 years, I'm gonna depreciate them at 10 per annum. The tax man will let you do the same.

Now, his tax treatment might be slightly different in the UK that for a lot of assets they have a 20% writing down allowance every year. But basically he, because you have bought those assets for a hundred for the higher value and you really have bought those assets for a hundred, then he will basically give you a tax depreciation, a writing down allowance, um, for based on the higher value. Now, if you buy shares in that target company and you fur value the assets from 50 to a hundred, you don't get the tax benefits.

You do fur value them up.

Your depreciation on consolidation will be based on the higher value.

But according to the tax man, the tax authorities say that those assets are still owned by the target company. They're still owned by the target company. Though though, the ownership of those assets hasn't changed. So the tax man, the tax authorities will still depreciate those assets based on the old historic 50 value, not the 100 value.

Now, when you eventually sell those assets, then you know

you potentially end up with a higher gain or a lower gain depending on which, whether it's an asset purchase or a share purchase.

But you don't initially get the benefits of a right, a higher tax writing down allowance if you have bought shares in the company.

That in turn basically means that you create a deferred tax liability.

Because what it actually means is you are going to be paying a higher level of tax on consolidation. If you've bought the shares then your depreciation would expect you to believe, basically your depreciation on consolidation is gonna be high because you've got the step up. Whereas the tax man doesn't recognize that step up and therefore he gives you a lower level of tax depreciation and therefore you don't get that tax benefit. Hence, you end up with a deferred tax liability, you are paying a little bit more in tax than you would imagine. Okay, I hope that answers that question. Sorry.

I'm not quite sure how long ago you answered you asked that question.

I hope that makes sense. Okay. I'm done.

if you basically download, if you have downloaded the full version of this, if you've downloaded the full version of this spreadsheet, then you should have the final version of this already complete.

And that gets you in a great position for next week when Maria is gonna take you through some of these, later tabs, the more complex tabs.

She's basically gonna look at how you actually start to construct the forecast, the combined forecast for this combination. Okay, done. That's one minute past three. So that's my hour. Well and truly up.

Thank you for your attendance. Thank you for your questions.

I hope that's been useful. And Maria, we'll see you next week.

Okay, thanks a lot then. Bye guys.