Автоматизація аналізу ризиків із застосуванням сценаріїв

У сучасних табличних процесорах реалізовані спеціальні засоби, які дозволяють створювати і зберігати у вигляді сценаріїв набори вхідних значень, що використовуються для аналізу різних ситуацій. Сценарій в ППП Excel – це множина змінюваних комірок, які зберігаються під вказаним користувачем іменем. Кожному такому набору відповідає своя модель припущень. Це дозволяє прослідкувати, як значення змінюваних комірок впливають на модель в цілому. Для кожного сценарію можна визначити до 32 змінюваних комірок. Як правило, в якості змінюваних використовуються ті комірки, від значень яких залежать ключові формули.

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

Здійсніть завантаження шаблону для аналізу чутливості і заповніть його даними для найбільш ймовірного сценарію розвитку подій (остання графа табл. 5.5). Приступаємо до формування першого сценарію.

1. Виділіть блок комірок, які будуть використовуватися в якості змінюваних (у даному прикладі блок В2.В6).

2. Виберіть на вкладці ДаніЗнаряддя данихАналіз «якщо»Диспетчер сценаріїв і задайте операцію Додати. Результатом виконання вказаних дій буде поява діалогового вікна Додавання сценарію.

3. Введіть ім’я сценарію, наприклад Ймовірний (рис. 5.7). при цьому у полі Змінювані клітинки автоматично буде підставлений виділений на першому кроці блок. У протилежному випадку в це поле необхідно ввести координати вхідного блоку - B2:B6. Поле Примітка заповнюється на розсуд користувача.

4. Натисніть кнопку ОК. На екрані появиться діалогове вікно Значення клітинок сценарію (рис. 5.8), що містить дані виділеного раніше блоку В2.В6. Оскільки вони відповідають найбільш ймовірному розвитку подій, залишимо їх без змін. Натисніть кнопку ОК.

Рис. 5.7. Діалогове вікно Додавання сценарію «Ймовірний»

Рис. 5.8. Діалогове вікно Значення комірок сценарію «Ймовірний»

Щоб сформувати наступний сценарій (наприклад, «найгірший» або «найкращий»), натисніть кнопку Додати і повторіть кроки 2 – 4. Відмінності будуть лише у заданні імені сценарію (крок 3) і значень вхідних комірок (крок 4), в якості яких слід вказати дані із відповідних граф табл. 5.5. Приклад задання сценарію Найгірший наведений на рис. 5.9 – 5.10.

Рис. 5.9. Діалогове вікно Додавання сценарію «Найгірший»

Рис. 5.10. Діалогове вікно Значення комірок сценарію «Найгірший»

Завершивши формування сценаріїв, натисніть кнопку Звіт, у діалоговому вікні, що появилося, вкажіть операцію СтруктураКлітинка результату D10 і натисніть кнопку ОК. ППП Excel автоматично сформує звіт на окремому листі робочої книги і присвоїть йому ім’я Структура сценарію (рис. 5.11).

Рис. 5.11. Звіт по сценаріях

Зверніть увагу на те, що в отриманому звіті комірки колонок E, F, G затемнені. Цим вказується, що їх значення використовуються в сценаріях в якості вхідних (змінюваних). Комірки колонки D показують поточні в даний момент значення змінюваних і наведені у звіті просто для довідки. Останній рядок звіту містить значення результату (критерію NPV) для заданих сценаріїв розвитку подій.

Як слідує із отриманого звіту, чиста теперішня вартість проекту при найбільш несприятливому розвитку подій буде від’ємною (-1259,15). При нормальному (очікуваному) або найбільш сприятливому розвитку подій проект забезпечує отримання додатного NPV (3658,73 і 11950,89 відповідно).

Отримані результати можна вивести на друк або зберегти на комп’ютері. Вони також можуть бути використані для проведення подальшого аналізу – оцінки ймовірнісного розподілу значень критерію NPV.

Перш за все, виконаємо ряд нескладних перетворень над звітом з метою видалення непотрібної інформації і проведення подальших обчислень. Для цього видалимо двічі колонку А, потім колонку В і рядок 1. Присвоїмо листу Структура сценарію будь-яке інше ім’я (наприклад, Аналіз ризиків). Введемо у блок комірок B4.D4 відповідні значення ймовірностей (див. табл. 5.5) і у комірку А4 коментар – «Ймовірності». Змінимо коментар у комірці А11 на «NPV».

Приступимо до проведення ймовірнісного аналізу.

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

(5.6)

