График сезонности продаж по трем товарным группам вашей компании пример

Прогнозирование продаж в Excel не сложно составить при наличии всех необходимых финансовых показателей.

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

Линейный тренд хорошо подходит для формирования плана по продажам для развивающегося предприятия.

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

Пример прогнозирования продаж в Excel

Рассчитаем прогноз по продажам с учетом роста и сезонности. Проанализируем продажи за 12 месяцев предыдущего года и построим прогноз на 3 месяца следующего года с помощью линейного тренда. Каждый месяц это для нашего прогноза 1 период (y).

Уравнение линейного тренда:

y = bx + a

• y — объемы продаж;
• x — номер периода;
• a — точка пересечения с осью y на графике (минимальный порог);
• b — увеличение последующих значений временного ряда.

Допустим у нас имеются следующие статистические данные по продажам за прошлый год.

1. Рассчитаем значение линейного тренда. Определим коэффициенты уравнения y = bx + a. В ячейке D15 Используем функцию ЛИНЕЙН:



2. Выделяем ячейку с формулой D15 и соседнюю, правую, ячейку E15 так чтобы активной оставалась D15. Нажимаем кнопку F2. Затем Ctrl + Shift + Enter (чтобы ввести массив функций для обеих ячеек). Таким образом получаем сразу 2 значения коефициентов для (a) и (b).



3. Рассчитаем для каждого периода у-значение линейного тренда. Для этого в известное уравнение подставим рассчитанные коэффициенты (х – номер периода).



4. Чтобы определить коэффициенты сезонности, сначала найдем отклонение фактических данных от значений тренда («продажи за год» / «линейный тренд»).



5. Рассчитаем средние продажи за год. С помощью формулы СРЗНАЧ.



6. Определим индекс сезонности для каждого месяца (отношение продаж месяца к средней величине). Фактически нужно каждый объем продаж за месяц разделить на средний объем продаж за год.



7. В ячейке H2 найдем общий индекс сезонности через функцию: =СРЗНАЧ(G2:G13).
8. Спрогнозируем продажи, учитывая рост объема и сезонность. На 3 месяца вперед. Продлеваем номера периодов временного ряда на 3 значения в столбце I:



9. Рассчитаем значения тренда для будущих периодов: изменим в уравнении линейной функции значение х. Для этого можно просто скопировать формулу из D2 в J2, J3, J4.
10. На основе полученных данных составляем прогноз по продажам на следующие 3 месяца (следующего года) с учетом сезонности:

Общая картина составленного прогноза выглядит следующим образом:

График прогноза продаж:

График сезонности:

Алгоритм анализа временного ряда и прогнозирования

Алгоритм анализа временного ряда для прогнозирования продаж в Excel можно построить в три шага:

1. Выделяем трендовую составляющую, используя функцию регрессии.
2. Определяем сезонную составляющую в виде коэффициентов.
3. Вычисляем прогнозные значения на определенный период.

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

• бланк прогноза деятельности предприятия

Чтобы посмотреть общую картину с графиками выше описанного прогноза рекомендуем скачать данный пример:

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

Особенности расчета коэффициента сезонности:

1. Сезонность учитывает влияние внешних факторов на изменение продаж. Основным признаком показателя сезонности является то, что мы не можем оказать влияние на его изменения. Например, увеличение продаж на цветы и конфеты в праздники 8 марта или рост объемов продаж мороженного в летний период. Мы не можем повлиять с вами на жаркую погоду или наличие праздников. Следовательно, все эти факторы являются внешними и относятся к сезонным колебаниям.
2. Расчет сезонности исключает внутренние факторы изменения продаж Исключаем все колебания продаж в текущем периоде связанные с деятельностью магазина, которая не планируется в будущем. Например, проведение промо акций или отсутствием товара на полке.
3. Используются только сопоставимые продажи по магазинам – показатель LFL

Совет:

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

Факт продаж искомого периода Факт продаж равносильного периода в прошлом

