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

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

В свою очередь краткосрочное банковское финансирование можно разделить на:

• получения краткосрочной ссуды (кредита);
• привлечение денежных средств по договору овердрафта.

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

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

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

Расчет суммы кредита для проведения операции и процентов по нему

Постановка задачи

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

Возможный сценарий

Предприятие заключает с банком кредитный договор, согласно которого оно:

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

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

Создание таблицы расчета суммы кредита

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

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

=МИН(E3:E200)

При заданных условиях и введенных данных в рабочий лист ИсхДанные она зафиксирует сумму 1223979 руб., которую введите в ячейку С1 (рис. 20.1.).

Рис. 20.1. Упрощенный расчет суммы кредита и процентов по нему

В ячейки В2:В4 введите даты получения кредита и выплаты процентов по нему. Формулы в ячейках С3 и С4 производят расчет процентов по кредиту за январь и февраль соответственно. В формулах производится операция умножения необходимой рассчитываемой суммы кредита на количество дней, за которые они платятся и ставку ежедневного процента. С целью упрощения расчета принимаем ставку процентов за кредит равной 36,5% и количество дней в году равному 365. Формула в ячейке С2 рассчитывает общую сумму привлекаемого кредита на основании:

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

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

=C1/(1-((B3-B2)/365)*36,5%)

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

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

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

Сразу же обратите внимание на сумму налога на прибыль (строка 49), которая по сравнению с 54600 рублей (рис. 18.1.) уменьшилась до 45184,78 рублей. А остаток денежных средств после завершения операции снизится с 115421 рубля до 67089,46 рублей.

Рис. 20.3. Рабочий лист СуммПотоки с оборотами и остатками денежных средств при введенном рассчитанном кредите

Расчет кредитной линии

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

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

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

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

Формулы расчета кредитной линии

Как и ранее, первый элемент автоматизации расчетов — предварительное создание формул, проверка правильности проводимых ими расчетов и последующая запись их ввода в макрос. Для проведения расчетов по кредитной линии введите на рабочем листе СуммПотоки формулы, которые показаны на рис. 20.4. и 20.5.

Рис. 20.4. Формулы расчетов по кредитной линии

Рис. 20.5. Формулы расчетов по кредитной линии

Ставка, по которой начисляются проценты по кредитной линии, находится в ячейке ВА1 — ссылка на ячейку рабочего листа Кредит . Далее формулы расчетов по кредитной линии рассматриваются в строке 4.

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

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

Необходимость получения дополнительных заемных средств или возможность погашения кредита регистрируется в ячейке ВС4. Формула при положительном значении в ячейке ВВ4 возвращает значение ячейки ВВ4, что говорит о потребности в привлечении заемных ресурсов. Если же в ячейке ВВ4 отрицательное значение, то формула в ячейке BD4 возвращает его с противоположным знаком. Это говорит о возможности погашения кредита.

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

Ежедневная сумма процентов по полученным денежным средствам проводится в ячейке BF4. Формула умножает сумму ежедневных остатков по кредитной линии на ставку процентов по кредитной линии и делит полученный результат на количество дней в году, равный 365.

Формула в ячейке ВН4 определяет факт наступления даты выплаты процентов по кредитной линии. Для этого применим два момента наступления такого случая:

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

Поэтому формула разделена на две части, в каждой из которых, функция ЕСЛИ определяет:

• что номер дня месяца в ячейке В4 больше, чем в ячейке В5 (см. рис. 20.5.). Это может быть только в одном случае — когда в ячейку В4 введена дата, содержащая последний день месяца. Тогда номер дня, определяемый с помощью функции ДЕНЬ в ячейке В5, будет однозначно равен единице — первый день месяца. Исходя из этого, из всей суммы начисленных процентов за весь период с первого дня вычитается вся сумма выплаченных ранее процентов;
• день, когда проценты по кредиту не начисляются, о чем укажет значение ячейки BF4 равное нулю. Это предполагает то, что кредитная линия погашена и тогда в этот день производится заключительная выплата процентов, сумма которой определяется как разность всей суммы начисленных процентов и выплаченных за весь период.

Если вышеперечисленные условия не выполняются, то формула возвращает значение ноль.

При необходимости, можно указать выплату процентов не в последний день месяца, а, например, в предпоследний. Для этого в первой части формулы введите изменение, при котором сравнение дат будет происходить не между соседними ячейками, а через одну — между ячейками В4 и В6. Аналогично можно указать любой день месяца, начиная счет с конца месяца. Если же необходимо изменить отсчет с начала месяца, то измените знак ">" на "<".

Если же в кредитном договоре оговорен конкретный день месяца, то введите номер этого дня, например, в ячейку ВН1 и результат, возвращаемой функцией ДЕНЬ(В4) сравните с содержимым ячейки ВН1. При совпадении номеров дней будет производиться расчет выплачиваемых процентов.

Дата выплаты процентов определяется в ячейке BG4, которая возвращает дату платежа при значении в ячейке ВН4 отличном от нуля.

Макрос ввода формул расчета кредита

Создайте макрос для ввода всех описанных выше формулы по расчету кредитной линии. Запись макроса ФормулыРасчетаКредита (рис. 20.6.) заключается в последовательном вводе формул в диапазоны ячеек каждого столбца от строки 3 до строки 200.

Макрос очистки области формул расчета кредита

Макрос ОчисткаФормулРасчетаКредита предназначен для очистки области формул расчета кредита.

