Trading Comps Case Study - Diluted Equity Valuation
- 09:15
How to calculate the enterprise value to LTM EBITDA multiple for Coca-Cola using Excel and Felix.
Glossary
10Q Diluted Shares EBITDA LTM LTM EBITDA net sharesTranscript
Now we're going to calculate the enterprise value to LTM EBITDA multiple for a couple of the case companies. The first one is Coca-Cola. So I'm gonna build this in Excel, but I'm going to be referencing Felix and I've split my screen. So Excel is on the left and Felix is on the right. So I'm just gonna start with the company name and I'm gonna type that in as Coca-Cola.
And then what I'm going to do is pull in the basic shares outstanding. Now we need to make sure that the basic shares outstanding comes from the latest financial filing. So I'm gonna go to Felix on the ran side here. So I'm doing this analysis as of May 9th, 2024. So I'm gonna be using all the information available up to that date if you're doing the same case. But at a later date it may be that newer information or more recent information is available. So I'm gonna go to Felix and I'm gonna type in Coca-Cola.
And you can see that there are actually three Coca-Cola companies. Two of them, the one quoted in London and Coca-Cola consolidated are the bottling operations. We want Coca-Cola company or the ticker KO as that's the main holding company. And you can see here the most recent filing is the 10 Q filing was filed on May 2nd 2024 and the 10 K the year end is on February 20th 2024. Most of the numbers we need will be from the 10 Q for the enterprise value to equity bridge. However, because the 10 Q filing is a summary and a much shorter document than the 10 K. There are some situations where we'll need to go to the 10 K filing because that information is not included in the 10 Q filing. So the basic shares outstanding. I'm gonna go to the most recent filing, the 10 Q. And on the very front page down at the bottom, you'll see they will list the shares outstanding as of April 30th. And the reason I'm taking this number and not the number in the balance sheet is that for US companies that file at the SEC, they will put the shares outstanding, not at the balance sheet date, but at the filing date. So the balance sheet is the end of March, but the filing date is the end of April. And so I get a more accurate number by taking this share count. So I've just clicked on that and I'm gonna paste it in here and I'm going to widen my screen a little bit. And that's in actual number of shares. Now you can, if you want to make it a little bit shorter, just put an equal sign and then at the end divide by a million and that will give you the number of shares in millions. Now if you want to keep up, you can rename these annotations. So I'm gonna do shares outstanding just so on my annotations. I can easily see what each item is. So I've got my shares outstanding there. And then what I'm going to do is I'm gonna pull in the number of options.
Now the number of options, ideally I would get this from the 10 Q filing, but unfortunately often they don't include that information in the quarterly accounts. And just looking at the little sections here, there doesn't seem to be anything related to share-based compensation. So what I'm going to do is I'm just going to do a quick check and I'll search for share based, yep, nothing's coming up or I'll do share options. Nothing's coming up. So this means because they don't have this information in the 10 Q, what I then need to do is I need to go to the 10 K filing. So I'm gonna go to the 10 K filing and I'm gonna look under sections. And normally what I'll do is I'll find the stock-based compensation plans. You can see that. So I'm gonna jump to that and it will list them here. So I'm gonna go down and look for the tables where they've got the number of options. And you can see here we've got the option information here. It's more out of date than the share count, but there's nothing we can do. We just don't have access to that information. Now we've got two metrics here. We've either got outstanding or exercisable. And you can see the expected divest is much closer to the outstanding. The expected divest number is actually a fairly recent disclosure. Previously we never really saw that. And as a general rule, people would take the outstanding options. The difference between the outstanding and exci of options is that the exer exercisable are legally able to convert today, the outstanding are options, some of which are exercisable, some of which are still within the vesting period. The view we take is that if these options are in the money, even if they're within the vesting periods and not exercisable today, because the market expects them to be exercised in the future, it's more reasonable to take the outstanding options. And that's what I'm going to do. So I'll take that standing of options there. And what I also want to do is pull in the option strike price two and the option strike price is just here, the 48.52. And I'm paste that in. And then what I'm going to do is I'm gonna put in the current share price. And the current share price if I go back to the front page of Felix for Coca-Cola, I will get the share price. Now I need to take yesterday's closing price, which is May 8th, and that's $62.85. So I'm just gonna type that in 62.85. And you can see that these options now are strongly in the money. So even though they're not able to exercise today, because it's very certain they will exercise in the future, the assumption is that they are kind of baked into the valuation. So I'm gonna calculate the net new shares and I'm gonna do a max function just in case the share price changes the maximum of the current share price minus the strike price. So that's the profit per option divided by the share price times the number of options or 0.
And that gives me my shares outstanding. I should also probably make that blue because that was typed in and I probably should make that two dec places 'cause it's a share price. Now let's go back because actually there's some more information. So we've included the stock options, but there are some other items, performance-based share unit awards, which are usually linked to some performance metrics. Some people do, some people don't include this. So I would just check in with your associate or vice president about what they wanted to do here. So I'm gonna put down RSUs performance-based, and sometimes these are known as PSUs and I'm just gonna type them in as the non vested. Now notice that these are in thousands. So I'm gonna put this number in and when I pasted it, pastes in the link as well as the number. So it's a super way of producing an audit trail. So I'm just going to make sure that I've divided that by 1,000. And just to be clear, I also divided that by a million. And then I've also on the right, got the restricted stock based unit award. So this is just time. You just need to stay with the company for a certain amount of time and you'll get these awards. I'm gonna put RSUs time based here, and it's very normal to include these. Pretty much everybody will include these. It's just the performance stock awards that you may find some bankers don't want to include, but I would double check. So we've got those two items there. We've got the RSUs and the PSUs. So now what we can do is we can calculate the diluted shares outstanding, and that's going to be the sum of our basic shares outstanding, plus the net new shares from options, plus the performance-based RSUs, and the time-based RSUs all added together. Finally, I can multiply that by the share price to get the diluted market capitalization.
So this gives us a value for the equity of Coca-Cola of $271 billion. If you're not getting the same number, it's probably because you are using slight different information. If you're doing this analysis at a different date.