Как посчитать коэффициент сезонности продаж в excel

Обновлено: 07.07.2024

Главной целью десезонализации данных является прогнозирование тренда объема продаж в будущем. Десезонализированные данные и функция ПРЕДСКАЗ, которая их обрабатывает, вместе поставляют необходимую информацию для прогнозирования объема реализации на целый следующий год.

Пример формул таблицы для анализа прогноза продаж в Excel

Ниже на рисунке представлены исходные данные. Допустим по этим данным необходимо составить прогноз продаж на 2020-й год, не смотря на то что собранные статистические данные заканчиваются в декабре 2019-го года. Первым шагом является использование функции ПРЕДСКАЗ и расширение десезонализированных данных на очередные 12 месяцев. Формулы в таблицах:

Для прогнозирования будущих значений функция ПРЕДСКАЗ использует метод линейной регрессии. Функция содержит 3 аргумента:

X – в данном аргументе будет указан месяц, для которого следует получить текущее прогнозируемое значение.
Известные значения y – аргумент содержит десезонализированные данные столбца C.
Известные значения x – здесь указаны месяца соответствующие данным по продажам в столбце A.

После создания с помощью функции ПРЕДСКАЗ прогнозируемых значений для всех месяцев следует восстановить сезонность данных, применяя коэффициенты в таблице, показанной на последнем рисунке выше.

График прогноза продаж с учетом сезонности

В результате всех вычислений и полученных данных можно составить график прогноза продаж с учетом сезонности в будущем году:

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

После восстановления показателей сезонности заметный прогнозируемый активный рост во втором квартале.

При составлении плана продаж на основе данных прошлых периодов многим компаниям приходится учитывать сезонные колебания спроса. Для расчёта плановых данных необходимо будет учесть фактор сезонности с помощью вычисления так называемого индекса сезонности.

�?ндекс сезонности показывает, насколько конкретный период «выбивается» из основной тенденции повышения или понижения продаж.

Планирование будет состоять в следующем:

Расчёт основной линии тренда (подробно о линии тренда – в предыдущем примере простого планирования) и продолжение тренда на прогнозный период.
Расчёт индекса сезонности для каждого периода.
Расчёт прогнозных данных на основе линии тренда и индекса сезонности.
Отображение фактических и прогнозных данных на одном графике.

Скачайте и откройте файл примера Planirovanie-prodazh-s-uchetom-sezonnosti. В диапазоне С6:С17 расположены фактические данные продаж за 3 года (разбитые на 12 кварталов, это упрощённо; в реальности лучше оперировать месячными данными). Необходимо рассчитать значения продаж на следующие два года.

Расчёт и построение линии тренда

Расчёт линии тренда производится следующим образом. На основе данных диапазона В6:С17 строится обычная диаграмма типа график (можно также использовать гистограмму). На диаграмме можно увидеть колебания выручки, явно привязанные к сезонам.

Затем нужно нажать правой клавишей мыши на линии графика, в открывшемся контекстном меню выберите Добавить линию тренда… В открывшемся окне нужно выбрать параметры: Линейная, отметить Показывать уравнение на диаграмме. На графике появится прямая линия тренда и уравнение, её описывающее.

Planirovanie-prodazh-s-uchetom-sezonnostiС помощью этого уравнения рассчитываются данные тренда по каждому периоду. В ячейку А6 записана соответствующая формула «=A6*4359+117264», аналогично рассчитываются все значения столбца Е, включая плановые значения линии тренда в диапазоне Е18:Е25.

Расчёт фактического и планового индекса сезонности

Фактический индекс сезонности в Excel рассчитывается как отношение выручки за период к соответствующему значению линии тренда. В ячейке F6 записана формула «=C6/E6», аналогично рассчитаны значения ячеек F7:F17.

Плановый индекс сезонности рассчитывается несколько иначе. В ячейке F18 это значение рассчитано формулой «=СРЗНАЧ(F6;F10;F14)/СРЗНАЧ($F$6:$F$17)»: взято усреднённое значение фактических индексов сезонности за несколько одинаковых периодов (1 квартал) и разделено на среднее по всем индексам сезонности за весь период. Аналогичным образом рассчитываются плановые индексы сезонности по остальным периодам.

