Прогнозирование продаж: 4 метода + модель в Excel

«Сколько мы продадим в следующем квартале?» — вопрос, на который у половины собственников нет честного ответа. Есть план, который спустили сверху, есть надежда, что команда его вытянет. Проблема в том, что надежда — это не прогноз. План в начале года выполняется на 50–70%, под него уже наняли людей и закупили товар, а в кассе вдруг пусто — и непонятно, кого винить: рынок, отдел продаж или таблицу в Excel.

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

Внимание: По бенчмарку Xactly, больше половины revenue-лидеров промахивались с прогнозом минимум дважды за год.

Почему прогноз продаж «не работает» (и при чём тут юнит-экономика)

Типичная фраза собственника, с которой приходят за помощью, звучит так: «прогноз не работает». Обычно за ней стоит не сломанная формула, а кое-что попроще — в компании просто нечем прогнозировать. Нет данных, нет понимания, по какой цене приходит клиент и сколько в среднем он приносит. А без этих цифр любой прогноз превращается в гадание.

Чаще всего так живёт малый и средний бизнес. Люди годами делают выручку, но не считают юнит-экономику — не знают стоимость привлечения клиента, конверсию воронки, средний чек по сегментам. А раз нет понимания юнит-экономики, то нет и материала, из которого собирается прогноз. Поэтому работа начинается не с таблицы, а с азов: считаем экономику, разбираем, откуда берутся клиенты, и только потом учимся прогнозировать.

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

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

Масштаб проблемы подтверждают и цифры. По бенчмарку Xactly, около 79% команд промахиваются с прогнозом больше чем на 10%. То есть «промахнуться» — это норма, в которой живёт почти весь рынок. Вопрос не в том, как попасть точно, а в том, как сократить разброс до управляемого.

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

Прогноз, план и факт: в чём разница

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

План — это обязательство. «Мы должны сделать 50 млн за квартал». План спускается сверху, он мотивирует, под него строят систему мотивации и ставят цели отделу. План может быть амбициозным, выше реальных возможностей — в этом и есть его роль: тянуть команду вверх.

Прогноз — это честная оценка. «При текущей воронке, конверсии и бюджете мы реально сделаем 38–42 млн». Прогноз опирается на данные, а не на желания. Он не обязан совпадать с планом — и чаще всего не совпадает. Прогноз нужен не чтобы мотивировать, а чтобы планировать: найм, закупки, бюджет, кассу.

Факт — это что получилось на самом деле. И здесь два разрыва, которые важно читать по-разному:

• Разрыв «прогноз — факт» показывает качество вашей модели. Если прогноз стабильно мимо факта — проблема в данных или в методе.
• Разрыв «план — прогноз» показывает точки роста. Если прогноз честно говорит «сделаем 38, а план 50» — это сигнал, что отделу не хватает 12 млн, и надо искать, откуда их взять: больше лидов, выше конверсия, другой средний чек.

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

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

4 метода прогнозирования продаж

Методов много, и конкуренты любят выкатывать списки на пять-шесть пунктов с пугающими названиями вроде ARIMA и метода Дельфи. На практике собственнику малого и среднего бизнеса достаточно понимать четыре базовых подхода и знать, какой из них подходит под его тип бизнеса.

Экспертная оценка

Самый простой метод: прогноз строится на опыте людей, которые знают рынок. РОП, собственник, опытные менеджеры садятся и оценивают, сколько реально продадут в следующем периоде.

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

Статистические методы: тренд и сезонность

Здесь прогноз строится на истории продаж. Берём данные за прошлые периоды, выделяем тренд (растём мы или падаем) и сезонность (в декабре продаём больше, в январе меньше) и проецируем это на будущее.

Инструменты простые: скользящее среднее сглаживает случайные колебания, линейный тренд показывает направление, индексы сезонности корректируют прогноз по месяцам. Когда подходит: зрелый бизнес с историей минимум за 12–24 месяца и коротким циклом сделки — розница, e-commerce, общепит.

Прогноз по воронке (weighted pipeline)

Метод для B2B и длинного цикла сделки. Здесь прогноз — это декомпозиция через воронку продаж по простой формуле:

Выручка = Лиды × Конверсия × Средний чек

Вы берёте количество лидов, прогоняете их через конверсию каждого этапа и получаете ожидаемую выручку. В B2B сделки взвешивают по этапам: сделка на стадии «выставлен счёт» закрывается с вероятностью 70%, на стадии «первый контакт» — 10%. Сумма взвешенных сделок и есть прогноз по pipeline.

