План-факт анализ в Excel: формулы, пример и отклонения

Что должен показывать план-факт анализ

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

Сам по себе столбец «Факт − План» недостаточен. Например, +100 000 ₽ по выручке — хороший результат, а +100 000 ₽ по расходам — перерасход. Поэтому рабочая модель должна хранить тип показателя, пороги существенности и комментарий. Тогда таблица отвечает на вопрос «что делать дальше», а не просто фиксирует историю цифр.

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

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

Не делайте отдельный файл на каждый месяц или отдел. Гораздо устойчивее хранить месяц и подразделение отдельными колонками. Тогда один набор данных можно фильтровать, сводить по кварталам и годам и использовать для нескольких отчётов. Пример структуры базы: Дата | Месяц | Отдел | Статья | Тип | План | Факт | Ответственный. Каждая строка — одно сочетание периода, отдела и статьи.

Формулы абсолютного и процентного отклонения

Абсолютное отклонение считается как Факт − План. Если план находится в C2, а факт в D2, формула: =D2-C2. Процентное отклонение: =(D2-C2)/C2. Ячейку оформляют как процент. При нулевом плане нужна защита от деления на ноль: =ЕСЛИ(C2=0;"—";(D2-C2)/C2). В англоязычном Excel та же логика записывается через IF.

Если исходная база хранится построчно, итог по статье удобно получать через СУММЕСЛИМН. Например, сумма факта для статьи из A2 и месяца из B1: =СУММЕСЛИМН(Данные!$G:$G;Данные!$D:$D;$A2;Данные!$B:$B;B$1). Аналогично считается план из колонки F. Это надёжнее ручного сложения диапазонов: при добавлении новых операций формула продолжает работать.

Для итоговой строки процент нельзя считать как среднее процентов отдельных статей. Сначала сложите общий план и общий факт, затем примените =(ОбщийФакт-ОбщийПлан)/ОбщийПлан. Иначе статья на 10 000 ₽ будет влиять на итог так же сильно, как статья на 1 000 000 ₽.

Практический пример с числами

Предположим, план выручки на месяц — 1 200 000 ₽, факт — 1 320 000 ₽. Отклонение = 1 320 000 − 1 200 000 = +120 000 ₽, или +10%. Себестоимость: план 480 000 ₽, факт 510 000 ₽ — перерасход +30 000 ₽, или +6,25%. Маркетинг: план 200 000 ₽, факт 235 000 ₽ — перерасход +35 000 ₽, или +17,5%. Остальные расходы: план 250 000 ₽, факт 245 000 ₽ — экономия 5 000 ₽.

Плановая прибыль равна 1 200 000 − 480 000 − 200 000 − 250 000 = 270 000 ₽. Фактическая прибыль: 1 320 000 − 510 000 − 235 000 − 245 000 = 330 000 ₽. В итоге прибыль выше плана на 60 000 ₽, или 22,2%. Это хороший результат, но план-факт показывает важную деталь: рост выручки перекрыл перерасход маркетинга и себестоимости. Без разбора статей руководитель увидел бы только рост прибыли и мог пропустить ухудшение эффективности затрат.

Если валовая маржа нужна отдельным KPI, рассчитайте её как =(Выручка-Себестоимость)/Выручка. По плану она составляет 60%, по факту около 61,4%. Такой показатель помогает отличить рост прибыли за счёт масштаба продаж от реального улучшения экономики.

Как считать статус с учётом доходов и расходов

Для управленческого отчёта полезна колонка «Статус»: В норме, Требует внимания, Критично. Порог можно задать, например, 5% и 10%, но для разных статей он может отличаться. У аренды допустимое отклонение часто почти нулевое, а у маркетинга или продаж диапазон может быть шире.

Логика доходов и расходов противоположна. Для дохода факт ниже плана — негативное отклонение, для расхода факт выше плана — негативное. Поэтому не окрашивайте строки только по знаку «Факт − План». В простом варианте статус расхода можно считать так: =ЕСЛИ(И(B2="Расход";E2>10%);"Критично";ЕСЛИ(E2>5%;"Внимание";"В норме")), но в реальном файле лучше вынести пороги в справочник и подтягивать их через ПРОСМОТРX.

Полезно применять двойной порог: процент и сумму. Отклонение 30% на статье 5 000 ₽ может быть несущественным, а 4% на статье 5 млн ₽ — важным. Например, требовать комментарий, если абсолютное отклонение больше 100 000 ₽ ИЛИ процент превышает 10%.

Причины отклонений и обновлённый прогноз

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

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

Дашборд: что действительно стоит показывать

На первом экране достаточно 4–6 KPI: плановая и фактическая выручка, расходы, прибыль, выполнение плана и сумма существенных негативных отклонений. Ниже — график План/Факт по месяцам и таблица пяти крупнейших проблемных статей. Руководителю обычно важнее быстро увидеть исключения, чем просматривать десятки декоративных диаграмм.

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

Типичные ошибки план-факт отчёта

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

Ещё две частые проблемы — ручные итоги и разрозненные файлы. Если менеджеры копируют формулы каждый месяц, рано или поздно диапазон будет пропущен. Если каждый отдел ведёт собственную структуру, общий отчёт превращается в ручную сборку. Единая база, справочники и СУММЕСЛИМН или сводные таблицы снижают этот риск.

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

Как собрать план-факт под свою компанию

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

Если не хочется вручную проектировать листы, формулы и оформление, в SmartXLSX можно описать задачу обычными словами: указать период, статьи, отделы, показатели и пороги отклонений. Сервис покажет структуру и соберёт персональный XLSX, который затем редактируется как обычный Excel. Для более широкой финансовой модели полезно также посмотреть материалы про бюджет компании, P&L и Cash Flow.