Excel Spreadsheet Valuation Guide

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 valuing companies and I thought I'd take you through the process of how I enter the inputs in fact if you look at if you if you sat in 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 Ginzu. 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 are trying to value the entire business. As opposed to what? You can value just the equity in a business. There's a difference. variation of this model on my spreadsheet called the FCFE simple Ginsu model that's to value company based on free cash flows to equity this is about valuing the entire business and this is the spreadsheet I draw on almost 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 financial service companies it'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 value of business and Let me talk a little bit about the simple and the Ginzu. 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 you say, 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. So what's Ginzu? I might give away my age, but I 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, broadcast. companies didn't have much to show. So you could buy up 30 minutes 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 Ginzu knives. And here's how it goes. There's a Japanese chef and he has a Ginzu knife and he says this is an amazing Ginzu 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 you 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 employee options for me. Add a worksheet that converts R&D into a capital asset. In other words, the things you run into in valuation. you have to keep leaving your spreadsheet to do them. I said, wouldn't we need to have 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 Ginzu knives. You get them all. And you get them all at no cost. So this is 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? Because one of the worksheets... that I've 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 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 trust Excel's functions. Like what? I don't think I ever used the NPV function in Excel. You think 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're 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 still remember when I used to value companies by hand enough 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 Excel. 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 structure going because I've seen... seen too many cases where people work for mortars 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 Demodran 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 year of financial statements. You don't need much. You don't need 10, 20, 30 years. This isn't a historical data-driven valuation. 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 in 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 footnotes 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 company, the company you've picked, what the current stock price is, 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 PowerPoint. go directly to the spreadsheet and take you through cell by cell what the spreadsheet is. Now before you get started though, this is very important, this spreadsheet has circular reasoning built into it. It's not a bug, it's a feature. I needed to get some things done, especially the option pricing. So before you get started go into 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 iterative 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 you know early early february 2024 and the most recent 10k for amazon had just come out as the 2023 10k And it was a calendar year. Remember that not all companies have 10Ks or annual reports that end on December 31st. 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 get further through the year, if I were doing a valuation of Amazon in October and 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 start by identifying the two colors of cells you're going to see. You're going to see yellow cells, which for the most part are input cells, we enter the numbers, and green cells, which are computed cells, where I'd prefer you don't overwrite it. If you do, it's not the 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 doing the valuation? End of 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 company is incorporated. Now I know I make a big deal about risk coming not from where you're incorporated but from where you do business. For this part of the spreadsheet at least, enter the country of incorporation. 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 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 its biggest business is. And guess what? Amazon is in... is not just in retail, it's in entertainment, it's in logistics, 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 for the most recent annual report across all their businesses. Again, you might say I'm 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 your most recent year. Incidentally, before you get started, make a decision on the units you're going to enter the numbers in. 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 if you enter Amazon's numbers in dollars, you're going to have numbers that stretch across the entire cell 12, 13, 14 digits. So 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 millions, 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 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 2023 and 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 0.75. So in this case because I have two I have an annual report it's one year since my last 10k I'll enter one. If this was after the third quarter of 2024 it'll be 0.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 though, 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 and 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'll usually see an operating income line item. There again, I enter the most recent year and the year before. And again, because it's an 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 flow to the firm. You might say, why do I care about interest expenses? Because I might need it to get a cost of debt. So again, ask for the interest expenses in the most recent year, in 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 library 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 crossholding that you consolidated. Add those two numbers up for the most recent year and again for the year before that. So basically, you're looking at the most recent balance sheet and the balance sheet from the last 10K. So in the case of Amazon, this is the shareholder's equity at the end of 2023 and the end of 2022. Now, I have to warn you. you that some of your companies, you might find a shocking number, which is your shareholders equity is negative. You say, that must be a basket case company. Not necessarily. I valued the Home Depot last week and its 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'll also include in your current libraries the short-term debt and the short-term portion of long-term debts. Three items. And since 2019 for many companies you'll also see lease debt both in the long-term section in the short term. Add them all up for the most recent from the most recent balance sheet and the balance sheet from the most 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 evaluation 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 accounting are inconsistent. On what? On treating capital expenses or 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 affect your 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 costs or exploration costs or whatever you think is an item that should be capitalized that's right now being expensed, 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. You 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 companies, big energy companies, it takes a long time between the time you invest an oil comes out of 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 should take 10 years for Amazon's R&D to pay off. I say it takes about three 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 dollars. And then enter the R&D expenses for the last three years. Here's the problem with having only one annual report though. You can get R&D expenses for only two years. In the case of Amazon, I had 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, R&D expenses from the last year, two years ago, three years ago. The rest I'll do. I'll convert your R&D into an asset. I will change your R&D. 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 it as an operating expense and not treat it as debt. Retailers, restaurant companies, even some airlines use that loophole to keep debt off the boat. books. That was a terrible practice and in 2019 in large segments of the world that was ended because 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've included in debt just leave it at no because you're trusting the accountants. You think but what if my company hasn't capitalized leases or I don't trust the accountants. then change this note to a yes. But if you 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 commitments, this will be a footnote. If you stay in those footnotes, it'll give you a table. The case of Amazon, the table gives me lease commitments each year for the next five 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 discard 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 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 include it as debt. So this option is available for you, even for companies who lease their 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 I ask you for cash and marketable securities. You're in the balance sheet still because you've 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 that 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 because some of you will be tempted to open up the balance sheet and start throwing in the kitchen sink things like goodwill and brand name. Don't do that. Here's the rule. Any asset that is generating cash flows for you already, you've already counted. Goodwill 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 you're saying, what's not counted? The most important item is cross holdings and other companies. What am I talking about if you want 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? The reason I need that is when I do a traditional firm valuation based on operating income, I haven't valued 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. Now, be careful and go to the footnotes and check out what's in the long-term investment. Make sure you're not double counting. Some companies mark to market. The rule in accounting is if you hold it for trading you got to mark to market. Softbank marks to market. That's nice because you get the market value cross holdings. But for most companies it'll be recorded at book value. Now in your 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 a chemical company, have a book value of 100 million. If you go to the industry averages, I give you 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 separated out and I said include them in book equity. Now I'm going to ask you how much those minority interests were. 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 10Qs, 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? It 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 accrual number and divide by taxable income. It should also be in 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 ask 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, it 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%. So look up the tax rate, put that in right below the effective tax rate number. So at this stage, you've pretty much got all of the numbers you need from financial statements. Now do you see why I don't forecast out all three financial statements? Because the number of inputs I need to value a company is relatively few. And why forecast out all those other numbers? But here's where valuation gets trickier. Why does it get trickier? Because you've got to leave the comfort of past data and make forecasts 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 cell or two so let me go down the cells one by one first to ask you for revenue growth next year and operating margins next year this is a pre-tax operating margin they say why separate next year from the next nine years because of 10 years of forecast because usually you have more more information about next year management often issue guidances for next year you might have more in more analyst projecting for next year you're 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 next year to get it off the ground, to get it started. Revenue growth and pre-tax operating margins next year. Now you might look at the past for this company to make a judgment on it. If you have a mature company, the past might give you some sense of what will happen next. will the growth look like? What will the margins look like? For other companies they might not. You might have to look at industry 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 5. 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 2 through 5. Here 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 most important, what your story is for the company. Then I ask you for a target operating margin, which is... is not what the margin is right now, but what will the margin be when this company gets through its growing pains. Here, you might want to look at industry average. In some cases, 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 costs you almost nothing. Economies of scale. All those business levers 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 five years, that's pretty speedy. I'll take it. So the choice is you have 5, 6, 7, 8, 9. 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. And I want to know how much I need to reinvest to get that growth rate. So I ask for a ratio called sales to capital. How many dollars of capital you get for every dollar of capital of dollars of sales you get for every dollar of capital you invest. The higher this number, the more efficiently you're generating growth. Again, to give you some... parameters. I've shown you the industry averages. I've shown you this company's sales to cap. But I also compute 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 margin, 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 to capital. That was easy, right? But we're almost done here. I ask you for two numbers to drive the discount rate. One is the risk-free rate. Whatever currency you're working with, in this case, I looked up the T-bond rate because I chose to value Amazon in dollars. For the cost of capital, 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... Estimated detailed cost of capital. What does that require? You go through and enter what business is. Remember I promised you I would let you do this. The businesses your company is in and again these are all pull down menus so you can pick you know four, five, six different business your company is in or you can pick and there you have a choice you can either use the US industry averages or global industry averages. So I use that to get a beta 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'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 out the regions 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 out 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 and the equity risk we might give you a chance to add more nuance more detail now if you look at the debt numbers the debt and the first two are green cells which means I'm taking what you inputted on the first page and just transfer 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 I ask you whether you know what the average maturity is, really weighted average maturities. 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 compute 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. 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 four and a half percent. Maybe for your company you know the pre-tax cost of debt. The second is your company is an actual rating but as 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 want to directly input it and you don't have a rating, there's a third way you can get it, which is 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 smaller and 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. I took it as a Ginzo worksheet. If you go to this worksheet called synthetic rating, There you see the two that you entered. I take your 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 default spread and the cost of debt. I thought it was pretty neat. I mean, it's not the greatest, not amazing, but you have a cost of debt. So you've got your pre-tax cost of 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 on the input. So you can either directly input it, you can have a detailed, 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 to just use an industry average. And it's really there is four choices, but the first one is a direct input. I didn'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 of the cost of capital. the other companies in the business. So for this you'll have to enter the industry 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 used surprisingly frequently. If you look at this 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, Amazon, that it is a U.S. company, that it's about average, it's a big company, so it can't be that far, the median. And then I will go to the table and look up for U.S. median, 8.6%, and use that. So direct input, detailed calculation with equity risk, for instance, 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 eight point six percent 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 exercised. They will drain your equity. A lot of analysts 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, 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 to restricted stock. there are many 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 okay it'll give you a weighted average exercise price for the options and an average maturity so all of that should be 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. At this point, you're saying, I have no idea. One of the nice things about having the industry averages, and you will see them towards the end, is I do report industry 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 no to every single one of these questions. But here's what the questions allow you to do. First I tell you look this spreadsheet is built up the default is I move your cost of capital towards a steady state. The steady state I compute by taking the risk-free rate and adding to that your risk premium. So 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 the risk premium. So if your risk premium is 6% I'll move your cost of capital. You're saying 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 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 of capital for Amazon, an 8% cost of capital. Then I tell you what I do 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 fail. Well, in the case of Amazon, I don't think that's going to happen. It's got amazing various entry. So I'm going to say yes 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 a hundred percent but this number can be ten percent if you have small competitive advantages and your cost of capital is eight percent can be twelve. For Amazon I've given it fifteen percent. 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 is 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 we are 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. where it almost never fits. So the case of Amazon, of course, I don't see failure risk. But if I say yes to this, there's failure risk, 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 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 a hundred percent of your value then it doesn't matter if you fail. So usually in failure you get back a smaller percentage and what you would get as a going concern and in some cases extreme case you could set it to zero percent. I lose everything if it happens but basically that's what the 50% here 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. You think that never happens. Not true, right? If you acquire a company, 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 allow you to set that lag. 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, it would 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 when you pay an effective tax rate, which is low because you have tax deferral, eventually you run out of deferrals, you run out of time, you're 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 rates, 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 rates. Not that much tax deferral at least that I see on the balance 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 it's called an NOL 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 NOL. This again will be in your financial statements. Check through the taxes section of the footnotes and if you have an NOL 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 you taxes. Finally the perpetual growth rate, the final couple of assumptions relate to the terminal value. One is when When you enter a risk-free rate, the default is I leave it at that number. Because I don't want you to be an interest rate forecaster. So if you enter a risk-free rate of 4.08%, that becomes my risk-free rate in perpetuity. But it's really a 10-year rate. So at the end of year 10, you might want to reconsider. This is 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 they 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 year 10, say yes and 2% and I will move your risk-free rate to 2% after year 10. But there's a catch. After year 10, When you look at the growth rate I'm using, in my default my growth rate is set equal to your risk-free rate. So if you pick a lower risk-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 set equal to your risk-free rate. Now you might want to change that assumption as well. Why? Not to make it higher than the risk-free rate. Don't mess with that. That'll truly blow up your valuation. But you might have companies, a 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 had this quirk in the tax law, where if you kept the income elsewhere, you didn't have to pay taxes on it. It created what was called trapped income. The 140 billion is actually from Apple eight years ago and I valued Apple. Huge trap cash and essentially I brought that in because that trap cash you can't leave trapped forever So I said, okay Tell me how much trap cash you have and what the tax Additional taxes you'll have to pay because 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 2026. 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 on 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 out of whack. And it's a very easy fix. Go into the adjusted edge. Number. Enter any number. 5. 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 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. It's a profit. That looks like it's too much profits or too little. You entered the margin number. My free cash flows to firm are all negative. Hey, you entered the sales to capital number. So what I'm trying to say is, if you see a number on this valuation page 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 $146.67 and that's about 15.23%. The price on that day, $169, is about 15.23% higher. I used to leave the spreadsheet at this but now one of the things I've talked about in valuation is how every input that we use comes from a story. So a few years ago I created a page and 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, 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 also show the valuation as a picture. Again there's nothing in this page that's happening that you couldn't have got in 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 say 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's in. 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 happened 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. 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 see those industry averages. I've also built in the country equity risk premiums and there's one cell here which I would suggest you update because this was updated February 2024 because I was doing the valuation February 2024 and it's based on the implied equity risk premium compute for the S&P 500 at 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 kind of 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.

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 Ginzu. 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 are trying to value the entire business. As opposed to what? You can value just the equity in a business. There's a difference.

