## Part – 17

In our last tutorial, we understood equity value and sensitivity Analysis.In this article we will understand DCF Excel summary.

### Step 1 – Forecast the Income Statement and other FCFF drivers for the Explicit Period

### Step 2 – Forecast Working Capital

### Step 3: Calculate FCFF using EBIT formula

### Step 4: Calculate Terminal Value

#### Method 1 – Perpetuity Growth Method

When WACC = 10% & Growth rate = 4.5%,

#### Method 2 – Exit Multiple Method

**When the EBITDA transaction multiple is 7x,**

### Step 5: Terminal Value Reality check of assumptions

### Step 6: Extract the Capital Structure

### Step 7 – Understanding The Convertible Features :: Calculate ‘in the money’ convertible securities

### Step 8: Calculate ‘in the money’ stock options

### Step 9: Calculate value of Debt / Proportion of Equity & Debt in Capital Structure

**Debt**

**Total Capital = Debt + Equity **

**Total Capital = 220.0 + 106.4 = 326.4**

**The above proportions will be used to calculate the Weighted Average Cost of Capital (WACC).**

### Step 10 – WACC :: Cost Of Debt

Using synthetic rating method, we have Interest coverage ratio = EBIT / Interest Expense

Interest Expense for ABC company (small cap $257million) is 15; Interest coverage ratio = 50/15 = 3.33

Pre tax Cost of Debt = Risk Free rate + default spread = 5.0% + 3.50% = 8.50%

Post tax cost of debt = 8.50% x (1-33%) = 5.70%

### Step 11 – Calculate Cost of Equity & WACC

Identify the listed comparables and their Beta. Also, find the Unlevered Beta for comparables

4.9 (3,296 ratings)

View Course

### Step 12: present value of the FCFF for the projected years

Calculate the Present Value of the Explicit Cash Flows using WACC derived above

### Step 13: Calculate the Present Value of the Terminal Value using WACC

#### (A) Terminal Value using Perpetuity Growth Method

#### (B) Terminal Value using Exit Multiple Method

Please note that the Terminal Value from both the approaches are not in sync. We may have to double check our assumptions on EBITDA Exit Multiples or the WACC/growth rate assumptions applied. Both the approaches should ideally give similar answers.

### Step 14: Calculate the Enterprise value of the firm

By summing the (adjusted) present value of the projected free cash flows and the (adjusted) present value of the terminal value (whether calculated using the perpetuity method or multiple method), the result is the Enterprise Value of the modeled business.

### Step 15 – Arrive at the Equity Value of the firm post Adjustments

### Step 16: Sensitivity Analysis of Key Inputs

The data table function is especially useful for conducting **Share Price sensitivity analysis**

### Recommended Articles

Here are some articles that will help you to get more detail about the DCF Excel summary so just go through the link

Satish chand says

interested but i requirement of this sheet