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