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
250+ Online Courses | 1000+ Hours| Verifiable Certificates| Lifetime Access
4.9
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
