Автоматизація аналізу ризиків із застосуванням сценаріїв
У сучасних табличних процесорах реалізовані спеціальні засоби, які дозволяють створювати і зберігати у вигляді сценаріїв набори вхідних значень, що використовуються для аналізу різних ситуацій. Сценарій в ППП 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, відповідно, ризик даного проекту дещо перевищує середній ризик інвестиційного портфеля фірми.
return false">ссылка скрытаУ випадку, якщо значення стандартного відхилення і коефіцієнту варіації при цьому проекті менше, ніж у інших альтернатив, за інших рівних обставин йому слід віддати перевагу.
В цілому метод сценаріїв дозволяє отримати достатньо наочну картину результатів для різних варіантів реалізації проектів. Він забезпечує менеджера інформаці-ymovirnisnih-rozpodiliv-potokiv-platezhiv.html">Аналіз ймовірнісних розподілів потоків платежів
Дата добавления: 2015-10-22; просмотров: 65; Опубликованный материал нарушает авторские права?.