Today I'll be going over some advanced DCF topics and building out a model with
you in real time in order to help you become a better and smarter investor
What's up everyone and welcome to rareliquid a channel where I draw from my
experiences as a JP Morgan investment banking analyst to cover
tech investing and career advice. As a friendly reminder for those of you who
are new to the channel on weekdays is when I post about stocks
and crypto and tech investing in general and on weekends is where I post about
career advice. With that said, today's agenda looks like this:
I'll first show you how to build multiple scenarios in a DCF,
then I'll show you how to build out sensitivity tables, and lastly how to
calculate your WACC In case you're unfamiliar with what a
discounted cash flow or DCF analysis is, then please check out this video
which I posted a few days earlier It's kind of more of a DCF for beginners
type video Throughout this video I will be building
out more of the Tesla DCF that I built in that previous video that I just
mentioned, but please note that all the numbers
you're gonna see are not really what I believe to be the future projections for
Tesla- they're really just for illustrative
purposes and this entire video is really for you to see how to build out a more
kind of advanced complex DCF Alright so first up let's talk about
multiple scenarios The DCF is a really great tool because
it's really flexible you can add your own assumptions into it to build out
your valuation but, of course, that also leads to a lot
of potential room for error in case your assumptions are not correct
This is why it's really important to make sure that your model is really
flexible and so basically what a lot of bankers and analysts and investors do
is they create multiple scenarios for their key assumptions and
you're able to toggle through them and I'll kind of show you what I mean
throughout the next few moments in this video where I actually build out
multiple scenarios in the DCF and kind of walk you through how to do that
I'll be providing some commentary as you see me working through excel
and please also note that I do make some mistakes here and there as I'm modeling
you know it happens to everyone and so please be a little bit
forgiving if I kind of mess up here and there but
you know it happens on the job too you can't just build an entire model
perfectly from start finish but with that said let's jump
straight into the model Okay so here's the model and what I'm
first going to be doing is creating a case toggle
the toggle that I mentioned earlier and you'll see that I'm just working
through excel right now building out everything
what I'm doing right now here is creating a conservative case,
base case, and optimistic case, and you'll kind of see how everything
will be flexible for you to be able to toggle through
So what I'm doing on right now is I'm trying to make sure that everything is
formatted nicely kind of something that I picked up in
banking and just can't get let go of to be honest. What you see right now
the shortcut I'm using is f2 on it doesn't work for macs only for
pcs; the f2 button lets you basically see what cells are being used in that cell
that you're currently on
and I what I did was I created an offset function
and if you're not sure what offset does then
you know I'd actually suggest probably googling it but basically it lets you
toggle between cases like you see here and it refers to
some of the cells below. So next up, let's just basically do the same
thing that I did for revenue but except for EBIT
and basically what you're going to see here is me
creating multiple scenarios where in the conservative case
things like the revenue growth grows a lot
more quickly down to three percent in year 2030 and so in the optimistic case,
the revenue growth is slowing down to 13% so
obviously a little bit more optimistic than the growth rates are higher for
something like your EBIT margins as you kind of see here
the more conservative you are, then the lower your
EBIT margins and the more optimistic you are, then the higher those margins
And very similarly for your tax rates, a lower tax rate is going to be
something that makes you more optimistic because you're
paying less in taxes and then a conservative case is where
the tax rates will be higher. So basically right now, I'm just building
that out so very very similar process to what I did in the previous line so a lot
of copy pasting but each section is still a little bit
different so here, what I'm going to be doing is
making my conservative case 21% base case 25% optimistic case 30%
Now that I'm looking at it actually I did realize that
your optimistic case should have a lower tax rate so my
my bad on that- the conservative conservative case and optimistic cases
here should be switched So next up we'll be looking at
depreciation and amortization and basically what you see here is going
to be pretty similar- just building out the three cases
and for depreciation and amortization also known as D&A
you make it as a percentage of CapEx or you can also make it a percentage of
sales and you make it a percentage of CapEx or I
prefer to at least just because the two concepts are so closely related if
you're spending more on CapEx then, you should
be getting more depreciation because CapEx is really just what you're
spending on on PP&E and that PP&E depreciates over time
usually at a pretty steady rate, so that's why
I prefer to just make D&A a percentage of CapEx
by the way, if this is all kind of boring to you I don't know if this is
interesting or not, then definitely like you know feel
free to just skip to other parts of the video
we're almost done here, just going to be building out CapEx and then changing
that working capital and then we'll move on kind of to the next part
but again just copy pasting here on the left
hand side you can see that I made a bit of a mistake with the
cases but I'll fix that in a bit. And for CapEx, the assumption that I'm using is
just the average over the past few years usually CapEx also D&A tends to be
relatively stable especially as the company becomes much more mature
Tesla obviously is growing a lot and so this assumption I would say needs a
lot more work, just because if you have been following
Tesla, they're building things out like the gigafactories all over the world
and they're trying to really really push production
by building out a lot of factories, right? So for capital expenditures
for Tesla it probably is gonna vary a lot
time to time depending on what they're really building
as you can see over the past three years for example, in
20 was that 17 it was 10% 18 it was I don't know 2018 it was 10%
percent then 5 percent the following year then 10%
again the next year but anyway now we're looking at your
change in networking capital most often, it's just understood as
your current assets minus current liabilities but
for the DCF it's actually your current operating assets minus your current
operating liabilities, and what this just basically means is
your current assets excluding your cash and cash equivalents and for
operating liabilities, it just excludes debt
Really just focuses on you know as you're operating the company what are
the current assets and liabilities that I have
to really like fund the business and who like who am I paying to make sure that
um I'm able to consistently push out my products or services
And so normally you're changing networking capital is actually a cash
outflow for but in Tesla's case it's actually a cash
inflow- this is really odd, I think for Tesla
it's a very good thing though because basically when I looked through their
financial statements I saw that they had a lot of like deferred revenue and
people paying them in advance basically to
purchase the car probably because there's a lot of
demand and so because people are paying them
in advance, this is a good thing for Tesla because they basically have that
cash to fund their business as long as they
can deliver the products to their customers
then they get that cash and can basically kind of use it
without raising additional money for that whatever portion that they get for
their operating working capital I mean. The
company still of course has to raise cash through debt or equity to
fund their overall business but I'm just saying a very very small
percentage they don't need to raise because people are
buying their cars in advance or paying in advance
But as you see here basically we just kind of built out the
cases for the change in networking capital and now I will be just for the
optimistic case saying okay let's say that it
goes slowly down to negative two percent and then
for the conservative case, let's say it grows to two percent, I had some issues
with the formatting here and this is something that
you know happens often when you're modeling-
you're trying to think about what are the shortcuts again that I need to use
or why is this not working and basically you're seeing me in real time
just kind of struggling to to make certain changes
But I think we're almost there so the change in networking
capital is almost done basically just changing a few more
formatting again changing this to let's see five percent two percent
and there we go we have our unlevered free cash flow now and the entire model
is flexible so I'm just doing a kind of final like
sweep through everything and um that's that's the model. One more
thing I did want to make flexible is our WACC and terminal growth rate
and as you can see here I use the choose
function instead of the offset function- basically the choose function
lets you it doesn't really matter where the cells are
in your list or the ones that you're trying to make flexible
um for the offset function, for example, you see that
basically it selects from below our reference cell
but for the choose function it can kind of be anywhere in the entire spreadsheet
as long as you're kind of selecting that cell in the formula when you're building
it. Again, if you do want to learn more about choose or
offset functions and all that I would recommend
googling it because I can't really go through it right now I guess
but basically what you're just seeing here is me making
the WACC and terminal growth rates flexible based on our cases and as you
can see as you toggle through them everything
will change in the model. Alright, so I don't know if that was super boring or I
don't even know how many of you guys are going to watch this please do let me
know in the comments if this was helpful at all because
I think a lot of my instructional videos like this- the educational type ones
don't seem to be as popular as like the deep dives I do on like crypto
assets or companies like stock reviews and stuff like that so
please let me know in the comments below if you found it interesting or if
there's anything further that would be helpful maybe to
adjust your you know whatever is on your mind. But next up, let's talk about
sensitivity tables as I mentioned before the DCF being so
flexible and having a ton of assumptions, it's just really helpful to see and
visualize what your assumptions are and how they
would change your ultimate outcome which usually for
your DCF is something like your share price,
equity value, or enterprise value and so the way to do this is through something
called sensitivity tables and this is one of those things that it'll
just be way easier if I show you so let's again dive into the model and I'll
be providing some commentary as I build out these tables
Alright so I'm gonna start from the very beginning which is really just
building out a header- I just usually what I do is
copy paste from wherever else in the excel sheet that
has some kind of formatting that I like and so that's what you're really just
seeing here and so what I'm going to do is just
build out one sensitivity table normally you can get a lot of them but I'm
linking it to the share price, so that's what we're going to be using to see
basically as the assumptions change, what will our share price for Tesla be, that's
basically what we're trying to see here and on this left hand column is where
I'm going to be making our WACC
I'm basically building out the WACC part of the
sensitivity table basically I'm showing a range from
okay what will our share price be from 12% to 16%
and on the top row here what you're seeing is
the sensitivity for our terminal growth rate and we're gonna be looking at what
is our share price if we have a growth rate terminal growth
rate of two percent to three percent so what you wanna do is go to the
data tab and then what if analysis and then there's something called data
tables. For the row, you want to select whatever or in our case I'm sorry
usually there's like a pop-up but with the screen
recorder thing that I'm doing right now it doesn't really show it but
again something that you could kind of google but the important thing really
here is what you're seeing here is the data
table where okay let's say our terminal growth
rate is two percent to three percent our WACC is 12%
to 16%, how exactly does our share price for Tesla change?
And this is really helpful because then you don't have to toggle through
you know five ten different cases to see what certain or how your model exactly
will be affect your share price- you can just
create a data table and then kind of think about okay if I believe
that Tesla for instance can have a terminal growth rate from two to
three percent or they deserve a WACC of 12% to 16%,
what are the different share prices in each of these different scenarios?
So that's really to the whole essence of the data table I'm just formatting it
here to make it look a little bit nicer and you can
really do this for anything- you can also build it up for things like
okay what do I have to believe for our revenue growth or EBIT margins
to believe that our enterprise value needs to be a certain
number or our equity value share price etc etc
I used to build sensitivities tables all the time during my time at JP Morgan and
it's really just one of those things that clients love to look at because it
shows so many different scenarios and I guess it makes me look a little
bit more analytical but really the important thing is
because your assumptions are so important in the DCF and
if you only had one assumption and didn't really look at the entire range
of possibilities then you're probably going to be wrong
because what are the chances that you chose the exact
correct revenue growth or terminal value or WACC right?
You're not really sure 100% what that number is going to be
but if you have a certain range then you can kind of see okay if I have
this range of potential outcomes and this is what I believe and these are our
share prices then you kind of feel comfortable about what
share price you might want to enter a stock in
Next up, let me show you how to calculate WACC for your DCFs
If you recall the formula for WACC is percentage of equity times cost of
equity plus percentage of debt times cost of
debt times one minus your tax rate You know when I first saw this formula I
was pretty confused by it so let me just show you and walk you through again
me going through how I find each of these assumptions and how to calculate
your WACC I guess a little bit from start finish
Before jumping straight into the model you're gonna see that there are some
assumptions already in there and so basically first off, let me just show you
how to find four of the key assumptions that are in there
for calculating your WACC First up is your risk free rate which a
lot of people and investors just use your 10-year
treasury rate and you can just google this
and it's pretty easy to find online. And for your beta,
you can find this on yahoo finance just look up whatever company you're looking
at for Tesla it's 1.98 And for the average annual return for
the S&P, this is what we're using for our annual average return for the or for
equity and then you're going to also see your
cost of that for this one is probably the most trickiest
but what I'm basically just using is an average of
Tesla's lowest and highest interest rates for the notes
Alright so we're in our WACC tab of our model and the first thing I'm doing
is linking to our equity value from our model, and then
connecting to our debt as you can see here,
and for our tax rate I'm just assuming 21%
for percentage of equity just really simple- equity value divided by equity
value plus debt For percentage of that your debt
divided by your equity value plus that and that gets you both of those and next
up let's just go to our cost of equity which
is your risk free rate plus your beta times your equity market return minus
your risk-free rate And so for Tesla, we're getting around
14.3 percent cost of debt- already covered how to get
that- and then we get to our WACC which is our percentage of equity
times your cost of equity plus your percentage of debt times your cost of
debt times one minus the tax rate. And from
there, we get a WACC of thirteen point
a little you know as I mentioned in previous videos 6 to
around 15% is for most companies and it makes sense that Tesla's around 14%
because it's a bit of a risky company Alright, so those are the three topics
that I wanted to go over with you guys for
more advanced DCF kind of concepts and I'd say that I really just covered the
tip of the iceberg really because there's just so much more detail you can
add to your DCF models- things like quarterly models instead of
annual like you saw in the video right now and there's
things like building out really detailed revenue builds
During my time at JP Morgan for example I had to start from the US population for
biotech companies that I was looking at and project out the US
population for the next let's say 10 to 20 30 years
and then figure out how many patients would be interested in a certain drug
how much they would pay to ultimately get to our revenue things
like that you know there's a lot of assumptions that are going in there and
you make each of those assumptions also flexible so the entire model can
get pretty you know a lot more complex- let me know
again in the comments below if things like that
or tutorials would be interesting because you know I really just want to
do the videos that are kind of what you guys are most
interested in and later on in some of the videos for stock
reviews, I'll probably have kind of stuff like that so you guys can probably see
that as well but yeah let me know what you guys
would be interested in. I also do get a lot of requests for the models that I
built but they're available to my patreon so unfortunately
I'm sorry but I can't send it out to people for free because some people pay
for it check out my terabytes tier in patreon
in case you are interested and I do also have a monthly Q&A
with gigabyte and terabyte patrons every month and
I think for the next zoom call what I'll be doing is building out a DCF model
from scratch so in case you are interested again, you
know just feel free to check out my patreon
and the other thing I want to also just mention is as you probably know
if you have been following my channel I award ten dollars to the first
commenter and then also to a random comment so
here are our winners today if that's you just they me on instagram
so you can collect your winnings. The last thing I want to mention is if you
want some free money then check out the different exchanges and
links that I have below. If you sign up for Coinbase for example you can get $10
free in bitcoin you can also get free stocks if you sign up on Weeble
or Moomoo and yeah if you want some free money check those out
that said as always thank you so much for watching hope to catch you in the
next video thanks so much and peace out
[Music]