Valuation Modeling: Excel as a tool

Hi, welcome back! In this session, I'm going to break the mold; not talk about big ideas or companies, but about how to use an Excel spreadsheet I've created on valuation. Before I go further, though, there's nothing magical about the spreadsheet. There are lots of spreadsheets out there which are much more powerful, have a lot more stuff in them, but this is the spreadsheet I use in 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 on my classes, you do know that I don’t spend much time. In fact, I almost never open an Excel spreadsheet in class or talk about equations. And this gives me a chance to get into the mud and talk about the details. So, before I start, a couple of things about the spreadsheet that I'm going to give you a link to. It's called FCFF Simple Ginsu.

Now, there’s a reason for each part of that: it’s an FCFF model; it’s a free cash flow to the firm model. We’re trying to value the entire business as opposed to what you can value just the equity in a business. There’s a different variation of this model on my spreadsheet called the FCF Simple Ginsu model. That’s the value company based on free cash flows 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 the financial service companies?” That’s a tale for another day. There’s a third spreadsheet just for financial service companies, but in this one, I want to focus on valuing a business.

Let me talk a little bit about the simple and the Ginsu. 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, I 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 Ginsu? I might give away my age, but when 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, broadcasting 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 on a product. I remember the second day I was in the US, I turned on the TV, and there’s a commercial for Ginsu knives.

Here’s how it goes: there’s a Japanese chef, and he has a Ginsu knife, and he says, “This is an amazing Ginsu knife. If you pay $19.99, you’ll get this knife.” And then, over the next 29 minutes, he throws in enough knives to be a serial killer! By the time you’re done, you’ve got 25 different variations all in that package.

And 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 an employee options for me, add a worksheet that converts R&D into a capital asset.” In other words, the things you run into valuation. We have to keep leaving your spreadsheet to do them. I said, “Would we need to add them?”

So if you look at the spreadsheet that I'm going to show you in a couple of minutes, it’s got multiple worksheets. Think of those as the Ginsu knives: you get them all, and you get them all at no cost! Now, this is the version—the version I’m going to talk about in this piece is the 2024 version. I update these spreadsheets at least twice a year, sometimes more. This is the January 2024 version.

You’re saying, “Why would spreadsheets vary across time?” Because one of the worksheets that I built in—or a couple of the worksheets—reflect data as of the start of 2024. Data on industry averages, data on country risk premiums, and it’s built into the spreadsheet, again, to 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 use the NPV function in Excel.

You say, “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 used to remember when I used to value companies by hand, I never could 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. I’ve seen too many cases where people work for models rather than the other way around. And finally, this is just my version of the spreadsheet. Feel free to bend it to your needs, adapt it, modify it, and at the end of the process, I want you to take ownership of what comes out of the spreadsheet.

Put differently, when you get a value for a company, I don’t want to hear the words, “The Deodorant model valued 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’s worth of financial statements. So, you don’t need much; you don’t need 10, 20, or 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 10-Ks, 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. If you have no idea how to do that, you might want to watch the session on risk-free rates. The second is what your current—the company you’ve picked— what the current stock 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 and go directly to the spreadsheet and take you through cell by cell what the spreadsheet is. Now, before you get started, though—I know this is very important— this spreadsheet has circular reasoning built into it. It’s not a bug; it’s a feature! I need it to get some things done, especially the option pricing.

So before you get started, go to Excel, go to preferences. I’m on a Mac; it might be in a different place on a PC. Click on the calculation option and make sure the box next to “use iterative calculations” is checked off. You can leave it at the default; 100 is more than enough for what we do—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 early February 2024, and the most recent 10-K for Amazon had just come out; it’s the 2023 10-K and was a calendar year. Remember that not all companies have 10-Ks or annual reports at the end of 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 or November, in addition to the 2023 10-K, I would print off the most recent 10-Q or the quarterly report, which could be in September, June, or March—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 where we enter the numbers, and green cells, which are computed cells where I prefer you don’t override it. If you do, it’s not the end of the world, but it’ll also mean that that cell will never get recalculated again.

So, the first thing I ask you when you’re doing the valuation, enter the date—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 anybody should have any issues so far. Then if you move to the first input, it’s a pull-down menu.

So rather than trying 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 with Amazon, 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 the cost of capital and I 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 the company is in—again, again, don’t try to enter the name of the 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. Guess what? Amazon is not just in retail; it’s in entertainment, it’s in logistics, it’s in the cloud business, but its biggest business is retail.

So I picked retail general for both the US and Europe, and as I said, most of the time you’re going to pick the same. Then we get to revenues! 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 from the most recent annual report across all their businesses.

