Как составить прогноз продаж на примере

Продавать без ориентира на результат — значит пустить продажи на самотёк. А ведь назвать размер прибыли за измеримый период можно с точностью до 90%. Для этого необязательно быть экстрасенсом, достаточно овладеть методиками прогнозирования, но для начала разберёмся, зачем нужен прогноз.

Зачем прогнозировать продажи

• Чтобы ставить цели. Цель — это сумма, которую компания должна получить через месяц, квартал, год. От этой цифры руководитель отдела продаж отталкивается, когда ставит менеджерам KPI и планирует премии.
• Чтобы снизить расходы. Прогнозирование продаж товаров помогает оптимизировать затраты на производство и логистику. В случае провала плана, эти направления первыми попадут под нож.
• Чтобы планировать ресурсы. Когда понятно, сколько получится заработать, можно планировать закупки, найм персонала, рекламу и аренду складов.
• Чтобы управлять складскими остатками. Когда перед глазами вероятная прибыль, понимаешь, сколько товара и по какой цене нужно продавать. Это помогает избежать дефицита или переизбытка на складе.
• Чтобы прогнозировать спад. Смягчить удар для бизнеса и остаться на плаву при снижении продаж можно, если учитывать причину падения спроса — например, сезонность.

Планирование и прогнозирование продаж: в чём разница

Прогноз продаж — это гипотеза.

То есть предположение о том, в каком объеме и за какой срок будет реализован товар. Аналитики строят гипотезы на основе исследования потенциала продаж — доли рынка. И только после того, как сформулированы гипотезы, составляют прогноз. Прогнозировать, опираясь на желание или интуицию, нельзя.

План продаж — это задача.

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

Методы прогнозирования объёмов продаж

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

Субъективные методы

• Ожидания пользователей

  Также известен как «метод изучения намерений покупателей». Помогает исследовать готовность потребителей приобрести услугу или товар. Этот метод больше оценивает потенциал рынка, нежели прогнозирует продажи. Точность метода невысокая, поскольку между намерением купить и покупкой — огромная пропасть, особенно при выводе на рынок новых товаров и услуг. Проблема в том, что потребители говорят о желании купить товар с определёнными функциями, но, в итоге, не покупают. Потому что, на самом деле, их интересуют не функции, а выгоды, которые они получают. Например, потребитель говорит, что ему нужен телефон с выходом в Интернет. На самом деле, он хочет тратить меньше времени на покупки, заказывая товары с доставкой на дом.

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

• Мнение продавцов

  Также известен как «сбор мнений торгового персонала». Такой метод ставит прогноз на основе предположений продавцов о том, какой объем продукции они рассчитывают продать в течение заданного периода. Затем мнения систематически корректируются. Этот метод тоже нельзя назвать сверхточным, поскольку сотрудники либо недооценивают, либо переоценивают свои способности. Когда реальные показатели продаж оказываются выше спрогнозированных, создаётся впечатление, что сотрудник сверхэффективен. А когда ниже, то не всегда это говорит о неэффективности, т.к. на сбыт могут влиять внешние факторы (дефицит товаров, например).

• Мнение менеджеров компании

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

• Метод экспертных оценок

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

Объективные методы

• Рыночное тестирование

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

• Анализ временных рядов

  Метод базируется на анализе «исторических» данных за прошедшие периоды времени. В простом варианте может точно определить объем сбыта для зрелой отрасли с незначительным ростом рынка. В иных условиях требует более сложным методов анализа. Например, таких:

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

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

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

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

  Тренд определяется вторым и показывает долгосрочные изменения во временном ряде.

  Циклический фактор определяется третьим и отражает подъемы и спады, длящиеся от двух до пяти лет.

  Случайный фактор показывает воздействия на кривую продаж после исключения других трёх факторов.

• Анализ годовых графиков

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

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

Дополнительные методы

• Каузальный метод

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

Прогнозирование продаж на квартал в Excel

