Valuation Modeling: Excel as a tool

hi welcome back in this session I'm going to break the mold not talk about big ideas or companies but about how to use an Excel spreadsheet I've created on valuation before I go further though there's nothing magical about the spreadsheet there are lots of spreadsheets out there which are much more powerful have a lot more stuff in them but this is the spreadsheet I use in valueing companies and I thought I'd take you through the process of how I enter the inputs in in fact if you look at if you if you sat on my classes you do know that I you know that I don't spend much time in fact I almost never open an Excel spreadsheet in class or talk about equations and this gives me a chance to get into the mud and talk about the details so before I start a couple of things about the spreadsheet that I'm going to give you a link to it's called fcff Simple Ginsu now there's a reason for each part of that it's an fcff model it's a free cash flow to the firm model we trying to Value the entire business as opposed to what you can value just the equity in a business there's a different variation of this model on my spreadsheet called the FCF simple Ginsu model that's the value company based on free cash flows Equity this is about valuing the entire business and this is the spreadsheet I drawn almost know 90% of the time when I value a company it is for non-financial service companies and you might say well what do I do with the financial service companies that's a tale for another day there's a third spreadsheet just for financial service companies but in this one I want to focus on valuing a business let me talk a little bit about the simple and the Gino my objective when I value companies is to keep things parsimonious I don't always succeed because I keep adding details and at the end of the process said that was complex but I'm going to try to keep it as simple as I can while not giving up the rigor of what you need in valuation say what's Ginsu might give away my age but I've first moved to the United States one of the first things I watched on TV was an infomercial what's an infomercial late at night know broadcasting companies didn't have much to show so you could buy up 30 minutes from a from a major TV station and show something about a product 30 minutes in a product and I remember the second day I was in the US I turn on the TV and there's a commercial for Ginsu Knives and here's how it goes there's a Japanese chef and he has a Ginsu knife and he says this is an amazing Ginsu knife if you pay $19.99 you'll get this knife and then over the next 29 minutes he throws in enough knives To Be A Serial Killer by the time you're done you've got 25 different variations all in that package and he say what's that got to do with the spreadsheet as I said I started the spreadsheet keeping it simple and at each stage in the process I would say wouldn't it be neat if I could add that if I can add a worksheet that valued an employe employee options for me add a worksheet that converts R&D into a capital asset you in other words the things you run into evaluation we have to keep leaving your spreadsheet to do them I said would we need to had them so if you look at the spreadsheet that I'm going to show you in a couple of minutes it's got multiple worksheets think of those as the Ginsu Knives you get them all and you get them all at no cost now so this is the version the version I'm going to talk about in this piece is the 2024 version I update these spreadsheets at least twice a year sometimes more this is the January 2024 version you're saying why would spreadsheets vary across time cuz one of the worksheets that I built in or a couple of the worksheets reflect data as of the start of 2024 data on industry averages data on Country risk premiums and it's built into the spreadsheet again to to to save you the trouble of leaving the spreadsheet to get that data now I want to caution you though before you start that I'm not an Excel ninja I don't like macros I don't think I've ever written a macro I don't even TR trust excel's functions like what I don't think I ever use the npv function in Excel you's saying why not maybe it's just because I'm old and I prefer to do this by hand but this isn't rocket science it takes me just about as much time to do a present value calculation by hand as to use the npv tool and I know exactly what I've done so what you going to see in these spreadsheets is very basic Excel no macros very few functions I do appreciate the power that Excel gives me to build models and more importantly to change inputs I used to remember when I used to Value companies by hand never to change input I would dread it because every single number had to be recalculated so I'm glad Excel is around but you know what I should be able to Value companies without access to Exel in fact as I built these spreadsheets here was my objective every single number in the spreadsheet I should be able to do manually not that I would want to but I should be able to do manually no magic boxes second the spreadsheet is my tool it's not the other way around and I'd strongly encourage you to keep that power you know power structure are going cuz I've seen too many cases where people work for models rather than the other way around and finally this is just my version of the spreadsheet feel free to bend it to your needs adapt it modify it and at the end of the process I want you to take ownership of what comes out of the spreadsheet put differently when you get a value for a company I don't want to hear the words the deoderant model value the company at because I know exactly what you're trying to do you're trying to blame me for what the model tells you so use my spreadsheet you're welcome to do it you don't have to pay me you don't even have to acknowledge me just make sure that at the end of the process this is your valuation finally some advice before we start on what you need before you open the spreadsheet first pick a company because there's no point opening the spreadsheet if you're not going to look at a company it's too abstract pick a company and get at least one years of financial statements so you don't need much you don't need 10 20 30 years this isn't a historical data driven valuation if if you have 10 20 years and you want to look at them by all means do so but all I need is one annual report if that annual report is not something that came out right now you know the last two weeks last three weeks in the middle of a year then get at least one quarterly report one annual report one quarterly report you're all set to go you can get those either as annual reports or as 10ks but basically you want all of the financial statements and nice to have access to the footnote in terms of Market data only two things you need one is the current risk-free rate whatever currency you're working with and if you have no idea how to do that you might want to watch the session on risk free rates the second is what your current the company you've picked what the current stock prices the share count and the market cap of the company that should be online you can look it up and those are all the things you need to get started so I'm going to close up this this PowerPoint and go directly to the spreadsheet and take you through cell by cell what the spreadsheet is now before you get started though pie you know this is very important this spreadsheet has circular reasoning built into it it's not a bug it's a feature I need it to get some things done especially the option pricing so before you get started go to excel go to preferences I'm on a Mac it might be in a different place on a PC click on the calculation option and make sure the box next to use iterate of iterative calculations is checked off you can leave it at the default 100 is more than enough for what we do no no need for false Precision here so I'm going to take you through a valuation I did of Amazon just a couple of weeks ago this was in know early early February 2024 and the most recent 10K for Amazon and just come out is the 2023 10K and was a calendar year remember that not all companies have 10ks or annual reports at end on December 3 first this one ended on December 31st fresh off the press so that's going to be my core document that's pretty much what's going to drive almost every number here now as you as you get further through the year if I were doing a valuation of Amazon in October no November in addition to the 2023 10K I would print off the most recent 10q or the quarterly report which could be in September or June or March now first quarter second quarter third quarter so that's what I had right next to me and cell by cell I'm going to take you through what I did incidentally every single one of the input cells has a comment it's really long I'm sorry some of the comments go to you know 10 sentences but I was trying to clarify what I was asking you so with each of the cells if you have a question read the comment it might answer your question so let's let's start by identifying the two colors of cells you're going to see you're going to see yellow cells which for the most most part are input cells we enter the numbers and green cells which are computed cells where I prefer you don't override it if you do it's not the end end of the world but it'll also mean that that cell will never get recalculated again so the first thing I ask you is when are you're doing the valuation enter the day right that should be easy enough the name of the company again enter the name of the company so so far so good I don't think any anybody should have any issues so far then if you move to the first input it's a pull down menu so rather than try to enter the name of your country where your spelling of the country might be different than what's in my data set I give you a pull down menu and it should cover pretty much any country in the world you're in if you have a country that's not in the pull down menu let me know and I will add it on but it pretty much is so in this case Amazon and in the pull down menu pick the country in which your compan is incorporated now I know make a I make a big deal about risk coming not from where you're Incorporated but from where you do business for this for this part of the spreadsheet at least enter the country of Corporation later when we do cost to Capital and I'll take you to that worksheet I'll let you give me more Nuance on geography where your operations are the next two cells should be the same right the the name should be the same but I've given you both options just in case you decide to change your mind on US versus global I ask you what industry is the company again again don't try to enter the name of Industry this is a pull down menu just like the geography it's a pull down menu and if you pull down the menu it'll basically give you a list of industries that you can work with so in this case with Amazon you can see that there are a bunch of Industries and as you go through for your company you might not be quite sure make a choice especially if it's a multi- business company as to what you think it's biggest businesses guess what Amazon is in is not just in retail it's in entertainment it's in logistics it's in the cloud business but its biggest business is retail so I picked retail general for both the US and Europe and as I said most of the time you're going to pick the same then we get to revenues revenues I ask you for what your revenues are in the most recent 12 months with Amazon as I said I got lucky my most recent 12 months came out of an annual report so I'm pulling the revenues the most recent annual report across all their businesses again you might s in many businesses how do I pick one I'll give you a chance when you get to cost of capital to do it but enter your revenues from be most recent year incidentally before you get started make a decision on the units you're going to enter the numbers in what are you talking what am I talking about well you can enter your numbers in millions billions thousands even in actual dollars the one piece of advice I would give you and this is purely practical is you enter Amazon's numbers and dollars you you're going to have numbers that stretch across the entire cell 12 13 14 digits so I I would keep it Compact and here the numbers that you see are in thousands $574 million so basically you've got I'm sorry they're in million so it's $574 billion in revenues so that's for the most recent 12 months now while I'm on that Revenue line remember it's an annual report if you look at the the the next column it'll give you the annual the revenues in the most recent year I'm going to enter those revenues here so that is the last 10K before your last 12 months and because of an annual report that last 10K is the previous year now let me stop here and talk about what will be different if I were doing this valuation in November in November my most recent 12 months will be through September of 2024 through September of 2024 it'll be the last quarter of of 2023 in the first three quarters of 2024 if that is what I'm putting in my in my as my most recent 12 months the number I will enter as my last 10K will be the 2023 number so it'll be separated and this is the last sell year by 75 so in this case because of two I have an annual report it's one year since my last 10K I enter one if this was after the third quarter of 2024 it'll be 75 after the second quarter 0.5 first quarter 0.25 essentially let the spreadsheet know when you're Computing things like growth rates it's been only half a year since the last number this case so one year second input I ask you for is operating income now when you do valuation you have to get comfortable with the differences between different measures of income gross income operating income net income they're not all equivalent they're very different numbers I'm asking for operating income or earnings before interest in taxes so go down the income statement below the gross income line but above the net income line so before interest income interest expenses other income you usually see an operating income line item there again I enter the most recent year and the year before and again because it's annual report it's 2023 numbers for the most recent 12 months last 10K will be 2022 I do ask for interest expenses again it's a free cash to the firm you might say why do I care about interest expenses because I might needed to get a cost of debt so again ask for the interest expenses in the most recent year the most recent 12 months and in the last 10K those three items come off income statements now I want you to leave the income statement and go to your company's balance sheet in your balance sheet turn to the liability side of the balance sheet and look at the number called shareholders Equity this will include items like paid in capital I don't even know why they break it out because it's this tiny number that nobody ever cares about retained earnings and in addition you might have if a company does buy back treasury stock that offsets it but there'll be a shareholders equity line that is total shareholders Equity some companies separate from shareholders Equity an item called minority interest which is equity in a cross holding that you've Consolidated add those two numbers up for the most recent year and again for the for the year before that so basically you're looking at the most recent balance sheet and the and the balance sheet from the last 10K so in the in the case of Amazon this is the shareholders Equity at the end of 2023 and at the end of 2022 now I have to warn you that some of your companies you might find a shocking number which is your shareholders Equity is negative he's saying that must be a basket case company not necessarily I valued the Home Depot last week and it's Book value of equity shareholders Equity at the end of 2022 was actually a negative number why because if you buy back enough stock it's an accounting problem part of the reason you should be skeptical about Book value of equity measuring the value of equity in a company now right below it I ask for Book value of debt notice I don't break it out to short-term debt and long-term debt I want all interest bearing debt here which will include the long-term debt in your balance sheet it will also include in your current liabilities the short-term debt and the short-term portion of longterm debts three items and since 2019 for many companies you'll also see lease debt both in the long-term section and the shortterm add them all up for the most recent from the most recent balance sheet and the balance sheet from the mo from the last 10K before the last 12 months so those are the bulk of the numbers and then I ask you two questions and if you've never sat through my valuation class the safest thing to say is no to these questions but you've sat through my classes you know I make a big deal about how accountants are inconsistent on what on training Capital expenses operating expenses to me the definition of a capital expense is it creates benefits over many years R&D Capital expense customer acquisition cost for a platform company Capital expense so because that's going to skew your numbers and effective valuation I'm going to give you a chance to capitalize those numbers because accountants treat those numbers as operating expenses so if you want to capitalize R&D or customer acquisition cost or exploration cost or whatever you think is an item that should be capitalized it's right now being expressed enter enter yes but don't stop there see this worksheet that says R&D converter go in and enter the numbers for your company and these are the numbers you will need to capitalize R&D first you need to specify how long it takes for R&D to pay off in your company say I don't know what to do well to guide you a little bit if you look at the bottom of that of this worksheet I've given you a rough amortization period pharmaceutical company big energy companies it takes a long time between the time you invest and oil come out the ground or a drug is can be sold so enter the number of years so let's say in this case that the case of Amazon because it shouldn't take 10 years for Amazon's already to pay off I say it takes about 3 years enter the R&D expense for the most recent 12 months the last 12 months so in the case of Amazon that's 85.6 billion and then enter the R&D expenses for the last 3 years here's the problem with having only one annual report you can get R&D expenses for only two years in the case of Amazon I to go back and get a second and a third R&D report or have access to an online database that gave me the numbers so I have R&D expenses most recent 12 months AR expenses from the last year two years ago 3 years ago the rest I'll do I'll convert your R&D into an asset I will change your earnings so essentially I'll go through the kabuki dance of converting R&D into a capital asset right below that is another item that accountants used to routinely get wrong which is treating a financial expense as an operating expense I'm talking about leases until 2019 if you structured your leases in the right way accountants allowed you to treat as an operating expense and not treat it as debt retailers restaurant companies even some Airlines used that loophole to keep that off the books that was a terrible practice and in 2019 in in large segments of the world that was ended cuz both IFRS and GAP decided that they need to do the right thing that doesn't mean every company does it and even within the US and Europe there are Escape hatches companies use but if your company has capitalized leases and You' included in debt just leave it at no because you're trusting the accountants he saying but what if my company hasn't capitalized leases or I don't trust the accountants then change this no to a yes but if you do do that you have to go to the operating lease converter the worksheet and enter the numbers for your company what numbers you first have to enter the lease expense from this year which should be in your footnotes it should say operating lease expense from the most recent 12 months was 295 million the case of Amazon and if you stay in those lease Commit This will be a footnote if you stay in those footnotes it'll give you a table in the case of Amazon the table gives me lease commitments each year for the next 5 years and a lump sum for lease payments Beyond year five here's what I will do I will take those lease commitments I will discount them back to today using the cost of debt to convert to debt and I will take the lump sum and spread it out over time and convert that as well to come up with the debt value of leases this is what I've always done well before accountants gave to their senses I realized I could not value companies with significant lease commitments without doing this and in some cases I'll actually override the accounting lease debt but if I do that I have to make sure I don't double count and includeed as debt so this option is available for you even for companies where leasees a converted debt if you choose to override the accountant's lease debt and use your own calculations we're almost there with the inputs let's keep going then ask you for cash and marketable securities you you're in the balance sheet still because you got the shareholders Equity go to the asset side and you should see cash and marketable securities some companies bundle to them together so just one item the case of Amazon that's what they did in some case It's Tricky they might have short-term Investments as a separate line item apple is particularly weird they this they have this long-term Investments reflecting the fact that they had a trapped cash component for a long time but for most companies it's cash and marketable security should be a slam dunk again last most recent balance sheet and the balance sheet from the most recent 10K if you go below that it says cross Holdings and other non-operating assets this is tricky cuz some of you will be tempted to open up the balance sheet and start throwing in the kitchen sink things like Goodwill and no brand name don't do that here's the rule any asset that is generating cash flows for you already you've already counted Good Will is not an asset so I don't care brand name is what allows you to earn high operating income it's already counted so he saying what's not counted the most important item is cross Holdings in other companies what am I talking about if you own 5 10% of another company it's shown as a cross holding and that cross holding is what I'm asking for here is what is it worth and the reason I need that is when I do a traditional firm valuation based on operating income I haven't value those cross Holdings now when you look at those cross Holdings for your company it'll be on the asset side as long-term Investments usually you know be careful and go to the footnotes and check out what's in the long-term investor to make sure you're not double counting some companies Mark to market the rule in a accounting is if you hold it for trading you got to Mark Market soft Bank marks the market that's nice because you you have the market value cross Holdings but for most companies it'll be recorded at Book value now when you're hurry leave it at Book value if it's a small number leave it at Book value but if it's a big cross holding you might want to convert that book value to a market value by applying a multiple of Book value based on what business it's in so if you have Chemical Company you have a book value of 100 million if you go to the Industrial Averages I give you a price to book ratios for chemical companies you can use them to come up with an estimated market value we're almost done staying on that cross holding commitment go down to the liability side now and I talked about how minority interests are often you know separated out and I said include them in book Equity now I'm going to ask you how much those minority interests were both the most recent balance sheet and the balance sheet from a year ago that's pretty much it for the accounting numbers then I ask you how many shares are outstanding now if you look at annual reports or even 10 Q's it'll give you in the balance sheet share count but since share count can change on a weekly basis for lots of different reasons I would recommend going online and checking how many shares are outstanding in your company at the most updated number and the share price while you're there then I ask for an effective tax rate what's an effective tax rate comes out of the income statement so if your company doesn't report an effective tax rate it's easy to compute an effective tax rate take taxes paid in your income statement it's an acral number and divide by taxable income it should also be an income statement that's your effective tax rate that's what your company on average paid across all of its income now right below it I as for a marginal tax rate now you might be puzzled what is a marginal tax rate the marginal tax rate is the tax rate in your last dollar of income you say how am I going to find that out well again I'm a full service operation if you go across to a worksheet says country Equity risk premiums as part of that worksheet I also list for each country what the marginal tax rate is so if you have a company that is in Bangladesh the marginal tax rate is 30% you look up the tax rate put that in right below the effective tax rate number so at this stage you pretty much got all of the numbers you need from financial statements now do you see why I don't forecast start all three financial statements because the number of inputs I need to value a company is relatively few and why forecast start all those other numbers but here's where valuation gets trickier why does it get trickier because you got to leave the comfort of past data and make forecast for the future in fact my entire valuation the next seven cells that you enter are what Drive the value of this company maybe an extra sell or two so let me go down the cells one by one first I ask you for Revenue growth next year and operating margins next year this is a pre-tax operating margin I say why separate next year from the next nine years because I have 10 years of forecast because usually have more more information about next year managements often issue guidances for next year you might have more in more analyst projecting for next year you have more crutches and you might want to use that to forecast the next year's Revenue growth and the next year's margin this is also your chance if you have a company which is pre-revenue to enter a really small Revenue number as this year's revenue and enter a huge Revenue growth in next year to get it out off the ground to get it started Revenue growth and pre-t pre-tax operating margins next year now you might look at the past for this company to make a judgment on if you have a mature company the past might give you some sense of what will the growth look like what will the margins look like for other companies they might not you might have to look at Industrial Averages and that's why if you look to the right of the inputs I've given you what the average growth rate is and margin is so in a sense you're constantly looking at all of the data you can to make your best estimates if you go below the operating margin line for next year I ask you for Revenue growth from years 2 through five this is your big growth input if you have a small company that's going to become a big company these are the growth rates that drive them there so you give me the growth rate from years two through five here you you will have less to go on but it's actually a more critical input think about the size of the market what you think about the company and more most important what your story is for the company then I ask you for a Target operating margin which is not what the margin is right now but what will the margin be when this company gets through its growing paints here you might want to look at Industrial Average in some case that'll help in other cases you might have to look at unit economics in the business this kind of business where the next unit you sell cost you almost ning economies are scale all those business levels we talk about will go into the target margin then I ask you which year will the target margin be hit and again this is your chance to tell me how smooth the pathway to profitability is for your company if you think it's going to go from minus 10% margins to plus 20% in 5 years that's pretty Speedy I'll take it so the choices you have 5 6 7 8 n you can pick any number and I'll move it to that you can even pick one in which case I'll move you to your target next year now the last two inputs are a little tricky because you might have never seen these items before remember I'm growing revenues I'm getting operating income but to grow those revenues I have to reinvest now I want to know how much I need to reinvest to get that growth rate so ask for a ratio called sales to Capital how many dollars of capital you get for every dollar of capital of so dollars of sales do you get for every dollar of capital you invest the higher this number the more efficiently generating growth again to give you some parameters I've shown you the industri averages I've shown you this company sales to cap but I also computed what's called a marginal sales to cap ratio you're saying what is that I look at the change in sales in the most recent year and the change in capital it's kind of a at the at the margin is are things getting better or worse that's pretty much all of the numbers you need to forecast your company's cash flows Revenue growth margins sales and capital that was easy right we're almost done here I ask you for two numbers to drive the discount rate one is the risk-free rate and whatever currency you're working with in this case I look looked up the t- bond rate because I chose to Value Amazon and dollars for the cost of capital load it's a green cell and remember what I said about green cells being output cells go to the cost of capital worksheet and you will see that I give you choices there are four different ways you can compute the cost of capital you can input it directly maybe you know your company's cost of capital just say I will input in which case enter the number you want to use right below you can estimate a detailed cost of capital what does that require you go through and enter what businesses remember I promised you I would let you do this the businesses your companies in and again these are all pull down menus so you can pick you know four five six different business your companies in or you can pick and there you have a choice you can either use a US industry averages or Global industry averages so I use that to get a bait for your company the business mix you have for your Equity risk premium I ask you do you want to enter your get your Equity risk premium based on the countries you operate in or regions you might not really have a choice because it it's based on what your company breaks its revenues down so if you're Coca-Cola you break it down by regions I've listed other region of the world just go enter the revenues for your company next to each region and make sure you all the other cells are blanked up the regions you're not in and I will compute a weighted average Equity risk premium by converting the revenues you know into a weight into weights and the weights into a weighted premium so for both the beta and the and the equity risk premium I give you a chance to add more Nuance more detail now if you look at the debt numbers the debt the first two are green cells which means I'm taking what you inputed on the first page and just trans transferring them there but I do need a cost of debt now and if I can I'd like a market value of debt so ask you whether you know what the average maturity there really weighted average maturity is if you don't have no idea just enter zero move on but if you do enter that number because I can use it to Compu a market value of debt if needed now to get a cost of debt again I have a pull down menu and I give you choices okay I say okay here are the choices you can use to get your cost of debt so the choice with the cost of debt is I can directly input and say my cost of debt is 4 and a half% maybe for your company you know the pre-tax cost of debt the second is your company is an actual rating if it has an actual rating right below I give you a chance to enter what that rating is so if you click on the rating and you pull the menu down you will actually see a list of ratings now it's if you have a rating that's not on that list pick the closest rating I didn't want to make the list too long if you want to directly input it enter the number directly and if you don't have want to directly input it and you don't have a rating there's a third way you can get it which called a synthetic rating what is that you tell me what type of company you have one is large and in a developed Market two is small or in an emerging market so enter just the one of the two I'll take care of the rest for you because here's what will happen is there is I thought it was a Ginsu worksheet I will if you go to this worksheet called synthetic rating there you see the two that you entered I take your oper ebit and you divide by the interest expense to come up with an interest coverage ratio I convert that interest coverage ratio into a rating and the rating into a default spread and the cost of debt I thought it was pretty neat I mean it's not not the greatest no not amazing but it's you have a cost of debt so you got a pre-tax cost to debt the marginal tax rate you entered in the first page becomes the marginal tax rate here that's pretty much all I need so the detail I get a cost of capital based the input so you can either directly input it you can have a detail but I've added two other options which I didn't have in my past one so this is the detailed cost of capital the third choice is just use an industry average and it's really it's four choices but the first one is a direct input so I don't want to call it an approach to get a cost of capital so the third approach you tell me what businesses you're in I'll take a weighted average the cost of capital of the other companies in the business so for this you'll have to enter the industry break breakdown and tell me whether you want us or Global but I'll take a weighted average of the different businesses you're in and there's a final option that actually use surprisingly frequently if you look at this um table I have a distribution of cost of capital at the start of 2024 which will update whenever you update the risk free rate so basically it's built to update you can go in and tell me for instance with Amazon that it is a US company that it's about average big company it can't be that far the median and then I will go to the table and look up for us median 8.6% and use that so direct input detailed calculation with you know equity risk prin and betas industry average or a histogram so that's the number let me go back and fix it though because I did originally use the distribution so I want to leave it at that so you can see the numbers right the distribution the distribution gives me 8.6% and there's a loose end to tie up which for many of your companies no longer might be an issue and here's what the loose end is if your company has options outstanding those options are an overhang on your Equity they will get exercise they will drain your Equity a lot of an just add the option numbers to the shares outstanding I don't think that's right I've got to treat them differently because options are not shares they might become shares but they might not so if you have options go in and again know make sure you take the yes and make it into a no but if I said yes here now I didn't Amazon did not have any options increasingly us companies have shifted restricted stock that any options but Tesla did I would enter the number of options outstanding which should be in the footnote which talk so this is not something you have to invent it should be in the annual report of the footnote they have options outstanding it'll tell you how many options are outstanding it'll often break them down into vested and non-vested count them all hey it'll give you a weighted average exercise price for the options and an average maturity so all of that should be in in your financial statements if you have options so if you can't find them don't freak out many companies don't finally ask for a standard deviation and at this point you're saying I have no idea one of the nice things about having the industri averages and you will see them towards the end is I do report industri averages for everything not just the accounting numbers like margins and revenue growth but also for things like beta and standard deviation you can look up the standard deviation of retail firms if you want to and enter that number we're pretty much done with the valuation but then I give you a few bells and whistles and these are options that you don't have to use to finesse your valuation so if you have no idea what you're doing the safest thing to do is enter Noe to every single one of these questions but here's what the questions allow you to do first I tell you look the spreadsheet is built up the default is I move your cost of capital towards a steady state in the steady state I compute by taking the risk-free rate and adding to that your risk premium say it says plus 4.5 that's not true it's actually your risk premium it's a modified word it's 0.9 times risk premium so if your risk premium is 6% I'll move your cost of capital using why 0.9 because if you look across all companies that seems to be where the cost of capital ends up so if you say no then I will automatically move it there but you have the the choice of saying yes and replacing it with your input in the case of Amazon as you can see I've replaced that default with what I think is a more reasonable cost to Amazon and 8% cost of capital then I tell you what I do with with the return on Capital Now the default in the spreadsheet is as you move towards year 10 which is your end year your return on Capital on your new Investments after that year will be equal to your cost of capital again that's my default because most companies competitive advantages fake well the case of Amazon I don't think that's going to happen it's got an amazing various to entry so I'm going to say yes and and if I say yes then I have the obligation to then say well what kind of return on Capital will I make and you can compare that to the cost of capital now the way to think about the bigger your competitive Advantage is the more you can earn over and above your cost of capital don't go crazy and enter 100% but this number can be 10% if you have small competive advantages and your cost of capital is 8% can be 12 for Amazon I've given it 15% healthy competitive advantages the next question is about what we do in discounted cash flow valuation every discounted cash flow valuation we value a company as a going concern what does that mean we assume your company if it has a bad idea bounces back and comes back as a company but we do know that sometimes you don't come back there's a failure risk and especially if you're valuing a company where that failure risk is high I want to give you a chance to enter that failure risk directly rather than try to push it into your cost of capital but it almost never fits so the case of Amazon of course I don't see failure risk but if I say yes yes to this this failure R then I ask you what's the chance of failure now your first reaction might be I have no idea but to help you I've created a failure rate worksheet where I give you different ways you can estimate failure I'll confess that this is one number where you are going to have to be make subjective judgments because even with my guidance there's not a whole lot you're going to learn so you put in a probability of failure then I ask you what happens if you fail you say what do you mean what happens if you fail well you liquidate right if you liquidate what do you get back as a percentage of value if you get back 100% of your value then it doesn't matter if you fail so usually in Failure you get back a smaller percentage than what you would get as a going concern and in some cases extreme cases you could set it to 0% I lose everything if it happens but basically that's what the 50% year is in the event of failure I'll get half of my estimated value now when I use the sales to Capital ratio to estimate reinvestment in my default I assume that they're contemporaneous what does that mean if I reinvest this year I get my revenues the same year saying that never happens not true right if you acquire a company the the growth happens and if it happens pretty quickly I would leave it at the default but if you're in a business where there's a lag between when you invest and when you get growth I'll give you an example you decide to build a factory it might take two or three years to be functional then I I allow you to set that lag in so the way this will work is if you say yes and put in three then my reinvestment in year one will be based on my Revenue growth between years three and four not the revenue growth in year one so it's an option to consider if you want to I do also adjust your effective tax rate remember the number you gave me for most companies be lower than the marginal tax rate I do adjust it towards the marginal tax rate as you get towards your 10 your terminal value why do I do it because because you know when you pay an effective tax rate which is lower because you have tax deferral eventually you run out of deferrals you run out of time your effective it happens when your growth drops off but if your effective tax rate is low because you're in other countries with low tax rate in other words it's a sustainable effective tax rate you can override this assumption and leave it at no and I will leave your tax rate whatever the number is over time Amazon's effective tax rate is low because it operates in countries with lower tax rate not that much tax deferral at least that I see on the Ballance sheet the next question is about companies that have had a history of losing money or are still losing money if you have a history of losing money what you're allowed to do is accumulate those laws it's called an nool and use it to offset your taxable income and you start to make money I build that into the spreadsheet but to do it I need to know what you're coming into the valuation with as your nool this again will be in your financial statements check through the taxes section of the footnotes and if you have an nool and you enter that number I will start with that number and if you lose money I will add to that number and keep track of it to save your taxes finally the Perpetual growth the final couple of assumptions relate to the terminal value one is um when when I when you enter a risk free rate I the default is I leave it at that number because I don't want you to for being sit there being an interest rate for interest rate forecaster so we enter a risk fre rate of 4.08% that becomes my risk free rate in perpetu but it's really a 10year rate so the end of your 10 you might want to reconsider this was especially the case when rates were really low in 2021 people said well maybe the rates will go up if you want to reconsider just enter if you enter yes here and you want to change that assumption the default Assumption of leaving the rates where there are enter what you think the rates will be after year 10 so if you think rates will go down the t- bond rate will go down to 2% by the end of your 10 it say yes and 2% and I will move your risk free rate to 2% after your 10 but there's a catch after your 10 when you look at the growth rate I'm using in my default my growth rate is said equal to your risk free rate so if you pick a lower R free rate your stable terminal growth rate will also be set lower but the default is whatever growth rate you see in my terminal value calculation will be said equal to your RIS fre rate now you might want to change that assumption as well why not to make it higher than the risk fre rate don't mess with that that'll truly blow up your valuation but you might have companies of fossil fuel company a tobacco company where you think that in steady state the company will get smaller Revenue growth will be negative and I want to give you that choice you can ignore the last three cells because they reflect what was true in the US before 2017 which is if you had income outside the US you're quirk in the tax law where if you kept the income elsewhere you didn't have to pay tax taxes on it it created what was called trapped income the 140 billion is actually from Apple 8 years ago when I valued Apple huge trap cash and essentially I brought that in because that trap cash you can't leave trap forever so I said okay tell me how much trap cash you have and how what the tax additional taxes you'll have to pay cuz I've got to reduce your value by that I would say you no longer have to do it but the US tax law was changed in 2017 but if nothing happens it actually expires in 202 6 we could be back to this so I've left it just in case that's pretty much it that's your input page let me take you very quickly through the output page the bulk of the numbers are in this page you'll see a black box that says if you get value errors all over which can sometimes don't freak out it's usually because your option value spreadsheet has gone has has gone out of wh and it's a very easy fix go into the adjusted s now number enter any number five and then undo it's like magic every time you do that the valuation errors seem to disappear so this spreadsheet take a look at at what you're projecting because it's coming directly off your inputs so if you don't like a number on this output page it's not me driving it it's your inputs you said you look at the revenue and said that looks way too big hey you're the one who entered the revenue growth number said The Profit that looks like it's too much profits or too little hey you enter the margin number my free cash flows to firm are all negative hey you enter the sales to Capital number so what I'm trying to say is if you see a number on this valuation page that that makes you uncomfortable it's coming from one of your inputs so if you look at this output page the valuation that I get for Amazon is 14667 and that's about 15.23% the price on that day 169 was about 15.23% High I used to leave the spreadsheet at this but you know one of the things I I talk about valuation is how every input that we used comes from a story so a few years ago I created a page so this page actually just takes the previous page and presents it in a different way so see these cash flows they come from the previous page but it also takes all of your inputs and gives you a chance to tell me why you think this company is going to have these inputs so here for Amazon I give you with each input the revenue growth the margins the sales to Capital the cost of capital what my story is for why I do it I'd strongly encourage you to fill in this worksheet because it'll make you think about your inputs more and whether you want to leave them as is two years ago I decided to also show the valuation as a picture again there's nothing in this page that's happening that you couldn't have gotten the evaluation output page but it just takes the numbers and shows them as a picture maybe some people connect better with pictures incidentally there's a Diagnostics page and if you go to this Diagnostics page I would take a look at it basically I take your numbers and I take you through a series of checks I said check your Revenue growth number to make sure you're not making your company's revenues higher than the market check your dollar revenues check your margins make sure that they're within lines with what you'd expect the company to earn given the sector it say check how much you're reinvesting that comes out of your sales to Capital ratio you're reinvesting too much you're reinvesting too little and one of the numbers that helps you make that judgment is what's happening to your return on Capital and for the cost of capital and failure rate just check those numbers again and make sure that they're reasonable so the diagnostic page is basically a page where it says look if you feel your value is too low here are some of the things that fix it I'm not saying that you should just go and change them but these are the cells that are driving it so if your calculated value is too low it's because your Revenue growth rate might be too low low increasing it will increase your value your margins might be too low so again I'm not suggesting you do this but it tells you the Dynamics that will drive the value up or down so one final thing I've talked about the industry averages that are built in for both the US so if you go towards the end of the worksheet you'll see those industry averages I've also built in the country Equity risk premiums and there's one sell here which I would suggest you update cuz this was updated February 20 24 because I was doing the valuation February 2024 and it's based on the implied Equity risk premium compute for the S&P 500 the start of February 2024 I do update that at the start of every month so if I were using the spreadsheet in April I would go look up that updated number and put that into the cell it'll update all of your country risk premiums as well which will give you as updated number as you can find I've got gone on way longer than I expected to but I hope you found this session useful if you're struggling with the spreadsheet but again remember it is your tool adapt it modify it take ownership of it I'm perfectly okay with it thank you very much for listening and I hope you have a good day