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]