We walk through how to build a discounted cash flow model to calculate present value of the future cash flows of a company. We then perform a valuation of the company with a sensitivity analysis to test our assumptions. Also included is downloadable Excel model.
RESOURCES & LINKS:
► Discounted Cash Flow Model Template Download: p
🚨 Join my Finance for Startups training program (with personalized support, livestreams, and more): s
Join My Communities:
► Subscribe to my email newsletter: r
► Discord: S
My Excel Courses:
► Become an Excel Power User in 2.5 Hours (85% discount): 1
► Master Excel Keyboard Shortcuts (85% discount): 1
Related Financial Modeling Videos:
► Build a 3 Statement Financial Model: k
► Finance Interview Prep Series | Crush Your Interview: p
► Enterprise vs. Equity Value: M
► Top 10 Startup Metrics Used by VCs: c
In this video, we build out a discounted cash flow analysis of a company to calculate a valuation of the equity value of the business. We also perform a DCF sensitivity analysis to test our assumptions and look at a range of valuation scenarios. You will learn all the formulas and concepts necessary to do this yourself in Excel!
The discounted cash flow method of valuing companies is probably the most common method to give a valuation to the business. In order to perform this type of analysis, we need a few things to start off with.
Forecasted Financials (5+ Years)
We will need a full set of projected financials of at least five years into the future from a 3 statement financial model (link above on how to build) that incorporate: EBITDA, EBIT, taxes, changes in net working capital, and CAPEX. With all of these variables, we can calculate unlevered free cash flow.
Unlevered Free Cash Flow
For the “future cash flows” in our DCF valuation, we generally use a metric called unlevered FCF. This is basically the cash flow of the business after covering all of its operating expenses, but it excludes any debt interest or principal payments. This is a cash flow stream available to the owners of the business to do what they want with (pay off debt, pay a dividend to themselves, etc).
Terminal Growth Rate & Discount Rate (WACC)
We need to look at the economy that this company is operating in so that we can make a guess on the terminal growth rate. The terminal growth rate is the rate we say the company will growth at “forever” into the future. Generally, this assumption will be lower than the overall GDP growth of the country. So if the country is growing 3% in GDP, the terminal growth rate assumption might be 1.5 or 2%. The WACC or discount rate is calculated based on the capital structure of the business, the overall economic environment, the returns of similar assets, and opportunity costs. We use the WACC to calculate both the terminal value and the net present value of cash flows.
EBITDA Exit Multiples for Comparable Deals
We need to know what other companies are being valued at (and bought at) in our industry, with similar growth rates and similar scale. If most business similar to ours are getting purchased for 10-12X EBITDA, then we know this is probably a good guideline for thinking about how to value our own business.
Once we have all of these assumptions, we can build out a discounted cash flow model & create different valuation estimates for the company.
One extra step we do is we perform a sensitivity analysis for our DCF model. In this analysis, we build out an Excel data table to examine how the valuation of the company changes while we simultaneously change two inputs. We look at what happens to the equity value as we input different discount rates and exit multiples. This helps us understand how sensitive our model is to small changes in the assumptions, and lets us know if our valuation makes sense.
0: 27 Description & calculation of unlevered free cash flow
2: 34 Calculating the terminal value: EBITDA multiple method
3: 39 Calculating the terminal value: perpetual growth method
5: 52 Calculating the net present value (NPV) of future cash flows
8: 39 Why we use Equity Value (rather than Enterprise Value) and how to calculate
9: 45 Discounted cash flow valuation sensitivity table [BONUS]
I hope that you can now feel confident building a discounted cash flow model in Excel. If you have questions – leave comments below and I’ll try to help. Cheers!
► Subscribe for more finance videos: e
#discountedcashflow #dcf #npv #terminalvalue