|
После прохождения этапа создания бюджетов в полу ручном режиме, специалисты финансового отдела разработали состав компьютерной модели и непосредственно те задачи, которые эта модель должна выполнять.
Состав модели
Компьютерная модель состоит из двух файлов: ДенежныеПотокиБюджет и новый файл, которому присвоено имя СистемаБюджетов, содержащий следующие рабочие листы:
- Пульт — предназначен для управления выполнения моделью заданных операций с помощью кнопок, которые запускают на выполнение макросы открытия файлов, содержащих функциональные бюджеты и макросы управления алгоритмами проведения расчетов (рис. 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. Подпрограмма ФормулыБДДС
Итоги
В этой главе, благодаря разработанной последовательности выполнения алгоритма создаются автоматически бюджет доходов и расходов и бюджет движения денежных средств, консолидируя данные из множества предоставленных функциональных бюджетов.
|