variation of this model on my spreadsheet called the FCFE simple Ginsu model that's to value company based on free cash flows to equity this is about valuing the entire business and this is the spreadsheet I draw on almost 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 financial service companies it'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 value of business and Let me talk a little bit about the simple and the Ginzu. 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 you say, 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. So what's Ginzu?

I might give away my age, but I 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, broadcast.

companies didn't have much to show. So you could buy up 30 minutes 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 Ginzu knives.

And here's how it goes. There's a Japanese chef and he has a Ginzu knife and he says this is an amazing Ginzu 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 you 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 employee options for me. Add a worksheet that converts R&D into a capital asset. In other words, the things you run into in valuation.

you have to keep leaving your spreadsheet to do them. I said, wouldn't we need to have 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 Ginzu knives.

You get them all. And you get them all at no cost. So this is 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?

Because one of the worksheets... that I've 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 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 trust Excel's functions.

Like what? I don't think I ever used the NPV function in Excel. You think 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're 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 still remember when I used to value companies by hand enough 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 Excel. 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 structure going because I've seen...

seen too many cases where people work for mortars 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 Demodran 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 year of financial statements. You don't need much. You don't need 10, 20, 30 years.

This isn't a historical data-driven valuation. 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 in 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 footnotes 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 company, the company you've picked, what the current stock price is, 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 PowerPoint. go directly to the spreadsheet and take you through cell by cell what the spreadsheet is. Now before you get started though, this is very important, this spreadsheet has circular reasoning built into it. It's not a bug, it's a feature. I needed to get some things done, especially the option pricing.

