Генератор случайные чисел

Для демонстрации техники применения этого инструмента изменим условия примера 6.1, определив вероятности для каждого сценария развития событий следующим образом (табл. 6.8). Мы также будем исходить из предположения о нормальном распределении ключевых переменных. Количество имитаций оставим прежним — 500.

Таблица 6.8. Вероятностные сценарии реализации проекта Показатели Сценарий наихудший Р = 0.25 наилучший Р = 0.25 вероятный Р = 0.5 Объем выпуска Q 150 300 200 Цена за штуку Р 40 55 50 Переменные затраты V 35 25 30

Приступим к формированию шаблона. Как и в предыдущем случае, выделим в рабочей книге два листа: Имитация и Результаты анализа.

Формирование шаблона целесообразно начать с листа Результаты анализа (рис. 6.8.).

I

і ї::

Имитационный анализ уМетод Монте-Карло) Нормальное Начг ~ьные инвест. (I) floLT. расходы (F) Амортизация (А)

Норм; г Налог (Т) Срок (п) Показатели Переменные Количество Цена Поступления ЧСС 6 М (G) IP) (NCFt) (NPV) ?

ОДЕШ

#ДЁЛЛ)!

ШЕЛ/0!

ЗДЕП/0!

bjjj 0,00

#ДЕЛ/0! 0,00 0,00

а

Среднее значение Стандарт отклонение Киэф. вариации Минимум Максимум

Число случаев NPV < О Сумма убытков Сумма дохода

0,00 0,00 «ДЕЛ/D! 9,00 0,00 0,00 0.00 000

#ДЕПЛ)! #ДЕЛЛ)! «ДЕЛЯ! 0,00 ОД)

%

ЯД^Л/О! 0,00 0,00

Ж

О.ОГ 0,0"

і

13

14

j

16 II

«ДЕЛІ!

!ДЕГЛГ

ДЕЛУ", ДЕЛЛ)

#ДГГМ)1 «ДЕГШ! 'ДЕЛ/Ц! «ДЕ Л/01

«ЧИСЛО! " ІИСЛО! «ЧИСЛО! «ЧИСЛО!

«ДЕЛ/ПІ *ДЕЛДИ гтДеЛЛЛ «ДЕЛ/ПІ

18

К

