Три шага к управленческой отчетности в Excel

44618
Вихров Андрей
руководитель направления методологии дирекция по экономике и финансам ЗАО «Объединенная металлургическая компания»
Управленческая отчетность, как правило, ограничиваются несколькими простыми формами отчетов с данными о результатах деятельности. Это неудобно и существенно ограничивает анализ информации. Однако можно внедрить полноценную систему формирования управленческой отчетности в Excel. Построение такой модели отчетности включает подготовку справочников, форм для ввода данных и гибких аналитических отчетов. Затраты на поддержку системы в будущем, как и риск ошибок, будут минимальными.

Рассмотрим поэтапно, как создается управленческая отчетность в Excel . Забегая вперед, скажем, что для работы нам понадобятся функции ВПР, СУММЕСЛИ, а также с инструменты «Автофильтр», «Проверка данных» и «Сводные таблицы». 

Единые справочники для управленческой отчетности в Excel

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

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

Если модель создается для компании или группы компаний, где бизнес-единицы занимаются различными видами деятельности, стоит заранее продумать, как та или иная бизнес-единица будет заполнять отчетные формы своими данными. В частности, будет ли у нее возможность работать только с необходимыми ей аналитиками или же придется заполнять единую для всех форму, пропуская несущественные для себя строки. Во втором случае вероятность ошибок выше, поэтому лучше создать справочники двух видов:
  • общие, с одинаковым набором значений (элементов) для всех бизнес-единиц (например, справочники «Затраты» будут содержать такие значения, как материалы, оплата труда, отчисления, прочие расходы);
  • специфичные, содержащие разные наборы значений признака, для каждой бизнес-единицы – свой набор (пример - справочник «Виды продукции»).

Структура общего справочника предельно проста - всего две графы. В одной - порядковый номер строки, в другой – значение аналитики (см. рис. 1).

Рисунок 1 Общий справочник для управленческой отчетности в Excel

Три шага к управленческой отчетности в Excel

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

Рисунок 3 Специфичный справочник для управленческой отчетности в Excel

Три шага к управленческой отчетности в Excel

В ее составе две бизнес-единицы – Уральский и Центральный металлургические заводы (УМЗ и ЦМЗ соответственно). Первый завод производит чугун и сляб и передает его на переработку на второй завод (выпускает металлические балки и швеллеры). В справочнике видов продукции перечислены все производимые продукты (чугун, сляб, балка, швеллер), для каждого из них указано ответственное за его производство предприятие (УМЗ или ЦМЗ), присвоен порядковый номер в целом по ГК «Век» и в рамках бизнес-единицы, назначен специальный код (объединением полей «Наименование бизнес-единицы» и «Порядковый номер продукта в бизнес-единице»). Например, швеллеры выпускает Центральный металлургический завод – соответственно, в строке, где указана эта продукция, приводятся название бизнес-единицы «ЦМЗ», ее номер (2), далее порядковый номер вида «Швеллер» во всем множестве продуктов, производимых на ЦМЗ (их всего два, этому присвоен «2»), и, наконец, код продукта – «ЦМЗ2», где 2 – номер продукта в рамках ЦМЗ.

Для удобства всю таблицу со справочной информацией лучше выделить цветом, включив шапку и захватив дополнительно несколько пустых строк. Именно на эту область ячеек будут настраиваться ссылки из форм. Запас из пустых строк упростит ввод новых значений аналитических признаков – достаточно их дописать в последние строки, и изменения автоматически отразятся во всех зависимых формах. В дальнейшем, когда модель отчетности будет закончена, не рекомендуется в справочниках удалять или менять местами строки, а также отдельные ячейки. Такие действия нарушат связи между таблицами. Можно лишь удалять значения в ячейках или заменять на новые так, чтобы строки оставались на своих местах.

Формы для ввода данных управленческой отчетности в Excel

После того как справочники готовы, можно приступать к разработке второго блока – форм для ввода данных и расчета показателей. Таких форм два типа: аналитические и мастер-формы.

Аналитические формы – это шаблоны операционных отчетов по определенным показателям (выручка, затраты, объем производства и т. д.) в одном или нескольких аналитических разрезах. В них бизнес-единицы вносят свои данные. Мастер-формы – традиционные финансовые отчеты, например, как отчет о прибылях и убытках. В них данные попадают из аналитических форм.

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

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

  • в справочнике выделяется диапазон ячеек с названиями бизнес-единиц, ему присваивается уникальное имя (например, «Спр_БЕ») с помощью команды меню Вставка – Имя – Присвоить;
  • далее выполняется команда меню Данные – Проверка. В появившемся диалоговом окне нужно выбрать тип данных «Список», в поле «Источник» указать заданное ранее имя диапазона («=Спр_БЕ»).

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

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

Структура шаблонной формы с одним аналитическим признаком идентична структуре соответствующего справочника. Разница лишь в колонках «План», «Факт», в которые заносятся числовые данные. Настроить такую форму под конкретную бизнес-единицу поможет инструмент «Автофильтр» (Данные – Фильтр – Автофильтр по столбцу «Бизнес-единица»), он скроет пустые строки, а также строки, относящиеся к другим подразделениям.

При подготовке шаблонной формы с двумя аналитиками нужно определиться с иерархией аналитики. На рисунке 4 приведен пример формы «Отчет о прибыли по продуктам» для ввода фактических данных о понесенных расходах в детализации по видам продукции. В ней задействованы две аналитики – виды продукции (первый уровень иерархии) и статьи расходов (второй уровень). Значение выручки в таблицу переносится из другой формы, а прибыль рассчитывается с помощью заданной формулы.

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