So before you get started go into 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 iterative 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 you know early early february 2024 and the most recent 10k for amazon had just come out as the 2023 10k And it was a calendar year. Remember that not all companies have 10Ks or annual reports that end on December 31st. 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 get further through the year, if I were doing a valuation of Amazon in October and 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 start by identifying the two colors of cells you're going to see. You're going to see yellow cells, which for the most part are input cells, we enter the numbers, and green cells, which are computed cells, where I'd prefer you don't overwrite it. If you do, it's not the 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 doing the valuation? End of 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 company is incorporated.

Now I know I make a big deal about risk coming not from where you're incorporated but from where you do business. For this part of the spreadsheet at least, enter the country of incorporation. 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 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 its biggest business is. And guess what?

Amazon is in... is not just in retail, it's in entertainment, it's in logistics, 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 for the most recent annual report across all their businesses. Again, you might say I'm 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 your most recent year. Incidentally, before you get started, make a decision on the units you're going to enter the numbers in. 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 if you enter Amazon's numbers in dollars, you're going to have numbers that stretch across the entire cell 12, 13, 14 digits. So 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 millions, 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 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 2023 and 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 0.75. So in this case because I have two I have an annual report it's one year since my last 10k I'll enter one. If this was after the third quarter of 2024 it'll be 0.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 though, 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 and 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'll usually see an operating income line item. There again, I enter the most recent year and the year before.

