Join Wayne Winston for an in-depth discussion in this video What is a seasonal index?, part of Excel Data Analysis: Forecasting.
- The fourth quarter of the year is the months October through December. As you probably know, and we pointed out in the chapter one videos, Amazon.com sells a lot more merchandise during the fourth quarter than any other quarter, primarily because of the holiday season. This is an example of seasonality, and the problem with seasonality is it makes it really difficult to forecast future values of a time series. If you've noticed, all the examples we've done so far in forecasting have not had seasonality. They've been yearly data, but now we're ready to tackle the issue of seasonality in the remaining two chapters of this video.
So, a really important concept that will really refine your understanding of, in this video, is the concept of a seasonal index, and then in the rest of the chapter we'll teach you the ratio to moving average method, which is a simple yet powerful method to incorporate seasonality in your forecasts, used by many corporations. Okay, so let's suppose you have for Q1 through Q4 these four numbers, which we will call seasonal indices. So, what do these mean? The Q4 seasonal index of 1.3 means in Q4 this company tends to sell 30% more than an average quarter.
That's what the 1.3 means. And in Q1 this company sells 20% less than an average quarter. That's what the 0.8 means. So, seasonal indices must have a certain property. They must average to one. In other words, the quarters that are above average must sort of be canceled out by the quarters that are below average. But you really can't do much forecasting on quarterly data or monthly data if you don't understand seasonality, and that's going to be the main topic of this entire chapter, but in this video, we just want to give you a simple understanding of seasonal indices.
So, we have a little brain teaser for you that I use often when I train at companies, and very few people get the brain teaser right. So, we'll work you through it. Okay, so let's see if we understand seasonality. So, suppose you work for a company whose fourth quarter is great. It's seasonal index is two. So, what does that mean? During the fourth quarter, their sales tend to be double an average quarter, and they were pretty bad in the first quarter. Their seasonal index is 0.5, which means in their first quarter their sales tend to be half of an average quarter.
Let's look at some sales data for this fictitious company. Let's suppose in Q4 of 2014 they sold 400 million dollars worth of merchandise. Q1 of 2015, they sold 200 million dollars worth of merchandise, and you were asked to evaluate the performance of the company as an outside consultant. Are they doing better or are they doing worse? Naive analysis is as follows. Sales dropped 50%. Two hundred is 50% of four hundred. This company has real problems.
Well, you're not a very good consultant if you think that, because you're neglecting seasonality. What you have to do is really deseasonalize the sales. I often say desalinization, but deseasonalize. So, what you want to do is say, hey, what really happened in each quarter, in terms of an average quarter? Basically, Q4 of 2014, but the seasonal index was two. So, that's really like selling this much in an average quarter. You divide by the seasonal index. That's a pretty good estimate of what the level was during that Q4.
In other words, 400 in Q4 is basically telling you the level of the time series, based on that observation, was 200 in that fourth quarter. Now, when you deseasonalize Q1 of 2015, you divide by the seasonal index for that quarter of 0.5, and you get 400 in an average quarter. So, if you look at this the right way, even though sales dropped 50%, the data indicates that the level of sales doubled from Q4 2014 to Q1 2015.
So, you can see from this very simple example, if you didn't understand seasonality, you would draw an incorrect conclusion that this company is doing worse, when they're actually doing fantastic. So, in the next video we'll introduce the ratio to moving average method, which can be used to incorporate seasonality in forecasts and estimate seasonal indices.
Lynda.com is a PMI Registered Education Provider. This course qualifies for professional development units (PDUs). To view the activity and PDU details for this course, click here.
The PMI Registered Education Provider logo is a registered mark of the Project Management Institute, Inc.
- Plotting and displaying time-series data
- Creating a moving average chart
- Accounting for errors and bias
- Using and interpreting trendlines
- Modeling exponential growth
- Calculating compound annual growth rate (CAGR)
- Analyzing the impact of seasonality
- Introducing the ratio-to-moving-average method
- Forecasting with multiple regression