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.
To download the backup Excel file, go to www.wallstreetprep.com/blog/financial-modeling-quick-lesson-building-a-discounted-cash-flow-dcf-model-part-1/
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.

[@wallstreetprep]

• Source: Wall Street Prep's Quick Lesson Series • DCF Model Training: www.wallstreetprep.com/knowledge/dcf-model-training-6-steps-building-dcf-model-excel/ • Walk Me Through a DCF: www.wallstreetprep.com/knowledge/walk-me-through-dcf/ • Financial Modeling Templates and Guides: www.wallstreetprep.com/wsp-blog/

[@MrJayJay198]

7 years on & you are still saving student's lives. Thank you sir!

[@fatemeazizi1863]

I would like to introduce you to the free online business valuation software of Ratiba . I hope it is useful for your business. retiba.com/online-valuation/discounted-cash-flows/ Online valuation tools in this software are : 1. DCF Method 2. Risk Factors Summation : retiba.com/online-valuation/risk-factors-summation/ 3. Multiples Method : retiba.com/online-valuation/multiples-method/ 4. Score Cards Method : retiba.com/online-valuation/score-cards-method/

[@skaidanseur6307]

Thank you so much for making this video. You've been such a help for me to understand finance.

[@wallstreetprep]

Matt - absolutely right - I accidentally took the yoy change b/c I set up capital expenditures right below the balance sheet items, I'll get this fixed shortly.

[@danielnguyenvu]

Watching this video and save me so much time browsing for the model. Thanks so much after 8 years

[@johnmavro1450]

Thanks so much, very informative!

[@chaingangritesh]

This video is too good, been looking out for understanding since long, thanks for the video. Looking forward to watch more videos.

[@MegaNarutorules]

This is such a good refresher, bless your soul

[@DesiWaveWorldwide]

Thanks a lot for sharing such valueable info and excel files.lot of love

[@mohitthakur79]

Excellent video. Thanks !

[@blueleonhart1139]

This video is very good for beginner like me. Thank you for making this video.

[@jf13579]

Thanks for the refresher

[@wallstreetprepsupportteam1830]

Ryan - the link does work, so we recommend that you right click and save as first.

[@monikozz]

Thank you so much

[@dreammajor]

Thanks!

[@benardwanjala8278]

very informative. nice work

[@mikes3637]

This is a much simpler version of the Excel Model. Ideally It goes into sheets and Separate Schedules are prepared for WC, D&A etc. But still good job thanks a lot

[@Samuel-wf6kk]

Love your video

[@jetfreak4]

What was the model/equation you used in the DCF? I am new to DCF and would like to know how you came up with your values.

[@conjetapierre8755]

Thank you

[@nvlptl]

I have IB interview tomorrow. Thanks for a great video!

[@llTheLastThronell]

+Nev Dog How did it go? I hope you've nailed it.

[@chickenceiling]

this is the nicest youtube comment i have ever seen

[@fauxcapital32]

I hope everyone does well in everything. Let's go KZbin positivity!

[@maxwulz9283]

how are you guys doing? are you in IB?

[@zoom-jx3so]

For the cash flow buildup- you mentioned the FSM already has forecasted cash flows, but they do not account for reinvestment (CAPEX). Can't you just use FSM forecasts and subtract capex, and theoretically arrive at the same result without going through building up the cash flows yourself? Thanks

[@TreasureXsu]

How did you came up with the numbers from 2013 to 2017? How did you estimated it? Thanks!

[@konsir80]

Hi Wallstreetprep, sorry to bother, but the link is not working. Is there some way to download it from your website? Thanks.

[@jasonmazurek3748]

Excel link not working

[@kakacricket]

This makes so much sense i swear. Why do college professors have to make this shit so hard. Maybe cause of all the non finance dumbasses in our classes, so they have to over complicate things just to make them get it. THankyou so much again

[@NexGenSlayer]

It would have been nice if you also explained that the numbers you used were found using the Long Term growth rate.

[@jonathantsarouhis4900]

I beleive he based it off of the US GDP Growth rate (average 3 -4% annually) if you see this and tax rate are in solid cells so he had that information going in

[@shak725]

The Capital expenditure mentioned in calculation ;will it be consider Loan from Bank? or only Equity?

[@ryanharwood9743]

Your link to the excel sheet has failed. Could you please provide a different one

[@caesercooldude]

Hey, Just a small questios, Capital expenditure would mean the total outflow of cash, so the value in cell F59 will be -F35. If we are assuming capital expenditures as PPE, then we would need to apply base analysis to make sure that we are picking up the right Capex number

[@philjames5961]

2 years later and I had this same question. Thanks for raising it because I wasnt sure why he was referencing the change in CAPEX, but I see from your comment he was probably using PPE rather than the actual capex for the year. I agree with you it would have been good to see the base analysis in this case

