Lab for Lecture 20: Valuation Using Discounted Cash Flow Models
Estimating MSFT intrinsic value using Discounted Cash Flow (DCF) models
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 data (for the most recent fiscal year) on the following variables:
| Financial variable | Label in the raw data |
|---|---|
| EBIT | OPERATING INCOME AFTER DEPREC |
| Depreciation expense | Depreciation, Depletion & Amortization |
| Interest expense | Interest Expense |
| Pretax income | PRETAX INCOME |
| Tax expense | Income Taxes - Total |
| CAPX | Capital Expenditures |
| Inventory | Inventories - Total |
| Accounts receivable | Receivables - Total (Net) |
| Accounts payable | Accounts Payable |
| Long term debt | Long-Term Debt |
| Long term debt due within 1 year | Debt (Long-Term) Due in 1 Year |
| Price (FYE) | Price - Fiscal Year Close |
| Nr of shares | Shares Outstanding at FYR end |
Analysis
- Using the financial data you extracted above, calculate the following variables for MSFT, in each year in the dataset:
- Tax rate (Tax expense / Pretax income)
- Net working capital (Inventory + Accounts Receivable - Accounts Payable)
- Total long term Debt
- Market capitalization
- Leverage ratio
- Cost of debt (estimated as Interest Expense / Prior Year Long Term Debt)
- Use the instructions in lab 13 to calculate MSFTs weighted average cost of capital (WACC)
- Estimate MSFT’s intrinsic value (per share) using the Free Cash-Flow to the Firm (FCFF) approach and each of the following methods:
- A constant growth model
- A two stage model, where the second stage is a perpetual constant growth stage
- A two stage model, where you estimate the terminal value of MSFT using a P/B multiple
- Repeat step 3, using a Free Cash Flow to Equity (FCFE) approach
Sensitivity analysis
The showcase a sensitivity analysis, we use one of the valuation methods above: the DCF based on FCFE approach, and we rerun the valuation changing two crucial parameters we used for it:
- The perpetual growth rate (g) and
- The cost of capital (r_e)
These parameters: the perpetual growth rate and the discount rate, have the highest impact on any DCF and DDM valuation.
To perform the sensitivity analysis in Excel:
- Create a table where
- the columns are the different cost of capital values we want to use
- the rows are the different growth rates we want to use
- In the top left corner of the table, we link to the valuation cell from the original analysis
- Select the entire table
- Go to Data -> What-if analysis -> Data Table
- for “Row Cell” select the cost of equity cell in the original analysis
- for “Column Cell” select the terminal growth rate cell in the original analysis
Questions
- Your sensitivity analysis shows how the estimated intrinsic value changes with the growth rate (\(g\)) and the discount rate (\(r\)). Which of these two parameters has a larger impact on the valuation, and does the answer depend on the starting values? What does this tell you about where you should spend most of your effort when building a DCF model in practice?
- In two of your models, you estimate a terminal value using a perpetual constant growth assumption, while in the third you use a P/B multiple. The perpetual growth approach requires you to assume the firm grows forever at a fixed rate — what are the economic constraints on how high that rate can be, and what goes wrong in your model if you violate them? Does the multiples-based terminal value suffer from the same limitation?
- The FCFF calculation starts from EBIT and adjusts for taxes, depreciation, capital expenditures, and changes in net working capital. Which of these components is most uncertain or hardest to forecast for a firm like MSFT, and how would an error in that component propagate through to your final intrinsic value estimate?