text
Журнал

Как консолидировать отчетность с помощью Excel

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

В файле три группы листов:

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

Какие справочники потребуются для консолидации отчетности в Excel

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

В модели есть классификаторы:

Как вводятся и обрабатываются исходные данные для консолидации управленческой отчетности в Excel

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

Ввод, преобразование, систематизация данных. Основной рабочий лист модели – «Журнал движений», представленный на рисунке 2. Все остальные по отношению к нему либо вспомогательные, либо зависимые. Каждая строка в этой таблице – не отдельная операция, а группа операций с одинаковыми аналитиками по дебету и кредиту (при выгрузке из учетной системы их суммы «сворачиваются» по итогам отчетного периода, например, месяца).

Этот лист по сути – модифицированный регистр бухучета со сводными проводками в необходимых и достаточных разрезах (см. таблицу 1. Структура таблицы «Журнал движений»).

Графа Ввод информации Описание
Загрузка
из учетной системы
Вручную Автоматический
расчет
Период V V *   Отчетный месяц, к которому относится проводка. Задается первой датой месяца
Организация V V *   Компания из периметра консолидации
Контрагент V V *   Контрагент, с которым проводится операция. Выбирается из классификатора контрагентов. Используется только в случаях, когда контрагента можно определить
Номенклатурная группа V     Доходный вид деятельности. Используется для отражения прямых затрат, расходов и доходов (обычно это 20-й счет бухучета, субсчета 90-го счета, возможно – 44-й счет)
Статья затрат V     Используется для 20, 23, 25, 26, 44 счетов бухучета
Статья ДДС V     Статья движения денежных средств. Заполняется для счетов учета денежных средств (50–57)
Товар V     Заполняется из классификатора «ТМЦ» для счетов 10 и 41
Партия   V   Номер партии товаров. Заполняется вручную по значению графы «Партия» листа «КонсПоправкиПоНереализПрибТМЦ»
Объект основных средств V     Инвентарный номер объекта основных средств. Заполняется для операций, связанных с куплей-продажей основных средств, а также их амортизацией и модернизацией
Аналитики     V Служебная группировочная графа, которая в одной строке выводит через точку с запятой список использованных в движении аналитик
Счет V     Счет из бухгалтерского (управленческого) плана счетов, по которому совершается операция
Коррсчет V     Счет из бухгалтерского (управленческого) плана счетов, корреспондирующий для счета операции
Оборот Дт V     Сумма дебетового оборота операции
Оборот Кт V     Сумма кредитового оборота операции
Количество V    

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

Необходима для расчета нереализованной прибыли по ТМЦ

Себестоимость V     Себестоимость приобретения (списания) ТМЦ
ВГО     V Внутригрупповые операции. Признак того, что операция выполняется между компаниями, входящими в группу
Тип движения ТМЦ     V Для операций, связанных с движением товарно-материальных ценностей, заполняется направление движения (приход или расход). Используется в формулах в качестве вспомогательного поля для упрощения расчетов
Итоговая консолидационная поправка     V Содержит итоговое значение всех консолидационных поправок по операции
Консолидационная поправка 1     V Сумма корректировки по взаиморасчетам внутри группы
Консолидационная поправка 2     V Сумма корректировки на нереализованную прибыль по ТМЦ (рассчитывается на основе партионного учета)
Консолидационная поправка 3     V Сумма корректировки на нереализованную прибыль по амортизации основных средств (рассчитывается как временная разница)
Консолидационная поправка 4   V   Сумма корректировки финансового результата. Вносится вручную, суммы рассчитываются предварительно на отдельном листе
Элиминированная сумма     V Сумма операции с учетом консолидационных корректировок
Оборот Дт Поправка     V Сумма корректировки по дебету
Оборот Кт Поправка     V Сумма корректировки по кредиту
Оборот Дт Элиминированный     V Консолидированная сумма оборота по дебету (элиминированная)
Оборот Кт Элиминированный     V Консолидированная сумма оборота по кредиту (элиминированная)
* Данные можно вводить и вручную, но это очень трудоемко.

Каждое движение необходимо заносить (загружать) дважды – одна строка по кредиту, вторая – по дебету. Так проще для работы со сводными таблицами и формулами.

Здесь же, в «Журнале движений» исключаются внутригрупповые обороты. В этой операции задействованы два столбца: «ВГО» (в зависимости от того, относится операция к внутригрупповым или нет, в нем автоматически проставляются значения «ИСТИНА» или «ЛОЖЬ» соответственно) и «Консолидационная поправка 1» (рассчитывается сумма корректировки на внутригрупповые обороты).

Признак в столбце «ВГО» устанавливается на основе данных классификатора «Контрагенты» с помощью формулы:
«ЕСЛИОШИБКА(ВПР([@Контрагент];Таблица4;2;ЛОЖЬ);ЛОЖЬ)».

