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

среднее — математическое ожидание случайной величины Е\ станд_о*гкл — стандартное отклонение.

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

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

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

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

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

Сформируйте данный шаблон и сохраните его на магнитном диске под именем SIMUL_1. XLT.

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

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

Ввести значения диапазонов изменений ключевых переменных (табл. 6.1) в ячейки ВЗ . С5 листа Имитация. 3.

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

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

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

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

Рассмотрим реализацию выделенных шагов более подробно.

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

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

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

Поскольку первая и последняя строки блока уже определены, число вставляемых строк равно 500 — 2 = 498.

в ячейке Е7 (508). Например, в качестве адреса перехода может быть указана ячейка А508. ІШ

Пеоеход Церейги к. ?Аморт ?-М Количество Налог Нач_инвест Норма Перем_раск 11ост_расх Поступления - - СрОг, Цп |а508| GK

Рис. 6.3. Окно диалога Переход

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

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

Выделите и скопируйте в буфер блок ячеек А10 .ЕЮ. 2.

Нажмите комбинацию клавиш [CTRL] + [SHIFT] + [1] . 3.

Нажмите клавишу [ENTER] . 4.

Нажмите клавишу [F9]* .

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

Этот пункт выполняется в том случае, если был установлен режим ручного пересчета

Необходимо все время помнить о случайной природе эксперимента Полученные вами результаты будут отличаться от приведенных щ А 0 г 1 П І _ Г _ . 'Т! 1 Исходные условия эксперимента г Минимум Максимум 3 4

5

Перем, расходы Количество Цена 25 150 40 35 300 55 О

да t Экспериментов - son Номер стр. - № р-

9 Переменные расходы (V) Количество

(Q) Цена

(Р) Поступления

[NCFt) ЧСС (NFVt) 10 29 288 52 2509,60 7513,3 f 1? 31 202 51 1476,00 3595,2U 12 28 200 46 1300,00 2928,02 13 35 297 54 2117,20 6025,85 14 32 260 40 692ДЮ 623,22 1? 31 229 42 867,60 12Ч8.8С 1 ?5 243 53 ?81,60 1786,31 1/ 25 213 40 1138,00 2313,92 1

— 32 225 41 670,00 539,83 IT 27 279 47 2092,00 5930,3- 20 26 297 ' 2354,8Г 6926,54 *

1 нгі І НІ ад 4 Имитация - * Oil" "I 1

Рис. 6.4. Результаты имитации

Сравним полученные результаты с данными анализа по методу сценариев, проведенного в гл. 5 (рис. 5.14).

Нетрудно заметить, что по результатам имитационного анализа риск проекта значительно ниже. Величина ожидаемой NPV меньше результата предыдущего анализа (3361,96 и 4502,30 соответственно). Однако величина стандартного отклонения также существенно ниже (2271,31 и 4673,62) и не превышает значения NPV. Коэффициент вариации (0,68) меньше 1, таким образом риск данного проекта в целом ниже среднего риска инвестиционного портфеля фирмы. Результаты вероятностного анализа показывают, что шанс получить отрицательную величину NPV не превышает 1%. Еще больший оптимизм внушают результаты анализа распределения чистых поступлений от проекта NCF. Величина стандартного отклонения здесь составляет всего 42% среднего значения. Таким образом, с вероятностью более 90% можно утверждать, что поступления от проекта будут положительными величинами. — ft tmm.t л Ь < * Имитационный анализ (Метод Монте-Карло) 1 Распределение с равными вероятностями Начальные инвест.

ч 1 иэртизация А) 100Д/1 Срок (п) 5,00 Показатели Переменные Количество Цена Поступления hPV в (VI (Q1 (Р1 fNCFt) ? Среднее значение 29,93 223,72 47 32 1414,47 ізкіда Ё Стандарт, отклонение 3,14 5,53 ' ,66 599.17 2271,31 и Коэф. вариации Р.1С 0.20 0,10 0.42 1Я и линимум 25Д; 150,00 40,00 i^6,i)0 Максимум 35,on ЗООДЮ 35,00 3224,00 10221.5Г Число случаев NPV < 0 14 Сумма убытко- -11691,9? і ~умм ДОХОДОи 1fiJVfibJ_7fi Я» 17 "ероятностк p(NPV<-X) Величина (X) Чормал. (X) p(NPV :?Х) 1,48 и,и/ і 1Л *43НРЯБ> Результаты анализа - щшшшш BUfM J *І Рис. 6.5. Результаты анализа

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

В данном случае они наглядно демонстрируют несоизмеримость суммы возможных убытков по отношению к общей сумме доходов (—11 691,92 и 1 692 669,76 соответственно).

На практике одним из важнейших этапов анализа результатов имитационного эксперимента является исследование зависимостей между ключевыми параметрами. Как показано в гл. 5,. количественная оценка вариации напрямую зависит от степени корреляции между случайными величинами. Методы оценки степени зависимости, а также технология ее автоматизации путем применения специальных инструментов ППП EXCEL будут продемонстрированы ниже. Здесь же мы ограничимся визуальным (графическим) исследованием. На рис. 6.6 приведен график распределения значений ключевых параметров V, Р и Q, построенный на основании 75 имитаций.

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

^Переменные расходы (V) -а—Количество (Q) —Цена (Р)

Зависимость методу V, Р, Q

—Поступления (NCR) — » ЧСС (NPVt)

Зависимость между NCF и NPV

Рис 6.1. Зависимость между NCF и NPV

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

Ф Имитация с инструментом