Расчёт прогнозных данных

Прогноз выручки рассчитывается на основе линии тренда и плановых индексов сезонности, эти величины нужно просто перемножить: в ячейке D18 формула «=E18*F18».

Отображение данных на одном графике

Обратите внимание на то, что фактические и прогнозные данные разнесены по разным столбцам таблицы. Это сделано специально для того, чтобы легко отобразить эти данные на графике разными цветами. Ещё одна хитрость: в ячейку С18 занесена формула «=D18», это нужно, чтобы фактические и прогнозные данные на графике отображались одной линией, если здесь будет пусто – на графике будет разрыв. Таблица готова, на основе диапазона B6:D25 строится обычная диаграмма-график.

Таким образом сформирован план продаж с учётом индекса сезонности в Excel. Теперь можно продолжать планирование бюджета в Excel, переходя от плана продаж к плану производства и административных расходов.

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

Уровни временного ряда (Yt) представляют из себя сумму двух компонент:

Регулярную составляющую
Случайную составляющую

В свою очередь регулярная составляющая состоит из:

Тренда
Сезонности
Циклической составляющей

Однако, в модели необязательно наличие всех этих компонент сразу.

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

То есть, в общем случае временной ряд представляет из себя наличие четырех составляющих:

Тренд (Tt)
Сезонность (St)
Цикличность (Ct)
Случайные возмущения (Et)

Циклическая компонента, по сравнению с сезонностью, имеет более длительный эффект и меняется от цикла к циклу. Поэтому, ее обычно объединяют с трендом.

Мультипликативная модель

Смешанная модель

При выборе необходимой модели временного ряда смотрят на амплитуду колебаний сезонной составляющей. Если ее колебания относительно постоянны, то выбирают аддитивную модель. То есть, амплитуда колебаний примерно одинакова:

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

Построение этих моделей сводится к расчету тренда (Tt), сезонности (St) и случайных возмущений (Et) для каждого уровня ряда (Yt).

Выравниваем ряд с помощью скользящей средней, то есть сглаживаем ряд и отфильтровываем высокочастотные колебания.
Рассчитываем значение сезонной компоненты St.
Рассчитываем значения Tt с использованием полученного уравнения тренда.
Используя полученные значения St и Tt, находим прогнозные значения уровней временного ряда.
Оцениваем качество модели.

Итак, мы имеем на руках данные о продажах за 2016 и 2017 год и хотим спрогнозировать продажи на 2018 год.

Шаг 1

Следуя нашему алгоритму, мы должны сгладить временной ряд. Воспользуемся методом скользящей средней. Видим, что в каждом году есть большие пики (май-июнь 2016 и апрель 2017), поэтому возьмем период сглаживания пошире, например, месячную динамику, т.е. 12 месяцев.

Удобнее брать период сглаживания в виде нечетного числа, тогда формула для расчета уровней сглаженного ряда:

Но так как мы решили использовать месячную динамику в виде четного числа 12, то данная формула нам не подойдет и мы воспользуемся этой:

Иными словами, мы учитываем половины от крайних уровней ряда в диапазоне, в остальном формула не претерпела больше никаких изменений. Вот ее точный вид для нашей задачи:

Сглаживаем наши уровни ряда и растягиваем формулу вниз:

Сразу можем построить график из известных значений уровня продаж и их сглаженной. Выведем ее уравнение и значение коэффициента детерминации R^2:

В качестве сглаженной я выбрала полином третьей степени, так как он лучше всего описывал уровни временного ряда и имел наибольший R^2.

Шаг 2

Так как мы рассматриваем аддитивную модель вида:

Найдем оценки сезонной компоненты как разность между фактическими уровнями ряда и значениями скользящей средней St+Et = Yt-Tt, так как Yt и Tt мы уже знаем.

