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

Worst Venture Capital Fund Names in the History of the Universe

Sorry for no meaningful posts this week.  I've been busy with stupid shit like cold-calling companies that may or may not have more than $500,000 in revenue.  Miserable.  But the one benefit of looking at shit companies is finding out who their shit investors are, and making fun of them.  Without further ado, I present the Shitty VC Name Hall of Fame:

I'm really well-known for starting a VC firm that invests in best-in-class fluffers.

1. Fluffco - Seriously? They are "formed by angel investor, Irving J. Levin. Mr. Levin is a well known entrepreneur in the Portland, Oregon vicinity."  Kind of reminds me of those well-known professional basketball players in the North Korea vicinity.  Good luck fluffing, Irv.
2. Crossbow Ventures - When I fundraise, I want people to envision medieval weaponry, because the threat of me using it to kill you is the only way you will invest in this stupid piece of garbage.
3. Techxas Ventures - Technology + Texas = Techxas.  Brilliant.  You know what's even more brilliant?  Listing "Unknown" companies on your portfolio page.  Hopefully Wal-Mart gets the memo and does their next rollback commercial with "unknown" items.
4. Carrot Capital Healthcare Ventures - Carrots are for pussies.
5. Double D Ventures - I can't even find a website for this firm, probably because someone at VentureSource made it up as a joke on me.  They may have funded Fluffco at some point though.

Sadly, this list isn't a real Hall of Fame.  I just took it from the last 3 companies on the list that I have been assigned to cold-call.  Jealous?  I thought so.

-F-One

Retirement

I was thinking about retirement accounts today, because obviously the success of this blog has enabled me to retire early.  One question that arose: does it ever make sense to take early distributions at the cost of taxes and early distribution penalties?  Wait?  When can that make sense?  Certainly, in a vacuum, you should never distribute retirement savings prior to retirement: $1,000 of retirement withholdings will only be about $300-500 after taxes and penalties, and you just gave up a bunch of money that would otherwise be earning some type of yield, rather than being in someone else's hands.

Do I need to tap into my retirement fund?

