Как с нуля автоматизировать бюджетирование в Excel

Анна Кондратенко, специалист по финансам и контроллингу ООО «Урал Лото»

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

Как вводить исходные данные в бюджетную модель

Все плановые данные отражаются на одном листе бюджетной модели – в рабочей таблице (лист Work table, см. таблицу. Структура рабочей таблицы бюджетной модели и рисунок 1. Лист ввода данных).Таблица. Структура рабочей таблицы бюджетной модели

Название столбцаНазначение
Для отчета, для справочникаВспомогательные столбцы, служат для связи таблиц бюджетной модели
ГодУказывается год, на который составляется бюджет
ЦФОЗакрепляется центр финансовой ответственности, то есть отдел, который непосредственно отвечает за соответствующую статью расходов или доходов
Код Д/РВспомогательный столбец, указывает на принадлежность статьи к расходной или доходной частям бюджета
Название бюджетаПоказывает, в какой бюджет попадет сумма из соответствующей строки – доходов и расходов, движения денежных средств, в оба бюджета – или будет задействована при подготовке справочной информации
Статья затрат/дохода, Подстатья-1, Подстатья-2Указываются основные статьи доходов или расходов, а также подстатьи первого и второго уровней (все в соответствии со справочником)
ПримечаниеДополнительная информация о статье (расчет, поставщик товара, услуги и др.)
Количество за год, шт.Приводится итоговое количество единиц товара, работ, услуг за год
Цена, руб.Отражается цена за единицу товара, работы, услуги. Если цена изменяется в течение года, то указывается средняя цена за год
Месяц, квартал, годУказывается стоимость товаров, работ, услуг за указанный в названии период

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

Рисунок 1. Лист ввода данных

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

В какой форме представляются основные финансовые планы компании в модели

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

Бюджеты в детализированной форме. 

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

Лист «detailed PL» позволяет сформировать бюджет доходов и расходов как в целом по компании, так и по отдельному подразделению. Чтобы получить его, достаточно выбрать в окне «Название бюджета» пункты «БДР» и «В оба бюджета». Если необходим план конкретного подразделения, потребуется ввести его название в окне «ЦФО».

Опция «В оба бюджета» предназначена на случай, если цифры одинаковы для обоих бюджетов, то есть, к примеру, когда 10 000 руб. в январе по какой-либо статье идет и в БДР, и в БДДС*. Это сделано, чтобы не вбивать одни и те же цифры два раза.

* Модель разрабатывалась для компании – неплательщика НДС.

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

Бюджеты в привычной форме.

 Бюджет доходов и расходов и бюджет движения денежных средств в целом по компании приведены на листах «PL Budget» и «CF Budget». В отличие от детализированных планов, они формируются не напрямую из рабочей таблицы, а после промежуточной обработки информации на вспомогательных листах («Свод ст.» и «Свод ст.-подст.»).

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

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

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

Бюджет доходов и расходов и бюджет движения денежных средств заполняются данными из вспомогательных таблиц с помощью формулы «ВПР». Например, расчет «Выручки от реализации лотереи по «6 из 40″» за январь (ячейка Е10 на листе «PL Budget») будет задан как ВПР($A10;’Свод ст.-подст.’!$A:$T;4;0), где A10 – ячейка, содержащая наименование подстатьи («6 из 40»), ‘Свод ст.-подст.’!$A:$T – исходный диапазон во второй вспомогательной таблице, где будет происходить поиск строки с тем же наименованием, 4 – номер столбца с данными за январь.

Рисунок 3. Бюджет доходов и расходов
Рисунок 3. Бюджет доходов и расходов


«Как с нуля автоматизировать бюджетирование в Excel». А. Кондратенко
© Материал из ФСС «Система Финансовый директор».
Подробнее: https://www.1fd.ru/#/document/173/459/lis26/?of=copy-72ac924982

Рубрики: Метки: ,

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход /  Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход /  Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход /  Изменить )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.