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

Depreciation

Apologies for the delay inbetween posts.  Over the past few days, I have been doing far more meaningful things, like reading about this Capital Grille chef who stole customers' credit cards to fund his toy car hobby.  WTF?  How do you do something like cook steak (awesome, manly) but at the same time collect toy cars (creepy, childlike, possibly to bag kids)? Good thing he's out of work now; I've gone ahead and done everyone a favor by registering him here.  Hopefully he doesn't get hired at another Capital Grille where he might decorate my lobster mac and cheese with more than the four requisite cheeses.

Unrelated: in college, my first accounting professor had a peculiar middle-America accent that caused him to pronounce "depreciation" as "deprishiation."  I don't have much meaningful to say about to him, but realized this is my only chance to mention how annoying that SOB was, since today's post is about depreciation. 

First off, those of you that read about my distaste for CPDS' may have also closely read the keyboard shortcuts and noticed the SYD function on the bottom right-hand corner.  SYD stands for "sum of the years' depreciation" (read more about it here, because I don't feel like writing an accounting lesson) and is an alternative to the straight-line method that allows for a greater amount of depreciation in the early years of using an asset.  If you had asked me what SYD is prior to a couple of weeks ago, I could have only told you about a guy I know from Queens, but now I can tell you it's an excel formula written as follows: SYD(cost, salvage, life, period).  Which goes to show how shitty my education from Professor Deprishiation was.

Stepping back (I bet you heard someone say this today) from SYD to depreciation more broadly, you have probably spent some time learning about how to build out depreciation for any new capital expenditures in a model.  The most common method I have seen is the use of a "waterfall;" assuming you project five years in your model, there would be one line showing annual depreciation for the first year of capex, another line for the second year, and so forth.  So let's look at a simple straight-line case:

For every year (or period) of capital expenditures, you add another row to show the depreciation amounts in each ensuing year.  In the above example, these rows are totaled in row 21 to show the total depreciation amount (for new capital expenditures, only).  It's quite the bitch to update the depreciation waterfall anytime you change an assumption on timing (like 5 years to 4 years) or add another year into the projections (thus requiring another row to be manually inserted).  Even if you have automated your formulae to adjust for timing assumptions (as rows 14-19 in this spreadsheet are), you cannot avoid adding another row if you are extending these projections out to 2015.

The solution is to write a formula in one line that accounts for all of these assumptions dynamically; in other words, have a depreciation waterfall built up in one row of a spreadsheet.  Here's how (based on the example above):

1. Use rows 9-12 as assumptions
2. In cell G23, enter the formula =+SUMPRODUCT($G$10:$M$10,1/$G$11:$M$11,IF($G$6:$M$6<=G$6,IF($G$12:$M$12>G$6,1,0),0)) followed by ALT + SHIFT + ENTER
3. Drag the formula in G23 to L23
4. Don't forget to enter in a number (any number is fine) in cell M11 to avoid dividing by zero in your calculation

The above method is obviously an array formula, where calculations are done using corresponding cell ranges of equal size; so the SUMPRODUCT function is really operating as G10 x (1/G11) x a + ... + M10 x (1/M11) x a, where "a" is an operator that determines whether each depreciation amount should be counted for the current time period (the current column). 

The logic for "a" is based on the year of the given capital expenditure and its last year of depreciation.  So, if G10 x (1/G11) is based on a capital expenditure from year 1 (column G) that depreciates through year 5 (column L), then "a" will equal the number 1 in each of those years.  Otherwise, "a" will be 0, which multiplies against the depreciation amount to make it zero, thus excluding it from the depreciation total.  A good example of how this calculation works can be found in K14 through K19.  If K14 belongs, our SUMPRODUCT formula multiplies it by 1, and if not, then by 0.  Thus the SUMPRODUCT formula is doing the work of the SUM formula in row 21, in addition to the calculations for the depreciation waterfall directly above.

Thus, you now have the ability to insert another year in your model, and allow for depreciation to be calculated precisely in one line, based on the assumptions you enter and without the need for manual adjustments.

So why have I not told you anything about sum of the years' depreciation, even though it's just calculated in the same waterfall format with different numbers for different years?  To be honest, it's complicated as shit to write a dynamic formula into one line.  So here it is for cell G23: =+SUM(IF($G$6:$M$6<=G$6,IF($G$12:$M$12>G$6,SYD($G$10:$M$10,0,$G$11:$M$11,IF($G$9:$M$9+IF($G$9:$M$9>F$9,-1*F$9,RANK($G$9:$M$9,$G$9:$M$9,0)-RANK(G$9,$G$9:$M$9,0)+1-$G$9:$M$9)<=$G$11:$M$11,$G$9:$M$9+IF($G$9:$M$9>F$9,-1*F$9,RANK($G$9:$M$9,$G$9:$M$9,0)-RANK(G$9,$G$9:$M$9,0)+1-$G$9:$M$9),1)),0)),0)

I'll explain it some day when I feel like it.

-F-One

What You Need to Know About Keyboard Shortcuts