Рассмотрим пример прогнозирования продаж в Excel. Допустим, у развивающегося предприятия стоит задача — составить прогноз на квартал с учетом роста и сезонности. Для решения этой задачи возьмём статистику по продажам за прошлый год (Рис. 1) и рассчитаем значение линейного тренда с помощью уравнения (Рис. 2).

Для нахождения коэффициентов a и b применим функцию ЛИНЕЙН для ячейки D15. Далее выделим ячейки D15 и E15 так, чтобы активной осталась только D15. Жмём F2, а затем Ctrl + Shift + Enter. Получаем показатели a и b (Рис. 3).

Теперь рассчитаем у для каждого периода тренда. Для этого подставим коэффициенты a и b в уравнение, где х — номер периода (Рис. 4).

Для того чтобы получить коэффициент сезонности, находим отклонения показателей продаж за год от значений тренда — B2/D2 (Рис. 5).

Рассчитаем средние показатели продаж за год, используя формулу СРЗНАЧ (Рис. 6).

Определим ежемесячный индекс сезонности с помощью формулы =B2/$F$2 (Рис.7).

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

Продлим номера периодов временного ряда на три значения в столбце «Периоды для прогноза» (Рис. 9).

Рассчитаем значения тренда для будущих периодов. Изменим в уравнении значение х путем копирования формулы из D2 в J2, J3, J4 (Рис. 10).

Дальше, используя формулу J2*G2, получим данные для прогнозирования продаж продукции на квартал (Рис. 11).

Так выглядит прогноз в Excel-таблице (Рис. 12) и в графиках (Рис. 13), (Рис. 14).

Прогнозирование продаж в 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. Вычисляем прогнозные значения на определенный период.

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

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

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

Бизнес в современных условиях развивается стремительно. На динамику продаж, доходов, расходов оказывает влияние множество различных факторов, как внутренних (инвестиции, стимулирование отдельных каналов продаж, исследование рынка и т.п.), так и внешних – различные непредвиденные обстоятельства, вроде, пандемий, стихийных бедствий, исторические событий. 

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

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

Меня зовут Нина Фещенко, с 2022 года в департаменте аналитики розничного бизнеса (B2C) я в большей степени занимаюсь работой с данными и BI-аналитикой, но без моделирования тоже не обходится. В этом посте я опишу свой опыт построения модели прогнозирования продаж конвергентных продуктов (2019-2021), а также прогнозирования оттока мобильных абонентов в 2022 году. Расскажу, как работала модель в относительно стабильный период до 2020-го года, и какие корректировки пришлось внести впоследствии. 

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

Давайте подробнее о том, что следует понимать под продажами FTTB и конвергенции. 

Термин FTTB представляет собой тип фиксированного подключения ШПД (широкополосного доступа) к сети интернет, при котором оптоволоконный кабель протягивается до границы здания и распределительный узел находится максимально близко к помещению абонента. Под продажей FTTB подразумевается факт подключения абонента. Понятие «конвергентный продукт» включает в себя подключение FTTB интернета совместно с подключением мобильной связи (FMC). 

Обозначения – и + в сегментах FMC означают, соответственно, отсутствие или наличие у абонента до подключения доступа к интернету (F) или мобильной связи (M) билайна. С точки зрения развития конвергентного бизнеса интерес представляют только продажи так называемых «минусов», под которыми понимаем отсутствие у абонента доступа к сети интернет билайна. Это собственно подключение FTTB-интернета, а также подключение интернета и продажа sim-карты (F-M-) или подключение интернета и объединение существующей у абонента sim-карты в общий конвергентный продукт (F-M+). 

На рисунке выше эти фокусные сегменты выделены синим цветом. Соответственно, сегменты F+M- и F+M+, то есть ситуация, когда у абонента уже есть доступ к интернету билайна и речь идет только о продажи sim-карты или просто объединении существующего интернета и существующей sim-карты в общий продукт, в нашем случае интереса не представляют (сегменты без заливки).

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

Выбираем прогнозную модель

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

