Lab for Lecture 19: Valuation Using Dividend Discount Models

Estimating MSFT intrinsic value using Dividend Discount Models (DDM)

Data

Use the accounting_data_msft_comps.xlsx dataset to obtain data on financial ratios and key accounting variables from the financial statements of MSFT and three comparable firms: AMZN (Amazon), CRM (Salesforce), and GOOGL (Alphabet).

In particular, extract MSFT data (for all available fiscal years) on the following variables:

Financial variable Label in the raw data
Dividends Dividends (Cash) - Common
Net Income NET INCOME (Loss)
Shares Outstanding Shares Outstanding at FYR end
Total Equity (Book Value) TOTAL SHAREHOLDERS’ EQUITY

Analysis

  1. Using the financial data you extracted above, calculate the following variables for each firm in the dataset:
  • Dividends per share
  • Earnings per share (EPS)
  • Payout ratio
  • Retention ratio
  • ROE
  • Fundamental growth rate
  • Dividend growth rate
  • EPS growth rate
  1. Use the instructions in lab 13 to calculate MSFTs cost of equity capital
  2. Estimate MSFT’s intrinsic value (per share) using the following methods
    • A constant growth DDM
    • A two stage DDM, where the second stage is a perpetual constant growth stage
    • A two stage DDM, where you estimate the terminal value of MSFT using a P/E multiple

Questions

  • The constant growth DDM is very sensitive to the gap between the cost of equity (\(k_e\)) and the growth rate (\(g\)). What happens to your estimated intrinsic value as \(g\) gets closer to \(k_e\)? Does this make economic sense, or does it reveal a limitation of the model?
  • Among the comparable firms (AMZN, CRM, GOOGL), some pay little or no dividends. Does this mean these firms are worth less than MSFT? What does the DDM framework implicitly assume about how firms create value for shareholders, and why might that assumption be problematic for non-dividend-paying firms?
  • Your two-stage DDM estimates likely differ depending on whether you use a constant growth terminal value or a P/E multiple for the second stage. Which estimate do you trust more, and why? What are the key assumptions embedded in each approach?
  • Look at the fundamental growth rate (\(g = ROE \times b\)) you calculated for MSFT. If MSFT decided to increase its payout ratio (pay out more dividends), how would that affect the growth rate and, in turn, the intrinsic value from the constant growth DDM? Is it possible for a firm to increase dividends and increase its intrinsic value at the same time?
  • Compare your DDM-based intrinsic value estimates to MSFT’s current market price. If your estimates are significantly different from the market price, does that mean the market is wrong, or that your model inputs are wrong? How would you go about diagnosing the source of the discrepancy?