Again, you might ask 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 the 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 million, so it’s $574 billion in revenues. So that’s for the most recent 12 months. Now while I’m on that revenue line, remember it’s an annual report. If you look at the next column, it’ll give you the revenues in the most recent year. I’m going to enter those revenues here, so that is the last 10-K before your last 12 months, and because of an annual report, that last 10-K 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 in the first three quarters of 2024. If that is what I’m putting in my most recent 12 months, the number I will enter as my last 10-K will be the 2023 number.

So it’ll be separated, and this is the last cell year by 75. In this case, because of two, I have an annual report; it’s one year since my last 10-K. I enter one; if this was after the third quarter of 2024, it’ll be 75, after the second quarter, 0.5; first quarter, 0.25. Essentially, let the spreadsheet know when you’re computing things like growth rates; it’s been only half a year since the last number—this case, so one year.

The 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 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 10-K 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, the most recent 12 months, and in the last 10-K. Those three items come from income statements. Now I want you to leave the income statement and go to your company's balance sheet. In your balance sheet, turn to the liability side and look at the number called shareholders’ equity. This will include items like paid-in capital.

I don't even know why they break it out because it’s this tiny number that nobody ever cares about—retained earnings—and in addition, you might have if a company does buy back treasury stock that offsets it, but there’ll be a shareholders' equity line that is total shareholders’ equity. Some companies separate from shareholders’ equity an item called minority interest, which is equity in a cross-holding that you've consolidated.

Add those two numbers up for the most recent year and again for the year before that. So basically, you're looking at the most recent balance sheet and the last 10-K's balance sheet. So in the case of Amazon, this is the shareholders’ equity at the end of 2023 and at the end of 2022.

Now I have to warn you that some of your companies you might find a shocking number, which is your shareholders’ equity is negative! 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 will also include in your current liabilities the short-term debt and the short-term portion of long-term debts—three items. Since 2019, for many companies, you’ll also see lease debt both in the long-term section and the short-term. Add them all up from the most recent balance sheet and the balance sheet from the last 10-K, before the last 12 months.

So those are the bulk of the numbers. And then I ask you two questions. If you’ve never sat through my valuation class, the safest thing to say is “no” to these questions. But if you’ve sat through my classes, you know I make a big deal about how accountants are inconsistent on capitalizing expenses versus operating expenses. To me, the definition of a capital expense is it creates benefits over many years—R&D capital expense, customer acquisition cost for a platform company capital expense.

So because that’s going to skew your numbers and effective valuation, I’m going to give you a chance to capitalize those numbers because accountants treat those numbers as operating expenses. So if you want to capitalize R&D or customer acquisition cost or exploration cost or whatever you think is an item that should be capitalized, it’s right now being expressed—enter “yes.” But don’t stop there!

See this worksheet that says R&D converter? Go in and enter the numbers for your company, and these are the numbers you will need to capitalize R&D. First, you need to specify how long it takes for R&D to pay off in your company. Say you don’t know what to do. Well, to guide you a little bit, if you look at the bottom of that worksheet, I’ve given you a rough amortization period. Pharmaceutical companies, big energy companies, it takes a long time between the time you invest and oil comes out of the ground or a drug can be sold.

So enter the number of years—let’s say in the case of Amazon. Because it shouldn’t take 10 years for Amazon’s R&D to pay off, I say it takes about 3 years. Enter the R&D expenses for the most recent 12 months—the last 12 months. So in the case of Amazon, that’s $85.6 billion and then enter the R&D expenses for the last 3 years.

Here’s the problem with having only one annual report: you can get R&D expenses for only two years. In the case of Amazon, I 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 earnings.

So essentially, I’ll go through the kabuki dance of converting R&D into a capital asset. Right below that is another item that accountants used to routinely get wrong, which is treating a financial expense as an operating expense. I’m talking about leases. Until 2019, if you structured your leases in the right way, accountants allowed you to treat them as operating expenses and not treat them as debt. Retailers, restaurant companies, and even some airlines used that loophole to keep that off the books.

That was a terrible practice, and in 2019, in large segments of the world, that was ended because both IFRS and GAAP 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 them in debt, just leave it at “no” because you’re trusting the accountants.

You say, “But what if my company hasn’t capitalized leases or I don’t trust the accountants?” Then change this “no” to a “yes.” But if you do 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 in 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. In the case of Amazon, the table gives me lease commitments each year for the next 5 years and a lump sum for lease payments beyond year five.