Как рассчитать сезонность продаж? Формулы и таблица сезонности в Excel.

Давайте рассмотрим пример расчета. Предположим, что сейчас январь месяц и нам надо рассчитать сезонность продаж на следующий год по месяцам.

1. Исключаем все внутренние факторы, которые не планируются в будущем (акции, недопоставки и т.д.)
2. Учитываем только магазины и объекты LFL.
3. Возьмем продажи за прошлый период –  за год. Так как на дворе январь, то нам необходимо посмотреть на сколько продажи последующих месяцев отличаются от января.
4.  Делим продажи февраля прошлого периода на январь прошлого периода.  И так последующие месяцы.
5.  В результате мы получаем коэффициент, перемножив который на текущие продажи января мы получим прогноз продаж на будущий период.

Коэффициент сезонности в феврале относительно января 

Сезонностьфевраля=

Факт продажфевраля Факт продажянваря

Коэффициент сезонности товаров по месяцам в продажах по категориям

Каждая категория товаров имеет свое сезонное изменение продаж. Индекс сезонности в таблице рассчитан:

• • на основе факта продаж 456 магазинов формата Супермаркет
• • исключены все продажи промо акций
• • Учтены только сопоставимые магазины, присутствующие в течении всего периода (LFL)
• • Анализ построен на основе трех лет продаж

КатегорииБакалеяВодаДиабетическое питаниеКондитеркаМолокоМясоОвощи и фруктыПивоПтицаРыба

Индекс сезонности в сентябре относительно января больше на 23%

Наибольшие продажи по воде приходятся на май- на 36% больше продаж января

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

 

Продажи по дням недели

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

Поделись или поставь Like, чтобы не потерять информацию:

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

• Сокращаем товарный запас магазина.
• Анализ LFL показателей в торговле – метод сопоставимых продаж
• Доступность товара на полке 
• Как пошагово сделать заказ на поставку товара
• Инфляция и рост цен  — каким данным можно верить?
• Фронт маржа, Бэк маржа, Гросс маржа, Наценка

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

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

Что такое сезонность продаж

Объём реализации многих товаров и услуг зависит от времени года и погоды. В «горячий» сезон продукцию буквально расхватывают с колёс, в «холодный» продажи падают до нуля.

В этом утверждении есть определённая условность. Для лучшего понимания следует привести примеры:

• Прохладительные напитки. Продажи вырастают летом и падают зимой.
• Мороженое. Аналогичная ситуация. Конечно, продукт продаётся и зимой, но объёмы продаж несоизмеримы.
• Замороженные полуфабрикаты. Как ни странно на первый взгляд, это тоже летний продукт. Продажи резко возрастают с началом дачного сезона — быстрое приготовление, не надо возиться на даче и по приезде домой. Другие периоды падения спроса — посты, особенно великий. Примечание. Сезонность продаж здесь не так однозначна, как в предыдущих примерах. Некоторые небольшие предприятия, попавшие в нишу, вообще не замечают связанных с погодой изменений.
• Летняя и зимняя одежда. Обычно покупается в начале соответствующего сезона. В качестве подтверждающего тезис примера можно привести весенне-осенние распродажи. Магазины стремятся освободить склады от непроданного товара, который иначе будет лежать мёртвым грузом 3-4 месяца.
• Автомобили. Можно дискутировать о динамике продаж в автосалонах, но есть бесспорный факт: в конце года автосалоны стремятся быстрее распродать остатки. Дело не только в выполнении месячного, квартального, годового плана. После нового года машина станет прошлогодней, и это серьёзно повлияет на стоимость её .
• Курортные отели, гостиницы, дома отдыха. Сезон окончен, поток туристов иссяк.

Ещё существует связь динамики продаж с конкретными датами:

