Шаблон Excel, который поможет быстро сформировать отчет о движении денежных средств

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

Во многих компаниях управленческий отчет о движении денежных средств (отчет ДДС) формируется в Excel на основе данных, которые выгружаются из «1С». Для каждой хозяйственной операции (проводки) приходится определять статью поступления или выбытия, а также указывать период (месяц). Например, если в выгруженном отчете из «1С» есть проводка Д-т 51 «Расчетные счета» – К-т 66.3 «Краткосрочные займы в рублях», то сумма по этой операции записывается по статье «Привлечение кредитов и займов» в управленческом отчете. А когда количество проводок исчисляется сотнями, да еще список статей управленческого отчета о движении денежных средств включает 30–40 наименований, ошибки неизбежны.

Упростить эту работу и составить нужный отчет буквально за несколько минут можно, если воспользоваться шаблоном Excel, разработанным авторами статьи. В шаблоне объединены:

  • четыре рабочих листа Excel. Первые три предназначены для выгрузки и обработки карточек 50, 51 и 52 счетов («Касса», «Расчетный счет» и «Валютный счет» соответственно). Четвертый лист – сам отчет, формируемый прямым методом;
  • встроенный макрос, написанный на язы­ке про­граммирования VBA (Visual Basic for Applications), автоматизирующий процесс разнесения сумм проводок по статьям отчета о движении денежных средств. Так­же макрос отвечает за возможность сквозного анализа цифр в уже сформированном отчете – функция drill - down. Другими словами, кликнув на сумму по статье, Excel выдаст информацию о том, какие проводки ее сформировали. Словом, все, как в дорогостоящих информационных системах.

Теперь расскажем подробно и по порядку о том, как работать с предлагаемым решением.

Обработка исходных данных

Первое, что потребуется сделать, – выгрузить из «1С» в формате xls карточки счетов 50 «Касса», 51 «Расчетные счета» и 52 «Валютные счета», например, за период с 1 января текущего года и до текущей даты. Данные первой карточки копируются в шаблон на лист «Касса», второй – на лист «Расчетный счет», третьей – «Валютный счет».

 таблица. Логика присвоения статей проводкам, реализованная в программе

Статья
Проводка
Прочие поступления
Д-т 51 «Расчетные счета» К-т 70 «Расчеты с персоналом по оплате труда»Д-т 51 «Расчетные счета», 52 «Валютные счета» К-т 91.1 «Прочие доходы и расходы»
Привлечение кредитов и займов
Д-т 51 «Расчетные счета» К-т 66.3 «Краткосрочные займы в рублях»
Снятие наличных денежных средств
Д-т 50.1 «Касса» К-т 51 «Расчетные счета»
Выдача кредитов и займов
К-т 58.3 «Предоставленные займы» Д-т 51 «Расчетные счета»
Возврат привлеченных ранее займов, включая проценты (в руб.)
Д-т 66.3 «Краткосрочные займы в рублях», 66.4 «Проценты по краткосрочным займам в рублях» К-т 51 «Расчетные счета»
НДС
Д-т 68.2 «НДС» К-т 51 «Расчетные счета»
Налог на прибыль
Д-т 68.4 «Налог на прибыль» К-т 51 «Расчетные счета»
Транспортный налог
Д-т 68.5 «Транспортный налог» К-т 51 «Расчетные счета»
Налог на имущество
Д-т 68.8 « Налог на имущество» К-т 51 «Расчетные счета»
Налог на землю
Д-т 68.10 «Налог на землю» К-т 51 «Расчетные счета»
Отчисления на социальные нужды
Д-т 69.1, 69.2.2, 69.2.3, 69.3.1, 69.3.2 «Расчеты по социальному страхованию и обеспечению» К-т 51 «Расчетные счета»
Заработная плата
Д-т 70 «Расчеты с персоналом по оплате труда» К-т 51 «Расчетные счета»
Расчетно-кассовое обслуживание
Д-т 91.2 «Прочие расходы» К-т 51 «Расчетные счета», 52 «Валютные счета»
Прочерк (отражается как “ – –– – –”). Соответствующей проводке не нужно присваивать статью, так  как не происходит поступление или расход денежных средств
Д-т 50.1 «Касса» К-т 51 «Расчетные счета»

Далее для каждой проводки указывается статья отчета о движении денежных средств и месяц. Набирать с клавиатуры ничего не придется, ручной ввод практически исключен. Для запуска процедуры автоматического разнесения хозяйственных операций нужно нажать комбинацию клавиш ALT + F8, в появившемся списке макросов выбрать EnterItemsAndMonths и кликнуть по кнопке «Выполнить». Автоматически статьи будут присвоены тем проводкам, которые однозначно интерпретируются (их перечень приведен в таблице). Программа поочередно переберет все строки на листах «Касса», «Расчетный счет» и «Валютный счет» (см. рис. 1), там, где возможно, укажет нужные статьи (вспомогательный столбец «J»). Она же расставит и месяцы (столбец «К»).

