7. ПРЕДСКАЗ.ETS и сезонность

Продажи мороженого, туров и обогревателей повторяют один и тот же рисунок каждый год — это сезонность. Прямая линия тренда (ПРЕДСКАЗ.ЛИНЕЙН) её не видит: на пике она прогнозирует слишком мало, в спад — слишком много.

В Excel 2016 и новее есть ПРЕДСКАЗ.ETS(целевая_дата; значения; временная_шкала; [сезонность]; [заполнение]; [агрегирование]). Она строит модель экспоненциального сглаживания (AAA-версия алгоритма ETS): свежие точки весят больше старых, отдельно оцениваются уровень, тренд и сезонная волна.

• сезонность: 1 или пропуск — Excel сам ищет длину цикла, 0 — без сезонности, 4 — квартальный цикл, 12 — месячный;
• временная шкала должна идти с постоянным шагом (каждый месяц, каждый квартал); небольшие пропуски Excel умеет заполнять;
• ПРЕДСКАЗ.ETS.ДОВИНТЕРВАЛ возвращает ширину доверительного интервала прогноза, ПРЕДСКАЗ.ETS.СЕЗОННОСТЬ — найденную длину сезона;
• Данные → Лист прогноза делает всё сразу: выделите даты и значения, выберите конец прогноза — Excel создаст новый лист с таблицей, формулами ETS, границами интервала и графиком.

Важно: тренажёр не вычисляет ПРЕДСКАЗ.ETS — алгоритм подбирает параметры сглаживания численно, и повторять его здесь мы не будем. Зато соберём классическую мультипликативную модель, которую можно проверить руками и объяснить руководителю:

1. Индекс сезона квартала = среднее продаж этого квартала / среднее всех продаж. Индекс 1,4 — квартал на 40% выше обычного;
2. Очищенный ряд = продажи / индекс своего квартала — сезонная волна убрана, остался тренд;
3. Тренд по очищенному ряду — ПРЕДСКАЗ.ЛИНЕЙН или НАКЛОН/ОТРЕЗОК;
4. Прогноз = значение тренда × индекс сезона нужного квартала.

Более точный вариант считает индексы по скользящему среднему, но идея та же. Если ETS и ручная модель дают похожие цифры — прогнозу можно доверять больше.