Печать

Как рассчитать аддитивную сезонность в 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 умеет автоматически подбирать аддитивную или мультипликативную сезонность, модель прогноза и подходит для прогноза большого массива данных.

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

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

Зарегистрируйтесь и протестируйте Forecast4AC PROскачать программу для прогнозирования Forecast4AC PRO

график модель прогноза + трендграфик сезенностьграфик прогноз + границы прогноза + трендграфик прогноз к предыдущим периодам

Forcast4AC Pro 4ddabd6bbee26 250x250Подписка "Прогноз с точностью 90% и выше!"

Присоединяясь к нам Вы получаете:

  • Более 10 рекомендаций по прогнозированию продаж!
  • Практические примеры!
  • Ссылку на Forecast4AC PRO - 40 дней бесплатно!
  • + советы по работе с программой!

Forecast4AC PRO - прогноз одним нажатием клавиши!

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

Ваши комментарии и вопросы:

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


Защитный код
Обновить