Составим консолидированный ОДДС с помощью Excel

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

МЕТОДИКА КОНСОЛИДАЦИИ ОДДС В ТАБЛИЧНОМ РЕДАКТОРЕ EXCEL

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

• производственная компания;

• транспортная компания;

• торговый дом;

• компания — балансодержатель основных средств группы;

• управляющая компания группы.

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

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

Во-вторых, в консолидированном ОДДС в обязательном порядке должны быть выделены внутригрупповые перемещения денег. Это нужно, чтобы видеть суммы внутренних оборотов, которые не влияют на общую сумму денежных средств ГК, иметь возможность проверить корректность отражения внутренних движений денег между компаниями группы.

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

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

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

ТЕОРЕТИЧЕСКИЕ АСПЕКТЫ СОСТАВЛЕНИЯ КОНСОЛИДИРОВАННОГО ОДДС В EXCEL

Всем известный табличный редактор Excel теоретически позволяет консолидировать данные о движении денежных средств из разных отчетов тремя основными способами:

• написание формул в ячейках (самый простой способ);

• использование механизма консолидации;

• создание сводной таблицы.

Использование функционала формул

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

На листы с отчетами компаний мы сохраняем ОДДС из различных учетных программ, а в ячейках сводного отчета прописываем ссылки на ячейки этих отчетов. В итоге получаем консолидированный отчет в целом по группе компаний.

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

Использование механизма консолидации данных

В данном случае применяем встроенный в редактор Excel механизм консолидации. Для этого в меню функций выбираем раздел «Данные», далее — подраздел «Консолидация». В появившемся окне указываем следующие параметры:

• Функция — для консолидации ОДДС здесь следует указать функцию «СУММА»;
• Ссылка — выбираем поочередно таблицы на каждом из листов с отчетами компаний группы;
• Список диапазонов — выбрав через ссылку нужную таблицу с отчетом, нажимаем кнопку «Добавить». В итоге получаем перечень всех таблиц с отчетами, которые хотим консолидировать в сводный ОДДС.

При использовании этого способа консолидации данных нужно соблюдать несколько условий:

•  таблицы всех объединяемых ОДДС компаний группы должны быть одинаковы;
• названия столбцов всех консолидируемых таблиц должны быть одинаковыми;
• в консолидируемых таблицах не должно быть пустых строк и столбцов.

На рисунке 2 показан пример использования механизма консолидации для объединения данных из отчетов компаний группы в сводный ОДДС по группе.

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

Использование механизма сводных таблиц

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

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

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

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

ПРИМЕР ФОРМИРОВАНИЯ КОНСОЛИДИРОВАННОГО ОДДС ГРУППЫ КОМПАНИЙ

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

• производственная компания (ПК) — производство продукции;

• торговая компания (ТД) — реализация продукции;

• управляющая компания (УК) — транспортные услуги и управление группой;

• индивидуальный предприниматель (ИП) — юридические услуги.

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

• бухгалтерские службы компаний ежедневно формируют отчеты о движении денежных средств в учетных программах, сохраняют их в формате и отправляют экономисту управляющей компании;
• экономист управляющей компании ежедневно заполняет в отдельной книге лист с отчетом о движении денежных средств группы. По строкам отчета отражаются статьи движения денежных средств, по столбцам — компании группы и общая сумма по холдингу;
• на отдельном листе той же книги создаются еженедельные отчеты о движении денежных средств ГК. Показатели отчетов автоматически формируются на основе формул типа СУММ из ячеек ежедневных отчетов;
• на этом же листе книги автоматически формируется сводный ОДДС группы компаний, показатели которого на основе формул типа СУММ собираются из ячеек еженедельных отчетов;
• дополнительно к сводному отчету на основе ежедневных данных о движении денежных средств автоматически формируются расшифровки о внутригрупповом перемещении денег между компаниями группы.