Here’s what I will do: I will take those lease commitments, I will discount them back to today using the cost of debt to convert to debt, and I will take the lump sum and spread it out over time and convert that as well to come up with the debt value of leases. This is what I’ve always done well before accountants gave up 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 them as debt. So this option is available for you, even for companies where leases are converted to debt. If you choose to override the accountants' 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 still in the balance sheet because you got the shareholders’ equity. Go to the asset side, and you should see cash and marketable securities. Some companies bundle them together as just one item; in the case of Amazon, that’s what they did.

In some cases, it’s tricky; they might have short-term investments as a separate line item. Apple is particularly weird; 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 securities should be a slam dunk. Again, last most recent balance sheet and the balance sheet from the most recent 10-K.

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 names. 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 say, “What’s not counted?” The most important item is cross-holdings in other companies. What am I talking about? If you own 5, 10% of another company, it’s shown as a cross-holding, and that cross-holding is what I’m asking for here. What is it worth? And the reason I need that is when I do a traditional firm valuation based on operating income; I haven’t 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. Be careful and go to the footnotes and check out what’s in the long-term investments to make sure you’re not double counting. Some companies mark to market; the rule in accounting is if you hold it for trading, you gotta mark the market. Soft Bank marks the market; that’s nice because you have the market value of cross-holdings.

But for most companies, it’ll be recorded at book value. Now when you 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, you have a book value of 100 million. If you go to the industry averages I give you a price-to-book ratio 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 in both the most recent balance sheet and the balance sheet from a year ago.

That’s pretty much it for the accounting numbers! Then I ask you how many shares are outstanding. Now, if you look at annual reports or even 10-Qs, it’ll give you in the balance sheet a 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 takes taxes paid in your income statement— it’s an acral number— and divide by taxable income; it should also be in the 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 on 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 that says country equity risk premiums, as part of that worksheet, I also list for each country what the marginal tax rate is.

So if you have a company that is in Bangladesh, the marginal tax rate is 30%. You look up the tax rate and put that in right below the effective tax rate number. So at this stage, you pretty much got all of the numbers you need from financial statements. Now, do you see why I don’t forecast the three financial statements? Because the number of inputs I need to value a company is relatively few, and why forecast all those other numbers?

But here’s where valuation gets trickier. Why does it get trickier? Because you got to leave the comfort of past data and make 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, I ask you for revenue growth next year and operating margins next year. This is a pre-tax operating margin. I say, “Why separate next year from the next nine years?” Because I have ten years of forecast, and because you usually have more information about next year. Management often issues guidances for next year; you might have more analyst projecting for next year. You have more crutches, and you might want to use that to forecast the next year’s revenue growth and the next year’s margin.

This is also your chance if you have a company that is pre-revenue to enter a really small revenue number as this year’s revenue and enter a huge revenue growth in next year to get it 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 if you have a mature company.

The past might give you some sense of what will the growth look like, what will the margins look like for other companies; they might not. You might have to look at 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 two through five 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 importantly, what your story is for the company.

Then I ask you for a target operating margin, which is not what the margin is right now, but what will the margin be when this company gets through its growing pains? Here you might want to look at industry averages; in some cases, that will 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 and all those business levels we talk about will go into the target margin.

Then I ask you which year will the target margin be hit, and again, this is your chance to tell me how smooth the pathway to profitability is for your company. If you think it’s going to go from minus 10% margins to plus 20% in 5 years, that’s pretty speedy; I’ll take it!

So the choices you have: 5, 6, 7, 8, N. You can pick any number, and I’ll move it to that. You can even pick one, in which case I’ll move you to your target next year. Now the last two inputs are a little tricky because you might have never seen these items before. Remember, I’m growing revenues; I’m getting operating income, but to grow those revenues, I have to reinvest.

Now I want to know how much I need to reinvest to get that growth rate, so I ask for a ratio called sales to capital. How many dollars of capital do you get for every dollar of sales? Do 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, and I’ve shown you this company’s sales to capital.

But I also computed what’s called a marginal sales to capital 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 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, and capital.

That was easy, right? We’re almost done here! I ask you for two numbers to drive the discount rate. One is the risk-free rate and whatever currency you’re working with. In this case, I looked up the T-bond rate because I chose to value Amazon in dollars for the cost of capital load. It’s a green cell, and remember what I said about green cells being output cells?

Go to the cost of capital worksheet, and you will see that I give you choices there. There are four different ways you can compute the cost of capital. You can input it directly—maybe you know your company’s cost of capital; just say, “I will input,” in which case enter the number you want to use right below. You can estimate a detailed cost of capital. What does that require? You go through and enter what businesses—remember I promised you I would let you do this, the businesses your company’s in?

