Calculating Seasonality & Trending

If you are attempting to forecast any sort of quarterly/monthly/daily growth rate, you'll likely need to calculate trending and seasonality.  How come?  Because:

• Things sometimes behave differently during different times of the year.  NORADSanta.org's internet traffic spikes in December, then drops in January. 
• Things sometimes behave differently over time.  MySpace was on a tear for a while, but not so much these days.

In this post, I'll start by describing Trending and Seasonality a bit, and then step through a very basic way to calculate them.


What is Trending, exactly?
Trending is the long-term compound growth rate of your data.  Let's say that I watched a stock over four years, and it grew from 100 to 400 over that period.   Then it's long-term monthly trend would be:

...that "power()" function is how you'd calculate the value in Excel.  Don't let the root intimidate you; all it's saying is "what number would I have to multiply 100 by forty eight times in a row to arrive at 400?"   And sure enough, if you started with 100, multiplied once is 102.93, multiplied twice is 105.94...forty seven times is 388.61, and forty eight times is 400.

You can calculate different trends, too.  For example, if you have lots of historical data, you could have a long-term trend (say, a stock's behavior over the past 5 years) and then a short-term trend (its behavior over the past six months).

Autumn: divide by 1 leaf.

What is Seasonality, exactly?
Seasonality is the impact that repeating periods have on your data -- hours of the day, days of the week, months of the year, et cetera.   

The trick is, at least for when talking about growth rates, seasonality should be geometrically neutral.   Said in non-geek-speak:  If you multiply together all of your seasonality coefficients, you should get a value of 1.  Said another way, the net effect of seasonality (across all periods) should be no effect whatsoever.

Fortunately, making seasonality geometrically neutral is pretty easy -- I'll show you below.  (One of the perks of calculating seasonality, is that you can tell your co-workers that "naturally, I made sure the seasonality was geometrically neutral."  This may impress them, but be warned: It is unlikely to get you any dates.)

Calculating Trending and Seasonality

In the attached spreadsheet (here), we have four years of time series data for a particular item.  

Winter: add 1/3 cup snow

If you look in the sheet "Creating the Data", you can see how I created the time series, by choosing trending and seasonality values.  Then, in the sheet "Calculation", you can see how, using only the time series data itself, I'm able to derive the time series' trending and monthly seasonality values.  (Granted, this is a basic example -- I have an even number of periods for each season and zero volatility, but I'm focusing upon the base concepts here; we can trick it out later on.)

To calculate trending and seasonality, follow these four steps:

1. Calculate your long-term trend.  
This is precisely as I described above.  In this instance, over forty-eight periods we went from a value of 100 to a value of 657, so our long-term trend is the 48th root of 6.57, which is 104% -- or 4% growth.

2. Determine the month-over-month change.
Easy breezy.  Just divide each value by the previous value, to get the percentage change.   For example, the February and March time series values are 99.84 and 149.52 respectively.  This represents 149.52/99.84 = 149.7% growth for March.

3. De-trend the month-over-month changes.
Here, we're stripping out the long-term trend, so just divide each month-over-month value by the long-term trend.  For example, in March 2010 the value grew by 149.76%, but after we subtract out the 104% seasonality, we're left with 149.76%/104% = 144%.

4. Take the arithmetic average of each month's growth rates, to calculate its seasonality.
Last but not least, you can determine the seasonality for each month by simply (arithmetic) averaging its growth rates.  (Remember, the arithmetic average is the "regular" average -- just add up the growth rates for each month and divide by the number of values.)   In this example, each month always has the same seasonality -- but in real life you'd need to take an average to account for random variability.

Is It Really This Simple In Real Life?
Yes and no.  Trending and Seasonality are faithfully described, and you should be able to use the techniques described here to great benefit, as a mainstay of your forecasting algorithm.  But unfortunately, a forecast is more than just trending and seasonality.

You'll likely have to deal with a bunch of other dynamics, such as these:

• Lots of random variability (aka "noise") in your data.
• Uneven number of seasons (e.g., 4 Novembers but 5 Decembers).
• Blurry or inconsistent data definitions (quite often customer, contract or product)
• Extreme outliers that skew your averages.
• Uneven amounts of data for each month.
• Different long-term and short-term trends.
• Operationalizing and fostering adoption of financial-centric forecasts.
• Defining business processes around forecasts.

 ...Some of them I've already covered in other articles; others I'll get to as soon as I can!  Some of these processes help "prep" the data -- so change your data input.  Some others, might affect how you calculate trending and seasonality.  However, on some level, the process will be more-or-less as described above.