And again, because it's an 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 flow to the firm. You might say, why do I care about interest expenses?

Because I might need it to get a cost of debt. So again, ask for the interest expenses in the most recent year, in 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 library 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 crossholding that you consolidated. Add those two numbers up for the most recent year and again for the year before that.

So basically, you're looking at the most recent balance sheet and the balance sheet from the last 10K. So in the case of Amazon, this is the shareholder's equity at the end of 2023 and the end of 2022. Now, I have to warn you. you that some of your companies, you might find a shocking number, which is your shareholders equity is negative. You say, that must be a basket case company.

Not necessarily. I valued the Home Depot last week and its 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'll also include in your current libraries the short-term debt and the short-term portion of long-term debts. Three items. And since 2019 for many companies you'll also see lease debt both in the long-term section in the short term.

Add them all up for the most recent from the most recent balance sheet and the balance sheet from the most 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 evaluation 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 accounting are inconsistent.

On what? On treating capital expenses or 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 affect your 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 costs or exploration costs or whatever you think is an item that should be capitalized that's right now being expensed, 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. You 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 companies, big energy companies, it takes a long time between the time you invest an oil comes out of 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 should take 10 years for Amazon's R&D to pay off. I say it takes about three 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 dollars. And then enter the R&D expenses for the last three years. Here's the problem with having only one annual report though.