Рисунок 2 Свободная аналитическая форма

Три шага к управленческой отчетности в Excel

* Здесь и далее зеленым полем выделены поля для ввода данных бизнес-единиц.

Наконец, значения из заполненных аналитических форм переносятся в мастер-формы – отчет о прибылях и убытках, отчет о движении денежных средств и баланс. Для суммирования и увязки значений показателей лучше использовать функцию СУММЕСЛИ (критерий поиска для функции – значение аналитики или ее код). Так в случае каких-либо изменений в справочниках все формулы в формах останутся рабочими, ссылки не перепутаются – не придется ничего править. Например, показатель «Выручка» мастер-формы «Отчет о прибылях и убытках» (ОПУ) рассчитывается по формуле: «=СУММЕСЛИ(’Форма_Прибыль по продуктам’!F:F;B4;’Форма_Прибыль по продуктам’!G : G)». Она позволяет выбрать из формы «Отчет о прибыли по продуктам» (см. рис. 4) все строки, в которых в графе «Статья» (столбец F) содержится слово «Выручка» (это кодовое слово указано в ОПУ в ячейке B4), а затем просуммировать по ним выручку (столбец G, где как раз и указаны значения этого показателя).

Рисунок 4 Шаблонная аналитическая форма

Три шага к управленческой отчетности в Excel

Поскольку формы в бизнес-единицах могут заполнять одновременно несколько человек, стоит предусмотреть возможность совместной работы над одним и тем же файлом (Сервис – Доступ к книге – Разрешить изменять файл нескольким пользователям. На вкладке «Подробнее» можно создать журнал изменений и задать порядок действий в случае противоречивых изменений файла).

Нравится статья? Добавьте страницу в закладки, распечатайте или поделитесь с коллегой. 

Построение управленческого отчета в Excel

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

Чтобы избежать путаницы, стоит упомянуть, что заполненные формы по своей сути также могут служить отчетами. Но, в отличие от настраиваемых отчетов (сводных таблиц), у них очень жесткая структура – нет возможности отобразить информацию в разных разрезах. Например, в форме «Отчет о прибыли по продуктам» (рис. 4) затраты на те или иные виды продукции отображаются в детализации по статьям расходов. С ее помощью нельзя разобраться, как выглядит структура расходов по продуктам. Можно, конечно, построить еще одну форму с теми же показателями, но с другой иерархией представления аналитических признаков, и предложить бизнес-единицам ее также заполнять, но это никому не нужный лишний труд и повод для ошибок. Гораздо проще воспользоваться сводными таблицами. Тем более что с их помощью можно создавать как консолидированные отчеты, так и отчеты по одной или нескольким бизнес-единицам.

Настраиваемые управленческие отчеты в Excel могут готовиться на основании одной или нескольких форм с фактическими данными. Если планируется задействовать несколько форм (главным образом, необходимо при консолидации), то для создания сводного отчета понадобится сделать дополнительную промежуточную таблицу – «База для консолидации» (в том же файле, где размещаются формы) и с помощью ссылок скопировать в нее содержимое консолидируемых форм. Пример такой базы представлен на рисунке 5.

Рисунок 5 База для консолидации

Три шага к управленческой отчетности в Excel

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

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

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

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

Подробнее о создании сводных таблиц для обработки данных читайте в статье Простое решение для безошибочной и быстрой консолидации бюджетов.

Скачать файл с примером управленческой отчетности в Excel, состоящей из справочников, форм и отчетов, можно по ссылке в конце статьи. 

Вложенные файлы

Доступно только подписчикам
  • Rukovodstvo.rar


Ваша персональная подборка

    Подписка на статьи

    Чтобы не пропустить ни одной важной или интересной статьи, подпишитесь на рассылку. Это бесплатно.

    Рекомендации по теме

    Школа

    Школа

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

    Записаться

    Самое выгодное предложение

    Самое выгодное предложение

    Воспользуйтесь самым выгодным предложением на подписку и станьте читателем уже сейчас

    Живое общение с редакцией

    А еще...




    © 2007–2017 ООО «Актион управление и финансы»

    «Финансовый директор» — практический журнал по управлению финансами компании

    Зарегистрировано Федеральной службой по надзору в сфере связи,
    информационных технологий и массовых коммуникаций (Роскомнадзор)
    Свидетельство о регистрации ПИ № ФС77-62253 от 03.07.2015;
    Политика обработки персональных данных
    Все права защищены. email: fd@fd.ru

    
    • Мы в соцсетях
    Сайт использует файлы cookie. Они позволяют узнавать вас и получать информацию о вашем пользовательском опыте. Это нужно, чтобы улучшать сайт. Если согласны, продолжайте пользоваться сайтом. Если нет – установите специальные настройки в браузере или обратитесь в техподдержку.
    ×
    Чтобы скачать документ, зарегистрируйтесь на сайте!

    Это бесплатно и займет всего 1 минуту.

    У меня есть пароль
    напомнить
    Пароль отправлен на почту
    Ввести
    Я тут впервые
    И получить доступ на сайт Займет минуту!
    Введите эл. почту или логин
    Неверный логин или пароль
    Неверный пароль
    Введите пароль

    Внимание!
    Вы читаете профессиональную статью для финансиста.
    Зарегистрируйтесь на сайте и продолжите чтение!

    Это бесплатно и займет всего 1 минут.

    У меня есть пароль
    напомнить
    Пароль отправлен на почту
    Ввести
    Я тут впервые
    И получить доступ на сайт Займет минуту!
    Введите эл. почту или логин
    Неверный логин или пароль
    Неверный пароль
    Введите пароль