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:
- In cell B6, enter the number of months per projection period (obviously 12 for an annual model and 3 for a quarterly model)
- In cell B7, enter the last date for which actual/historical numbers are available (12/31/09 for simplicity)
- Starting in cell F6, enter in the first time period in your model (12/31/09 is fine), with custom number format mmm dd,
- 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
- Drag the formula in G6 through to L6
- 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)
- 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
