Построение системы план-факт анализа в Excel

План-факт анализ в Excel создается путем формирования таблицы с колонками «План», «Факт» и расчетными столбцами для отклонений. Ключевые формулы: абсолютное отклонение =Факт-План, процент выполнения =Факт/План и процент отклонения =(Факт-План)/План. Для защиты от ошибок деления на ноль используйте конструкцию ЕСЛИ(План=0; 0; ...).

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

Структура идеальной таблицы план-факт

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

1. Наименование статьи/задачи (Столбец A) — текстовое описание позиции.
2. План (Столбец B) — утвержденное бюджетное или временное значение.
3. Факт (Столбец C) — реальное значение по итогам периода.
4. Абсолютное отклонение (Столбец D) — разница в цифрах.
5. Отклонение % (Столбец E) — эффективность выполнения в процентах.
6. Комментарий (Столбец F) — причина перерасхода или экономии.

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

Формулы для расчета отклонений и эффективности

Расчет показателей базируется на трех основных метриках. Вводите эти формулы во вторую строку данных (например, строка 2), а затем протягивайте их вниз.

1. Абсолютное отклонение

Показывает разницу в деньгах или единицах. Положительное значение может означать как перерасход (для затрат), так и перевыполнение (для выручки).

=C2-B2

2. Процент выполнения плана

Демонстрирует, насколько задача выполнена относительно цели. Значение 100% — идеальное попадание.

=ЕСЛИ(B2=0; 0; C2/B2)

Примечание: Функция ЕСЛИ страхует от ошибки #ДЕЛ/0!, если плановое значение равно нулю. После ввода примените к ячейке Процентный формат.

3. Процент отклонения

Показывает, на сколько процентов факт отличается от плана.

=ЕСЛИ(B2=0; 0; (C2-B2)/B2)

Здесь также необходим процентный формат. Отрицательное значение для статей расходов обычно хорошо (экономия), а для доходов — плохо (недовыполнение).

Автоматизация и визуальный контроль

Ручной просмотр чисел неэффективен. Настройте таблицу так, чтобы проблемы были видны мгновенно.

Условное форматирование

Выделите столбец с процентным отклонением и создайте правила:

• Красный цвет: Значение меньше -10% или больше 10% (критическое отклонение).
• Желтый цвет: Значение от -5% до -10% или от 5% до 10% (требует внимания).
• Зеленый цвет: Значение от -5% до 5% (норма).

Это делается через меню: Главная -> Условное форматирование -> Правила выделения ячеек.

Блок итогов

Внизу таблицы (например, на 101-й строке, если данные до 100-й) создайте строку «Итого»:

• План: =СУММ(B2:B100)
• Факт: =СУММ(C2:C100)
• Абс. отклонение: =C101-B101 (или сумма столбца D)
• % выполнения: =C101/B101

Частые ошибки при ведении план-факта

• Смешение периодов: Внесение факта за март в план, составленный на февраль. Всегда проверяйте даты среза данных.
• Отсутствие версионности: Исправление плана «задним числом» без фиксации старой версии. Если план меняется, сохраняйте копию файла или добавляйте столбец «План (актуальный)» и «План (первоначальный)».
• Игнорирование комментариев: Цифра отклонения без пояснения («почему ушли в минус?») бесполезна для руководства. Столбец с причинами обязателен.
• Некорректные нули: Деление на ноль при расчете процентов, если не использована функция ЕСЛИ.

FAQ

Как сделать план-факт для нескольких месяцев на одном листе? Добавьте столбец «Период» (Месяц/Квартал) первым столбцом таблицы. Затем используйте Сводную таблицу (Insert -> Pivot Table), где в строки поместите «Статью», в столбцы — «Период», а в значения — «План» и «Факт».

Можно ли автоматически подтягивать факт из другой таблицы? Да, используйте функцию ВПР (VLOOKUP) или ПРОСМОТРX (XLOOKUP) для поиска фактических данных по уникальному коду статьи в смежном файле или листе с выгрузкой из 1С/CRM.

Что делать, если план был перевыполнен на 200%, это хорошо? Зависит от статьи. Для выручки 200% — отлично. Для расходов (например, фонд оплаты труда) 200% означает катастрофический перерасход бюджета. Всегда анализируйте знак отклонения в контексте типа статьи (Доход/Расход).