Does FORECAST.ETS.SEASONALITY work for anyone?

[Bob Flisser]

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.

[#2]

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-e.... 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.

[#3]

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.

[#4]

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.

[#5]

I took my original sheet and extend the input numbers to 3 full years, then ran FORECAST.ETS to estimate the first 3 months of the 4th year. They were silly: January showed a high number instead of low, February was low as it should be, then March was high again. It's like Excel was throwing darts.

The good news is that FORECAST.ETS.SEASONALITY did show a 12-month cycle, so you're right that the function needs at least 3 full years of data. (So it's not much use for startup companies.) The confidence interval from FORECAST.ETS.CONFINT showed an interval of 150, which might be a little high but is at least reasonable.

[#6]

Yes, for the business with forecasting based on monthly results seasonality will always be 12 (or zero). However, that could be more useful cases like time peaks during the day for call center; availability of candidates on labour market during the year; even weather forecast, where seasonality function could be more useful.