Valuation Model (2011)

Archived Information for Spring 2011

DCF Valuation Model
  • I've created a DCF (Discounted Cash Flow) Valuation Model template (in Microsoft Excel format), using Proctor and Gamble (NYSE: PG) as an example.
    • Download the latest version here (Version 4/1/2011).
  • You should use this template to value stocks for the the Research Report part of Stock Pitch Competition,  
    • DCF valuation is not necessary and discouraged in the presentation part of the Competition. 
    • The output can be printed out in two pages in landscape orientation.
    • Please check back later for updated versions. I am still trying to improve it to make it more user-friendly.
  • Throughout this semester, I am going to use many variants of this model, in the context of real business cases, to illustrate the techniques of stock valuation.
What it is and what it is not:
  • This model is simple but is able to captures the most important value drivers for a business.
  • This is certainly not one of the most sophisticated models.
    • However, it works quite well in real world investing. 
  • I make the template available to you so as to create a level playing field among students. 
    • I removed the burden from you of creating the spreadsheet by yourselves, because I'd like to evaluate your business intuitions, not your Excel skills.
    • Spend more time on understanding the economics of a business, and less time on number-crunching
How to use it:
  • Don't be intimidated by the spreadsheet. Most of the calculations are automated.
  • However, you will need to populate the spreadsheet with historical data from Google Finance.
    • The blue cells require your inputs.
    • Some "comments" are embedded in the template to guide you to finding the right data items in Google Finance. 
  • All data inputs needed can be found in Google Finance (which standardizes a company's financial numbers and make them more accessible for nonprofessional users).
    • However, more advanced users can (and should) read the original financial reports of a company to double check for data accuracy.
    • A company's 10-K and 10-Q reports can be obtained from EDGAR for free.
  • The yellow cells are where you will enter your inputs, i.e., your forecast of the future.
    • You can use the historical trend as a guide for the near future.
    • Use your common sense to extrapolate the long-term trend
      •  e.g. few companies can enjoy extraordinarily high profitability and sustain extraordinarily fast growth for a long period of time. 
      • Instead, reversion to the mean is the norm. A high return on investment will inevitably attract competition, and high growth will eventually lead a company to hit a wall once it saturates a market.
  • Entering numbers into an Excel spreadsheet is easy. Know what numbers to enter is far more difficult.

To the beginners: 
  • Focus your energy on understanding the economics and not just the financial/accounting side of a business. 
    • Understand a business's products and markets
    • Understand how it makes money
    • Understand its competitive advantage and "economic moats"
      • It helps you see the future
  • You don't need to fully understand how this model works, but you do need to know something about accounting.
    • Review what you learnt in accounting courses.  
  • Put in some efforts
    • Even if you don't plan to work in the financial industry, you will still need to manage your own retirement money in the future.
    • The U.S. social security system will most likely have bankrupted by the time your generation retires.
  • If you are asking the following questions, you are probably a more advanced user
  • Why do you limit the explicit forecast period to only 7 years?
    • Few firms can sustain exceptional profitability for more than 7 years, let alone a decade. 
      • Even the most glamorous business right now, such as Apple, will inevitably fall back to earth and start making only normal profits (not much higher than its WACC) and most likely experience only normal growth rate. 
      • The history of Apple itself in the 1990s was a good example.
    • When ROIC equals WACC, growth doesn't add any value to a business and can be ignored from valuation.
  • I heard that APV (Adjusted Present Value) is a better variant of DCF models. Why don't you teach APV instead?
    • Yes, APV is better and I personally use APV.  
    • However, APV is just a variant of DCF models. 
      • When done properly, both DCF and APV should produce exactly the same results. 
      • When done improperly, which is frequently the case for beginners, you are more likely to make mistakes when using APV. 
      • That's why most business schools teach their MBA students DCF and not APV.
    • I am going to teach you APV in the context of one of the M&A cases. 
      • APV is particularly useful in leveraged buyout situations, because capital structure changes from year to year. 
  • Why do you use 5% as the risk-free rate and 6.5% as the equity premium.
    • There is no consensus on what numbers to use. You are using these two numbers because I said so.
    • And that's why I include a Sensitivity Analysis table in the spreadsheet to show you how the value of a stock may change depending on what WACC we choose.
Rocco Huang,
May 19, 2011, 1:18 PM