Как составить бдр в excel

Автоматизация и моделирование бизнес-процессов в Excel

Глава 24
Бюджетирование Создание модели формирования БДДС и БДР

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

Состав модели

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

  • Пульт — предназначен для управления выполнения моделью заданных операций с помощью кнопок, которые запускают на выполнение макросы открытия файлов, содержащих функциональные бюджеты и макросы управления алгоритмами проведения расчетов (рис. 24.1.);
  • БДДС — предназначен для формирования бюджета движения денежных потоков (рис. 20.18.);
  • БДР — предназначен для формирования бюджета доходов и расходов (рис. 20.17.);
  • Прогноз — предназначен для распределения движения денежных потоков на фиксированные платежи и усредненные платежи в течение месяца (рис. 25.10.);
  • РаспределенныеПлатежи — предназначен для автоматического формирования таблицы денежных потоков, к которым можно применить некоторые правила закономерности поступления или списания денежных средств (рис. 25.18.);
  • ФиксПлатежи — предназначен для полуавтоматического ввода записей о поступлении или списании денежных средств, когда известны сроки и суммы такого движения денежных средств (рис. 25.2.);
  • ПереченьБюджетов — лист, содержащий информацию о названии функционального бюджета, дате и времени его формирования, и подразделении, его предоставившем (рис. 24.7.);
  • ПромРасчеты — рабочий лист, предназначенный для консолидации данных функциональных бюджетов (рис. 24.9.).

Задача модели

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

Рабочий лист Пульт

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

Рис. 24.1. Рабочий лист Пульт с кнопками управления работой модели

Для записи макроса открытия файлов выполните следующие действия:

  • после начала записи макроса, для открытия файла: нажмите на кнопку Открыть, выполните комбинацию клавиш Ctrl+F12 или выполните команду Файл/Открыть;
  • в появившемся диалоговом окне Открытие документа, откройте папку Самоучитель на диске D и выберите нужную папку, в которой выберите файл, для открытия которого записываете макрос, после чего нажмите на кнопку Открыть;
  • остановите запись макроса и отредактируйте код VBA.

Рис. 24.2. Макросы открытия файлов с функциональными бюджетами

На рабочем листе Пульт справа (рис. 24.1.) показаны четыре кнопки, предназначенные:

  • Формирование бюджетов — для формирования БДДС и БДР на одноименных рабочих листах;
  • Создание таблицы распределенных платежей — для заполнения таблицы на рабочем листе РаспределенныеПлатежи;
  • Перенос данных в платежный календарь — для переноса фиксированных и распределенных денежных потоков в файл ДенежныеПотокиБюджет;
  • Создание платежного календаря — для формирования платежного календаря планируемого бюджета.

В ячейку F2 вводится дата первого дня планируемого периода, а в ячейку F1- сумма остатка денежных средств на эту дату. В ячейку J1 введена формула ТДАТА, показывающая текущее время и дату.

Формирование бюджетов

Для формирования бюджета движения денежных средств (БДДР) и бюджета доходов и расходов (БДР) предназначена кнопка Формирование бюджетов, которой назначена подпрограмма ФормированиеБюджетов (рис. 24.3.), состоящая из трех процедур:

  • СборДанныхБюджетов;
  • ФормулыБДР;
  • ФормулыБДДС.

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

Рис. 24.3. Подпрограмма ФормированиеБюджетов

Подпрограмма для консолидации данных всех функциональных бюджетов

Подпрограмма СборДанныхБюджетов (рис. 24.4.) производит очистку столбцов А:С рабочих листов ПромРасчеты (рис. 24.9.) и ПереченьБюджетов (рис. 24.7.), после чего переходит к последовательному выполнению процедур открытия файлов с функциональными бюджетами, из которых с помощью процедуры ОбработкаДанных переносит данные в файл СистемаБюджетов.

Рис. 24.4. Подпрограмма СборДанныхБюджетов

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

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

