БЛОГ

Только качественные посты

Динамический План Факт анализ в Microsoft Excel

Смотрите видео к статье:


Задача «План Факт анализ» является очень востребованной, особенно в бизнесе.

PLAN_FACT_ANALYSIS_EXCEL_BIWEB

Давайте разберемся, что под этим подразумевается:

Немного теории…

У нас есть План и Факт каких-то значений, например, «Продаж», где:

  • План – плановое/прогнозное значение, которое мы хотим достичь, т.е. «План продаж»
  • Факт – фактическое/текущее значение, которого нам удалось достичь, т.е. «Факт продаж»

Проведем анализ План – Факта, для этого посчитаем следующие значения:

  • Отклонение от выполнения плана – сравниваем Факт с Планом в абсолютном значении, видим отклонения от выполнения плана в абсолюте. Соответственно, отклонение равное «0» говорит нам о том, что план выполнен полностью
  • Выполнение плана — сравниваем Факт с Планом в относительном значении, видим выполнение плана в процентах. Соответственно, результат равный «100%» говорит нам о том, что план выполнен полностью

Детализация План Факт анализа

План может иметь разную детализацию – это определяет детализацию (глубину) План/Факт анализа, например:

  • План может быть выставлен по каждому «Товару» – это будет одноуровневый план
  • План может быть выставлен по каждому «Товару» и для каждого «Клиента» – это будет двухуровневый план
  • План может быть выставлен по каждому «Товару» и для каждого «Клиента» в разрезе «Каналов продаж» – это будет трехуровневый план (предполагается, что один клиент может работать в разных каналах продаж)

Все это называется — уровни планирования, т.е.: 1 уровень, 2 уровень, 3 уровень (на практике более 3-х уровней не используется)

(Здесь нужно уточнить, что естественные атрибуты/иерархии не считаются уровнями планирования. Например, если у нас есть план по товарам, то план по категориям — это просто сумма товаров. Проще говоря, если атрибуты/иерархии не увеличивают количество строк детализации плана, то это не уровень планирования)

Также, как правило, план имеет базовый уровень планирования (детализации) – это Время, чаще всего план детализируется для каждого месяца

Уровни планирования – это самый важный критерий в системе планирования, который определяет сложность планирования. Например, если у Вас трехуровневый план продаж (=трехуровневая система планирования продаж в разрезе: Канал продаж/Клиент/Товар), где имеется:

  • Каналов продаж = 5
  • Клиентов = 1000
  • Товаров = 300

То Вам нужно спланировать: 5 * 1000 * 300 = 1 500 000 строк детализации плана, т.е. плановых значений по умолчанию для каждого месяца. Соответственно, умножаем это на *12 получаем, 18 000 000 строк в год

Поэтому самые распространенные системы планирования в продажах бывают 1-2 уровневые. Вот несколько примеров уровней планирования для отделов продаж:

  • Менеджер/Клиент – один уровень (если с одним клиентом работает один менеджер)
  • Бренд/Товар – один уровень
  • Категория/Товар – один уровень
  • Категория/Бренд — два уровня (если один бренд входит в несколько категорий)
  • Клиент/Товар – два уровня
  • Территория/Товар – два уровня
  • Регион/Товар – два уровня
  • Канал продаж/Товар – два уровня
  • Менеджер/Товар – два уровня
  • Регион/Канал продаж/Товар – три уровня
  • Канал продаж/Клиент/Товар – три уровня (если один клиент работает в нескольких каналах)

В примерах уровень планирования «Товар» можно заменить на «Категорию» или «Брэнд». Это позволит уменьшить количество строк детализации плана продаж

Также, как правило, верхний уровень планирования должен быть связан напрямую с исполнителями – менеджерами отделов продаж, иначе за выполнение плана не будет персональной ответственности

В бизнесе показатели Плана и Факта любых значений называют KPI – ключевые показатели эффективности, об этом подробно можно прочитать в моей статье про KPI

Перейдем к практике…