In today's New York Post, I saw this article describing the acrimonious departure of a female cook from one of Jean-Georges' restaurants.  The cook recently filed a sexual harassment lawsuit, and as in all such cases these days, the defendant "went so far as to text her a picture of his manhood."  At the risk of accusations that I think about this topic frequently, I am totally confounded by the picture-messaging of manhood becoming a rapidly growing trend (at least in professional sports).  Just in the past year, Brett Favre, Martellus Bennett and Greg Oden, among others, have all been accused.

They should call them Wang-ler jeans.

What the hell?  Whatever happened to calling/texting a girl? Why do people engage in this nonsense?  Who even enjoys it (other than this guy)?  Let's assume that your target girl is head-over-heels obsessed with you. What exactly do you gain by sending a cell phone shot of your unit to her? It's not like her cell phone shapeshifts into your dong when she opens up the picture, so the chances that she'll enjoy it are minimal.  Thus, let's assume the other possible scenario that the girl is NOT head-over-heels obsessed with you. No matter how well-equipped you are, at least one of these outcomes will befall your dong photo: 1) topic of ridicule among her friends/coworkers, 2) topic of ridicule among your friends/coworkers, 3) police evidence.  There are no winners in autophallography.

So what do dongs have to do with Excel, you ask? Nothing, unless you use your dong to type. But I recently experienced the Excel equivalent of receiving a CPDS ("cell phone dong shot"), which was a reader sending in a photo of keyboard shortcuts taped to their cubicle.  Why do I even bother to make the comparison?  Like I said, the CPDS doesn't help the girl because the phone isn't morphing into a penis.  Likewise, keyboard shortcuts on the page aren't typing themselves out for you.  In order to get any benefit from shortcuts, you must have full, immediate access to them.

=IF(dongshot=1,0,1)

This is the end of the entry, and you've learned nothing meaningful about Excel.  The next post will be overly bland and information-filled to compensate.

-F-One

Dating in Excel

You might think this entry is about the time I tried to pick up Mrs. F-One with some cool OFFSET formulae.  But it's not.  I'm just trying to exhaust every known Excel pun in the universe, and I'll warn you that there's another one coming before this post ends.  The "dating" to which I refer is actually the labeling of time periods in models, so 2009, 2010, 2011, etc.  For example:

These are dates?  They tell me nothing!

This is how you lay out dates in your model if you are a child that colors with crayons.  By the way, if you watch a lot of TV, you have probably seen some ads for the movie Ramona and Beezus, during which Ramona gets props for "coloring outside the lines."  Supposedly that saying is analagous to creativity or being a free spirit, but what the hell does it even mean?  Regardless of your personality/disposition, if you fully color an entire illustration, don't you NEED to color outside of the lines in order to reach every area of the page?  Or does it mean scribbling colors wrecklessly and ignoring the lines that separate distinct objects and different colors?  Sure, maybe you color that way and happen to be a free spirit, but the only thing it actually guarantees is that you suck at coloring, and might be developmentally handicapped.

In next summer's sequel, Ramona hides from her Asian foster parents, who decide her inability to color "within the rines" has earned her bitch ass two months at math camp.

Let's move beyond the crayon age.  Your computer's microprocessor performs billions of operations per second, so try letting it do a bit more than count from 2009 to 2015:

1. In cell B6, enter the number of months per projection period (obviously 12 for an annual model and 3 for a quarterly model)
2. In cell B7, enter the last date for which actual/historical numbers are available (12/31/09 for simplicity)
3. Starting in cell F6, enter in the first time period in your model (12/31/09 is fine), with custom number format mmm dd,
4. In G6, enter the function =+EOMONTH(F6,$B$6); this function automatically returns the last day of a given month in a given year based on an input date and a specific number of months following the input date
5. Drag the formula in G6 through to L6
6. In F7, enter the function =+YEAR(F6)&IF($B$7>=F6,"A","E"); the YEAR function identifies the year number of a given date, and the IF function determines whether the projection period is historical/actual (A) or projected/estimated (E)
7. Drag the formula in F7 through to L7

Why do we set up the dates in this way?  One reason is that you can easily show the fiscal year end (not always 12/31) for a given company, and which dates are actual vs. estimated, without having to manually adjust any of the labels.  You also have a good way to easily produce annual or quarterly dates in a model.  Further, using the EOMONTH function ensures that you precisely calculate the last day of any given month, rather than occasionally erratic calculations (December 30, anyone?) if you simply add 365 or 365.25 to the last date.  Lastly and most importantly, listing the actual end dates for each period will allow you to do lots of date-relative calculations in your models, which will be discussed in future posts.

The format I've suggested above is not the ultimate date format for financial modeling, but should provide you a base to add further bells and whistles.  It will also prevent your seniors from victimizing you with annoying questions like, "Which periods are actual?" and, "What is the fiscal year end?"  Or as the senior bankers might threaten you, "WHAT IF YOU SHOWED THIS TO A CLIENT?!?!"  After all, clients have been known to open every pitchbook and instinctively identify minorly unclear date formats.
 
Sounds like a lot of fun right?  If you think dating in Excel is fun, just wait until the lesson on punishing!
 
-F-One