й Автоматизация анализа рисков с применением сценариев

Таким образом, процесс создания сценариев в ППП EXCEL сводится к определению наборов входных значений. Рассмотрим технику использования сценариев на решении примера 5.3. При этом в качестве базы для определения сценариев можно использовать шаблон, сформированный для анализа чувствительности при решении примера 5.2.

Осуществите загрузку шаблона для анализа чувствительности и заполните его данными для наиболее вероятного сценария развития событий (последняя графа табл. 5.5). Приступаем к формированию первого сценария. 1.

Выделите блок ячеек, которые будут использоваться в качестве изменяемых (в данном примере блок В2 . Вб). 2.

'Выберите в главном меню тему Сервис пункт Сценарии. В появившемся диалоговом окне Диспетчер сценариев задайте операцию Добавить. Результатом выполнения указанных действий будет появление диалогового окна Добавление сценария. 3.

Введите имя сценария, например Вероятный (рис. 5.7). При этом в поле Изменяемые ячейки автоматически будет подставлен выделенный на первом шаге блок. В противном случае в это поле необходимо ввести координаты входного блока — $В$2.$В$6. Поле Примечание заполняется по усмотрению пользователя. 4.

Нажмите кнопку [ОК]. На экране появится диалоговое окно Значения ячеек сценария (рис.

Чтобы сформировать следующий сценарий (например, "наихудший" или "наилучший"), нажмите кнопку [Добавить] и повторите шаги 2 — 4. Отличия будут лишь в задании имени сценария (шаг 3) и значений входных ячеек (шаг 4), в качестве которых следует указать данные из соответствующих граф табл. 5.5. Пример задания сценария Наихудший приведен на рис. 5.9—5.10. ІШ

Значения яиеек сценария 1

Ok

Отк&но | Доёа^игь |

Вьедиги w. .ачь.іия каждом изгиемяь.-.зй ячейки f oti вств^ | 00

Цена

Го"

Норма Сргж Рис 5.8. Диалоговое окно Значение ячеек сценария вероятный'

в— — -і і

ОК

H.S3L іииє сценарии. (Най^дшии

Отмена j

Изменяемые ячейки:

ІівіГІРІб

}ГОбЬ1 ДО&ЭШИК НвСЬврЭДШЗ «ЗМЄНЯМ»^?

ячейку, укажк. ^ fee rtpi наз$Яго& йяаемше' См Примечание- втор ВЗ'ТЗ И114.03 97 W Защите

Ш

f? ения Г Скрьш? Рис 5 9 Диалоговое окно Добавление сценария 'Наихудший"

иищпанн і

OR

Отмена |

бегите значений каждом vjMeHт їй ячейка X Ксгчесгк: ^50 .1 Цен? W~ До^аи- < tjjj

Э. lepef цасэ 4 Норм* 015 Срок

Рас 5.Ю. Диалоговое окно 'Значение ячеек сценария "'Наихудший "

Завершив формирование сценариев, нажмите кнопку Отчет (Итоги)1, в появившемся диалоговом окне укажите операцию

В скобках здесь и датее указаны обозначения, используемые в ППП EXCEL версии 5 0

Структура (Итоги сценария) и нажмите кнопку [ОК]. ППП EXCEL автоматически сформирует отчет на отдельном листе рабочей книги и присвоит ему имя Структура сценария (Итоги сценария, рис. 5.11). !і І- А. С в С Aj 6 «Г і % Струкгуоа сценария г/ Теп ІДИ и ЭНпЧеНИЧ' Ніип мший Варсчти і? НПИ";ДЫ1"'1 % Л | і Количестве 35Щ> эеде щеи Iі Цей 50J00 Шф в г ЛІврямjht зад) 25 Д* nuu зідо 9 nif> 010 Г.1 b.15 10 Срок sen S Off 5 f 700 Ячейки результате: п $мэ 365Р73 11Э5ПЧ9 3653 73 1259.15 13 Примечании столбец 'екущие значения" представляет значения изменяемых ячеек е 14 момент создания Итогового отчета по Сценарию Изменрнмые ячейки для каждого ш сценария выделены серым цветом

Рис.

Обратите внимание на то, что в полученном отчете ячейки колонок Е, F, G затенены. Этим указывается, что их значения используются в сценариях в качестве входных (изменяемых). Ячейки колонки D показывают текущие в данный момент значения изменяемых переменных и приведены в отчете просто для справки. Последняя строка отчета содержит значения результата (критерия NPV) для заданных сценариев развития событий.

Как следует из полученного отчета, чистая приведенная стоимость проекта при наиболее неблагоприятном развитии событий будет отрицательной (—1259,15). При нормальном (ожидаемом) или наиболее благоприятном развитии событий проект обеспечивает получение положительной NPV (3658,73 и 11950,89 соответственно).

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

Прежде всего, выполним ряд несложных преобразований над отчетом в целях удаления ненужной информации и проведения дальнейших вычислений. Для этого удалим два раза колонку А, затем колонку В и строку 1. Присвоим листу Структура сценария (Итоги сценария) какое-нибудь другое имя (например, Анализ рисков). Введем в блок ячеек B4.D4 со- ответствующие значения вероятностей (см. табл. 5.5) и в ячейку А4 комментарий — "Вероятности". Изменим комментарий в ячейке АН на "NPV".

Приступим к проведению вероятностного анализа. Прежде всего определим среднее ожидаемое значение NPV. Для этого можно использовать соотношение:

M(NPV) = j^PiNPVi. (5.6)

Введем в ячейку А14 комментарий "Средняя ожидаемая NPV", а в ячейку С14 формулу:

