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

Мы подошли к ключевому элементу учетной системы — отчету о движении денежных средств.

Добавим в уже созданную книгу еще один лист — ОДДС.Создадим структуру отчета так, как показано на рис. 1. Рассмотрю эту структуру подробнее.

Рисунок 1. Форма отчета о движении денежных средств

Отчет состоит из трех разделов: операционная деятельность, финансовая деятельность и инвестиционная деятельность.

Каждый раздел состоит из двух подразделов — поступления и платежи, а также строки сальдо.

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

Каждая строка отчета содержит свой уникальный пятизначный числовой код.

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

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

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

Группа 1. Рассчитываемые из значений других показателей отчета.Это всевозможные суммирующие показатели. Например, значение показателя «Поступления от операционной деятельности» определяется как сумма показателей «Поступления от продаж» и «Прочие поступления». Показатель «Сальдо по операционной деятельности» определяется вычитанием показателя «Платежи по операционной деятельности» из показателя «Поступления от операционной деятельности». Весь столбец «Итого» рассчитывается суммированием значений по каждой статье ДДС за каждый месяц, с января по декабрь. То есть соответствующие показателям первой группы ячейки необходимо заполнить арифметическими операциями между ячейками, на основе которых они рассчитываются (сложение или вычитание), либо использовать функцию СУММ.

Группа 2. Рассчитываемые на основе данных реестра.С этими показателями сложнее, так как они должны каким-то образом вычисляться на основе данных, находящихся в реестре. Например, ячейка D6 должна содержать сумму всех транзакций реестра, одновременно удовлетворяющих двум требованиям:

• значение графы «Статья ДДС» должно равняться «Поступления от продаж»;
• значение графы «Месяц» должно равняться 1 (январь).

Подобный расчет мы можем произвести с помощью очень мощной и важной для нашей системы управленческого учета функции — СУММЕСЛИМН. Она имеет следующий синтаксис:

СУММЕСЛИМН(ДиапазонСуммирования;
ДиапазонУсловий1; Условие1;
[ДиапазонУсловий2; Условие2]; …)

Аргументы функции означают следующее:

• ДиапазонСуммирования — группа ячеек, значения которых суммируются при одновременном выполнении условий Условие1, Условие2 и т. д.;
• ДиапазонУсловий1— группа ячеек, значения которых проверяются на соответствие определенным условиям, необходимым для суммирования ячеек из диапазона суммирования;
• Условие1— условие, на соответствие которому проверяются ячейки из ДиапазонаУсловий1;
• ДиапазонУсловий2, Условие2 — аргументы, аналогичные ДиапазонУсловий1 и Условие1, но проверяющие другой диапазон на соответствие другому условию.

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

В нашем случае аргументы функции СУММЕСЛИМН для ячейки D6 должны быть следующими:

• ДиапазонСуммирования — столбец B реестра операций (графа «Сумма операции»);
• ДиапазонУсловий1— столбец D реестра операций (графа «Статья ДДС»);
• Условие1— значение «Поступления от продаж» или ссылка на ячейку C6 (второй вариант более предпочтителен, так как позволит скопировать нашу формулу на ячейки, соответствующие другим статьям ДДС);
• ДиапазонУсловий2 — столбец G реестра операций (графа «Статья ДДС»);
• Условие2 — значение «1» или ссылка на ячейку D3 (по тем же причинам, что и в случае с Условием1).

С учетом всего вышесказанного формула в ячейке D6 должна выглядеть так:

=СУММЕСЛИМН(Реестр!$B:$B;Реестр!$D:$D;$C6;
Реестр!$G:$G;D$3)*ЕСЛИ($A6="-";-1;1)

Еще две особенности этой формулы нуждаются в нашем пристальном внимании.

1. Знаки доллара в аргументах «Реестр!$B:$B», «Реестр!$D:$D» и «Реестр!$G:$G» необходимы для того, чтобы ссылки на данные столбцы не сбивались при копировании формулы в ячейки, расположенные правее ячейки D6. Аналогично в аргументах «$C6», «D$3» и «$A6» стоит один знак доллара (перед названием строки или столбца) для того, чтобы при копировании формулы вправо и вниз сохранялись ссылки на столбец отчета, содержащий название статей ДДС, и строку отчета, содержащую номер месяца.

