Lab for Lecture 18: Valuation Using Multiples
Estimating MSFT intrinsic value using P/E, P/B, and EV/EBITDA ratios
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 |
|---|---|
| EBITDA | OPERATING INCOME BEFORE DEPREC |
| Net Income | NET INCOME (Loss) |
| Shares Outstanding | Shares Outstanding at FYR end |
| Stock Price (FY Close) | Price - Fiscal Year Close |
| Total Equity (Book Value) | TOTAL SHAREHOLDERS’ EQUITY |
| Preferred stock | Preferred Stock - Liquidation Value |
| Long-Term Debt | Long-Term Debt |
| Long-Term Debt due within 1 year | Debt (Long-Term) Due in 1 Year |
| Cash & Equivalents | Cash & Equivalents |
Analysis
- Using the financial data you extracted above, calculate the following variables for each firm in the dataset:
- Total market capitalization
- Enterprise value (EV)
- Book value of equity / Share
- Earnings per share (EPS)
- Calculate the P/E, P/B, and EV/EBITDA ratios for all firms in the dataset
- Estimate MSFT’s true (intrinsic) P/E, P/B, and EV/EBITDA ratios based on the average values these ratios take for MSFT’s comparable firms
- Back out MSFT’s intrinsic value per share from these estimated ratios
Questions
- A simple average of peer multiples assumes each comparable is equally informative. Would you be more comfortable using the median, or dropping an outlier, for some ratios? Explain how that choice would change your story about MSFT’s “fair” multiple.
- How appropriate are AMZN, CRM, and GOOGL as comps for MSFT on each multiple? For which ratio do you think the peer set is strongest, and for which is it weakest—and why?
- When your intrinsic value per share differs from the actual stock price, what are at least two distinct interpretations (e.g., market mispricing versus limitations of the comparables approach)? What additional evidence would you want before claiming the stock is under- or overvalued?
- Should MSFT itself be included in the set of firms whose multiples you average to obtain MSFT’s “intrinsic” multiple, or should the benchmark use only the other three firms? Defend your choice and describe how the numbers would change if you did the opposite.