max) М(Е»

20

С'(Е с- 0) P(t<-I ИН(ЕД (М(Е) * a <- Е ~w(E) Е <-

ЙІІЦ1 '< Резяяьтаты і

«ЧІИСЛО! «ЧІКГЛС! ЧИСПО! «?Ж ,10! «І

J ыГ Рис.

Таблица 6.9. Формулы листа Результаты анализа

(шаблон II) Ячейка Формула В17 =НОРМРАСП(0;В8;В9;1) В18 =НОРМРАСП (ВИ ;В8 ;В9 ; 1) В19 =НОРМРАСП(В12;В8;В9;1)-НОРМРАСП(В8+В9;В8;В9;1) В20 =НОРМРАСП(В8;В8;В9;1)-НОРМРАСП(В8-В9;В8;В9;1) С17 =НОРМРАСП(0;С8;С9;1) С18 =НОРМРАСП(С11;С8;С9;1) С19 =НОРМРАСП(С12/С8;С9;1)-НОРМРАСП(С8+С9;С8;С9;1) С20 =НОРМРАСП(С8;С8;С 9;1)-НОРМРАСП(С8-С9;С8;С9;1) D17 =НОРМРАСП(0;D8;D 9;1) D18 =НОРМРАСП(D11;D 8;D 9;1) D19 =НОРМРАСП(D12;D8;D9;1)-НОРМРАСП(D8+D9;D8;D9;1) D20 =НОРМРАСП(D8;D8;D9;1)-НОРМРАСП(D8-D9;D8;D9;1) Е17 =НОРМРАСП(0;Е8;Е9;1) Е18 =НОРМРАСП(E11;E 8;E 9;1) Е19 =НОРМРАСП(E12;E 8;E 9;1)-НОРМРАСП(E8+E9;E8;E9;1) Е20 =НОРМРАСП(E8;E8;E9;1)-НОРМРАСП(E8-E9;E8;E9;1) F17 =НОРМРАСП(0;F8;F9;1) F18 =НОРМРАСП(F11;F8;F9;1) F19 =НОРМРАСП(F12;F8;F9;1)-НОРМРАСП(F8+F9;F8;F9;1) F20 =НОРМРАСП(F8;F8;F9;1)-НОРМРАСП(F8-F9;F8;F9;1)

Используемые в нем собственные имена ячеек также взяты из аналогичного листа предыдущего шаблона (см. табл. 6.7).

Для быстрого формирования нового листа Результаты анализа выполните следующие действия. 1. Загрузите предыдущий шаблон SIMUL_1.XLT и сохраните его под другим именем, например SIMUL 2 . XLT. 2.

Удалите лист Имитация. Для этого установите указатель мыши на ярлычок этого листа и нажмите правую кнопку. Результатом выполнения этих действий будет появление списка операций в виде контекстного меню. Выберите операцию Удалить. Подтвердите свое решение нажатием кнопки [ОК] в появившемся диалоговом окне. 3.

Перейдите в лист Результаты анализа. Удалите строки 17-18. Откорректируйте заголовок ЭТ. 4.

Добавьте формулы из табл. 6.9. Для этого введите соответствующие формулы в ячейки блока В17 .В20 и скопируйте их в блок C17.F20.

Сверьте полученную таблицу с рис. 6.8.

Перейдите к следующему листу и присвойте ему имя Имитация. Приступаем к его формированию (рис. 6.9). У . / " (Г с ~ D • ? Г ґ І

г ИСУОС )ные условия эксперимента

Перем.расх. Количество Цена Вероятногть 3 4

at Минимум

вероятное

Максимум Л

В Спіднєє Отклонение РССЬІЛІ^ «ССЫЛКА! «ССЫЛКА

«ССЫЛКА! ffCLolHKA! «ССЫЛКА! ЇЇ f

ft Экслериментоз ? 5J| JoMep строки • 51/ 1 Переменные расходы Количество Цена Поступления ЧСС ІЗ И IS 0,00

? НШ І ? J ./і

Рис. 6.9. Лист Имитация (шаблон II)

Первая часть этого листа (блок ячеек А1 .ЕЮ) предназначена для ввода исходных данных и расчета необходимых параметров их распределений. Напомним, что нормальное распределение случайной величины характеризуется двумя параметрами - математическим ожиданием (средним) и стандартным отклонением. Формулы расчета указанных параметров для ключевых переменных модели заданы в блоках ячеек В7. D7 и В8. D8 соответственно (см. табл. 6.11). Для удобства определения формул и повышения их наглядности блоку ячеек ЕЗ. Е5 присвоено имя Вероятности (см. табл. 6.10).

Таблица 6.10. Имена ячеек листа Имитация (шаблон II) Адрес ячейки Имя Комментарии Блок ЕЗ:Е5 Вероятности Вероятность значения параметра Блок А13: А512 Перем_расх Переменные расходы Блок ВІЗ:В512 Количество Объем выпуска Блок С13:С512 Цена Цена изделия Блок D13:D512 Поступления Поступления от проекта NCF Блок ЕІЗ:Е512 ЧСС Чистая современная стоимость NPV

Таблица 6.11. Формулы листа Имитация (шаблон II)

Ячейка Формула В7 =СУММПРОИЗВ{ВЗ:В5; Вероятности) В8 {=КОРЕНЬ(СУММПРОИЗВ((ВЗ:В5 - В7)А2; Вероятности) ) } С7 =СУММПР0ИЗВ(СЗ:С5; Вероятности) С8 {=КОРЕНЬ(СУММПРОИЗВ((СЗ:С5 - С7) А2; Вероятности) ) } D7 =СУММПР0ИЗВ(D3:D5; Вероятности) D8 {=КОРЕНЬ(СУММПРОИЗВ((D3:D5 - D7)A2; Вероятности) ) } ЕЮ =В10+13 -1 D13 =(ВІЗ*(С13-А13)-Пост_расх-Аморт)*(1- Налог)+Аморт Е13 =ПЗ(Норма; Срок; -D13) - Нач инвест

