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

welcome to Wall Street perhaps video on how to build a basic discounted cash flow model Before we jump into the model what I'd like to first do is go over from a high-level perspective what a DCF entails and why we use it in industry so a basic DCF model involves projecting future cash flows and discounting them back to the present using a discount rate that reflects the riskiness of the capital you then add up all of those discounted cash flows and the sum is really the intrinsic value of the company and so many people in industry compare that value to market values to determine if something is overvalued undervalued or valued correctly so it really is an important form evaluation and almost all people in finance use this valuation to a certain degree it's also worth noting that there are many different variations of the DCF but there are generally two different types unlevered and levered unlevered ecfs are before the payments of debt and that's actually what our model is so you'll see we're dealing with items like EBIT EB OTT which means it's before the payments of interest as well in debt pay down levered on the other hand is after interest expense and after debt pay down given that unlevered free cash flow analyses are the most commonly used we're going to focus on that for the purposes of this video for more information on levered you can check out online or some of our other videos now so now that we know more about the DCF and we know about why we use it as well as what it entails from a high-level perspective let's take a stab at our first section which is the free cash flow build-up now if you've created a model before you know that you might have a financial statement model that precedes the DCF analysis and then that model you've got a forecasted cash flow statement so you're probably wondering well then why do I even need to do a free cash flow build-up if I already have that basic FSM and the answer is because cash flow from operations does not include the important capital expenditures that you see here and we know that free cash flow by definition is operating profit less reinvestment reinvestment being capital expenditures so while a lot of these other items are common to the CFO section of a cash flow statement it is missing a critical element which is the capex so that is why we need a cash flow build-up as you see here the next thing you're probably wondering is what why we forecasted for five years the real answer is well it depends on when your company has stable earnings so we're assuming that in five years the company will have stable earnings so typically practitioners forecast cash flows anywhere between five and ten years and even if you've got a company that is mature like a coca-cola Procter & Gamble or GE it's still recommended that you do five years so you can see the cash flow build-up now that we understand sort of what the section entails it let's go ahead and start building it so we're going to go ahead and reference revenues from our select operating data schedule and I'm going to ctrl copy this paste special the formula and I'm going to do the same thing with EBIT da as well as EEB it okay we're going to go ahead and reference EBIT now again this is an unlevered free cash flow analysis so it's before the payments of debt now given that taxes are not an optional thing you have to pay taxes to the government we're going to focus on EBI which is earnings before interest after taxes so we take a bit and we multiply by one minus the tax rate to find the after-tax value of EBIT another word for EB OTT that you might hear is NOPAT and that's net operating profit after taxes next in order to go from EB OTT to unlevered free cash flows we need to make several adjustments those adjustments include adding back non-cash expenses and subtracting out non-cash gains making our accrual adjustments and then subtracting out our our reinvestment which is capital expenditures so first thing we're going to do is we're going to add back our non-cash expense expenses which in our case is just DNA so again control copy paste special the formula now as you recall when we when you build a cash flow statement an increase in an asset is viewed as a use of cash while an increase in liabilities or equity is viewed as a source of cash now I know that accounts receivable when we increase that it's hard to really picture why that's a use of cash so I'm going to explain it from a different angle here and that is that we know that from cash versus accrual accounting revenues includes both sales made on credit as well as sales made on with cash so in other words if our credit sales increase that's going to indeed increase revenue but we know that we're not actually receiving that cash yet we're going to receive it at a later point in the future so if our accounts receivable goes up we need to make the necessary adjustments to EB OTT to reflect the actual cash that is coming in which is only cash sales so therefore an increase in accounts receivable will need to be deducted from EB OTT to correctly reflect cash coming in so one way to do this would be to do uh the first forecasted year minus the previous year but you need to make sure to add a negative sign to that value otherwise you won't have the correct signage now what I'm going to do is I'm going to do previous year - the first forecasted here because that will automatically embed the sign now the beautiful thing is in our model is that accounts receivable inventory and prepaid are all right underneath each other so I don't really need to do much more here other than control copy downward and I can use what's called a control D function which is a built in excel function so I do control D and then I can do control R which is also an excel function and you can see it populated without me having to reference the balance sheet data constantly now accounts payable being a liability an increase in that reverie a source of cash so I'm going to do first forecasted period minus previous forecasted period and again if you look up on our schedule accounts payable and accrued are right underneath each other so all I need to do is simply reference these two rows here in the free cash flow build-up I could do control D and then control R again both are Excel functions now regarding capital expenditures they are an asset so an increase represents a use of cash okay now what we need to do is we're going to add EB OTT DNA our working capital adjustments as well as capex and you'll see that we have whack here whack we're going to actually go ahead and calculate later so we're going to leave that blank for now but we can still figure out the present value of free cash flows which is unlevered free cash flow divided by 1 plus whack raised to the period so we go ahead and control copy paste that you'll notice that the present value of free cash flow hasn't changed why because we haven't calculated whack yet and so our sum of present value free cash flows is the sum of all those cash flows that you see discounted back to the present and this is what we call our stage one of the free cash flow analysis an enterprise value is the sum of stage one and what we call stage two which we'll talk about momentarily so as you can see we've now built up our free cash flow for the explicit forecasted period ie stage one and it's now time to focus on what we call stage two which is the terminal value terminal value represents all value beyond the explicit forecast period so from your 6 onward now it's hard to use a basic discounted cash flow formula to to calculate terminal value given that a extends into the future long into the future so we there's really two different methods to calculate terminal value the first one is what we call growth and perpetuity which is what we're going to do in our model and the second is what we call exit EBIT on multiple method but we're going to focus again on growth and perpetuity terminal value is a value that's assumed to exist in year 5 and so we're going to have to apply the discount factor from year 5 to this terminal value to arrive at the present value of stage 2 and our enterprise value will be that stage 1 plus stage 2 and so now that we know what terminal value is let's go ahead and and calculate it so a whack again will be calculated later on so our free cash flow and t plus 1 is going to be this unlevered free cash flow times 1 plus the growth rate we take that free cash flow and we divide by whack minus the growth rate again growth and perpetuity and the value looks funny now because we haven't calculated whack yet as you can see we're going to do that in a later section so the present value of terminal value is going to be this terminal value divided by 1 plus the whack from the fifth period raised to the 5th power again because it's like terminal value from a discounting standpoint happens in 2017 again don't worry about this value yet it will it will fix once we add in our whack so this concludes our part 1 of this series part 2 we're going to go ahead and calculate whack we're going to link that up top so that our our stage 1 as well as stage 2 fixes and then we're going to go from enterprise value to equity value so I'll go ahead and see you in this next video thanks for watching

Channel: Wall Street Prep

Views: 557,361

Rating: 4.8753133 out of 5

Keywords: dcf, discounted cash flow model, dcf model, wall street prep, financial modeling

Id: gLUlP6jyFvE

Length: 11min 7sec (667 seconds)

Published: Fri Feb 22 2013