На практике, ключевой вопрос, который возникает – В какой программе можно выполнить План Факт анализ?

Известно, что на сегодняшний день самым удобным инструментом для анализа данных является Microsoft Excel. Соответственно, План-Факт анализ лучше делать в Excel, и для этого есть три подхода:

  • Подход 1: Значения Плана и Факта хранятся в централизованной базе данных (это вариант наличия в компании полноценной системы Бизнес – аналитики BI), из этой базы BI (SQL или OLAP) данные автоматически подгружаются в Excel, где строится отчет, содержащий «План Факт анализ»
  • Подход 2: Значения Плана хранятся в Excel, а Факт подгружается автоматически из системы Бизнес – аналитики BI, как в первом подходе, на основе этих данных можно также построить отчет, содержащий «План Факт анализ»
  • Подход 3: Значения Плана хранятся в Excel, а Факт вставляется вручную. Например, путем копирования данных из 1С или любой другой корпоративной учетной системы ERP (это случай, когда в компании нет системы Бизнес – аналитики BI), на основе этих данных можно построить отчет, содержащий «План Факт анализ», как во втором подходе

Соответственно, самый продвинутый подход к План Факт анализу – это «Подход 1», который, в первую очередь, позволяет делать динамический План / Факт анализ с помощью Сводных таблиц Excel

Но, как вариант (на случай, если Подход 1 не используется в компании), приведу пример динамического План Факт анализа в Excel для «Подхода 2 и 3», который позволяет получить эффект, близкий к «Подходу 1»

На мой взгляд, это очень удачный и гибкий вариант план факта для предприятия…

Пример: Динамический План Факт анализ в Excel с помощью Сводных таблиц

PLAN_FACT_ANALYSIS_EXCEL_BIWEB

Как работает?

Лист Данные:

  • На листе «Данные» хранится План для каждого месяца в нужной детализации (каждая строка — это минимальный уровень детализации Плана. В нашем случае — Менеджер/Клиент)
  • Также на лист «Данные» автоматически подтягивается Факт из листа «BI_Факт» для каждой строки детализации Плана (т.е. к каждому значению Плана подтягивается соответствующее значение Факта), для этого используется следующая функция Excel:

Функция СУММЕСЛИМН, которая выполняет суммирование данных по нескольким условиям.

В нашем примере используется следующая формула Excel:
=СУММЕСЛИМН
(
BI_Факт!$D:$D; -- Значения Факта
BI_Факт!$A:$A;[@[Месяц ID]]; -- Условие 1
BI_Факт!$B:$B;[@Менеджер]; -- Условие 2
BI_Факт!$C:$C;[@Контрагент] -- Условие 3
)
PLAN_FACT_ANALYSIS_EXCEL_BIWEB

Лист BI_Факт:

  • На лист «BI_Факт» автоматически загружаются фактические данные из корпоративной BI системы (Подход 2) или фактические данные вставляются вручную (Подход 3)

PLAN_FACT_ANALYSIS_EXCEL_BIWEB

Лист План_Факт:

  • Лист «План_Факт» содержит динамический «План Факт анализ», который построен с помощью Сводный таблицы Excel, а источником данных Сводной таблицы является лист «Данные»

PLAN_FACT_ANALYSIS_EXCEL_BIWEB

И самое интересное, что для актуализации данного План Факт анализа при «Подходе 1 и 2» достаточно просто выполнить команду Excel: «Данные» > «Обновить Все», и План Факт станет актуальным на текущий момент времени. В случае «Подхода 3» необходимо предварительно вставить в файл текущий факт продаж

Скачать шаблон Excel файла «Динамический План Факт анализ в Excel» можно здесь

Пошаговая инструкция: Как сделан и работает Excel файл «Динамический План Факт анализ в Excel» находится в видеоуроке к данной статье

Январь 29, 2018
1
24 776

Популярные статьи

1 комментарий

  • Алексей 3 года назад

    Сделал все по Вашему примеру. Почему у меня не активно поле «вычисляемое поле…»?

    Ответить

Оставить комментарий