Обратите внимание на то, что для расчета стандартных отклонений используются формулы-массивы, правила задания которых рассмотрены в § 5.5.

Ячейки D13.E13 содержат уже знакомые нам формулы для расчета величины потока платежей NCF и его чистой современной стоимости NPV.

Сформируйте элементы оформления листа Имитация, определите необходимые имена для блоков ячеек (табл. 6.10) и задайте требуемые формулы (табл. 6.11). Сверьте полученную ЭТ с рис. 6.9. Сохраните полученный шаблон под именем SIMUL_2 . XLT.

Введите исходные значения постоянных переменных (табл. 6.2) в ячейки В2.В4 и D2.D4 листа Результаты анализа. Перейдите к листу Имитация. Введите значения ключевых переменных и соответствующие вероятности (табл. 6.8). Полученная в результате ЭТ должна иметь вид, показанный на рис. 6.10.

1 в. ^ -О -У.Т'ЛгЕ

Исходные условия эксперимента

Лерем.расх. Количество Цена Вероятность Минимум

Вероятное

Максимум

25 30 35

150

щ

50 55

30 3,54

212,5 54,49

48,75 5,45

Среднее Отклонение 18

500

Номер строки

512

Экспериментов

11 ЧСС

Количество

Цена

Поступления

12

Переменные расходы 13

-140,00

-2530,71 14 15

- j л?

>ЛП

16

.. Имитация Лиerfjjlj Рис. 6.10. Лист Имитация после ввода исходных данных

Установите курсор в ячейку А13. Приступаем к проведению имитационного эксперимента. 1.

Выберите в главном меню тему Сервис пункт Анализ данных. Результатом выполнения этих действий будет появление диалогового окна Анализ данных, содержащего список инструментов анализа. 2.

Выберите из списка Инструменты анализа пункт Генерация случайных чисел и нажмите кнопку [ОК] (рис. 6.11). 3.

На экране появится диалоговое окно Генерация случайных чисел. Укажите в списке Распределения требуемый тип — Нормальное. Заполните остальные поля изменившегося окна согласно рис. 6.12 и нажмите кнопку [ОК]. Результатом будет заполнение блока ячеек А13.А512 (переменные расходы) сгенерированными случайными значениями.

Анализ данных і

№

"3

J

Корреляция Ковариация

3 писательная статистика Экспоненциальное сглаживание Доухвы^трочн^ й F-тест для дисперсии Анализ Фурье I стограмме. d

и гегсеь иль -JL. Л І

Рис. 6.11. Выбор инструмента Генерация случайных чисел

Приведем необходимые пояснения. Первым заполняемым аргументом диалогового окна Генерация случайных чисел является поле Число переменных. Оно задает количество колонок ЭТ, в которых будут размещаться сгенерированные в соответствии с заданным законом распределения случайные величины. В нашем примере оно должно содержать I, так как ранее мы отвели под значения переменной V (переменные расходы) в ЭТ одну колонку — А. В случае, если указывается число больше 1, случайные величины будут размещены в соответствующем количестве соседних колонок, начиная с активной ячейки. Если это число не введено, то все колонки в выходном диапазоне будут заполнены. Генерация случайных чисел

Число переменных |1

Ч ислосітучайньйі чисея »iOG

Распределение: | Нормальное

^->1 |х|

Отмена |

