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]