Введемо у комірку А14 коментар «Середня очікувана NPV», а у комірку В14 формулу:

=SUMPRODUCT(B4:D4;B11:D11) (Результат: 4502,30).

Відзначимо, що середнє очікуване значення NPV більше за величину, яку ми надіялися отримати у найбільш ймовірному випадку.

Відразу ж визначимо для комірки В14 - Середнє.

Для обчислення стандартного відхилення необхідно попередньо знайти квадрати різниць між середньою очікуваною NPV і множиною її отриманих значень. Введемо у комірку А15 коментар – Квадрати різниць, а у В15 формулу:

=(B11-Середнє)^2 (Результат: 711611,20).

Скопіюємо дану формулу у комірки С15:D15. Оскільки стандартне відхилення дорівнює квадратному кореню із дисперсії, формула для його обчислення у комірці В16 може мати такий вигляд:

=SQRT(SUMPRODUCT(B15:D15;B4:D4)) (Результат: 4746,02).

Введемо у комірку А16 відповідний коментар і визначимо для В16 ім’я – Відхилення. Тепер для обчислення коефіцієнта варіації CV достатньо задати у комірці В17 формулу вигляду:

=Відхилення/Середнє (Результат: 1,05).

Введемо у комірку А17 коментар – Коефіцієнт варіації CV. Отримана таблиця повинна мати наступний вигляд (рис. 5.12).

Рис. 5.12. Перетворений звіт

Таким чином, виходячи із припущення про нормальний розподіл випадкової величини, із ймовірністю біля 70% можна стверджувати, що значення NPV буде знаходитися у діапазоні 4502,30±4746,02.

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

Визначимо ймовірність того, що NPV буде мати нульове або від’ємне значення, тобто: .

Для цього скористаємося відомою із теми 4 функцією NORM.DIST (). Введіть у комірку В18 формулу:

=NORM.DIST(0;Середнє;Відхилення;1) (Результат: 0,17).

Знайдена ймовірність дорівнює 17%. Таким чином, існує приблизно один шанс із шести виникнення збитків. Визначимо ймовірність того, що величина NPV буде меншою за очікувану на 50%.

Введіть у комірку В19 формулу:

=NORM.DIST(Середнє*0,5;Середнє;Відхилення;1) (Результат: 0,32, або 32%).

Визначимо ймовірність того, що величина NPV буде більшою за значення для найбільш сприятливого результату.

Введіть у комірку В20 формулу:

=1-NORM.DIST(C11;Середнє;Відхилення;1) (Результат: 0,06 або 6%)

Кінцевий варіант електронної таблиці наведений на рис. 5.13).

Рис. 5.13. Результати ймовірнісного аналізу

Отримані результати в цілому свідчать про наявність ризику для цього проекту. Незважаючи на те, що середнє значення NPV (4502,30) перевищує прогноз експертів (3658,73), її величина менша за стандартне відхилення. Значення коефіцієнта варіації (1,04) більше за 1, відповідно, ризик даного проекту дещо перевищує середній ризик інвестиційного портфеля фірми.

У випадку, якщо значення стандартного відхилення і коефіцієнту варіації при цьому проекті менше, ніж у інших альтернатив, за інших рівних обставин йому слід віддати перевагу.

В цілому метод сценаріїв дозволяє отримати достатньо наочну картину результатів для різних варіантів реалізації проектів. Він забезпечує менеджера інформацією як про чутливість, так і про можливі відхилення вибраного критерію ефективності. Застосування програмних засобів типу ППП Excel дозволяє значно підвищити ефективність і наочність подібного аналізу шляхом практично не обмеженого збільшення числа сценаріїв, введення додаткових (до 32) ключових змінних, побудови графіків розподілу ймовірностей і т. д.

Разом із тим використання даного методу спрямоване на дослідження поведінки лише результуючих показників (NPV, IRR, PI). Метод сценаріїв не забезпечує користувача інформацією про можливі відхилення потоків платежів та інших ключових показників, що визначають у кінцевому підсумку хід реалізації проекту.

Незважаючи на ряд притаманних для нього обмежень, даний метод успішно застосовується у багатьох розділах фінансового моделювання.

Використовуючи отриману таблицю (рис. 5.13), самостійно визначте ймовірність того, що:

а) величина NPV буде меншою 70% від очікуваної середньої;

б) значення NPV буде більше очікуваної середньої на величину двох стандартних відхилень .

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


Понравилась статья? Добавь ее в закладку (CTRL+D) и не забудь поделиться с друзьями:  



double arrow
Сейчас читают про: