Seasonality is a pattern of variation in a time series that is related to the time of the year, such as monthly, quarterly, or annual cycles. For example, ice cream sales may peak in summer and decline in winter, or retail sales may surge during the holiday season and drop afterwards. Seasonality can affect the accuracy of forecasting models, as it may obscure the underlying trend and noise in the data. Therefore, it is often desirable to remove the seasonal component from the data before applying forecasting techniques, such as regression, exponential smoothing, or ARIMA. This process is called de-seasonalization or seasonal adjustment.
The basic idea of de-seasonalization is to estimate the seasonal component of the data and subtract it from the original data, leaving only the trend and noise components. There are different methods to estimate the seasonal component, such as moving averages, seasonal indices, or seasonal dummy variables. One of the most common and simple methods is to use the average of the data for each season as the seasonal component. For example, if the data is monthly, the seasonal component for January is the average of all the January values in the data, and so on for the other months. Then, the de-seasonalized data is obtained by subtracting the seasonal component from the original data.
Procedures
To de-seasonalize your data in Excel, you can follow these steps:
- Arrange your data in a table with two columns: one for the time period (such as month, quarter, or year) and one for the variable of interest (such as sales, revenue, or demand).
- Calculate the average of the data for each season. You can use the AVERAGEIF function to do this. For example, if your data is monthly and in cells A2:B25, you can enter the formula
=AVERAGEIF(A$2:A$25,A2,B$2:B$25)in cell C2 and copy it down to cell C25. This will give you the seasonal component for each month. - Subtract the seasonal component from the original data to get the de-seasonalized data. You can use the simple subtraction operator to do this. For example, you can enter the formula
=B2-C2in cell D2 and copy it down to cell D25. This will give you the de-seasonalized data for each month. - You can now use the de-seasonalized data to apply any forecasting technique you prefer, such as regression, exponential smoothing, or ARIMA. You can also plot the de-seasonalized data to see the trend and noise components more clearly.
Example
To illustrate the de-seasonalization method, let us use a hypothetical example of monthly ice cream sales data from January 2020 to January 2021. The data is shown in the table below:
| Month | Sales |
|---|---|
| Jan-20 | 120 |
| Feb-20 | 100 |
| Mar-20 | 150 |
| Apr-20 | 200 |
| May-20 | 250 |
| Jun-20 | 300 |
| Jul-20 | 350 |
| Aug-20 | 320 |
| Sep-20 | 280 |
| Oct-20 | 240 |
| Nov-20 | 180 |
| Dec-20 | 140 |
| Jan-21 | 130 |
We can apply the de-seasonalization method as follows:
- Arrange the data in a table with two columns: Month and Sales. We can use cells A1:B14 for this.
- Calculate the average of the data for each month. We can use the AVERAGEIF function to do this. For example, we can enter the formula
=AVERAGEIF(A$2:A$14,A2,B$2:B$14)in cell C2 and copy it down to cell C14. This will give us the seasonal component for each month, as shown in the table below:
| Month | Sales | Seasonal Component |
|---|---|---|
| Jan-20 | 120 | 125 |
| Feb-20 | 100 | 100 |
| Mar-20 | 150 | 150 |
| Apr-20 | 200 | 200 |
| May-20 | 250 | 250 |
| Jun-20 | 300 | 300 |
| Jul-20 | 350 | 350 |
| Aug-20 | 320 | 320 |
| Sep-20 | 280 | 280 |
| Oct-20 | 240 | 240 |
| Nov-20 | 180 | 180 |
| Dec-20 | 140 | 140 |
| Jan-21 | 130 | 125 |
- Subtract the seasonal component from the original data to get the de-seasonalized data. We can use the simple subtraction operator to do this. For example, we can enter the formula
=B2-C2in cell D2 and copy it down to cell D14. This will give us the de-seasonalized data for each month, as shown in the table below:
| Month | Sales | Seasonal Component | De-seasonalized Data |
|---|---|---|---|
| Jan-20 | 120 | 125 | -5 |
| Feb-20 | 100 | 100 | 0 |
| Mar-20 | 150 | 150 | 0 |
| Apr-20 | 200 | 200 | 0 |
| May-20 | 250 | 250 | 0 |
| Jun-20 | 300 | 300 | 0 |
| Jul-20 | 350 | 350 | 0 |
| Aug-20 | 320 | 320 | 0 |
| Sep-20 | 280 | 280 | 0 |
| Oct-20 | 240 | 240 | 0 |
| Nov-20 | 180 | 180 | 0 |
| Dec-20 | 140 | 140 | 0 |
| Jan-21 | 130 | 125 | 5 |
- We can now use the de-seasonalized data to apply any forecasting technique we prefer, such as regression, exponential smoothing, or ARIMA. We can also plot the de-seasonalized data to see the trend and noise components more clearly. The plot is shown below:
As we can see, the de-seasonalized data has a slight upward trend and some random fluctuations, but no clear seasonal pattern.
Other Approaches
The method we used above is one of the simplest ways to de-seasonalize your data, but it is not the only one. There are other approaches that may be more suitable for different types of data or situations. Some of the other approaches are:
- Using a moving average to smooth out the seasonal fluctuations and obtain the trend component. Then, dividing the original data by the trend component to get the seasonal component. This method is also known as the ratio-to-moving-average method.
- Using seasonal indices to measure the relative strength of the seasonal effect for each season. Then, dividing the original data by the seasonal indices to get the de-seasonalized data. This method is also known as the ratio-to-seasonal-index method.
- Using seasonal dummy variables to capture the seasonal effect in a regression model. Then, using the residuals of the regression model as the de-seasonalized data. This method is also known as the regression-with-seasonal-dummies method.
Each of these methods has its own advantages and disadvantages, and you may need to experiment with different methods to find the best one for your data. You can also use Excel’s built-in Data Analysis Toolpak to perform some of these methods, such as moving average and regression.