[@MarkEm]

Did wall street prep make a video on leveraged dcf? Want to assess the non-ebit model

[@lulumink0]

Hey, does the video still contain the error I suppose, right? So, the correct way would be just copy the row 35 and paste it to row 59? An explanation would be awesome. Thanks

[@dok2.333]

what are all the numbers you are referencing for the calculations? Do you have a video explaining how you projected those numbers or are they all historic data?

[@VlerkeDamne]

Late reply but might help others: These numbers are taken from a company's balance sheet, income statement and cash flow statement. Which can be found for most companies on websites such as Yahoo Finance or the company's website itself. The values are then forecasted with a certain growth rate. A basic accounting class can help you understand these numbers.

[@maxdominate2481]

Very Nice!

[@alexchien2755]

Thank you for sharing such important information. Can you please send me the template so I can try on my own? thank you

[@zubinwilson9989]

please share the link for the excel spreadsheet

[@MrMainmoon]

can you or someone please explain why you didn't simply getting the free cashflow from the cashflow statement subtracting capex from operating cashflow. You seem to do more than what i learn from other videos. You also included acct receivable, inventories, and expenses. I dont understand why.

[@starzhl]

CAPEX also includes depreciation but we are not given the amount of amortization.

[@HeldEd]

Is this the same type of audio we would hear if we purchase the course ?

[@josephchun7401]

The excel backup does not work. Is there a new link you can send me? Thanks.

[@fatemeazizi1863]

I would like to introduce you to the free online business valuation software of Ratiba . I hope it is useful for your business. retiba.com/online-valuation/discounted-cash-flows/ Online valuation tools in this software are : 1. DCF Method 2. Risk Factors Summation : retiba.com/online-valuation/risk-factors-summation/ 3. Multiples Method : retiba.com/online-valuation/multiples-method/ 4. Score Cards Method : retiba.com/online-valuation/score-cards-method/

[@fatemeazizi1863]

I would like to introduce you to the free online business valuation software of Ratiba . I hope it is useful for your business. retiba.com/online-valuation/discounted-cash-flows/ Online valuation tools in this software are : 1. DCF Method 2. Risk Factors Summation : retiba.com/online-valuation/risk-factors-summation/ 3. Multiples Method : retiba.com/online-valuation/multiples-method/ 4. Score Cards Method : retiba.com/online-valuation/score-cards-method/

[@robbo8074]

Where do you get the long term growth rate of 4%?

[@Dokuzu]

Dzięki mordo

[@HGHOU-ys8nn]

how to calculate capex?

[@myles_21]

Does anyone know where the Growth % assumptions came from? (for all line items in which Growth % was already plugged in)

[@sevenup25]

what is the reason for creating Financial Modelling. and how is it useful for the company, and i dont have any understanding on Financial Modelling. will please give the detailed outlook of financial modelling.

[@ntcuong01ct1]

Dear Friends, I have 1 question regarding Financial Modeling: 1/ Please tell me the difference between Scenario analysis, sensitivity analysis, stress test?, and the intended use of each?. Thank you.

[@justinseah4011]

How does one calculate the growth rate which is essentially the most important other than WACC

[@howzthatIE]

+Justin Seah, A million Dollar questions! as per experts and academics, this is an art not science. However, as one reads more into a sector/company through qtly/annual reports and management presentation, the art starts taking shape. Above all, there are no more than 5-6 ways of stock valuation. If all analyst predictions are based on these methods, it has a causality effect on the asset price.

[@spiritualwoods4677]

Why do we subtract the CHANGES in acc.reci - inventories etc. and not the actual number of that year? In order to get the FCF?

[@MisterGujjuu]

Because we are looking at accumulated change in working capital

[@blueleonhart1139]

Hello, I want to know how to calculate account payable growth. Thanks

[@timothycoudriet8951]

Your first need to find DPO: DPO= AP/sales * 365 (this gives you average days it takes to pay bills) , then you can make a projected assumption of how long it takes to pay. Then AP: AP = DSO * (COGS/365)

[@prathamsharma9150]

Can you share the excel template? Thanks

[@sumanarora739]

Did you get that?

[@sumanarora739]

If yes please share at [e-mail]

[@sahilarora1986]

Two things: 1. You need to calculate tax after deducting interest expense. Understand it's your model assume no debt, but it is misleading to do the way you have show 2. Capex is not a balance sheet item. Youre showing a y/y variance the way you're showing it. Correct way is to pull the full amount of caped for the year down

[@oliverb6313]

" You need to calculate tax after deducting interest expense". Why are you deducting interest for an unlevered cash flow calculation? Edit: on your second point I believe you are totally right and the whole model is wrong as a result. The entire capex amount for each year should be deducted to arrive at FCF, not the year over year change as he has done. This is hugely overstating the FCF and everything thereafter is completely incorrect. That said I'm still in disbelief that such a colossal mistake could be made, so I keep thinking I'm missing something but I don't think I am...

