Формирование консолидированного бюджета

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

1. Объединить реестры плановых операций всех ЦФО в сводном реестре компании.

2. На основании сводного реестра сформировать сводный бюджет компании.

В новом файле Excel создадим три листа, которые назовем «Бюджет», «Реестр» и «Бюджеты ЦФО». На листе «Бюджеты ЦФО» укажем полные пути файлов всех центров финансовой ответственности компании. Эта информация нужна для того, чтобы система знала, где брать данные для сводного реестра. Выглядеть это может так, как показано на рис. 1.

Рисунок 1. Список бюджетов ЦФО с файлами, в которых они хранятся

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

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

Рисунок 2. Сводный реестр

Последний лист, «Бюджет», будет собирать консолидированный бюджет на основании данных реестра по тому же принципу, что и бюджеты ЦФО (см. рис. 3). Формулы, с помощью которых формируется данный бюджет, полностью аналогичны формулам в бюджете ЦФО.

Рисунок 3. Сводный бюджет

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

Sub UpdateRegistry()

Dim Count, LinesFrom, LinesTo As Integer
Dim WB As Workbook
Dim SheetFrom, SheetTo As Worksheet
Dim R As Range

Set SheetTo = ThisWorkbook.Sheets("Реестр")
SheetTo.Range("A4:Z100000").ClearContents

Count = 2
LinesTo = 4

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Do While ThisWorkbook.Sheets("Бюджеты").Cells(Count, 1) <> ""
Set WB = Workbooks.Open(Filename:=Worksheets
("Бюджеты ЦФО").Cells(Count, 2))
Set SheetFrom = WB.Sheets("Реестр")
LinesFrom = 4

Do While SheetFrom.Cells(LinesFrom, 1) <> ""
SheetTo.Range(SheetTo.Cells(LinesTo, 1), SheetTo.Cells(LinesTo, 9)).Value = SheetFrom.Range(SheetFrom.Cells(LinesFrom, 1), SheetFrom.Cells(LinesFrom, 9)).Value
SheetTo.Cells(LinesTo, 10).Value = ThisWorkbook.Sheets("Бюджеты").Cells(Count, 1).Value
LinesFrom = LinesFrom + 1
LinesTo = LinesTo + 1
Loop

WB.Close savechanges:=False
Count = Count + 1
Loop

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox ("Плановые транзакции успешно загружены из файлов бюджетов ЦФО.")

End Sub

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

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

Интеграция бюджетирования с управленческим учетом и план-фактный анализ

Настало время интегрировать систему бюджетирования компании с системой управленческого учета, которую разработали ранее. Данная задача серьезно упрощается благодаря тому, что обе эти системы реализованы в Excel, на основе реестров операций и учетных политик с одинаковыми правилами отражения хозяйственных операций в учетных регистрах, структуры транзакций и форм отчетов. Все, что нужно сделать, – объединить листы Excel и макросы, которые относятся к обеим системам, в одном файле Excel. В результате этих действий получим такой файл, в котором, помимо всего прочего, присутствуют два реестра: фактических транзакций и плановых транзакций. Причем указанные реестры можно сравнивать между собой, реализуя таким образом план-фактный анализ исполнения бюджета. Рассмотрим, как реализовать этот процесс, более детально.

План-фактный анализ данных реализуется через отчет, в котором одновременно представлены как плановые значения из подсистемы бюджетирования компании, так и фактические показатели из подсистемы управленческого учета. Также в отчете присутствуют отклонения между ними. Подобный отчет чаще всего имеет такой вид, который представлен на рис. 1.

Рисунок 1. План-фактный анализ

Чтобы составить этот отчет, необходимо действовать следующим образом.

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

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

3. Основная часть отчета заполняется формулами:

столбцы «План» – формулами, которые извлекают данные из планового реестра:

=СУММЕСЛИМН(ДВССЫЛ("'Реестр план'!"&$E$3);
'Реестр план'!$F:$F; $C9;'Реестр план'!$I:$I;D$5)*ЕСЛИ
($A9="-";-1;1)

столбцы «Факт» – формулами, извлекающими данные из фактического реестра:

=СУММЕСЛИМН('Реестр факт'!$C:$C;
'Реестр факт'!$J:$J;E$5;'Реестр факт'!$E:$E;$C9;
'Реестр факт'!$I:$I;"факт")*ЕСЛИ($A9="-";-1;1)

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

=ЕСЛИ(D9=0;0;(E9-D9)/D9)

Отмечу, что в данной формуле производится дополнительная проверка того, что плановое значение не равно нулю. В противном случае возникнет ошибка деления на ноль.

Для большей наглядности в ячейках столбцов «Отклонения» применен такой удобный инструмент Excel, как условное форматирование:

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

Реализовать такую возможность в отчете необходимо следующим образом.

1. Выделить все столбцы с отклонениями.

