Corporate Model Collection with Video Explanations

This page includes excel files and videos that allow you to download and work through completed corporate finance models. By working through one or more of the corporate finance models available on this page you can understand some of the complex corporate finance modelling issues and hopefully steal some ideas. Some people find that a good way to really learn a model is to work through every line item of a completed model. Other people can get ideas by just looking at the output pages, the calculation pages and the methods for setting-up inputs. This page includes allows you to download some corporate models as well as video links to a few of the corporate finance models. Hopefully the models and links will remove fears you may have of looking into the models. The page references on the table refer to my book “Corporate and Project Finance Modeling, Theory and Practice”.

All of the models have some things in common. They all involve reading financial statements, making the models flexible so that historical data can be updated, comparing the forecast assumptions with historic data, presenting historic and projected return on invested capital along with alternative valuation models with careful definition of stable terminal cash flow. The various models on this page show that everything is really about forecasting future returns and to a less extent growth. Different reasons for changing returns include surplus capacity (Karachi Port Authority and Kitty Hawk Airlines), returns not being able to be sustained at very high levels (First Solar, Evraz), changes in the cost structure of an industry, cyclical and mean reverting prices and returns (Aziza chicken producer in Palestine and Mongolia Mining).

Innovations in Corporate Finance Theory

Some of the innovations in Corporate Finance Theory on this website include:

  • Solution to IRR problem of re-investment and ranking with Weighted Average IRR
  • Computing Cost of Capital with Market to Book Regression Rather than CAPM
  • Demonstration of Flaws in Value Driver Formula: V=Income x (1-g/ROI)/(COC-g)
  • Correct Evaluation of WACC using Tax Shield from Interest Expense
  • Use of Credit Spread to Derive Debt Beta and More Properly Derive Unlevered Beta
  • Evaluation of Terminal Value Adjustments for Capital Expenditures, Working Capital and Deferred Tax that Depend on Growth
  • Adjustments to Free Cash Flow and EV to Enterprise Value Bridge for Deferred Tax, Warranty Cost and Other Items
  • Development of Terminal Valuation Techniques for Financial Institutions that Use Market to Book Ratio and ROE from Financial Models
  • Evaluation of Political Risk Premiums from Computing Implied Probability of Default

Innovations in Corporate Modelling

You will also find a lot of innovations in corporate modelling.  These include:

  • Use of Historic Switch to Make Incorporation of New Financial Statements
  • Evaluation of ROIC and Invested Capital Using Switches and SUMPRODUCT
  • Development of INTERPOLATE Function to Evaluate Assumptions
  • Automation of Scenario Analysis with Scenario Reporter
  • Effective Automation of Historic Data Graphs with Flexible Spinner Box
  • Resolution of Circular References Related to Interest Expense and Taxes
  • Deprecation Techniques that Account for Changing Growth and Implied Retirements
  • Development of Techniques to Automate Constant Capital Structure in Financial Models
  • Dynamic Goal Seek Functions for Evaluation of Cost of Capital Using P/E Ratios
  • User Defined Functions for Computing Stable Capital Expenditures to Depreciation and Other Items

Innovations in Corporate Data Analysis

  • Creation of techniques to download stock price data, financial statement data and economic data
  • Stock price database that allows you to evaluate IRR’s, volatility and beta for stocks, stock price indices, economic series and commodity prices.
  • Financial Database that allows you to extract and evaluate financial data, financial ratios, and cost of capital across companies.
  • Extraction of Data that Enables you to have Historic Basis for Creating Financial Models.
  • Interest Rate, Exchange Rate and Commodity Price Databases that Include Historic Evaluation of Term Structures, Volatility and Other Statistics.
  • Comprehensive Country by Country Database to Evaluate Growth and Risks Across the World.

 

Here is a model that starts with ROIC and applied to Amazon.

 

Financial Model with Changing ROIC, Growth to Derive Free Cash Flow and Terminal Period Using Amazon Example

 

 

 

 

 Model of a Chicken Production Company Named AZIZA

This is a relatively simple model of a chicken production company in Ramalah, Palistine. It is a company with one line of business company, but very interesting. In the past there have been swings in the income and returns. Apparently the swings in earnings and ROIC come from times during which Israel had surplus capacity and flooded the chicken market. For a company such as this with observed historic cycles, I hope you agree that if you take the top or the cycle or the bottom of the cycle for stable valuation, your model will be biased. For this reason, the stable cash flow in the terminal period includes an assumption for a stable ROIC — you cannot assume cash flows at the bottom of the cycle or at the top of the cycle will continue indefinitely. This is also a very competitive business where it is pretty easy to enter and exit. This means if you assume high returns over the long-term other companies will enter the business. Similarly, if you assume the ROIC is below the cost of capital this is not realistic because companies will leave the business.

