Forum Discussion
Does FORECAST.ETS.SEASONALITY work for anyone?
In Excel 2016 Windows or Mac: has anyone been able to get the FORECAST.ETS.SEASONALITY function to return anything other than zero? I've tried this function on several sets of data, some with very obvious seasons, but this function doesn't recognize them. Maybe I'm doing something wrong?
Attached is a sample file of sales data for two years, with obvious high values in the 4th quarters. The function on the sheet returns 0 seasons. As I understand the function, it should return 3 because there are 3 months of unusually high sales in each year. Even if I restrict the data to a single year, the function still returns 0.
And since Excel doesn't recognize the seasonality, the confidence interval is nonsense. Using the function
=FORECAST.ETS.CONFINT
....the confidence level is about the same number as the forecasted sales units. This is a garbage value.
5 Replies
Hi Bob,
I'm not a specialist in this area, just checked the file which could be downloaded from this support page https://support.office.com/en-US/article/FORECAST-ETS-SEASONALITY-function-32a27a3b-d22f-42ce-8c5d-ef3649269f3c. On that sample data the function returns something different from zero.
After that i simply added few points to your dataset, and it shows now 12 instead of zero. It looks like timeline shall have at least 30 points.
Again, i have no idea what's the logic behind.
Thanks for your response, but it seems that either I don't understand what seasonality is or the functions are still experimental. I think of seasonality as having an "on season" and and "off season" like gifts that are sold more during holiday time or beach rentals that are sold more in the summer. That's why my original values had the 4th quarter months with significantly higher numbers.
The numbers you put in your sheet are all much closer to each other, and the Seasonality function shows 12. To me, a 12 month season means there's no season. That's why I think I'm either misunderstanding or the function doesn't really do much.
Bob, my understanding seasonality returns how many points are found in repeated pattern on your timeline.
In your case the pattern is Q1-Q3 are average season and Q4 is high season. That pattern is repeating every year and consist of 12 months (points). Thus seasonality returns 12.
In figures that's approximately - pattern = (9 months with average sales 700 plus 3 months with average sales 3000), totally 12 months.
If you change the Q2 and Q4 on high season, Q1 and Q3 on low season when repeated pattern will be 6 months (3 low+3 high and so on), seasonality returns 6.
And to find such pattern the timeline shall be at least 3 times bigger than pattern length.
