DCF valuation
- Calculation of FCF
- WACC
- Terminal value
- Present values
- Sensitivity analysis
1. Calculation FCF
Continuing operating profit | (EBIT) |
- Tax | Use the long-term effective tax rate |
= NOPAT | (EBIAT) |
+ Depreciation and amortization | ( |
- Capital expenditure | Amount ‘reinvested’ in the business to maintain existing assets and support future growth |
- Increase in OWC | ㅤ |
+/- Changes in other operating assets/liabilities | ) |
= Free cash flow | Cash available to pay out to debt and equity holders |
2. WACC
Weighted average cost of capital
cost of equity * %market value of equity + cost of debt * %market value of debt * (1-marginal tax rate)
- cost of debt
- traded debt
- risk free rate + a credit spread based on company’s credit rating
- rate paid by the companys from recent debt issuances with similar characteristics (proxy company in same sector)
- cost of equity
CAPM: capital asset pricing model
risk free rate + beta * market risk premium
beta: volatility of the company compare to the market/ sensitivity to market risk
- Market value of debt
use market value of traded debt, if not, use book value
3. Terminal value
Cash flow from year 6 to perpetuity. (steady phase)
- Exit miltiple
use trading companies
Cyclical: average of multiples over cycle + average profit
- growing perpetuity
4. Present value
Enterprise value (Core operations) |
Add: Non-current assets/cash |
Deduct: debt |
Equity value |
No. of shares |
Implied Share price |
Compare-Actual Share price |
5. Sensitivity analysis
- 左上角等于当下model预测的股价，完成两侧的不同的sensitivity度量。
- file- option-formula-workbook calculation —> automatic.
- select 分析区域- data-模拟分析-模拟运算表。（Alt+A+W+T)