In this lesson, we're really going to amp up our modeling skills. I want to show you how you can take the financial model we just built and convert it into an entity level model that possibly has multiple subsidiaries, divisions, business units or channels. You will notice that in the lessons thus far, I have opted to put many calculations and assumptions inside a single workbook. Now I'm going to tell you why. And that reason is because to keep your workbook and the number of worksheets within the workbook organized by including it all in one workbook, then you can then easily move your model to other workbooks or replicated. To use it as a template of sorts.

You can do this by right clicking on the worksheet tab and selecting move or copy, as I'm going to show you in a moment. So let's get back to a storyline here as simplistic as it may seem Let's consider that model company acquires the shares of competitor incorporated at the beginning of 20 x three. So you now have two companies and assume just for argument's sake that these two companies are exactly the same. You've been asked to build one financial model that contains both subsidiaries, and a consolidated entity. Sounds daunting, doesn't it? Believe it or not, you're a lot closer to being done than you think.

Using our planning approach, we draw out what our model will look like. We have two companies that should be added together to get one consolidated result. However, for those of you familiar with consolidation accounting, you know that there are a number of intercompany transactions that we need to eliminate. And because I don't want to mix up these consolidation adjustments with the two subsidiaries, I'm going to set up a dummy financial model to capture these separately. So in actuality, there are three sub models that people into the consolidated model, as represented by this graphic. To make this manageable, my advice to you is to make each of these models replicas of one another, at least initially.

We will program a few variations in this lesson. But let's stay up front and recognize and remember that because our model just quadrupled in size, so as our risk of error, so to every extent possible, we want to standardize it control the modifications we make to the model after this point. Let's create three bottles of the pro forma worksheet by right clicking on the pro forma tab at the bottom of your screen, select move or copy and then check the create a copy box. Do this two more times so that your worksheet now looks like this. We will rename the pro forma worksheets as follow. Call For consolidated MC for model company, ci for competitor Incorporated, and a for adjustments.

I like using very short titles for my tabs because it keeps your referencing formulas shorter and allows you to navigate between workbooks more seamlessly. One caution here before you start doing this, for the MC model, the model company model, be sure to use the original pro forma model and not one of the copies. And the reason is because of the worksheet that is feeding our capital assets schedule. The replica models obviously don't have separate capital asset schedules. This is an illustration of why I prefer to the extent possible having calculation contained within one worksheet. For our purposes today, we now have four copies of the financial model, which we are now going to modify to achieve the schematic we just discussed.

Let's get to Work con, the consolidated model is going to be the sum of the three sub models. So to begin with, let's change the formulas for each account in the rows to reference the sub models instead of the assumptions. Do not add or change any of the subtotals or ratio formulas, just the accounts. Those are the formulas that previously we had forecast using our assumptions and the 2003 budget. Now, we can copy this formula to all the other relevant line items in our income statement and balance sheet, check to ensure that your balance sheet still balances. However, your statement of cash flows will not for a variety of reasons that I'm not going to get into in this course.

But I have included in the solution to this lesson for those so inclined. The point is we now have essentially created a consolidated financial model that allows us to fulfill Phil, what we envisioned from the outset? Now, how do we go about changing some of the programming of the model to actually make it work. When model company bought competitor Incorporated, it bought some shares. So now we have to adjust our models to reflect this purchase of a long term investment. This is a new account for our balance sheet, because I always want all of these models to have the exact replica formatting and descriptors of one another.

The easiest and surest way to do this is to hit your ctrl key and select each of the four tabs. Now, any edits you make are being made to four worksheets simultaneously. Let's insert a row at row 52. And we'll name it long term investments. In doing so this row has now been inserted into all four of the highlighted models. Now that we've just changed the programming of our base model, we have to think about what other accounts may have been affected by this insertion of an account.

For example, in our two operating models, model company, and competitor Incorporated, we will need to update our formula that calculates the cash balance. So let's just select these two tabs at this time, and change our cash formula in row 4710. Now include the new row 52 long term investments. Next, let's assume that competitor incorporated is purchased by model company for $56,094. The book value of equity at the beginning of 2000 x three, this will be the balance in MCs investment account. Remember to have only the MC worksheet active otherwise you will be inserting this value into multiple worksheets.

