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

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