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

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

Перенос планируемого потока ДДС в модель прогнозирования движения денежных потоков

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

Открытие файла прогнозирования денежных потоков

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

Задача подпрограммы ПереносДанныхДенежныеПотоки — сравнить текущее время и дату в ячейках:

• J1 листа Пульт книги СистемаБюджетов;
• С1 листа ПК книги ДенежныеПотокиБюджет

и в зависимости от того идентичны ли эти значения определить открыт ли файл ДенежныеПотокиБюджет. С этой целью в ячейку L1 листа Пульт вводится ссылка на ячейку С1 листа ПК, после чего сравниваются их текущие значения, округленные до 4-го знака после запятой (секунды). Округление играет роль в случае, если в Excel будет открыто несколько файлов, то возможно что при сравнение двух значений по функции определения текущего времени даст расхождение в миллионные доли секунды, и тогда Excel воспримет это как неравенство. Если значения равны, следовательно файл ДенежныеПотокиБюджет открыт, в противном случае закрыт.

Рис. 26.1. Подпрограмма ПереносДанныхДенежныеПотоки

Основным элементом подпрограммы ПереносДанныхДенежныеПотоки является инструкция If-Then-Else, которая применяется для изменения хода выполнения подпрограммы в зависимости от результатов проверки условия.

Упрощенный синтаксис инструкции:

If условие Then
[инструкции]
Else
[инструкции_else]
End If
Элементы инструкции If-Then-Else в нашем примере:
• If — ключевое слово обозначающее начало инструкции;
• условие — проверяемое условие равенства значений текущего времени, возвращаемых функцией ТДАТА в двух файлах;
• Then — ключевое слово обозначающее конец проверки условия;
• инструкции — при выполнении условия идентичности текущего времени в двух файлах подпрограмма осуществляет переход к файлу ДенежныеПотокиБюджет и затем к выполнению подпрограммы ПереносДанных;
• Else — ключевое слово, обозначающее конец участка, выполняемого при выполнении условия, или начало участка, выполняемого при несоответствии условия;
• инструкции — активизация пользовательского диалогового окна (рис. 26.5.) с вопросом об открытии файла. При нажатии на кнопку Да выполнение подпрограммы переходит к процедуре ОткрытиеФайла, на кнопку Нет — завершение выполнения подпрограммы;
• End If — ключевое слово обозначающее конец инструкции.

Перенос данных в файл прогнозирования денежных потоков

Если файл ДенежныеПотокиБюджет закрыт, то при нажатии на кнопку Да пользовательского диалогового окна (рис. 26.5.) выполнение процедуры переходит к выполнению макроса ОткрытиеФайла (рис. 26.2.), для записи которого выполните команду Файл/Открыть и откройте файл. В конце макроса введите имя подпрограммы ПереносДанных.

Задача подпрограммы ПереносДанных (рис. 26.2.) — очистка рабочей области листа ИсхДанные файла ДенежныеПотокиБюджет и перенос остатка денежных средств на начало планируемого периода. Выполнение всех остальных действий подпрограмма поручает другим процедурам.

Рис. 26.2. Подпрограммы ОткрытиеФайла и ПереносДанных

Подпрограмма переноса даты, сумм и назначения денежных потоков

Подпрограммы ФиксированныеПлатежи и РаспределенныеПлатежи одинаковы (рис. 26.3.). Они выполняются при активном рабочем листе ИсхДанные и их задачей является последовательное копирование областей дат, наименований статей управленческого учета и сумм на рабочих листах с этой информацией и вставка в области, отведенные для этого. Обратите внимание на две детали:

• предполагается что количество записей по каждому виду платежей не превышает 500. Тогда для области фиксированных платежей выделен диапазон строк от 10-й до 509-й, а для распределенных платежей от 510-й до 10009-й. При необходимости эти области можно изменить — увеличить или уменьшить. Можете ввести поиск первой пустой строки с последующей вставкой скопированных диапазонов. Это не принципиально. Основная задача перенести данные за минимально короткое время и не внеся при этом ошибок. То есть важен результат;
• ранее, во всех предыдущих главах, копирование ячеек (диапазонов) проводилось в пределах одной рабочей книги. Здесь же применено копирование диапазонов из другой рабочей книги, поэтому указан полный путь нахождения этих диапазонов соблюдая иерархию: имя книги, имя листа, адрес диапазона ячеек.

Рис. 26.3. Подпрограммы переноса платежей в модель прогнозирования денежных потоков

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

