Управленческая отчетность: несколько путей доработки в Excel

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

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

Рассмотрим эти и другие важные вопросы, касающиеся построения управленческой отчетности в Excel.

ЛИСТ «ПАРАМЕТРЫ»

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

Основная ошибка большинства начинающих специалистов финансово-экономической службы при создании отчетности заключается в том, что они пытаются провести все вспомогательные расчеты или разместить настраиваемые параметры на основных листах модели. Для этого задействуются свободные ячейки справа от таблиц или пространство под ними. Через некоторое время таблицы начинают «расти», и технические расчеты нужно переносить на новое место. Иногда по этой причине появляются ошибки в формулах. Неудачное расположение таких расчетов может привести к тому, что их случайно удалят другие сотрудники.

ЭТО ВАЖНО

Вспомогательные расчеты и настраиваемые параметры модели располагайте на листе «Параметры» («Справочник»).

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

• отчет о прибылях и убытках (план и факт);
• отчет по отклонению выручки (факт и план);
• лист с данными, необходимыми для построения двух динамических графиков;
• лист с параметрами модели.

1. Название организации. Данная информация постоянная, поэтому заполняем ячейку текстовой константой.
2. Период. В рамках примера нам нужно задействовать сразу ряд лет (период с 2015 по 2019 год), обеспечить конечным пользователям свободный доступ к выбору начальной и конечной даты.

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

Начнем с самого простого: создадим вручную диапазон с датами начала периодов (01.01.2015–01.01.2019). Получим последний день каждого года, используя формулу: =ДАТАМЕС(D8;12)-1 (рис. 1).

Функция ДАТАМЕС возвращает порядковый номер даты, отстоящей на заданное число месяцев вперед или назад от заданной даты. Чтобы в итоге получить именно 31-е число каждого года, добавляем в подсчеты –1.

Открываем Диспетчер имен на вкладке Формулы (можно вызвать комбинацией клавиш Ctrl+F3) и создаем два динамических диапазона данных с помощью формул:

Начало_года=СМЕЩ(Параметры!$D$8;0;0;СЧЁТ(Параметры!$D$8:$D$12);1);

Конец_года=СМЕЩ(Параметры!$E$8;0;0;СЧЁТ(Параметры!$E$8:$E$12);1).

Таким образом мы решим два важных вопроса. Во-первых, в случае появления новых временных отметок в первоначальных диапазонах (2020 г., 2021 г. и т. д.) формулы отследят это и добавят данные в списки автоматически.

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

Приступаем к формированию выпадающих списков для начала и конца периода планирования: вкладка Данные → Проверка данных → Тип данных: Список → Источник: Начало_года или Конец_года.

1. Интервал планирования. Интервалов планирования будет два — месяц или квартал. Указывать неделю не будем, так как это слишком детализирует отчет. Для каждого из двух параметров укажем число месяцев — 1 и 3 соответственно. Добавляем новый список с выбором интервала, как делали это для начала и конца периода планирования. Единственное исключение: в данном случае динамического списка не будет, просто укажем диапазон =Параметры!$F$8:$F$9 в Диспетчере имен. Вытягиваем число месяцев с помощью функции ВПР: =ВПР(B9;$F$8:$G$9;2;0).
2. В конце рассчитаем число интервалов: =ОКРУГЛ(ДОЛЯГОДА(Старт;B8;1);0)*12/Число_месяцев.