В статье рассматриваются инструменты Excel для работы с бюджетами, бюджетирование, цифровые инструменты оптимизации бюджетирования в компании.
Ключевые слова: Excel, бюджет, бюджетирование, функция.
Рассмотрим, как использовать Excel при бюджетировании.
Бюджетирование – одна из профильных тем для Excel. Практически во всех компаниях бюджеты ЦФО и консолидированные бюджеты компаний создаются именно в Excel, в виде таблиц различного формата.
Как сделать процесс бюджетирования в Excel более эффективным?
Бюджет представляет собой таблицу, каждая строка которой – определенная статья ДДС, если мы говорим о бюджете движения денежных средств, либо статья доходов или затрат, если мы говорим о бюджете доходов и расходов. Каждый столбец бюджета – календарный месяц планируемого года. Соответственно, на пересечении этих строк и столбцов находятся суммы денежных потоков или доходов/расходов, которые мы планируем осуществить в соответствующем месяце по соответствующей статье.
Обычно процесс бюджетирования осуществляется в Excel вручную, путем пересылки бюджетов ЦФО в финансовую службу и их ручной консолидации в бюджет компании. Соответственно, ни о каком сценарном анализе, ни о какой версионности, ни о каком план-фактном анализе речи не идет, потому что вся работа осуществляется вручную, данные из ячеек в ячейки переносятся вручную, это занимает очень много времени, дает мало информации для работы, и как следствие — это неэффективно.
Как создать эффективную форму бюджета?
Бюджеты ЦФО должны формироваться не путем непосредственного занесения сумм в форму бюджета, а путем создания реестра плановых транзакций, каждая из которых должна характеризоваться следующими реквизитами:
Дата операции.
Сумма операции.
Статья затрат/ДДС.
Наименование контрагента (если необходимо).
Комментарий.
Месяц операции (вычисляется на базе графы «Дата операции» с помощью функции МЕСЯЦ).
То есть, необходимо формировать Реестр операций и на его основе автоматизировать подготовку бюджета.
Для этого на основании реестра плановых транзакций формируется бюджет ЦФО с помощью функции СУММЕСЛИМН():
Функция в данном случае должна выглядеть так (при условии, что реестр плановых транзакций находится на листе «Реестр», а бюджет — на листе «Отдел маркетинга»):
=СУММЕСЛИМН(Реестр!$B:$B;Реестр!$C:$C;'Отдел маркетинга'!$B7;Реестр!$F:$F;'Отдел маркетинга'!C$2)
Здесь:
Реестр!$B:$B — столбец «Сумма», который мы суммируем в части транзакций, которые попали в выборку;
Реестр!$C:$C — столбец с наименованием статей затрат/ДДС;
'Отдел маркетинга'!$B7 — наименование искомой статьи затрат/ДДС в форме бюджета отела маркетинга (обратите внимание, что мы фиксируем название столбца, чтобы можно было копировать эту формулу в ячейках правее);
Реестр!$F:$F — столбец с номером месяца транзакции;
'Отдел маркетинга'!C$2 — номер искомого месяца транзакции в форме бюджета отдела маркетинга (обратите внимание, что мы фиксируем название строки, чтобы можно было копировать эту формулу в ячейки ниже).
Данная формула вставляется во все ячейки бюджета и корректно собирает данные по каждой статье затрат/ДДС и по каждому календарному месяцу. Таким же образом должны быть сформированы бюджеты остальных ЦФО.
Далее рассмотрим, как консолидировать бюджеты в Excel/
Как консолидировать бюджеты в Excel?
Задача формирования сводного бюджета сводится к формированию сводного реестра транзакций (который имеет точно такой же вид, как реестры транзакций всех ЦФО) и дальнейшей сборке сводного бюджета с помощью функции СУММЕСЛИМН(), синтаксис которой аналогичен указанному выше.
Рассмотрим, как можно реализовать возможность нескольких сценариев в Excel.
Рассмотрим, как реализовать возможность формирования бюджета по нескольких сценариям (допустим, оптимистичный, реалистичный и пессимистичный).
1. В реестры плановых транзакций ЦФО вместо одного столбца «Сумма» внести несколько столбцов, каждый из которых будет соответствовать определенному сценарию (их можно так и назвать: «Сумма оптим.», «Сумма реал.» и «Сумма пессим.»).
2. Заполнить реестр данными по всем сценариям.
3. В форме бюджета ЦФО предусмотреть ячейку с выбором конкретного сценария.
Для этого создаем «Сценарий» и для задания значений выбираем меню «Данные»/ «Проверка данных» – выбираем тип данных «Список» и задаем нужные значения – они должны соответствовать исходному источнику.
4. Далее необходимо скорректировать функцию СУММЕСЛИМН() бюджета ЦФО. Это делается следующим образом:
=ЕСЛИ($C$3="пессим.";СУММЕСЛИМН(Реестр!$C:$C;Реестр!$F:$F;'Отдел маркетинга'!$B7; Реестр!$I:$I;'Отдел маркетинга'!C$2);ЕСЛИ($C$3="реал.";СУММЕСЛИМН(Реестр!$D:$D; Реестр!$F:$F;'Отдел маркетинга'!$B7; Реестр!$I:$I;'Отдел маркетинга'!C$2); СУММЕСЛИМН(Реестр!$E:$E;Реестр!$F:$F;'Отдел маркетинга'!$B7;Реестр!$I:$I;'Отдел маркетинга'!C$2)))
Здесь в ячейке С3 указывается конкретный вариант сценария, а значения сумм берутся из столбцов реестра C, D или E реестра в зависимости от выбранного сценария.
Аналогичным образом модифицируются формулы в консолидированном бюджете.
В этой главе рассмотрим, как можно реализовать возможность просмотра различных версий бюджета.
Чтобы реализовать возможность просмотра различных версий бюджета, в исходные данные – в примере это Реестр плановых операций, нужно добавить столбец «Дата занесения» и указать там дату формирования показателей:
Далее, чтобы вносить корректировки, необходимо перейти в конец реестра, указать дату и величину корректировки:
Непосредственно в бюджет ЦФО нужно добавить механизм, который будет отслеживать актуальность бюджета и учитывать корректировки:
Модифицируем формулу СУММЕСЛИМН():
=ЕСЛИ($C$3="пессим.";СУММЕСЛИМН(Реестр!$C:$C;Реестр!$F:$F;'Отдел маркетинга'!$B7; Реестр!$I:$I;'Отдел маркетинга'!C$2; Реестр!$A:$A;”<=”;& 'Отдел маркетинга'!G$3);ЕСЛИ($C$3="реал.";СУММЕСЛИМН(Реестр!$D:$D; Реестр!$F:$F;'Отдел маркетинга'!$B7; Реестр!$I:$I;'Отдел маркетинга'!C$2; Реестр!$A:$A;”<=”;& 'Отдел маркетинга'!G$3); СУММЕСЛИМН(Реестр!$E:$E;Реестр!$F:$F;'Отдел маркетинга'!$B7;Реестр!$I:$I;'Отдел маркетинга'!C$2; Реестр!$A:$A;”<=”;& 'Отдел маркетинга'!G$3)))