So that scenario is easy to quantify.  But in a world of tradeoffs and opportunity costs, there are definitely going to be other scenarios where it might make sense for you to distribute early.  Let's look at the most common one: you have some credit card debt and aren't paying it off for a while.  If you did have unlimited funds idling in your savings account generating 1% interest income annually, then you would be stupid not to pay off your AMEX, which could be pushing 18% interest expense annually (I'm keeping these percentages very simple for illustrative purposes).  If you did not pay that debt off, you'd be losing 17% a year on your capital for no reason.  Simple.  Then again, very few people have unlimited funds.

If you are on the opposite end of the spectrum and have no savings to speak of, and no sufficient earnings to pay down debt, that is the point at which you might consider dipping into a retirement account to pay off your debt (let's pretend that loans collateralizing your 401(k) do not exist).  Similar to the savings account example, your retirement account might be earning 7% a year vs. your debt costing 18% a year.  All you need to do is make sure your foregone net interest costs offset the taxes and penalties from distributing prematurely.  The important metric is the time period over which you don't pay off the debt.  At some point  after you hold the debt long enough (maybe in 10 years), the dollar amount of interest costs will be so high that you will have wished you dumped the retirement savings to pay that debt off.  So here is a very basic decision framework that will tell you, for every dollar of debt, the payback time above which you should be touching that nest egg:

1. Let a = retirement account balance
2. Let b = % penalty from early distribution (assume 40% for taxes and 25% for penalties, total of 65%)
3. Let y = % annual yield on retirement account (7%; and assume post-tax to keep the formula simple)
4. Let i = % annual interest on credit card debt (18%)
5. Let d = amount of debt
6. Let n = number of years of not paying off debt above which you should simply take retirement funds to pay the debt (this is the variable that we hope to solve for)
7. Conceptually, to distribute retirement funds early, the following equation must be true: [d(1+i)^n-d] - [a(1+y)^n-a] < ba (this states that the foregone interest costs over the given time period are less than the early distribution penalty; in other words, DISTRIBUTE!)
8. Remember that if you take the early distribution of "a," you receive (1-b)*a; this is thus the amount of debt you can pay off using the distribution proceeds, meaning d = (1-b)*a
9. Resubstituting and simplifying, you get a(1-b)[(1+i)^n-1]-a[(1+y)^n-1] < ba
10. Reduce this formula to: [(1+i)^n-1]-b[(1+i)^n)-1]-[(1+y)^n-1] < b
11. Then: (1+i)^n - b(1+i)^n - (1+y)^n < 0
12. Then: (1-b)(1+i)^n < (1+y)^n
13. Take the log of both sides to get: LN((1-b)(1+i)^n) <> LN((1+y)^n)
14. Then: LN(1-b) + n*LN(1+i) < n*LN(1+y)
15. Ultimately: LN(1-b) / (LN(1+y) - LN(1+i)) < n
16. In spreadsheet form, enter "b" into C2, "y" into C3, and "i" into C4, then calculate using the formula =+LN(1-C2)/(LN(1+C3)-LN(1+C4))
17. Using the assumptions from 2-4, we have n > 10.73 years

In other words, as long as you can pay off your debt in 10.73 years or less with savings or earnings for this scenario, you should NOT be taking anything out of your retirement account.  If you are in the unfortunate position of not having sufficient savings or earnings to pay down the debt in 10.73 years, then think about doing something.  Practically speaking, unless you have unusually light penalties for early distribution, a really bad money manager getting you no returns on your retirement fund, or ridiculously high interest rates on your debt, you probably shouldn't think about using your retirement money before it's time.  But it could happen...

This post is dedicated to Antonio Cromartie, whose spending habits and child support payments (despite his large salary) make him a candidate to evaluate the retirement account tradeoff.  You're welcome for the formula.

F-One

Following up yesterday's post on coloring and formatting, here are some additional thoughts.  First, what do you do after you have color-coded all of the text in your cells, and then need to paste the spreadsheet into a presentation?  From experience, a lot of senior people get irrationally upset if the text is color-coded rather than all black.  So you could always select the range you are copying, change all the text to black, paste into the presentation and then "undo" in Excel to preserve your color-coding.  Or maybe now that you know all the shortcuts to color-code a spreadsheet within a few seconds, you don't care.  But you should care that traditional copying and pasting using Excel 2007 results in gridlines being pasted, often when you don't want them to appear.  Another big formatting annoyance.

This will make sense later...I promise

What many people do not know is that Excel can copy a selection as it appears on a physical printout.  Moreover, most printers these days have a special setting that is loosely defined as "print all text black."  The command is in different places depending on your specific printer and software, but the idea is that when you physically print a document, borders and backgrounds will print in color, while all text will print in black.  Logically, if your default printer had these settings, copying and pasting would yield the same results, eliminating the problem of having to recolor and uncolor text.  At the very least, you can paste things without the gridlines.  Here is what do (skip to step 5 if you don't care about text colors):

1. CTRL + P for print
2. ALT + R for properties...
3. What happens next differs for each printer, but typically within the "advanced" settings, you will find an option for printing text as black.  Select that option.
4. If you use CTRL + C for copying a range and pasting as a picture, you will find that the pasted image still maintains colors in the text.  The reason is that Excel is copying what appears on the screen, including gridlines, which is largely painful if you like seeing gridlines but need to remove them for every paste-in of a presentation.
5. To address the CTRL + C issue, select the range you want to copy, and then type ALT, H, V, A, C, P, ENTER, which opens up the home tab on the ribbon, then the paste menu (not intuitive when you are trying to copy something), the "As picture" option, the "Copy as picture..." option, and finally the "As shown when printed" option.  By doing this, you have told Excel to make a copy of the picture exactly as if it were printed.
6. Now, in Word or Powerpoint, you can hit CTRL + ALT + V for paste special (notice that often in the 2007 suite, ALT + E +S no longer works) and select the enhanced metafile option.

Voila!  Pasted with no gridlines and as black text.  Bonus point on colors: In Excel 2007, you can transfer color palettes easily by saving them and them copying them out of the default folder.  ALT + P for page layout, then T, C for "Colors" followed by ALT + C for "Create New Theme Colors..."  If you name this theme, it will show up in your default color palette folder as an XML file; on my computer, the folder is C:\Users\Ron Mexico\AppData\Roaming\Microsoft\Templates\Document Themes.  You can then freely copy these palettes to the same folder on other computers, which will cause them to appear on your Excel palette menu.

That also works for the other Microsoft applications, which should make it clear to you why the Presentations group used to call me MichaelSoft Jordan.  Oh shit!

-F-One


 

Coloring Linked Cells

No matter what firm you work at, best practices and common sense dictate that you color hard-coded numbers, formulae and links to other worksheets using distinct colors.  We have discussed the first two, which are easily accomplished using F5, ALT + S to select either constants or formulae (Microsoft says formulas; they're apparently interchangeable).

Unfortunately, Excel does not have a built-in command to highlight all cells linking to other worksheets.  That means if you are working late at night on the Sunday-Monday morning of Labor Day weekend and need to color all other-sheet links to green in a spreadsheet with thousands of rows mixing numbers, formulas and other-sheet links, YOU ARE FUCKED.  Until now.

With the help of my some times enemy and other times friend visual basic, you can do what F5 does, but to select formulas with links to other sheets.  Remember, I am not a VBA expert, but I know how to use Google and combine other people's VBA code.  Here is what to do:

1. ALT + F11 to open up Microsoft Visual Basic within Excel
2. ALT, I, M to insert a new module
3. Within the module code editing window, paste in: Sub FormatOtherSheetLinks()
   Dim TestRange As Range, C As Range, MyRange As Range
   Dim FirstAddress As String, SheetString As String
   Dim wsh As Worksheet
   
   On Error Resume Next
   Set TestRange = Cells.SpecialCells(xlCellTypeFormulas)
   On Error GoTo 0
   
   If Not TestRange Is Nothing Then
   For Each wsh In ActiveWorkbook.Worksheets
   SheetString = wsh.Name & "*!"
   SheetString = Replace(SheetString, "'", "''")
   With TestRange
   Set C = .Find(SheetString, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False)
   If Not C Is Nothing Then
   FirstAddress = C.Address
   If MyRange Is Nothing Then Set MyRange = C
   Do
   Set C = .FindNext(C)
   If Not MyRange Is Nothing Then Set MyRange = Union(MyRange, C)
   Loop Until C.Address = FirstAddress
   End If
   End With
   Next
   If Not MyRange Is Nothing Then MyRange.Select
   End If
   
   Set TestRange = Nothing
   Set MyRange = Nothing
   End Sub 
4. F5, ALT + R to run the module on each desired sheet

Notice that all the formulae with links to other sheets are highlighted.  See for yourself by hitting CTRL + ~.  Now, all you need to do is color the highlighted cells!  You can also hit ALT, F, E to save the module for future use.

So how did this work?  Imagine that a human was doing this exercise.  He could use CTRL + F to find each cell containing links to other sheets by searching for "!" within each formula.  He could then select each cell that contained "!" cumulatively (using either the keyboard and SHIFT + F8 or the mouse and CTRL).  Excel does all of that almost instantaneously.  The only problem would be if you write footnotes like, "Assumes leverage of 3.5x!!!!" because that's getting highlighted too.  Anyway, imagine the spreadsheet is huge and contains a mix of constants and other formulae.  That could have been a 15-minute exercise, but will now take 2 seconds!

Speaking of taking 2 seconds, I decided to write this post while thinking of ways to satisfy Mrs. F-One.  Happy Labor Day Weekend.

-F-One

Millions of Stupid Abbreviations

How do you abbreviate "millions" when you are writing about millions of dollars but have limited space on your pages?  Most bankers tend to use "mm" or "MM" as their abbreviations of choice.  But how is this possible?  There is only one "m" in the word!  And somehow, for consistency, the "mm" methodology is shared in abbreviating "billions" as "bb" or "BB," which becomes really confusing if you ever have to talk about billions of dollars on the same page as big balls or busty brunettes.

As a starting point, think about metric nomenclature, in which "K" stands for "kilo" (1,000) and M stands for "mega" (1,000,000).  After all, that's why you pay for champagne rooms by dropping several "k" rather than several "thousand."  And if you're talking about electronic data storage, 1,000 bytes (8,000 bits, but that is a whole other conversation) could also be called 1 kilobyte, or 1 KB.  Therefore, $1,000,000 would really be called 1 megadollar, or 1 MD.  But that's too ambiguous, since so many people in finance call themselves MDs!  What about 1 M$?  That is my suggestion if you want to be consistent with the metric system (which rich scientists also abbreviate as M$...confusing).  But practically speaking, people will whine about not having a currency sign as the leftmost character in an expression of numerical, monetary amounts, unless they are European.

I prefer the Euro sign on the right of the number because my beret constricts the blood flow to my brain.

Since I reside in America, the currency sign MUST be the leftmost character in the expression.  Thus, the best compromise with the metric system for abbreviating $1,000,000 appears to be $1M, as in $1 mega.  Then, theoretically, $1,000,000,000 should be 1 gigadollar, or $1 giga shortened to $1G.  But I've never seen that in any presentations before.  Why?  Because people are stupid, and will just ask you whether $1G means $1,000.  Now how do you feel about people that deal with billion dollar transactions not knowing the difference between $1,000 and $1,000,000,000?

So how did we ever get to mm, MM, bb, BB, etc.?  I've heard lots of theories about these, so let me highlight them (and how stupid they are) for you.

1. 1MM should represent 1 million because M is the Roman numeral for 1,000: A lot of people actually only know the first 9 digits of the Roman numeral system and assume this is true.  Really?  How do you write "20" in this system?  XX!  And "XX" doesn't mean "100," so why the hell would "MM" mean "1,000,000?"  Hopefully you told that big oil and gas company that their acquisition synergies would be $50MM next year, since you seriously meant $100,000, or 2 secretaries.  Note that drawing a line above (overlining?) the two Ms means multiplying, so that would theoretically work.
2. 1mm should represent 1 million since mm is the abbreviation for megamillions:  What?  First of all, I have heard many people say this, and it makes no sense.  Maybe they mean multimillions, but megamillions means either trillions or the lottery.  And "mm" means millimeters.
3. 1bb or 1BB should be used to represent 1 billion for consistency with abbreviations meaning 1 million: False.  Ah, the compulsive/psychotic fixation of bankers to make all formatting consistent, even if doing so makes no logical sense.  Neither bb or BB make ANY sense.  It's not even like we say begabillions or bultibillions.  Maybe they mean bunch(es) of billions like a fucking cereal.

Realistically, the best we can probably do is $1M and $1B for $1,000,000 and $1,000,000,000, respectively.  And guess what?  $1,000,000,000,000 would be $1T, which coincidentally matches the metric prefix of "tera."  But to be clear, my personal preference would be scientific notation; I suggest custom number formatting of $0.0_)E+00; ($0.0)E+00; "- " or you could always use the old-school method of x 10^a.  Keep in mind that if you are using Word, you can superscript selected text using CTRL + SHIFT + = and subscript using CTRL + =.

Lastly, what do you call someone obsessed with numbers in the quadrillions?  A petaphile!!!

-F-One

Degrees

Are you one of those people that graduated "summa cum laude" from college?  Would you be able to tell me what "summa cum laude" means?  Because it really just sounds like a retarded black guy informing me that "the current summer's semen ejaculations are excessively noisy."  Drawing an analogy to the banking bonus, summa cum laude for college graduates is the equivalent of "top bucket" in an analyst class, followed by magna cum laude, then cum laude, and lastly all the losers that don't get bona (I know, I know, the economy...).  And yes, "bona" is the appropriate plural for "bonus," and should be a daily reminder that every Latin-based term in popular use could just as easily come from a '70s black porno.

What kinda degree you got?

So for any of the people that throw around "cum laude" or some variation without knowing the meaning, here you go: "cum laude" is the latin term for "with honor."  And that explains why those that graduate "with honors" do so "cum laude."  Make sense?  It should, since loud ejaculations are how every man becomes honorable in college.  I just don't even want to know what my black readers (they exist!) have to say about this.

I raise the topic only because a CFO recently asked me whether I have a Ph.D. in Excel, which eventually led to him asking whether I have any types of cum on my undergraduate degree.  No, but that would probably change if I left my degree out while you watch me awesomely build a 5-year quarterly model in only a few minutes.

Anyway, today's lesson is short.  The DEGREES function converts radians into degrees.  So you can do quick conversions using =+DEGREES(a*PI()), where "a" is any number.  Maybe you'll get lucky and be able to use it on a sell-side for these guys.  Sorry, tomorrow's post will be more educational.

And, just remember, if someone brags to you that they graduated "cum laude," that's like bragging that they won a bronze medal or other type of third place (shit) distinction.  Good thing these types of events aren't done "to-the-death," otherwise we would never experience the pleasure of people bragging about 2nd and 3rd place.

TSM,

-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

Modeling Framework

I used to have a superior (in title only; he was a dumbass) that commonly used the phrase, "level-set."  As in, before we go any further, let's do some level-setting with a 30,000 foot overview.  Because, you know, commercial airline pilots always use construction tools.  He really was a dumbass.  Anyway, taking a page out of his dumbass playbook, I'm using this post to do some level-setting of my own with regard to financial modeling and all of the important pieces.  Thereafter, I can use future blog entries to heavily rain down Excel knowledge for your continued benefit.

Most readers have familiarity with the basic layout of a financial model.  For those lacking that familiarity, let me borrow a trick from crappy resume-writing and just tell you the objective: to show a company's income statement, balance sheet and cash flow statement projected into the future, with the ability to change assumptions for various accounts and display their impact on each of the statements.  So let's start with the basic framework of a financial model, which a VP once told me is not much different from a fine-tuned automobile:

1. Steering wheel (controls / assumptions)
2. Axes and tires (formulas / financial statements)
3. Interior furnishing/paint job (formatting)
4. Higher likelihood that Asian females would crash it (ditto)

Does this also mean Asian males would soup up their models with "Toyota Racing" decals and nitrous oxide engines?  Cause that might be 2 fast, 2 furious.

How much do you think your dream car would skimp on any of the above-mentioned items?  If it does at all, then improve your imagination.  You can't have a shitty steering wheel or axes if you want your car to get around and change directions.  You also don't want paint peeling off the side doors.  And if it's Asian-chick-proof, it's not a car, it's a cement block (assuming they don't know martial arts).  By the way, why do car salesmen say "side doors" when talking about sedans? All doors are on the F'ing side.  Front and rear doors?  I don't see anyone climbing into their seats through the hood.

But enough nonsense.  For a model to work mechanically (disregarding how realistic the numbers may be), you need acertain number of inputs (they only need to be numbers to start off), and the rest of the model will all be calculations based on these inputs.  At the most advanced levels, these inputs become models within themselves, driven by many underlying functions and assumptions.  For example, a basic model may show revenue as $100 million for each of the next five years.  However, a slightly more advanced model will show revenue as a function of annual growth percentages.  Further, a complex model may show revenue as a function of several individual customers or accounts, with each based on numerous assumptions for price, volume, market size, etc.

So here is the basic framework, along with which inputs will drive the model.  Again, many future posts will be dedicated to transforming these numbers into more complex, functional formulas.
Income Statement 

1. Revenue - COGS - operating expenses = operating income
2. Operating income + interest income - interest expense = pre-tax income
3. Pre-tax income - taxes = net income

Balance Sheet:

1. Assets = cash + current assets (accounts receivable, inventory, prepaid expenses, etc.) + long-term assets (PP&E, etc.)
• Cash = previous year's balance + net cash flow
• PP&E = previous year's balance + capital expenditures - D&A
2. Liabilities = current liabilities (accounts payable, deferred revenue, etc.) + debt + long-term liabilities
• Debt = previous year's balance + debt issuance - debt repayment
3. Shareholders' equity = 
• Retained earnings = previous year's balance + net income - dividends
• Share capital (is this what you call it? I got fired from an accounting internship) = previous year's balance + stock issuance - stock repurchase
4. Assets = liabilities + shareholder's equity

Cash Flow Statement

1. Cash flow from operations = net income + D&A - increases in working capital
2. Cash flow from investing activities = - capital expenditures
3. Cash flow from financing activities = debt issuance - debt repayment + stock issuance - stock repurchase - dividends
4. Cash flow from operations + cash flow from investing activities + cash flow from financing activities = net cash flow

If you happened to read this far, you're more of a loser than I am for writing this far,

-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

Custom Number Formatting

Anyone that has worked on a client pitchbook for banking or an investment committee memo for private equity knows that formatting is of the utmost importance (some might say gross overimportance).  I can't speak for hedge funds, but I have a friend that proudly works at a health care hedge fund, and believes that formatting is for people who don't have a real job like him.

I'd rather not debate who has a real job or whether formatting is important or not, but let me give you some words of motivation on why I even attempt to format spreadsheets and presentations.  When I suffered Training the Street (are all these references a delayed onset of Stockholm Syndrome?), our instructor Scott threatened all bad formatters by saying, "Watch out, I am an anal monster."  Yeah.  Of course you are.

So some of you may be interested in what an anal monster does or who among your coworkers are anal monsters, but I just assume that everyone is one and that nice formatting can prevent me from finding out anything more.  For this particular post, let's talk about nice formatting in terms of number formats, starting with CTRL + 1, ALT + C, END, ALT + T:

I will assume you know the basics on formatting dollars in millions, with negative numbers in parentheses (rather than with a "minus") and one or two decimal places of significant digits.  If you're already doing this formatting with custom formats, then great; otherwise, read closely to find out what you've been missing.  In the "Type:" field of the "Number" format menu shown above, number formats are entered as: a;b;c where "a" represents positive numbers, "b" represents negative numbers, and "c" represents zero.  You can thus apply some of the following format types:

1. $#,##0.0_);($#,##0.0);"-" for dollars: The #'s ensure that if you enter the number 1.1, it will show up as $1.1 (not $0,001.1) but a number in the thousands or above will still have a comma separating every three digits.  Moreover, zero will appear as a dash, and not "0;" this is more of a personal preference to eliminate seas of zeros that sometimes make extensive models hard to read, and also to allow easy recognition between an absolute zero and a very small number like 0.001 that appears as 0.0.
2. $#,##0.0_);[RED]($#,##0.0);"-" for dollars with negative numbers automatically colored red: Basic colors like [RED] and [BLUE] work in the customer formatting framework when in brackets and preceding an expression.  You can also try [TURQUOISE], but that would make you an idiot.
3. #,##0.0x_);(#,##0.0x);"-" for multiples
4. #,##0.0%_);(#,##0.0%);"-" for percentages
5. "Yes";;"No" for binary code: Excel also has TRUE and FALSE built into the program as 1 and 0, respectively.
6. "" or ;;; to make cells blank
7. mm/dd/yy for dates: Other formats that work include mmm dd, yyyy or mmmm dd or some combination.  I have no idea how this follows the positive;negative;zero rule.

You get the idea.  And you have probably figured out by now that you can also format a number using one of the preset options, and later toggle to the custom option to see the code.  The possibilities are limitless.  Just don't use that stupid accountant format where you right align a number and the dollar sign is still all the way to the left.  If you do use it, why don't you try writing out the number 1,000 as 1,                  000?  Maybe that will satisfy your urge to make documents as illegible as possible.

Last thought: does an anal monster enjoy "anus" or "ani?"

I work for TTS and enjoy the plural of anus 

Helpful,

-F-One

Need for Speed

How fast can a human being possibly use Excel?  This is not an easy question to answer.  However, it's frequently a very important question to the younger people that work in investment banking, PE or hedge funds, because much of their livelihoods are tied to Excel.  Particularly in banking, the faster your execution speed is overall, the less miserable life becomes.  Speaking of miserable, for the week that I was exposed to Training the Street, they introduced us to their version of the "Excel speed test," which was a formatting exercise typically completed in a minute, but sometimes completed as quickly as 30 seconds.  I never finished it, so you could probably call me slow.

Regardless, I do know a thing or two about speed relative to Excel. Here is what it takes:

How fast (or slow) of a typist are you?

1. Typing speed - If you can't type at least capably, you can give up any hope of using Excel any faster than that old MD that doesn't check e-mails or use Excel since he started working before computers and high-yield debt existed. Typing speed is like the 40-yard dash for football players: raw speed. You could type 130 WPM (probably equivalent to a 4.2 second 40), or you could only type 50 WPM (offensive lineman speed). Anything below that range, and you should probably consider changing professions.
2. Shortcut/function knowledge - Even if you are the greatest typist in the world, you can't do much financial modeling (at least quickly) without knowing your way around Excel.  This is the same reason track stars cannot always be great football players; speed is no guarantee that they know the playbook and how to play (think of John Capel vs. Randy Moss).  So for maximum speed, you need to know things like CTRL + 1, CTRL + arrow keys, etc.  Having the ability to type out a Steven King novel in a couple of hours may help, but not much since you aren't writing novels in the spreadsheet.
3. Brainpower - Some might argue that this is tied to typing speed, which is partly true, but I want to highlight the importance of a good memory, quick recall function, etc.  You may have posted on the side of your cubicle (a good Asian friend of mine used to call it a "cubic") a sheet listing commonly used keyboard shortcuts.  Does this help you?  Maybe, but certainly not in significantly increasing your speed.  If you just transitioned from Excel 2003 to 2007 and are looking for the command for "Page Setup" (ALT, P, S, P), scanning the sheet and then typing the command is not very efficient.  Similarly, if you somewhat remember the function and have to think about it for 10 seconds, that's not very efficient either.  But if you know every function off the top of your head, you'll obviously operate much faster than others who need to refer to either their physical or mental cheat sheets.

The good news: all 3 of the aforementioned attributes can be significantly developed with the proper amount of repetition.  One year of banking analyst experience will yield roughly 5,000 hours of work, much of which is sadly just improving your computer skills through repetition.

So how fast is fast?  Well, Mrs. F-One can type 120 WPM.  That's pretty fast.  I can build a 3-statement model while using one hand to eat buffalo wings in less than 15 minutes.  Maybe you're not impressed, but given that I only type 80 WPM with TWO hands, that's not bad.  I've often mentioned to friends that if Mrs. F-One and I were to produce a child that gets all the right traits, this child would be the Lebron James of investment banking.  The other child would probably get cut from middle school basketball.  Anyway, I would gladly let Le-One go straight from high school to the pros.  The only part I want to avoid is Gloria James banging Delonte West in the team hotel.

I'm taking my talents to Wall Street...as soon as I unlodge this basketball from my dong.

There are obviously many ways to improve Excel speed, some of which I will discuss in future posts.  In the mean time, I strongly suggest 1) continuing to work hard while making an effort to memorize more functions/keystrokes and 2) http://www.freetetris.org/

Enjoy while Mrs. F-One gives me a beatdown for suggesting that our future child should become a banker,

F-One

Automatically Updating Page Titles for Sheets

Has anyone ever asked you how to automatically link a cell to the title of a sheet?  For example, if the current worksheet is called "Sheet1," how do you link to this name such that a change in the name will be reflected in your cell link?  More importantly, isn't it amazing how often a senior person asks you to do something in Excel without even being sure of whether it's possible or how the hell it could work?

You might be in the process of creating a product model in which there are 25 different products, and each product occupies a sheet within the model.  Let's say that you name each sheet for each different product, and have a title on each sheet that also matches the name of the product.  If you decide to change the nomenclature, you may be making 25 changes twice over (or even more than that, if you are using the product name in multiple cells on each sheet).

How do we link to sheet names?

Obviously, the more you link things together, the less manual updating you will need to do.  So here is a way to link to a sheet name, for whatever purpose you might need (make sure your file is saved on the hard drive).

1. Start with a formula that will return a string of text containing the sheet name.  Assume you are on "Sheet1."  You can go to cell B3 and enter: =+CELL("filename",$B$3), which returns "C:\Folder\[filename.xlsx]Sheet1"
2. Having the filepath and sheet name, you can create a function that returns only the part of this string of text that follows the "]," which would be "Sheet1"
3. One function that will work to return only the text following "]" is the RIGHT function: =+RIGHT(CELL("filename",$B$3),a) where a is the number of characters following "]"
4. Obviously, if all of your sheet names are 6 characters in length, you can just use the function from step 3 with a = 6,.  But a truly dynamic function will require solving for "a" in all cases.  So first, let's assume a = b - c, where b is the total length of the filename and c is the total number of characters through "]"
5. To find "b," use =+LEN(CELL("filename",$B$3)), which tells you that the filename is 31 characters in length
6. To find "c," use =+SEARCH("]",CELL("filename",$B$3),1), which tells you that "]" occupies the 25th position starting from the left in "C:\Folder\[filename.xlsx]Sheet1"
7. Using a = b - c, we have a = 31 - 26 = 5.  In formulaic terms, a = LEN(CELL("filename",$B$3))-SEARCH("]",CELL("filename",$B$3),1)
8. Thus, using the formulae from steps 3 and 7, the sheet name is =+RIGHT(CELL("filename",$B$3),LEN(CELL("filename",$B$3))-SEARCH("]",CELL("filename",$B$3),1))

