План фактный анализ пример в excel

Анализ план факт в Excel

Добрый день, уважаемые подписчики и читатели сайта! Сегодня поговорим о такой вещи как анализ план факт в Excel.

Данный вопрос, в принципе, не сложен, но иногда возникают вопросы — как можно наглядно показать выполнен ли план или нет?

Представьте себе небольшую таблицу.

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

Выделим всю таблицу, на вкладку «Вставка», в блоке кнопок «Диаграммы» выберем вариант «Гистограмма с группировкой».

На гистограмме уже видно, что за периоды: май, август, ноябрь, декабрь, — план не выполнен (отрицательное значение). Нужно выделить данные этого блока визуально, так как серый цвет не очень заметен:

щелкнуть на ряд данных (разница);
открыть вкладку «Макет»;
выбрать кнопку «Заливка»;
указать цвет (я установил красный).

Видим, столбцы стали более заметными, но они не разделены по смыслу: выполнен план или нет.

В таком случае лучше пойти по пути построения графиков.

удаляем гистограмму;
выделяем таблицу;
переходим на вкладку «Вставка»;
в блоке «Диаграммы» щёлкаем кнопку «Графики»;
выбираем вариант «График с маркерами».

График необходимо улучшить. Удалим линию «Разница», для этого щёлкаем на серую линию и нажимаем кнопку «Delete».

Переходим на вкладку «Конструктор», слева нажимаем кнопку «Добавить элемент диаграммы». В выпавшем списке выбираем «Полосы повышения и понижения», в выпавшем списке снова выбираем «Полосы повышения и понижения».

Диаграмма примет следующий вид.

На графике появились вертикальные прямоугольники — белый (план выполнен), тёмно-серый (план не выполнен). Зальём их соответственно в зелёный и красный цвета. Щёлкаем на белый прямоугольник — выделятся все. На вкладке «Формат» щёлкаем кнопку «Заливка фигуры», выбираем зелёный цвет.

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

Добавим подписи к данным и расположить их соответственно столбцам данных.

На вкладке «Конструктор» щелкаем кнопку «Добавить элемент диаграммы», в выпадающем списке выбираем «Подписи данных», далее «Сверху». Диаграмма будет вот такой.

Вот и всё, полосы понижения и повышения отлично подходят для визуализации любых данных в положительном и отрицательном диапазоне.

Факторный и дисперсионный анализ в Excel с автоматизацией подсчетов

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

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

Дисперсионный анализ в Excel

Условно цель дисперсионного метода можно сформулировать так: вычленить из общей вариативности параметра 3 частные вариативности:

1 – определенную действием каждого из изучаемых значений;
2 – продиктованную взаимосвязью между исследуемыми значениями;
3 – случайную, продиктованную всеми неучтенными обстоятельствами.

В программе Microsoft Excel дисперсионный анализ можно выполнить с помощью инструмента «Анализ данных» (вкладка «Данные» — «Анализ»). Это надстройка табличного процессора. Если надстройка недоступна, нужно открыть «Параметры Excel» и включить настройку для анализа.

Работа начинается с оформления таблицы. Правила:

В каждом столбце должны быть значения одного исследуемого фактора.
Столбцы расположить по возрастанию/убыванию величины исследуемого параметра.

Рассмотрим дисперсионный анализ в Excel на примере.

Психолог фирмы проанализировал с помощью специальной методики стратегии поведения сотрудников в конфликтной ситуации. Предполагается, что на поведение влияет уровень образования (1 – среднее, 2 – среднее специальное, 3 – высшее).

Внесем данные в таблицу Excel:

Открываем диалоговое окно нашего аналитического инструмента. В раскрывшемся списке выбираем «Однофакторный дисперсионный анализ» и нажимаем ОК.
В поле «Входной интервал» ввести ссылку на диапазон ячеек, содержащихся во всех столбцах таблицы.
«Группирование» назначить по столбцам.
«Параметры вывода» — новый рабочий лист. Если нужно указать выходной диапазон на имеющемся листе, то переключатель ставим в положение «Выходной интервал» и ссылаемся на левую верхнюю ячейку диапазона для выводимых данных. Размеры определятся автоматически.
Результаты анализа выводятся на отдельный лист (в нашем примере).