Простейшей моделью для прогнозирования продаж является линейный тренд вида:

где t – период времени.

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

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

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

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

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

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

где: 

F – прогнозное значение, 

T – линейный тренд вида yt+1=a0+a1t0, где t – период времени,

S – сезонные колебания, рассчитанные с использованием индекса сезонности.

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

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

где: 

Et – случайный остаток в периоде t, 

Yt – фактическое значение в периоде t, 

Tt  – линейный тренд вида yt+1=a0+a1t0, где t – период времени,

St –  сезонные колебания в периоде t.

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

Я использовала помимо основного ряда Et еще четыре ряда Et+1, Et+2, Et+3 и Et+4. Большее количество использовать уже не имеет смысла – это усложняет модель, к тому же если автокорреляция отсутствует на четырех рядах с лагом, то велика вероятность, что она вовсе отсутствует. 

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

где: 

Et – случайный остаток в периоде t, 

Et-k – случайный остаток в периоде t для ряда, сдвинутого на k периодов,

rk – коэффициент автокорреляции между рядом Et и сдвинутым рядом Ek.

С учетом полученного выражения для случайной компоненты окончательно трехкомпонентная аддитивная модель принимает вид:

где: 

Ft – прогнозное значение в периоде t, 

Tt – линейный тренд вида y_t+1=a₀+a₁t₀, где t – период времени,

St – сезонные колебания, в периоде t, 

Et – случайные колебания в периоде t.

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

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

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

где: 

τ – коэффициент доверия Стъюдента,

U’– стандартная ошибка выборки.

Здесь доверительный интервал с доверительной вероятностью 85% добавлен в виде светло-зеленой сплошной линии:

Строим прогноз

Прогноз я строила отдельно для каждого канала продаж, поскольку необходимо было учитывать их особенности. Так для активных каналов продаж (агенты, телесейл) характерны всплески продаж в конце месяца. Это объясняется управляемостью данных каналов – например, возможностью быстро скорректировать отставание от плана введением мотивационных программ и просто желанием самих агентов закрыть месяц максимально возможным количеством продаж. 

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

Кроме того, для решения задач долгосрочного прогнозирования, в частности бюджетирования продаж на следующий год, прогнозная модель делилась на сегменты – отдельно строился прогноз для продаж FTTB и отдельно для суммы «конвергентных минусов» – F-M-/F-M+. Это, возможно, выглядит излишним усложнением модели, однако с постепенным смещением фокуса продаж с FTTB на FMC (для продаж конвергенции вводились мотивационные программы, существовал отдельный KPI – доля FMC в продажах) тренды данных сегментов устремились в противоположные стороны.

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

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

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

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

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

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

Чем сложнее времена, тем проще должны быть модели

Потому что в условиях неопределенности главным качеством хорошей модели становится не столько аптечная точность, сколько устойчивость к изменениям и интерпретируемость. Преимущество линейной модели как раз в её простоте и лёгкости интерпретации – полученные результаты, только если зависимость между факторами не близка к линейной, будут далеки от идеальных, но их будет легко объяснить. Поэтому, когда возникла необходимость в поиске новой модели в условиях кризиса, то я не стала искать что-то принципиально новое, а просто преобразовала модель, оставив её линейную структуру и аддитивность. Я убрала из модели случайную компоненту и оставила только тренд + сезонность. Как уже было показано ранее, сезонность продаж сохраняется даже в условиях кризисов и существенных структурных сдвигов, поэтому упрощать модель максимально (только до линейного тренда) не имело смысла. В 2021-м году начался постепенный выход на тренды, что позволило мне вернуться к трехкомпонентной прогнозной модели.

В 2022-м году в фокусе моего внимания были уже не столько продажи, столько активная (платящая) абонентская база, а также отток, под которым будем понимать отсутствие активности и/или прекращение оплаты. Моделирование оттока напрямую связано с моделированием абонентской базы, точнее, является одним из этапов ее моделирования. Величина оттока (RTC) в процентах рассчитывается как количество неактивных абонентов за период к значению активной базы в предыдущем периоде (или к среднему значению активной базы в текущем и предыдущем периодах, но в данном случае рассмотрим более простой вариант):

