Corporate Model Collection with Video Explanations – Edward Bodmer

Course Info

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”.

Instructions for Downloading Files with Macros

Here are some of the models that are described in more detail on the individual pages.

Corporate Finance Model for Aziza, A Chicken Producer Demonstrating Football Field Diagrams and ROIC Simple Corporate Model for a Bank Demonstrating Financial Institution Structure and Constant Capital Structure Simple Corporate Model for a Bank Including Exercise for Resolving Circularity and Valuation Corporate Finance Model for Vestas Demonstrating Measurement Issues with ROIC from Service Contracts Karachi Port Authority Corporate Finance and Project Finance Model for Evaluation of Proposed New Projects Flower Foods Corporate Model Demonstrating Historic and Projected ROIC with Different Valuation Methods Corporate Model of LaFarge Cement with Analysis of History, Valuation and Modelling Structuring Issues Corporate Model of Air Freight Company Kitty Hawk Standalone Basis Demonstrating Difficulty in Modelling Asset Portfolio Corporate Model of Air Freight Company Kalitta on a Standalone Basis Demonstrating Difficulty in Modelling Asset Portfolio Corporate Model of Freeport McMorran Mining Company with Incorporation of Commodity Price and Impact on ROIC Corporate Model of Evraz Steel Company in Russia Demonstrating Commodity Price Forecasts and Impact on ROIC PT Bank Central Asia Corporate Model Demonstrating Valuation Issues for Financial Institution Corporate Model of Sateri in Singapore Demonstrating Valuation and Model Structure and Impact on ROIC

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.

AZIZA Financial Valuation Model.xlsm

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. I will upload the models in the future, but if you want them in the meantime, please send me an e-mail to edwardbodmer@gmail.com and ask for the resource library.

Corporate Model Example – First Solar.xlsm

First Solar Model – AIF.xlsx

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. I will upload the models in the future, but if you want them in the meantime, please send me an e-mail to edwardbodmer@gmail.com and ask for the resource library.

Kitty Hawk Corporate Finance Model.xlsm

Kalitta Corporate Finance Model.xlsm

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. I will upload the models in the future, but if you want them in the meantime, please send me an e-mail to edwardbodmer@gmail.com and ask for the resource library.

KPT Corporate and PF Model.xlsm

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. I will upload the models in the future, but if you want them in the meantime, please send me an e-mail to edwardbodmer@gmail.com and ask for the resource library.

Evraz Finished.xlsm
Sateri Corporate Model.xlsm
LaFarge Financial Model.xlsm
Meracorp Acquistion.xlsm

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. I will upload the models in the future, but if you want them in the meantime, please send me an e-mail to edwardbodmer@gmail.com and ask for the resource library.

Simple Bank Model with Circular Exercise.xlsm
screener banks.xlsm
Bank Central Asia Model.xlsm

Contrast of Project Finance Model with Valuation in Corporate Finance

Corporate and Project Model.xlsm

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