Превратить бухгалтерские данные в управленческие отчеты можно при помощи Excel

Николаев И.А. — Финансовый директор. — 2009. — № 12.

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

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

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

Таблица. Реестр движения денежных средств ООО «Альфа» по расчетному счету в ОАО «ВТБ-Северо-Запад»

№ п/п	Дата	Месяц	Сумма	Статья ДДС	Контрагент	Первичный документ	ЦФО
001	15.11.09	11	-950,00	РКО	ВТБ Северо-Запад	—	Бухгалтерия
002	15.11.09	11	327 877,50	Поступления от продаж	Северная Звезда, ЗАО	Счёт № 1672 от 30.06.09	Отдел городских продаж
003	16.11.09	11	2 000,00	Поступления от продаж	Материа Медика, ООО	Счёт № 1764 от 01.07.09	Отдел городских продаж
004	22.11.09	11	99 802,57	Поступления от продаж	Випс-Мед, ООО	Счёт № 1244 от 12.03.09	Отдел региональных продаж
...	...	...	...	...	...	...	...

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

Составление реестров

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

Данные в Excel потребуется вносить и обновлять ежедневно. Источник информации — программа, в которой компания ведет бухгалтерский учет. В «1С» данные для заполнения реестра движения денежных средств можно взять из «карточки» счета 51 «Расчетные счета», реестра отгрузок — «карточки» счета 62 «Расчеты с покупателями и заказчиками». Если количество хозяйственных операций в день относительно невелико, данные выгружаются из «1С» в Excel и вручную переносятся в соответствующие реестры. Когда речь идет о большом объеме транзакций (100 и больше), имеет смысл автоматизировать эту работу. Для компании подобная автоматизация обойдется недорого, скорее всего хватит сил собственных программистов.

Например, в группе компаний «Интернест» реестр движения денежных средств заполняют так. Сначала в «1С» формируют «карточку» по счету 51 и сохраняют ее в виде файла Excel. Затем лист с «карточкой» переносят в другой файл Excel, где ведется реестр движения денежных средств. Поскольку количество операций достаточно большое, финансовый директор компании написал макрос (небольшая программа, написанная на языке программирования Visual Basic for Applications и встроенная в файл с книгой Excel. — Примеч. ред.), который автоматически переносит каждую строку карточки в реестр. Соответственно дата операции попадает в поле реестра «Дата», наименование документа-основания — в поле «Первичный документ» и т. д. Остальные сведения (ЦФО, статья БДДС) заполняются вручную. Кстати, если добавить к счету 51 субконто «ЦФО» и «Статьи БДДС», то в карточке по этому счету необходимая аналитика появится автоматически.

После того как все операции перенесены в реестр, остается только добавить данные об остатке средств на расчетном счете на первый день составления реестра. Обновлять созданный отчет имеет смысл после получения банковской выписки за предыдущий день.

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

Алексей Лапиров, директор по внедрению корпоративных информационных систем «ФОРС - Центр разработки», о том, как можно организовать подготовку оперативной отчетности

Большинство компаний для анализа данных использует инструменты, легко реализуемые на базе Excel или «1С». Однако данный подход не лишен некоторых недостатков.

Так, например, если речь идет о получении консолидированной оперативной отчетности по группе компаний или по ряду ЦФУ в рамках одного бизнеса, то сведение данных с помощью Excel, даже с применением макросов, может оказаться задачей трудновыполнимой.

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

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

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

Анализ собранных данных

Первое и самое простое, что можно получить из реестров, — выборка данных по заданному критерию. Для этой цели пригодится команда меню Excel «Автофильтр» (выделяем шапку таблицы, переходим в меню «Данные» — «Фильтр» — «Автофильтр». — Примеч. ред.). Например, включив фильтр по полю «Контрагент» (выбрав наименование определенного контрагента в раскрывающемся меню автофильтра) в реестре движения денежных средств, можно быстро получить информацию о суммах поступлений от того или иного покупателя, по полю «ЦФО» — о динамике движения денежных средств, связанных с работой интересующего подразделения. Если же применить несколько фильтров по разным графам одновременно, не составит труда получить сведения о том, какие платежи от того или иного подразделения ушли к конкретному контрагенту за указанный период времени.