Совет: Формула «Выручка = Лиды × Конверсия × Средний чек» — это не просто способ посчитать прогноз. Это карта, которая показывает узкое горлышко. Если выручка не дотягивает, видно, что именно проседает: мало лидов, низкая конверсия или маленький чек. Прогноз и точки роста читаются из одной таблицы.

Каузальный метод: юнит-экономика и маркетинг

Метод для бизнеса, который живёт на лидогенерации и зависит от маркетинговой активности — онлайн-услуги, инфобизнес, агентства. Логика обратная воронке: вы отталкиваетесь не от количества лидов, а от бюджета.

Знаете цену клиента из маркетинга — допустим, привлечение одного клиента стоит 5 000 рублей. Планируете бюджет на месяц — например, 500 000 рублей. Делите бюджет на цену клиента, получаете 100 клиентов, умножаете на средний чек — вот ваш прогноз выручки. Чем точнее вы знаете стоимость привлечения, тем точнее прогноз.

Обратите внимание: разница между методами не в «крутизне формулы», а в том, какие данные есть у вашего бизнеса. И это подводит нас к главному.

Главный секрет: всё сводится к 2 моделям

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

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

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

И всё. По большому счёту других моделей прогнозирования нет. Статистический метод, прогноз по воронке, каузальный метод, любые «шесть способов» из статей конкурентов — это лишь разная степень усложнения этих двух базовых источников данных. Скользящее среднее и ARIMA — оба про статистику. Weighted pipeline и расчёт от бюджета — оба про экономику клиента.

К сведению: Это снимает паралич выбора. Не нужно мучительно решать, какой из шести методов «правильный». Нужно ответить на один вопрос: мой бизнес прогнозируется от статистики прошлого или от цены клиента и бюджета? Определили тип — выбрали модель. Остальное детали.

Мнение эксперта

Павел Котов

Эксперт по систематизации бизнеса

Задать вопрос

Я верю в продажи, которые базируются на исходных данных. Чтобы прогнозировать, нужны данные, и источников по сути два. Первый — статистика за прошлый период: опираемся на неё и корректируем, например, «в этом году маркетинг будет активнее — значит, и продажи будут выше». Второй — цена клиента: понимаем, по какой цене покупаем клиента через маркетинг, и от бюджета считаем, сколько клиентов получим. По большому счёту других моделей прогнозирования нет — всё остальное лишь разная степень усложнения этих двух.

Готовая модель прогноза в Excel (пошагово)

Теория без таблицы бесполезна, поэтому соберём рабочую модель прогноза в Excel. Хорошая новость: это не нейросеть и не сложная регрессия. Рабочая модель — это два-три листа и пять формул. Разберём по шагам.

Шаг 1. Лист «Данные»

Сюда складываем факты. Для статистической модели — история продаж по месяцам за последние 12–24 месяца: дата, выручка, при желании — количество сделок и средний чек. Для модели по воронке — данные из CRM: количество лидов, конверсия по этапам, средний чек.

Это фундамент. Если данные грязные — менеджеры не заполняют CRM, выручка собрана «по памяти» — дальше можно не продолжать. Прогноз будет ровно такого качества, как этот лист.

Шаг 2. Лист «Справочники»

Здесь живут коэффициенты, на которые вы будете опираться: сезонные индексы по месяцам (январь — 0,7, декабрь — 1,4), конверсии воронки, средний чек по сегментам, цена привлечения клиента, планируемый маркетинговый бюджет. Вынести их на отдельный лист удобно: поменяли одну цифру — пересчиталась вся модель.

Шаг 3. Расчёт

Здесь работают формулы. Для статистической модели Excel закрывает почти всё встроенными функциями:

• ТЕНДЕНЦИЯ — строит линейный тренд по истории и продлевает его на будущие периоды.
• ПРЕДСКАЗ.ETS — даёт прогноз с учётом сезонности (нужна история по датам).
• СРЗНАЧ по последним периодам — простое скользящее среднее для сглаживания.

Для модели от юнит-экономики формула ещё проще: Бюджет ÷ Цена клиента × Конверсия × Средний чек. Никаких специальных функций — обычная арифметика по ячейкам справочника.

Шаг 4. Переключатель сценария

Удобно сделать одну ячейку-переключатель: «розница (статистика)» или «лидген (юнит-экономика)». В зависимости от выбора модель считает прогноз либо от истории и сезонности, либо от бюджета и цены клиента. Реализуется через ЕСЛИ — одна формула, два сценария.

