Transcript
consolidation model let's look at the components of a consolidation model it has everything in a typical three statement financial model meaning assumptions and each of the three statements it has everything in a DCF model as well meaning the calculation of free cash flow and net present value and then it has assumptions from multiple business units so instead of just one operating company or one operating unit we have a tab for each of them separately there are full-blown models for each of the businesses then what we do is consolidate all of them into one business let's look at the purpose of a consolidation model it's usually for internal budgeting and planning purposes with larger companies that own multiple subsidiary businesses or different business units they need it for detailed reporting it's also used in corporate development for M&A it's also used in valuation purposes by investment bankers and equity research professionals if you want to learn how to build this model from scratch you can take any of these three courses particularly the M&A modeling course let's jump into the model now and look at it in detail here we are inside the consolidation model on the cover page we have links to each of the business units corporate overhead and the consolidated model let's start by clicking through to business unit number one in business unit number one we have a typical financial model it has assumptions and drivers then the three financial statements any supporting schedules a DCF model and sensitivity analysis so this business unit can be evaluated entirely on its own when we open it up we see a typical CFI style of financial model with a nicely linked set of statements and DCF then we have business unit number two is structured in exactly the same way and business unit number three then there's corporate overhead in this section all we have are the expenses that are borne by corporate overhead or adjustment that need to be made to any of the business units if there are cost that can't easily be allocated to the business units themselves it may make sense to have this separate overhead tab and then finally there's the consolidation tab in the consolidation tab let's open it up we don't have any operating scenarios because this is the sum of individual businesses and each of them have their own operating scenarios but then what you'll see is a green colored formula the reason it's green is because it's linking to other worksheets and if you look at the formula the revenue line here is the sum of the revenue in each of the business units and any adjustments from corporate overhead so we have the income statement balance sheet cash flow etc and then the DCF model below that and in the DCF model we actually have a breakdown of the consolidated business by business unit and then the negative impact of corporate overhead expenses or other adjustments so as you can see this type of a model makes it very easy to see the consolidated view of an entire business as we see here but then drill in deeper to each of the individual business units see their operating scenarios and how they all add up to the consolidated total let's look at a quick example of conceptually speaking how to add another business unit to this model imagine of the company is going to undergo an acquisition what we would do is take one of the existing business units say business unit number three right click and select move or copy once you select move our coffee you're gonna tick this box here that says create a copy and we're gonna put it right before the corporate overhead so it says here before sheet and we want to be for corporate overhead let's press ok and then we see that it's copied over let's change that to business unit number four once we've done that we would change all of the numbers of course since it's a different business we would change all the assumptions change all of the the revenue numbers etc and then we would go to the consolidated tab and we would just have to update all of these green formulas so it may seem like a lot of updates to make but since the structure is identical assuming we've left it as identical then all we have to do is here add a plus and let's go to this is the revenue in 2013 business unit for the revenue in 2013 and press Enter now that formula can be copied all the way across and it can be filled all the way down and once you go through and fill it for all of the green cells which won't be too hard then you'll have completely updated the consolidated model to include this additional business unit now you may be wondering what if the new company has different line items in most likely will most companies have very different items on their income statement balance sheet etc the key is to try to remap the financial statements so that they fit together and in our M&A modeling course we show you how to remap statements so that they can fit with other businesses that's an entire exercise in itself but just know that it is possible and there is quite a bit of detail around how to do that check out the M&A modeling course to see how to do that and make sure that everything consolidates nicely together