• Цветы. Продаются в течение года, в праздники больше, между ними меньше. Возможно, присутствует рост в конце недели, когда пары ходят на свидания. Есть один пик, когда продажи возрастают многократно — 8 марта. Ходит даже шутка, что Клара Цеткин и Роза Люксембург придумали Международный женский день, потому что у них был бизнес по торговле цветами.
• Носки и пена для бритья плюс разные мужские принадлежности. Резкий рост, как уже догадались, приходится на 23 февраля.
• Яйца. Пик приходится на Пасху. Разумеется, только в регионах, где преобладает христианство.

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

Как проводить анализ динамики продаж

Для достоверного прогнозирования необходимо иметь информацию о продажах за прошлые периоды.

Чтобы планирование было достоверным, надо обработать данные минимум одного года. Можно, конечно, взять и меньшие интервалы, например, полугодие, квартал, месяц. Ввести индикаторы объёмов продаж минимального — неделя и максимального — две недели или месяц, и, исходя из них, формировать складские запасы.

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

Более полную картину покажут данные за год. Для полноценного же анализа потребуется информация за 3…5 и больше лет.

Почему требуется многолетний период для достоверного прогноза продаж

На спрос может повлиять множество случайных факторов. Чтобы их учесть, надо сравнить данные за аналогичные периоды разных лет. Кроме собственно реализованных объёмов, сравнивают дополнительные факторы.

Пример: продажа мороженого. Эта продукция продаётся преимущественно в жаркое время года — лето. Если оно выдалось холодным, продажи заметно проседают. Значит, для анализа собирают не только количество реализованного товара, но и информацию о погоде. Например, температуру воздуха конкретно рассматриваемого регионе. Если погода и сбыт коррелируют, учитывают при планировании оба фактора.

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

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

Расчёт коэффициентов

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

Пример. Объём продаж некоторого товара по месяцам составил

20, 25, 37, 39, 45, 51, 55, 57, 46, 32, 27, 23

Вычисляют среднее значение, оно равно 38,083.

Затем вместо каждого из 12 чисел подставляют коэффициент равный этому значению, делённому на среднее: 0,53, 0,66, 0,97, 1,02, 1,18, 1,34, 1,44, 1,50, 1,21, 0,84, 0,71, 0,60. Для удобства здесь и дальше оставляем только 2 знака после запятой.

Теперь сразу видно, если коэффициент больше единицы, продажи выше среднего, меньше — ниже.

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

20, 25, 37, 39, 45, 51, 55, 57, 46, 32, 27, 23, 22.

В ряду коэффициентов соответственно будет

0,53, 0,66, 0,97, 1,02, 1,18, 1,34, 1,44, 1,50, 1,21, 0,84, 0,71, 0,60, 0,58.

Получается, в последующий январь продажи были выше, чем в предыдущий. Для корректного планирования важно понимать, это случайное превышение (совпадения в объёмах достаточно редки) или идёт тенденция к увеличению.

Обычно, разовой погрешностью до 20% пренебрегают, считая её случайностью. При большей разнице приходится выяснять, почему это произошло, чтобы понять, закладывать большие или меньшие объёмы в прогноз или нет. При падении сбыта — нужно ли искать его причины.

Учёт тренда

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

Предположим, в следующие месяцы показатели были такими (выделены жирным):

20, 25, 37, 39, 45, 51, 55, 57, 46, 32, 27, 23, 22, 28, 41.

Имеем в течение трёх месяцев (квартала) увеличение продаж на 10 %.

Вносим поправки в годовой прогноз:

22, 28, 41, 43, 50,56, 61, 63, 51, 35, 30, 25.

Или в коэффициентах:

0,58, 0,72, 1,07, 1,13, 1,30, 1,47, 1,59, 1,65, 1,33 0,92, 0,78, 0,66, 0,64.

Примечание. При переводе коэффициентов в натуральные единицы, следует брать данные предыдущего года, т. к. увеличение на 10% — по отношению к нему, а не к текущему.

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

Использование медианного значения вместо среднего

Часто при расчётах и прогнозировании вместо среднего значения используют медианное.

