Learning

Steps for Performing a DCF Model in Excel

Step 1: Gather Financial Information

  • Obtain the company's financial statements, including the income statement, balance sheet, and cash flow statement.
  • Collect relevant financial metrics and forecasts, such as revenue growth rates, operating margins, capital expenditures, and changes in net working capital.

Step 2: Forecast Free Cash Flows (FCF)

  1. Forecast Revenue Growth: Project the company's revenue for the next 5-10 years based on historical growth rates or analyst estimates.
  2. Estimate Operating Margins: Project future operating margins to calculate operating income.
  3. Calculate Taxes: Apply the corporate tax rate to the operating income to find the after-tax operating income.
  4. Adjust for Non-Cash Items: Add back non-cash expenses (like depreciation) to the after-tax operating income.
  5. Subtract Capital Expenditures: Estimate future capital expenditures necessary for maintaining or growing the business.
  6. Adjust for Working Capital: Calculate changes in working capital that affect cash flows.

Free Cash Flow Formula

$ \text{FCF} = (\text{Net Income} + \text{Depreciation/Amortization}) - \text{Changes in Working Capital} - \text{Capital Expenditures} $

Step 3: Calculate the Discount Rate (WACC)

  • Calculate the Weighted Average Cost of Capital (WACC) which serves as the discount rate in the DCF model. The WACC is a weighted average of the cost of equity and the after-tax cost of debt.

WACC Formula

$ \text{WACC} = (\frac{E}{V} \times \text{Cost of Equity}) + (\frac{D}{V} \times \text{Cost of Debt} \times (1 - \text{Tax Rate})) $ Where:

  • $ (E) $ is the market value of the equity,
  • $ (D) $ is the market value of the debt,
  • $ (V) $ is the total market value of the firm (equity + debt),
  • $ (\text{Cost of Equity}) $ can be estimated using the Capital Asset Pricing Model (CAPM),
  • $ (\text{Cost of Debt}) $ is the average interest rate the company pays on its debt.

Step 4: Discount Future Cash Flows to Present Value

  • Use the WACC calculated in Step 3 as the discount rate.
  • Discount the forecasted FCFs over the forecast period back to their present value.

Present Value of a Single Future Cash Flow Formula

$ \text{PV} = \frac{\text{FCF}}{(1 + \text{WACC})^t} $ Where:

  • $ (PV) $ is the present value,
  • $ (FCF) $ is the free cash flow in year (t),
  • $ (WACC) $ is the weighted average cost of capital,
  • $ (t) $ is the time in years.

Step 5: Calculate Terminal Value

  • After the forecast period, assume the company grows at a constant rate forever (perpetual growth rate).
  • Calculate the terminal value using the Gordon Growth Model.

Terminal Value Formula (Gordon Growth Model)

$ \text{TV} = \frac{\text{FCF}_{n+1}}{(\text{WACC} - \text{G})} $ Where:

  • $ (\text{TV}) $ is the terminal value,
  • $ (\text{FCF}_{n+1}) $ is the free cash flow in the first year beyond the forecast period,
  • $ (G) $ is the perpetual growth rate.

Step 6: Calculate Enterprise Value

  • Discount the Terminal Value back to its present value.
  • Sum up the present values of the forecasted FCFs and the present value of the Terminal Value to get the Enterprise Value.

Step 7: Determine Equity Value

  • Subtract the net debt from the Enterprise Value to get the Equity Value.
  • Divide the Equity Value by the number of outstanding shares to get the intrinsic value per share.