Merger Analysis with Pro-Forma Consolidation in Excel

[00:00:01]okay um I'm making a video and uh finally kind of finished with our little case study about this uh airline company that I still Air Freight uh company that happened a long time ago but it demonstrates for me it has a whole lot of lessons maybe a whole lot of other uh merger cases have have these lesson now what we're going to do in this video is put together a merger case and uh this uh model has a whole lot of references because it doesn't have the two uh individual company models in the in the file yet but I just want to tell you first the errors that were made in terms of paying ridiculous fees to borrow uh incredible amount of money uh and assume that a very small company could somehow magically fix the problems of a much larger debt Laden company with hindsight were just crazy the fact that you wouldn't make a downside case that says look what happen happens if we don't fix this company what happens if the company remains problems remain that doesn't strike me as a extreme downside case and that's exactly what you want to do with the models because things like debt to Capital debt toah all of that stuff doesn't mean a lot until you really make a a scenario analysis that's kind of a lesson the real lesson of this one so here's what we're going to do in general uh we have a sources and uses statement kind of made but we need to get the starting point from a company so we're going to show you I'm going to show you how to take numbers from the balance sheets of the two companies work through the proor statement we already did that in another video but this is more of the mechanics of just how to uh how to uh uh put put everything together then we'll include some merger assumptions shift control C is I just pressed and then we'll um uh uh uh put together a Consolidated analysis which includes like any model in the whole world getting the dates right getting a starting point right getting the operations and and uh capex and then depreciation and working capital rights and uh finally uh putting the the financing together okay so we're going to do that it looks like they're a whole lot of references but don't worry I don't think it's going to be that bad so let's get our acquiring company first which is uh uh this one called Kitty hwk okay and why don't we just well how about this let's we need really three things we need the scenarios the assumptions and let's uh move these into the uh this one we'll move them uh before this one and create a copy okay uh and we have some uh uh double names click yes to use that or no so we'll name this code one put now put and call one I don't know what that is and oh this is for the uh oh [ __ ] replace the rest okay so this is the uh acquire scenario acire assumptions now the reason we're doing this or I'm doing this is no oh [ __ ] it looks like I should have uh crap um oh I have to um I have to also take the uh functions from here so we better go to our developer go to the macros go to the functions edit this and uh okay I'm sighing again I have a feeling okay and uh I'm afraid that uh we already have some of these functions in here oh great okay I didn't it turns out I didn't have to do that because I had copied it later that was just a little bit of a waste but does show you have to copy all the functions as well okay and then let's uh close this other model and uh uh let's get the Target Model okay and I'm going to do the same thing uh let's get the UPS let's get the scenario the assumptions and the financial model I hope um this will be enough and let's move those to the same place create a copy okay and this one I'll put [Music] to we have the same kind of data table excuse me it's too bad this range name stuff sucks doesn't it and then let's put Target scenario Target assumptions and Target Model now everything here's what we would like to do hopefully when we rerun these cases I hope it it looks like it it changed we want to be able to change these uh assumptions and see how this doesn't affect each individual case but affects the whole uh whole whole case now we can start I'm signing in by matching we're going to use the match a lot but we'll match this is the the target uh company acquiring company sorry and we have I thought we had the period number okay just a minute oh God just a minute okay uh this is really bad I need to put the uh um the period so I just press alt Eis and uh transaction period here is number six and in the acquir model we have to do the same [Music] thing shift oops shift right arrow D enter I got rid of the 9 months thing at the top and then we'll uh start by matching the period number of the target company acquiring company okay and we'll do the same thing here match the so we we just need the column number to kind of get everything really started and that's sounds like I wasted a lot of your time on this and all that but it's actually really important you might want to put this in as a date you might want to have all sorts of uh uh quas things in there no uh the first thing we need to do really is once we have this column number which came from here now we can uh um we can U find the row number of the various titles for the acquiring company and I hope that's here all the way down to the balance sheet we have to be careful I hope I did this okay these this is a simple balance sheet and it's in column number c okay so I am sorry about uh going back and forth see okay so uh we can uh go all the way down to the bottom hopefully control control D and I was wrong [ __ ] try it like this one too [Music] uh what's all okay okay well I got [Music] one and I have to uh pause for a minut okay that actually was a pain I had to REO a couple of the titles now uh so once we have the balance the row numbers and we can use we can uh look in theel for the r number and e47 no I would what we do is just simply take the entire just be a little bit lazy somebody thinks uhoh this is copy oops it doesn't work because we have to attack lock these in because if we're going to copy them down it's going to look in other places for them now we have a completely flexible um approach and of course our balance sheet this balancing just pretend for just a moment that that wasn't the case but and of course I'm going to have to uh delay this because I used to get a lot of emails that was really cool I got emails but a big email I got was can you help me my balance sheet doesn't balance and that is a enormous pain okay so we do the rest of it down here okay the same thing down here now did I uh okay so let's pause this and find I know what the hell's going on okay I'm going to try to continue but uh this is I know what well this might be really really horrible cuz I just got off the plane and I just had a couple glasses of wine and sleeping shouldn't really do well so what I'm doing for the rest of this by the way the the uh I hope I took the right [ __ ] just okay I'm pausing already okay so I'm going back to the B sheet the bance she yeah that was really good all right and then we're just going to link up the assumptions now we have the same period in each model I can't remember exactly what these things were let take them out so we put a match of this column against again everything was going to come out of the m so we match this against this one don't need a a zero match second one things in the second model okay and um control R okay and we uh then once we have the columns we just start filling it we put it with index and I probably could make it very fancy and automat and put a row number here and then put an indirect here but I think it's almost going to be just as easy to kind of go because there aren't all that many items so I would go straight to the uh the financial statements click on the entire line and go to the back to the mer oops take the uh calling number and we'll do the same thing for the other company now so we you can see that the whole thing I mean I uh like I said my friend Victor he says he's going to get his his dogs and match an index and you can see by that be a good good thing to name your dogs if you want dog oops course did it wrong okay stting so we go [Music] to okay of course shift control R oh my God that if we would use index I guess I can show you how you wouldn't use index we could get the row number oops that's and we' still have to go to the acquire mod next one is the total cash operating cost we just put any any of the R numbers so if you look at this you need the Target Model and index so what you'd have to do is put X and then you'd have to put in direct and then I suppose you could put now that could of course be [Music] a press the F4 key until we get here I we have to put another thing like this has to be and then we can go to the cage this of course I put a semicolon instead of a colon and I didn't uh close the back in and it still didn't work okay so um if you use the indirect command it wasn't that b you put in the sheet name first then you put in the r number and then up to five now the knows about that is uh if we would go to the capital expenditures here all we would have to do is uh find the capex down here which is 230 Capital expend appreciation just simply go to the depreciation L better step up and go to the profit loss somewh and then we put together all the assumptions but we want the total conr assets so we put the r number for the conr assets and you know I messed up the balance sheet just a little bit here but not so [Music] bad okay and that demonstrates that you really get all this stuff from uh from ear about sh okay and uh same thing with the current liabilities okay so maybe I'm uh arguing for using the indirect command although I don't think it's a very big deal in this case in other cases it certainly is and uh we get the working cap we other assets and other liabilities ladies and gentlemen this is special announcement for passengers Eric cter and passenger Sheila cter traveling to Miami on British Airway service b209 please make us I thought he was a football player okay and I could put a r number other income just in case go back up to the profit assumption uh and uh the minority interest I guess that's just comes from Keta model so um once we have these things copy this okay just took a minute don't worry I think it just takes a minute okay and uh then we'll do the same thing clearly for the other company and that doesn't not look very good because there should be some current assets onur I mean I spend % of my time at home I do more at this problem espe the us know when like 12:00:00 where I am and you know they start 4:00 in time afternoon 10:00 at night and then 1 hour call pain and that's cop was across Elric B okay and uh so that's the indirect command and I'm not going to do this again for the what I'm just uh finishing this up now and um uh there are a few things you can do now I I copied these little spinner boxes into the case so the first thing you can do is see what happens to the accretion of delution and you can see well this how it dramatically depended dramatically on what case you um assumed for the for the the um acquiring comp the the the target company in the base case you had some accre not dramatic now what you can also do then is just just illustrates this now to do if you do this I had to copy and paste the cash so and I changed this so if we if we would pay less for the company let's say we pay 10,000 instead of 5,000 then uh I mean 20,000 we save 20,000 you can see that uh actually the the the uh you can see the effect if we give give them only 1,000 shares then we have a much higher accretion if we use is a different share price assumption uh it wasn't that uh big in effect if we didn't buy the uh additional equipment that's the effect and we have to make sure that of course that would uh if we not affect the cash flows that would affect the cash flows so if we only pay 5,000 fees that has a little bit of an effect so you can see all the transaction uh kind of assumptions and then you can say well okay what happens if we this this number now is a flexible number but what happens if we issue no no no we could uh we'd have to switch that around sorry we'd have to switch that around to we could make the residual the shares to the public or the the fixed amount so if I could uh leave this in as the as the oops excuse me this is the the amount of the debt we issued so now let's switch this around so now instead of uh we we'll leave the residual as the shares that we have to issue to the public now if we have to issue shares to the public [Music] um where did we put the amount of new debt new uh this was the amount uh the shares issued here okay this is 33 + 8 transaction assumptions um well we'd have to uh this really should be computed as the cach I'm going to get a circular reference I think but the cash from the share issuance is divided by the share price this is very so uh let's I got to work on I got to fix that at any rate uh we can put different uh transaction assumptions and see what happens to the accretion and delution and then really importantly if we have a low case we have to we need to raise all this amount of new debt even if we have the U company case we have to even that we have to raise a little bit of new debt so we better have a revolving credit agreement here um in the base case how much did we have to raise a whole bunch of new debt so this was less than 100,000 in the base case 140,00 am I looking at the oh I guess in know what has to look at why why that accumulated debt changes but essentially you can look at the there the uh the credit analysis in the lowcase we got way up here we're clearly not able to finance anything okay and I'm going to stop this video now done enough damage here okay and this is going to be Kittyhawk col model I don't know why in the heck this happens when you CH save the file the graph somehow disappears okay that's enough EV that