=СУММПР0ИЗВ (В4 : D4; BllrDll) (Результат: 4502,30).

Отметим, что среднее ожидаемое значение NPV больше величины, которую мы надеялись получить в наиболее вероятном случае.

Следуя правилам хорошего тона, сразу же определим для ячейки В14 имя — Среднее.

Для вычисления стандартного отклонения <х необходимо предварительно найти квадраты разностей между средней ожидаемой NPV и множеством ее полученных значений. Введем в ячейку АІ5 комментарий — Квадраты разностей, а в В15 формулу: = (В11 - Среднее) Л2 (Результат: 711611,20).

Скопируем данную формулу в ячейки C15.D15.

=КОРЕНЬ (СУММПРОИЗВ (В15. D15 ;В4 .D4)) (Результат: 4673,62).

Введем в ячейку А16 соответствующий комментарий и определим для В16 имя — Отклонение. Теперь для вычисления коэффициента вариации СУ достаточно задать в ячейке В17 формулу вида:

=Отклонение / Среднее (Результат: 1,04).

Введем в ячейку А17 комментарий — Коэффициент вариации

CV. Полученная таблица должна иметь следующий вид (рис. 5.12).

! Более эффективным способом реализации подобных расчетов является использование массивов EXCEL. А 1 8 J С , .1 Р ... D і 1

г Сценарии Наилучший Вегоятный Наия лчіий 4 Вероятности 0,25 0,5 0,25 5 . 7 В 9 Количества Цена

Перем расх

Норма

Срок 300,00 55,00 25,00 0,08 5,00 200,00 50,00 ЗОД) 0,10 5,00 150,00 40,00 35,00 0,15 7,00 10 11 NPV 11950,89 3658,73 -1259,1.5 12 13

14

15

16

17 16 Средняя NPV Квадраты разностей Отклонение о Коэф вари^циь П/ 4502,30 711611,20 4673,62 1,04 33194291,33 20270736,42 ib М Ш^™?^.™*/ЯжйИ тш» J ЛЬ

ап

Рис. 5.12. Преобразованный отчет

Таким образом, исходя из предположения о нормальном распределе1 нии случайной величины, с вероятностью около 70% можно утверждать, что значение NPVбудет находиться в диапазоне 4502,30 + 4673,62.

Зная основные характеристики распределения NPV, можно приступать к проведению вероятностного анализа.

Определим вероятность того, что NPV будет иметь нулевое или отрицательное значение, т.е.: p(NPV< 0).

Для этого воспользуемся известной из материала гл. 4 функцией НОРМРАСП (). Введите в ячейку В18 формулу:

=НОРМРАСП (О ;Среднее;Отклонение; 1) (Результат: 0,17).

Найденная вероятность равна 17%. Таким образом, существует приблизительно один шанс из шести возникновения убытков. Определим вероятность того, что величина Л'РКбудет меньше ожидаемой на 50%.

Введите в ячейку В19 формулу:

=НОРМРАСП (Среднее * 0 ,5; Среднее; От клонение; 1) (Результат: 0,32, или 32%).

Определим вероятность того, что величина NPV будет больше значения для наиболее благоприятного исхода.

Введите в ячейку В20 формулу: =1 -НОРМРАСП (Е11; Среднее; Отклонение; 1) (Результат 0,06, или 6%).

Окончательный вариант электронной таблицы приведен на рис. 5.13.

Полученные результаты в целом свидетельствуют о наличии риска для этого проекта. Несмотря на то, что среднее значение NPV (4502,30) превышает прогноз экспертов (3658,73), ее величина меньше стандартного отклонения. Значение коэффициента вариации (1,04) больше 1, следовательно, риск данного проекта несколько выше среднего риска инвестиционного портфеля фирмы30.

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

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

Л 1 - Ї г В . „ X ГЗ

х; |

2-1 Сценарии Нанг. иііий Вероятный Наихудший .1

Вероятности 0,25 0^5 0,25 5

Ж !

Количество 300.00 200,00 150ДЮ Цена 55Д) 50,00 40Д)0 llepeMjpacx 25,00 3D,00 35,00 Норма 0J18 0,10 0,15 Срок ЦЦЮ 5Д) 7,00

U 11

' PV 11950,89 363 -1259,15 1? ш

л

Л

Н> J?

щ

!

21

&

«|<

Средняя MV 4502,30

Квадраты разностей 711611,20 33194293,33 20270736,42

Отклонение a 4673,Ы

I оэф. вариации CV 1,04

*fMPV<-0? 0,17

y*4PV<= -дні \ 0,32

WV> максимум^) 016

f^NPVx^pedHee * 10%) 0,46

[WV>Cpedwee * 20%) 0,42

Ltlii Ани в риска л ПИСГІ / ЯастЗ/ЩЩЩШ Ы і J

Рис. 5.13. Результаты вероятностного анализа

Вместе с тем использование данного метода направлено на исследование поведения только результирующих показателей {NPV, IRR, РГ). Метод сценариев не обеспечивает пользователя информацией о возможных отклонениях потоков платежей и других ключевых показателей, определяющих в конечном итоге ход реализации проекта.

Несмотря на ряд присущих ему ограничений, данный метод успешно применяется во многих разделах финансового анализа.

Используя полученную таблицу (рис. 5.13), самостоятельно определите вероятности того, что.

а) величина NPVбудет меньше 70% от ожидаемой средней;

б) значение NPV будет больше ожидаемой средней на величину двух стандартных отклонений ( NPV> Среднее + 2а).

Дайте объяснения полученным результатам.