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

Несмотря на то, что модель прогнозирования денежных потоков готова, специалисты финансового отдела пришли к выводу, что таблицы с цифрами не могут дать полного осмысленного представления о происходящих процессах движения наличности. Только большое количество практически одинаковых чисел не позволяет прийти к выводам о какой-то закономерности движения денежных потоках или, наоборот, об хаотичности. Гораздо легче информация воспринимается, если она может быть представлена в виде диаграмм (графиков). Решено создать диаграммы отражающие движение и остатки денежных потоков за 2-х месячный период.

Создание графика движения денежных средств

Для быстрого построения графика выделите область В2:Е61 на рабочем листе СуммПотоки и нажмите клавишу F11. Диаграмма будет построена автоматически на отдельном листе Диаграмма1 слева от рабочего листа СуммПотоки и будет иметь вид представленный на рис. 22.1. Она представлена в виде гистограммы, отражает три графика: приход, расход и остатки наличности. Несмотря на скорость ее создания у нее есть недостаток — трудно что-либо разобрать. Следовательно, мало просто построить какой-то график, а необходимо подобрать такие его параметры, которые позволят максимально быстро делать соответствующие выводы.

Рис. 22.1. Диаграмма ежедневных оборотов движения и остатков денежных потоков

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

Рис. 22.2. Диалоговое окно Мастер диаграмм (шаг 1 из 4): тип диаграммы

Далее на диаграмме дважды щелкните по линии график, отражающего развитие процесса, что вызовет появление диалогового окна Формат ряда данных. На вкладке Вид задайте подходящий Тип линии, Цвет и Толщину (рис. 22.3.).

Рис. 22.3. Диалоговое окно Формат ряда данных вкладка Вид с открытым списком Толщина

Для графика поступления денежных средств на вкладке Ось диалогового окна Формат ряда данных, активизируйте переключатель По вспомогательной оси области Построить ряд (рис. 22.4.). График будет иметь две оси по вертикали: левая для графика расхода и остатков денежных средств, правая — для поступления. Это придаст графику большую наглядность и понимание.

Рис. 22.4. Диалоговое окно Формат ряда данных вкладка Ось с активизированным переключателем по вспомогательной оси для графика поступления денежных средств

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

Рис. 22.5. Диалоговое окно Формат области построения

Рис. 22.6. Полученный график движения денежных потоков

Управление таблицей денежных потоков с помощью графика

У созданного графика имеется существенный недостаток — он отражает статистические данные. То есть при введении изменений исходных данных для графика поступления или списания денежных средств на листе СуммПотоки никак не повлияет на график остатка денежных средств. Для устранения этого недостатка запишите макрос СальдоДенежныхПотоков (рис. 22.7.), который заключается во вводе формул вычисления остатка денежных средств на рабочем листе СуммПотоки Формула в ячейке Е4:

=СУММ(C4:D4)+E3

Рис. 22.7. Подпрограмма СальдоДенежныхПотоков

Теперь можно управлять остатками денежных средств в таблице на рабочем листе СуммПотоки, не только вводя необходимые числа в область стольбцов С и D этой таблицы, но и изменяя графики поступления или списания денежных средств. Предположим, в силу каких то обстоятельств, 11 января 2002 года, сумма, которая будет перечислена экспедиторам, возрастет со 120 тысяч рублей до 180 тысяч. Для того чтобы увидеть, как это повлияет на остатки денежных средств, щелкните правой клавишей мыши по точке за 11.02.2002 года линии графика расхода денежных средств и затем поместите курсор в эту точку. Курсор преобразится в вертикальную стрелку и на графике будет показаны координаты этой точки (рис. 22.8.).

Рис. 22.8. Фрагмент графика с параметрами выбранной точки графика расхода денежных средств

Перетяните вниз точку графика с помощью мыши, удерживая ее правую клавишу. При этом будет отражаться числовое значение этой точки. Если отпустить правую кнопку мыши, то точка зафиксируется. При этом изменится содержимое ячейки D9 (рис. 20.3.), и остатков денежных средств в диапазоне ячеек Е9:Е200, а соответственно и весь график остатков денежных средств (рис. 22.9.) сдвинется вниз на 60 тысяч рублей.

Рис. 22.9. Фрагмент графика с параметрами измененной точки графика расхода денежных средств и изменившимся графиком остатков денежных средств.

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

Но и такой метод имеет недостаток — при изменении денежных потоков не оказывается влияние на изменение параметров кредитной линии.

Создание графической модели управления параметрами кредитной линии

Устранению недостатка по управлению параметрами кредитной линии поможет создание графика изменения этих параметров, для построения которого создайте таблицу (рис. 22.10.) на рабочем листе СуммПотоки, отражающую эти изменения.

Рис. 22.10. Таблица для построения графика изменений по параметрам кредитной линии

В таблице находятся следующие формулы.

в ячейке СА3 ссылка на область дат:

=B3

в ячейке СВ3 ссылка на столбец, отражающий получение транша по кредитной линии:

=BC3

в ячейке СС3 ссылка на столбец погашения кредита:

=-BD3

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

=BE3+СУММ(BF$3:BF3)

После ввода формул запишите макрос ФормулыДополнительныхПараметровКредита, создающий эти формулы в области СА3:CD200 и создайте график на рабочем листе Диаграмма2 (рис. 22.11.).

Рис. 22.11. График дополнительных параметров кредитной линии

В таблице дополнительных параметров кредитной линии (рис. 22.10.) и на графике (рис. 22.11.) автоматически будет отражено:

