Электронная версия журнала

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

«Планово-экономический отдел» №11 2017 / Анализ

 

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

Для качественного анализа необходимо иметь плановые данные и фактические значения. Из статьи вы узнаете, как c помощью 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 — указывает, что диапазон поиска будет отсортирован автоматически (как было сказано выше, данные в таблице для поиска не обязательно должны быть расположены в том же порядке, как в таблице с перенесенными данными; главное — соблюдать количество строк);

 

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

 

 

 

 

A

B

C

D

E

F

G

H

I

J

K

1

План-факт

2

Товарные группы

Май 2017 г.

3

План (выпуск), тыс. руб.

Факт (выпуск), тыс. руб.

Отклонение, тыс. руб.

Отклонение, %

4

количество, шт.

себестоимость выпуска

сумма выпуска (прайс)

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

количество, шт.

себестоимость выпуска

сумма выпуска (прайс)

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

5

ТГ 1

1039

17 835

25 479

7644

1158

20 930

29 587

8658

1014

13

6

ТГ 2

941

5545

7296

1751

1020

5809

7407

1598

–153

–9

7

ТГ 3

459

4175

5642

1467

536

4667

6118

1451

–16

–1

8

ТГ 4

38 591

3611

4815

1204

39 650

4192

5529

1337

134

11

9

ТГ 5

14 869

2337

3075

738

18 021

2547

3540

993

255

35

10

ТГ 6

298

1757

2510

753

321

1825

2581

756

3

0

11

ТГ 7

187

1437

1796

359

0

0

0

0

–359

–100

12

ТГ 8

0

0

0

0

536

9305

13 212

3907

3907

0

13

Итого

56 384

36 696

50 611

13 915

61 242

49 274

67 974

18 701

4786

34

 

Рис. 1. Данные план-факт

 

 

 

A

B

C

D

E

F

G

H

I

J

1

Плановые и фактические показатели по выпуску

2

Товарные группы

Май 2017 г.

Отклонение

3

План (выпуск), тыс. руб.

Факт (выпуск), тыс. руб.

производственная прибыль, тыс. руб.

4

количество, шт.

себестоимость выпуска

сумма выпуска (прайс)

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

количество, шт.

себестоимость выпуска

сумма выпуска (прайс)

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

5

ТГ 1

1039

17 835

25 479

7644

1158

20 930

29 587

8658

1014

6

ТГ 2

941

5545

7296

1751

1020

5809

7407

1598

–153

7

ТГ 3

459

4175

5642

1467

536

4667

6118

1451

–16

8

ТГ 4

38 591

3611

4815

1204

39 650

4192

5529

1337

134

9

ТГ 5

14 869

2337

3075

738

18 021

2547

3540

993

255

10

ТГ 6

298

1757

2510

753

321

1825

2581

756

3

11

ТГ 7

187

1437

1796

359

0

0

0

0

–359

12

ТГ 8

0

0

0

0

536

9305

13 212

3907

3907

13

Итого

56 384

36 696

50 611

13 915

61 242

49 274

67 974

18 701

4786

 

Рис. 2. Использование функции ВПР

 

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

АЛГОРИТМ ПЛАН-ФАКТНОГО АНАЛИЗА

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

 

К СВЕДЕНИЮ

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

 

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

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

 

Е. И. Полевая, начальник финансового отдела

Материал публикуется частично. Полностью его можно прочитать в журнале «Планово-экономический отдел» № 11, 2017.

Купить этот номер в электронном виде

Подпишитесь на нашу рассылку

Рассылка о новых материалах в блоге и новых номерах журналов. Отправляется в среднем 1 письмо в 2 недели.