Разработка оперативного управленческого отчета с помощью Excel

«Планово-экономический отдел» №10 2018 / Технологии управления

 

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

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

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

Чтобы избежать указанных проблем, предлагаем руководителям финансово-экономических служб разработать ежедневный отчет о результатах работы компании, используя возможности табличного редактора Excel. Такой отчет формируется путем выгрузки из учетной базы нескольких отчетов и автоматической их консолидации в файле Excel. Методику создания отчета подробно рассмотрим в данной статье.

ПОКАЗАТЕЛИ ЕЖЕДНЕВНОГО ОПЕРАТИВНОГО ОТЧЕТА

В первую очередь руководителю финансово-экономической службы нужно составить и согласовать с руководством компании перечень показателей для ежедневного управленческого отчета. Можно предложить два критерия отбора:

1) периодичность внесения первичных документов в учетную базу данных компании;

2) полнота информации в первичных документах.

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

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

Исходя из указанных принципов, в качестве набора показателей ежедневного управленческого отчета в формате Excel были выбраны:

  • отгрузка продукции покупателям за текущий день в рублях и килограммах;
  • продажи продукции по номенклатуре в рублях и килограммах;
  • валовая прибыль (сумма отгрузки – сумма сырьевой себестоимости отгрузки) за текущий день в рублях;
  • продажи продукции по каналам сбыта в рублях и килограммах.

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

РАЗРАБАТЫВАЕМ ФОРМАТ ЕЖЕДНЕВНОГО ОПЕРАТИВНОГО ОТЧЕТА

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

  1. Заголовок отчета.
  2. Данные о дневной отгрузке продукции покупателям в рублях и килограммах.
  3. Накопительная сумма продаж за отчетный месяц в рублях и килограммах, данные о валовой прибыли в рублях. В этом блоке будут показатели прогноза отчетного месяца, данные о прошлом месяце и аналогичном месяце прошлого года, а также сравнение показателей отчетного месяца с указанными периодами.
  4. Данные о продажах в аналитике по номенклатуре продукции в рублях.
  5. Данные о продажах в аналитике по номенклатуре продукции в килограммах.
  6. Данные о валовой прибыли в аналитике по номенклатуре в рублях.
  7. Данные о продажах в аналитике по каналам сбыта в рублях.
  8. Данные о продажах в аналитике по каналам сбыта в килограммах.

Опишем работу этих блоков.

Заголовок отчета состоит из трех строчек:

 

Октябрь 2018 г.

 

Количество рабочих дней месяца (всего)

31

Количество отработанных дней месяца

23

Количество отработанных дней месяца (для прибыли)

22

 

Наименование месяца и общее количество рабочих дней месяца пользователь проставляет вручную. Количество отработанных дней месяца также указывают вручную, но здесь есть особенность, реализованная через функции Excel. Если, к примеру, утром 23 октября мы ставим в ячейку число 23, данные отчета автоматически сформируются по состоянию на 23-е число. Предположим, нам нужно посмотреть, какие показатели были в отчете за 20-е число. Для этого мы меняем дату 23 на 20, и отчет автоматически переформируется по состоянию на 20-е число. Этот момент реализован через формулы функции «ЕСЛИ», указанные в листах с учетными данными, которые представлены так:

 

=ЕСЛИ('ИТОГИЦИФРЫ'!$C$3>=U$35;ЕСЛИ(U20<>"";U20/U$35*$AG$35;T37);НД()),

 

где $C$3 — ячейка заголовка из строки о количестве отработанных дней месяца;

U$35 — ячейка листа с учетными данными, в которой указано 20-е число месяца;

U20 — ячейка с данными о сумме или количестве показателя отчета из учетной базы;

$AG$35 — итоговая ячейка дней текущего месяца;

T37 — ячейка с накопительным итогом в сумме или количество за указанный период (в нашем случае с 1 по 20 октября).

Формула выглядит несколько громоздко, но работает эффективно.

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

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

 

Отгрузка текущего дня, руб.

9 009 676

Отгрузка текущего дня, кг

52 291

 

В эту часть отчета данные попадают через формулу, которая использует функцию поиска искомого значения «ГПР»:

=ГПР(C3;'Валовые продажи руб'!1:13;13;0).

Третий раздел отчета показывает динамику выполнения плана отчетного месяца на основе фактических данных за указанное количество дней и сравнивает это значение с аналогичными показателями прошлого месяца и данными за этот же месяц прошлого года (табл. 1).

 

Таблица 1. Данные о продажах и валовой прибыли

Показатель

Факт за 23 дня

Прогноз — текущий месяц, %

Факт — сентябрь 2018 г.

Индекс роста, % (октябрь/сентябрь)

Факт — октябрь 2017 г.

Индекс роста, % (2018/2017)

Выполнение плана продаж, руб.

193 447 255

71,20

221 086 426

114,13

242 211 498

104,17

Выполнение плана продаж, кг

1 365 760

79,29

1 612 852

110,45

1 589 926

112,04

Валовая прибыль (от отгрузки), руб.

78 857 855

78,25

95 853 378

112,19

101 161 645

106,30

 

В этом блоке пользователь вручную вносит фактические показатели за прошлый месяц (сентябрь 2018 г.) и за аналогичный месяц прошлого года (октябрь 2017 г.). Показатели факта отчетного месяца за указанное в заголовке количество дней попадают автоматически через функцию «ГПР». Данные об итоговом прогнозе за весь отчетный месяц попадают по ссылкам на листы отчетной формы.

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

 

Таблица 2. Данные о продажах продукции в рублях по номенклатурным группам

Номенклатура

План, руб.

Трекинг, руб.

Выполнение, %

План на один день

Среднедневные отгрузки

Выполнение среднедневного плана, %

Средняя цена, руб./кг — план

Средняя цена, руб./кг — факт

Средняя цена, руб./кг —отклонение

Продукция: Колбасные изделия

260 378 011

182 291 189

70,01

8 399 291

4 508 277

53,67

170,76

184,80

14,05

Продукция: Колбасы ВМК

19 574 709

16 226 474

82,90

631 442

401 300

63,55

107,44

108,01

0,56

Продукция: Кондитерские изделия

24 323 082

17 125 330

70,41

784 616

423 530

53,98

191,74

190,68

–1,06

Продукция: Молочные изделия

1 400 000

1 948 350

139,17

45 161

48 185

106,70

32,00

26,50

–5,50

Продукция: Мясные полуфабрикаты

50 217 636

33 271 182

66,25

1 619 924

822 836

50,79

157,46

146,96

–10,50

Материалы: Мясное сырье

12 000 000

10 590 150

88,25

387 097

261 907

67,66

215,56

67,32

–148,24

Итого

375 534 898

267 388 423

71,20

           

 

В этой таблице данные в столбец «План, руб.» вносит пользователь, показатели по столбцу «Трекинг, руб.» рассчитываются через функцию «ГПР», которая ищет искомые значения на листе «Валовые продажи, руб.».

В столбце «Среднедневные отгрузки» показатель считается через функцию «СРЗНАЧ». Она определяет среднеарифметическое значение текущего дня на основе суммы заполненных ежедневных данных на листе «Валовые продажи, руб.» по формуле:

=СРЗНАЧ('Валовые продажи руб'!B5:AF5).

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

А. А. Гребенников, главный экономист ГК «Резон»

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

Получить статью целиком бесплатно

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

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