2. Выражение «*ЕСЛИ($A6="-";-1;1)» необходимо для того, чтобы операции оттока денежных средств (которые, как мы помним, заносятся в реестр с минусом) в отчете меняли свой знак на противоположный (путем умножения на -1). Это позволит не загромождать разделы платежей по операционной, финансовой и инвестиционной деятельности лишними символами «минус», так как в отчете и так понятно, что данные операции обозначают отток.

Заполнив ячейку D6 указанной выше формулой, мы можем скопировать эту формулу во все остальные ячейки отчета, рассчитываемые из данных реестра, простым нажатием комбинации клавиш Ctrl-C (команда «Копировать») и Ctrl-V (команда «Вставить»), и в результате наша формула в каждой ячейке примет вид, необходимый для корректного расчета ее значения. Например, в ячейке L50 формула будет выглядеть так:

=СУММЕСЛИМН(Реестр!$B:$B;Реестр!$G:$G;L$3;
Реестр!$D:$D;$C50)*ЕСЛИ($A50="-";-1;1)

Забегая вперед, скажу, что функция СУММЕСЛИМН будет использоваться во всех отчетах нашей учетной системы (кроме баланса), так как практически все отчеты будут формироваться путем осуществления выборки из реестра транзакций, удовлетворяющих тем или иным критериям. Вот почему все транзакции должны находиться в одном реестре — в противном случае нам пришлось бы в отчетах собирать данные из реестров несколькими функциями СУММЕСЛИМН в одной формуле, что сильно загромоздило бы отчет и затормозило скорость его работы.

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

Рисунок 2. Реестр с добавленными в него операциями движения денежных средств

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

Рисунок 3. Заполненный отчет о движении денежных средств

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

Как видно из рис. 3, в январе поступления на счета компании от ее клиентов составили 123 655 рублей. Интересно узнать, от кого именно были получены данные денежные средства. И в этом нам поможет реестр, а также два его механизма, которые мы добавили как раз для таких целей: автофильтр и промежуточные итоги.

Откроем лист реестра, нажмем на стрелочку в правом нижнем углу ячейки шапки «Статья ДДС» и снимем галочки со всех статей, кроме статьи «Поступления от продаж». Реестр примет следующий вид, изображенный на рис. 4. Как видно, все строки реестра кроме строк, в которых в графе «Статья ДДС» присутствует значение «Поступления от продаж», оказались скрытыми, а ячейка B2, содержащая промежуточные итоги по выборке, теперь содержит значение 123 655 рублей, то есть как раз сумму январских поступлений от клиентов компании. Эта сумма теперь расшифрована по контрагентам (в нашем примере их четыре) с суммой поступлений от каждого контрагента. Если бы нам понадобилось помимо статьи ДДС сделать выборку по значению месяца (что было бы актуально, если бы в нашем реестре присутствовали данные не за один месяц, а за несколько), мы могли бы помимо фильтра по графе «Статья ДДС» наложить дополнительный фильтр по графе «Дата оплаты» или «Месяц» и получить данные по конкретному месяцу.

Рисунок 4. Реестр с наложенным на него фильтром

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

Можно добиться еще большего сходства с «1С», реализовав drill-down не ручной настройкой фильтров в реестре, а с помощью специальной кнопки. Для этого следует действовать по следующему алгоритму.

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

2. Создаем макрос DrillDown и вводим в него следующий код:

Sub DrillDown()
Dim R, C As Integer
R = ActiveCell. Column
C = ActiveCell. Row
If (R < 4) Or (R > 15) Or (C < 6) Or (C > 66) Then
MsgBox («Выберите ячейку в табличной части для просмотра ее детализации!»)
Else
Sheets(«Реестр»).Select
ActiveSheet. Range(«$A$3:$G$10000»).AutoFilter Field:=4, _
Criteria1:=Sheets(«ОДДС»).Cells(C, 3).Value
ActiveSheet. Range(«$A$3:$G$10000»).AutoFilter Field:=7, _
Criteria1:=Format(R — 3, «00»)
End If
End Sub

3. Для вызова макроса создаем кнопку «Детализация» (команда меню «Разработчик» — «Вставить» — «Кнопка (элемент управления формы)») и размещаем ее в верхней части ОДДС.

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