План продаж в Excel: как построить план-факт, посчитать отклонения и спрогнозировать месяц

От /
Данные и таблицы

План продаж в Excel: как построить план-факт, посчитать отклонения и спрогнозировать месяц

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

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

Содержание

Зачем вести план продаж и план-факт в таблице

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

Что даёт таблица план-факт

  • Раннее обнаружение проблем. Если в середине месяца выполнение плана держится на 30%, это видно сразу, а не в конце месяца, когда исправить уже нечего.
  • Однозначность вместо споров. Одна таблица с фактом из выгрузки снимает расхождения «по моим данным мы сделали больше».
  • Основа для прогноза. Зная факт на сегодня и темп, можно прикинуть результат месяца и заранее решить, где добавить усилий.
  • Материал для решений по продукту и клиентам. Отклонения по менеджерам, товарам и сегментам показывают, что тянет план вниз.

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

Структура таблицы: какие листы и колонки нужны

Надёжнее разделять данные по слоям, а не вести одну простыню «всё в куче». Три листа закрывают почти все сценарии: план, факт и сводная аналитика.

Три листа таблицы

  • Лист «План». Цели на месяц по менеджерам и, при необходимости, по товарам или направлениям: плановая выручка, количество сделок, средний чек. По строкам — менеджер или продукт, по столбцам — показатели.
  • Лист «Факт». Реальные продажи: дата, менеджер, клиент, продукт, сумма. Это регистр операций — каждая строка одна продажа. Отсюда всё считается формулами, а не руками.
  • Лист «План-факт» (свод). Сравнение по каждому менеджеру: план, факт, отклонение, процент выполнения. Здесь же — итоги по отделу.

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

Формулы: отклонение и процент выполнения плана

Сводный лист строится на трёх базовых вычислениях. Они простые, но именно их отсутствие превращает план-факт в бесполезный список цифр.

Базовые расчёты

  • Процент выполнения плана = факт ÷ план × 100%. Показывает долю цели, которую уже закрыли.
  • Отклонение в деньгах = факт − план. Положительное значение — перевыполнение, отрицательное — отставание.
  • Отклонение в процентах = (факт − план) ÷ план × 100%. Удобно для сравнения менеджеров с разными планами: «−12%» у одного и «+8%» у другого читаются одинаково наглядно.

Для свода факта по менеджерам используйте функцию СУММЕСЛИМН — она суммирует выручку по условию «менеджер равен нужному». Плановые значения подтягиваются с листа «План» через ВПР по фамилии менеджера. Условное форматирование закрашивает строки: зелёное — план выполнен, красное — отставание. Так отклонения видны без чтения каждой цифры.

Прогноз до конца месяца: простая формула

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

Пример расчёта

  • План на месяц — 900 000 ₽, в месяце 30 дней.
  • К 10-му дню факт — 240 000 ₽.
  • Средний дневной темп: 240 000 ÷ 10 = 24 000 ₽.
  • Прогноз на месяц: 24 000 × 30 = 720 000 ₽ — это 80% плана, отставание видно за 20 дней до конца.

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

Правила ведения, чтобы план-факт работал

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

Что соблюдать

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

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

FAQ: частые вопросы

Чем план-факт отличается от обычной таблицы продаж?

Обычная таблица продаж фиксирует только факт — что и на сколько продано. План-факт добавляет цель и сравнение: по каждому менеджеру или продукту видно план, факт, отклонение и процент выполнения. Именно сравнение делает таблицу инструментом управления, а не журналом.

Как посчитать процент выполнения плана?

Разделите факт на план и умножьте на 100: факт ÷ план × 100%. Например, при плане 500 000 ₽ и факте 420 000 ₽ выполнение составит 84%. Отрицательные значения не возникают при корректно заданном положительном плане.

Что делать, если факт сильно отклонился от плана?

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

Как спрогнозировать выполнение плана до конца месяца?

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

Можно ли вести план-факт в Google Таблицах вместо Excel?

Да. Для план-факта достаточно функций СУММЕСЛИМН, ВПР и условного форматирования, которые есть и в Google Таблицах. Преимущество — одновременный доступ нескольких сотрудников; недостаток — нужно следить за правами доступа и единой версией структуры.

Коротко: как построить план продаж и план-факт в Excel

  • Разделите данные на три листа — план, факт (строки продаж) и сводный план-факт.
  • Считайте три показателя — процент выполнения (факт ÷ план × 100%), отклонение в деньгах и в процентах.
  • Сводите факт формулами — СУММЕСЛИМН по менеджеру, ВПР для подтягивания плана, условное форматирование для наглядности.
  • Стройте прогноз — (факт ÷ прошедшие дни) × число дней в месяце, с поправкой на сезонность.
  • Соблюдайте регламент — ежедневное обновление, разбор отклонений, план не менять на ходу, ответственный за каждый показатель.

План-факт в Excel не заменяет CRM, но закрывает самую важную управленческую задачу: вовремя показать, где продажи отклоняются от цели, и дать руководителю повод действовать, а не ждать конца месяца.

Материалы по теме

Прокрутить вверх