где: 

c_t – отток в периоде t,

AB_t и AB_t-1 – активная база, соответственно, периода t и предыдущего периода,

Sl_t – продажи в периоде t.

Соответственно, преобразовав данную формулу, получаем выражение для активной базы отчетного периода:

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

Как и писала выше, до 2022 года все основные KPI я моделировала по трехкомпонентной аддитивной модели, в том числе и отток, который с некоторыми оговорками, но в нее укладывался. Отток как правило не имеет ярко выраженного тренда, что логично, величина оттока колеблется вокруг своего среднего значения. При этом сезонность оттока прослеживается. Есть месяцы с более высоким оттоком, например, периоды отпусков – лето, декабрь-январь, есть периоды со стабильным или низким оттоком. 

Вот пример трехкомпонентной модели оттока. 

Можно заметить, что тренд практически отсутствует. Это подтверждает и величина аппроксимации R² – значение 0.003 показывает, что простой линейный тренд совсем не подходит для описания данного ряда. Однако отсутствие тренда в данном случае не существенно, и это еще одно преимущество трехкомпонентной модели. Даже при отсутствии тренда она все равно решает свои задачи – сглаживает ряд, позволяет учесть сезонность и случайные колебания, оставляет возможности для регулирования – возможности завысить или занизить прогнозное значение в зависимости от поставленных задач. При анализе оттока это не менее актуально, чем для продаж. Например, для решения задач вида: на сколько процентов необходимо снизить отток, чтобы активная база вышла на бюджетные значения. 

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

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

Самым распространенным видом адаптивной модели является модель экспоненциального сглаживания вида:

где 

F_t – прогнозное значение для периода t,

Y_t-1 – последнее имеющееся фактическое значение,

F_t-1 – сглаженное значение для периода t-1,

a – веса модели.

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

Такую модель я использовала для прогнозирования оттока на один шаг вперед, то есть на фактических значениях до октября 2022 года я прогнозировала ноябрь и затем аналогично декабрь. В качестве первого сглаженного значения я использовала простое среднее первых 6 уровней. Вопрос заключался в том, какие значения использовать в качестве весов . Веса в модели выбираются исходя из условий задачи – насколько выраженное сглаживание ряда мы хотим получить (чем меньше значения a, тем выше эффект сглаживания). Важно было использовать какую-то значимую величину, а не просто некоторую случайную оценку. В итоге весами стали отклонения количества неактивных абонентов, регистрировавшихся на приграничных базовых станциях, в отчетном месяце по отношению к аналогичному периоду прошлого года. Для расчетов прогноза ноября эта величина составила 0.326, это отклонение сентября 2022 от сентября 2021 (на момент расчетов всех данных за октябрь еще не было).

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

Здесь заметно, что модель в ноябре дала очень хороший результат – разница между фактическим и прогнозным значением составила всего 0.004%. Результат по модели в декабре был уже не столь впечатляющим (см следующий рисунок) – отклонение составило 0.085%, сглаживание в данном случае оказалось чересчур сильным.

Веса в расчете декабря составили 0.426, напомню, что это отклонение количества неактивных абонентов с трафиком на приграничных базовых станциях в октябре 2022 по сравнению с октябрем 2021. Значение относительно небольшое, а отставание сглаженного ряда от фактического проявляется в большей степени при малых значениях a.

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

Вот как выглядит пример расчета с величиной a равной 0.700 (лиловая линия):

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

