Financial Modeling Quick Lesson: Building a Discounted Cash Flow (DCF) Model - Part 2

Note: To download the Excel file for this lesson, go to www.wallstreetprep.com/blog/financial-modeling-quick-lesson-building-a-discounted-cash-flow-dcf-model-part-1/.
Learn the building blocks of a simple one-page discounted cash flow (DCF) model consistent with the best practices you would find in investment banking. If you are preparing for investment banking interviews, know that the DCF is the source of a TON of investment banking interview questions.
The DCF modeled here is a simplified version of a fully-integrated DCF model. For a deeper dive into DCF modeling in Excel, please visit www.wallstreetprep.com.

[@Rome.W]

what an excellent video. I learned so much in a short amount of time. bravo.

[@pcranston5]

Would love to see a video where you use the exit EBITDA method rather than the growth in perpetuity method

[@thomaskavoori395]

This was very helpful. Thank you.

[@deanathanailos7140]

Great Video

[@jiajundu5925]

Thank you very much! This video has helped me a lot and definitely made sense.

[@thegermanmonst]

Fantastic video. Thank you very much!

[@thetachi1896]

amazing video serious, very helpful thank you!

[@pcranston5]

Sorry I see where I went wrong, I was grabbing the PV of FCF at t=5 when I should be just using the FV. Thanks

[@jhnellejohnson5897]

very informative

[@aaftabkhan9789]

Thank you for this DCF model. it really helped me a lot to clear DCF valuation concept. Can you do a Capital asset pricing model(CAPM) model to find out rate of equity (15%)

[@Nader95]

just plug in: annual yield on 10 year treasury bond * (Beta or riskiness of the particular stock relative to sp500, which can be found on yahoo) * (average yearly return of sp500 - annual yield n 10 year treasury bond)

[@eeshueevasworld9226]

How do we calculate cost of equity?

[@sahil99farm]

Have you used FCFF or FCFE in this model?

[@erikac.3570]

This is FCFF.

[@sahil99farm]

+Erika Oh. Can you please tell me what changes we need to make in the "Enterprise Value to Equity Value" section in case of FCFE?

[@limrb]

hi - just a question - should we consider terminal capex when calculating terminal value?

[@Nader95]

no, capex is already in incorporated in the explicit forecast period AND the implicit forecast period. The implicit forecast period just takes a 4% assumed growth rate from every previous period, so this assumes CAPEX is growing in the implicit period which accounts for the "terminal capex"

[@sandman8347]

Why do you subtract net debt? Every FCFE formula says that you need to ADD net debt. FCFE = FCFF - (Int(1-t)) + NET DEBT

[@lukeandriuk2793]

You're correct when moving from Equity Value to Enterprise Value. He, however, is doing the opposite, so net debt must be subtracted.

[@marktwyman2417]

has anybody successfully accessed the link to the excel file

[@ooch89]

Mark Twyman www.wallstreetprep.com/blog/financial-modeling-quick-lesson-building-a-discounted-cash-flow-dcf-model-part-2/

[@heruilin]

These two videos were excellent .. greatly appreciated! Being new to finance I had to stop them a few times to look up Wikipedia for formula derivations (specifically the geometric sum for determining the terminal value using the perpetuity growth method). As a follow up I would like to see a video that discusses how the 15% value for cost of capital was determined for this example.

[@jkehannon]

If I could give you a hug, I would. This is EXACTLY what I needed

[@Nader95]

According to Valuation: Measuring and Managing the Value of Companies, you should use UNDILUTED shares outstanding, not diluted...trying to search for the page again

[@etuipp]

I understand that the WACC for the FCF calculation is kept the same for simplicity, but should inflation not be considered additionally when it comes to terminal value? I understand it it technically is already included (interest rates for both debt and equity), but when considering it in perpetuity my brain says it needs to adapt. Can someone help me explain?

[@MrBish435]

You are really good simplifying and explaining the models. I love it!!

[@23jamieboy]

Best DCF analysis I've come across. Covers nearly all technicalities.

[@amedeoscarano]

It seems that Lars Ulrich of Metallica is actually explaining this lesson!! lol

[@rohampourmehr2275]

What if you buy the stock and the market stays inefficient longer than you can stay solvent?

[@holymaccaronister]

lmao

[@sshetty2k]

Excellent Video and content . I was looking at DCF and your video captured it very well. Thanks for the great video.

[@Nader95]

Actually, for cell 98 you don't want to make a buy recommendation just yet. You want to know why your model produced a different result than the market's, which is filled with sophisticated hedge funds. You need to analyze the sensitivity of your inputs to see which ones are truly affecting your estimated value and whether changing certain parameters will or can produce a similar market value price of 25 dollars. You need to also look at the macro picture of why wall street is undervaluing the stock; perhaps it is because of pending lawsuits, new or threatened regulations, changing industry outlook or competitor advancements. Remember, your 4 percent perpetuity growth rate is just that: a forecasted rate assuming a mature company that doesn't have to deal with special cases I just mentioned. So, the recommendation cannot be a simple function of whether an estimated value is above market value (buy suggestion) or estimated value if below market value (sell suggestion). thanks

[@StephanieGarciaMoneyEvo]

Do you have a cost of equity video? Great video.

[@pcranston5]

Great video, thanks very much. Just want to point out that your free cash flow @ (t+1) does not change after inputting WACC. I get a value of $1,165.58 which gives a PV of terminal $8,287.37. If I am doing something wrong please let me know. Thanks.

[@simonclasse9435]