Значимый параметр залит желтым цветом. Так как Р-Значение между группами больше 1, критерий Фишера нельзя считать значимым. Следовательно, поведение в конфликтной ситуации не зависит от уровня образования.

Факторный анализ в Excel: пример

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

всесторонне описать измеряемый объект (причем емко, компактно);
выявить скрытые переменные значения, определяющие наличие линейных статистических корреляций;
классифицировать переменные (определить взаимосвязи между ними);
сократить число необходимых переменных.

Читать еще: Выделить дубли в excel

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

Посмотрим, за счет, каких наименований произошел основной рост по итогам второго месяца. Если продажи какого-то товара выросли, положительная дельта – в столбец «Рост». Отрицательная – «Снижение». Формула в Excel для «роста»: =ЕСЛИ((C2-B2)>0;C2-B2;0), где С2-В2 – разница между 2 и 1 месяцем. Формула для «снижения»: =ЕСЛИ(J3=0;B2-C2;0), где J3 – ссылка на ячейку слева («Рост»). Во втором столбце – сумма предыдущего значения и предыдущего роста за вычетом текущего снижения.
Рассчитаем процент роста по каждому наименованию товара. Формула: =ЕСЛИ(J3/$I$11=0;-K3/$I$11;J3/$I$11). Где J3/$I$11 – отношение «роста» к итогу за 2 месяц, ;-K3/$I$11 – отношение «снижения» к итогу за 2 месяц.
Выделяем область данных для построения диаграммы. Переходим на вкладку «Вставка» — «Гистограмма».
Поработаем с подписями и цветами. Уберем накопительный итог через «Формат ряда данных» — «Заливка» («Нет заливки»). С помощью данного инструментария меняем цвет для «снижения» и «роста».

Теперь наглядно видно, продажи какого товара дают основной рост.

Двухфакторный дисперсионный анализ в Excel

Показывает, как влияет два фактора на изменение значения случайной величины. Рассмотрим двухфакторный дисперсионный анализ в Excel на примере.

Задача. Группе мужчин и женщин предъявляли звук разной громкости: 1 – 10 дБ, 2 – 30 дБ, 3 – 50 дБ. Время ответа фиксировали в миллисекундах. Необходимо определить, влияет ли пол на реакцию; влияет ли громкость на реакцию.

Переходим на вкладку «Данные» — «Анализ данных» Выбираем из списка «Двухфакторный дисперсионный анализ без повторений».
Заполняем поля. В диапазон должны войти только числовые значения.
Результат анализа выводится на новый лист (как было задано).

Та как F-статистики (столбец «F») для фактора «Пол» больше критического уровня F-распределения (столбец «F-критическое»), данный фактор имеет влияние на анализируемый параметр (время реакции на звук).

Для примера также прилагаем факторный анализ отклонений в маржинальном доходе.

БЛОГ

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

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

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

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

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

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

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

Отклонение от выполнения плана – сравниваем Факт с Планом в абсолютном значении, видим отклонения от выполнения плана в абсолюте. Соответственно, отклонение равное «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 с помощью Сводных таблиц

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

Лист Данные:

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

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

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

Лист BI_Факт:

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

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

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

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

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

Качественный план-фактный анализ в Excel

Занимаясь план-фактным анализом, сравнивают и изучают плановые и фактические значения показателей, объясняют полученные отклонения и формулируют выводы.

Для качественного анализа необходимо иметь плановые данные и фактические значения. Из статьи вы узнаете, как c помощью Excel:

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

ФОРМИРОВАНИЕ БАЗЫ ДАННЫХ В ВИДЕ ТАБЛИЦЫ

Сначала подготовим файл Excel. Первый лист будет содержать плановые и фактические данные, поэтому назовем его «План-факт».

Читать еще: Часы в эксель

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