Экспоненциальная модель уже не подойдет для бюджетирования и прогнозирования на длительные периоды. Для сравнения по трехкомпонентной модели я строила прогнозы на срок до 5 лет, например, при построении стратегии развития FMC-бизнеса или при расчете данных для кейсов по отдельным проектам, вроде, амбиция по приросту активной абонентской базы на 30% через 5 лет. При этом преимущество экспоненциальной модели в ее адаптивности – прогноз уточняется каждый раз с получением новых фактических значений, а также в рекурсивности – каждое значение сглаженного ряда уже содержит информацию обо всех предыдущих значениях. То есть данная модель тоже неявным образом учитывает сезонность.

Итого

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

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

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

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

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

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

Как спрогнозировать продажи?

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

Эти методы разделяются на:

• экспертные оценки;
• анализ и прогнозирование временных рядов;
• причинно-следственные связи.

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

Управленческий прогноз

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

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

• доля жилья эконом-класса;
• сезонность;
• среднестатистический ценник на комплекты
  сантехники для каждой эконом-квартиры;
• средняя квадратура эконом-квартиры;
• объём жилстроительства по эконом- и элитному
  классам в общем потоке;
• доля жилья эконом-класса.

Из вышеприведённого примера следует, что управленческий
прогноз не идёт в отрыве от одного определённого фактора. Невнимание к такому
фактору может поставить под угрозу рост прогнозируемых продаж.

Линейный прогноз

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

Например, сотовый провайдер Yota в Ростове-на-Дону, на
Пойменной, 1, продал 1 ноября 2019 г. 120 SIM-карт с новым тарифом «Для
смартфонов». 2-го он продал 200, 3-го -40, 4-го – 100, 5-го – 140 SIM-карт. За
5 дней – 600. Разумно предположить, что за весь ноябрь, — салон работает каждый
день, — он может продать 36000 SIM-карт всем желающим. Это и есть линейный
прогноз. Но в реальности объём продаж, скорее всего, упадёт, — несмотря на то,
что безлимитный трафик у Yota самый дешёвый – 410 р. Продать они могут не 36000
SIM-карт, а, скажем, 15000 – насыщение рынка в районе этой точки продаж уже
случилось.

Вдруг, 2 ноября, Yota предоставляет скидку – безлимитный
трафик продаётся не за 410, а за 290 р. – по старой цене 2015-2017 гг. Что
будет? Продажи подстегнутся – Yota в этом салоне продаст 50000 SIM-карт за ноябрь. Причём
бум продаж придётся именно на первые дни – а не будет разгоняться постепенно.
Затем продажи пойдут на спад. Потому что все всем расскажут, что Yota снизила
тариф. Это уже линейно-управленческий прогноз: в дело вступил новый фактор –
снижение расценок на безлимитный Интернет. Параллельно, происходит отток
клиентов, которым нужен самый дешёвый безлимит, а не пакеты трафика – у
«Билайна», «МегаФона», «МТС» и «Теле2». Рынок абонентов-«безлимитчиков» перерассредоточился.

Формула прогноза продаж

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

Формула линейного прогноза продаж

Эта формула рассчитывается крайне просто:

Считаем среднее арифметическое по единицам
товара (как в примере выше, с SIM-картами провайдера Yota) за несколько дней,
например, неделю.

Умножаем среднее арифметическое на количество
недель в месяце, квартале или в году.

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

Переключиться на другие направления и виды
товаров, чтобы «отбить» образовавшиеся долги, или заполнить «прибыльную яму»;

Уценить и перепродать товар (со скидкой, за
полцены и т. д. – объявив распродажу).

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

Формула управленческого прогноза продаж

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

Здесь на помощь приходит нелинейный прогноз. Важно
отталкиваться от кривых прогнозируемого роста продаж. Кривая прогнозируемых
продаж может быть как степенной, так и полиномиальной (движение кривой по дугам
разных окружностей, устремлённым лишь вперёд). Линейный рост уже не
используется. Кривая может как проседать, так и резко стремиться вверх – при
срабатывании таких факторов, как:

• курсы валют;
• погода, условия доставки;
• цены конкурентов на аналогичный товар, их
  динамика;
• качество товаров одних (или похожих) видов,
  которыми вы и ваши конкуренты торгуете;