Holy sheet!

-F-One

My Favorite Cousin: The F5 Key

 Keep going...keep going

Everyone knows that the fastest way to navigate to a random cell is entering the cell reference into the "Go To" menu.  In other words, if you want to get to cell AZ355, type: F5, AZ355, ENTER

Similarly, you know that if you have named a cell or cell range, you can select that name on this menu, and Excel will highlight the selection for you.

But what happens if you want to select some other group of cells that isn't named and isn't contiguous?  What if you're a first-year analyst who is being forced to color all of the hard inputs in a model to blue?  What if the model is 100 pages?  What if the penalty for forgetting to color one of the numbers is being called an idiot even though you sit next to an admin from Brooklyn that doesn't know how to read?

There is a solution for everything in Excel, and in this particular situation, it would be: F5, ALT + S, which brings you to the "Go To Special" menu.

...there!

At this menu, you can select "Constants" (ALT + O, ENTER), and then uncheck all of the boxes except "Numbers."  Now, you can highlight only the hard inputs in your model, and turn all of those numbers blue with just a couple of keystrokes, rather than selecting range after unbearable range and hoping you didn't miss anything along the way.

Step 1: Select a range

Step 2: Select criteria

Step 3: Only format these numbers...

Think of some of the other possibilities!  If you have hidden or grouped data that you do not want to format, you can select only visible cells and format those.  If you want to find out what cells are conditionally formatted, rather than trying to guess, you can do that easily.  In any case, my cousin F5 is extremely underutilized for how powerful it is.

