DCF Excel summary

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

 

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

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

1. Equity Value
2. Employee Stock Options
3. Unlevered Beta
4. Levered Beta Formula