А. К. Любимов в пособии представлены методологические основы преподавания курса «Имитационное моделирование экономических систем»

55 
Приведение случайной переменной 
E
к стандартно распределенной 
величине 
Z
осуществляется с помощью нормализации – вычитания средней и 
последующего 
деления 
на 
стандартное 
отклонение: 
)
(
)
(
E
E
M
E
Z



. 
Следовательно, величина 
Z 
выражается в количестве стандартных отклонений. 
Для вычисления вероятностей по значению нормализованной величины 
Z 
используются специальные статистические таблицы.
В Excel подобные вычисления осуществляются с помощью 
статистических функций НОРМАЛИЗАЦИЯ() и НОРМСТРАСП(): 

НОРМАЛИЗАЦИЯ(x;среднее;стандартное_откл) 
нормализует 
значение x для распределения, характеризуемого средним и 
стандартным отклонением. 

НОРМСТРАСП(z) 
возвращает 
стандартное 
нормальное 
интегральное распределение для значения z. Это распределение 
имеет среднее, равное нулю, и стандартное отклонение, равное 
единице. Данная функция используется вместо таблицы площадей 
стандартной нормальной кривой. 
Эти функции заданы в ячейках K21 и L21. В ячейке L21 отображается 
вероятность того, что чистый приведенный доход будет меньше некоторого 
значения X. В примере, отображаемом на рис. Х, риск получить отрицательную 
величину чистого приведенного дохода не превышает 8%. Сумма всех 
отрицательных значений NPV в полученной генеральной совокупности (ячейка 
L17) может быть интерпретирована как чистая стоимость неопределенности 
для инвестора в случае принятия проекта. Аналогично сумма всех 
положительных значений NPV (ячейка L18) может трактоваться как чистая 
стоимость неопределенности для инвестора в случае отклонения проекта. 
Несмотря на всю условность этих показателей, в целом они представляют 
собой индикаторы целесообразности проведения дальнейшего анализа.
2. Имитационное моделирование с применением инструмента «Генерация 
случайных чисел» 
Инструмент «Генерация случайных чисел» применяется для заполнения 
диапазона случайными числами, извлеченными из одного или нескольких 
распределений. С помощью этой процедуры можно моделировать объекты, 
имеющие случайную природу, по известному распределению вероятностей. 
Использование этого инструмента возможно после установки надстройки 
«Анализ данных» (см. Приложение 1). 
Рассмотрим ранее описанный пример. Также предположим, что 
распределение ключевых переменных является нормальным. Создайте копию 
листа «Лр 3. Формулы», переименуйте его в «Лр 3. Генерация», очистите 
диапазоны $A$11:$A$510, $B$11:$B$510 и $C$11:$C$510, а также внесите 
соответствующие изменения согласно рис. 16 и описанию ниже. 

56 
Рис. 16. Лист «Лр 3. Генерация» 
Как следует из рис. 16 этот лист практически соответствует ранее 
разработанному для решения предыдущей задачи (см. рис. 14). Отличие 
составляют лишь формулы для расчета вероятностей, которые приведены в 
табл. 20 и небольшие дополнения к таблице со сценариями (вероятности, 
средние и отклонения), которые необходимы для расчета параметров 
распределений ключевых величин (табл. 21). 
Таблица 20. Формулы для расчета вероятностей листа «Лр 3. Генерация» 
Показате
ли 
Переменные 
затраты (
V
) 
Объем 
выпуска (
Q
) 
Цена за штуку 
(
P
) 
Поступления 
(
NCF
) 
Чистый 
приведенный 
доход (
NPV
) 
P(E<=0) 
=НОРМРАСП
(0;I11;I12;1) 
=НОРМРАСП(
0;J11;J12;1) 
=НОРМРАСП(0
;K11;K12;1) 
=НОРМРАСП(0
;L11;L12;1) 
=НОРМРАСП(0;
M11;M12;1) 
P(E<=min
(E)) 
=НОРМРАСП
(I14;I11;I12;1) 
=НОРМРАСП(
J14;J11;J12;1) 
=НОРМРАСП(K
14;K11;K12;1) 
=НОРМРАСП(
L14;L11;L12;1) 
=НОРМРАСП(M
14;M11;M12;1) 
P(M(E)+s
<=E<=ma
x) 
=НОРМРАСП
(I15;I11;I12;1)-
НОРМРАСП(I
11+I12;I11;I12;
1) 
=НОРМРАСП(
J15;J11;J12;1)-
НОРМРАСП(J
11+J12;J11;J12
;1) 
=НОРМРАСП(K
15;K11;K12;1)-
НОРМРАСП(K1
1+K12;K11;K12;
1) 
=НОРМРАСП(
L15;L11;L12;1)-
НОРМРАСП(L
11+L12;L11;L12
;1) 
=НОРМРАСП(M
15;M11;M12;1)-
НОРМРАСП(M1
1+M12;M11;M12
;1) 
P(M(E)-
s<=E<=M
(E)) 
=НОРМРАСП
(I11;I11;I12;1)-
НОРМРАСП(I
11-
I12;I11;I12;1) 
=НОРМРАСП(
J11;J11;J12;1)-
НОРМРАСП(J
11-
J12;J11;J12;1) 
=НОРМРАСП(K
11;K11;K12;1)-
НОРМРАСП(K1
1-
K12;K11;K12;1) 
=НОРМРАСП(
L11;L11;L12;1)-
НОРМРАСП(L
11-
L12;L11;L12;1) 
=НОРМРАСП(M
11;M11;M12;1)-
НОРМРАСП(M1
1-
M12;M11;M12;1) 
Обратите внимание на то, что для расчета стандартных отклонений 
используются формулы-массивы, то есть формулы, при помощи которых 
можно выполнять различные вычисления с одним или несколькими элементами 
в массиве (для ввода таких формул в рабочих книгах используется сочетание 
клавиш CTRL+SHIFT+ВВОД). 

57 
Таблица 21. Формулы для расчета параметров распределения листа «Лр 3. Генерация» 
Показатели 
Среднее 
Отклонение 
Объем выпуска – Q 
=СУММПРОИЗВ(B3:D3;$B$6:$D$6) 
{=КОРЕНЬ(СУММПРОИЗВ((B3:D3-
E3)^2;$B$6:$D$6))} 
Цена за штуку – P 
=СУММПРОИЗВ(B4:D4;$B$6:$D$6) 
{=КОРЕНЬ(СУММПРОИЗВ((B4:D4-
E4)^2;$B$6:$D$6))} 
Переменные затраты – 
V 
=СУММПРОИЗВ(B5:D5;$B$6:$D$6) 
{=КОРЕНЬ(СУММПРОИЗВ((B5:D5-
E5)^2;$B$6:$D$6))} 
Функция 
НОРМРАСП (x;среднее;стандартное_откл;интегральная) 
возвращает нормальную функцию распределения для значения x для 
указанного среднего и стандартного отклонения. Параметр «интегральная» - 
логическое значение, определяющее форму функции. Если аргумент 
«интегральная» имеет значение ИСТИНА, функция НОРМРАСП возвращает 
интегральную функцию распределения; если этот аргумент имеет значение 
ЛОЖЬ, возвращается функция плотности распределения. 
Задайте значения вероятностей: 0,5 для вероятного сценария и по 0,25 для 
каждого из наилучшего и наихудшего сценариев.
Проведение имитационного эксперимента заключается в следующем: на 
вкладке «Данные» в меню «Анализ данных» выберите пункт «Генерация 
случайных чисел». В появившемся диалоговом окне выберите тип 
распределения «Нормальное», заполните остальные поля согласно рис. 17 и 
нажмите кнопку ОК. В результате переменные затраты будут заполнены 
сгенерированными случайными значениями. Аналогично заполните количества 
и цены. 
Рис. 17. Заполнение полей окна «Генератор случайных чисел» 
При заполнении полей окна «Генератор случайных чисел» следует 
обратить внимание, что параметры «Среднее» и «Стандартное отклонение» 

58 
можно задать только в виде констант. Использовать адреса ячеек и собственные 
имена не допустимо! Указание аргумента «Случайное рассеивание», равным 1, 
позволяет при повторных запусках генератора получать те же значения 
случайных величин, что и при первом.
Результаты (рис. 16) проведенного имитационного эксперимента 
ненамного отличаются от предыдущих (рис. 14). Величина ожидаемого чистого 
приведенного дохода (NPV) равна 3555,35 при стандартном отклонении 
2679,49. Коэффициент вариации (0,75) немного выше, чем в предыдущем 
случае, но меньше 1, таким образом, риск данного проекта в целом ниже 
среднего риска инвестиционного портфеля фирмы. Результаты вероятностного 
анализа показывают, что шанс получить отрицательную величину NPV не 
превышает 9%. Общее число отрицательных значений NPV в выборке 
составляет 34 из 500. Таким образом, с вероятностью около 91% можно 
утверждать, что чистый приведенный доход проекта будет больше 0. При этом 
вероятность того, что величина NPV окажется больше чем М(NPV)+

, равна 
16% (ячейка M22). Вероятность попадания значения NPV в интервал [М(NPV)-

; М(NPV)] равна 34%. 

жүктеу/скачать 4,53 Mb.

Достарыңызбен бөлісу: