Discounted Cash Flow Model | Quickly Value a Business - YouTube

hi everyone eric here in this video we're  going to build a private equity style  

discounted cash flow model to value a company  with a bonus sensitivity analysis at the end okay  

let's get started so the point of this model  is we need to figure out what do we think the  

future cash flow of this business is and what do  we think those future cash flows are worth today  

and for the purpose of this analysis the best cash  flow metric we can use is unlevered free cash flow  

and this is basically the cash flow that we will  have available to us as owners if we want to pay  

down debt or pay ourselves a dividend but after  we've paid for all of the expenses of the company  

so really what cash is available to us so in order  to calculate this you need a couple metrics that  

you can see here all of these metrics you could  easily pull out of a three statement financial  

model that's the income statement balance sheet  cash flow statement if you don't know how to  

build one of those i'll include a link in the  description below i uploaded a video on how to  

build from scratch an entire three statement  financial model it's on my channel anyway  

so if you had one of those models you could  pull these metrics out you would have ebitda  

which we'll use for the terminal value you have  ebit which is your operating profit and so to get  

from ebit to unlevered free cash flow you start  with your operating profit on the income statement  

then you need to subtract taxes we know we're  going to have to pay taxes that's going to come  

out of our cash flow absolutely then we actually  add back depreciation and amortization why because  

this is a non-cash expense we put it on the income  statement as part of the accounting matching  

principles but it's not cash out the door it's  just something that we do because of accounting  

rules so this cash is actually something we  need to add back because we still have the cash  

then we need to adjust for changes in net working  capital which is current assets minus current  

liabilities so these are cash changes in the short  term assets and liabilities on our balance sheet  

and finally we need to subtract capital  expenditures capex so this is any assets  

we bought because if we buy an asset that cash  is gone so after we've done all that we have 334  

thousand dollars and let's say this is year one  this is actual financials and then everything  

from 2024 forward are projections and so we can  just you know apply a little color to that just to  

make sure we don't forget and so we're trying to  estimate the value of these cash flows but there's  

one more thing we need to do at 2028 we're going  to assume that we're going to sell the company so  

what do we think the company is worth in that year  well there's two ways to calculate this number  

and this number is the is basically called the  terminal value it's the future value of the  

company at a future date so the first method is  the ebitda multiple method so for typical private  

equity backed companies that generate significant  ebitda maybe grow 10 20 30 percent a year this is  

going to work and so let's say companies like  this one are selling out in the market for 10  

times ebitda so we're going to put in basically a  market multiple because we're saying okay in 2028  

we think this company will be worth 10 times its  own ebitda so we take ebitda of the company here  

and we multiply it by 10 so that gives us 14.9  million dollars so that's one way the second  

way is this something called the perpetual  growth method it's worth taking a look at  

but probably less accurate and definitely less  used in the real world and in for this method  

we're going to need basically two metrics one  is a metric called the terminal growth rate  

and so the terminal growth rate is what  we think the company is going to grow at  

in the future forever in perpetuity that's  why they call it perpetual growth and so if  

the economy is growing three percent in in the  country that you're doing this deal in we're  

always going to assume that the terminal growth  rate is less than the economic growth rate so if  

it's in the u.s or europe or something it's  going to be it's going to be like 2 percent  

and it could be less or it could be more and  that's one of the reasons if you change this  

number just a little bit the valuation gets thrown  way way off and that's one of the reasons why the  

perpetual growth method is a little bit wacky and  doesn't you know people don't have that much faith  

in it the other thing we need is a discount rate  which is the wacc and you could release you know  

you could do an entire semester on exactly how to  calculate this 10 is probably a reasonable number  

but it depends on how much debt and the equity the  company has what are your inflation expectations  

how risky the whole thing is you know what's the  opportunity cost of the money you're putting into  

it and and so there's a bunch of things but 10  percent should give us a reasonable number here  

so the formula is you take your unlevered free  cash flow and you divide it by your discount rate  

minus your terminal growth rate that's the formula  and you'll see that this gives us a value terminal  

value of 13.166 million a little under your  ebitda multiple in this case they're pretty  

close together but if ebitda multiple is like  really really high if it was like 20 times or  

50 times ebitda which that's the kind of valuation  you could see with a super super high growth tech  

company then you start to see that this perpetual  growth method it's not super informative and so  

you're gonna have to use something you see out  in the market actually to value the company  

so and this is the cash and the debt that the  company has so for now let's figure out how to  

value the company based on the net present value  of its future cash flows so we have our unlevered  

free cash flow and i'm just creating a  second line here because we need to add  

our terminal value to year five year  five we need to put them in the same year  

and now we will have the net present value of  your cash flows so here you go here's the formula  

you take your cash flow so we're one year in the  future and you divide it by one plus the discount  

rate let's lock the discount rate i hit f4 to  do it raised to the power of in this case one  

because you're only one year in the future and  to do this automatically i'm going to take 2024  

and i'm going to subtract 2023 and lock  2023 so what does this give us 1 divided by  

110 percent to the power of 1. so you can see  this gives us a net present value of four hundred  

thousand nine hundred and nine dollars for this  cash flow in the future so what about your two  

in this year you see that we're raising the  denominator to the power of two 2025 minus 2023 so  

in this row we can calculate the net  present value of all these cash flows  

now the only thing we need to do is add up  the all of these different cash flows and so  

the sum of all of these is actually  called the enterprise value  

you know the net present value the whole thing  but it's called the enterprise value and we're  

going to talk just in one moment here about  whether that's the number that we want so  

the enterprise value if we just add up these  cash flows is going to be 11.8 million dollars  

there is also a formula to do this so you  could skip a step just for your information  

and in this case it's called the npv formula  not surprisingly so you open the parentheses  

you link in the discount rate it tells you  right here rate then comma and then you grab  

the unlevered free cash flow so it's going  to discount all these free cash flows for us  

so if we close the parentheses you'll see  we get the exact same number because they  

do the exact same thing mathematically so that's  just for your information do it however you want  

and the thing that we're really after here we  have the enterprise value but enterprise value  

doesn't take into account what's on the balance  sheet and so we need to make some adjustments  

based on what the company owns and what the  company owes because we're trying to get an  

equity value because we're trying to buy the  equity of the company or figure out you know  

what is the equity value of the company so the  basic formula is you take your enterprise value  

the value of its cash flows and you add any  cash the company has to it and you subtract any  

debt the company has so in this case the equity  value is higher than the enterprise value why  

well when you buy a company if it has the cash on  the balance sheet you're going to get that cash  

too so you should increase the price by that  cash because you're just going to get the cash  

right back but you also assume any debts that the  company has so you should be decreasing the value  

of the company by what the company actually owes  because now you're going to owe that as the owner  

so this gets us the equity value this is  the discounted cash flow valuation of the  

company 12.3 million dollars so now we want  to look at one other thing we're going to  

run a sensitivity analysis data table to see how  the discounted cash flow valuation changes when  

we change some of the assumptions of our model  right now you'll see what we're going to do so  

on this side let's look at the discount rate and  so we used a discount rate of 10 percent right  

well what if we used uh slightly higher and  lower discount rates so let's say we had 10  

8 percent 9 percent 10 percent 11 percent 12  percent maybe we calculated it incorrectly and  

with these models it's it's not a science it's  you need to be aware of the market you need to  

look at a range of assumptions to make sure that  you're you know giving a valuation that actually  

makes sense and let's say on the other side let's  actually move this over one cell and say on the  

other side you want to look at different exit  multiples because let's say today this kind of  

company that we're looking at trades at you know  10 times ebitda but in the past this in this  

sector companies have sold at seven times and  they've sold at you know all kinds of different  

ebitdas so let's say we want to look at eight  times ebitda nine ten eleven twelve thirteen  

okay so here's how you set this up first let's  just put a little border on the table so i'll  

just put some little borders so you can see what  is going on i'm going to link in the equity value  

so with these tables you need to basically put  hard-coded assumptions along the top and left  

and then you need to link in with a formula equals  and you need to link the metric from your model  

now you need to highlight this whole section with  the assumptions and with the formula go to the  

data tab now what-if analysis and data table  so the row inputs what inputs are in this row  

discount rate so we need to click discount rate  now column inputs what inputs are in the column  

that is the ebitda exit multiple now click ok so  what you'll see here in the center of the table  

if i just format it a little more nicely are  different equity values at these combinations  

of assumptions so let's say the discount rate is  11 percent and the exit multiple is eight times  

ebitda well in that case your equity value is  only about 10 million and just so you can see  

if we change this to 11 this is 10.0 million if we  change this to 11 percent and we change the ebitda  

multiple to 8 you'll see that this is the same  number 10 million so this allows us to look at all  

of these combinations of assumptions in this table  or what if the discount rate is 12 percent and  

but the exit multiple is even higher and so then  you get sort of a sense of what is possible this  

company you know within these variations what is  a reasonable price what is a reasonable valuation  

so that's all for this video i hope you're feeling  really confident now about basically creating  

a discounted cash flow model if you like this  video and want more hit the like button right now  

let me know in the comments subscribe to my  channel i'll be releasing more videos like this  

and if you want to improve your excel skills  check out some of my microsoft excel training  

courses below in the description thank you for  your time and i'll see you in the next video