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

Как разработать формы таблиц и алгоритм для анализа данных?

Как прописать формулы расчета?

Как автоматизировать выборку значений?

Как подготовить отчеты в требуемой детализации?

В ходе план-фактного анализа сравнивают и изучают плановые и фактические значения показателей, выявляют и анализируют полученные отклонения и формулируют выводы.

Формируем базу данных

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

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

• товарное направление;

• товарная группа;

• товарная подгруппа;

• номенклатура и т. д.

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

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

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

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

• сломалось оборудование;

• нет комплектующих;

• нет заказов;

• ввели новый продукт (товарная группа 8), которым заменили товарную группу 7.

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

Второй лист файла Excel будем использовать для ежемесячного план-фактного анализа. Назовем его «Анализ». На этот лист с помощью функции ВПР перенесем данные с листа «План-факт» (таблицы одинаковые). Необходимо соблюдать некоторые требования относительно оформления таблиц при использовании функции ВПР:

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

2. Допускается несоответствие в порядке расположения аналитики в столбце А. Не обязательно, чтобы порядок расположения совпадал на обеих страницах. Функция ВПР позволит отсортировать по возрастанию диапазон поиска.

3. Не должно быть пустых строчек в ячейках массива. Если какого-либо значения нет, обязательно ставим «0».

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

=ВПР($A5;Факт!$A$4:$J$12;2;0),

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

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

2 — номер столбца заданного массива, из которого нужно перенести значение;

Важно!

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

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

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

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

К сведению

Данные с анализом за отчетный месяц можно скопировать и сохранить на отдельном листе (например, «Анализ — июль»).

Проводим план-фактный анализ

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

К сведению

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

Важнейшие факторы, влияющие на величину производственной прибыли:

• изменение себестоимости продукции. Снижение себестоимости приводит к росту прибыли, а рост себестоимости прибыль уменьшает. Изменение себестоимости связано с изменением цен на материалы, отклонением норм расхода материалов, изменением объема производства, заработной платы;

• изменение объема производства;

• изменение прайсовой цены;

• изменение структуры выпущенной продукции. Увеличение доли более рентабельных видов продукции в общем объеме производства увеличивает прибыль, рост производства низкорентабельной продукции — уменьшает.