Another aspect of corporate modelling that may help you and is in this file is a football field diagram (American football where much of the time you go back an forth without going too far). The good thing about these diagrams is they hilight that valuation is not a precise business and that you can use alternative methods of valuation that may be reasonable. The football field diagram is easy if you do not show the labels on the graph. But it is not very good without the labels. So, in the model you can find a football field diagram that is explained on a step-by-step basis.

The video below describes various aspects of the AZIZA chicken production company including the football field diagram.

Flower Foods: Corporate Model of a Food Company with Stable Returns and Cash Flow

Flower Foods is a food company in the U.S. I remember we made this model in the New York during Super Storm Sandy. Everybody else was staying home and we continued to have a financial modelling course. I use this model to demonstrate how you cannot look at the valuation without looking at the return on invested capital at the same time. Return on invested capital is more important than return on equity because of the typical assumption that surplus cash flow goes to build up cash and deficit cash flow goes to building up debt. This means the capital structure and thus also the return on equity is not a good measure to verify if you have a reasonable model. When you try the upside case in this model, the ROIC goes very high means your forecast is not worth much. The same is true for the downside case where the return falls. This model also compares valuation computed using free cash flow and equity cash flow.

Flower Foods Coprorate Model.xlsm

First Solar: Demonstration that High ROIC’s Cannot be Sustained in Competitive Markets

I have made models of First Solar Corporation on a couple of occasions. In my opinion the history of this company is explained by false beliefs that a company in a competitive industry can continue to earn very high returns that were originally driven by government subsidies to the industry. Before 2008, when subsidies were high (as well as the price of polysilicon), first solar earned high returns on invested capital (more than 40%). After 2008, the stock price and the returns fell, but they were still very high. Then, in 2011 the Chinese entered the market. The industry cried that there was over-capacity and that the competition was unfair (maybe). But prices remained low and returns stabilized at somewhere around the cost of capital. You can see all of this by looking at the return on invested capital in the models below. The model demonstrates how high returns cannot be sustained if you do not have a real competitive advantage which is very difficult in the solar manufacturing business. The corporate models for first solar use financial data and make alternative computations of the ROIC to reflect distortions created by writing off goodwill and taking losses due to re-structuring. The first model includes a lot of detail on how much prices and margins in the industry have come crashing down.

The second model demonstrates how to build scenario analysis and convert a corporate model into an acquisition model.

Kalitta and Kitty Hawk: Forecasting Prices and Margins with Incomplete Information for Independent Air Freight Companies

Kitty Hawk and Kalitta were two independent freight airline companies that merged and went bankrupt. I worked a little bit on the case more than 25 years ago and I did not recognize how difficult the whole forecasting business was. It seems not too difficult to build a model from the number of planes and then assume different pricing and costs for the planes. The problem comes about because the price of used planes in the market can crash if there is a surplus of old planes. Further costs are very heavily affected by maintenance capital expenditures for existing planes. I began with analyst reports that made very simplistic forecasts derived from management forecasts. They turned out to be very wrong. Forecasts should have been made from models of the pricing of new planes. These forecasts should have recognized that pricing can fall very fast when there is a surplus of old plane capacity on the market. The forecasts should have also recognized the importance of maintenance capital expenditures.

In addition to the very difficult economic issues, there I have used the models to illustrate other corporate modelling issues. In particular the corporate models demonstrate how to be careful with depreciation and retirements in a capital heavy industry. The models also include different ways to make scenario analyses in corporate models when the focus is on projected earns per share and other items from financial statements. The video below is a long video that shows how to compute a target capital structure in a model that adjusts share buy-backs or share issues to meet a target capital structure. I am sorry about the length of the long length of the video that walks through how to construct a user-defined function to resolve the issue.

HT Media – Illustration of What Not to Do in Financial Models

The videos below demonstrate some of the things not to do in making a financial model. I think that you can learn a lot about corporate models and valuation by seeing what not to in a model from both a mechanical and a conceptual perspective. The videos associated with the model demonstrate what not to do ranging from not keeping consistent formulas across columns to not presenting assumptions next to history to not using a historic switch. A more important problem than these mechanical issues is the return on invested capital that is produced in the model that increases to unrealistic levels. The model demonstrates that highly detailed assumptions that lead to unrealistic results should be checked with rate of return statistics. I have used this model to also demonstrate conceptual and mechanical issues associated with applying the model to compute the DCF. One issue is assuming a terminal growth rate below the rate of inflation and applying a high cost of capital. Other problems include not using a half-year assumption in discounting cash flow, not computing stable normalized using stable ratios of capital expenditures to depreciation, working capital and EBITDA.

Karachi Port Trust: Evaluating the Danger of Over-Supply in Oligopoly Industry Structure

This model includes a corporate model and a project finance model that I worked on a few years ago. I was very inefficient in making graphs and somewhat crude in making the scenario analysis. The key in this model is analysis of the Karachi Port relative to its competitor named Port Quasim. There is limited movements of goods into and out of Pakistan and both ports are intending to increase the capacity in a dramatic fashion. This new capacity will not go away and must wait a potentially long time until demand catches up with the increases in capacity that come from construction of a large deep water port. This model demonstrates the necessity to evaluate the entire industry and not simply focus on one company. This is particularly true when companies are earning a high return (like hotels earning a high return) and then other companies moving into the industry.

 