You can get R&D expenses for only two years. In the case of Amazon, I had 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, R&D expenses from the last year, two years ago, three years ago. The rest I'll do. I'll convert your R&D into an asset.

I will change your R&D. 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 it as an operating expense and not treat it as debt. Retailers, restaurant companies, even some airlines use that loophole to keep debt off the boat.

books. That was a terrible practice and in 2019 in large segments of the world that was ended because 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've included in debt just leave it at no because you're trusting the accountants. You think but what if my company hasn't capitalized leases or I don't trust the accountants. then change this note to a yes. But if you 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 commitments, this will be a footnote. If you stay in those footnotes, it'll give you a table. The case of Amazon, the table gives me lease commitments each year for the next five 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 discard 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 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 include it as debt. So this option is available for you, even for companies who lease their 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 I ask you for cash and marketable securities. You're in the balance sheet still because you've 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 that 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 because some of you will be tempted to open up the balance sheet and start throwing in the kitchen sink things like goodwill and brand name.

Don't do that. Here's the rule. Any asset that is generating cash flows for you already, you've already counted.

Goodwill 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 you're saying, what's not counted? The most important item is cross holdings and other companies.

What am I talking about if you want 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? The reason I need that is when I do a traditional firm valuation based on operating income, I haven't valued 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. Now, be careful and go to the footnotes and check out what's in the long-term investment. Make sure you're not double counting. Some companies mark to market. The rule in accounting is if you hold it for trading you got to mark to market.

Softbank marks to market. That's nice because you get the market value cross holdings. But for most companies it'll be recorded at book value.

