Автоматизация бюджетирования центров финансовой ответственности

Требования, которым должна удовлетворять создаваемая в Excel система бюджетирования, можно сформулировать следующим образом.

1. Учетная политика, лежащая в основе системы бюджетирования, должна полностью соответствовать учетной политике для целей управленческого учета.

2. Система бюджетирования должна уметь формировать бюджеты центров финансовой ответственности (БДДС и БДР) и консолидировать их в общий бюджет компании.

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

4. Система бюджетирования должна позволять проводить план-фактный анализ.

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

Теперь посмотрим, как вы можете организовать в Excel такую систему бюджетирования, которая будет соответствовать этим требованиям.

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

Рисунок 1. Форма бюджета ЦФО

Далее подробно разберемся, как вся эта конструкция работает, в чем ее особенности и преимущества.

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

Альтернативный подход, которым мы и воспользуемся, предполагает, что бюджеты ЦФО формируются не напрямую занесением плановых показателей в форму бюджета, а с помощью реестра плановых операций. То есть он будет заполняться значениями посредством функции СУММЕСЛИМН на основании реестра плановых операций аналогично тому, как формировалась управленческая отчетность на основании реестра хозяйственных операций в первой главе.

Добавим в Excel лист «Реестр» и заполним его так, как показано на рис. 2. Прокомментируем структуру реестра плановых операций.

Рисунок 2. Форма реестра плановых операций бюджета ЦФО

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

Плановые транзакции занесли в реестр 25.12.2014. Примерно в середине января какие-то показатели бюджета изменились, как следствие возникла необходимость в их корректировке. Не трогая плановые операции, которые были занесены в реестр ранее, мы добавляем в него новые строки и проставляем по ним текущую дату (15.01.2015), при этом указывая не новое значение статьи бюджета, а разницу между старым и новым значением.

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

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

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

Графы «Пессим.», «Реалист.» и «Оптимист.» предназначены для занесения суммы операции по пессимистичному, реалистичному и оптимистичному сценариям. Если в вашей компании сценарии называются по-другому или их количество отличается, в реестр надо внести соответствующие изменения.

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

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

Графа «Комментарии» содержит дополнительную справочную информацию о транзакции и нужна только для прояснения отдельных непонятных моментов.

Графа «Месяц операции», как уже говорилось выше, является служебной, она извлекает месяц транзакции из графы «Дата операции» для того, чтобы отнести данную транзакцию в правильный столбец месяца на листе «Бюджет».

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

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

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

=ЕСЛИ(C3="Пессимистичный";"$C:$C";ЕСЛИ(C3="Реалистичный";
"$D:$D";"$E:$E"))

Итак, мы подошли к самому сложному этапу: составлению формул. С их помощью в таблицу бюджета будут попадать данные из реестра, соответствующие выбранному сценарию и версии бюджета компании.

Для первой статьи бюджета, «Участие в выставках», и первого столбца (январь) формула выглядит так:

=ЕСЛИ(ЕПУСТО($F$3);СУММЕСЛИМН(ДВССЫЛ("Реестр!"&$K$3);

Реестр!$F:$F;$C7;
Реестр!$I:$I;D$4);СУММЕСЛИМН(ДВССЫЛ("Реестр!"&$K$3);

Реестр!$F:$F;$C7;
Реестр!$I:$I;D$4;Реестр!$A:$A;"<="&Версия))*ЕСЛИ($A7="-";-1;1)

Разберем эту формулу подробно.

Проверка =ЕСЛИ(ЕПУСТО($F$3) предназначена для того, чтобы корректно обработать ситуацию, при которой поле «Версия» в шапке бюджета будет пустым. То есть конкретная версия бюджета там не будет указана.

В этом случае, очевидно, мы должны просуммировать все транзакции реестра по следующим критериям:

• столбец суммирования соответствует сценарию (аргумент ДВССЫЛ("Реестр!"&$K$3));
• статья ДДС соответствует текущей строке бюджета (аргументы Реестр!$F:$F;$C7);
• месяц плановых транзакций соответствует текущему столбцу бюджета (аргументы Реестр!$I:$I;D$4).

Если же поле «Версия», которое расположено в шапке бюджета, уже заполнено значением, необходимо будет провести дополнительную проверку транзакций и включать в суммирование только те из них, даты которых меньше или равны дате, указанной в поле «Версия». Именно такую проверку осуществляют аргументы Реестр!$A:$A;"<="&Версия. Обратите внимание на то, что слово «Версия» в данной формуле — это имя ячейки, которая содержит дату текущей версии бюджета.

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

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

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

Покажем это на примере. Занесем в реестр плановых операций две записи, которые наглядно проиллюстрируют использование и сценариев, и версий бюджетов (см. рис. 3). Первая запись описывает датированное 25.12.2014 намерение руководителя ЦФО принять участие в выставке СТТ. Для этого в январе необходимо будет потратить по пессимистичному сценарию 50 тыс. рублей, по реалистичному — 60 тыс. рублей, а по оптимистичному — 70 тыс. рублей. Расходные операции заносятся в реестр со знаком минус, как и в управленческом учете.

Рисунок 3. Плановые операции бюджета ЦФО

Поставщик 15.01.2015 уведомил отдел маркетинга, что цены на участие в выставке выросли, в результате чего возникла необходимость скорректировать расходы по данной статье. Новая сумма затрат — 60 тыс. рублей по пессимистичному сценарию, 75 тыс. рублей — по реалистичному, 90 тыс. рублей — по оптимистичному. Чтобы отразить эти изменения в бюджете, руководитель отдела маркетинга занес в реестр новую запись, датированную 15.01.2015, в которой отразил разницу между старой и новой суммой затрат на участие в выставке по каждому из сценариев: 10 тыс. рублей, 15 тыс. рублей и 20 тыс. рублей соответственно. Поля «Статья ДДС» и «Наименование контрагента» оставлены прежними, в поле «Комментарий» можно было бы внести описание причин изменения сметы, но руководитель этого не сделал, просто скопировав комментарий из предыдущей записи.

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

Рисунок 4. Отражение плановой операции в бюджете ЦФО

В статье «Участие в выставках» по столбцу января отражено значение, равное 50 тыс. рублей. При этом выбран пессимистичный бюджет, версия на 01.01.2015. И система извлекла именно то значение расхода, которое соответствует данной комбинации сценария и версии бюджета.

Рисунок 5. Бюджет ЦФО по оптимистичному сценарию

Переключимся на оптимистичный сценарий, выбрав соответствующее значение из выпадающего списка в поле «Сценарий». Форма бюджета примет такой вид, как показано на рис. 5 на стр. 81. Значение по статье «Участие в выставках» автоматически изменится на 70 тыс. рублей. Теперь изменим версию бюджета с 01.01.2015 на более позднюю, допустим, на 01.02.2015 (см. рис. 6).

Рисунок 6. Переключение на новую версию бюджета ЦФО

Таким образом, все сценарии и все версии бюджета ЦФО в любых их комбинациях доступны для просмотра сразу после заполнения реестра плановых операций.

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

• руководитель ЦФО всегда имел к нему доступ;
• другие сотрудники такого доступа не имели (так как бюджет ЦФО может содержать коммерческую тайну);
• данный файл был доступен финансовой службе, причeм как для чтения, так и для записи, поскольку информация из него будет перекачиваться в консолидированный бюджет, а фактические данные будут загружаться в него из файла управленческого учeта.

Наиболее простой вариант — сохранить файл центра финансовой ответственности на корпоративном сервере компании, прописав права доступа к соответствующей папке, либо на одном из облачных сервисов (например, DropBox, OneDrive и т. д.).