При записи макроса для перехода от файла СистемаБюджетов к файлу функциональным бюджетом, используйте комбинацию клавиш Ctrl+Tab, выполнение которой позволяет переместиться в следующее окно Excel. Подобная методика может найти свое объяснение в упрощении записи выполнения макросами операций — нет необходимости перечислять имена всех открываемых файлов с функциональными бюджетами. При выполнении комбинации клавиш Ctrl+Tab макрос запишет код VBA открытия следующего окна:

ActiveWindow.ActivateNext

Вся запись макроса может быть заключена в трех действиях — после начала записи макроса при открытом файле СистемаБюджетов и, например, новой книги, нажмите два раза комбинацию клавиш Ctrl+Tab, после чего закройте новую книгу без сохранения и остановите запись макроса. После этого откройте Редактор Visual Basic, отредактируйте код и вставьте имена входящих в него процедур.

Рис. 24.5. Макрос ОбработкаДанных

Макрос ВставкаПараметровБюджета

Подпрограмма ВставкаПараметровБюджета (рис. 24.6.) копирует в открытом файле с функциональным бюджетом содержимое диапазона ячеек А1:С1 и перейдя на рабочий лист ПереченьБюджетов (рис. 24.7.) файла СистемаБюджетов находит первую свободную строку и производит вставку скопированной области из буфера обмена. Таким образом на рабочем листе ПереченьБюджетов будет сформирован весь перечень функциональных бюджетов, на основании которых будут составлены основные бюджеты БДДС и БДР.

Рис. 24.6 . Подпрограмма ВставкаПараметровБюджета

Рис. 24.7. Рабочий лист ПереченьБюджетов с перечнем функциональных бюджетов, данные которых консолидировались

Подпрограмма СборБюджетов

Задача подпрограммы СборБюджетов — консолидировать данные, которые введены во все функциональные бюджеты. Для этого на рабочем листе файла с функциональным бюджетом выделяется ранее оговоренная в регламенте формирования этого электронного документа, область таблицы с функциональным бюджетом (в нашем примере С5:Е54) и копируется в буфер обмена. После перехода на рабочий лист ПромРасчеты (рис. 24.8.) файла СистемаБюджетов в столбце А определяется первая свободная строка и осуществляется вставка содержимого скопированного диапазона, но в виде значений, используя для этого диалоговое окно Специальная вставка. Затем осуществляется сортировка созданного списка по убыванию по столбцу В, в котором находится текст кодов управленческого учета.

Рис. 24.8. Подпрограмма СборБюджетов

Рис. 24.9. Рабочий лист ПромРасчеты с фрагментом собранных данных функциональных бюджетов

Формирование бюджета доходов и расходов

Задачей подпрограммы ФормулыБДР является формирование бюджета доходов и расходов на рабочем листе БДР. Для создания подпрограммы используются два записанных макроса, для чего соблюдайте следующую последовательность записи:

  • перед записью первого макроса, введите в диапазон ячеек F9:F69 рабочего листа БДР формулы суммирования данных из рабочего листа ПромРасчеты по признакам текста кода управленческого учета, предварительно введенных ранее в рабочий лист БДР. Формула в ячейке F9:
  • =СУММЕСЛИ(ПромРасчеты!$B:$B;$E9;ПромРасчеты!$C:$C)
  • запись макроса заключается в выделении диапазона ячеек F9:F69 и нажатии клавишу F2 и затем комбинации клавиш Ctrl+Enter;
  • остановите запись макроса и введите формулы суммирования итоговых значений по статьям БДР;
  • проверьте правильность вычислений, после чего запустите на запись второй макрос, который заключается во вводе созданных итоговых формул. Для этого передвигаясь по столбцу F сверху вниз при выделении ячеек, содержащих формулы, нажмите на клавишу F2 и затем Enter;
  • последний этап — выделение столбца F, копирование его содержимого в буфер обмена и последующая вставка в ту же область, но как значений, используя для этого диалоговое окно Специальная вставка;
  • заключительный этап — соединение текста двух макроса в единое целое используя для этого операции копирования и вставки в Редакторе Visual Basic.

Рис. 24.10. Подпрограмма ФормулыБДР

Подпрограмма ФормулыБДДС

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

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