Now in your 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 a chemical company, have a book value of 100 million. If you go to the industry averages, I give you 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 separated out and I said include them in book equity.

Now I'm going to ask you how much those minority interests were. 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 10Qs, 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? It 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 accrual number and divide by taxable income. It should also be in 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 ask 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, it 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%. So look up the tax rate, put that in right below the effective tax rate number.

So at this stage, you've pretty much got all of the numbers you need from financial statements. Now do you see why I don't forecast out all three financial statements? Because the number of inputs I need to value a company is relatively few. And why forecast out all those other numbers? But here's where valuation gets trickier.

Why does it get trickier? Because you've got to leave the comfort of past data and make forecasts 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 cell or two so let me go down the cells one by one first to ask you for revenue growth next year and operating margins next year this is a pre-tax operating margin they say why separate next year from the next nine years because of 10 years of forecast because usually you have more more information about next year management often issue guidances for next year you might have more in more analyst projecting for next year you're 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 next year to get it off the ground, to get it started. Revenue growth and pre-tax operating margins next year.

Now you might look at the past for this company to make a judgment on it. If you have a mature company, the past might give you some sense of what will happen next. will the growth look like?

What will the margins look like? For other companies they might not. You might have to look at industry 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 5. 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 2 through 5. Here 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 most important, what your story is for the company. Then I ask you for a target operating margin, which is... is not what the margin is right now, but what will the margin be when this company gets through its growing pains. Here, you might want to look at industry average.

In some cases, 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 costs you almost nothing. Economies of scale. All those business levers 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 five years, that's pretty speedy.

I'll take it. So the choice is you have 5, 6, 7, 8, 9. 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. And I want to know how much I need to reinvest to get that growth rate. So I ask for a ratio called sales to capital.

How many dollars of capital you get for every dollar of capital of dollars of sales you get for every dollar of capital you invest. The higher this number, the more efficiently you're generating growth. Again, to give you some...

parameters. I've shown you the industry averages. I've shown you this company's sales to cap. But I also compute 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 margin, 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 to capital. That was easy, right?

But we're almost done here. I ask you for two numbers to drive the discount rate. One is the risk-free rate. Whatever currency you're working with, in this case, I looked up the T-bond rate because I chose to value Amazon in dollars.

For the cost of capital, 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...

Estimated detailed cost of capital. What does that require? You go through and enter what business is. Remember I promised you I would let you do this. The businesses your company is in and again these are all pull down menus so you can pick you know four, five, six different business your company is in or you can pick and there you have a choice you can either use the US industry averages or global industry averages.

So I use that to get a beta 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'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 out the regions 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 out 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 and the equity risk we might give you a chance to add more nuance more detail now if you look at the debt numbers the debt and the first two are green cells which means I'm taking what you inputted on the first page and just transfer 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 I ask you whether you know what the average maturity is, really weighted average maturities. 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 compute 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. 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 four and a half percent.

Maybe for your company you know the pre-tax cost of debt. The second is your company is an actual rating but as 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 want to directly input it and you don't have a rating, there's a third way you can get it, which is 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 smaller and 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.

I took it as a Ginzo worksheet. If you go to this worksheet called synthetic rating, There you see the two that you entered. I take your 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 default spread and the cost of debt.

I thought it was pretty neat. I mean, it's not the greatest, not amazing, but you have a cost of debt. So you've got your pre-tax cost of 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 on the input. So you can either directly input it, you can have a detailed, 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 to just use an industry average. And it's really there is four choices, but the first one is a direct input. I didn'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 of the cost of capital.

the other companies in the business. So for this you'll have to enter the industry 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 used surprisingly frequently. If you look at this 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, Amazon, that it is a U.S. company, that it's about average, it's a big company, so it can't be that far, the median. And then I will go to the table and look up for U.S. median, 8.6%, and use that. So direct input, detailed calculation with equity risk, for instance, 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 eight point six percent 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 exercised. They will drain your equity. A lot of analysts 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, 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 to restricted stock. there are many 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 okay it'll give you a weighted average exercise price for the options and an average maturity so all of that should be 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. At this point, you're saying, I have no idea.