Расположим данные продаж в течение года в порядке возрастания. Выберем среднеарифметическое средних (5 и 6) значений.

Примечание. Если количество чисел в ряду нечётное, берут срединное из них. Поскольку в рассматриваемом случае — чётное, 12, за основу берутся 5-ое и 6-ое.

20, 23, 25, 27, 32, 37, 39, 45, 46, 51, 55, 57.

(32+37)/2= 34,5.

Теперь построим ряд коэффициентов по отношению к полученному медианному значению (объёмы продаж базового года, делённые на медиану):

0,58, 0,72, 1,07, 1,13, 1,30, 1,48, 1,59, 1,65, 1,33, 0,93, 0,78, 0,67, 0,64.

Сопоставим полученные данные с коэффициентами, вычисленными по среднему:

0,53, 0,66, 0,97, 1,02, 1,18, 1,34, 1,44, 1,50, 1,21, 0,84, 0,71, 0,60.

Видим, что в 3 месяце (марте) по средним данным коэффициент меньше 1 — падение продаж ниже средней величины. По медианным коэффициент больше 1 — рост, продажи выше срединного значения.

Построение графиков сезонности продаж

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

Рекомендуется использовать следующие индикаторы:

• Скользящие средние. Усредняют значение показателей за заданный период времени. Обычно используют неделю, 2 недели, месяц, квартал. Кроме отсева случайных величин они могут показать изменение тенденций. Если средняя с меньшим периодом (например, недельная) пересекает месячную снизу вверх, идёт увеличение продаж. Если сверху вниз — падение. Построить индикатор можно на графике в Exel во вкладке «анализ данных».
• RSI – относительная сила тренда. Кроме общей тенденции показывает, насколько продажи близки к своему верхнему или нижнему пределу. Пример. График продаж имеет два пика, второй выше первого. Индикатор находится в зоне выше 70 или 80%% от максимума (перекупленность) и тоже имеет два пика, но второй меньше первого. Это называется дивергенцией — расхождением и свидетельствует о развороте тренда — смене роста падением. Аналогичная ситуация в зоне минимальных значений индикатора — перепроданности. Здесь дивергенция предсказывает смену падения ростом. Это более сложный индикатор. Узнать о его построении можно, например, здесь.

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

Влияние маркетинговых мероприятий на продажи

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

Автопокрышки

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

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

Для принятия решения требуется просчитать все варианты, сопоставить плюсы и минусы мероприятия.

Цветы

Продажа цветов, как уже было сказано, имеет выраженный пик — 8 марта. В остальное время они продаются в гораздо меньших количествах. Стимулировать объёмы предложениями 5 по цене 3 не имеют коммерческого смысла, поскольку практически не влияют на выручку. Исключение — дамы, покупающие цветы для себя.

Вариант «Молодой человек, у вас нет ни жены, ни подруги. Купите цветы, раз уж вы такой счастливый!» рассматривать, скорее всего, не стоит. Это разовая акция.

Ситуация с носками и пеной для бритья ещё выразительнее. После 23 февраля продажи круто проседают. Товар куплен и подарен. Пока он не израсходуется, новых покупок можно не ждать.

Алкоголь

Покупается всегда и имеет пики в праздники. Яркий пример — шампанское к Новому Году. В остальное время стимулировать продажи можно акциями со скидками при покупке нескольких единиц товара. Плюс грамотные консультации о преимуществах того или иного сорта вин, коньяков и т. д. Главное — помнить о ст. 21 Закона о рекламе.

Курортные гостиницы

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

Поздней осенью, зимой и ранней весной — затишье.

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

Подслушанный разговор владельца одной из черноморских гостиниц с потенциальным клиентом, собирающимся привезти зимой на сбор какую-то команду: «Зачем ты сразу спрашиваешь, сколько это будет стоить? Давай сядем и обсудим, сколько это ДОЛЖНО стоить, чтобы и тебе было интересно, и мне тоже».