[@NexGenSlayer]

Agreed, had he used Long Term Assets (PPE) and subtracted that from year to year it would be correct. I believe he addressed this in an annotation though. But yeah, CAPEX is an expense, not an asset.

[@casst346]

i see he put a footnote indicating that he understood that it should be done "explicitly" each year but just did the year over year for this example..

[@Samiksha_W]

@@NexGenSlayer Also if it was considered as PPE , then it should add back the depreciation to arrive at the CAPEX as the PPE would be stated at WDV.

[@jasonduryea6714]

For anyone wondering.. Cap X is an expense not an asset. thus treat it like an expense when calculating the FCF

[@Nader95]

why are you taxing the Row EBIT? You should pay the interest and then apply the tax (since interest expense is tax deductible) Or is your method done because you are assuming an unlevered model?

[@mrzipdisk]

Microphone windscreen must be associated with negative cash flow.

[@sandeshshetty6888]

Hi, Wall Street Prep or any knowledgable person could help me understand, why inventories and prepaid expenses are deducted to arrive at Free cash flow. I believe both are cash expenditure.

[@debitcredit4279]

The best way to think of it is from a debit/credit perspective. For example, an increase in prepaid expenses is an asset because you're paying for something ahead of time. This fits the description of an asset because it's anticipated to provide a future benefit to the company. Since it's an increase in an asset, it must be debited. Because we are doing a cash flow, we will assume the other entry is going to be to cash. Since its double entry accounting, and we've just debited prepaid expense, we must credit cash because debits must equal credits. Same logic applies to inventories. For example, if we increase inventories, which is an asset, we must debit it cuz all assets increase with a debit. Again, since we're preparing a cash flow statement, we must assume the other entry is to cash. Since we debited(increased) inventory, we must credit cash to balance the entry... hope that helps.

[@charlespercy1386]

Don’t understand why the change in Capex is used , rather than the actual Capex, in determining the FCF

[@wallstreetprep]

Hi Charles - good question. The change in gross PP&E is used - which is the capex in the period. In other words, as you spend on capex each period, this adds to your balance of gross PP&E.Working backwards, you can arrive at the annual capex by looking at the change in gross PP&E.

[@jamesmortenson961]

Paste special that formula

[@mengl7241]

Can someone tell me what is the hot key for paste special?

[@miqbalkoto]

This might be one year late, it's Ctrl+Alt+V.

[@maximillianthomas3693]

Why don't you have cash included in the free cash flow buildup?

[@vincentwess3416]

because to calculate unlevered fcf you look at the change of net working capital on a year on year basis. Since net working capital includes current operating assets it does not include cash and cash equivalents because it is considered a non operating asset.

[@lindsaybettiol1]

haven't you discounted terminal value twice? You use year 5 discounted CF, divide it by (WACC-g) - to me this is terminal value already discounted. Why is it then discounted by WACC a 2nd time?

[@1mbA4ever]

+Lindsay Bettiol This comes really late as I just came to this video and looked through the comment section. What he is doing is calculating the Present Value of the infinite future CF for period 5. In other words, he takes the FUTURE VALUE in period 5 and calculates with this value to figure out the terminal value at hand. Note that he linked towards the NON-discounted FCF not the already discounted one. If you do it based on this way of calculating you will have to have discount it from period 5 down to the present. You could also (in this case at least) link the PRESENTVALUE of period 5 and only divide by (WACC-g) and receive exactly the same result. TL;DR: Either calculate with the future value and then discount to present values or discount future values and then calculate with that.

[@ritwikghosh8174]

I am here 2021

[@rocker4579]

Why's yung blood spittin into the mic so hard

[@TheCrackedFX]

frfr

[@talbothouse]

I think that the CAPEX amount you are using in row 59 in incorrect. You are only showing the YoY variance in CAPEX, as if the CAPEX balance in row 35 was a multi-year cumulative amount. The CAPEX for 2013 is 588.5 and that entire amount should be used in row 59 not the YoY variance of CAPEX.

[@lc424]

that is the point. thank you for bringing it up, I doubted myself :D

[@thecolson26]

you're killing my ears homie!

[@ericaltm]

Please redo the series with a better mic

[@kaprismedmos]

the sound is bad :(

[@karllogan5084]

He doesn't even have a developer tab on his excel pfft smh

[@cockerbrains]

so?

[@bailarinasesina1752]

I think this DCF looks really bad

[@cockerbrains]

link to one better?

[@ranoch1]

You are too fast as if you need to catch a flight go slow and explain slowly

[@ryanpalm6415]

Dude your mic work... very unprofessional

[@snandbakansenwsbandjs5493]

Yang milih allah like 40000 ya