Build A Full Discounted Cash Flow Model for a REAL Company

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 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 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 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 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 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 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 this figure minus this one hit enter and  again we're just going to drag that across 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 get to the free cash flow so let's go back to  the free cash flow tab by pressing ctrl up arrow 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 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 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 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 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 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 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 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 because interest payments are tax deductible  so that's why you leave that right over there 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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.