Corporate+Finance+Models+Collection

= = =|| Home|| Book || Featured PF Models || Featured Corp Models || Featured Acq Models || PF Exercises || Corp Exercises || Download to Excel || Scenario Analysis || Tornado Diagrams || Monte Carlo || Flexible Graphs || Real Estate || Acq Exercises || Macros || Reading PDF || Waterfall Graphs || Circular Ref || Depreciation || Financial Ratio || Value Driver || Renewable || Testimony || Pricing Case Studies ||=

= This website is now obsolete because of being extinguished by horrible wikispaces. Please go to http://edbodmer.com for a much improved website. =

= =

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

= Corporate Finance Files Associated with Videos: =

=Carlsberg Financial Model - Valuation and Normalised Cash Flow in Stable Period=

The Carlsberg model was developed for a class in Denmark where the objective was to build a basic model and work through valuation. This model is presented as a comprehensive analysis that illustrates normalised cash flow in the terminal period of the DCF model, issues with measuring and evaluating ROIC, scenario analysis and effective presentation of valuation. As with all of the corporate models, the general process involves the following steps: (1) acquire historic financial data (this is why I am obsessive about reading data from the internet and pdf); (2) create assumptions from historic data and analyse the historic ROIC; (3) work through forecasts of key items of revenues and operating expenses and try to understand fixed and variable operating cost; (4) evaluate changes in working capital and try to understand cap exp; (5) work through depreciation and plant balances using the historic levels as a starting point.

This model does not go all the way to net income and compute a balance sheet as it was used for demonstrating how to construct a LBO and how to work through valuation issues. Instead, the valuation section in this model does include tricky adjustments to arrive at normalised cash flow. You can think of the adjustments to arrive at stabalised cash flow as working through the components of cash flow -- the EBITDA, the Capital Expenditures required to earn EBITDA, the Working Capital Adjustment to EBITDA and Deferred Tax. Adjustments to arrive at stabalised EBITDA are made through analysis of ROIC and use of the INTERPOLATE function. Adjustments to capital expenditures are made through depreciation analysis with growth analysis. Adjustments to working capital change are made using the formula WC/EBITDA x EBITDA in Terminal Year x [g /(1+g)] and stable capital expenditures that account for historic and future growth are modeled using a user defined function name STABLE_CAPEXP.

The model can also be used together with the cost of capital analysis for beer companies where a number of different companies are downloaded and alternative ways of computing the cost of capital are analysed. The model is described in the video below (the sound level is low and you may have to use headphones or something, I am fixing this in other videos).

media type="youtube" key="w8jK4lh-v4Y" width="560" height="315" = = ==

=Saudi Cement Company - Basic Model with Updated Historic Analysis=

If you are looking for a simple corporate model that goes from A-Z and includes history, the Saudi Cement Model may be a good example. A big deal in corporate modelling is acquiring financial data and making a model flexible enough to incorporate updated historic data. This model gathers financial data from the Saudi Stock Market page called TAWDAUL. The idea is that you should be able to get your data, easily connect it to the prior history, change the last historic data, change assumptions from the history and then quickly have a new model with new updated data. I have included another simple example of a construction company in this section that illustrates very similar ideas. The models work all the way through the balance sheet and first develop operating cash flows. The separation between operating cash flow and financing cash flow is important and it is important to set-up accounts that keep track of debt and cash balances. The cash and debt accounts find the changes in cash flows from the cash flows.

This Saudi Cement model and the construction company model also demonstrates how you can make presentations of historic and projected financial statements and incorporate scenario analysis into a corporate model. The model contains graphs that show all of the items on a three statement model for both historic and projected levels. The scenario analysis is set-up with sensitivity analysis so that you can make a tornado diagram and see which variable has the biggest effects on the valuation of the company.

A video describing the Saudi Cement model is included in the table at the bottom of this page.



= = =Model of a Chicken Production Company Named AZIZA=

This is a relatively simple model of a chicken production company in Ramala, 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 indefinately. 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 lables. 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.

media type="youtube" key="7u_En5nkTq0" width="560" height="315"



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



=New Store Analysis: How to Evaluate Data from Corporate Databases in Creating Models of New Stores=

The model below demonstrates how to extract data from a corporate database that records data for different facilities and then use the information to evaluate statistics reative to the proposed new store. This is similar to evaluating history relative to the forecast in a standard corporate model. Some of the tricky issues in the model are selecting different samples of existing stores for comparison. The model also includes a method where data can be graphed and then a macro can be used to drill down to the assumption. In this model the type of model structuring that is flexible, structured and transparent is an important part of the process. The inputs page is structured in the same order as the financal model and all of the inputs come from this sheet. The financial model describes each item in the financal statement in a transparent and simple manner whereby you can see how each of the revenue, cost, capital expenditure and balance sheet items are derived. These items are presented before the financial statements are presented. Finally a summary is presented where key outputs are shown next to the inputs.

