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

1092
Шелемех Денис
финансовый директор ГК «СНК»
Чтобы сформировать консолидированный отчет о движении денежных средств в разных валютах, воспользуйтесь моделью в Excel, которая доступна для скачивания. С ее помощью вы избавитесь от большей части ручных операций и потратите меньше времени на консолидированный бюджет.

Какие трудности возникают при подготовке консолидированного отчета о движении денежных средств в разных валютах

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

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

Модель в Excel для формирования консолидированного ОДДС

Я решил эти проблемы, разработав модель в Excel, которую вы можете скачать и адаптировать под себя.

Состав модели:

  • четыре справочника: «Статьи движения денежных средств», «Расчетные счета», «Валюты», «Центры финансовой ответственности» (ЦФО);
  • три листа Excel для ввода данных: «Реестр операций холдинга в долларах», «Реестр операций холдинга в евро» и «Общий реестр операций холдинга в рублях»;
  • лист Excel с промежуточными расчетами «Остатки»;
  • отчет о движении денежных средств холдинга, сформированный прямым методом;
  • встроенные макросы для формирования консолидированного ОДДС. Один автоматически переносит все операции в валюте в общий реестр. Второй дает возможность проваливаться из статей к транзакциям. Третий макрос сбрасывает фильтрацию общего реестра.

Расскажу, как работать с моделью и как ее адаптировать под себя.

Иконка ExcelСкачайте модель в Excel для формирования консолидированного ОДДС

Работа со справочниками

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

Статьи движения денежных средств

Этот справочник состоит из двух столбцов: статья движения денежных средств и признак «Внутри группы». Значения первого столбца служат источником для выпадающего списка на листах «Реестр общий», «Реестр USD», «Реестр EUR». Значения второго задают условие для консолидации, исключать операцию как внутригрупповую или нет.

В справочнике по умолчанию 88 строк. Вы можете заменить названия на свои в пределах этого количества. Внимание! Аналогичные изменения надо внести в названия статей в итоговом отчете (столбец С на листе «ОДДС»). Укажите, с каким знаком эта статья должна переноситься в консолидированный отчет (столбец, А на листе «ОДДС»). Операции оттока — с минусом, притока — с плюсом.

Расчетные счета

Справочник состоит из одного столбца — наименование расчетного счета. Он служит источником для выпадающего списка на листах «Реестр общий», «Реестр USD», «Реестр EUR». Внесите в этот справочник все расчетные счета вашей группы компаний, а также значение «Касса» — для наличных.

Валюты

Справочник состоит из одного столбца — наименование валюты. Он служит источником для выпадающего списка на листах «Реестр общий», «Реестр USD», «Реестр EUR» и «Остатки». В модели по умолчанию используются рубли, евро и доллары США. Если ваша компания работает с другими валютами, внесите соответствующие изменения в данный справочник.

Центры финансовой ответственности

Справочник состоит из одного столбца — наименование центра финансовой ответственности. Он служит источником для выпадающего списка на листах «Реестр общий», «Реестр USD», «Реестр EUR». Внесите в данный справочник название всех фирм (ЦФО) холдинга.

Ввод данных в консолидированный отчет о движении денежных средств

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

Рублевые операции

Заполните общий реестр в следующем порядке — перечислю по столбцам (см. рис. 1).

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

Рисунок 1. Реестр операций холдинга в рублях в ОДДС

Реест операций холдинга в консодидированном ОДДС

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

Валютные операции