6:58 why at this stage you didn't substract cash to compute the net debt and later on you did 9:50 ? I'm confused.

[@jamesemanuel6464]

The strange thing about this case study is that if you calculate the growth rate for Revenue or EBITDA or EBIT based on the "Actuals" data it bears no similarity to the forecast growth rates used to calculate the projected annual forecast. For example revenue grew at 7.55% from 2010 to 2011 and by 5.26% from 2011 to 2012, so revenue growth seems to be in decline and this is a known quantity based on Actual data, yet the forecast revenue growth starts at 10% and increases to 13%! How realistic is that? I know that this is only a case study example, but it would have been nice if it had been made more realistic.

[@dimashkuanysh5288]

0:01 is the best part of the video)

[@jinzzz3490]

Present value of free cash flows for periods 2-5 are wrongly shown. I used the same formula and got 1318, 1375,1587 and 1762 respectively. Anyone else faced this problem?

[@Luke_D]

humm I learned the final number in the DCF analysis as Firm value and not Enterprise Value. With EV, cash and cash equivalents are already subtracted. I see that you compensated for that by subtracting "net debt" from EV to get the correct equity value, but is that really whats happening? The number at the end of the DCF analysis before debt is subtracted includes cash and cash equ, so it's not really EV. Or am I missing something?

[@yomajo]

07:05 That Market Cap, not Equity... :

[@Mike-uz9hs]

How do I determine long-term growth rate?

[@mannurawat4522]

Through gdp of that particular company.

[@NexGenSlayer]

Historical Data with trend analysis. You could do regression analysis too, that is probably preferred

[@rockey9045]

Where is declared sheet for this presentation?

[@simonclasse9435]

@@NexGenSlayer Can we use the formula ROE*(1-payout ratio) ? If it depends , it depends on what ?

[@NexGenSlayer]

Simon Classe Yeah. That formula is correct and is often used as a predictor of Long Term growth rate. Just recognize that the ROE is typically only derived from one period and you should check to make sure its not abnormal (and if it is, why is it abnormal; if the company for example just introduced a revolutionary new product that customers just ate up, it may indicate there is yet room to grow over the next few years assuming technology doesnt change rapidly (the method you described would do assuming payout ratio has been stable over time), but if the product is a “fad” product and the company has no vision or strategy for the future when demand for the “fad” product dies off then growth may not be reflected by the most recent ROE (your method that you described wouldnt likely work well in this case) this is the qualitative part of Finance). Hope that helps!

[@Enricarenee]

How do I forecast?

[@ahmedxh]

Why do we use investors equity appreciation as an expense? I mean even if they sell after the stock is appreciated another investor is taking position at the same price The first one got out on

[@lighto999]

Great video, but shouldn't "cash" be subtracted from Enterprise Value and debt be added back in? you only added the FCF

[@alexlindgren1]

Very good - but I can only find the excel-file for the first stage done, not the second one.

[@rinmonimpak]

Impressive, very good explanation. Simple and precise

[@RiccardoRoccoPierre]

Any chance you can upload this template?

[@arifarafat999]

Do you share the files publicly?

[@sahil99farm]

Have you used FCFF in this model? Or is it FCFE?

[@fffppp8762]

is it gordon growth model beyond forecast period?

[@gayathrinagarajan868]

Excellent! Extremely useful

[@arifarafat999]

Question: how we can evaluate the private company which the data is very limited?

[@Nader95]

that is soo hard to do. that is why regulations forbid small investors in investing in private equity. I'm sure they have models for private equity but without good data you are out of luck. maybe people look at other similar private companies and see how much they were valued for in seed round financing. But usually when these companies go public their shares drop big (meaning they were overvalued as private companies)

[@TheJaebeomPark]

Great video!

[@jakekramer9377]

Excellent video

[@wallstreetprep]

thank you Jake!

[@Warfieldization]

How did you get 15%?

[@MrMainmoon]

What if the net debt exceed the enterprise value and the equity value come out as negative?

[@Nader95]

then, the shares would be worthless I guess

[@Dokuzu]

Jesteś najlepszy

[@mariacaceres8951]

So well explained. Thank you. Full recommended.

[@eroltasdelen3377]

great lesson. thanks a lot.

[@khushpindersingh686]

For the perpetual growth period cf, I suppose you need to use (wacc-growth) in the denominator

[@Nader95]

just divide the terminal value by wacc - growth rate. nowhere else do you need to divide by wacc - growth rate

[@martinsekaziga3738]

Great video..how did you determine the long term growth rate?

[@Nader95]

most mature companies will grow at the annual GDP growth rate of about 2-5% So, he just estimated the long-term growth rate. It's a reasonable estimate according to GDP growth

[@merrillseq]

awesome! thanks man

[@KPrandecka]

Awesome, thanks a lot!

[@agadler]

Fantastic! Thanks!

[@chaingangritesh]

Great help i say.

[@kahlschlag17]

Great post mate, thanks a lot. I do have a question? Would it be appropriate to use personal opportunity cost as cost of equity rather than CAPM derived cost of equity and if not why not?

[@Nader95]

that's not industry standard. CAPM incorporates risk free rate so that is technically an opportunity cost. That is, I could have gotten, say, 2% annual yield in a 10 year bond but instead I'm investing in this stock. So in my model, I need at least 2% = rf + riskiness of stock, which is measured in Beta (or correlation coefficient relative to sp500) * difference in sp500's return (10%, for example) or bond yield of 2% (therefore, 8%) = 2% + 1.14 * (10 - 2)