рисунок 1. Карточка счета 51 с присвоенными наименованиями статей и указанием месяца

Шаблон Excel, который поможет быстро сформировать отчет о движении денежных средств

Часть строк в карточках счетов программа пропустит. Это вполне оправданно. Ведь не каждой проводке можно однозначно поставить в соответствие статью отчета о движении денежных средств. Например, проводке Д-т 60.1 «Расчеты с поставщиками» – К-т 51 «Расчетные счета» могут соответствовать статьи «Сырье и материалы», «Техническое обслуживание и ремонт», «ГСМ». Для таких случаев наименование поступлений и выплат нужно будет выбрать самостоятельно из раскрывающегося списка в соответствующих ячейках. Исходные данные для этого списка перечислены в диапазоне M9:M48 на трех листах*.

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

Отчет размещается на отдельном листе шабло­на Cash flow. Чтобы перенести в него суммы поступ­лений и выплат с карточек счетов, нужно снова обратиться к перечню макро­сов (ALT + F8), но на этот раз выбрать Cash_flow. В соответствии с ранее размеченной аналитикой программа распределит исход­ные данные в отчете по статьям поступления и выбытия денег и месяцам. Эта информация, в свою очередь, послужит основой для автоматического расчета данных по всем укрупненным статьям, например, таким, как «Поступления от операционной деятельности» или «Платежи по финансовой деятельности». Обратите внимание, что последняя операция производится с помощью обычных формул Excel, она не прописана в мак­росе. Программой предусмотрено лишь суммирование в пределах статей поступления и выплат нижнего уровня (например, «НДС», «Покупатель 1» и т. д.).

Шаблон позволяет представить отчет о движении денежных средств как в полном (детализированном), так и в свернутом виде. Для удобства работы с документом наименования укрупненных статей, а также отчетных периодов оформлены как кнопки (серый фон). Чтобы раскрыть ту или иную статью (вывести на экран ее составляющие), достаточно щелкнуть по ней мышью. Для некоторых строк отчета заложено несколько уровней детализации. Например, «Операционная деятельность» – «Поступления от операционной деятельности» – «Поступления от покупателей». Если последовательно раскрывать каждую из статей, можно получить максимально детализированный отчет о движении денежных средств. И наоборот, последовательное сворачивание строк обеспечивает пользователя максимально компактной формой.

Развернуть или свернуть отчет можно и в одно действие – щелчком по кнопке «На­именования статей» (см. рис. 2).

рисунок 2. Детализированный отчет о движении денежных средств

Шаблон Excel, который поможет быстро сформировать отчет о движении денежных средств

Если же кликнуть по названию месяца, слева от него появятся три вспомогательных столбца – «Касса», «Расчетный счет» и «Валютный счет» (см. рис. 2). В этих столбцах напротив статей, по которым в соответствующем периоде имело место движение денежных средств, проставлены надписи «Банк. Выписка». Щелкнув по ним, можно провалиться в проводки и посмотреть, в результате каких операций сформировалась сумма по той или иной статье. Это, по сути, функция drill - down, которая встречается в профессиональных программных продуктах.

Предлагаемый шаблон настроен на определенный набор статей движения денежных средств. Если в вашей компании этих статей больше или используются другие названия, то для корректной работы модуля понадобится внести небольшие изменения в програм­мный код. Иначе говоря, адаптировать программу под свои нужды. О том, как это можно сделать, подробно написано в инструкции, которую можно скачать по ссылке в конце статьи.

При работе с шаблоном не забудьте разрешить запуск макросов, а также обеспечить доступ программы к объектам VBA (Разработчик – Безопасность макросов – Параметры макросов – Доступ к объектной модели проектов VBA).

* Если в этих ячейках вы не видите данных – все нормально. Цвет шрифта – белый. Это сделано, чтобы не загромождать лист лишней информацией. А если хотите отобразить содержание ячеек, выделите их и примените стандартный черный цвет. – Прим. ред.

Вложенные файлы

Доступно только подписчикам
  • Файл Excel, содержащий шаблон отчета и инструкцию по его адаптации.zip


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

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

Школа

Школа

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

Записаться

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

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

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

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

А еще...


Рассылка




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

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

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


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

В подарок, на адрес электронной почты, которую Вы укажете при регистрации, мы отправим форму «Порядок управления дебиторской задолженностью компании»

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

Еще Вы сможете бесплатно:
Скачать надстройку для Excel. Узнайте риск налоговой проверки в вашей компании Прочитать книгу «Запасной финансовый выход» (раздел «Книги»)

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