Единственная проблема, которая возникает в связи с использованием фильтра, — определение суммы по отобранным хозяйственным операциям. Решить ее можно так. В одной из ячеек ввести формулу определения промежуточных итогов - «=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;D4:D19241)». Где «109» — код, указывающий Excel на то, какую функцию применить по отношению к отобранным автофильтром значениям (109 — сумма, 101 — среднее значение по выборке, 102 — количество отобранных значений и т.д.), а «D4:D19241» — диапазон ячеек по которым будет определяться сумма. Кстати, по мнению автора, строку с функцией «Промежуточные итоги» лучше разместить выше шапки реестра. Так каждый раз после добавления строк в реестр не придется спускать еще ниже строку с результатами, да и сумма по выборке будет всегда перед глазами.

Сводный отчет о движении денежных средств

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

1. Мышью выделяем реестр движения денежных средств (вместе с шапкой таблицы). Поскольку реестр будет постоянно пополняться, имеет смысл при выделении захватить и пустые строки снизу с большим запасом или же полностью выделить столбцы реестра.
2. Вызываем команду «Сводная таблица» из меню «Данные». В открывшемся окне мастера сводных таблиц выбираем пункты «В списке или базе данных Microsoft Office Excel» и «Сводная таблица» и нажимаем кнопку «Далее».
3. В следующем окне задается диапазон, содержащий данные реестра. В нашем случае диапазон определяется автоматически, поскольку мы его выделили на первом шаге. Переходим к следующему окну (кнопка «Далее»).
4. Остается указать Excel, что сводная таблица будет располагаться на новом листе. После этого «кликаем» мышью кнопку «Готово».

В результате в книге с реестром появляется новый лист (см. рис. 1). Основа положена, теперь остается собрать интересующий отчет о движении денежных средств. Для этого потребуется последовательно перетащить мышкой строки «Статья ДДС», «ЦФО», «Контрагент» из окна «Список полей сводной таблицы» в раздел «Перетащите сюда поля строк». Строку «Сумма» перетягиваем в раздел «Перетащите сюда элементы данных». И наконец, чтобы данные о платежах разбивались по месяцам, перенесем строку «Месяц» из окна «Список полей сводной таблицы» в раздел «Перетащите сюда поля столбцов». Теперь отчет готов (см. рис. 2). По аналогии составляется сводный отчет на основе реестра отгрузок.

Рисунок 1

Рисунок 2

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

Кстати, на тот случай, если сводные отчеты вдруг не обновятся автоматически после корректировки реестров, не будет лишним «кликнуть» по ним правой кнопкой мыши, а в появившемся меню выбрать команду «Обновить данные».

Отчет о динамике дебиторской задолженности

Используя сводные отчеты о движении денежных средств и отгрузках, не составит труда консолидировать данные о динамике дебиторской задолженности клиентов.

Графы отчета о динамике дебиторской задолженности заполняются так (см. рис. 3). В колонке «Контрагент» наименования вводятся либо вручную, либо копируются из реестра отгрузок, а затем сортируются по алфавиту.

Рисунок 3

В случае ручного заполнения важно следить за тем, чтобы вводимые наименования контрагентов полностью соответствовали тому, как они написаны в реестрах. Задолженность каждого контрагента на начало анализируемого периода заносится вручную из оборотно-сальдовой ведомости по счету 62 «Расчеты с покупателями и заказчиками». «Дебиторка» на конец периода определяется как долги на начало плюс отгрузка за период и минус поступившая от контрагента оплата.

А теперь самое интересное. Графы «Отгрузка» и «Оплата» заполняются автоматически на основе сводных отчетов, созданных ранее. Для этого используется стандартная функция Excel — «Получить.данные.сводной.таблицы».

Итак, в первую ячейку графы «Отгрузка» вводится следующая формула:

«=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ («Сумма»;Лист2!$A$16;«Контрагент»;B4)».

Элементы этой формулы означают следующее:

«Сумма» — поле сводной таблицы, чьи данные нужно отразить в отчете о динамике дебиторской задолженности (по сути, это ответ на вопрос, какие именно данные требуется извлечь. В нашем примере - суммы отгрузок);

Лист2!$A$16 — ссылка на сводную таблицу (где искать информацию. В примере источником служит сводная таблица «Отчет об объемах отгрузки по контрагентам в разрезе ЦФО», сформированная на основании реестра отгрузок);

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

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

«=ЕСЛИ(ЕОШИБКА(ПОЛУЧИТЬ.ДАННЫЕ. СВОДНОЙ.ТАБЛИЦЫ(«Сумма»;Лист2!$A$16; «Контрагент»;B4));0;ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(«Сумма»;Лист2!$A$16;«Контрагент»;B4))».

Данная формула проверяет, есть ли требуемые нам данные в сводной таблице. Если их нет, подставляется в отчет — 0, а если есть — полученное значение из сводной таблицы.

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

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