### 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)

**Forecast Revenue Growth**: Project the company's revenue for the next 5-10 years based on historical growth rates or analyst estimates.**Estimate Operating Margins**: Project future operating margins to calculate operating income.**Calculate Taxes**: Apply the corporate tax rate to the operating income to find the after-tax operating income.**Adjust for Non-Cash Items**: Add back non-cash expenses (like depreciation) to the after-tax operating income.**Subtract Capital Expenditures**: Estimate future capital expenditures necessary for maintaining or growing the business.**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.