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

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

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

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

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

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

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

Шаг 1. Подготовьте исходные данные

Основной источник информации для консолидированной управленческой отчетности — бухгалтерские данные из программы «1С» за отчетный период.

Для отчета о прибылях и убытках компании возьмите обороты из карточек счетов 44, 91 и оборотно-сальдовых ведомостей (ОСВ) по счетам 90,09, 96.

Для управленческого баланса (отчета о финансовом положении) сформируйте оборотно-сальдовые ведомости по счетам 01, 02, 04, 05, 09, 41, 50, 51, 55, 57, 58, 60, 62, 66, 67, 68, 69, 70, 71, 73, 75, 76, 79, 80, 84, 96 и возьмите из них сальдо на конец периода.

Для отчета о движении денежных средств нужно разделить операционный и финансовый поток и затем его детализировать. Для этогосформируйте анализы счетов 50, 51, 52, 55, 57 в корреспонденции со счетами расчетов по операциям с инвестиционным и финансовым содержанием.

Шаг 2. Создайте структуру базы данных

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

рисунок 1. Структура базы данны

Теперь расскажу, какие поля должны быть обязательно в вашей базе:

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

Далее добавьте в модель поля с нужной вам аналитикой. Примеры информационных срезов вы можете посмотреть в дополнительных материалах к этой статье.

Шаг 3. Измените исходные данные в соответствии с заданной структурой

Каждый показатель бухгалтерских данных из шага 1 расшифруйте в соответствии с заданной структурой из шага 2. Чтобы избежать ошибок, воспользуйтесь функцией Excel «Проверка на значение». Для этого в рабочем файле на отдельном листе Excel создайте список статей и установите проверку на значение в таблице базы данных финансовой информации. Так вы сможете выбирать нужную расшифровку из списка и не ошибетесь даже при ручном вводе или копировании.

В актив баланса заносите значения со знаком плюс, а в пассив — со знаком минус. В этом случае при расчете сальдо получите ноль.

Шаг 4. Настройте форму финансовой отчетности

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

В области «Выберите поля для добавления в отчет» отметьте все строки. Затем определите порядок статей в отчете — для этого перетащите их в нужную область мышкой. Точно так же можно поступить с колонками. Сводная таблица позволяет выбрать четыре типа значений.

Область «Фильтр отчета». Выберите, какой отчет вы формируете. Например, отчет:

• о прибылях и убытках;
• движении денежных средств;
• управленческий баланс.

Область «Название строк». Укажите все расшифровки бухгалтерских данных, которые используете, а также другие колонки таблицы, которые нужны для отчетности. Строки добавляйте в порядке, который хотите видеть в конечном отчете.

Область «Название столбцов». Выберите год, месяц, тип отчетности.

Область «Значения». Укажите числовые значения: сумму, количество или любые другие, которые используете в отчетности.

Каждый отчет формируйте на отдельном листе книги Excel.

Так я подготовил три формы отчетности: управленческий баланс (см. рис. 2), отчет о прибылях и убытках (см. рис. 3) и отчет о движении денежных средств (см. рис. 4). Теперь, если акционеры просят детализировать те или иные статьи отчетности, я могу собрать данные за короткое время.

Шаг 5. Проверьте корректность финансовой отчетности

Перед тем как подавать управленческую отчетность акционерам, проверьте ее корректность.

Этап 1. Для начала убедитесь, что не задвоили названия статей, например «Ком. расх.» и «Коммерческие расходы». Затем визуально проверьте базу данных и, если в ячейках присутствуют маркеры ошибок, например «#ССЫЛКА!» или «#Н/Д», найдите и исправьте неверные ссылки и формулы.

Чтобы в сводном отчете не было пробелов, заполните все строки в базе данных, для этого на место пустых ячеек вставьте «0». Если вы составляете отчетность по группе компаний, проверьте, все ли юридические лица добавили.

Этап 2. Проверьте отчетность на логические ошибки. Для этого убедитесь, что показатели или изменения показателей в отчетах вы можете объяснить. Затем обратите внимание на значения, которые должны быть константой, например прибыль за прошлый год, резервы или безнадежные долги. Если они изменились, значит, в отчете есть ошибка.

Этап 3. На завершающем этапе проверьте финансовую арифметику в итоговых формах. Отчеты сформированы с ошибками, если:

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