One of the nice things about having the industry averages, and you will see them towards the end, is I do report industry 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 no to every single one of these questions. But here's what the questions allow you to do. First I tell you look this spreadsheet is built up the default is I move your cost of capital towards a steady state.

The steady state I compute by taking the risk-free rate and adding to that your risk premium. So 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 the risk premium.

So if your risk premium is 6% I'll move your cost of capital. You're saying 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 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 of capital for Amazon, an 8% cost of capital. Then I tell you what I do 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 fail. Well, in the case of Amazon, I don't think that's going to happen.

It's got amazing various entry. So I'm going to say yes 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 a hundred percent but this number can be ten percent if you have small competitive advantages and your cost of capital is eight percent can be twelve.

For Amazon I've given it fifteen percent. 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 is 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 we are 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. where it almost never fits.

So the case of Amazon, of course, I don't see failure risk. But if I say yes to this, there's failure risk, 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 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 a hundred percent of your value then it doesn't matter if you fail. So usually in failure you get back a smaller percentage and what you would get as a going concern and in some cases extreme case you could set it to zero percent.

I lose everything if it happens but basically that's what the 50% here 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. You think that never happens.

Not true, right? If you acquire a company, 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 allow you to set that lag. 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, it would 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 when you pay an effective tax rate, which is low because you have tax deferral, eventually you run out of deferrals, you run out of time, you're 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 rates, 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 rates. Not that much tax deferral at least that I see on the balance 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 it's called an NOL 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 NOL. This again will be in your financial statements. Check through the taxes section of the footnotes and if you have an NOL 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 you taxes. Finally the perpetual growth rate, the final couple of assumptions relate to the terminal value.

One is when When you enter a risk-free rate, the default is I leave it at that number. Because I don't want you to be an interest rate forecaster. So if you enter a risk-free rate of 4.08%, that becomes my risk-free rate in perpetuity.

But it's really a 10-year rate. So at the end of year 10, you might want to reconsider. This is 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 they 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 year 10, say yes and 2% and I will move your risk-free rate to 2% after year 10. But there's a catch. After year 10, When you look at the growth rate I'm using, in my default my growth rate is set equal to your risk-free rate. So if you pick a lower risk-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 set equal to your risk-free rate. Now you might want to change that assumption as well.

Why? Not to make it higher than the risk-free rate. Don't mess with that. That'll truly blow up your valuation.

But you might have companies, a 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 had this quirk in the tax law, where if you kept the income elsewhere, you didn't have to pay taxes on it. It created what was called trapped income.

The 140 billion is actually from Apple eight years ago and I valued Apple. Huge trap cash and essentially I brought that in because that trap cash you can't leave trapped forever So I said, okay Tell me how much trap cash you have and what the tax Additional taxes you'll have to pay because 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 2026. 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 on 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 out of whack. And it's a very easy fix. Go into the adjusted edge.

Number. Enter any number. 5. 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 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. It's a profit. That looks like it's too much profits or too little.

You entered the margin number. My free cash flows to firm are all negative. Hey, you entered the sales to capital number. So what I'm trying to say is, if you see a number on this valuation page 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 $146.67 and that's about 15.23%. The price on that day, $169, is about 15.23% higher. I used to leave the spreadsheet at this but now one of the things I've talked about in valuation is how every input that we use comes from a story. So a few years ago I created a page and 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, 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 also show the valuation as a picture. Again there's nothing in this page that's happening that you couldn't have got in 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 say 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's in. 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 happened 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.

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 see those industry averages. I've also built in the country equity risk premiums and there's one cell here which I would suggest you update because this was updated February 2024 because I was doing the valuation February 2024 and it's based on the implied equity risk premium compute for the S&P 500 at 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 kind of 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.

Transcript for:Excel Spreadsheet Valuation Guide

Transcript for:
Excel Spreadsheet Valuation Guide