Если в классификаторе «Контрагенты» по столбцу «Внутригрупповой» указано «ИСТИНА» (компания входит в группу) или «ЛОЖЬ» (внешний покупатель или поставщик), то же самое значение отразится в графе «ВГО» на листе «Журнал движений».

Для заполнения столбца «Консолидационная поправка 1» применяется формула:
«ЕСЛИ([@ВГО]=ИСТИНА;-[@[Оборот Дт]] – [@[Оборот Кт]];0)».

Эта формула проверяет, указан ли по соответствующей строке в столбце «ВГО» признак наличия внутригрупповых связей. Если да – рассчитывает величину корректировки как разницу между суммами оборота по дебету и кредиту, если нет – проставляет ноль.

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

Консолидационные корректировки по нереализованной прибыли при внутригрупповой реализации товаров, сырья и материалов. На листе «КонсПоправкиПоНереализПрибТМЦ», представленном на рисунке 3, учитываются партии товарно-материальных ценностей, реализованные внутри группы, движения по ним и их остатки. Эти данные – основа для расчета консолидационных корректировок на листе «Журнал движений» (графа «Консолидационная поправка 2»).

Нереализованная прибыль от продажи ТМЦ элиминируется с применением двух корректировок. Консолидационная поправка 1 полностью исключает суммы операций внутригрупповой продажи ресурсов. Однако она не распространяется на операции с внешними контрагентами и не корректирует себестоимость окончательной продажи – от покупателя, входящего в группу, к внешнему покупателю. Например, товар был продан внутри группы за 1000 руб. при себестоимости 900 руб., при этом возникла нереализованная прибыль, равная 100 руб., которую исключает консолидационная поправка 1. Если затем этот товар продать внешнему контрагенту за 1050 руб., то в системе отразится прибыль 50 руб., а 100 руб. – «потерянную» нереализованную прибыль, временно исключенную консолидационной поправкой 1, необходимо дополнительно добавить к реализации внешнему контрагенту. Для этих целей применяется консолидационная поправка 2.

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

Структура данного листа представлена в таблице 2.

Графа Ввод информации Описание
Вручную Автоматический расчет
Партия V   Уникальный идентификатор партии реализации ТМЦ между компаниями из периметра консолидации
Поставщик V   Компания холдинга, которая продала внутри группы ТМЦ; далее – организация-продавец
Товар V   Запись классификатора «ТМЦ», реализуемый товар (материал) внутри группы
Покупатель V   Компания холдинга, которая приобрела товар внутри группы, далее – организация-покупатель
Сумма начальный остаток V   Графа необходима, если консолидация начинается с даты, когда уже фактически есть партии товаров, реализованных внутри группы. Рассчитывается по себестоимости покупки товара у внешних контрагентов организацией-продавцом, входящей в холдинг
Количество начальный остаток V   Графа необходима, если консолидация начинается с даты, когда уже фактически есть партии товаров, реализованных внутри группы. Рассчитывается количество товара, оставшееся в партии
Сумма прихода   V Себестоимость покупки товара у внешнего контрагента организацией-продавцом
Количество прихода   V Количество товара, реализованное в рамках группы продавцом покупателю (количество прихода на партию)
Себестоимость   V Себестоимость покупки товара у внешнего контрагента организацией-продавцом. Рассчитывается делением суммы прихода на количество прихода (либо суммы начального остатка на количество начального остатка)
Количество расхода   V Количество товара из партии, реализованного внешним контрагентам (т. е. фактическая реализация после всех внутренних операций)
Количество остаток   V Количество товара из партии, оставшееся после реализации внешним контрагентам
Сумма остаток   V Стоимость товара из партии, оставшегося после реализации внешним контрагентам, в ценах закупки от внешних контрагентов
Контрольная сумма прихода   V Стоимость покупки товара организацией-покупателем в трансфертных ценах. Графа необходима для контроля правильности элиминации
Контрольное количество прихода   V Количество товара, приобретенного организацией-покупателем внутри группы
Возникшая нереализованная прибыль   V Сумма возникшей нереализованной прибыли в результате внутригрупповой реализации
Разница по количеству   V Контрольная графа. Рассчитывается разница между количеством товаров, проданных и купленных внутри группы. Если значение отличается от нуля, значит, возникла ошибка при партионном учете в механизме элиминации внутригрупповой нереализованной прибыли по ТМЦ
Погашенная нереализованная прибыль   V Сумма нереализованной прибыли, на которую фактически была произведена консолидационная корректировка себестоимости проданных товаров внешним контрагентам от организации-покупателя, входящей в группу
Остаток нереализованной прибыли   V Сумма нереализованной прибыли, приходящейся на остаток не реализованных внешним контрагентам товаров