Шаг 5. Числовой пример

Возьмём условную компанию с выручкой 50 млн в год, которая живёт на лидогенерации. Считаем прогноз на месяц от юнит-экономики:

• Маркетинговый бюджет на месяц: 600 000 ₽
• Цена привлечения клиента: 6 000 ₽
• Значит, клиентов: 600 000 ÷ 6 000 = 100
• Конверсия из лида в сделку: 40% → сделок: 40
• Средний чек: 110 000 ₽
• Прогноз выручки: 40 × 110 000 = 4,4 млн ₽ за месяц

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

Совет: Не усложняйте. Рабочая модель прогноза — это 2–3 листа и 5 формул, а не нейросеть с машинным обучением. Сложность модели не повышает точность — её повышает чистота данных и регулярность сверки. Лучше простая таблица, которую вы реально ведёте каждую неделю, чем гениальная модель, которую открыли один раз и забыли.

Как рассчитать прогноз продаж: формулы и функции Excel

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

Формула прогноза продаж «в лоб»

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

Для воронки формула другая и точнее: прогноз = количество лидов × конверсия × средний чек. Она полезнее тем, что показывает не только результат, но и рычаг: видно, за счёт чего именно вы собираетесь вырасти.

Как посчитать конверсию в Excel

Базовая операция, без которой прогноз по воронке не построить. Если в столбце B количество сделок, а в столбце A — количество заявок, конверсия считается как =B2/A2 с процентным форматом ячейки. Для конверсии между этапами воронки — то же деление количества на следующем этапе на количество на предыдущем.

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

Функция ПРЕДСКАЗ: как работает

Встроенная функция линейного прогноза. Синтаксис: =ПРЕДСКАЗ.ЛИНЕЙН(x; известные_значения_y; известные_значения_x), в старых версиях — просто ПРЕДСКАЗ, в англоязычном интерфейсе — FORECAST.LINEAR.

Как это работает по сути: функция строит прямую линию по вашим прошлым данным и продолжает её в будущее. То есть x — это номер месяца, который вы прогнозируете, известные_значения_y — продажи прошлых месяцев, известные_значения_x — номера этих месяцев.

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

Лист прогноза: инструмент, который учитывает сезонность

Более честный способ спрогнозировать данные в Excel — встроенный «Лист прогноза». Порядок: выделяете два столбца (даты и значения) → вкладка «Данные» → «Лист прогноза» → задаёте дату окончания прогноза → «Создать».

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

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

Отдельно про ARIMA — модель, которую часто ищут применительно к Excel. В стандартном Excel её нет: «Лист прогноза» использует другое семейство алгоритмов. ARIMA доступна в надстройках и статистических пакетах, но для задач управления продажами в компании до нескольких сотен человек она избыточна: выигрыш в точности редко окупает сложность сопровождения.

Прогноз выполнения плана: формула в Excel

Самая нужная в ежедневной работе формула — не годовая, а внутримесячная: закроем ли план при текущем темпе. Считается как факт на сегодня ÷ прошедшие рабочие дни × всего рабочих дней в месяце.

В таблице это выглядит так: =B2/C2*D2, где B2 — факт с начала месяца, C2 — сколько рабочих дней прошло, D2 — сколько их в месяце всего. Процент выполнения плана — =B2/E2, где E2 — план на месяц. Количество рабочих дней между датами удобно получать функцией ЧИСТРАБДНИ.

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

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

Какая точность прогноза считается нормальной

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

Реалистичная норма для малого и среднего бизнеса — погрешность 10–25%. То есть если вы спрогнозировали 40 млн, а сделали 34 или 46 — это хороший, рабочий прогноз. По бенчмарку Fullcast, точности 90%+ достигают лишь 7% компаний — это единицы с выстроенной аналитикой, а не средний бизнес.

Ориентиры по уровню зрелости тоже известны. По данным MXM Revenue Insights, сильные команды держат точность в пределах ±5–10%, средние — ±15–25%. Если вы только начинаете прогнозировать, цельтесь в верхнюю границу и постепенно сужайте разброс.

Внимание: Если ваш прогноз стабильно промахивается больше чем на 25% — проблема не в формуле. Дело в воронке, в дисциплине заполнения CRM или в самих процессах продаж. Менять модель в этой ситуации бессмысленно: вы будете точнее считать неверные данные. Смотреть надо на источник данных, а не на таблицу.