• планы розничных и оптовых покупателей.

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

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

Прогноз продаж в Excel

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

Например, в Excel
2007 дайте команду «Формулы» — «Финансовые». Список финансовых формул
представляет наибольшую ценность для любых экономических расчётов. С их помощью
несложно рассчитать деятельность практически любой коммерческой организации.  Однако бывают случаи, когда требуются и
математические формулы: так, для тренда есть понятие линейного,
логарифмического, гиперболического и т. д.

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

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

В качестве примера – расчёт прогноза продаж на неделю,
месяц, квартал и год.

Прогноз продаж на неделю

Примером служит следующий расчёт. Например, фермер реализует
в городе с 50000 человек населения молоко каждый день. Не все его хотят брать,
т. к. многим проще после работы заехать в тот же «Магнит» и закупиться. Берут в
основном пенсионеры и те, что ушли в отпуск или в декрет.

Значения, по которым рассчитаны график продаж и тренда,
позволяют отследить не только за неделю проданный в данной точке товар, но и за
весь месяц. Для расчёта линейного тренда используется такая же функция – график
y=ax+b. Это алгебраическое уравнение 1-й степени, известное нам чуть ли не с
третьего класса.

Прогноз продаж на месяц

Обратимся всё к тому же графику. Здесь также ясна ещё одна
истина: если прямая тренда вдруг превратиться в горизонталь – это точка
безубыточности. Работать безрезультатно – только лишь, чтобы выйти «в нули», ни
один уважающий себя коммерсант не станет, только если есть ощутимая прибыль. Если
же эта линия станет опускаться – серьёзный сигнал к тому, что пора
реорганизовываться, либо закрывать фирму (или предприятие). Очень часто
причиной понижения тренда становится обилие конкурентов, сумевших реализовать
продажи быстрее и лучше, санкции со стороны регулирующих органов, общий обвал
фондовых рынков и несколько иных весомых причин.

Более достоверный прогноз на месяц можно получить, используя
данные за месяцы предыдущего года. Ваш опыт и история подскажут с большой
вероятностью, будет ли тренд компании идти вверх. Для развивающегося
предприятия, давно перешедшего границу стартапа, используется всё та же формула
y=ax+b, где Y – объём продаж,
X – порядковый номер очередного интервала, A – поднятие каждого последующего
значения в ряду времени, B – минимальная грань. Последовательность действий
будет следующей.

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

Далее рассчитывается отклонение значений
реальных продаж от значений тренда.

Сезонность подсчитывается как частное от
деления реального объёма продаж за этот же период на среднее значение объёма.

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

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

Прогноз продаж на квартал

Здесь можно воспользоваться реальной картиной по продажам за
предыдущие кварталы этого и прошлого годов.

Усложнять нелинейными расчётами свои прогнозы зачастую не
имеет смысла, если факторов, влияющих на вашу деятельность, не так много.
Широкую популярность линейные расчёты обрели лишь в годы индустриализации СССР:
целью следующей пятилетки было превысить показатели предыдущей на сколько-то
десятков процентов.

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

Примером расчёта продаж на квартал служит всё тот же
веломаркет – например, велосипедный отдел в «Спортмастере». Каким будет прогноз
на 2-й и 3-й квартал – в велосезон, когда тепло с апреля по май и в сентябре, и
жарко летом? Естественно, продажи велосипедов могут подскочить несколько раз –
с апреля, или в канун отпусков (в июле). Подскакивают продажи велосипедов и в конце
сентября – когда велосезон заканчивается, и веломаркеты объявляют о распродажах
моделей байков, выходящих из моды после истечения этого года, за, скажем, 70%
от цены.

Прогноз продаж на год

Для расчёта прогноза продаж на год лучший результат дают
предыдущие факты за прошедший период хотя бы в 2-3 года. С одного прошлого года
прогноз на весь грядущий год составить куда сложнее. Может потребоваться
криволинейная функция. Однако тренд всегда описывается строго и чётко – по закону
прямой.