Содержание
Качественный план-фактный анализ в 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 — указывает, что диапазон поиска будет отсортирован автоматически (как было сказано выше, данные в таблице для поиска не обязательно должны быть расположены в том же порядке, как в таблице с перенесенными данными; главное — соблюдать количество строк);
$ — закрепляет область поиска. Можно зафиксировать столбец, строчку или весь диапазон, что позволяет переносить формулу в другие ячейки копированием. Незакрепленные параметры поиска будут изменяться автоматически.
Диаграмма "План-Факт"
Редкий менеджер в своей практике не сталкивается с необходимостью наглядного представления достигнутых результатов по сравнению с запланированными изначально. В разных компаниях я встречал много подобных диаграмм, называющихся "План-Факт", "Actual vs Budget" и т.д. Иногда их строят примерно так:
Неудобство такой диаграммы в том, что зритель должен попарно сравнивать столбцы плана и факта друг с другом, пытаясь удержать всю картину в голове, и гистограмма здесь, по-моему, не лучший вариант. Если уж и строить такую визуализацию, то однозначно нагляднее использовать графики для плана и факта. Но тогда перед нами встает задача наглядного попарного сравнения точек за одинаковые периоды и выделения разницы между ними. Давайте попробуем применить несколько удобных техник для этого.
Способ 1. Полосы повышения-понижения
Это наглядные прямоугольники, соединяющие попарно точки графиков плана и факта на нашей диаграмме. Причем их цвет зависит от того, выполнили мы план или нет, а размер показывает — на сколько:
Включаются такие полосы на вкладке Конструктор — Добавить элемент диаграммы — Полосы повышения/понижения (Design — Add Chart Element — Up/Down Bars) в Excel 2013 или на вкладке Макет — Полосы повышения-понижения (Layout — Up-Down Bars) в Excel 2007-2010. По умолчанию они будут черно-белые, но можно легко изменить их цвет, щелкнув по ним правой кнопкой мыши и выбрав команду Формат полос повышения/понижения (Format Up/Down Bars) . Очень рекомендую использовать полупрозрачную заливку, т.к. сплошная закрывает сами исходные графики.
К сожалению нет легкого встроенного способа регулировать ширину полос — для этого придется воспользоваться небольшим трюком.
- Выделите построенную диаграмму
- Нажмите сочетание клавиш Alt+F11, чтобы попасть в редактор Visual Basic
- Нажмите сочтетание клавиш Ctrl+G, чтобы открыть панель прямого ввода команд и отладки Immediate
- Скопируйте и вставьте туда вот такую команду: ActiveChart.ChartGroups(1).GapWidth = 30 и нажмите Enter:
Само собой, параметром (30) можно поиграться, чтобы получить нужную вам ширину экспериментальным путем.
Способ 2. Диаграмма с заливкой зоны между линиями плана и факта
Этот способ предполагает наглядную заливку (можно со шриховкой, например) области между графиками плана и факта:
Весьма эффектно, не так ли? Давайте попробуем это реализовать.
Для начала добавьте к нашей таблице еще один столбец (назовем его, допустим, Разница), где посчитаем формулой разность между фактом и планом:
Теперь выделим одновременно столбцы с датами, планом и разницей (удерживая Ctrl) и построим диаграмму с областями с накоплением, используя вкладку Вставка (Insert) :
На выходе должно получиться примерно так:
Следующим шагом выделим ряды План и Факт, скопируем их (Ctrl+C) и добавим в нашу диаграмму вставкой (Ctrl+V) — в нашем "бутерброде в разрезе" сверху должны появиться два новых "слоя":
Теперь переключим тип диаграммы для этих двух добавленных слоев в график. Для этого выделите по очереди каждый ряд, щелкните по нему правой кнопкой мыши и выберите команду Изменить тип диаграммы для ряда (Change Series Chart Type) . В старых версиях Excel 2007-2010 дальше можно выбрать нужный тип диаграммы (График с маркерами), а в новом Excel 2013 появится диалоговое окно со всеми рядами, где нужный тип выбирается для каждого ряда из выпадающих списков:
После нажатия на ОК увидим уже похожую на то, что нам нужно картину:
Легко сообразить, что осталось только выделить синюю область и поменять у нее цвет заливки на прозрачный Нет заливки (No Fill) . Ну, и навести общий блеск: добавить подписи, заголовок, удалить лишние элементы в легенде и т.д.
По-моему, это сильно лучше чем столбики, нет?
Загрузка...
