Create Your Excel Pro Forma Template for Accounting Effortlessly

Excel pro forma template for Accounting

hey this is Chris from Mission capital and in today's video I'm going to show you how to model one of the most confusing parts of an lbo buyout model so let's pretend I have a hypothetical company here that I've determined is worth 25 million dollars and I know that from doing some diligence over the last couple of months and they also have an ebitda of about 3.9 or 4 milliamp what I've done here is I've compiled my sources and my uses and if you want to see some details on that look through my profile for an older view a video on the sources and uses but basically I have to bring some cash to the deal to pay for it and then that cash all has to go somewhere just like if you're going to buy something from the grocery store you have to bring your credit card or cash to pay for the groceries and so how do we model this how do we bring the sources and uses that reflect the transaction into the balance sheet because this is one of the hardest parts of building out an lvo model and the nice thing is you can actually break it down into seven steps so I'm just going to go through them quick because this is a you know a video video for social media but the first thing is you eliminate the Old Co cash so I've got my balance sheet of what it's going to look like before the deal closes and the first thing is get rid of the old cash so I'm going to do a negative do a negative on this this cache and then that comes out of the retained earnings so I'm going to do equals and get rid of that cash and so now I have a cash free balance sheet that is in Balance why am I getting rid of the cash flow because this is value the seller has created in the past and that's value that they're entitled to I'm not going to keep that if I acquire this company it's theirs so they get to keep it so that step is done I'm just going to check that off yes you can use emojis in Excel if I can just get this right okay so then the next one is bring in the new Equity well I have the new Equity here in my sources right I'm bringing that to the deal so I'm going to come over here again the source is the new equity I'm going to link to these right there and just quickly do control D to bring them down and then my offsetting entry here is the cash right this is Cash coming into the business because I'm bringing in new Equity so that one's good then the next one is to bring in the new debt again this is another part of my sources I have new debt right here and so I've created lines for them on the balance sheet again a new source of debt I'm going to put them right here linking to my schedule and quickly pressing Ctrl G to bring them in my offsetting entry again to the cache I brought this cache into the business so that's done but now I've got to pay the seller right all of this cash isn't going to sit on the balance sheet it's not going to have 26 million it comes in and it goes out to buy the business from the seller so the next thing is now I have to buy out the old equities let's look at that here's the old Equity I'm going to come over here now I'm flipping over to the uses so it's going to be negative contributive capital the negative retained earnings and then what I have to do is I have to calculate some form of Goodwill because the fair market value of the equity that I'm acquiring is greater than the book value you can see here the book value is only about 6.6 but I'm saying the fair market value is 24 7. so that premium goes to the Goodwill so I'm going to say here's the fair value of the equity here but then I'm going to strip out what I've already purchased which is the book value and basically I'm calculating the premium so that's the Goodwill piece got that in there and then I'm going to pay off the old debt right because I'm bringing in new debt to the deal so I'm going to do negative the old debt and now you can see the offsetting entries right now the cache is starting to go out right I brought in the cache and now I'm paying off the equity paying off the old debt to compensate the seller so I've got that one done then the next is the transaction fees which I have down here diligence fees and sponsor fees those are fees that I have to do to get the deal done those are going to hit my retained earnings the second the deal closes so it's the negative sum of my fees right there close that out you can see that's in my retained earnings and now I'm paying that in cash basically and then lastly is the financing fees so anything I raise in debt is going to have a corresponding fee which I have here for this video I am going to capitalize it in the asset section there's new accounting rules to do this a little bit differently but this is kind of how you used to do it and just for modeling purposes this is simpler so this is going to come to the balance sheet my financing fees I'm going to hit enter again that's cash I've paid out because I've brought it to the business and now you can see leftover over on my balance sheet I've got the 500 of cash which is an entry that I specified here and now I have my brand new balance sheet with a new cash balance new debt and new equity and so if I want to change my ending cash it will change ingly see if I just make this a million you can see now my ending cash is a million so these are the steps that you have to go to go through to map out the sources and the uses on the balance sheet to reflect an lbo or a buyout so we can just check these off because I'm a completionist so again I know that's a lot of detail for this video this is one of the hardest parts of an lbo model so you have any questions just let me know thank you so much for watching I will see you in the next one