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

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