Other Corporate Models: Evraz (Russia); Sateri (Singapore); Meracorp (Solvenia) and LaFarge

I have included some other corporate models that we have developed in classes. All of the models begin with historic data and use the key concept of the historic switch. The Evraz is a model of a steel producer in Russia. This model demonstrates a lot of scenario analysis and shows how to build a lot of assumptions and analysis from a few lines of production and volume sales that are in financial reports. The Evraz model also documents issues associated with modelling maintenance capital expenditures and capital expenditures for new development projects that add new capacity. Sateri is a corporate model of a chemical company and includes and analysis of capacity from existing facilities and capacity from new facilities. The capacity allows modelling of alternative capacity utilization scenarios. The LaFarge model is a comprehensive model that includes sensitivity analysis from reports to investors and other sources. Finally, the Meracorp model is an analysis of a retailer that was having financial difficulties.

Corporate Models of Banks and Financial Institutions: PT Bank Central Asia in Indonesia

Corporate Models have a few key differences with other models above. The above models separate financing from operations and focus a lot on EBITDA. Bank models instead work through deposits, loan to deposit ratios, costs of deposits, returns from loans and importantly the amount of loan write-offs. The bank models also have a key difference in that financing must maintain an input capital structure. This can be done with the SOLVER or it can be done with a user defined function which I of course think is a better solution. A third difference between standard corporate models and models of financial institutions is the use of equity cash flow and terminal value from the market to book ratio. I think a good method is to project the book value of the financial institution and then use a price to book ratio that depends on the return on equity earned.

Contrast of Project Finance Model with Valuation in Corporate Finance

 

Video Overviews of Corporate Finance Models

Link to My Youtube Channel Where You Can Look At All of the Different Videos that I have Made

 

General Category Subject File Video Chapter Reference Page Reference
Featured Corporate Models Overview of Featured Coporate Models Overview Chapter 4 21
Featured Corporate Models Presentation, Structuring, Valuation Saudi Cement https://www.youtube.com/watch?v=TWogZ1bB_fs Chapter 4 25
Featured Corporate Models ROIC and History/Forecast Flower Foods https://www.youtube.com/watch?v=fvrqoTfaWHw Chapter 4 316
Featured Corporate Models Development of Assumptions First Solar https://www.youtube.com/watch?v=XTU9EQ7uE_E Chapter 4 55
Featured Corporate Models Assumptions from Industry Analysis KPT https://www.youtube.com/watch?v=ku8Jcpk6cA8 Chapter 4 58
Featured Corporate Models Data from PDF/Depreciation Evraz Chapter 4 28
Featured Corporate Models Credit Analysis Mongolia Mining Chapter 4 28
Featured Corporate Models Use of Analyst Presentations LaFarge Chapter 4 28
Featured Corporate Models Financial Analysis and Ratios Meracorp Chapter 23 307
Featured Corporate Models Balance Sheet Calculations Mongolia Mining https://www.youtube.com/watch?v=F1dQmmyzf7Y Chapter 12 144
Featured Corporate Models Presentation of Operating Assumptions with INDEX Mongolia Mining https://www.youtube.com/watch?v=MHBdhFuWmaQ Chapter 15 167
Featured Corporate Models Adding Financing Calculations Meralco https://www.youtube.com/watch?v=D1Fzwyorqks
Featured Corporate Models Kalitta Standalone Model Kalitta Corprorate Model
Theoretical Corporate Models Introduce to Corporate/Project Reconciliation Corporate and Project Model https://www.youtube.com/watch?v=bmMo8Q2xFHk Chapter 15 167
Theoretical Corporate Models Portfolio of Projects Corporate and Project Model
Theoretical Corporate Models Dynamic Goal Seek to Compute Price from IRR Corporate and Project Model https://www.youtube.com/watch?v=8c_FopDzo6U
Scenario Analysis Scenario Analysis with Flexible Reference Case Kalitta Corporate Model
Operating Analysis Corporate Model Operating Analysis Kalitta Corporate Model
Featured Corporate Models Depreciation on Existing and New Assets Kitty Hawk Corporate Model
Featured Corporate Models Marginal Cost and Analysis of New Assets using Function Kitty Hawk Corporate Model
Featured Corporate Models Target Capital Structure with Function Kitty Hawk Corporate Model Chapter 15 167
Existing Debt Existing Debt in Corporate Model Kalitta Corporate Model Chapter 15 167
Debt and Depreciation Debt and Depreciation in Corporate Model Kalitta Corporate Model Chapter 15 167
Lookup and Interpolate Interpolate Growth and Other Items Corporate and Project Model https://www.youtube.com/watch?v=sonGGtUBtpQ Chapter 15 167