=ЕСЛИ(СЧЕТЕСЛИ(Пульт!$S$2:$U$10;E9)=1;СУММЕСЛИ(ПромРасчеты!$B:$B;$E9;ПромРасчеты!$C:$C)*120%;СУММЕСЛИ(ПромРасчеты!$B:$B;$E9;ПромРасчеты!$C:$C))

которая вначале анализирует — может ли эта статья поступления или расхода денежных средств содержать дополнительно сумму НДС, что не включалось ранее ни в функциональные бюджеты, ни в БДР. Для этого с помощью функции СЧЕТЕСЛИ производится поиск этой статьи кода в списке на листе Пульт, и если эта статья обнаружена, то тогда при сравнении со значением 1 в первом аргументе функции ЕСЛИ возвращается значение ИСТИНА. Тогда определенная сумма функционального бюджета во втором аргументе функции ЕСЛИ умножается на заданный коэффициент 120%. В противном случае вычисленная сумма функциональных бюджетов рассчитывается в третьем аргументе функции ЕСЛИ и остается без изменений.

Рис. 24.11. Список статей бюджета денежных средств, которые содержат НДС

Рис. 24.12. Подпрограмма ФормулыБДДС

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

  • Оглавление
  • Следующая глава
  • Бюджетирование на предприятии на примере

    147 591 просмотров

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

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

    Положение о бюджетировании на предприятии может содержать следующие разделы:

    • Стратегические цели и задачи предприятия;
    • Бюджетная модель;
    • Финансовая структура компании и т. д.

    На основании Положения о бюджетировании в компании необходимо разработать Регламент бюджетирования на предприятии, который может содержать следующие разделы:

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

    Есть несколько путей реализации готовой бюджетной модели. Самыми распространенными и относительно дешевыми способами являются:

    • Бюджетирование в Excel
    • Бюджетирование в программах на платформе 1С

    Бюджет компании пример Excel

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

    Ниже приведен Бюджет доходов и расходов (пример составления в эксель) и пример Бюджета движения денежных средств.

    Бюджет доходов и расходов предприятия образец Excel

    Рисунок 1. Бюджет доходов и расходов предприятия образец Excel.

    Бюджет движения денежных средств пример в excel

    Рисунок 2. Бюджет движения денежных средств пример в Excel.

    Составление БДР и БДДС пример в Excel

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

    БДДС пример в Excel (Бюджет движения денежных средств пример в Excel)

    Рисунок 3. БДДС пример в Excel (Бюджет движения денежных средств пример в Excel).

    БДР пример в Excel (Бюджет доходов и расходов пример составления в Excel)

    Рисунок 4. БДР пример в Excel (Бюджет доходов и расходов пример составления в Excel).

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

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

    Бюджетирование в программах на платформе 1С

    Автоматизация бюджетирования и управленческого учета на базе 1С, например, в системе «WA: Финансист», делает процесс бюджетирования на предприятии более эффективным по сравнению с бюджетированием в Excel.

    Подсистема бюджетирования «WA: Финансист» включает в себя возможности формирования и контроля операционных и мастер бюджетов.

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

    Данная система позволяет эффективно строить бизнес-процесс бюджетирования на всех его этапах:

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

    Интерфейс «WA: Финансист: Бюджетирование». Раздел Бюджетирование

    Рисунок 5. Интерфейс «WA: Финансист: Бюджетирование». Раздел Бюджетирование.

    WA: Финансист «Бюджетирование» включает следующие бизнес-процессы:

    • Моделирование – разработка бюджетной модели;
    • Основной бюджетный процесс – регистрация плановых показателей подразделениями. Утверждение бюджетов. Корректировка планов и согласование корректировок;
    • Подсистема взаимодействия с источниками данных – настройка получения данных из внешних источников (как частный случай—обращение к данным системы).
    • Отчеты системы – набор аналитических отчетов.

    Дашборд руководителя в системе «WA: Финансист»

    Дашборд руководителя в системе «WA: Финансист».

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

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

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

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

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

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

    Программный модуль Бюджетирование решает все основные задачи и проблемы, писанные в статье «Бюджетирование на предприятии на примере».
    Оцените качество и удобство использования системы "WA: Финансист".

    Ссылка на основную публикацию