Листы «Реестр операций USD» и «Реестр EUR» можно заполнить вручную или перенести из учетной системы. Заполните валютные реестры в следующем порядке — объясню по столбцам (см. рис. 2 и 3).

  • Дата, Статья движения денежных средств, Расчетный счет, Валюта, Центр финансовой ответственности, Комментарий, Наименование контрагента — по аналогии с рублевым реестром.
  • Сумма операции — введите значение в валюте.
  • Курс операции — укажите значение курса, по которому покупали или продавали валюту. В большинстве случаев он не равен курсу Центрального банка.
  • Курс ЦБ — приведите курс Центрального банка на дату транзакции.
  • Сумма по курсу операции — отражает произведение суммы операции и курса операции или курса Центрального банка (в случае отсутствия первого). Протяните формулу из строки выше.
  • Сумма по курсу Центробанка — показывает произведение суммы операции и курса Центрального банка. Скопируйте формулу из строки выше.
  • Отклонение по курсу операции — отражает разницу между суммой по курсу операции и суммой по курсу ЦБ. Протяните формулу из строки выше.
  • Переоценка валютных остатков — содержит произведение конечного остатка в валюте и разницы курсов ЦБ по текущей и предыдущей транзакции. Скопируйте формулу из строки выше.
  • Курсовая разница — показывает сумму отклонения по курсу операции и переоценки валютных остатков. Протяните формулу из строки выше.
  • Конечный остаток в валюте и в рублях — содержит актуальные значения остатков в валюте и по курсу Центрального банка соответственно. Скопируйте формулу из строки выше.

Рисунок 2. Лист «Реестр USD» в консолидированном ОДДС, часть 1

Реестр USD в консодидированном ОДДС

Рисунок 3. Лист «Реестр USD» в консолидированном ОДДС, часть 2

Вторая часть реестра USD в консодидированном ОДДС

Для корректной работы реестра операций в валютах вам необходимо выполнить следующие действия:

  • внесите значение начального сальдо в верхнюю строку;
  • впишите значение конечного сальдо и курса валюты Центрального банка на конец отчетного периода (см. рис. 2).

Чтобы перенести операцию в валюте в общий реестр и откорректировать остатки, выделите любую ячейку нужной транзакции и нажмите кнопку «Копировать запись в общий реестр». Повторите действия для всех валютных операций. Макрос, привязанный к кнопке, перенесет:

  • курсовую разницу на начало года в начальные остатки в рублевом эквиваленте на листе «Остатки»;
  • значение в столбце «Сумма по курсу операции» по всем транзакциям в валюте в общий реестр в столбец «Сумма операции»;
  • курсовую разницу в общий реестр второй строкой к соответствующей транзакции. Значение «Статья ДДС» в общем реестре при этом меняется на «Курсовая разница». Все остальные атрибуты копируются из предыдущей строки;
  • курсовую разницу на конец отчетного периода в общий реестр.

Модель также содержит лист Excel с промежуточными расчетами — таблицей «Остатки» (лист «Остатки»). Таблица логически разделена на две части: начальные и конечные остатки. Рублевый эквивалент начальных остатков (см. рис. 4) модель перенесет в соответствующую строку итогового отчета. Рублевый эквивалент конечных остатков нужен для проверки правильности расчетов. Его значение модель перенесет в ячейку P102 листа «ОДДС». Оно должно совпасть со значением ячейки P101 «Остаток денежных средств на конец периода».

Рисунок 4. Лист «Остатки» консолидированного ОДДС

Лист Остатки в консодидированном ОДДС

Формирование отчета о движении денежных средств

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

Рисунок 5. Лист «Отчет о движении денежных средств»

Лист Отчет о движении денежных средств

Чтобы увидеть, из каких данных сформировалось значение по статье отчета в том или ином месяце, выберите нужную ячейку и нажмите кнопку «Детализация». Это позволит вам проваливаться из статей отчета к транзакциям на листе «Реестр общий».

Подготовлено по материалам журнала «Финансовый директор»

Методические рекомендации по управлению финансами компании



Ваша персональная подборка

    Подписка на статьи

    Чтобы не пропустить ни одной важной или интересной статьи, подпишитесь на рассылку. Это бесплатно.

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

    Школа

    Школа

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

    Записаться

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

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

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

    Живое общение с редакцией

    А еще...




    © 2007–2016 ООО «Актион управление и финансы»

    «Финансовый директор» — практический журнал по управлению финансами компании

    Зарегистрировано Федеральной службой по надзору в сфере связи,
    информационных технологий и массовых коммуникаций (Роскомнадзор)
    Свидетельство о регистрации Эл №ФС77-43625 от 18.01.2011
    Все права защищены. email: fd@fd.ru

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

    Это бесплатно и займет всего 1 минуту.

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

    Внимание!
    Вы читаете профессиональную статью для финансиста.
    Зарегистрируйтесь на сайте и продолжите чтение!

    Это бесплатно и займет всего 1 минут.

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