2. Вызвать команду меню «Главная» – «Условное форматирование» – «Управление правилами», и откроется окно Диспетчера правил условного форматирования.

3. В окне Диспетчера нажать кнопку «Создать правило».

4. В поле «Стиль формата» открывшегося окна «Создание правила форматирования» выбрать вариант «Наборы значков», в поле «Стиль значка» – треугольные стрелки вверх и вниз, после чего прописать правила применения различных значков так, как показано на рис. 2.

Рисунок 2. Настройка правил форматирования

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

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

Чтобы иметь возможность выгружать фактические данные в бюджеты ЦФО, необходимы:

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

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

Рисунок 3. Реестр фактических операций с графой «ЦФО»

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

Непосредственную выгрузку фактических данных в бюджеты центров финансовой ответственности реализуем с помощью следующего макроса:

Sub SaveFact()

Dim Count, LinesFrom, LinesTo As Integer
Dim WB As Workbook
Dim BudgetsSheet, SheetFrom, SheetTo As Worksheet

Set BudgetsSheet = ThisWorkbook.Sheets("Бюджеты ЦФО")
Set SheetFrom = ThisWorkbook.Sheets("Реестр факт")
Count = 2

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Do While BudgetsSheet.Cells(Count, 1) <> ""
Set WB = Workbooks.Open(Filename:=BudgetsSheet.Cells(Count, 2))
Set SheetTo = WB.Sheets("Факт")
SheetTo.Range("A4:L10000").ClearContents

LinesFrom = 4
LinesTo = 4

Do While SheetFrom.Cells(LinesFrom, 5).Value <> ""
If SheetFrom.Cells(LinesFrom, 8).Value = BudgetsSheet.Cells(Count, 1).Value Then SheetTo.Range(SheetTo.Cells(LinesTo, 1), SheetTo.Cells(LinesTo, 12)).Value = SheetFrom.Range(SheetFrom.Cells(LinesFrom, 1), SheetFrom.Cells(LinesFrom, 12)).Value
LinesTo = LinesTo + 1
End If
LinesFrom = LinesFrom + 1
Loop

WB.Close savechanges:=True
Count = Count + 1
Loop

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

MsgBox ("Фактические транзакции успешно выгружены в файлы бюджетов ЦФО.")

End Sub

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

Автоматизация казначейства

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

Допустим, транзакции в реестре хозяйственных операций могут иметь три статуса:

• «зарег.» — транзакции, которые планируются, но еще не утверждены;
• «план» — для утвержденных транзакций, которые будут осуществлены в установленную дату;
• «факт» — для выполненных транзакций.

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

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

1. Добавить в реестр столбец «Перерасход».

2. Заполнить этот столбец для всех транзакций реестра формулой:

=ЕСЛИ(I4<>"план";"";ЕСЛИ((ABS(СУММЕСЛИМН($C:$C;$J:$J;J4;
$E:$E;E4;$I:$I;"факт"))+ABS(C4))>ABS(СУММЕСЛИМН(ДВССЫЛ
("'Реестр план'!"&'План-факт'!$E$3);'Реестр план'!$F:$F;E4;
'Реестр план'!$I:$I;J4));"перерасход";"ок"))

Эта формула действует следующим образом.

Если статус текущей транзакции не равен «план», формула возвращает пустое значение.

Если статус текущей транзакции равен «план», формула суммирует все транзакции текущего месяца, в которых статья ДДС равна статье ДДС текущей транзакции и месяц операции равен месяцу операции текущей транзакции. Например, если данная формула стоит в строке, в которой статья ДДС равна «Реклама и PR» и месяц транзакции — сентябрь 2015 года, то формула будет суммировать все фактически осуществленные сентябрьские расходы по статье «Реклама и PR». Далее формула суммирует плановые транзакции (в реестре плановых транзакций, который находится на листе «Реестр план»), которые соответствуют месяцу и статье ДДС фактической транзакции.

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

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

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

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

2. Вызвать команду меню «Главная» – «Условное форматирование» – «Управление правилами».

3. Создать два правила: для значения «ок» и для значения «перерасход» (см. рис. 1).

Рисунок 1. Правила условного форматирования реестра хозяйственных операций

4. В обоих случаях проверяем значение поля «Перерасход» для ячейки $M4 (это ячейка первой транзакции реестра), причем имя столбца фиксируется знаком $, а имя ячейки – нет. В качестве диапазона, к которому применяется условное форматирование, задается весь реестр хозяйственных операций, включая пока еще пустые его строки (рекомендую задавать диапазон с большим запасом, чтобы его хватило даже на большее количество транзакций, чем предполагалось изначально).

Покажу работу данного механизма на примере. В реестре зарегистрирована транзакция на оплату канцелярских товаров в размере 20 тыс. рублей (см. рис. 2).

Рисунок 2. Плановая транзакция в реестре

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

Рисунок 3. Плановая транзакция сверх бюджета

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

Рисунок 4. Плановая транзакция в рамках бюджета