Для ввода признака денежных потоков и наименования валюты, запишите подпрограмму ФормулыВводаБюджета (рис. 26.4.), задачей которой является ввод формул определяющих — находится ли в этой строке в столбце С текст наименования статьи управленческого учета и если да, то ввести в ячейки столбца Н единицу — признак денежного потока, а в столбец К текст RUR — наименование валюты.

Рис. 26.4. Подпрограммы ФормулыВводаБюджета и Вопрос

Перед записью макроса введите в ячейку Н10 формулу:

=ЕСЛИ(C10=0;0;"RUR")

и в ячейку К10:

=ЕСЛИ(C10=0;0;1)

после чего запишите макрос ввода этих формул в диапазон ячеек Н10:Н1010 и К10:К1010 и последующей замены формул на вычисленные значения.

Создание диалоговых окон для выбора направления выполнения подпрограммы

Подпрограмма Вопрос (рис. 26.4.) предназначена для создания пользовательского диалогового окна с вопросом — сформировать ли платежный календарь (рис. 26.6.). В главе 21 было рассмотрено создание однокнопочного диалогового окна на основании функции MsgBox, которое дополнительно отображало время выполнения подпрограммы (рис. 21.11.). Рассмотрим создание других пользовательских диалоговых окон на основании этой же функции.

Подпрограмма ПереносДанныхДенежныеПотоки формирует двухкнопочное пользовательское диалоговое окно (рис. 26.5.) с вопросом: Открывать файл ДенежныеПотокиБюджет?. Это может быть удобно, если пользователь передумает открывать файл и тогда нажмет на кнопку Нет.

Задача подпрограммы Вопрос — создание двухкнопочного пользовательского диалогового окна (рис. 26.6.) с вопросом Создать платежный календарь?.

При нажатии на кнопку Да процедура переходит к выполнению подпрограммы СозданиеПлатежногоКалендаря (рассмотрен ниже). При нажатии на кнопку Нет подпрограмма ПереносДанныхДенежныеПотоки завершает свою работу.

Рис. 26.5. Пользовательское диалоговое окно с вопросом об открытии файла ДенежныеПотокиБюджет

Рис. 26.6. Пользовательское диалоговое окно с вопросом о создании платежного календаря

Для отображения в диалоговом окне двух кнопок в отличие от однокнопочного окна (рис. 21.11.), во втором аргументе функции MsgBox (buttons) указано цифра 4 вместо 0. Если необходимо будет создавать другие диалоговые окна, то в этом поможет таблица с перечнем аргументов для различного отображения режимов кнопок.

Таблица 26.1. Допустимые значения аргумента buttons:

Первая группа значений (0-5) указывает число и тип кнопок, отображаемых в окне диалога, вторая группа (16, 32, 48, 64) задает тип используемого значка, третья (0, 256, 512) определяет кнопку, которая является основной.

Просмотр данных

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

Реализуйте второй вариант, написав подпрограмму, которая будет задавать и выполнять последовательность необходимых операций.

Создание платежного календаря

Последовательность выполнения расчетов

Чтобы в после переноса платежей из файла СистемаБюджетов в файл ДенежныеПотокиБюджет не было необходимости задумываться какие операции нужно выполнять для получения результатов, запишите подпрограмму СозданиеПлатежногоКалендаря (рис. 26.7.) в файле СистемаБюджетов. Задача этой подпрограммы заключается в последовательном запуске на выполнение процедур файла ДенежныеПотокиБюджет для получения отчетов, а в частности:

• отчета о суммарных оборотов на листе СуммПотоки;
• отчета о параметрах кредитной линии при нехватке денежных средств;
• платежного календаря

В начале подпрограммы запускается таймер и по окончанию всплывает пользовательское диалоговое окно с указанием времени выполнения программы. Эти строки кода VBA создаются копированием из подпрограммы ПолныйРасчетДенежныхПотоков (рис. 21.10.) первой и двух последних строк с последующим редактированием последней строки. Обратите внимание — в строке функции MsgBox указано числовое значение второго аргумента (buttons) равное 48. В этом случае пользовательское диалоговое окно появляется с предупреждающим значком .

Рис. 26.7. Подпрограмма подготовки отчетов по прогнозируемым денежным потокам

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