Аналитика отчета может содержать любые показатели (на усмотрение руководства компании):

товарное направление;
товарная группа;
товарная подгруппа;
номенклатура и т. д.

В нашем примере в качестве аналитики используем товарные группы (далее — ТГ), план-фактный анализ проведем по показателю «Производственная прибыль» (рис. 1).

Выводы на основании первичных данных отчета:

1) общий размер фактической производственной прибыли больше на 4786 тыс. руб., план по производственной прибыли перевыполнен на 34 %.

При этом отдельные товарные группы дали положительный прирост, другие — отрицательный. Что является причиной этих отклонений, узнаем с помощью план-факт анализа;

2) по товарной группе 7 выпуска не было. Причины могут быть разные:

поломка оборудования;
отсутствие комплектующих;
отсутствие заказов;
ввод нового продукта (товарная группа 8) и замена им товарной группы 7.

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

Второй лист файла Excel будем использовать для ежемесячного план-фактного анализа. Назовем его «Анализ».

На этот лист с помощью функции ВПР переносят данные с листа «План-факт» (таблицы одинаковые).

Необходимо соблюдать некоторые требования относительно оформления таблиц при использовании функции ВПР:

1. Данные аналитики в крайнем левом столбце (A) в обеих таблицах должны совпадать, так как формула ВПР осуществляет поиск именно по информации, указанной в этом столбце.

Если в фактические данные попадает новое наименование, оно обязательно должно быть отражено в таблице на листе «Анализ».

2. Допускается несоответствие в порядке расположения аналитики в столбце А.

Не обязательно, чтобы порядок расположения совпадал на обеих страницах.

Функция ВПР позволит отсортировать по возрастанию диапазон поиска.

3. Не должно быть пустых строчек в ячейках массива.

При отсутствии какого-либо значения обязательно ставим «0».

Используя функцию ВПР, переносим данные с листа «План-факт» на лист «Анализ» (рис. 2). При заполнении первой ячейки для переноса данных следует указать формулу:

Пояснения к формуле:

$A5 — значение для поиска;

A$4:$J$12 — массив, в котором будет произведен поиск необходимого значения;

2 — номер столбца заданного массива, из которого нужно перенести значение. Важный момент: при копировании формулы номер столбца в первой строчке меняют. Далее эта формула копируется на все строчки: =ВПР($A5;’План-Факт’!$A$5:$I$12;3,4,5 и т. д.;0);

0 — указывает, что диапазон поиска будет отсортирован автоматически (как было сказано выше, данные в таблице для поиска не обязательно должны быть расположены в том же порядке, как в таблице с перенесенными данными; главное — соблюдать количество строк);

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

Как упростить план-факт анализ с помощью Excel

Реализовать такой подход можно с помощью модели в Excel, созданной с использованием сводных таблиц. Формировать бюджет каждого подразделения может, например, его руководитель, занося плановые данные в унифицированную форму в соответствии с установленными требованиями. Так, финансовой службе не составит труда свести все данные по компании в единый бюджет, а потом контролировать его исполнение и анализировать отклонения от плана.

Как разработать шаблон для заполнения плановых данных

Какие справочники нужно создать для план-факт анализа исполнения бюджета с использованием Excel

Как создать таблицу для план-факт анализа исполнения бюджета

Как добавить фактические показатели в таблицу для план-факт анализа

Проверь свои знания и приобрети новые

Самое выгодное предложение

Воспользуйтесь самым выгодным предложением на подписку и станьте читателем уже сейчас

58 решений по оптимизации налогов

Как снизить НДС, сэкономить на налоге на прибыль и взносах и оптимизировать НДФЛ

Все для контроля долгов компании

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

Самые популярные статьи месяца: выбор редакции

Факторный анализ ключевых финансовых показателей

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

Как провести SWOT-анализ компании

Воспользуйтесь пошаговой инструкцией для проведения SWOT- анализа

Самые популярные статьи месяца: выбор читателей

«Финансовый директор» — практический журнал по управлению финансами компании

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