And again, these are all pull-down menus, so you can pick four, five, six different businesses your company’s in. Or you can pick, and there you have a choice— you can either use US industry averages or global industry averages— so I use that to get a bait for your company, the business mix you have for your equity risk premium. I ask you, do you want to enter your 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 all 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 up for 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 and the weights into a weighted premium.

So for both the beta and the equity risk premium, I give you a chance to add more nuance and more detail. Now if you look at the debt numbers, the first two are green cells, which means I'm taking what you inputted on the first page and just transferring them there. But I do need a cost of debt now, and if I can, I’d like a market value of debt.

So ask you whether you know what the average maturity—the weighted average maturity is. If you don’t have no idea, just enter zero, move on. But if you do, enter that number because I can use it to 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.

Okay, I say, “Okay, here are the choices: you can use to get your cost of debt.” So the choice with the cost of debt is I can directly input and say my cost of debt is 4.5%. Maybe for your company, you know the pre-tax cost of debt. The second is your company has an actual rating. If it has an actual rating, right below I give you a chance to enter what that rating is.

So if you click on the rating and you pull the menu down, you will actually see a list of ratings. Now, 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, and two is small or in an emerging market! So enter just one of the two; I’ll take care of the rest for you because here’s what will happen. As I thought it was a Ginsu worksheet, I will—if you go to this worksheet called synthetic rating—there you see the two that you entered. I take your operating income and divide by the interest expense to come up with an interest coverage ratio.

I convert that interest coverage ratio into a rating and the rating into a default spread and the cost of debt. I thought it was pretty neat; I mean, it’s not the greatest, not amazing, but you have a cost of debt. So you got a 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 it 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 just use an industry average, and it’s really, it’s four choices, but the first one is a direct input, so I don’t want to call it an approach to get a cost of capital.

So the third approach, you tell me what businesses you’re in; I’ll take a weighted average cost of capital of 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 I actually use 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, with Amazon, that it is a US company, that it’s about an average big company; it can’t be that far.

The median, then I will go to the table and look up for the US median 8.6% and use that. So direct input; detailed calculation with, you know, equity risk premium and betas, industry average, or a histogram. So that’s the number—let me go back and fix it, though, because I did originally use the distribution, so I want to leave it at that.

So you can see the numbers, right? The distribution gives me 8.6%, and there’s a loose end to tie up, which for many of your companies no longer might be an issue, and here’s what the loose end is: if your company has options outstanding, those options are an overhang on your equity. They will get exercised; they will drain your equity. A lot of an just add the option numbers to the shares outstanding; I don’t think that’s right.

I’ve got to treat them differently because options are not shares; they might become shares, but they might not. So if you have options, go in and again know, make sure you take the “yes” and make it into a “no.” But if I said “yes” here, now, I didn’t— Amazon did not have any options. Increasingly, US companies have shifted to restricted stock than any options.

But Tesla did; I would enter the number of options outstanding, which should be in the footnote which talk. So this is not something you have to invent; it should be in the annual report or the footnote. They have options outstanding; it’ll tell you how many options are outstanding, and it’ll often break them down into vested and non-vested. Count them all; hey, it’ll give you a weighted average exercise price for the options and an average maturity.

So all of that should be in your financial statements if you have options. So if you can’t find them, don’t freak out; many companies don’t. Finally, I 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, the spreadsheet is built— the default is I move your cost of capital towards a steady state. In the steady state, I compute by taking the risk-free rate and adding to that your risk premium.

Say it says plus 4.5%. That’s not true; it’s actually your risk premium; it’s modified— it’s 0.9 times the risk premium. So if your risk premium is 6%, I’ll move your cost of capital using. Why 0.9? Because if you look across all companies, that seems to be where the cost of capital ends up. So if you say “no,” then I will automatically move it there.

But you have the choice of saying “yes” and replacing it with your input. In the case of Amazon, as you can see, I’ve replaced that default with what I think is a more reasonable cost to Amazon at 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 fade.

Well, in the case of Amazon, I don’t think that’s going to happen; it’s got an amazing barrier to 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 it, the bigger your competitive advantage is, the more you can earn over and above your cost of capital. Don’t go crazy and enter 100%, but this number can be 10% if you have small competitive advantages and your cost of capital is 8%; it can be 12% for Amazon.

