Моделирование денежных потоков является одним из основных инструментов финансового планирования. Для реализации данной модели целесообразно использовать методы имитационного моделирования, поскольку именно имитационные модели предоставляют наиболее широкие возможности анализа и оценки функционирования объекта при разнообразных изменениях входных параметров.
Разработку имитационной модели денежных потоков приведем на примере одной сельскохозяйственной культуры: пивоваренного ячменя.
В хозяйстве возделывается пивоваренный ячмень сорта Скарлетт на площади 100 га. Планируемая урожайность – 40 ц/га. Для моделирования помесячного денежного потока была разработана технологическая карта выращивания данной культуры. Путем обработки технологической карты с помощью инструмента MS Excel «Сводная таблица» из пункта меню «Данные» была получена информация, представленная в таблице 36.
Таблица 36. Потребность в денежных средствах, тыс. руб.
| Статьи затрат | Номер месяца | За год | |||||||||||
| Работы прошлого года | |||||||||||||
| Оплата с отчислениями | 8,3 | 8,3 | |||||||||||
| Семена | |||||||||||||
| Удобрения | 404,0 | 404,0 | |||||||||||
| ХСЗР | |||||||||||||
| Дизельное топливо | 54,4 | 54,4 | |||||||||||
| Текущий ремонт | 6,8 | 6,8 | |||||||||||
| Электроэнергия | |||||||||||||
| Автотранспорт | 1,0 | 1,0 | |||||||||||
| Всего | 474,4 | 474,4 | |||||||||||
| Работы текущего года | |||||||||||||
| Оплата с отчислениями | 15,6 | 2,1 | 2,1 | 19,0 | 38,9 | ||||||||
| Семена | 231,3 | 231,3 | |||||||||||
| Удобрения | 125,0 | 125,0 | |||||||||||
| ХСЗР | 12,4 | 108,4 | 50,4 | 171,2 | |||||||||
| Дизельное топливо | 15,6 | 2,1 | 2,1 | 19,0 | 38,9 | ||||||||
| Текущий ремонт | 4,4 | 1,0 | 1,0 | 24,6 | 31,0 | ||||||||
| Электроэнергия | 0,1 | 2,2 | 2,2 | ||||||||||
| Автотранспорт | 0,7 | 9,5 | 10,2 | ||||||||||
| Всего | 405,1 | 113,7 | 55,7 | 74,2 | 648,7 |
По условиям задачи на начало производственного цикла собственные средства у предприятия отсутствуют. Все операционные (текущие) расходы до реализации произведенной продукции ежемесячно покрываются за счет заемных средств. Предприятие имеет кредитную линию с лимитом до 1,5 млн руб. Процентная ставка за пользование кредитом составляет 17% годовых. Информация о планируемых денежных потоках приведена в таблице 37.
|
|
Таблица 37. Имитационная модель денежных потоков по операционной деятельности, тыс. руб.
| Операционные доходы и расходы | До начала производства | Прошлый год | Текущий год | Всего | ||||||||||
| Операционные доходы | ||||||||||||||
| Реализация ячменя | 1 920,0 | 1 920,0 | ||||||||||||
| Операционные расходы | ||||||||||||||
| Оплата с отчислениями | 8,3 | 15,6 | 2,1 | 2,1 | 19,0 | 47,1 | ||||||||
| Семена | 231,3 | 231,3 | ||||||||||||
| Удобрения | 404,0 | 125,0 | 529,0 | |||||||||||
| ХСЗР | 12,4 | 108,4 | 50,4 | 171,2 | ||||||||||
| Дизельное топливо | 54,4 | 15,6 | 2,1 | 2,1 | 19,0 | 93,2 | ||||||||
| Текущий ремонт | 6,8 | 4,4 | 1,0 | 1,0 | 24,6 | 37,8 | ||||||||
| Электроэнергия | 0,1 | 2,2 | 2,2 | |||||||||||
| Автотранспорт | 1,0 | 0,7 | 9,5 | 11,1 | ||||||||||
| Всего | 474,4 | 405,1 | 113,7 | 55,7 | 74,2 | 1 123,0 | ||||||||
| Сальдо от операционной деятельности | -474,4 | -405,1 | -113,7 | -55,7 | -74,2 | 1 920,0 | 797,0 | |||||||
| то же, нарастающим итогом | -474,4 | -474,4 | -474,4 | -474,4 | -474,4 | -474,4 | -474,4 | -879,4 | -993,1 | -1 048,8 | -1 123,0 | 797,0 |
|
|
Таблица 38. Имитационная модель формирования источников финансирования производства ячменя, тыс. руб.
| Показатели | До начала производства | Прошлый год | Текущий год | Всего | ||||||||||
| Потребность в денежных средствах | ||||||||||||||
| На операционную деятельность | 474,4 | 405,1 | 113,7 | 55,7 | 74,2 | 1 123,0 | ||||||||
| На выплату процентов за кредит | 6,7 | 6,8 | 6,9 | 7,0 | 7,1 | 7,2 | 7,3 | 13,2 | 15,0 | 16,0 | 17,2 | 110,4 | ||
| Всего требуется | 474,4 | 6,7 | 6,8 | 6,9 | 7,0 | 7,1 | 7,2 | 412,4 | 126,8 | 70,6 | 90,2 | 17,2 | 1 233,4 | |
| Имеется выручки | 1 920,0 | 1 920,0 | ||||||||||||
| Источники покрытия | ||||||||||||||
| Из выручки | 17,2 | 17,2 | ||||||||||||
| Привлеченные средства | 474,4 | 6,7 | 6,8 | 6,9 | 7,0 | 7,1 | 7,2 | 412,4 | 126,8 | 70,6 | 90,2 | 1 216,2 | ||
| Остаток выручки | 1 902,8 | |||||||||||||
| На погашение основного долга по кредиту | 1 216,2 | |||||||||||||
| Остаток кредита нарастающим итогом | 474,4 | 481,1 | 487,9 | 494,8 | 501,8 | 508,9 | 516,1 | 928,5 | 1 055,4 | 1 126,0 | 1 216,2 | |||
| Сальдо от финансовой деятельности | 686,6 | |||||||||||||
| то же, нарастающим итогом | 686,6 |
|
|
Рисунок 35. Имитационная модель денежных потоков и формирования источников финансирования производства ячменя
На рисунке 29 приведена имитационная модель денежных потоков и формирования источников финансирования производства ячменя.
В строках 6-17 листа «Потоки» приведена информация, характеризующая операционную деятельность по производству ячменя. Информация по строкам 8-17 заполняется на основании данных таблицы 37. Реализация ячменя планируется в августе. Объем выручки от реализации ячменя определяется по формуле: N7=H1*H2/10*C1/1000.
Сальдо денежных потоков (строка 18) определяется как разница между поступлением и расходованием денежных средств. C18=C7-C17, а затем эта формула копируется для ячеек D18:O18.
Сальдо нарастающим итогом (строка 19) определяется по формуле: C19=B19+C18, а затем эта формула копируется для ячеек D18:N18.
Модель формирования источников финансирования приведена в строках 20-32 листа «Потоки».
Месячная потребность в денежных средствах на операционную деятельность (строка 21) соответствует строке 17, то есть C21=C17, а затем эта формула копируется для ячеек D21: O21.
Месячная потребность в денежных средствах на выплату процентов за кредит (строка 22) определяется как произведение величины кредиторской задолженности в предыдущем месяце и ставки по кредитам, деленной на 12: C22=B30·$C$2/12, а затем эта формула копируется для ячеек D22:N22.
Годовая сумма выплат процентов определяется как сумма месячных выплат: O22=СУММ(C22:N22).
Общая потребность в денежных средствах определяется путем суммирования месячной потребности в денежных средствах на операционную деятельность (строка 21) и месячной потребности в денежных средствах на выплату процентов за кредит (строка 22). C23=C21+С22, а затем эта формула копируется для ячеек D23: O23.
Значения строки 24 «Имеется выручки» соответствуют значениям строки 7, характеризующим размер выручки от реализации ячменя. C24=C7, а затем эта формула копируется для ячеек D24:O24.
Покрытие потребности в денежных средствах из выручки (строка 26) происходит в соответствии с условием: если разница между имеющейся выручкой нарастающим итогом, суммарными выплатами, произведенными из выручки и суммарным погашением основного долга по кредиту превышает текущую месячную потребность в денежных средствах, то из выручки покрывается весь объем текущей месячной потребности в денежных средствах. В противном случае из выручки может быть покрыта лишь разница между имеющейся выручкой нарастающим итогом, суммарными выплатами из выручки и суммарным погашением основного долга по кредиту. C26=ЕСЛИ(СУММ($B$24:C24)-СУММ($B$26:B26)-СУММ($B$29:B29)>C23;C23; СУММ($B$24:C24)-СУММ($B$26:B26)- СУММ($B$29:B29)), а затем эта формула копируется для ячеек D26:N26.
Годовая сумма финансирования текущих затрат из выручки определяется как сумма месячных выплат: O26=СУММ(C26:N26).
Объем заемных средств в текущем месяце определяется исходя из следующего условия: если разница между общей потребностью в денежных средствах и сальдо от операционной деятельности нарастающим итогом имеет отрицательное значение, меньше или равна нулю, то заемные средства не привлекаются, в противном случае находится разница между общей потребностью в денежных средствах и покрытием потребности в денежных средствах в текущем месяце за счет выручки. C27=ЕСЛИ(C23-C19<=0;0;C23-C26), а затем эта формула копируется для ячеек D27:N27.
Годовая сумма заемных средств определяется как сумма месячных заимствований: O27=СУММ(C27:N27).
Для определения ежемесячного остатка выручки проверяется условие: если разница между имеющейся выручкой и выплатами из выручки меньше или равна нулю, то остатка выручки нет, в противном случае остаток выручки определяется как разница между имеющейся выручкой и выплатами из нее. C28=ЕСЛИ(C24-C26<=0;0;C24-C26), а затем эта формула копируется для ячеек D28:N28.
Величина средств, направляемых на погашение основного долга по кредиту, определяется исходя из условия: если остаток выручки превышает остаток основного долга по кредиту, то весь остаток кредита гасится, в противном случае на погашение основного долга направляется весь имеющийся остаток выручки. C29=ЕСЛИ(C28>B30;B30;C28), а затем эта формула копируется для ячеек D29:N29.
Остаток кредита нарастающим итогом определяется исходя из условия: если разница между суммой заемных средств нарастающим итогом и суммой денежных средств нарастающим итогом, направленных на погашение основного долга, отрицательна, то остатка основного долга по кредиту нет, в противном случае остаток находится как разница между суммой заемных средств нарастающим итогом и суммой денежных средств нарастающим итогом, направленных на погашение основного долга. C30=ЕСЛИ(СУММ($B$27:C27)-СУММ($B$29:C29)<0;0;СУММ($B$27:C27)-СУММ($B$29:C29)), а затем эта формула копируется для ячеек D30:N30.
Сальдо от финансовой деятельности находится как сумма имеющейся в текущем месяце выручки и заемных средств за вычетом средств, направленных на покрытие текущей потребности в денежных средствах, и погашение основного долга по кредиту. C31==C24+C27-C23-C29, а затем эта формула копируется для ячеек D31:N31.
Сальдо нарастающим итогом (строка 32) определяется по формуле: C32=B32+C31, а затем эта формула копируется для ячеек D32:N32.
В отличие от моделей оптимального выбора данная модель не позволяет выбрать наилучшее решение в соответствии с каким-либо критерием, но является надежным инструментом изучения поведения системы при различных изменениях входных параметров.
Так, при планируемых условиях хозяйствования общая потребность в денежных средствах, необходимых для выращивания ячменя на площади 100 га, составит 1 233,4 тыс. руб., из которых 1 123,0 тыс. руб. будет направлена на финансирование операционной деятельности, а 110,4 тыс. руб. – на выплату процентов по кредитам. Сальдо денежных потоков составит 686,6 тыс. руб., что свидетельствует о довольно высоком уровне привлекательности пивоваренного ячменя для сельских товаропроизводителей. Правда, этот вывод корректен лишь для заданных условий функционирования.
С помощью разработанной модели можно как сымитировать изменения условий хозяйствования через корректировку входных параметров модели, так и определить критические для предприятия значения этих параметров.
Например, при падении цены реализации ячменя с 4 800 до 3 500 руб. за 1 т сальдо денежных потоков за рассматриваемый период снизится до 166,6 тыс. руб. (рисунок 36), при снижении урожайности с 40 до 30 ц/га – до 206,6 тыс. руб. (рисунок 37).
Рисунок 36. Результаты прогона имитационной модели при цене реализации ячменя
3 500 руб. за 1 т
Рисунок 37. Результаты прогона имитационной модели при урожайности ячменя 30 ц/га
Изменения параметров можно задавать с помощью генератора случайных чисел. Для этого необходимо задать нижнюю (НГ) и верхнюю (ВГ) границу значений параметра и использовать функцию СЛЧИС(). Обратите внимание, что аргумент у этой функции отсутствует. Генерация случайного числа в заданных пределах изменения происходит по формуле: =СЛЧИС()·(ВГ-НГ)+НГ.
Для исследования поведения системы при случайных изменениях цен реализации ячменя на расчетном листе «Потоки» в ячейках O1 и O2 необходимо ввести значения нижнего и верхнего пределов ее колебаний. Тогда в ячейку ХХ вместо заданной по условию цены реализации необходимо ввести формулу C1=СЛЧИС()·(O2-O1)+O1. Для генерации нового случайного числа следует использовать клавишу «F9».
Рисунок 38. Результаты прогона имитационной модели при выборе цены реализации ячменя с использованием генератора случайных чисел
Для установления критических или некоторых заданных значений параметров целесообразно использовать инструмент «Подбор параметра» из пункта меню «Сервис». Данный инструмент позволяет получить заданное значение какого-либо параметра при изменении указанной ячейки.
Например, для того чтобы определить, какой уровень цены реализации ячменя является критическим для предприятия (сальдо от финансовой деятельности равно 0), необходимо задать следующие значения инструмента «Подбор параметра»:
Рисунок 39. Пример заполнения полей инструмента «Подбор параметра»
После подтверждения введенных значений с помощью нажатия виртуальной кнопки «ОК» на экране появляется диалоговое окно «Результаты подбора параметра»:
Рисунок 40. Пример состояния диалогового окна «Результаты подбора параметра»
Если в диалоговом окне «Результаты подбора параметра» появляется информация «Решение найдено», то после нажатия виртуальной кнопки «ОК» можно оценить все выходные параметры, отражающие состояние системы. Если в диалоговом окне «Результаты подбора параметра» появляется информация «Решение не найдено», то после нажатия виртуальной кнопки «Отмена» необходимо скорректировать значения полей инструмента «Подбор параметров»
На рисунке 35 приведен результат подбора критического уровня цены реализации ячменя, при котором сальдо финансовых потоков принимает нулевое значение.
Рисунок 41. Результат подбора критического уровня цены реализации ячменя
Критической ценой реализации ячменя при прочих заданных условиях является 3 084 руб. за 1 т.
Задание для самостоятельной работы
Используя условия предыдущей задачи, разработать имитационную модель и осуществить ее прогоны по следующим сценариям:
Используя инструмент «Подбор параметров» определить:
· при каких ставках за пользование кредитом сальдо денежных потоков достигнет значения 100 тыс. руб.;
· при каком уровне урожайности сальдо денежных потоков достигнет значения 0;
· при каких ставках за пользование кредитом годовая сумма средств, направленных на выплату процентов, составит 50 тыс. руб.
Проанализировать изменения выходных параметров модели при условии, что до начала процесса производства у предприятия имелся остаток выручки в объеме 250 тыс. руб. (ячейка В24=250). Для этого в разработанной модели необходимо дополнительно ввести следующие формулы: B31=B24 и B32=B31.
При условиях, заданных в пункте 2, выполнить задания пункта 1.
При исходных условиях проанализировать изменения выходных параметров модели при росте цен на дизельное топливо, минеральные удобрения и химические средства защиты растений соответственно на 25%, 20% и 15%.
Вопросы для самоконтроля
При выполнении данного задания проверяются только практические навыки разработки и реализации имитационной модели.