• сумма получения дополнительного транша по кредитной линии;
• определена дата и сумма погашения дополнительного транша по кредитной линии;
• сальдо по кредитной линии с учетом начисленных дополнительных процентов.

Для того чтобы таблица и график были созданы, запустите на выполнение макрос ОтчетПоКредиту расчета параметров кредитной линии (рис. 20.9.). Для автоматического ввода формул и создания таблицы расчета параметров по дополнительной кредитной линии запишите подпрограмму ПараметрыДополнительногоКредита (рис. 22.12.), которая объединит в себе все процедуры, выполнение которых необходимо для выполнения операции графического просмотра параметров дополнительной кредитной линии.

Рис. 22.12. Подпрограмма ПараметрыДополнительногоКредита

Одновременный просмотр изменения параметров кредитной линии в итерактивеном режиме

Для одновременного просмотра двух диаграмм: диаграммы движения денежных потоков и диаграммы изменения параметров кредитной линии желательно чтобы они были расположены рядом. Например, диаграмма движения денежных потоков в левой половине монитора, а вторая диаграмма — в правой. Такая возможность в Windows и Excel предусмотрена. Для этого необходимо создать еще одно окно с рабочим листом, для чего выполните команду Окно/Новое.

Если после этого активизировать команду меню Окно, то увидите, что в перечне открытых файлов произошли изменения. Вместо одного файла до создания нового окна (рис. 22.13.) в меню отражено два файла (рис. 22.14.):

• имя файла с ДенежныеПотоки изменится на ДенежныеПотоки:1;
• и появится еще одна строка с именем файла ДенежныеПотоки:2.

Рис. 22.13. Активизирована команда меню Окно до создания нового окна

Рис. 22.14. Активизирована команда меню Окно после создания нового окна

Для одновременного появления двух окон выполните команду Окно/Расположить, после чего появится диалоговое окно Расположение окон (рис.22.13.), в котором активизируйте опцию Рядом.

Рис. 22.15. Диалоговое окно Расположение окон

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

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

• щелкните правой кнопкой мыши по левому окну и потом по ярлыку имени рабочего листа Диаграмма1;
• щелкните правой кнопкой мыши по правому окну и потом по ярлыку имени рабочего листа Диаграмма2.

После этого при изменении графика в левом окне будете видеть изменения параметров кредитной линии в правом.

Габариты монитора ограничены и при работе в двухоконном режиме с графиками нет необходимости в дополнительном отображении панелей инструментов и строке формул. Для удаления стандартных панелей инструментов и строки формул последовательно выполните команды Вид/Панели инструментов/Стандартная и Вид/Панели инструментов/Форматирование. А после этого команду Вид/Строка формул.

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

Автоматизация показа двухоконного режима представления графиков

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

Рис. 22.16. Подпрограмма ГрафикиПросмотраИзменений

Эта подпрограмма создает удобный двухоконный режим для просмотра — как поведут себя параметры кредитной линии в зависимости от изменений внесенных в таблицу денежных потоков. Для записи макроса последовательно выполните команды:

• Вид/Панели инструментов/Стандартная убрать флажок с опции;
• Вид/Панели инструментов/Форматирование убрать флажок с опции;
• Вид/Строка формул убрать флажок с опции;
• Окно/Новое;
• в отрывшемся диалоговом окне Расположение окон (рис. 22.15.) поставьте флажок в опции Рядом;
• активизируйте второе окно ДенежныеПотоки.xls:2, расположенное слева щелкнув по нему правой кнопкой мыши;
• активизируйте рабочий лист Диаграмма1;
• активизируйте второе окно ДенежныеПотоки.xls:1, расположенное справа, щелкнув по нему правой кнопкой мыши;
• активизируйте рабочий лист Диаграмма2;
• остановите запись макроса и отредактируйте код VBA.

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

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

Рис. 22.17. Одновременное представление двух интерактивных графиков: денежные потоки и параметры кредитной линии

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

Ввод изменившихся параметров кредитной линии

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

• активизируйте в правом окне рабочий лист СуммПотоки;
• выделите область ячеек СВ3:СС200 и скопируйте в буфер обмена;
• выделите ячейку С3 и произведите вставку вызвав диалоговое окно Специальная вставка, в котором активизируйте переключатели Значение и Сложить;
• проделайте аналогичную операцию с диапазоном ячеек BH3:BH200 выделив при вставке ячейку D3;
• активизируйте лист Диаграмма2;
• остановите запись макроса и отредактируйте код VBA.

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

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

Переход в исходное состояние из двухоконного режима просмотра графиков заключается в создании прежнего однооконного режима и активизации закрытых панелей инструментов и строки формул. Для этого запишите макрос ВозвратИсходноеСостояние.

Рис. 22.18. Подпрограмма перехода из двухоконного режима просмотра графиков в однооконный режим

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

• закройте правое окно, щелкнув правой кнопкой мыши по кнопке закрытия окна;
• щелкните правой кнопкой мыши по кнопке восстановления левого окна;
• выполните команду Вид/Панели инструментов/Стандартная;
• выполните команду Вид/Панели инструментов/Форматирование;
• выполните команду Вид/Строка формул;
• активизируйте рабочий лист Кредит щелкнув правой кнопкой мыши по ярлычку с его именем;
• остановите запись макроса и отредактируйте код VBA.

Для удобства в работе разместите кнопки управления макросами ВводПеременныхДанных и ВозвратИсходноеСостояние прямо на листе Диаграмма2.

Итоги

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