План продаж в Excel: от цели до прогноза выполнения

1. Что должен показывать рабочий план продаж

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

Начинайте с управляемых драйверов. Для отдела продаж выручку можно разложить на количество оплаченных заказов и средний чек; для B2B — на число сделок и среднюю сумму договора; для подписки — на активных клиентов и средний доход на клиента. Если план 5 000 000 ₽ получен просто как прошлый месяц плюс 20%, команда видит цель, но не понимает, сколько конкретно действий и сделок требуется.

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

2. Рекомендуемая структура Excel-файла

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

На листе «Факт» придерживайтесь правила: одна строка — одна операция или одна товарная позиция заказа. Не делайте отдельные листы «Январь», «Февраль», «Март». Единый журнал проще фильтровать, проверять и агрегировать через СУММЕСЛИМН. Исходный диапазон лучше превратить в таблицу Excel через Ctrl+T, чтобы новые строки автоматически включались в расчёты.

Минимальные колонки итогового листа: Период, Менеджер, План, Факт, Выполнение %, Отклонение ₽, Прошло рабочих дней, Всего рабочих дней, Прогноз, Прогноз выполнения %, Требуется до плана и Требуемый темп в день. Этого достаточно, чтобы отчёт был управленческим, а не декоративным.

3. Как декомпозировать план до сделок и среднего чека

Допустим, месячная цель отдела — 5 000 000 ₽, работают пять менеджеров. Простое деление даст по 1 000 000 ₽ на человека, но оно корректно только при сопоставимых территориях, опыте и потоке лидов. Если у менеджеров разные портфели, распределяйте план по потенциалу: например 25%, 22%, 20%, 18% и 15%. Контроль суммы долей: =СУММ(B2:B6), результат должен быть 100%.

План менеджера при общей цели в $B$1 и его доле в B2: =$B$1*B2. Если доля 20%, план равен 1 000 000 ₽. Затем разложите его на сделки. При среднем чеке 50 000 ₽ нужно =1000000/50000, то есть 20 оплаченных сделок. Если ожидаемая конверсия из квалифицированного лида в оплату 25%, потребуется 20/25%=80 квалифицированных лидов.

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

4. Реальные формулы план-факт в Excel

Если план менеджера находится в C2, а факт в D2, выполнение считается формулой =ЕСЛИОШИБКА(D2/C2;0). При плане 1 000 000 ₽ и факте 760 000 ₽ получится 76%. Денежное отклонение: =D2-C2, то есть −240 000 ₽. Сколько осталось сделать до цели: =МАКС(0;C2-D2). Эти показатели лучше показывать рядом, потому что процент без суммы скрывает реальный масштаб разрыва.

Факт не нужно вводить вручную в итоговую таблицу. Если на листе «Факт» столбец A содержит даты, B — менеджера, F — выручку, то выручку выбранного сотрудника за период можно получить через =СУММЕСЛИМН(Факт!$F:$F;Факт!$B:$B;$A2;Факт!$A:$A;">="&$B$1;Факт!$A:$A;"<="&$C$1). B1 и C1 — начало и конец отчётного периода.

Итоговый процент отдела считайте как общий факт / общий план: =ЕСЛИОШИБКА(СУММ(D2:D6)/СУММ(C2:C6);0). Не используйте СРЗНАЧ процентов пяти менеджеров, если их планы различаются: сотрудник с маленьким планом получит такой же вес, как сотрудник с большой целью, и итог будет искажён.

5. Прогноз до конца месяца и требуемый темп

План-факт показывает положение сегодня, а прогноз помогает действовать до закрытия месяца. Для простой линейной оценки используйте фактическую выручку и долю прошедших рабочих дней. Если D2 — факт, E2 — прошедшие рабочие дни, F2 — всего рабочих дней, прогноз: =ЕСЛИОШИБКА(D2/E2*F2;0). При 760 000 ₽ за 12 рабочих дней из 22 прогноз составит примерно 1 393 333 ₽.

Линейный прогноз нельзя воспринимать как обещание. Он работает, когда продажи распределены относительно равномерно. Если большая часть B2B-сделок закрывается в конце месяца, лучше дополнить его взвешенной воронкой: сумма сделки × вероятность закрытия. Сравнение линейного прогноза и прогноза по воронке даёт руководителю более полезный диапазон.

Для управления важен требуемый темп. Если до плана осталось 240 000 ₽ и осталось 10 рабочих дней, нужно в среднем 24 000 ₽ в день: =МАКС(0;C2-D2)/МАКС(1;F2-E2). Если исторический темп менеджера 15 000 ₽ в день, красный статус должен появиться до последней недели, а не после провала месяца.

6. Практический пример: отдел из пяти менеджеров

Компания поставила месячный план 5 000 000 ₽. Цели менеджеров: Анна — 1 250 000 ₽, Борис — 1 100 000 ₽, Виктор — 1 000 000 ₽, Галина — 900 000 ₽, Денис — 750 000 ₽. После 12 из 22 рабочих дней факт составил соответственно 760 000, 650 000, 620 000, 470 000 и 390 000 ₽. Общий факт — 2 890 000 ₽.

Выполнение отдела: =2890000/5000000 = 57,8%. По календарю прошло 12/22 = 54,5% рабочих дней, поэтому команда пока идёт немного выше равномерного темпа. Линейный прогноз: =2890000/12*22 ≈ 5 298 333 ₽, или около 106% плана. Но на уровне сотрудников ситуация неодинакова: Галина выполнила 52,2%, а Анна — 60,8%.

До общей цели осталось 2 110 000 ₽ и 10 рабочих дней. Требуемый темп отдела — 211 000 ₽ в день. Если средний чек равен 50 000 ₽, нужно ещё примерно 43 сделки. При конверсии 25% это около 172 квалифицированных возможностей. Такой расчёт позволяет сразу проверить воронку: если в ней осталось только 90 подходящих лидов, линейный прогноз выглядит оптимистично и требуется управленческое действие.

7. Дашборд: что показывать руководителю

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

Условное форматирование используйте для исключений. Например: прогноз выполнения ниже 90% — критический риск, 90–99,9% — внимание, 100% и выше — цель достижима при текущем темпе. Порог лучше хранить на листе настроек, а не зашивать в каждое правило.

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

8. Типичные ошибки и контрольные проверки

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

Добавьте контрольные ячейки: сумма индивидуальных планов равна плану отдела; сумма долей распределения равна 100%; даты факта попадают в выбранный период; нет неизвестных менеджеров; отменённые сделки исключены; прогноз не делит на ноль в первый день месяца. Для проверки дублей используйте номер заказа и статус операции.

Перед регулярным использованием вручную пересчитайте одного менеджера за 2–3 дня и сравните с итоговым листом. После добавления новой строки в «Факт» она должна автоматически изменить факт, выполнение и прогноз. Если для этого приходится протягивать диапазоны вручную, структуру лучше исправить до масштабирования.

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

План продаж становится сильнее в связке с план-факт анализом и P&L. Продажи отвечают за объём и коммерческие драйверы, P&L показывает, превращается ли выручка в прибыль, а платёжный календарь — когда деньги реально поступят. Не пытайтесь заменить все три задачи одной гигантской книгой, если отчётом пользуются разные сотрудники.

Если нужна исходная структура без ручной сборки, используйте инструмент SmartXLSX «План продаж»: опишите менеджеров, период, показатели и нужные разрезы обычными словами. Для более общего анализа отклонений перейдите к руководству по план-факт анализу, а для оценки прибыльности — к статье о P&L. Внутренние связи должны идти через понятные поля: период, менеджер, канал, выручка и валовая прибыль.