Corporate Modeling and Valuation Analysis Files

This page includes a summary of the files that I use for a valuation and corporate modelling analysis class.  I have included corporate case study files for Amazon and Macy’s ; the comprehensive stock price file; the file that reads in financial data and stock prices for different companies; the files that help you out with excel efficiency (generic macros) and reading in data from PDF files; files that demonstrate depreciation and EV/EBITDA multiple issues; files that demonstrate alternative terminal value and problems with the value driver method; power point files that work through the valuation analysis.  Details of these files are included elsewhere on the website. The files for Macy’s are also the files I used to explain some of the modelling issues when there is a sudden reduction in demand.  The power point file attached to the button below describes some of my opinions on both valuation and financial modelling using the Macy’s and the Amazon case.

Power Point File that Describes Financial Modelling and Valuation Theory Using Case Studies of Amazon and Macy's

 

Case Study Files for Acquiring Historic Data and Creating a Model

The files for the Amazon financial model case study and the Macy’s  case study are included below this paragraph.  The first file is an excel workbook that automatically reads in data for Amazon, Macy’s and Apple.  You can find much more information about how to use and create this file at the link assigned to this sentence. These files include both a file that is complete and a file that has some blanks for key numbers to enter (in yellow).  I have also included a file that reads the Amazon and the Macy’s data from the file that converts financial statements from MarketWatch.  You can see how this file works by clicking on the link attached to this sentence.

 

Excel File with Macros that Allow you Read Historical Data Using Amazon, Apple and Macy's Example

 

The files attached to the buttons below demonstrate how you can create and use a financial model for Amazon and Macy’s.  These files include the historic financial data and work through assumptions to derive the company valuation.  For both Macy’s and Amazon, I have included two different files.  One of the files have selected blanks and the second file has the completed model. Similar files for the Macy’s case study — both the completed file and files with exercises are attached to the buttons below the Amazon files.  Unlike Amazon, this case has declining sales and lower earnings. In the files that have blanks, I have tried to concentrate on key modelling and valuation issues so that you do not have to work through every single line.

Excel File with Blanks for Macy's Financial Model and Valuation File that Works Through History to Valuation

 

Excel File with Blanks for the Financing Section Macy's Financial Model and Ending With Balancing the Balance Sheet

 

Excel File with Selected Blanks in Amazon Financial Model with Scenarios and Valuation Using Alternative Terminal Value

 

Terminal Value and Multiple File

In discussing multiples I try to evaluate what is the underlying theory for multiples and terminal value.  You can evaluate this both on the basis of equity cash flow and free cash flow. This also includes adjustments for terminal value multiples.  In this file I have created a long-term model with changing returns and growth rates to “prove” the true value.  Next. I compare this value with values that are generated by different models.  These files demonstrate how value is affected by changing growth rates and rates of return.

 

Excel File with Analysis of Alternative Terminal Methods with Fade Period, Value Driver Methods and Stable Adjustment

 

Excel File with Blanks for Analysis of Corporate Valuation Using Alternative Valuation Methods For Exercise

 

Excel File with Selected Blanks in Amazon Financial Model with Scenarios and Valuation Using Alternative Terminal Value

 

 

Stable WC Change =  [EBITDA x WC/EBITDA * Growth]/[1+Growth]

 

The file with a scatter plot of total value versus different returns and growth rates is shown below.  I made this with a little random variation against a series of different values.

 

 

Stock Price File

In describing the valuation, I use a file that creates a database of stock prices, stock indices, economic series, commodity prices and interest rates.

 

Comprehensive Stock Price, Commodity Price, and Economic Series Database with Monthly Prices

 

 

Generic Macros and Read PDF Files

 

 

Some Different Ideas About Finance in the Website

  • Evaluation of Normalised 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
  • Demonstration of Flaws in Value Driver Formula: Value = Income x (1-g/ROI)/(COC-g) with Changes in Growth and Inflation
  • Correct Evaluation of WACC using Tax Shield from Interest Expense by Using Net of Tax Debt in Capital Structure and Gross of Tax Interest Expense
  • Reconciliation of IRR and ROIC with Solution to IRR Problem of Re-investment and Ranking with Weighted Average IRR
  • Macroeconomics and the Ability to Earn High Premiums over the Long-term and Philosophy of Modelling as Assessment of Potential for Maintaining Real Economic Rents
  • Computing Cost of Capital with Market to Book Regression Rather than CAPM or using P/E Ratios
  • Evaluating Depreciation Expense and Net Depreciation Rates in the Context of Changing Growth
  • Different Drivers of P/E Ratio and EV/EBITDA Ratio and Dependence of EV/EBITDA Ratio on Asset Life
  • Use of Credit Spread to Derive Debt Beta and More Properly Derive Unlevered Beta
  • 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
  • Simulating Credit Ratings in M&A using Financial Ratios and Business Risk

 

Derived Capital Expenditures

 

Gross Plant(t) = Gross Plant(t-1) + Capital Expenditure – Retirement

Accumulated Depreciation(t) = Accumulated Depreciation(t-1) + Depreciation Expense – Retirement

Net Plant(t) = Net Plant(t-1) + Capital Expenditure – Depreciation

Capital Expenditure = Net Plant(t) – Net Plant(t-1) + Depreciation