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

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