How to Find Seasonality In Data

In almost every business results meeting I’ve attended, the same topic comes up and it sounds like this:

Business Partner: “I know that sales dropped last month, but isn't that because we had fewer days than last month?”

Or

Business Partner: “But did your plan assume that the holiday was on Saturday this year and a Monday last year?”

It’s the most common way a business partner can deflect attention away from a poor result. And often it's completely bogus.

So in FP&A we need to be armed and ready with our math to explain with confidence how the number we are seeing on the page is truly bad or good - not just a factor of seasonality differences.

And fair warning to my statistics and data science friends, this will be very basic. But in my last 10 years in FP&A, it has been good enough for me.

Let’s jump in:

Step 1: Pull historical data

In order to assess the seasonality of a data set, you need enough historical periods to make sure you are picking up a pattern and not just an anomaly.

For me, this means at least 3 historical periods. But ideally 5.

If you are assessing monthly seasonality (like I show you below), then that means you need 5 years of data arranged by month.

⚠️ If your company had a major event that caused a disruption in the data (for example, a global pandemic cough, cough) then I recommend ignoring that time period completely. Our goal in this process is to understand what a ‘normal’ year will look like. And nothing about 2020 was normal.

Here's the daily sales data we are going to be using. And it's arranged by month:

Step 2: Normalize the data

If you are evaluating monthly data like we are here, then I highly recommend you normalize for the number of days in the month and/or the weighting of each day.

Here are a few things to consider when doing this:

• Was there a leap year in any of your years of data. If so, make sure you normalize that month's output.

• Does your product have stronger sales on a particular day of the week? If so, make sure you go through the daily seasonal process to normalize your data.

• Does your company only sell product Monday-Friday? If so, you’ll need to consider the number of workdays in each month. There are some cool excel formulas that can help you do this.

While you don’t need to do this step if your data is clean enough from the common pitfalls noted above, it’ll create even more confidence in your seasonality.

Step 3: Find the seasonal pattern

Let’s pretend my data table above is normalized for days of the week and everything I mentioned in the previous section.

Now we are going to do a simple math formula to measure the monthly seasonal within each year. This formula will give us the ability to compare each year against each other and create an overall seasonal.

We will simply divide the monthly sales number for a single year by the average of the full year's monthly sales numbers. This means that the years can be compared against each other because they are normalized.

Here's the output and an example formula in Excel (Z20 is the daily sales number for Jan 2022 in the table above, and Z20:AK20 is the full year of 2022 monthly sales data):

Once we have the in-year seasonality above, it’s time to simply create an average of each year.

No need to put it in a graph, but it's more fun that way. Can you start to see the seasonal trend emerging?

Step 4: Remove outliers

But what if your graph has a decent amount of variability across the years? You’ll want to take out those outliers so that your seasonal trend isn’t skewed (April of 2017 is a great example from the graph above - you see how it jumps up when the other years have April trending down?).

My favorite way to ensure that the outliers are thrown out of my seasonal pattern is to use what I call a ‘5 year xHigh xLow’.

A 5 year xHigh xLow simply looks at the past 5 years and removes the highest seasonal number (the outlier on the high end) as well as the lowest seasonal number (the outlier on the low end). By doing this, you are typically left with a very smooth and predictable seasonal pattern.

In Excel, you can do this by using the following formula:
=(sum(Jan from all years)-max(Jan from all years)-min(Jan from all years))/3

In the view below I show you what the straight average is and what the 5 year xHigh xLow is (both exclude 2020).

⚠️ A word of caution: If you are only working with 3 years of data, then this may not work for you. Since you are throwing out 2 of the 3 data points, you are likely making the trend more volatile.

Not a huge difference between the Seasonal line and the 5 Year xHigh xLow, but enough to give me confidence that removing outliers has improved my seasonal predictions.

Now it's time for you to incorporate this into your reporting, analysis and forecasting efforts. You can find seasonal patterns everywhere in my financial models and analyses. Anytime I'm working with data across multiple time periods I'm considering how seasonal trends may be effecting my result.

In Summary:

Understanding the seasonality in your data can help you anticipation questions, produce a better forecast, and understand the root cause of financial results.

So make sure you pull enough data, normalize it, remove outliers, then use it in your analysis.

 Say hi 👋 on LinkedIn