Чек-лист: выбираем подходящий метод прогнозирования
Зачем держать руку на пульсе или почему бизнес не может работать вслепую
Учёт и прогнозирование продаж дают прекрасную возможность понять, сколько товара закупать, когда запускать рекламу и стоит ли нанимать дополнительных сотрудников. Компания, которая видит тренды заранее, использует возможности по максимуму без переполненных складов или пустых полок.
Допустим, вы продаёте спортивное питание. Весной спрос растёт — люди готовятся к пляжному сезону. Зимой интерес падает, продажи соответственно тоже. Если вы заранее знаете эти колебания, то закупите оптимальное количество товара и не потратите деньги впустую. И всё это благодаря тому, что вы разобрались в исторических данных и поняли, как их использовать.
Ещё один плюс: вы сможете понять, какие каналы приносят деньги. Реклама в соцсетях работает лучше контекста? Отлично, перераспределите бюджет. Один продукт продаётся в три раза активнее другого? Значит, пора расширять ассортимент. Правда, это только базовые возможности — Excel умеет гораздо больше, если знать правильные инструменты и формулы.
С правильными навыками вы превращаете данные в конкретные действия и прибыль
На курсе «Excel и Google Таблицы» за 2 недели научитесь прогнозировать продажи, строить интерактивные дашборды и экономить до 10 часов в неделю на повторяющихся задачах. В подарок получите 40 шаблонов и шпаргалок для ускорения ежедневной работы.
После обучения на курсе «Руководитель отдела продаж» сможете масштабировать бизнес без потери качества и увеличивать выручку даже при ограниченном бюджете. Среди спикеров — преподаватели Гарвардской школы бизнеса и университета Джорджа Вашингтона.
На курсе «ИИ-сотрудники и нейросети для отдела продаж» освоите практическое применение ИИ, чтобы увеличить производительность отдела на 30–40% без найма дополнительных сотрудников. Бонусом заберёте ИИ-гайд: он подсветит, какие задачи команды можно автоматизировать сразу.
Выберите курс, который решит вашу задачу прямо сейчас, и оставьте заявку, чтобы начать обучение уже в ближайшее время
Как заглянуть в будущее и не ошибиться
Существует несколько способов спрогнозировать, сколько вы продадите в следующем месяце или квартале. Разберём основные модели прогнозирования продаж.
Субъективные методы — или когда интуиция встречается с опытом
Эти подходы базируются на мнениях и ощущениях. Звучит не очень научно, но иногда работает лучше любых формул. При этом для серьёзного анализа их лучше комбинировать с объективными данными.
Ожидания пользователей
Спросите напрямую у клиентов, что они планируют купить. Проведите опрос, разошлите анкету, соберите отзывы. Этот метод хорош для B2B-сегмента, где клиенты заранее планируют закупки. Например, производитель мебели может узнать у своих поставщиков, сколько товара им понадобится в ближайшие месяцы.
Минус очевиден: люди часто обещают одно, а делают другое. Планировали купить новый диван, а в итоге потратили деньги на отпуск. Ну, бывает :)
Мнение продавцов
Ваши менеджеры по продажам общаются с клиентами каждый день. Они чувствуют настроение рынка, знают, какие вопросы задают покупатели, какие товары вызывают интерес. Соберите их мнения — получите довольно точную картину.
Правда, тут есть подводный камень. Продавцы могут быть слишком оптимистичными (особенно если их премия зависит от планов) или, наоборот, занижать прогнозы, чтобы потом легко выполнить показатели.
#INNER3#
Мнение менеджеров компании
Руководители подразделений видят картину шире. Они знают о маркетинговых кампаниях, запуске новых продуктов, изменениях в конкурентной среде. Их прогнозы основаны на стратегическом понимании бизнеса.
Но топ-менеджеры могут быть оторваны от реальности. Они видят цифры в отчётах, а не живых людей в магазине.
Метод экспертных оценок
Попросите нескольких независимых специалистов оценить будущие продажи. Зафиксируйте их оценку, усредните результаты или обсудите расхождения. Такой метод называется Дельфи — он часто даёт неплохие результаты, особенно если комбинировать его со статистическими моделями.
Объективные методы — или пусть цифры говорят сами за себя
Глобально модели прогнозирования продаж имеют в основе исторические данные и статистические закономерности. Это фундамент, на котором можно выстроить сложные аналитические системы.
Рыночное тестирование
Запустите пробную продажу в небольшом сегменте рынка. Например, начните продавать новый вкус йогурта только в одном регионе. Посмотрите на результаты и масштабируйте на всю страну. Такой метод — частая история в FMCG-секторе.
В чём плюс: вы получаете реальные данные, а не догадки. Минус: на это нужны и время, и деньги.
Анализ временных рядов
Возьмите статистику продаж за прошлые периоды и найдите закономерности. Может быть, каждый месяц продажи растут на 5%? Или весной всегда происходит скачок? Используя эти паттерны, вы построите линейный прогноз или более сложную модель.
Классика: магазин мороженого продаёт больше летом и меньше зимой. Если вы знаете выручку за последние три года, то легко сможете предсказать следующий сезон. Это базовый уровень анализа — дальше можно подключать экспоненциальное сглаживание и другие продвинутые техники.
Анализ годовых графиков
Здесь важен коэффициент сезонности в продажах. Вы смотрите, как менялись продажи в течение года, и выявляете сезонные колебания. Потом применяете эти коэффициенты к базовому тренду.
Допустим, в июле продажи стабильно растут на 30%, в январе же падают на 20%. Зная это, вы скорректируете свои планы и запасы товара.
Дополнительные методы — или что делать тем, кто хочет копнуть глубже
Каузальный метод
Этот подход учитывает факторы, которые прямо влияют на продажи: цены, рекламные акции, действия конкурентов, экономическую ситуацию. Вы строите регрессионную модель и смотрите, как каждый фактор связан с количеством покупок.
Например, вы заметили, что когда конкурент повышает цены на 10%, ваша выручка растёт на 7%. Или что после каждой рекламной кампании в соцсетях продажи подскакивают на 15% в течение недели. Используя эти связи, вы создаёте более точный прогноз.
Сколько продали? Почему продали? Почему нет?
На курсе «Руководитель отдела продаж» вы научитесь выстраивать аналитику, в которой увидите ответы на все вопросы. Сможете анализировать эффективность каналов, рассчитывать unit-экономику и принимать решения на основе реальных цифр.
Оставьте заявку на обучение и разберитесь уже к следующему кварталу
Как сделать прогноз продаж в Excel
Давайте пошагово разберём, как построить прогноз в Excel. Это базовая инструкция, после которой вы сможете усложнять модели, добавлять автоматизацию через макросы и интегрировать внешние данные.
Подготовьте данные
Вам понадобятся: даты продаж, объёмы продаж в штуках, выручка в рублях, количество проданных товаров по категориям. Желательно иметь информацию минимум за 12 месяцев, а лучше за 2–3 года — чем больше будет история, тем точнее прогноз.
Учтите эти технические моменты ↓
Создавайте таблицу с чёткой структурой. Первый столбец — дата (в формате ДД.ММ.ГГГГ), второй — объём продаж, третий — выручка. Никаких пустых строк, никаких объединённых ячеек. Excel любит порядок.
Обратите внимание на формат чисел. В некоторых русских ОС в качестве разделителя дроби по умолчанию используется запятая (1500,50), иногда же Excel сразу ставит точку (1500.50). Проверьте настройки: Файл → Параметры → Дополнительно → Использовать системные разделители. Если формулы выдают ошибку, возможно, дело именно в ОС.
Удалите выбросы и аномалии. Был день, когда интернет-магазин лежал и продаж не было? Или наоборот, провели мощную акцию — и продажи взлетели в десять раз? Такие точки исказят прогноз.
Какие есть варианты:
- убрать такой день из данных, если он совсем выбивается;
- заменить средним или медианным значением за обычные дни;
- отметить этот период как акционный и считать такие показатели отдельно;
- сгладить сильные скачки, чтобы они меньше влияли на общий расчёт (использовать винсоризацию).
Проверьте даты — они должны идти последовательно. Пропущенная неделя или запись-дубль точно сломают расчёты. Убедитесь, что им ничего не мешает.
Постройте линейный прогноз
Это самая простая модель, она отлично работает для стабильных рынков.
Шаг 1. Подготовьте таблицу
Создайте два столбца: в первом укажите порядковый номер периода (1, 2, 3... для каждого месяца), во втором — фактические продажи за эти периоды.
Почему нужны порядковые номера, если Excel и так нумеруют каждую строчку? Дело в том, что функции ПРЕДСКАЗ и ТЕНДЕНЦИЯ требуют, чтобы у каждого значения продаж была своя числовая координата во времени. Поэтому мы создаём отдельный столбец «Период» (1, 2, 3…), и Excel использует именно его, чтобы построить линию тренда и предсказать следующий период.
Шаг 2. Используйте функцию ПРЕДСКАЗ
Формула выглядит так: =ПРЕДСКАЗ(x; известные_значения_y; известные_значения_x)
Где:
- x — номер периода, для которого строите прогноз (например 13 для тринадцатого месяца);
- известные_значения_y — диапазон фактических продаж (возьмём B2:B13);
- известные_значения_x — диапазон номеров периодов (пусть будут A2:A13).
Пример: =ПРЕДСКАЗ(13; B2:B13; A2:A13)
Такая формула позволит рассчитать прогнозное значение продаж для тринадцатого месяца на основе тренда предыдущих двенадцати.
Кстати: в новых версиях Excel лучше использовать ПРЕДСКАЗ.LINEAR для линейного прогнозирования и ПРЕДСКАЗ.ETS для сезонных или нелинейных рядов — так вы получите более гибкие и устойчивые прогнозы.
Шаг 3. Постройте график
Выделите данные — и фактические, и прогнозные, — перейдите во вкладку Вставка → Диаграммы → График. Выберите график с маркерами. На графике фактические данные обычно отображаются одним цветом, прогнозные — другим. Так вы сразу видите, где заканчивается история и начинается будущее.
Бонус: Excel также предлагает встроенную функцию ТЕНДЕНЦИЯ. Она тоже строит прогноз по имеющимся данным, но позволяет сразу получить значения на несколько будущих периодов. Это удобнее и быстрее, если нужно сразу посмотреть продолжение тренда.
#INNER2#
Рассчитайте сезонность
Многие бизнесы зависят от сезона: мороженое летом, обогреватели зимой, цветы к 8 Марта. Коэффициент сезонности покажет, насколько сильно меняется спрос в разные месяцы.
Шаг 1. Посчитайте средние продажи
Найдите среднее арифметическое продаж за весь период. Формула: =СРЗНАЧ(B2:B13), где B2:B13 — ваши данные по продажам.
Шаг 2. Вычислите коэффициент сезонности для каждого месяца
Разделите фактические продажи каждого месяца на среднее значение. Формула: =B2/$B$14, где B2 — продажи конкретного месяца, $B$14 — средние продажи (знак $ фиксирует ячейку при копировании формулы).
Если коэффициент больше 1, значит, в этом месяце продажи выше среднего. Если меньше 1 — ниже. Например, коэффициент 1,3 означает, что продажи на 30% выше обычного.
Шаг 3. Примените коэффициенты к прогнозу
Возьмите линейный прогноз, который построили ранее, и умножьте его на соответствующий коэффициент сезонности. Формула: =B14*B16, где B14 — базовый прогноз, B16 — коэффициент сезонности для этого месяца.
Теперь ваш прогноз учитывает не только общий тренд, но и сезонные колебания. Это уже более реалистичная картина. А если добавить анализ трендов за несколько лет, учёт праздников и промоакций — точность вырастет ещё сильнее.
Готовый шаблон таблицы для прогнозирования продаж
Лист 1: Данные продаж
- Столбец A: Дата (формат ДД.ММ.ГГГГ)
- Столбец B: Номер заказа
- Столбец C: Клиент (выпадающий список из справочника)
- Столбец D: Товар (выпадающий список из справочника)
- Столбец E: Количество
- Столбец F: Цена за единицу
- Столбец G: Сумма (формула: =E*F)
- Столбец H: Канал продажи (выпадающий список)
- Столбец I: Менеджер (выпадающий список)
- Столбец J: Статус оплаты
Лист 2: Справочник товаров
- Название товара | Артикул | Категория | Цена
Лист 3: Справочник клиентов
- Название | Контакт | Сегмент | Город
Лист 4: Сводная по месяцам
- Сводная таблица с группировкой по месяцам
- Показатели: Выручка, Количество заказов, Средний чек
Лист 5: Прогноз
- Столбец A: Период (порядковый номер)
- Столбец B: Дата
- Столбец C: Фактические продажи
- Столбец D: Базовый прогноз (формула ПРЕДСКАЗ)
- Столбец E: Коэффициент сезонности
- Столбец F: Прогноз с учётом сезонности
- График: фактические данные + прогноз
Интерпретируйте результаты
Построили прогноз — отлично. Но как понять, что он адекватный?
Посмотрите на график. Линия прогноза должна логично продолжать тренд фактических данных. Если фактические продажи плавно росли, а прогноз вдруг показывает резкий скачок или падение — что-то пошло не так.
Проверьте отклонения. Сравните прогнозные значения с фактическими за последние месяцы, если они у вас есть. Вычислите процент ошибки: =(Факт - Прогноз)/Факт*100. Если значение больше 30% — доработайте модель.
Обратите внимание на сезонность. Если ваш бизнес сезонный, а прогноз показывает ровную линию — вы забыли про коэффициенты. Или применили их неправильно.
Сравните несколько моделей. Постройте и линейный прогноз, и прогноз с учётом сезонности. Посмотрите, какой больше соответствует реальности. Иногда простая модель работает лучше сложной.
Здравый смысл — ваш главный инструмент. Прогноз говорит, что в следующем месяце вы продадите в три раза больше обычного? Спросите себя: есть ли для этого причины? Может, запланирована мощная рекламная кампания? Или это просто артефакт расчётов?
Что ещё можно сделать? Проверить прогноз на будущих данных.
Для этого используйте простые методы проверки:
- Holdout (разделение данных). Отделите последние 1–3 месяца данных и не используйте их в построении модели. Сначала постройте прогноз только на старой части данных, а потом сравните результат с отложенными месяцами. Если модель попадает близко — прогноз надёжный. Если нет — нужно пересмотреть подход.
- Backtesting (проверка по скользящему окну). Представьте, что вы перематываете время назад. На каждом шаге берите данные до какой-то даты, стройте прогноз вперёд и сравнивайте его с реальными продажами, которые были после. Так можно увидеть, насколько стабильно модель работает на разных периодах.
- Сравните несколько моделей между собой. Например, простое среднее, линейный тренд, ETS.
И выбирайте ту модель, которая показывает лучшую точность на тестовых периодах, а не только на истории. Валидация помогает избежать ситуации, когда модель прекрасно угадывает прошлое, но резко ошибается в будущем. Это особенно важно, когда вы планируете закупки, бюджеты и акции.
Чтобы прогнозы стали ещё точнее, пройдите курс «Excel и Google Таблицы: от новичка до эксперта». Вы научитесь использовать продвинутые функции и работать с массивами и Power Pivot. Сможете строить модели, которые автоматически подтягивают данные из внешних источников и обновляют прогнозы в режиме реального времени.
Доступ к материалам и обновлениям — бессрочный, вы всегда сможете вернуться к обучению.
Зарегистрируйтесь в программе и получите бонусный урок по нейросетям
Плюсы и минусы учёта продаж с помощью Excel
Плюсы:
- доступность — Excel есть почти у всех;
- гибкость — можно настраивать как угодно;
- простота — не нужно долго учиться.
Для маленького бизнеса зачастую этого уже достаточно.
Минусы:
- ручной ввод — привет, ошибки и потерянное время;
- отсутствие автоматизации — ну, этому можно научиться отдельно, но нужно время;
- сложность масштабирования — когда продаж много, Excel начинает тормозить;
- несколько человек не могут одновременно комфортно работать с одним файлом — но это если вы ведёте записи не онлайн.
Excel — это фундамент и ни в коем случае не потолок. Можно использовать надстройки вроде Power Query для автоматической загрузки данных, Python для продвинутой аналитики, макросы на VBA, чтобы рутинные задачи выполнялись сами. Так вы точно выжмете из инструмента гораздо больше.
Если Excel уже не справляется
Сервисы бизнес-аналитики
Power BI, Tableau, Yandex DataLens собирают данные из разных источников (сайт, соцсети, офлайн-касса), автоматически строят отчёты и дашборды. Вы видите актуальную картину в режиме реального времени.
Например, у вас сеть кофеен в пяти городах. Каждая точка отправляет данные в единую систему. Вы открываете дашборд и сразу видите, где продажи растут, где падают, какие напитки популярны в каждом городе. Это следующий уровень после Excel — аналитика глубже, данные обрабатываются быстрее, и можно работать даже с очень большими объёмами информации.
#INNER2#
CRM-системы
«Битрикс24» и amoCRM фиксируют продажи, в них же можно управлять клиентской базой, автоматизировать воронку продаж и напоминать менеджерам о звонках.
#INNER1#
В них можно держать информацию о клиенте в одном месте: всю историю его покупок, все переписки, назначенные задачи. Многие CRM уже встраивают искусственный интеллект для предиктивной аналитики и автоматического скоринга лидов.
Выберите своё обучение на ближайшие недели
— увеличить продуктивность команды;
— выстроить отдел продаж с нуля.
Нужно всё сразу? Оставляйте заявку на любой курс, а когда менеджер перезвонит — опишите, какой у вас запрос.
FAQ
Как часто нужно обновлять прогноз?
Зависит от динамики вашего бизнеса. Интернет-магазин с ежедневными продажами может пересматривать прогноз раз в неделю или месяц. B2B-компания с длинным циклом сделки — раз в квартал. Главное правило: обновляйте прогноз, когда появляются новые данные или меняются внешние условия. А если настроите автоматическое обновление данных через Power Query, Python или API, то прогноз будет актуализироваться сам.
Можно ли использовать Google Таблицы вместо Excel (Эксель)?
Да, потому что функциональность там почти такая же. К тому же Google Таблицы удобнее для командной работы: редактировать документ могут сразу несколько человек, а все изменения сохраняются автоматически. Плюс есть свои уникальные функции для работы с внешними данными и интеграции с другими сервисами Google.
Что делать, если прогноз сильно отличается от реальности?
Прогноз — это не истина в последней инстанции, а всего лишь ориентир. Проанализируйте причины расхождения: изменилась ли рыночная ситуация? Конкурент запустил масштабную акцию? Или вы просто неправильно оценили сезонность?
Используйте эту информацию для корректировки модели. Со временем ваши прогнозы станут точнее. Захотите погрузиться — попробуйте ARIMA, экспоненциальное сглаживание и ИИ, они будут учитывать больше факторов и дадут более стабильные результаты.
