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

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

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

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

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

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

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

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

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

Дата добавления: 2015-07-15