Как рассчитать аддитивную сезонность в Excel

аддитивная сезонностьИз данной статьи вы узнаете

  • Что такое аддитивная сезонность,
  • Как рассчитать аддитивную сезонность в Excel,
  • Как учесть аддитивную сезонность в прогнозе.

Что такое аддитивная сезонность?

Сезонность можно разделить на 2 типа:

  1. Аддитивная;
  2. Мультипликативная.

В математике:

  1. Аддитивность - это операция сложения, формула прогноза F = T + S;
  2. Мультиплекативность – это операция умножения, формула прогноза F=T*S.

Где,

  • T – это средняя или тренд;
  • S – сезонность;
  • F – прогноз.

Аддитивная сезонность измеряется в тех же единицах, что и ряд, т.е. если мы рассматриваем ряд с продажами в рублях по месяцам, то аддитивная сезонность будет выражена в отклонениях одного месяца относительно средней или тренда в рублях.

Мультипликативная сезонность измеряется в относительных единицах – коэффициентах и в среднем равна 1. Т.е. коэффициент января у нас может получится - 0,9, февраля - 1,1…

Аддитивную сезонность имеет смысл использовать, если амплитуда колебаний сезонности из года в год не меняется. Если амплитуда колебаний сезонности из года в год меняется (т.е. размах уменьшается или увеличивается), то используем мультипликативную сезонность.

Как рассчитать аддитивную сезонность в Excel?

Скачайте Excel-файл с примером расчета аддитивной сезонности

Возьмем продажи, например, муки по месяцам. Сезонность есть, но продажи из года в год стабильны, возрастающей амплитуды колебаний сезонности не наблюдается.

как определить сезонность

Для расчета аддитивной сезонности:

  1. Выделим линейный тренд из данных;
  2. Рассчитаем разницу «фактические продажи минус тренд»;
  3. Определим аддитивную сезонность по месяцам - среднее отклонение продаж от тренда для каждого месяца.

1.  Выделим линейный тренд из данных.

Для расчета значений тренда для каждого периода времени пронумеруем значения временного ряда – продажи по месяцам:

как посчитать сезонность

С помощью функции Excel =предсказ() рассчитаем значения тренда по месяцам:

как рассчитать сезонность

 

=ПРЕДСКАЗ где

  • D5 – X – номер периода, для которого рассчитываем значение тренда;
  • $C$5:$C$40 – известные значения y — фиксированная ссылка на диапазон с объемами продаж;
  • $D$5:$D$40 – известные значения X – фиксированная ссылка на диапазон с номерами периодов.

Как зафиксировать ссылку, читайте в статье «Как зафиксировать ссылку в Excel».

Рассчитали значения тренда:

аддитивная сезонность

2. Рассчитываем разницу значений ряда и тренда — объем продаж минус тренд:

анализ сезонности

3. Определим аддитивную сезонность по месяцам - среднее отклонение продаж от тренда для каждого месяца.

Определяем среднее отклонение для каждого месяца:

анализ сезонности продаж

Т.к. первый и последний годы не полные, чтобы не запутаться с месяцами и формулами, воспользуемся формулой:

=СУММЕСЛИ($B$5:$B$40;B5;$F$5:$F$40)/СЧЁТЕСЛИ($B$5:$B$40;B5), где

  • =СУММЕСЛИ - формула суммирует отклонения по заданным месяцам
  • $B$5:$B$40; - ссылка на диапазон с номерами месяцев
  • B5; - номер конкретного месяца для суммирования
  • $F$5:$F$40 - ссылка на диапазон для суммирования
  • /  - делим сумму за определенный месяц на количество, получаем среднее по месяцам
  • СЧЁТЕСЛИ - формула считает количество месяцев в диапазоне
  • $B$5:$B$40; - диапазон с номерами месяцев
  • B5 – номер конкретного месяца для счета

Получаем среднее отклонение по месяцам – аддитивную сезонность:

влияние сезонности

Для расчета прогноза:

  1. Продлеваем тренд в будущее;
  2. К тренду прибавляем аддитивную сезонность соответствующего месяца.

Получаем прогноз:

Скачайте Excel-файл с примером расчета аддитивной сезонности

график сезонности

Программа Forecast4AC PRO умеет автоматически подбирать аддитивную или мультипликативную сезонность, модель прогноза и подходит для прогноза большого массива данных.

Если есть вопросы, пожалуйста, обращайтесь!

Точных вам прогнозов!

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

Novo Forecast - прогноз в Excel - точно, легко и быстро!

  • Novo Forecast Lite - автоматический расчет прогноза в Excel.
  • 4analytics - ABC-XYZ-анализ и анализ выбросов в Excel.
  • Qlik Sense Desktop и QlikView Personal Edition - BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

  • Novo Forecast PRO - прогнозирование в Excel для больших массивов данных.

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

Зарегистрируйтесь и скачайте решения

Статья полезная? Поделитесь с друзьями

 

Комментарии   

#3 Алексей Батурин 03.08.2017 14:35
Цитирую Анна:
Добрый день!
Я попыталась выделить аддитивную сезонность согласно вашему алгоритму. Как я понимаю показатели сезонности за год в сумме должны давать 0. У вас в расчете так и выходит. Но у меня не получилось. Не могли бы вы посмотреть мой расчет? Спасибо.

Анна, добрый день!

Пришлите, пожалуйста, расчет на

Извиняюсь, за задержку с ответом, пропустил письмо, был в отпуске.

Хорошего Вам дня!
Цитировать
#2 Анна 13.07.2017 19:01
Добрый день!
Я попыталась выделить аддитивную сезонность согласно вашему алгоритму. Как я понимаю показатели сезонности за год в сумме должны давать 0. У вас в расчете так и выходит. Но у меня не получилось. Не могли бы вы посмотреть мой расчет? Спасибо.
Цитировать
#1 Иван 22.09.2016 21:56
Цитирую Иван:
Здравствуйте! Как использовать индексы сезонности для экстраполяции, если они рассчитаны методом средних, т.е без очищения от тренда?

Иван, добрый день.

В каждой модели коэффициенты сезонности рассчитываются по разному и надо понимать, что к чему применять. Нет одних коэффициентов сезонности, которые применимы ко всем моделям. В каждой модели нужно делать расчет коэффициентов в рамках логики данной модели.

Вот 3 модели и 3 способа расчета коэффициентов - они разные:
http://4analytics.ru/prognozirovanie/prognoz-po-metodu-eksponencialnogo-sglajivaniya-s-trendom-i-sezonnostyu-xolta-vintersa.html

http://4analytics.ru/prognozirovanie/kak-rasschitat-prognoz-prodaj-s-uchetom-rosta-i-sezonnosti-v-excel.html

http://4analytics.ru/prognozirovanie/raschet-prognoza-prodaj-po-metodu-skolzyasheie-sredneie-v-excel.html

Мой ответ - рассчитать сезонность для вашей модели.
Цитировать

Добавить комментарий