Имитационное моделирование рисков инвестиционных проектов с применением функций EXCEL

     Функция СЛЧИС():

     Функция СЛЧИС() возвращает равномерно распределенное случайное число E, большее либо равное 0 и меньшее 1, т.е.: 0 <= E < 1. Вместе с тем, путем несложных преобразований, с ее помощью можно получить любое случайное вещественное число. Например, чтобы получить случайное число между a и b, достаточно задать в любой ячейке ЭТ формулу 3.1.

Адрес ячейки=СЛЧИС()*(b-a)+a   (3.1)

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

     Настройка режима управления вычислениями производится установкой соответствующего флажка в подпункте "Вычисления" пункта "Параметры" темы "Сервис" главного меню.

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

     Функция СЛУЧМЕЖДУ(нижн_граница; верхн_граница):

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

     В качестве примера сгенерируем случайное значение для переменной Q (объем выпуска продукта).

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

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

1)     Выбрать в главном меню тему "Сервис".

2)     Выбрать пункт "Параметры" подпункт "Вычисления".

3)     Установить флажок "Вручную" и нажать кнопку "ОК".

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

     Первый лист – "Имитация", предназначен для построения генеральной совокупности (рисунок 3.1). Определенные в данном листе формулы и собственные имена ячеек приведены в таблицах  3.2 и 3.3.

Рисунок 3.1 - Лист "Имитация"

Таблица 3.2 - Формулы листа "Имитация"

Таблица 3.3 - Имена ячеек листа "Имитация"

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

     Вторая часть листа (блок ячеек А9:Е11) предназначена для проведения имитации. Формулы в ячейках А10:С11 генерируют значения для соответствующих переменных с учетом заданных в ячейках В3:С5 диапазонов их изменений. Обращаем внимание на то, что при указании нижней и верхней границы изменений используется абсолютная адресация ячеек.

     Формулы в ячейках D10:E11 вычисляют величину потока платежей и его чистую современную стоимость соответственно. При этом значения постоянных переменных берутся из следующего листа шаблона – "Результаты анализа".

     Лист "Результаты анализа" кроме значений постоянных переменных содержит также функции, вычисляющие параметры распределения изменяемых (Q, V, P) и результатных (NCF, NPV) переменных и вероятности различных событий. Определенные для данного листа формулы и собственные имена ячеек приведены в таблицах 3.4 и 3.5. Общий вид листа показан на рисунке  3.2.

Таблица 3.4 - Формулы листа "Результаты анализа"

Продолжение таблицы 3.4