Now, insert the $56,094 into cells f 52 and copy it across all years. check to ensure that your balance sheet still balances, which it does. Note that I've inserted hard codes into the programming area of my financial model. This needs to be replaced at some point with a sub schedule that will track the long term investment account. For the time being, I've made the font red to indicate where I have inserted raw data into the model. It's really important to identify when you're breaking protocol so that you can clean it up later.

So now we have our two operating companies modeled. Let's move over to the a tab and figure out what needs to be done here to consolidate them in the a worksheet. We don't need to reference our planning assumptions anymore. In fact, think of this as a crew trial balance. Post your consolidation entries directly to each financial account. So we can zero out all the accounts for the time being.

Now I've shaded all of the accounts gray to indicate that these are now assumption cells, where we can post consolidation journal entries. Note that I've left the subtotals and the ties between the various statements. I just zeroed out each of the ledger accounts revenues, expenses, assets, liabilities and capital stock. Note that the consolidation journal entry needs to have offsetting debits and credits. So being in an account helps in this regard. Now we can post our consolidation adjustments, which is generally beyond the scope of this course.

But let's just say for the sake of argument that there are intercompany sales of $100 to prevent double counting of intercompany activity, these get eliminated from sales and cost of sales, eliminated in the sense that $100 sold by one car To the other gets reversed from both the sales and the cost of sales, which pretends that the transaction never happened because we're looking at the two entities as one. Let's further assume, down on the balance sheet that there's an intercompany account of $50. Between model company and competitor Incorporated, that needs to be eliminated. intercompany accounts are like funny money because when you add the two companies together, they offset. Finally, for those of you familiar with consolidation accounting, you would recognize that we need to eliminate the long term investment against the equity of the subsidiary at the date of acquisition. For simplicity, I'm assuming the cost method.

Once again, we post a journal entry in the a model to make this happen without messing up our standalone legal entity models of model company or competitor Incorporated. If you're not an account, the last couple of minutes may have been a little bit painful, which goes back to my earlier comment. That'd be a great analyst and helps to know a little bit about accounting. Reality might be slightly more complex than this, but this really covers the major adjustments you'll need to consider. Let's take a look at our consolidation model to see how things now look. Back on the consolidated worksheet, I can see that we have no formulas in our newly added row 52.

Let's add those now. Notice how the cell references in each of the three sub models are in exactly the same location. With just different worksheet identifiers. scroll up and down your model looking at the income statement in the balance sheet. Notice that everything balances accounts appear to be exactly double except for the bank indebtedness, which is how the model in essence paid for the acquisition of competitor Incorporated. Believe it or not, you're done.

One consolidated model of two companies. You can see quickly how financial models can be Come rather large rather quickly. Companies rarely tie themselves to one line of business. What you will find in practice is that the enterprise will be made up of any number of subsidiaries, divisions or business units. This is a modeling challenge for the corporate analyst. But if you're smart about it, you can dynamically contemplate various scenarios of the future all inside one model has demonstrated in this lesson.

One final point before we finish this lesson, now that we have several layers of calculations, and multiple sets of financials, it will become likely that we will need to develop custom reports. Such reports will extract various snips of information from within our financial model to share with our users, rather than modifying or formatting or models to suit these purposes. Let's set up a separate worksheet. So lesson Not taint the financial model, or its elegant architecture. presented here I've summarized components of consolidated sales as an example, the financial model itself should never reference any of the cells on this reporting worksheet. At the same time, the reporting worksheet should never contain any raw data that doesn't originate from within the model.

This preserves the dynamic integrity of the model from top to bottom. In this final lesson, we discussed three important ideas. First, replicating your model allows you to bring together multiple businesses and enhance model integrity. Secondly, using a standard architecture and layout allows you to update multiple worksheets simultaneously, which is not only efficient, but ensures that each year models stay in sync with one another. And then finally setting up a separate reporting worksheet. To allow you to develop custom reports for your audience.

There is so much more I could tell you about financial modeling. But as for the basics, this covers the most important parts, and I suspect sensitizes you to some of the more subtle complexities. As a bonus, I'm going to include a supplemental lesson on the five Excel features you've probably never even considered. They will prove invaluable to those professionals who aspire to do this type of work day in day out. financial models are powerful tools with a model we have just prepared together. We are now well positioned to seek financing to establish a valuation to evaluate impairment to prepare capital budgets to undertake strategic planning, or any other range of purposes.

Congratulations on making it all the way. Until next time, I'm Blair cook