I’ve given it 15% healthy competitive advantages. The next question is about what we do in discounted cash flow valuation. Every discounted cash flow valuation, we value a company as a going concern. What does that mean? We assume your company, if it has a bad idea, bounces back and comes back as a company. But we do know that sometimes you don’t come back—there’s a failure risk. And especially if you’re valuing a company where that failure risk is high, I want to give you a chance to enter that failure risk directly rather than try to push it into your cost of capital.

But it almost never fits. So in the case of Amazon, of course, I don’t see failure risk, but if I say “yes,” to this failure R, then I ask you what’s the chance of failure. Now your first reaction might be, “I have no idea.” But to help you, I’ve created a failure rate worksheet where I give you different ways you can estimate failure.

I’ll confess that this is one number where you are going to have to make subjective judgments because even with my guidance, there’s not a whole lot you’re going to learn. So you put in a probability of failure. Then I ask you what happens if you fail. You say, “What do you mean what happens if you fail?” Well, you liquidate, right? If you liquidate, what do you get back as a percentage of value?

If you get back 100% of your value, then it doesn’t matter if you fail. So usually, in failure, you get back a smaller percentage than what you would get as a going concern, and in some cases, in extreme cases, you could set it to 0%—”I lose everything if it happens.” But basically, that’s what the 50% year is; it says in the event of failure, I’ll get half of my estimated value.

Now, when I use the sales to capital ratio to estimate reinvestment in my default, I assume that they’re contemporaneous. What does that mean? If I reinvest this year, I get my revenues the same year. Saying that never happens is 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 in.

So the way this will work is if you say “yes” and put in three, then my reinvestment in year one will be based on my revenue growth between years three and four, not the revenue growth in year one. So it’s an option to consider if you want to. I do also adjust your effective tax rate; remember the number you gave me? For most companies, it’ll be lower than the marginal tax rate.

I do adjust it towards the marginal tax rate as you get toward your terminal value. Why do I do it? Because, you know, when you pay an effective tax rate which is lower because you have tax deferral, eventually, you run out of deferrals—you run out of time. Your effective tax rate 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 in 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 losses. It’s called an NOL, and use it to offset your taxable income when 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. 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 your taxes.

Finally, the perpetual growth, the final couple of assumptions relate to the terminal value. One is, when I 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 we 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 the end of year 10, you might want to reconsider; this was especially the case when rates were really low in 2021. People said, “Well, maybe the rates will go up.” If you want to reconsider, just enter—if you enter “yes” here and you want to change that assumption, the default assumption of leaving the rates where 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 said 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? 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, your quirk in the tax law—you can keep 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 when I valued Apple; huge trapped cash.

And essentially, I brought that in because that trapped cash— you can’t leave trapped forever, so I said, “Okay, tell me how much trapped cash you have and how much 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 in this page. You’ll see a black box that says if you get value errors all over, which can sometimes—don’t freak out.

It’s usually because your option value spreadsheet has gone—has gone out of whack, and it’s a very easy fix. Go into the adjusted S-naught 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. You said, “The profit that looks like it’s too much profits or too little!” Hey, 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 14,667, and that’s about 15.23%. The price on that day, 169, was about 15.23%.

I used to leave the spreadsheet at this, but you know, one of the things I talk about valuation is how every input that we used comes from a story. So a few years ago, I created a page; so this page actually just takes the previous page and presents it in a different way.

So see these cash flows? They come from the previous page, but it also takes all of your inputs and gives you a chance to tell me why you think this company is going to have these inputs. So here for Amazon, I give you, with each input, the revenue growth, the margins, the sales to capital, the cost of capital—what my story is for why I do it. I’d strongly encourage you to fill in this worksheet because it’ll make you think about your inputs more and whether you want to leave them as is.

Two years ago, I decided to also show the valuation as a picture. Again, there’s nothing in this page that’s happening that you couldn’t have gotten from the evaluation output page, but it just takes the numbers and shows them as a picture. Maybe some people connect better with pictures. Incidentally, there’s a diagnostics page, and if you go to this diagnostics page, I would take a look at it—basically, I take your numbers and I take you through a series of checks.

I said, “Check your revenue growth number to make sure you’re not making your company’s revenues higher than the market. Check your dollar revenues. Check your margins; make sure that they’re within lines with what you’d expect the company to earn given the sector.” It says, “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 help you make that judgment is what’s happening to your return on capital.

And for the cost of capital and failure rate, just check those numbers again and make sure that they’re reasonable. So the diagnostic page is basically a page where it says, “Look, if you feel your value is too low, here are some of the things you can fix.” 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’ll 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 20, 2024, because I was doing the valuation in February 2024.

And it’s based on the implied equity risk premium computed 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 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!