Рекомендовать такой подход не всегда морально и законно, но и полностью исключить его тоже неправильно.

Заключение

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

Наиболее корректные результаты получаются при комплексном подходе — использовании и сопоставлении разных методов анализа, обработке значений нескольких лет и использовании приёмов маркетинга.
Для этих целей мы разработали сервис, который встраивается в типовые программные продукты 1С и расширяет их возможности по анализу продаж и прогнозированию спроса.

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

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

Одна из проблем, сильно влияющих на составление плана – сезонность продаж некоторых товаров. Одни товары, например — кроссовки для бега, летом — более популярны, чем зимой. А вот обогреватели продаются лучше в холодное время года. Эти товары — сезонные.

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

Потому, при составления плана продаж, учитывайте сезонной фактор, и тренды в компании.

Каким образом мне рассчитать план с учетом сезонных колебаний?

Что такое сезонный фактор — «сезонность»? Это запланированное и регулярное отклонение продаж товара от среднестатистических значений. Сезонность часто рассчитывается помесячно на календарный год относительно предыдущего календарного года для каждого товара, на который строится план продаж и по каждой торговой точки индивидуально, а итоговый план составляется путем консолидации из полученных значений.

Для расчета коэффициентов я рекомендую рассчитывать в штучном выражении. Если вы рассчитываете в денежном выражении, то количество влияющих факторов увеличивается многократно и это, кроме того что увеличит объем расчетов, еще и сильно увеличит шанс ошибки.

Рассчитать годовые коэффициенты сезонности достаточно просто – необходимо взять средние месячные продажи на конец года (сумма продаж на год, деленное на количество), а потом, для каждого месяца, рассчитать отклонение фактического объема продаж от среднегодового.

(Потребление в месяц / Среднегодовое потребление = Сезонный коэффициент)

Если у нас график продажи сложились примерно вот так вот:

То по результатам расчета, должно получиться приблизительно такая табличка для расчета (для 2010 года):

Сезонные коэффициенты:

Но задача — не рассчитать коэффициенты как таковые, а рассчитать план продаж, согласно текущим фактическим значениям продаж в году. Предположим, что мы проводим анализ в конце апреля 2011 года и рассчитываем план продаж на май 2011:

И наша табличка будет выглядеть вот так вот:

Задача – понять, сколько мы должны продать на май, с учетом текущих фактических объемов продаж и сезонности. Для этого — каждый из месяцев текущего года приведем к единой базе, убрав из них сезонный коэффициент, которые мы знаем.

(Факт Потребление в месяц / Сезонный коэффициент = Оц Среднегодовое потребление)

Получим вот такие значения:

Что означает, что если учитывать сезонные факторы, то ожидаемая средняя месячная на год равна 246 шт./мес.

Из этого, зная ожидаемую среднюю на год и сезонный коэффициент в мае (рассчитанный на предыдущем шаге), рассчитываем — сколько ожидается продаж в мае месяце, умножив ожидаемые среднегодовые продажи на рассчитанный сезонный коэффициент: 246 * 1,44 = 354,4 шт.

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

К сожалению, эти лаконичные расчеты не совсем корректны…

Мы учли влияние сезонных колебаний, но не рассчитали влияние общего тренда. Если у вас спрос падает (или растет) на 10% каждый месяц по объективным причинам, то без учета этих движений ваш вновь составленный план станет несостоятельным, и, как мы уже говорили выше — приведет вас к убыткам.

Как оценить влияние тренда?

Выделить и рассчитать тренд — само по себе сложная задача. Я рекомендую использовать метод, разработанный Робертом Ходриком и Эдвардом Прескоттом для анализа бизнес-циклов. Основные «фишки» метода: метод более чувствителен к долгосрочным тенденциям, чем к краткосрочным (что нам и нужно), и его можно настраивать на нужный период оценки тренда (что полезно при построении тренда как внутри года, так и на несколько лет). Узнать больше об этом методе можно по ссылке: http://mycroftbs.ru/trend-filtr-hodrik-preskotta/

