Stock Option Dilution (Part II)

I've been reviewing a large amount of resumes recently and noticed a common trend: the word "avid" is used almost universally.  At least in the resumes I have seen, but I guess that wouldn't necessarily be universal.  (Unrelated: isn't it inaccurate when people attempt to say something is the best and define it as best in the entire universe?  What if there's something better within the multiverse?!?)

Anyway, do people even use the word "avid" when speaking everyday English?  Maybe if you're a birdwatcher, but no one I know in real life says they are an avid golfer, swimmer, (rusty) trombonist, etc. I have no idea whether some d-bag business school resume reviewer started the "avid" trend, or whether all business school, finance and consultant applicants are so unoriginal that they just CTRL + C CTRL + C CTRL + C.  FYI, "avid" comes from Latin "avidus," meaning "to crave," so once I interview one of these avid fools, maybe I'll ask how their unplanned pregnancy is going or whether they avidus some penis.

Speaking of unplanned pregnancy, a long time ago I gave birth to the most efficient tranche-by-tranche treasury stock method calculation in the multiverse.  Those of you who have spent time reading 10Ks know that oftentimes several tranches of options are listed out.  If you didn't know any better, you might calculate dilution for each tranche separately, as in the example below:

Accurate, yes.  But not exactly efficient.  What happens if you need to scale this calculation up to include several companies?  For example, let's say you are doing comps and need to calculate equity value for 10 different companies on an input sheet.  You could theoretically copy the above block of calculations ten times over, but then how will you get all of the values onto an output sheet?  Manually linking?  That would be dumb.  Also dumb (but not as much) would be creating 10 different sheets, one for each company; waste of memory, particularly when you start to use INDIRECT for the output linking.

I always suggest when calculating comps to use one sheet, and have each column represent one company.  This way, adding companies just requires a simple CTRL + R and replacing the relevant data.  Having all the data on one sheet also eliminates potential for error (i.e., if you have 10 sheets, adding a row on one sheet will screw up the output sheet that uses INDIRECT).  There are many other reasons as well, but just believe me for now.  Having said all of that, here is the way I would lay out options for a comp:

In cell C28, insert the formula {=SUM(IF(C5:C14<C2,C17:C26-C17:C26*C5:C14/C2,0))} and don't forget the brackets, since this is an ARRAY formula.  As we have discussed in the past, such a formula will do each calculation along a range of cells; in other words, you can interpret this formula as the single calculation =IF(C5<C2,C17-C17*C5/C2,0), followed by CTRL + D to drag it down for 9 more cells, followed by summing all 10 cells to arrive at total option dilution.

While other young people waste their time by running around like idiots with broomsticks between their legs, I again incrementally increase your Excel efficiency.  Although I will take back my criticism if these losers sharpen their broomsticks and turn it into a death sport, which would at least offer the side benefit of fatally injuring some of them.

-F-One

Stock Option Dilution (Part I)

When I first started banking, I had no idea that "fully diluted" equity value existed.  You may be wondering how this is possible after they put you through a training program.  See below:

There was also a game in training (or I should say while training was going on) that involved trying to fly penguins across the screen for as long as possible.  Why would you pay attention to a 2-year banking flame-out teaching (incorrect) finance concepts when you have that type of entertainment?  Whatever.  I still learned everything, including the fact that market cap. on Yahoo! finance is in fact not fully diluted.

So what does "diluted" mean?  Bankers use the term as often as MBA students ask stupid questions to feel valuable, so there's no way to tell by context.  For this post, when additional shares of a company's stock are issued, they "dilute" the ownership of existing shareholders.  Market cap. does not account for this dilution, as it is calculated by multiplying share price x basic shares outstanding. So when a company has issued options, any new shares from option exercises would be added to the basic share count in order to calculate fully diluted equity value.  The common method of accounting for these potential dilutive effects is the treasury stock method, which states New Shares = Options - (Options x Exercise Price)/(Current Price).

Using a blank spreadsheet, assume a company employee has 100 (cell A1) options at a $10.00 (A2) strike price, and the current stock price is $15.00 (A3).  In this example, the employee will exercise his options to buy 100 shares, as he can immediately sell them for a net gain of $5.00 per share.  The company will then buy back as many of those shares as possible at the current price to minimize the dilutive effect of the option exercise.  Here are two ways to lay out the treasury stock calculation:

1. =+IF(A3>A2,A1-A1*A2/A3,0)
2. =+MAX(A1-A1*A2/A3,0)

In case you were about to call me stupid or Captain Obvious for telling you about the above calculations, these are primarily meant to serve as groundwork for my next post, which will discuss more detailed option dilution calculations and why the DJ Hero 2 ad campaign makes me want to vomit.

-F-One