Мнение эксперта о точности

Павел Котов

Эксперт по систематизации бизнеса

Задать вопрос

Прогнозирование продаж обычно даёт погрешность от 10 до 25 процентов — и это нормально. Не стоит ждать попадания «в точку»: только 7% компаний достигают точности 90%. Если ваш прогноз стабильно промахивается сильнее, чем на 25%, проблема не в формуле, а в воронке и дисциплине в CRM — туда и надо смотреть.

Прогноз — это процесс, а не Excel-файл

А теперь главное, чего нет ни у одного конкурента в топе. Устойчиво прогнозируют не те, у кого красивая таблица, а те, у кого выстроена система вокруг неё. Excel-файл — это инструмент. Процесс — это то, что делает его рабочим.

Из чего складывается процесс. Во-первых, данные собираются в CRM, а не «по памяти» — это единственный источник правды по лидам, сделкам и конверсиям. Во-вторых, у прогноза есть владелец: его делает РОП, а визирует финансовый директор. Это разделяет ответственность — РОП отвечает за реалистичность, финдиректор за то, чтобы под прогноз корректно спланировали деньги.

В-третьих, регулярность. Прогноза, как правило, достаточно на 3 месяца вперёд — но с уточнением каждый месяц. Это скользящий горизонт: каждый месяц вы сдвигаете окно и пересматриваете цифры на свежих данных. Раз в квартал «нарисовать и забыть» не работает — к концу квартала прогноз уже не имеет отношения к реальности.

Внимание: Регулярность важнее метода. Исследование Forecastio показало: команды, которые еженедельно отслеживают pipeline, дают точность 87% против 52% у тех, кто делает это нерегулярно. Простая модель с еженедельной сверкой бьёт сложную модель, которую открывают раз в квартал.

Как это выглядит на практике — покажу на кейсе. К Business Commandos обратилась ювелирная компания с оборотом 300 млн в год: премиальные украшения, сильная команда, продажи по всей России. Запрос был — «стабилизировать продажи», потому что прогнозировать их было невозможно. Копнули и нашли корень: CRM внедрили с ошибками, менеджеры ею не пользовались и вели клиентов каждый по-своему. Данные были грязные, а значит, прогнозировать было не на чем.

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

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

Что будет, если прогноз не сходится

Несбывшийся прогноз без резервов — это всегда небольшая трагедия для бизнеса. И самое частое её имя — кассовый разрыв.

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

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

И тут важный вывод. Если прогноз не сходится разово — это нормальная погрешность. Но если он не сходится из года в год, проблема не в модели. Дело в системе продаж: грязные данные в CRM, размытая ответственность, нет регулярной сверки. В этом случае новая таблица не поможет — нужен аудит системы продаж, чтобы понять, где именно рвётся: в воронке, в данных, в мотивации РОПа или в контроле. С этого и начинается диагностика бизнеса — сначала находим разрыв, потом чиним.

Частые ошибки прогнозирования продаж

Большинство провалов с прогнозом — это одни и те же грабли. Собрал шесть самых частых.

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

Частая ошибка: Прогнозировать на грязных данных. Если CRM заполняется кое-как и менеджеры ведут сделки «по-своему», любая модель будет точно считать неверные цифры. Сначала дисциплина в CRM, потом прогноз.

Частая ошибка: Ждать попадания «в точку». Норма — погрешность 10–25%. Требовать от прогноза 100% точности — значит обесценить рабочий инструмент из-за нереалистичных ожиданий.

Частая ошибка: Пересчитывать раз в квартал. Прогноз без ежемесячного уточнения устаревает к середине периода. Скользящий горизонт 3 месяца с пересмотром каждый месяц — рабочий стандарт.

Частая ошибка: Применять метод не под свой бизнес. Натягивать статистическую модель на стартап без истории или прогнозировать B2B-продажи «от бюджета маркетинга» — значит получить красивую, но бессмысленную цифру.

Частая ошибка: Считать Excel решением. Таблица — это инструмент, а не система. Без CRM, владельца прогноза и регулярной сверки даже идеальная модель не работает.

Часто задаваемые вопросы

Выводы

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

Не ждите попадания «в точку»: норма — погрешность 10–25%. Сделайте прогноз процессом, а не файлом: данные в CRM, владелец в лице РОПа с визой финдиректора, скользящий горизонт на 3 месяца с ежемесячным пересмотром. Тогда прогноз перестанет быть гаданием и станет опорой для найма, закупок и бюджета.