• перейдите на листе ИсхДанные и нажмите кнопку Полный расчет листа ИсхДанные, что запустит на выполнение макрос ПолныйРасчетИсхДанные для перерасчета таблицы на этом листе;
• перейдите на лист СуммПотоки и нажмите на кнопку Денежные потоки ЭКВИВАЛЕНТ для запуска Подпрограммы ДенежныеПотокиБюджет. В результате выполнения макроса на рабочем листе СуммПотоки будет создана таблица ежедневных оборотов и остатков денежных средств (рис. 26.8). Остаток денежных средств по состоянию на 1 августа (конец планируемого периода) в созданной таблице меньше остатка по БДДС на 8 копеек. Это расхождение получено в результате округлений при проведении вычислений;
• перейдите на лист Кредит и с помощью кнопки Параметры кредитной линии запустите на выполнение подпрограмму ПараметрыДополнительногоКредита. На листе Кредит будет создана таблица (рис. 26.9.), показывающая возможную ежедневно изменяющуюся нехватку денежных средств и возможность их ежедневного возврата в случае привлечения внешних ресурсов, а также сумму начисленных процентов при таком развитии событий. Эта же подпрограмма произведет расчеты для создания диаграммы возможного привлечения и погашения кредитных ресурсов на листе Диаграмма2 (рис. 26.10.);
• перейдите на лист ПК и в ячейку D2 введите формулу определения месяца периода, за который требуется сформировать платежный календарь:
• =МЕСЯЦ([СистемаБюджетов.xls]Пульт!$F$2)

После этого нажмите кнопку Заполнение платежного календаря, которой назначен подпрограмма ЗаполнениеПлатежногоКалендаря. На рис. 26.11. показан фрагмент сформированного планового платежного календаря за июль 2003 года.

Рис. 26.8. Таблица ежедневных оборотов и остатков денежных средств

Рис. 26.9. Параметры недостающих денежных средств и начисленным процентам при условии привлечения кредитной линии

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

Рис. 26.11. Сформированный платежный календарь на июль 2003 года

Рис. 26.12. График планируемого движения денежных потоков за июль 2003 года

Анализ полученных результатов

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

• 3 июля — незначительный при погашении кредита;
• 10 июля — максимальный за счет выплаты зарплаты, оплаты потребленной электроэнергии и приобретения оборудования для цеха посуды;
• после 10-го июля отрицательный остаток денежных средств плавно уменьшается и к 31 июля выходит на запланированный бюджетом остаток денежных средств.

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

Сценарии могут быть самыми различными — от самого оптимистического до самого пессимистического, в чем поможет моделирование по принципу "Что-если", при котором останутся наиболее максимальные и реальные остатки денежных средств.

Описание одного из сценариев устранения кассовых разрывов

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

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

Принято решение:

• пролонгировать кредит 1 млн. руб., срок погашения которого наступает 3 июля, выплатив сумму процентов 20 тысяч руб. за прошлый период 3 июля и 29150 рублей 31 июля за срок продления;
• перенести выплату заработной платы управленцам администрации завода в сумме 1040 тысяч руб. с 10 июля на 17 июля. Это позволит снизить пиковое значение суммы кассового разрыва 10 июля и несколько сгладить его неравномерности в середине месяца;
• подписать с двумя банками договора на получение краткосрочных кредитов. С первым в сумме 3 млн. рублей с 10 июля по 24 июля под 40% годовых, что составляет 46 тысяч руб. Второй кредит в сумме 1 млн. рублей с 10 июля по 29 июля под 39% годовых, что составляет 20,3 тыс. руб. Проценты выплачиваются одновременно с погашением кредитов.

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

Рис. 26.13. Расчеты по привлечению денежных ресурсов по договору овердрафта и процентам по нему

Согласно условий прогнозирования денежных потоков остаток денежных средств на конец планируемого периода составит 48002,92 рубля, а график движения денежных средств представлен на рис. 26.14.

Рис. 26. 14. График денежных потоков

Корректировка БДР и БДДС

В результате проведенного моделирования не учтены суммы процентов за привлечение дополнительных денежных средств. Введите эти суммы в функциональный бюджет по дополнительной финансовой информации в сумме 29150 + 46000 + 20300 + 6842 = 102 292 рубля и проанализируйте изменение БДР и БДДС.

Для исследования отклонений создайте копии рабочих листов БДДС и БДР и после обработки измененных данных сравните результаты введением формул вычитания, например, в одном из столбцов справа. Введенные формулы показаны в строке формул.

Результаты сравнений приведены на рис. 26.15. и 23.16.

Рис. 26.15. Сравнение БДДС до и после введения корректировок

Рис. 26.16. Сравнение БДР до и после введения корректировок

Итоги

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