Skip to main content
Loading Explore guides…
Discounted Cash Flow Model Steps · Acedra
Home
Explore
Business
Discounted Cash Flow Model Steps
Sign in to save
Chat
If YouTube asks you to sign in, open the video on YouTube.
Watch on YouTube
Reload player
Transcript
72 segments
Copy
Hide video
0:00
in this video we'll go over a full discounted cash flow model on excel using five steps so firstly we're going to forecast the company's future cash flows secondly we'll calculate the wac which is the discount rate we'll use to bring back the future cash flows to the present thirdly we'll calculate the terminal value which is the value of the company after the forecasted period fourth we're going to discount both the cash flows as well as the terminal value back to the present
0:28
value and lastly we'll calculate evaluation an implied share price and a sensitivity table and don't worry if you then understand some of these steps we'll go over them in detail in the video let's go first up we need to project the cash flows into the future and for this you can download the excel file we'll be working with in the description so over here we've got towards the end a tab for the income statement of the company the balance sheet
0:53
and the cash flow statement and we'll start our analysis at the very first tab which is going to be the free cash flow one and over here when we refer to the future cash flows they're actually going to be free cash flows so to to get those we're going to have to find all of these figures over here for the estimated years and eventually reach the free cash flow and at this point you're probably wondering what does free cash flow mean and in short it's a cash flow that's available
1:16
to debt and equity holders after a company pays for everything it needs to continue operating so things like its operating expenses capital expenditures etc so let's take a look at the excel file and starting off with the revenue revenue line up top for this one it's going to be equals to the previous year's revenue so this figure over here multiply that by in brackets 1 plus the growth rate that we have projected for this estimated year which if you go down under the
1:43
assumptions it's going to be this figure right up here close those brackets hit enter and from here if you want to drag it across you're just going to select it the shift and right arrow and then press ctrl r that's going to drag it across for you you're probably wondering where we got these assumptions from these are basically simplified estimates that we made based on previous figures
2:03
if you wanted to make the revenue projections more sophisticated you would basically model out each line item so for example if a car company has three different car models you would model out each of the cars and try to see what kind of quantity you're expecting to sell at what price in the future and eventually find the sum of those to get to the revenue if you look at the other assumptions down below for the cogs as a percentage of revenue it's simply the average of
2:26
that press the f2 key there to take a look at it and we just projected that average out into the future and same thing with sgna down below so let's look into those for the cogs it's simply going to be equals to the revenue multiplied by control down arrow all the way to the bottom here the percentage here and we're going to drag that across as well so shift right arrow and ctrl r and for the gross profit it's just going to be one minus the other so we'll go equals
2:51
this figure minus this one hit enter and again we're just going to drag that across
2:56
same thing goes for sgna down below so it's going to be revenue times control down arrow this figure over here and then we're just going to drag that across ctrl r and then for total operating expenses well we only really have this one so it's going to be equals to this figure and again we'll drag that ctrl r and then for the ebitda it's going to be equals to the gross profit minus the total operating expenses hit enter there and we'll drag that across as well now that takes
3:23
us all the way to ebitda and now to calculate depreciation and amortization we're actually going to do that in a different tab called the fixed assets so go to control page down for that using this table we're going to be able to calculate both the dna as well as the capital expenditures which we'll need later as well and down over here we've got the different set of assumptions which basically are like things like the average based on the previous years and over here we just left
3:46
at a conservative 15 this one seemed a bit of an outlier at 25 percent so let's go ahead and go about that for beginning ppe it's just going to be equals to the ending pp of the previous year then for dna we'll go to equals and it says dna as a percentage of beginning ppe so it's going to be this figure over here and we're going to multiply that by beginning p p and e hit enter there and
4:09
for copics it's going to be the same concept so it's going to be this figure over here and we're going to multiply that by the beginning pp and e from there we're just going to go to equals it's going to be beginning pp and e minus depreciation and amortization because that dilutes the value plus capex because that's actually increasing the value of pve and then that's going to be it hit enter there and now to drag this across let's just go to the very top one control shift down
4:35
shift right arrow and press control r great now we can go back to the free cash flow tab by going to control control page up and over here we can start filling in depreciation and amortization this one's going to be for 2022 so we'll go equals control page down and it's going to be this figure right over here hit enter then we'll just drag that shift right arrow and ctrl r then this is just one minus the other this one minus this one hit enter there and then control
5:02
r and for operating and operating taxes if you scroll down all the way to the bottom you'll see over here that we have tax as a percentage of ebay so that's the figure we'll be using this 21 percent is typically the corporate tax rate in the us so we'll use that equals ebit multiplied by the tax rate so it's going to be this figure here and then again control r and down over here it's just gonna be equals to one minus the other hit enter there that's it takes us to notepad control
5:29
r again then for the depreciation and amortization here we need to add it back but before we get into that let me show you the whole formula for the free cash flow it's going to be ebit minus taxes plus depreciation and amortization that's because they're non-cash expenses the reason it's at the back is because there's no real cash outflow so the cash isn't actually leaving the company
5:52
instead this is actually an accounting term which essentially says that you should allocate the cost of the asset over its useful life so if it's a 10-year life you will be allocated throughout those 10 years but that doesn't necessarily mean that the cash is actually coming in or out because it already happened at the time of the transaction so that's why you actually end up adding it back
6:11
then you subtract capital expenditures or copics and this is obviously a cash outflow because it's something like buying a new factory and lastly an increase in non-cash working capital is subtracted while a decrease in non-cash working capital would be added in short non-cash working capital is the capital that the company uses in its day-to-day operations the formula for this is current
6:34
assets minus cash minus current liabilities so if non-cash working capital is increasing that means that the company's assets are going up but for that that means that it's actually spending cash so a company's cash flow is decreasing hence the negative sign in front now that we have an idea of the formula let's go about filling in the different line items so for depreciation and amortization it's just going to be equals to control page down under fixed assets it's going
7:01
to be this figure right over here hit enter there and then ctrl r for copics is going to be equals to control page down we're going to select the copics right over here select this one and again ctrl r now for a change in networking capital we're gonna have to calculate the networking capital first so the current assets and the current liabilities to do so we'll go to the networking capital tab so control page down control page down again and this is where we're
7:27
gonna be working on this so in this tab to save time we went ahead and put all of the different line items so we've got all of these that are that are going to reach current assets and down below we have all the ones that are going to reach current liabilities if you go lower down these are all of the different assumptions that we need to make in order to then be able to project all of this into the future more accurately so first off starting off with the daily sales outstanding
7:51
to briefly define the day sales outstanding it's the number of days it takes a company to collect payment on a sale so let's go ahead and calculate that it's going to be equals to the accounts receivable it's going to be this figure over here divided by the revenue which is going to be right up over here and then we're going to multiply that by 360. enter there and that's going to give us 4.2 this is num in number of days so it takes them 4.4.2 days to collect payment on a sale if
8:20
you just copy and paste that down press f2 there to see if that's working all right we have the days inventory outstanding and this basically says for how many days does our inventory stay in the warehouse before it gets sold obviously the sooner it goes out and gets sold the better for us as it costs money to leave things in a warehouse now depending on the industry this number is going to
8:41
vary quite a lot for example if you sell something like luxury cars and they probably stay in your warehouse for quite a long time as opposed to something perishable like what might be groceries same thing down over here for this pay while outstanding it's going to be equals to accounts payable divided by the cogs and then multiply by 360. hit enter there and that's going to be around 32 days so this basically says that it takes us as a company 32 days to go ahead and pay for our
9:09
bills obviously the longer time span the better here and we can go ahead and drag these out so we'll select all three of them shift right arrow shift down arrow sorry and then shift right arrow all the way till 2021 and press ctrl r now you can see that these figures are actually staying fairly consistent so for the future estimates we're just going to take the average so we'll go average
9:29
select all of these over here hit enter there and same thing for the ones below so press shift down arrow and then ctrl d that's gonna do it for us and then we're just gonna link them like so
9:41
and that should be good nice now that we've calculated these three ratios let's go ahead and forecast all of the figures up over here so firstly for accounts receivables we already calculated the deal sales outstanding so that's what we'll use to project it out into the future so we'll go equals then it's going to be the day sales outstanding times the revenue figure which is going to be this one up over here and then we're going to have to divide that by 360. the
10:08
reason for that is because here it's a number of days and we want to convert them back to regular numbers hit enter there then the next one is going to be the inventory which is going to be the merchandise inventory right over here so it's going to be the same concept here or just copy ctrl c ctrl v press the f2 key to make sure everything is making sense and that is indeed the case nice then we'll just select these two shift right arrow and press ctrl r to drag them across
10:36
and the last one here is accounts payable which is going to be the um this payable outstanding right over here multiply that by the cogs which is going to be right over here then again we're going to have to divide by 360 hit enter there and we'll just drag that across shift right arrow ctrl r great now we need to work on all these other line items so other current assets as well as these ones down over here now to do so we can go all the way to the very bottom here and this
11:05
is where we'll make a few different assumptions so as you can see these are all actually a percentage of revenue so that means that if we do it for one we should be able to just drag it across if we lock the relevant cells so we'll go equals then firstly we've got other current assets which is going to be this figure right over here and we're going to divide that by the revenue for the revenue you're going to want to press the f2 key not once but twice the f4 key sorry not once
11:32
but twice and now you want it the dollar sign only on the 18 that means that when you copy this across it's going to remain on this row but it's still going to be able to move sideways same thing goes for the accrued salaries as a percentage of revenue so that's going to be a good salary so it's going to be this one over here so we'll go equals then we're going to select the accurate salaries divide that by the revenue and again we'll press the f4 key twice
11:57
hit enter there now you can see that all the other ones are going to be in order here so we can just drag them down and just press ctrl d great and now all of these ones we can just drag across all the way till the last actual year and then press ctrl r nice and then for all of these because they don't really have big discrepancies we'll just go ahead and take the averages hit the average select these four hit enter then we're just going to drag that down as well press the ctrl d
12:26
great and then for all these ones we'll just link them ctrl c there and then just drag across ctrl v nice now we have all of these other estimates as well now that we have all of the relevant assumptions we can start plugging them up over here so first off for other current assets we'll go equals and this is going to be the other current assets right here multiply that by the revenue figure which is going to be this one right over here hit enter and then control r same thing
12:57
over here for accrued accrued salaries equals control down arrow all the way till we reach this one and then we're going to multiply that by the revenue key let's make sure to lock this one press the f4 key twice so the dollar sign is only on the number hit enter there then we should be able to just drag that across so go to control r then control d great now that's looking more like it so we've calculated the current assets and the current liabilities meaning that we can finally
13:25
get to the free cash flow so let's go back to the free cash flow tab by pressing ctrl up arrow
13:32
all the way up over here and so we're gonna plug the current assets so that's that equals to control page down all the way here and it's gonna be current assets for 2022 hit enter there same thing for current liabilities equals control page down and it's going to be this figure right over here hit enter then we're just going to drag these two across so press the ctrl r key great now for
13:55
networking capital this is going to be current assets minus current liabilities so it's equals to this one minus this other one right below it hit enter there and again we'll drag that across ctrl r and lastly for the change in networking capital press the f2 key there so you can see what it was like previously and it's basically the current years minus the previous years and we can just go ahead and copy that so we'll go shift right arrow and then ctrl r that's going to copy the
14:23
same formula to make sure let's just press the f2 key there and you can see that it's one minus the other one in the same way great now to find the unlevered free cash flow we're going to have to go to equals it says the it's going to start with this one here and then we're going to plus depreciation and amortization as it says right over here we're gonna minus capital expenditures as that's an outflow we're gonna minus the change in networking capital and then hit enter there
14:51
from here shift right arrow again and control r so we finally reached the free cash flow and i haven't quite yet talked about what the company is or what it does so let me know in the comments if you're able to guess out of all of these industry groups over here what industry this company could be in based on its financial statements especially look through their income statement balance sheet
15:11
and cash flow and try to guess that also look at the networking capital and all these ratios that we calculated that should give you a few hints let me know in the comment below alright now moving on to the discounted cash flow tab so go to control page down all the way to the dcf tab and first things first we're gonna have to plug the unlevered free cash flow up over here so we'll go equals control page up all the way to the first one and we'll select this first figure
15:37
over here hit enter and we just drag that across by pressing the ctrl r and because these values from 2022 to 2026 are actually in the future we're gonna need to discount them back using a discount rate and that discount rate is going to be the walk which takes us to the step 2 in our dcf process the walk is short for the weighted average cost of capital and it's basically the cost of financing for a company now this financing can come either in the form of debt through things
16:05
like bonds or in the form of equity through things like selling stock now both of these do come with a cost so there's the cost of debt and there's also the cost of equity that's associated with it and this is a formula for the walk i know it looks a bit daunting so let's go ahead and break it down first off you have the portion of equity which stands for the e there and then on the bottom
16:26
you have the total company value which is the equity plus the debt so here you're essentially getting the proportion then you're going to multiply that by the return on equity which is basically the cost of equity here and similarly on the debt side you've got the debt on top followed by the debt plus equity on the bottom times that by the cost of debt and over here you have this one minus t which is basically the one minus the tax three that's
16:49
because interest payments are tax deductible so that's why you leave that right over there
16:54
now that we understand the formula let's go ahead and apply it on excel so go to the walk tab right next to the dcf tab over here and up top we're going to have the equity proportion and we're also going to have the debt so this is the total debt value and the total equity value of the company if you want to see how we calculated the debt value press the f2 key there and this is basically from the balance sheet it's a current portion of long term debt and then the long-term debt
17:18
is the sum of those two then right below that we're gonna have everything to do with the cost of debt and then right over here we're gonna have everything to do with the cost of equity and finally we'll be able to derive the walk the weighted average cost of capital so first off for the cost of debt this is going to be equals to the interest expense so go to control page down all the way to the income statement and what we're going to want to select here is this interest
17:43
expense which is going to be this last figure in 2021 then we're going to divide that by control page up all the way to the walk the depth here and hit enter now we want this to be a positive sign so press the f2 key at the very front we're just going to put a negative sign and that's going to make it positive for us then for the debt over the plus equity it's a simple this one over here
18:05
divided by the sum of the debt plus the equity so these two up over here and close those brackets and hit enter there so the after tax cost of debt for us you can see we have a tax rate of 21 if you hover over all of these with red red um red buttons it's basically a comment that you can see right there so this is the us corporate tax rate for instance so for us the after tax cost of debt is going to be equals to the cost of that then we're going to multiply that by in brackets
18:34
1 minus the tax rate close those brackets and hit enter that should give you a 1.8 percent then we've got the cost of equity and this one's a bit trickier as you can see it's got more line items so to calculate the cost of equity you actually need to use what's known as the cup m which stands for the capital asset pricing model so let's look into the formula for that the formula here is the risk-free rate which is typically the 10-year u.s treasury which
18:58
is seen as the safest investment out there then you're gonna add the b which stands for the beta now the beta represents how volatile your stock is relative to the markets and the market has a beta of one meaning that if you have a beta of say two that means that when the market's up ten percent you're up twenty percent same thing goes when you're down obviously so it's just a lot more
19:20
volatile then you've got the expected return of the market minus the risk-free rate so let's look into this on the excel file so up top we have all of the relevant information that you might need we went ahead and gathered that from the internet so for the market race premium it's basically press the f2 key there it's basically the expected market return minus the risk-free rate that's what's known as the market risk premium and based on those let's first calculate the proportions
19:46
here so equals the equity value divided by the sum press the top key there of the debt plus the equity close those brackets and hit enter and then for the cost of equity we gotta do the kaplan formula we just mentioned earlier so it's a risk-free rate which as you can see it's 3.1 percent which is actually higher than it typically is mainly because of the current market conditions plus we're going to select the beta which in this case is 0.7 and we're going to multiply
20:15
that by in brackets the market risk premium which is basically the expected market return minus the risk-free rate close those brackets and hit enter there that should give you six for six point four percent from there we can go ahead and start calculating the walk so the walk formula is going to be equals to we already have the after tax cost of that so we'll go ahead and select that
20:36
multiply by the proportion of that which is this one and then plus the other side which is the cost of equity multiplied by the proportion of equity and hit enter there as you can see if you do the formula in several steps it just looks a bit less daunting and easier to understand so in our case it's at 6.2 percent so we'll go control page up and we'll just go ahead and plug the walk which
21:00
is this line over here equals control page down and we'll select the six point two percent uh that we just calculated here now that we calculated the whack which is going to stand as a discount rate we can go ahead and calculate the present value of the free cash flows so all the present value of all of these cash flows that we calculated which are for the estimated years so we'll go equals
21:22
then we'll select the unlevered free cash flow divide that by in brackets one plus and here we're gonna need the discount rate which is the walk we mentioned we'll press the f4 key once which is basically going to lock it for us close those brackets and we're gonna put that to the power of one in this case because it's a projection here of one there hit enter and then we'll go shift right arrow and then control r to make sure this is all right let's go to the very last one
21:49
press the f2 key and you can see that this is still locked on the same spot and we've moved all the way to the fifth year which makes sense and speaking of valuing a company if you're liking this video you can also check out our course where an investment banker financial analyst and myself teach everything we know about finance valuation and financial modelling on excel first we cover financial statement analysis using apple's real annual report as an example then we get into
22:19
financial modeling through a three statement model after that we begin the valuation phase where you learn to do a discounted cash flow a comparable company's valuation and a present transactions valuation on adobe looking at the real financial statements to eventually derive a valuation range lastly we'll show you how to present an investment thesis using a stock pitch format so if you're interested in checking it out go to the link in the description below alright back to video
22:51
moving on to step 3 which is calculating the terminal value and this is the value of the company beyond the forecasted period so in this case we forecasted for five years but the company doesn't just go bankrupt or disintegrate after that instead it probably has an ongoing life and so we want to calculate how much that's going to be worth there's a couple ways to calculate the terminal value but in our case we'll use the perpetuity growth method and here's the formula
23:16
for it we've got the free cash flow in year n which is the final year forecast and you multiply that by one plus the growth three which is that g there now this growth rate is typically the gdp growth of a country or the industry growth of a particular company and below that you have the whack minus the g for the growth rate again in our case if we look at the excel file you can see we've got a growth rate of 3 and so this is the industry standard growth rate that we went for
23:43
and right below we have the terminal value over here which we need to calculate so let's go ahead and apply the formula it's going to be equals to the free cash flow on the final year forecast so it's 20 26 for us then let's let's multiply that by the in brackets one plus the three percent over here close those brackets and then we're going to divide that by in brackets again we're going to have the walk so we can actually see it but it's the row 12 over here
24:09
minus the growth rate which is the three percent again close those brackets and hit enter currently the terminal value is in the future so this takes us to our fourth step which is discounting both the terminal value and the free cash flow so let's go about the terminal value first so it's going to be equals to the terminal value over here and then we're going to divide that by in brackets one plus
24:32
the discount rate which is going to be the walk for us close those brackets and we're going to put this to the power of the fifth one which is where we're at and then close the brackets there and then for the present value of the free cash flows we already have them discounted right up over here so we just need to go to equals sum and we're going to sum all of these the reason we can go ahead and sum them because they're already discounted nice now we get to the enterprise value
24:58
which is simply going to be equals to the present value of the terminal value plus the present value of the free cash flows and hit enter there all right now that we've reached the enterprise value we're getting into the last step which is calculating an implied share price now to do so we first need to know the equity value so let's look into that to reach the equity value you can see that we have these few line items that we need to fill first the cash the debt and the minority
25:24
interest depending on which company they might not have the minority interest so firstly for the cache it's just gonna be equals to under the balance sheet so go to control page down cache and cache equivalence and we're gonna select the most recent hit enter there then for the day we're gonna go equals and we actually already have this under the walk which is gonna be this figure right over here hit enter and lastly for minority interest we should find this one on the balance
25:49
sheet so control page down all the way to the balance sheet and if we go towards the bottom we should be able to find it let's see under equity non-controlling interest so that's the same thing we'll go over here select the 514 there and hit enter there so for the equity value it's going to be equals to the enterprise value plus the cash minus the debt minus the minority interest hit enter there and now to reach an implied share price we're going to have to use the
26:17
shares outstanding over here so it's going to be one divided by the other so it's a total equity value divided by the number of shares there are out there in the market that's going to give us the implied share price of 358 dollars great so we finally reached an implied share price but in order to do so we had to make a few different assumptions if we look into the excel file you'll notice we made a big assumption here on the growth rate and
26:40
we're right to change this to a two percent then the share price does change quite dramatically press ctrl z to go back the same thing would happen to the walk and so in order to combat this and try to see what it would feel like with a different type of growth rate say we will use this sensitivity table over here with the growth rate on one side and the walk on the other and see how that varies depending on whether this goes up by say 0.5 percent or down so to
27:04
do so for the growth rate here we're just gonna have to take the growth rate number here ctrl c and we're gonna have to paste it as a value it's important that we paste as a value go to alt h v v for that that's gonna paste as a value same thing goes for the walk ctrl c we'll paste it over here as a value alt h v and also we're going to have to link the share price so we'll go equals
27:28
and we'll link the implied share price over here hit enter and so we want to go in increments of 0.5 so we'll go equals 3 plus 0.5 percent hit enter there and ctrl c ctrl v same thing over here equals 3 plus 0.5 percent hit enter there sorry this should be minus f2 there minus 0.5 percent and same thing right over here and on this side it's the same concept let me fast forward that
27:57
nice now that we have this whole table laid out let's go ahead and use one of excel tools to be able to auto populate this whole table so first we'll select everything ctrl shift down ctrl shift right from here we'll go under data what-if analysis and here we want to go under data table for the row input cell we're going to select this growth rate over here and then press the tab key for the column input cell we're going to want the walk select that one hit ok there
28:25
and then this should auto populate with all of the different share prices depending on how the growth rate changes or how the work changes as well now i realize that this valuation was a bit simplified but i hope you found it useful to learn more about different valuation methods go to this link over here to learn the comparable companies valuation or this other link over here to learn more about finance and valuation
28:47
hit that like and that subscribe if you liked it and i'll catch you in the next one
Here to help you learn
Sign in to keep a private conversation with your course tutor.
Ask me anything about the material…
Sign in to chat