Спраека [ Параметры

Среднее^ |3D

Стандартное уклонение » р 54 іерем_расх

Случайное рассеивание" Параметры вывода

Вщвдной шившая f Новый ра0ОЧИЙ wx. і" Новая рабочая книга Рис 6.12. Заполнение полей окна Генерация случайных чисел

Следующий обязательный аргумент для заполнения — содержимое поля Число случайных чисел (количество имитаций). Согласно условиям примера оно должно быть равно 500 (см. рис. 6.12). При этом ППП EXCEL автоматически подсчитывает необходимое количество ячеек для хранения генеральной совокупности.

Необходимый вид распределения задается путем соответствующего выбора из списка Распределения. Как уже отмечалось ранее, могут быть получены семь наиболее распространенных в практическом анализе типов распределений, каждое из которых характеризуется собственными параметрами. Выбранный тип распределения определяет внешний вид диалогового окна. В рассматриваемом примере выбор типа распределения Нормальное повлек за собой появление дополнительных аргументов — его параметров Среднее и Стандартное отклонение, рассчитанных ранее для исследуемой переменной V в ячейках В7 и В8 листа Имитация. К сожалению, эти аргументы могут быть заданы только в виде констант. Использование адресов ячеек и собственных имен здесь не допускается!

Указание аргумента Случайное рассеивание позволяет при повторных запусках генератора получать те же значения случайных величин, что и при первом. Таким образом одну и ту же генеральную совокупность случайных чисел можно получить несколько раз, что значительно повышает эффективность анализа (сравните с предыдущим шаблоном!) В случае если этот аргумент не задан (равен 0), при каждом последующем запуске генератора будет формироваться новая генеральная совокупность. В нашем примере этот аргумент задан равным 1, что позволит нам оперировать с одной и той же генеральной совокупностью и избежать постоянных перерасчетов ЭТ.

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

® ВЫХОДНОЙ блок ячеек на текущем листе — введите ссылку на левую верхнюю ячейку выходного диапазона, при этом его размер будет определен автоматически и в случае возможного наложения генерируемых значений на уже имеющиеся данные на экран будет выведено предупреждающее сообщение; о новый рабочий лист — в рабочей книге будет открыт новый лист, содержащий результаты генерации случайных величин, начиная с ячейки А1; в новая рабочая книга — будет открыта новая книга с результатами имитации на первом листе.

В рассматриваемом примере для проведения дальнейшего анализа необходимо, чтобы случайные величины размещались в специально отведенные для них блоки ячеек (см. табл. 6.10). В частности, для хранения 500 значений первой переменной ранее был отведен блок ячеек А13.А512. Поскольку для этого блока определено собственное имя — Перем_расх, оно указано в качестве выходного диапазона. При увеличении либо уменьшении количества имитаций необходимо также переопределить и выходные блоки, предназначенные для хранения значений переменных.

Генерация значений остальных переменных Q и Р осуществляется аналогичным образом, путем выполнения шагов 1—3.

Пример заполнения окна Генерация случайных чисел для

переменной Q (количество) приведен на рис. 6.13.

Генерация случайных чисел Г

і

J500

Чмсяо переменных'

Уишю чисел:

Справка [

Распределение /нормальное

-Параметры

Среднее =

Стандартное |?жломение « I

Сяучайноараисеивание. Параметры вывода

оличество

(

І F Выходной интервал; Г Н^рабо» ї Н овая рабочая книга Рис. 6 13 Заполнение полей окна для переменной Q

Для получения генеральной совокупности значений потока платежей и их чистой современной стоимости необходимо скопировать формулы базовой строки (ячейки D13.E13) требуемое число раз (499). С проблемой копирования больших диапазонов ячеек мы уже сталкивались в предыдущем примере. Ее решение осуществляется выполнением следующих действий. 1.

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

Нажмите клавишу [F5]. На экране появится диапоговос окно Переход. 3.

Укажите в поле Ссылка имя блока Поступления и нажмите кнопку [ОК]. Результатом этих действий будет выделение заданного блока. 4.

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

Если в ЭТ был установлен режим ручных вычислений, нажмите клавишу [F9] .

Аналогично копируется формула из ячейки Е13. При этом в поле Ссылка диалогового окна Переход необходимо указать имя блока — ЧСС. Можно также выбрать необходимое имя из списка Перейти к.

Полученные автором результаты решения примера приведены на рис. 6.14 — 6.15. і г

і 2

А в С 1 D 1 Б

Исходные условия эксперимента

Пер4м.расх. Количество Цена Вероятность Минимум 25 150 40 0,25 Вероятное 30 200 50 0.5 Максимум 15 300 55 1,25 Среднее 30 212,5 48,75 Отклонение 3,54 54,49 5,45 Экспериментов = 500 Номер строки = 512 Количество

Цена

Поступления

ЧСС

1 2 5

6

11

Переменные расходы

12

48;Ъ27706 49 950RR328 41,47096875 4R ,65623326 51 96375592

664,54 1669 50 97Р.11 530f36 ?083,07 2656 7f 1554.13 1679,66 1038,74

1277,30 4328,72 17С7.79 10,49 589? 17 8071,22 3891,36 2092,77 1937,64

30.2136453Г 23.^368554 29.12875/1 25 Л5789674 25,66879383 25ЖІ?40Т8

н I 1 > t г*тпьтдты анализа х Имитация,: Лисг& / f | < 1 J »11 /

Рис. 6 14. Результаты имитационного эксперимента (шаблон II) 13

14

15

16

17

1В

19

20 21

33,6014693: 36,92651065 32 ?5799031 137J0895119 169.8921946 226 48010Г4 77 59419759 211.3587947

7-U7R1 Щ 55,5711С22Я 29" ,8uG02Cr 46 J 1563563 1375887 5Т 50616151 W 0688688 Щ4003696! Результаты проведенного имитационного эксперимента ненамного отличаются от предыдущих Величина ожидаемой NPV равна 3412,14 при стандартном отклонении 2556,83. Коэффициент вариации (0,75) несколько выше, но меньше I, таким образом, риск данного проекта в целом ниже среднего риска инвестиционного портфеля фирмы. Результаты вероятностного анализа показывают, что шанс получить отрицательную величину NPV не превышает 9% Общее число отрицательных

значений NPV в выборке составляет 32 из 500 Следовательно, с вероятностью около 91% можно утверждать, что чистая современная стоимость проекта будет больше 0 При этом вероятность того, что величина NPV окажется больше чем M(NPV) + с , равна 16% (ячейка F19) Вероятность попадания значения NPV в интервал \M(NPV) - о, M(NPV)] равна 34% I

а

~Х Имитационный анализ (Метод Монт^-Карло) • Нормальное распределение щ Начальные инвест. (1) 2000,00 Норма г 0,10 3 Пост, расходы (F) 500,00 Налог (Т) 0,60 Амортизация (А) 100,00 Срок (п} 5,00 Показатели Переменные Количество Цена Поступления чсс 6 (VI IQI (Р) (NCFt) (NPVi ? Среднее значение 30,09 214,21 48,44 1427,71 341?,М 91 Стандарт, отклонение 3,61 52,1Я 5,39 67448 2556.83 Щ Коэф. вариации 0.12 0,24 0,11 0,47 0,75 11 Минимум 19,92 60,91 35,40 09,10 1662,23 Максимум 41,87 387,74 65,62 J638.98 П794,6Г 13 Число случаев NPV< 0 32.Н 14 Сумма убытков 15590,05 IS- Сумма доходов 1721662,3? 17 Р(Б < 0) ОДО 0,00 1,00 С.С2 Р,К If P(E<=MMH(F)) 0,00 о,пс 0,01 0 02 ЛДР 19 Р(М(Е) * о <| Е <» max) 0,16 0,16 0,16 0,16 U,1С 20 Р.М(Е) -о - - Е <= М(Е)) 0.34 0,34 0,34 0,34 3,3' ИЗ *1\Ре?а' ' "Ч дна-us ?? —Г ТЖч? ^ ЇЬсгЗ / l]

Рис 6 15. Результаты анализа (шаблон II)