Рис. 20.6. Подпрограмма ФормулыРасчетаКредита, предназначенная для ввода формул и макрос очистки этой области

Рис. 20.7. Числовые данные расчетов по кредитной линии

Отчет по кредитной линии

О важности отчетов в управлении современным предприятием говорилось на протяжении всей книги. Желательно чтобы отчеты готовились не вручную, а в автоматическом режиме. В создаваемой модели отчет о движении денежных средств по кредитной линии готовится на рабочем листе Кредит (рис. 20.8.) с помощью подпрограммы ОтчетПоКредиту.

Рис. 20.8. Отчет по рассчитываемой кредитной линии на рабочем листе Кредит

Подпрограмма формирования отчета по кредитной линии

Прежде чем приступить к записи макроса введите на рабочем листе Кредит формулы.

В ячейку В2 введите формулу, которая при движении средств по кредитной линии в ячейках ВС4 и ВD4 на рабочем листе СуммПотоки возвращает дату, введенную в ячейку В4:

=ЕСЛИ(СУММ(СуммПотоки!BC4:BD4)=0;"";СуммПотоки!B4)

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

=ЕСЛИ($B2="";0;СуммПотоки!BC4)

В ячейке F2 формула, которая возвращает дату выплачиваемых процентов:

=СуммПотоки!BG3

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

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

Автоматизация уточненного расчета кредитной линии с учетом налогов

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

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

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

Алгоритм поиска оптимального расчета параметров кредитной линии

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

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

Как показывает практика — достаточно трех-пяти итераций для нахождения решения.

Перенос данных в исходную таблицу на рабочем листе ИсхДанные

В таблице на рабочем листе ИсхДанные для переносимых данных выделите строки 4911:5000 для:

• 4911:4950 — перечисляемые налоги;
• 4951:4984 — расчеты по кредитной линии;
• 4985:5000 — проценты по кредитной линии.

Макрос очистки данных в исходной таблице

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

Макрос ввода в исходную таблицу сумм процентов и дат их перечисления

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

Рис. 20.10. Подпрограммы ВводСуммыПроцентов и ОчисткаАвтоматическиВведенныхДанных

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

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

• ввод данных по кредитной линии;
• ввод данных сумм налогов;
• создание формул для ввода текста проводимой операции, валюты и признака движения ДС.

Формулы, вводимые в диапазоны ячеек С4911:С4950, Н4911:Н4950 и К4911:К4984 листа ИсхДанные аналогичны формулам в макросе ВводСуммыПроцентов, кроме текста Налоги в первой формуле. В ячейку С4951 введите формулу, которая при наличии значения в ячейке М4951 больше нуля возвращает текст Кредит получен, а в противном Кредит возвращен:

=ЕСЛИ(D4951="";"";ЕСЛИ(M4951>0;"Кредит получен";"Кредит возвращен"))

Если же значение в ячейке М4951 отсутствует, то формула ничего не возвращает.

Рис. 20.11. Подпрограмма ФормулыПереносаДанных

Полный цикл расчета параметров кредитной линии

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

Рис. 20.12. Подпрограмма УточненыйРасчетКредитнойЛинии

Последовательность выполнения полного цикла расчетов в созданной модели

Задача созданной подпрограммы УточненныйРасчетКредитнойЛинии последовательно передавать выполнение различных процедур другим подпрограммам. Она формируется в Редакторе Visual Basic, в основном, методом копирования и вставки в необходимой последовательности, имен подпрограмм, которых насчитывается около 20-ти.

В процессе выполнения последовательности операций будут активизироваться различные рабочие листы, а в них ячейки и диапазоны. И если наблюдать на мониторе за выполнением операций, то глаза могут устать, а компьютеру требуются дополнительные ресурсы для постоянного обновления экрана. Поэтому в первой строке кода VBA для свойства ScreenUpdating (обновление экрана во время выполнения подпрограммы) объекта Excel (Application) устанавливается свойство False. Поэтому при выполнении подпрограммы на экране никаких изменений наблюдаться не будет.

Далее производится очистка содержимого строк 4911:5000 на рабочем листе ИсхДанные. Эта процедура необходима для приведения таблицы в исходное состояние до проведения расчетов. Затем происходит:

• перерасчет таблицы на рабочем листе ИсхДанные;
• расчет всех налогов на рабочем листе Налоги;
• перерасчет таблицы на рабочем листе СуммПотоки с учетом рассчитанных налогов;
• расчет параметров кредитной линии и подготовка отчета по кредитной линии на рабочем листе Кредит.

Далее используется инструкция For- Next, в состав элементов которой входят девять подпрограмм, выполняющих:

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

После завершения цикла For- Next производится очистка содержимого области расчета параметров кредитной линии на рабочем листе СуммПотоки.

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

Рис. 20.13. Введенные суммы налогов в исходную таблицу на рабочем листе ИсхДанные

Таблица движения денежных потоков при получении кредитной линии

Если сравнить полученную таблицу движения денежных потоков на рабочем листе СуммПотоки при получении кредитной линии (рис. 20.14.) с аналогичной таблицей на рис. 20.3., то заметите что второй пример более выгоден.

В первом случае остаток денежных средств по завершению торговой операции 67089,46 руб., во втором же 73804,55 руб. Получена экономия денежных средств в размере 6715,09 руб.

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

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

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

Итоги

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

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

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