Используем оценки сезонной компоненты (St+Et) для расчета значений сезонной компоненты St. Для этого найдем средние за каждый интервал (по всем годам) оценки сезонной компоненты St.

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

В моделях с сезонной компонентой обычно предполагается, что сезонные воздействия за период взаимопогашаются. В аддитивной модели это выражается в том, что сумма значений сезонной компоненты по всем интервалам должна быть равна нулю. Поэтому найдя значение случайной составляющей, поделив сумму средних оценок сезонной составляющей на 12, мы вычитаем ее значение из каждой средней оценки и получаем скорректированную сезонную компоненту, St.

Далее, заполняем нашу таблицу значениями сезонной составляющей дублируя ряд каждые 12 месяцев, то есть три раза:

Шаг 3

Теперь рассчитываем значения уровня тренда T(t) по тому уравнению, которое мы получили при построении сглаженного тренда на первом шаге.

T(t) = - 23294 + 34114 * t - 1593 *t^2 + 26,3 *t^3

Вместо t используем значения из столбца Период из соответствующей строки.

Шаг 4

Имея рассчитанные значения S(t) и T(t) мы можем рассчитать прогнозные значения уровней ряда Y(t). Для этого накладываем уровни сезонности на тренд.

Теперь построим график известных значений Y(t) и спрогнозированных за 2018 год.

Вот мы и нашли спрогнозированные значения уровней продаж на 2018 год. Значения отражают возрастающую тенденцию и сезонные пики. Конечно, эти данные не дают 100% точности, ведь существует множество внешних воздействий, которые могут изменить направление тренда, поэтому к прогнозным значениям обычно строят доверительный интервал, это такой коридор, внутри которого могут колебаться прогнозные значения с заданной вероятностью (чаще всего выбирают 95%). Но об этом я расскажу в следующей статье.

Шаг 5

Осталось оценить точность модели. Для этого будем использовать среднюю ошибку аппроксимации, которая поможет рассчитать ошибку в относительном выражении. Иными словами, это среднее отклонение расчетных значений от фактических, которое вычисляется по формуле:

Модель может считаться адекватной, если:

Итак, рассчитываем ошибку аппроксимации для нашего случая. Так как в основе нашего тренда лежит полином третьей степени, прогнозные значения начинают хорошо повторять фактические значения к концу 2016 года, думаю, я думаю, поэтому корректнее было бы рассчитать ошибку аппроксимации для значений 2017 года.

Сложив весь столбец с ошибками аппроксимации и поделив на 12, получаем среднюю ошибку аппроксимации 4,13%. Это значение меньше 15% и можем сделать вывод об адекватности модели.

Не забывайте, что прогнозы не бывают точными на 100%. Любые неожиданные внешние воздействия могут развернуть значения уровней ряда в неизвестном направлении 🙂

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

Базовые понятия

Думаю, еще со школы все знакомы с линейной функцией, она как раз и лежит в основе тренда:

Построение модели

Итак, мы знаем объем продаж за прошедшие 9 месяцев. Вот, что из себя представляет наша табличка:

Определение коэффициентов модели

Строим график. По горизонтали видим отложенные месяцы, по вертикали объем продаж:

Вот, что получилось:

На графике мы видим уравнение функции:

y = 4856*x + 105104

Она описывает объем продаж в зависимости от номера месяца, на который мы хотим эти продажи спрогнозировать. Рядом видим коэффициент детерминации R^2, который говорит о качестве модели и на сколько хорошо она описывает наши продажи (Y). Чем ближе к 1, тем лучше.

У меня R^2 = 0,75. Это средний показатель, он говорит о том, что в модели не учтены какие-то другие значимые факторы помимо времени t, например, это может быть сезонность.

Прогнозируем

Чтобы рассчитать продажи за 10-ый месяц, подставляем в функцию тренда 10 вместо x. То есть,

y = 4856*10 + 105104

Получаем 153664 продажи в следующем месяце. Если добавим новую точку на график, то сразу видим, что R^2 улучшился.

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

Повысить точность модели можно добавлением сезонности к функции тренда, что мы и сделаем в следующей статье.

Читайте также: