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

Окончание таблицы 3.4

Таблица 3.5 - Имена ячеек листа "Результаты анализа"

Рисунок 3.2 - Лист "Результаты анализа"

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

     Функции МИН() и МАКС() вычисляют минимальное и максимальное значение для массива данных из блока ячеек, указанного в качестве их аргумента. Имена и диапазоны этих блоков приведены в таблице 3.5. [6, с.32]

     Функция СЧЕТЕСЛИ() осуществляет подсчет количества ячеек в указанном блоке, значения которых удовлетворяют заданному условию. Функция имеет следующий формат:

=СЧЕТЕСЛИ(блок; "условие").

     В данном случае, заданная в ячейке F13, эта функция осуществляет подсчет количества отрицательных значений NPV, содержащихся в блоке ячеек ЧСС (см. таблицу 3.5).

     Механизм действия функции СУММЕСЛИ() аналогичен функции СЧЕТЕСЛИ(). Отличие заключается лишь в том, что эта функция суммирует значения ячеек в указанном блоке, если они удовлетворяют заданному условию. Функция имеет следующий формат:

=СУММЕСЛИ(блок; "условие").

     В данном случае, заданные в ячейках F14:F15 функция осуществляет подсчет суммы отрицательных (ячейка F14) и положительных (ячейка F14) значений NPV, содержащихся в блоке ЧСС. Смысл этих расчетов будет объяснен позже.

     Две последние формулы (ячейки Е18 и F18) предназначены для проведения вероятностного анализа распределения NPV и требуют небольшого теоретического отступления.

     В рассматриваемом примере мы исходим из предположения о независимости и равномерном распределении ключевых переменных Q, V, P. Однако какое распределение при этом будет иметь результатная величина – показатель NPV, заранее определить нельзя.

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

     В прикладном анализе для целей аппроксимации широко применяется частный случай нормального распределения – так называемое стандартное нормальное распределение. Математическое ожидание стандартно распределенной случайной величины Е равно 0: M(E) = 0. График этого распределения симметричен относительно оси ординат и оно характеризуется всего одним параметром – стандартным отклонением, равным 1.

     Приведение случайной переменной E к стандартно распределенной величине Z осуществляется с помощью так называемой нормализации – вычитания средней и последующего деления на стандартное отклонение, рассчитываемой по формуле 3.2.

 (3.2).

     Как следует из (3.2), величина Z выражается в количестве стандартных отклонений. Для вычисления вероятностей по значению нормализованной величины Z используются специальные статистические таблицы.

     В ППП EXCEL подобные вычисления осуществляются с помощью статистических функций НОРМАЛИЗАЦИЯ() и НОРМСТРАСП() .

     Функция НОРМАЛИЗАЦИЯ(x; среднее; станд_откл):

     Эта функция возвращает нормализованное значение Z величины x, на основании которого затем вычисляется искомая вероятность p(E<=x). Она реализует соотношение (3.2). Функция требует задания трех аргументов:

х – нормализуемое значение;

среднее – математическое ожидание случайной величины Е;

станд_откл – стандартное отклонение.

     Полученное значение Z является аргументом для следующей функции – НОРМСТРАСП().

Функция НОРМСТРАСП(Z):

     Эта функция возвращает стандартное нормальное распределение, т.е. вероятность того, что случайная нормализованная величина Е будет меньше или равна х. Она имеет всего один аргумент – Z, вычисляемый функцией НОРМАЛИЗАЦИЯ().

     Нетрудно заметить, что эти функции следует использовать в тандеме. При этом наиболее эффективным и компактным способом их задания является указание функции НОРМАЛИЗАЦИЯ() в качестве аргумента функции – НОРМСТРАСП(), т.е.:

=НОРМСТРАСП(НОРМАЛИЗАЦИЯ(x; среднее; станд_откл)).

     С целью повышения наглядности, в проектируемом шаблоне функции заданы раздельно (ячейки Е18 и F18).

     Сформируем данный шаблон и сохраним его на магнитном диске под именем SIMUL_1.XLT. Приступаем к имитационному эксперименту. Для его проведения необходимо выполнить следующие шаги.

1.      Ввести значения постоянных переменных в ячейки В2:В4 и D2:D4 листа "Результаты анализа".

2.      Ввести значения диапазонов изменений ключевых переменных в ячейки В3:С5 листа "Имитация".

3.      Задать в ячейке В7 требуемое число экспериментов.

4.      Установить курсор в ячейку А11 и вставить необходимое число строк в шаблон (номер последней строки будет вычислен в Е7).

5.      Скопировать формулы блока А10:Е10 требуемое количество раз.

6.      Перейти к листу "Результаты анализа" и проанализировать полученные результаты.

     Рассмотрим реализацию выделенных шагов более подробно. Введём значения постоянных переменных в ячейки В2:В4 листа "Результаты анализа". Введём значения диапазонов изменений ключевых переменных в ячейки В3:С5 листа "Имитация". Укажем в ячейке В7 число проводимых экспериментов, например – 500. Установим табличный курсор в ячейку А11.

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

     Нажмём функциональную клавишу [F5]. На экране появится окно диалога "Переход" (рисунок 3.3).

Рисунок 3.3. - Окно диалога "Переход"

     Для перехода к нужному участку электронной таблицы достаточно указать в поле "Ссылка" адрес или имя соответствующей ячейки (блока). В данном случае, таким адресом будет любая ячейка последней вставляемой строки, номер которой вычислен в ячейке Е7 (508). Например, в качестве адреса перехода может быть указана ячейка А508.

     Введём в поле "Ссылка" адрес: А508 и нажмём комбинацию клавиш [SHIFT] + [ENTER]. Результатом выполнения этих действий будет выделение блока А11:А508. После чего осуществим вставку строк любым из известных способов.

     Теперь необходимо заполнить вставленные строки формулами блока ячеек А10:Е10. Для этого выполним следующие действия:

1)     Выделим и скопируем в буфер блок ячеек А10:Е10.

2)     Нажмём комбинацию клавиш [CTRL] + [SHIFT] + [].

3)     Нажмём клавишу [ENTER].

4)     Нажмём клавишу [F9] .

     Результатом выполнения этих действий будет заполнение блока А10:Е509 случайными значениями ключевых переменных V, Q, P и результатами вычислений величин NCF и NPV.

     Нетрудно заметить, что по результатам имитационного анализа риск проекта получился достаточно низким. Величина стандартного отклонения не превышает значения NPV. Коэффициент вариации (0,76) меньше 1, таким образом, риск данного проекта в целом ниже среднего риска инвестиционного портфеля фирмы. Результаты вероятностного анализа показывают, что шанс получить отрицательную величину NPV не превышает 10%. Еще больший оптимизм внушают результаты анализа распределения чистых поступлений от проекта NCF. Величина стандартного отклонения здесь составляет всего 59% от среднего значения. Таким образом, с вероятностью 90% можно утверждать, что поступления от проекта будут положительными величинами.

     Сумма всех отрицательных значений NPV в полученной генеральной совокупности (ячейка F14) может быть интерпретирована как чистая стоимость неопределенности для инвестора в случае принятия проекта. Аналогично сумма всех положительных значений NPV (ячейка F15) может трактоваться как чистая стоимость неопределенности для инвестора в случае отклонения проекта. Несмотря на всю условность этих показателей, в целом они представляют собой индикаторы целесообразности проведения дальнейшего анализа.

     В данном случае они наглядно демонстрируют несоизмеримость суммы возможных убытков по отношению к общей сумме доходов (-1792572,7 и 166597101 соответственно).

     На практике одним из важнейших этапов анализа результатов имитационного эксперимента является исследование зависимостей между ключевыми параметрами. Количественная оценка вариации напрямую зависит от степени корреляции между случайными величинами. Здесь мы ограничимся визуальным (графическим) исследованием. Для этого построим график распределения значений ключевых параметров V, P и Q на основании 75 имитаций (рисунок 3.4).

     Нетрудно заметить, что в целом, вариация значений всех трех параметров носит случайный характер, что подтверждает принятую ранее гипотезу о их независимости. Для сравнения также построим график распределений потока платежей NCF и величины NPV (рисунок 3.5).

Рисунок 3.4 –Распределение значений параметров V, P и Q

Рисунок 3.5 -  Зависимость между NCF и NPV

     Как и следовало ожидать, направления колебаний здесь в точности совпадают и между этими величинами существует сильная корреляционная связь, близкая к функциональной.  Дальнейшие расчеты показали, что величина коэффициента корреляции между полученными распределениями NCF и NPV оказалась равной 1.

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

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