media type="youtube" key="0F0drJhBSmI" width="560" height="315"media type="youtube" key="8DPdaVM-wsY" width="560" height="315" align="right"

= = =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 stabalised 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 distrortions 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.

media type="youtube" key="LGwoqmX40y8" width="560" height="315"



=Mongolia Mining: Assuming Prices During High Cycle will Continue and Problems with Cost Structure=

Mongolia Mining owns a large Coal Mine in Mongolia. It's coal is trucked to China. When it began operations a few years ago, coal prices were spiking and it could profitably sell to China with the expensive trucking cost. Coal prices have fallen and so has the demand for coal from the northern part of China. This has pushed the value of Mongolia Mining to very low levels (the company is traded on the Hong Kong exchange). This model demonstrates how to incorporate volumes and capacity into a model and evaluate the key driver which is the price of coal and the ability to transport volumes on a profitable basis. The Mongolia mining case therefore demonstrates how to use a financial model to perform credit analysis and evaluate the re-financing capacity.



=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 unrelaistic 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 normalised 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.



=Timing in Corporate Models and Subtotals that Accumulate Monthly or Quarterly Data=

I did not really know where to put the model that illustrates how to take monthly data and then make it into quarterly, semi-annual or annual data. The thing not to do is to manually put in sub-totals with the sum function. If you do this your model will be clumsy, in-flexible and inaccurate. Instead, you can include separate sheets that summarise the same information as in your detailed analysis, but include flexible sub-totals. You need to establish a counter with the MOD function after starting with a basic period counter. Then use the SUMIF function to add across months or quarters. Finally, use the MATCH (with zero at the end) and the INDEX to put the sheet with sub-totals together. This model demonstrates the importance of setting-up time lines in corporate finance models. The time lines are essential for history versus forecast as well as the flexible sums.

media type="youtube" key="eBR5JeBDC3c" width="560" height="315"



=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 utilisation 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 =


 * || General Category ||  || Subject ||   || File ||   || Video ||   || Chapter Reference ||   || Page Reference ||
 * || Featured Corporate Models ||  || Overview of Featured Coporate Models ||   || Overview ||   || https://www.youtube.com/watch?v=-THY2VW8nSw ||   || 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 ||   || https://www.youtube.com/watch?v=nqDuOMygF2U ||   || 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 ||   || https://www.youtube.com/watch?v=-BW3-UKpYIs ||   ||   ||   ||   ||
 * || 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 ||   || https://www.youtube.com/watch?v=UxxLMYAnWMA ||   ||   ||   ||   ||
 * || Operating Analysis ||  || Corporate Model Operating Analysis ||   || Kalitta Corporate Model ||   || https://www.youtube.com/watch?v=pMlgHgQvTqY ||   ||   ||   ||   ||
 * || 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 ||   || https://www.youtube.com/watch?v=LGwoqmX40y8 ||   || Chapter 15 ||   || 167 ||
 * || Existing Debt ||  || Existing Debt in Corporate Model ||   || Kalitta Corporate Model ||   || https://www.youtube.com/watch?v=rAlyWQxsKY8 ||   || Chapter 15 ||   || 167 ||
 * || Debt and Depreciation ||  || Debt and Depreciation in Corporate Model ||   || Kalitta Corporate Model ||   || https://www.youtube.com/watch?v=tyLTTmZWljY ||   || 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 ||
 * || Lookup and Interpolate ||  || Interpolate Growth and Other Items ||   |||| Corporate and Project Model || https://www.youtube.com/watch?v=sonGGtUBtpQ ||   || Chapter 15 ||   || 167 ||

=**General Comments About Corporate Models:**=

The tricky part about corporate models involves how to incorporate moving from historic to projected periods in a smooth way and how to model terminal value. The model below illustrates how to: 1. use macros to read from PDF files into excel 2. put history and forecasts together in an effective way that allows you to add history in the future 3. compute stable ratios of working capital, depreciation, capital expenditures and deferred tax that depend on the terminal growth 4. create flexible valuation periods

=**Related Pages on the Site:**=


 * Other Featured Models **
 * Other Project Model Examples **
 * Corporate Model Exercises **
 * Template Models **
 * Scenario and Senstivity Analysis **
 * Generic Macros **
 * Auditing Files **