Выглядит результат его расчета примерно вот так (оранжевая линия):

Проблема в том, что такой метод сложно использовать при расчетах в экселе. Но можно попробовать использовать просто линейные функции, посчитав средние месячные продажи на состояние «на начало года» и «на конец года» (с учетом сезонности), и оценив то, как она изменилась со временем. Или просто взяв за целевое значение, то, на которое вы бы хотели бы ориентироваться («я уверен, что объем продаж должен вырасти на 10%»).

Как бы то ни было – результат расчетов, это полученные ежемесячные коэффициенты «наклона» тренда для каждого из товара по каждой из торговых точек на каждый месяц, где вы рассчитываете план продаж. Проблема еще и в том, что в нормальной ситуации, внутри года это не прямая линия, а плавно изгибается.

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

Если предположить, что в текущих экономических реалиях, спрос в штучном выражении будет падать на 10% к концу года, тогда ежемесячный корректирующий коэффициент должен быть, приблизительно, равен 0,987. Это означает, что на этот коэффициент мы изменим оценочную среднюю месячную внутри текущего года согласно трендовому коэффициенту на каждый месяц:

(Факт Потр. в мес. / Сез. Коэф * Тренд. коэффициент = Оц Среднегодовое потребление)

А расчет текущих значений будет выглядеть так:

Заметили, что в результате получилось 349,8 шт. вместо ранее рассчитанных 354,4 шт.? Кажется, что это не очень много, но если у вас миллиардные обороты, то такая погрешность стоит очень много.

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

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

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

Если это слишком трудоемко делать в «ручном режиме», и в том случае, если у вас 10 торговых точек и 15000 товаров, то — добро пожаловать к нам. Наше решение сделает все за вас. Mycroft Assistant в режиме реального времени автоматически собирает данные о продажах, анализирует текущее состояние продаж, самостоятельно высчитывает оптимальную модель работы и влияющие коэффициенты. И на основании полученных данных формирует прогноз продаж для каждого из товаров на каждой из торговых точек. А на основании данных прогнозов выдает рекомендации о необходимости пополнения запасов, чтобы вы этот план продаж рационально обеспечили. Так что, если хотите оптимизировать работу компании, но не знаете каким образом лучше всего это сделать – добро пожаловать к нам.

Так же, приглашаем вас на вебинар «Эффективное управление запасами», который проводит наш партнер Корус-Консалтинг. Регистрация доступна по ссылке: http://korusconsulting.ru/press-center/events/demonstracia-upravlenie-zapasami-03-02-2016.html.

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

Из чего состоит временной ряд

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

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

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

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

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

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

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

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

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

Виды моделей временного ряда

Обычно, выделяют две модели временного ряда и третью — смешанную.

1. Аддитивная модель 

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

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

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

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

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

Алгоритм построения модели

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

Реализация на практике

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

Шаг 1

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

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

yi — фактическое значение i-го уровня ряда,

yt — значение скользящей средней в момент времени t,

2p+1 — длина интервала сглаживания.

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

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

yi — спрогнозированные уровни ряда,

yi* — фактические уровни ряда,

n — количество складываемых элементов.

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

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

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

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

Полезные ссылки:

• Ссылка на пример Google Sheets
• Построение функции тренда в Excel. Быстрый прогноз без учета сезонности
• Бывшев В.А. Эконометрика

Как рассчитать коэффициент сезонности

Для того, чтобы сформировать заказ поставщикам, распределить запасы РЦ по филиалам и сбалансировать первые между вторыми, необходимо спрогнозировать спрос. Но важно понимать, что спрос не равно продажи. 

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

Формула коэффициента сезонности

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

Классический метод по средним продажам

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

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

Коэффициент сезонности каждого месяца = продажи в штуках этого месяца/ продажи за год.

Коэффициент сезонности = среднее значение из коэффициентов по конкретным месяцам.

