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

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

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

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

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

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

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

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

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

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

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

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

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

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

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