По мере реализации товаров внутри группы (данные «Журнала движений»), нужно все операции разделить на однотипные партии (по сочетанию «период – товар – поставщик – покупатель – себестоимость – цена реализации», т. е. практически каждый документ внутригрупповой реализации можно считать партией). На листе «КонсПоправкиПоНереализПрибТМЦ» регистрируются внутригрупповые продажи: партиям присваиваются уникальные номера, указываются поставщик и покупатель из группы компаний, наименование товара (см. рисунок 4. Ввод данных для расчета консолидационных корректировок по нереализованной прибыли при внутригрупповой реализации товаров, сырья и материалов). Так создается список «Партии».

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

Затем к этим же партиям «привязываются» операции реализации ТМЦ от внутригруппового покупателя сторонним контрагентам, «погашается» (уменьшается) остаток рассчитанной нереализованной прибыли.

Для расчета сумм нереализованной прибыли (ее прихода и расхода) на листе «КонсПоправкиПоНереализПрибТМЦ» используется функция СуммаЕслиМн (), которая по сочетаниям партии, внутригрупповой организации-продавца и внутригрупповой организации-покупателя собирает из «Журнала движений» всю необходимую информацию – по количеству и себестоимости операций продажи внутри и вне группы, данные по возникшей и погашенной нереализованной прибыли.

В результате после выбора партий для внутригрупповых реализаций (покупок) в «Журнале движений» в нем заполнится графа «Консолидационная поправка 2», а на листе «КонсПоправкиПоНереализПрибТМЦ» – «Количество расход», «Количество остаток», «Сумма остаток» и «Остаток нереализованной прибыли».

Выбор партий во внутригрупповых операциях реализации (и покупки) позволит сверить входящие и исходящие обороты – разница сразу будет видна, если в графе «Разница по количеству» (на листе «КонсПоправкиПоНереализПрибТМЦ») появится ненулевое значение. Это означает, что нужно искать ошибку – либо некорректно разнесены партии, либо расходятся взаимные обороты у покупателя и продавца (а они должны совпадать по сумме и количеству).

Нужно также контролировать остатки нереализованной прибыли, они не должны быть отрицательными.

Консолидационные корректировки по нереализованной прибыли при внутригрупповой реализации основных средств. На листе «КонсПоправкиПоАмортизации», представленном на рисунке 5, отражается информация об основных средствах, реализованных между участниками группы; амортизации по ним и о консолидационных корректировках амортизации (консолидационная поправка 3), рассчитанных по методу временных разниц (по аналогии с временными разницами в налоговом учете). Эти данные – основа для расчета консолидационных корректировок на листе «Журнал движений».

При внутригрупповой реализации основных средств на листе «Журнал движений» в графе «Основное средство» нужно указывать конкретный объект.

На листе «КонсПоправкиПоАмортизации» вручную заполняются графы «ОС», «Поставщик», «Покупатель» и «Оставшийся срок эксплуатации». Значения по всем остальным полям рассчитываются по формулам с использованием функции СуммЕслиМн(), которая согласно корреспонденциям проводок собирает с листа «Журнал движений» все необходимые данные. В свою очередь в «Журнале движений» автоматически рассчитаются значения в графе «Консолидационная поправка 3», которая корректирует сумму затрат по амортизации у внутригруппового покупателя объекта основных средств. После этого необходимо откорректировать финансовый результат, для чего предназначена следующая консолидационная поправка.

Консолидационные корректировки по финансовому результату. На листе «КонсПоправкиФинРез», показанном на рисунке 6, рассчитываются корректировки финансового результата (консолидационная поправка 4). В результате выполнения этой операции закрываются остатки, возникшие на счетах доходов и затрат из-за выполненных ранее консолидационных корректировок.

Этот этап консолидации можно реализовать различными способами (например, полный пересчет финансового результата или внесение приблизительных поправок) – в зависимости от того, какие меры необходимы.

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

На листе «КонсПоправкиФинРез» период, наименование организации, счета расходов и затрат, а также номенклатурные группы вводятся вручную (достаточно сделать это один раз, а затем копировать данные, меняя только период), распределяемые суммы и суммы поправок рассчитываются автоматически.

Информация о суммах корректировок из «Журнала движений» попадает в столбец «Распределяемая сумма». Коэффициенты распределения расходов берутся со вспомогательного листа «КоэффициентыРасчетаФинРез», представленного на рисунке 7, а там они заносятся вручную.

Расчет остатков. На листе «Расчет остатков», представленном на рисунке 8, вводятся данные об остатках на начало периода; на конец периода и начало следующего они рассчитываются автоматически.

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

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

Конечные остатки определяются на основе входящих, взятых из «Журнала движений».

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

Как формируются консолидированные отчеты

В модели консолидации управленческой отчетности предусмотрено четыре консолидированных отчета:

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

Консолидированный управленческий баланс. Структура этого отчета повторяет структуру обычного баланса с той разницей, что в нем представлены сводные (суммированные по всем компаниям группы) и консолидированные данные (с учетом корректировок), как показано на рисунке 9.

Источником данных для консолидированного управленческого баланса служит таблица «Расчет остатков», из которой берутся остатки по счетам. Остатки объединяются по группам «Активы» (с разделением на «Внеоборотные активы» и «Оборотные активы») и «Пассивы» (включая «Долгосрочные пассивы» и «Краткосрочные пассивы»). Соответственно, для активов остатки берутся по дебету, для пассивов – по кредиту.

Консолидированный отчет о доходах и расходах. В отчет, форма которого представлена на рисунке 10, данные попадают из таблицы «Журнал движений». Значения некоторых показателей отчета о доходах и расходах определяются исходя из оборота по счету учета, а другие рассчитываются по формулам (например, валовая прибыль = выручка без НДС – себестоимость продаж).

Консолидированный отчет о движении денежных средств. Отчет содержит информацию о начальном остатке денежных средств, поступлениях и выплатах, сгруппированных по видам деятельности ( операционной, финансовой и инвестиционной), сальдо всех выплат и поступлений и конечном остатке денежных средств. Он формируется по информации с листов «Расчет остатков» и «Журнал движений». В рассматриваемой модели отчет реализован в упрощенном виде (см. рисунок 11. Консолидированный отчет о движении денежных средств).

Остатки и обороты. Обороты по счетам в краткой форме и детальной аналитике приведены на листах «ОстаткиИОборотыСводнаяТаблица» и «ОборотыСводнаяТаблица» соответственно.

Первый из них – это по сути оборотно-сальдовая ведомость, которая показывает не только первоначальные суммы, но и консолидированные (см. рисунок 12. Остатки и обороты). Есть ограничение на количество аналитик (в данной модели возможно только указание организаций).

На листе «ОборотыСводнаяТаблица» можно сформировать отчеты, показывающие в любых разрезах обороты по счетам (см. рисунок 13. Обороты).

Сверка взаиморасчетов. Лист «СверкаВзаиморасчетов» (см. рисунок 14) поможет выявить ошибки учета и консолидации. Все взаиморасчеты в рамках группы анализируются дважды – у компании-покупателя и компании-продавца (т. е. сравнивается дебетовый оборот у Организации 1 по отношению к Организации 2 с кредитовым оборотом Организации 2 по отношению к Организации 1). Появление любых разниц в крайних правых таблицах, расположенных на этом листе, сигнализирует о наличии ошибки.

Сергей Алферов , финансовый директор компании «Вита Транс»

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

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

Рекомендации по теме

Школа

Проверь свои знания и приобрети новые

Записаться

Самое выгодное предложение

Самое выгодное предложение

Воспользуйтесь самым выгодным предложением на подписку и станьте читателем уже сейчас

Мы в соцсетях
А еще:
×
Чтобы скачать документ, зарегистрируйтесь на сайте!

Это бесплатно и займет меньше минуты!

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
Зарегистрироваться

Зарегистрируйтесь или войдите на сайт:

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

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
Зарегистрироваться

Здравствуйте, меня зовут Александр
Давайте знакомиться!

Чтобы я открыл вам доступ, пожалуйста, пройдите короткую регистрацию

Вам будут доступны:

  • - 15 000 рекомендаций по управлению финансами;
  • - 1000 образцов положений и форм отчетов
  • - 300 финансовых Excel-моделей.

После регистрации пришлю Вам подарок
КНИГА "Cash is king. Управление денежными потоками"

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
...и получить подарки!
Зарегистрироваться

Здравствуйте, меня зовут Александр
Давайте знакомиться!

Чтобы я открыл вам доступ, пожалуйста, пройдите короткую регистрацию

Вам будут доступны:

  • - 15 000 рекомендаций по управлению финансами;
  • - 1000 образцов положений и форм отчетов
  • - 300 финансовых Excel-моделей.

После регистрации пришлю Вам подарок
КНИГА "Cash is king. Управление денежными потоками"

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
...и получить подарки!
Зарегистрироваться
×
Пожалуйста, войдите на сайт

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
Зарегистрироваться

Здравствуйте, меня зовут Александр
Давайте знакомиться!

Чтобы я открыл вам доступ, пожалуйста, пройдите короткую регистрацию

Вам будут доступны:

  • - 15 000 рекомендаций по управлению финансами;
  • - 1000 образцов положений и форм отчетов
  • - 300 финансовых Excel-моделей.

После регистрации пришлю Вам подарок
КНИГА "Cash is king. Управление денежными потоками"

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
...и получить подарки!
Зарегистрироваться
Пожалуйста, войдите на сайт

Зарегистрируйтесь или войдите на сайт:

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
Зарегистрироваться
×

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