Рассмотрим на примере.

В строке 21 посчитаны средние продажи за год. Показатель февраля – 8307. Затем мы посчитали средние продажи за второй год. Цифра за февраль – 14243, и так далее для каждого года. После реальные продажи за каждый месяц (строка 20) поделили на средние – за год (строка 21).

7322/8307 = 0,8814 – это коэффициент сезонности для февраля.

Получаем набор коэффициентов для каждого месяца. Чтобы найти общий суммарный коэффициент по месяцу, берём среднее значение и получаем коэффициент сезонности.

Расчёт сезонности с очисткой от тренда

Первый шаг будет таким же, как и в предыдущем методе – суммируем продажи по месяцам. Затем вместо средних продаж рассчитываем тренд. В Exel это можно сделать с помощью функции «Тенденция». Затем проводим расчёт коэффициента сезонности для каждого месяца.

Итак, как считается коэффициент сезонности?

Коэффициент сезонности = Продажи в месяц/ тренд

После того как мы получили набор коэффициентов для каждого месяца, ищем не средние значения, а медиану. Это число, которое находится в середине. Половина получившихся значений выше его, а половина ниже. Рассмотрим на примере.

Итак, у нас есть данные по спросу по месяцам. Строим функцию тренда (с помощью функции «Тенденция» в Excel). Не забываем исключать дефициты и прочие факторы, которые могут привести к ошибкам в расчётах. Делим спрос на получившееся значение тренда и получаем коэффициент сезонности.
И так для каждого месяца:

В чём ключевые отличия этого метода от классического? Во-первых, в расчёт коэффициента сезонности идёт не показатель средних продаж, а тренд. Во-вторых, вместо средних значений – медиана. Так, например, за апрель у нас всего шесть значений сезонности: два по 1,01, два ниже этого показателя и два выше. Значит, медианой для апреля будет показатель 1,01. 

Почему лучше брать медиану?

Этот показатель наиболее стабилен к выбросу. Посмотрим на таблице ниже.

Мы видим: в январе 2017 года по товару был низкий коэффициент сезонности – 0,4. При этом в аналогичные периоды других лет продажи были стабильными – 1,2 – 1,3. Если посчитать среднее значение, мы получим, коэффициент 1,03. Это означает, что товар в этом месяце не обладает сезонностью, но это то не так. Медиана более устойчивый показатель. Если брать в расчёт её, то коэффициент будет 1,2. Это уже говорит об умеренной сезонности и ближе к правде.

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

Если взять в расчёт средний показатель, мы увидим, что товар не обладает сезонностью. По медиане же мы получили коэффициент 0,9, который говорит, что продажи в январе ниже средних. Поэтому для расчёта сезонности по каждому месяцу лучше брать медиану. 

Как считать: по отдельному товару или по группе? 

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

Нужно ли агрегировать данные для расчёта?

Чтобы минимизировать влияние случайного фактора, можно агрегировать данные для расчёта. Но делать это нужно только по складам и магазинам одинакового формата и похожего географического положения. Например, по всем магазинам у дома ЦФО или по супермаркетам Дальнего Востока. Возможно объединять похожие по географии магазины внутри города. Например, отдельно считать сезонность по проходным точкам, отдельно по магазинам в спальных районах и т.д. Делать прогноз с учётом сезонности одновременно для точки во Владивостоке и в Москве не имеет никакого смысла. 

Как посчитать недельную сезонность?

Иногда этот показатель важен. Например, перед 8 марта традиционно растёт спрос на конфеты. Как считается коэффициент сезонности в таких случаях? Мы можем посчитать недельную сезонность так же, как и месячную. Агрегируем данные по неделям, даём им номера и считаем коэффициенты. Но у вас получится уже на 12, а 52 коэффициента. А чем больше декомпозиция данных, тем сложнее расчёты и их интерпретация. Если вам важен показатель недельной сезонности, рассчитывайте его отдельно.

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