Comments/questions welcome.

-F-One

Greetings!

Welcome!  Many (3) people have asked me to share my perceived wealth of excel knowledge with the rest of the world, so I have created this blog with the plan of regularly posting facts, "how-tos" and other updates on Excel for financial modeling and corporate finance in general.

What qualifications suggest that I can teach you, the reader, anything useful about Excel?  Well, I graduated from college (barely) in 2006 having taken one semester of an Excel class (B-), worked 3 years at a large investment bank, and currently work at a private equity firm.  So I have no legitimate credentials to support my case, but you can be the judges of whether my contributions are useful.  Here are some brief notes on the intended focus of this blog:

1. Formulas that improve the efficiency of financial modeling, data analyses and other commonly needed functions within Excel
2. Commands that improve efficiency, formatting and accessibility within Excel
3. I am currently using Excel 2007
4. Topics I do not intend to cover in-depth are: visual basic (not my expertise), statistics (my worst class in college), and pivot tables

The key word is efficiency.  Everywhere I have looked, there is someone using Excel less efficiently than they could, and in turn being less productive for no good reason.

As mentioned, the Excel information on this blog will be most relevant to corporate finance, particularly those people that work in investment banking (and related areas) and frequently use Excel.  But hopefully anyone looking to save some time or learn some interesting things about Excel will benefit.

If you need Excel help or have a topic you want to learn more about, feel free to leave a comment!

Thanks so much,

Mr. F-One