This article allows you to learn some corporate modelling ideas from review of an actual model that is pretty horrible. I have made various files that are allow you to create corporate models from A-Z beginning with a blank sheet. Another way to learn how to build models is to see what not to do. The model below may look pretty sophisticated when you open it. But the model does not follow a natural flow, does not present history compared to forecast in an effective manner and it contains important logical and mechanical mistakes.
I think reviewing this model and seeing the mistakes is a good way to learn modelling, perhaps better than beginning with a blank workbook. The videos and associated files below walk through an explanation of what not to do in corporate models and how to repair mechanical and logical errors. This may be somewhat less boring than working through each of the detailed exercises. Looking at a badly structured model and fixing the model is demonstrated in the videos and the files below.
When you open the model it looks sophisticated with a whole bunch of sheets. But all of the detail and the work in collecting the data is really a lot of B.S. When you work through all of the sheets, you see things that are very difficult to follow and no natural and logical structuring of the sheets. With all of the sheets, presenting a nice summary with graphs of the history and forecast is impossible. The thing you get when you open the model is illustrated below.
When you finally find the key sheet with the valuation, it again seems really sophisticated. But there is a whole bunch of crap in the fancy DCF calculations. For example, the discounting does not reflect a 1/2 year assumption; there is no normalisation of terminal cash flow; there is only one terminal value method; there is no flexibility to make the model use different explicit periods; and, the enterprise to equity value does not work through the balance sheet. The DCF page is illustrated below.
One of the mechanical problems with the model is the computation of depreciation expense. The model looks really fancy in the depreciation section with the gross block, the additions and the depreciation for each year as shown in the screenshot below. It looks like the depreciation section of an annual report. But there is a dramatic mistake because the model does not account for any retirements. This means the depreciation is overstated by a wide margin.
The final screenshot of the model is illustrated below. If you look hard enough you can find ratios in the balance sheet page (I have no idea why it would be in this page). If you look hard you can find the return on capital employed which is essentially another name for the return on invested capital. When you find this you can see that the model assumes a dramatic increase in the return. Note that the return in the first couple of years is around 11.5%. But then the return increases to 30%. What a lot of B.S. This single statistic should completely invalidate the model.
Before reviewing a couple of fixes to the model, I evaluate the actual stock price. The model suggest a current stock value of more than 130. This means the future stock price was supposed to increase a lot from 130. The screenshot below demonstrates that the actual stock price never increased and has stabalised at about 80.
The file with the fixed model includes a summary page that graphs the history and the forecast for various items. The best way to do this would be to put all of the key assumptions in a single page and then make a drop-down graph where you can graph any of the items. The screenshot below illustrates how a summary page with a graph could work. The selected graph shows the history and the forecast for the Return on average common equity.
Some of the fixes to the DCF analysis that are included in the repaired model are shown in the screenshot below. The first screenshot illustrates how the assumptions could be set-up that include assumptions for stable ratios. In the case below there is no fix to the mechanical problems with the depreciation expense. Note also that the model includes switches for the terminal period. Note that the price is 158 from the valuation with all of the assumptions in the model.
The second and third screenshots show the cash flow calculation that are used to derive the value in a corrected framework. The first page demonstrates the adjustments that should be included in the free cash flow calculation including deferred taxes and changes in other liabilities. The free cash is adjusted with TRUE and false calculations for the flexible discounting process. The third screenshot below illustrates the effect of changing the depreciation calculation on the valuation.
Videos that Illustrate Problems with the Model and the Process of Repairing the Model