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

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

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

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