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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Наименование месяца и общее количество рабочих дней месяца пользователь проставляет вручную. Количество отработанных дней месяца также указывают вручную, но здесь есть особенность, реализованная через функции 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 октября).

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

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

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

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

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

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

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

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

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

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

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

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