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
