Реферат: Міністерство освіти І науки україни харківська національна академія міського господарства

МІНІСТЕРСТВО ОСВІТИ І НАУКИ УКРАЇНИ

ХАРКІВСЬКА НАЦІОНАЛЬНА АКАДЕМІЯ МІСЬКОГО ГОСПОДАРСТВА


До друку

дозволяю

Перший проректор


_____________ Г. В.Стадник


МЕТОДИЧНІ ВКАЗІВКИ

до виконання лабораторної роботи

«ІМІТАЦІЙНЕ МОДЕЛЮВАННЯ ІНВЕСТИЦІЙНИХ РИЗИКІВ»

з дисципліни «Інформаційні системи в менеджменті»


(для студентів 4 курсу усіх форм навчання спеціальності 7.050201 «Менеджмент організацій»)


ХАРКІВ—ХНАМГ— 2006

Методичні вказівки до виконання лабораторної роботи «Імітаційне моделювання інвестиційних ризиків» з дисципліни «Інформаційні системи в менеджменті» (для студентів 4 курсу усіх форм навчання спеціальності 7.050201 «Менеджмент організацій»). Укл.: Карпенко М.Ю., Уфимцева В.Б., Грінчак М.І. , Волков Д.О., Гомза Н.І. — Харків: ХНАМГ, 2006.—38 с.


Укладачі: М.Ю. Карпенко,

В.Б. Уфимцева,

М.І. Грінчак,

Д.О. Волков,

Н.І. Гомза


Рецензент: канд. екон. наук, доц. В.О. Костюк


Рекомендовано кафедрою «Прикладна математика та інформаційні технології», протокол № 5 від 27 грудня 2005 р.
^ Мета роботи
Мета виконання лабораторної роботи — вивчення методів та інструментів дослідження інвестиційних ризиків з використанням засобів імітаційного моделювання.
^ Етапи виконання роботи
Робота складається з таких етапів:

ознайомлення з теоретичним матеріалом;

вивчення моделі аналізу інвестиційних ризиків;

опанування інструментом реалізації моделі аналізу інвестиційних ризиків;

одержання індивідуальних даних у викладача та реалізація моделі аналізу інвестиційних ризиків;

аналіз моделі та написання звіту.
Вступ
Імітаційне моделювання (simulation) є одним із потужних методів аналізу економічних систем. Під імітацією розуміють процес проведення на ЕОМ експериментів з математичними моделями реальних систем. Цілі проведення імітаційних експериментів можуть бути різними — від виявлення властивостей та закономірностей досліджуваної системи до вирішення конкретних практичних завдань. З розвитком засобів обчислювальної техніки та програмного забезпечення спектр застосування імітації у сфері економіки істотно розширився. Імітаційне моделювання з успіхом використовують як для розв'язання завдань внутрішньофірмового управління, так і для моделювання процесів управління на макроекономічному рівні. Розглянемо основні переваги застосування імітаційного моделювання у процесі вирішення завдань фінансового аналізу.

Оскільки імітація — це комп'ютерний експеримент, її головна відмінність полягає в роботі з моделлю системи, а не з самою системою. Тому імітаційне моделювання доцільно в тих випадках, коли робота з реальною системою ускладнена або взагалі неможлива. Наприклад, проведення реальних експериментів з економічними системами, як правило, вимагає значних витрат і навряд чи може бути здійснено на практиці. У такому випадку імітація виглядає єдиним способом дослідження систем без проведення реальних експериментів.

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

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

Існує багато інших переваг імітації. Докладний виклад основ імітаційного моделювання і його застосування в різних сферах можна знайти у відповідній літературі. Ми ж розглянемо технологію застосування імітаційного моделювання для аналізу ризиків інвестиційних проектів з використанням програми MS Excel.
^ Розробка моделі для аналізу ризиків інвестиційних проектів Загальний вигляд моделі
Імітаційне моделювання — це серія експериментів, що мають на меті одержати емпіричні оцінки ступеня впливу вхідних величин (факторів) на залежні від них результати (показники). Проведення імітаційного експерименту можна розбити на етапи, а саме;

встановити модель, що пов'язує вхідні й вихідні показники у вигляді математичного рівняння або нерівності;

завдання законів розподілу ймовірностей для ключових параметрів моделі;

проведення комп'ютерної імітації значень ключових параметрів моделі;

розрахунок основних характеристик розподілу вхідних і вихідних показників;

аналіз отриманих результатів і прийняття відповідних рішень.

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

Фірма розглядає інвестиційний проект з виробництва продукту "А". Експериментально були виявлені три ключових параметри проекту й визначені можливі межі їх змін (табл. 1). Інші параметри проекту вважаються сталими (табл. 2)

Таблиця 1. Ключові параметри проекту


Показники

Сценарій

Найгірший

Найкращий

Ймовірний

Обсяг випуску Q

150

300

200

Ціна за штуку P

40

55

50

Змінні витрати V

35

25

30

Таблиця 2. Сталі параметри проекту

Показники

Найбільш ймовірне значення

Постійні витрати - F

500

Амортизація - A

100

Податок на прибуток - T

60%

Норма дисконту - r

10%

Термін проекту - n

5

Початкові інвестиції - I0

2000



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

, (1)

де: NCFt – значення чистого потоку платежів у періоді t.

За умовами прикладу значення норми дисконтування r і початковий обсяг інвестицій I0 відомі і вважаються сталими протягом строку реалізації проекту (табл. 2). Будемо вважати, що потік платежів, який генерується проектом , має вид ануітету. Тоді величина потоку платежів NCF для будь-якого періоду t однакова і може бути визначена із співвідношення:

. (2)

Наступним етапом проведення аналізу є вибір законів розподілу ймовірностей змінних факторів. У нашому прикладі до таких факторів належать: змінні витрати V, обсяг випуску Q і ціна P. Діапазони можливих змін показників наведено у табл. 1. Ми будемо виходити з припущення, що всі ключові змінні фактори мають рівномірний розподіл ймовірностей. Реалізація третього етапу може бути здійснена тільки із застосуванням комп'ютерної техніки з відповідним програмним забезпеченням. Тому перш ніж приступити до третього етапу — імітаційного експерименту, познайомимося з відповідними засобами Excel, що автоматизують його проведення.
^ Технологія імітаційного моделювання засобами MS Excel
Проведення імітаційних експериментів в Excel можна здійснити двома способами: за допомогою вбудованих функцій і використанням інструменту "Генератор случайных чисел"1. Для порівняння нижче розглядаються обидва способи. При цьому основна увага приділена технології проведення імітаційних експериментів і наступного аналізу результатів з використанням інструмента "Генератор случайных чисел".
^ Імітаційне моделювання із застосуванням функцій Excel
Слід зазначити, що застосування вбудованих функцій доцільно лише в тому випадку, коли ймовірності реалізації всіх значень випадкової величини вважаються однаковими. Тоді для імітації значень необхідної змінної можна скористатися математичними функціями СЛЧИС() або СЛУЧМЕЖДУ()2. Формати функцій наведені у табл. 3.

Таблиця 3. Математичні функції для генерації випадкових чисел

Найменування функції

Формат функції

Оригінальна версія

Локалізована версія

RAND

СЛЧИС

СЛЧИС() - не має аргументів

RANDBETWEEN

СЛУЧМЕЖДУ

СЛУЧМЕЖДУ (нижня_границя; верхн_границя)

Функція СЛЧИС() повертає рівномірно розподілене випадкове число E, яке більше або дорівнює 0 і менше 1, тобто: 0<=E<1. Разом з тим, шляхом нескладних перетворень з її допомогою можна одержати будь-яке випадкове речовинне число. Наприклад, щоб одержати випадкове число між a та b, досить задати в будь-якому осередку наступну формулу: =СЛЧИС()*(b-a)+a. Ця функція не має аргументів. Якщо в Excel встановлений режим автоматичного виконання обчислень, то результат функції буде змінюватися щоразу, коли відбувається введення або коригування даних. У режимі ручного обчислення перерахування таблиці здійснюється тільки після натискання клавіші [F9]. Змінити режим виконання обчислень можна через меню СервисПараметрыВычисления.

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

Функція СЛУЧМЕЖДУ(нижн_граница; верхн_граница) 3

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

Наприклад, згенеруємо випадкове значення для змінної Q (обсяг випуску продукту). Згідно з табл. 1, Q приймає значення з діапазону 150 - 300. Формула матиме вигляд: =СЛУЧМЕЖДУ(150; 300).

Якщо задати аналогічні формули для змінних P та V, а також формулу для обчислення NPV і скопіювати їх необхідну кількість разів, можна одержати генеральну сукупність, що містить різні значення вихідних показників та результатів. Після чого, використовуючи статистичні функції, неважко розрахувати відповідні параметри розподілу та провести ймовірнісний аналіз.

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

Вибрати СервисПараметрыВычисления

Установити параметр "Вручную" і натиснути "ОК".

Приступаємо до розробки шаблону. З метою спрощення та підвищення наочності аналізу виділимо для його проведення в робочій книзі Excel два аркуші.

Перший аркуш - "Имитация" призначений для побудови генеральної сукупності (рис. 1). Визначені в даному аркуші формули та власні імена осередків наведені у табл. 4, 5.

Перша частина аркуша (блок осередків А1:Е7) призначена для введення діапазонів змін ключових параметрів, значення яких будуть генеруватися в процесі проведення експерименту. В комірці В7 задається загальне число імітацій (експериментів). Формула, в Е7 обчислює номер останнього рядка вихідного блоку, до якого будуть записані отримані значення. Зміст цієї формули буде розкритий пізніше.



^ Рис. 1 — Аркуш «Имитация»


Таблиця 4. Формули аркуша "Имитация"

Осередок

Формула

Е7

=B7+10-2

A10

=СЛУЧМЕЖДУ($B$3;$C$3)

A11

=СЛУЧМЕЖДУ($B$3;$C$3)

B10

=СЛУЧМЕЖДУ($B$4;$C$4)

B11

=СЛУЧМЕЖДУ($B$4;$C$4)

C10

=СЛУЧМЕЖДУ($B$5;$C$5)

C11

=СЛУЧМЕЖДУ($B$5;$C$5)

D10

=(B10*(C10-A10)-Пост_расх-Аморт)*(1-Налог+Аморт

D11

=(B11*(C11-A11)-Пост_расх-Аморт)*(1-Налог)+Аморт

E10

=ПЗ(Норма;Срок;-D10)-Нач_инвест

E11

=ПЗ(Норма;Срок;-D11)-Нач_инвест

Таблиця 5. Імена осередків аркуша «Имитация»

Адреса осередку

Ім'я

Коментарі

Блок A10:A11

Перем_расх

Змінні витрати

Блок B10:B11

Количество

Обсяг випуску

Блок C10:C11

Цена

Ціна

Блок D10:D11

Поступления

Надходження від проекту NCFt

Блок E10:E11

ЧСС

Чиста нинішня вартість NPV



Друга частина аркуша (блок осередків А9:Е11) призначена для проведення імітації. Формули в осередках А10:З11 генерують значення для відповідних змінних з обліком заданих в осередках В3:З5 діапазонів їхніх змін. Зверніть увагу на те, що нижню та верхню межі змін вказувати треба з абсолютною адресацією.

Формули в осередках D10:E11 обчислюють величину потоку платежів і його чисту сучасну вартість відповідно. При цьому значення постійних змінних беруться з наступного аркуша шаблона - "Результаты анализа".

Аркуш "Результаты анализа", крім значень постійних змінних, містить також функції, що обчислюють параметри розподілу змінюваних (Q, V, P) і результатних (NCF, NPV) змінних та ймовірності різних подій. Визначені для даного аркуша формули та власні імена осередків наведені у табл. 6,7. Загальний вид аркуша показаний на рис. 2.

Таблиця 6. Формули аркуша «Результаты анализа»

Осередок

Формула

B8

=СРЗНАЧ(Перем_расх)

B9

=СТАНДОТКЛОНП(Перем_расх)

B10

=B9/B8

B11

=МИН(Перем_расх)

B12

=МАКС(Перем_расх)

C8

=СРЗНАЧ(Количество)

C9

=СТАНДОТКЛОНП(Количество)

C10

=C9/C8

C11

=МИН(Количество)

C12

=МАКС(Количество)

D8

=СРЗНАЧ(Цена)

D9

=СТАНДОТКЛОНП(Цена)

D10

=D9/D8

D11

=МИН(Цена)

D12

=МАКС(Цена)

E8

=СРЗНАЧ(Поступления)

E9

=СТАНДОТКЛОНП(Поступления)

E10

=E9/E8

E11

=МИН(Поступления)

E12

=МАКС(Поступления)

F8

=СРЗНАЧ(ЧСС)

F9

=СТАНДОТКЛОНП(ЧСС)

F10

=F9/F8

F11

=МИН(ЧСС)

F12

=МАКС(ЧСС)

F13

=СЧЁТЕСЛИ(ЧСС;"<0")

F14

=СУММЕСЛИ(ЧСС;"<0")

F15

=СУММЕСЛИ(ЧСС;">0")

Е18

=НОРМАЛИЗАЦИЯ(D18;$F$8;$F$9)

F18

=НОРМСТРАСП(E18)

^ Таблиця 7. Імена осередків аркуша «Результаты анализа»

Осередок

Ім'я

Коментар

B2

Нач_инвест

Початкові інвестиції

B3

Пост_расх

Постійні витрати

B4

Аморт

Амортизація

D2

Норма

Норма дисконту

D3

Налог

Ставка податку на прибуток

D4

Срок

Строк реалізації пуття




^ Рис. 2 — Аркуш «Результаты анализа»


Оскільки формули аркуша містять ряд нових функцій, наведемо необхідні пояснення.

Функції МИН() і МАКС() обчислюють мінімальне та максимальне значення для масиву даних із блоку осередків, зазначеного як їхній аргумент. Імена та діапазони цих блоків наведені у табл. 7.

Функція СЧЕТЕСЛИ(блок; "умова") знаходить кількість елементів у вказаному блоці, значення яких задовольняють вказаній умові.

Функція в F13 підраховує кількість негативних значень NPV в блоці осередків ЧСС (табл. 7).

Дія функції СУММЕСЛИ(блок;"умова") схожа на функцію СЧЕТЕСЛИ(). Відмінність полягає в тому, що СУММЕСЛИ() підсумовує значення елементів в блоці, якщо вони задовольняють заданій умові. Функції в блоці F14:F15 знаходять суми негативних (F14) і позитивних (F14) значень NPV блоку ЧСС. Зміст цих розрахунків розглянемо пізніше.

Дві останні формули (комірки Е18 та F18) виконують ймовірнісний аналіз розподілу NPV. Цей момент вимагає незначного теоретичного пояснення.

У прикладі ми виходимо з припущення про незалежність і рівномірний розподіл ключових змінних Q, V, P. Визначити, який розподіл при цьому буде мати показник NPV, заздалегідь неможливо. Одне з можливих вирішень цієї проблеми — спробувати апроксимувати невідомий розподіл одним із відомих. Зазвичай для апроксимації використовують функцію нормального розподілу 4.

У прикладному аналізі для цілей апроксимації широко застосовується окремий випадок нормального розподілу — стандартний нормальний розподіл. Математичне очікування стандартно розподіленої випадкової величини Е дорівнює 0: M(E) = 0. Графік цього розподілу симетричний відносно осі ординат. Розподіл характеризується одним параметром — стандартним відхиленням , яке дорівнює 1.

Приведення випадкової змінної E до стандартно розподіленої величини Z здійснюється за допомогою нормалізації, яка полягає у вирахуванні середньої та наступного ділення її на стандартне відхилення:

. (3)

Виходячи з (3), величина ^ Z виражається в кількості стандартних відхилень. Для обчислення ймовірностей за значенням нормалізованої величини Z використовуються спеціальні статистичні таблиці.

В Excel для подібних обчислень використовують статистичні функції НОРМАЛИЗАЦИЯ () і НОРМСТРАСП().

Функція НОРМАЛИЗАЦИЯ(x; среднее; станд_откл) повертає нормалізоване значення Z величини x, на підставі якого потім обчислюється ймовірність p(E x). Вона реалізує співвідношення (3). Функція має три аргументи:

х — значення, що нормалізується;

среднее — математичне очікування випадкової величини ^ Е;

станд_откл — стандартне відхилення.

Отримане значення Z є аргументом для наступної функції НОРМСТРАСП().

Функція НОРМСТРАСП(Z)повертає стандартний нормальний розподіл, тобто ймовірність того, що випадкова нормалізована величина ^ Е буде менше або дорівнюватиме х. Вона має всього один аргумент — Z, що обчислюється функцією НОРМАЛИЗАЦИЯ().

Вказані функції слід використовувати в тандемі. При цьому доцільно використовувати функцію НОРМАЛИЗАЦИЯ() як аргумент функції НОРМСТРАСП(), тобто:

=НОРМСТРАСП(НОРМАЛИЗАЦИЯ (x; среднее; станд_откл)).

Для підвищення наочності у проектованому шаблоні функції задані роздільно (осередки Е18 та F18).

Сформуйте даний шаблон і збережіть його на магнітному диску з іменем SIMUL_1.XLT. Приступаємо до імітаційного експерименту. Для його проведення треба виконати такі кроки.

Ввести значення постійних змінних (табл. 2) в осередки В2:В4 та D2:D4 аркушу "Результаты анализа", діапазони змін ключових змінних (табл. 1) в осередки В3:З5 аркушу "Имитация".

Задати в осередку В7 необхідне число експериментів.

Встановити курсор в осередок А11 і вставити необхідне число рядків у шаблон (номер останнього рядка буде обчислений в Е7).

Скопіювати формули блоку А10:Е10 необхідну кількість разів.

Перейти до аркуша "Результаты анализа" і проаналізувати отримані результати.

Розглянемо реалізацію вказаних кроків докладіше. Виконання перших трьох пунктів не повинне викликати особливих утруднень. Введіть значення постійних змінних в осередки В2:В4 аркуша "Результаты анализа". Введіть значення діапазонів змін ключових змінних в осередки В3:З5 аркуша "Имитация". Вкажіть в осередку В7 число проведених експериментів, наприклад - 500. Зробіть активним комірку А11.

Тепер до шаблону потрібно вставити 498 рядків (перший та останній рядок блоку вже визначені, число рядків, що вставляють, дорівнює: 500 2=498). Однак виділення такої кількості рядків за допомогою миші незручно. На щастя, Excel надає більш ефективні засоби для виконання подібних дій. Зокрема, можна скористатися операцією переходу, що допомагає швидко виділити великий діапазон комірок. Натисніть функціональну клавішу [F5]. На екрані з'явиться вікно діалогу «Переход» (рис. 3). Для переходу до потрібної ділянки електронної таблиці досить вказати у полі «Ссылка» адресу або ім'я відповідної комірки (блоку). У нашому випадку це буде будь-яка адреса комірки в останньому рядку. Її номер (508) міститься у комірці Е7. Наприклад, за адресу переходу ми можемо вказати А508.

Введіть у поле "Ссылка" адресу А508 і натисніть комбінацію клавіш [SHIFT] + [ENTER]. Результатом виконання цих дій буде виділення блоку А11:А508. Далі вставте рядки будь-яким із відомих вам способів.

Заповніть вставлені рядки формулами блоку А10:Е10, а саме:

Скопіюйте до буфера обміну блок А10:Е10.

Натисніть комбінацію клавіш [CTRL] + [SHIFT] + [ ].

Натисніть клавішу [ENTER].

Якщо встановлено режим ручного перерахування таблиці, натисніть [F9] .

У результаті цих дій Excel заповнить блок А10:Е509 випадковими значеннями ключових змінних V, Q, P і результатами обчислень NCF та NPV. Приклад імітації наведено на рис. 4, відповідні результати аналізу наведені на рис. 55.



^ Рис. 4 — Результати імітації



Рис. 5 — Результати аналізу


Прокоментуємо результати ймовірнісного аналізу. Вони показують, що шанс одержати негативну величину NPV не перевищує 7%. Ще більший оптимізм вселяють результати аналізу розподілу чистих надходжень від проекту NCF. Величина стандартного відхилення тут становить усього 42% від середнього значення. У такий спосіб із ймовірністю більше 90% можна стверджувати, що надходження від проекту будуть позитивними величинами.

Сума всіх негативних значень NPV в отриманій генеральній сукупності (комірка F14) може бути інтерпретована як чиста вартість невизначеності для інвестора у випадку прийняття проекту. Аналогічно сума всіх позитивних значень NPV (комірка F15) може трактуватися як чиста вартість невизначеності для інвестора у випадку відхилення проекту.

Незважаючи на певну умовність цих показників, у цілому вони виступають індикаторами доцільності проведення подальшого аналізу. У цьому випадку вони наочно демонструють несумірність суми можливих збитків стосовно загальної суми доходів (-11691,92 та 1692669,76 відповідно).

Важливим етапом аналізу результатів імітаційного експерименту є дослідження залежностей між ключовими параметрами. Кількісна оцінка варіації прямо залежить від ступеня кореляції між випадковими величинами. На даному етапі обмежимось візуальним (графічним) дослідженням. На рис. 6 наведено графік розподілу значень ключових параметрів V, P та Q, побудований на підставі 75 імітацій. Неважко помітити, що в цілому варіація значень всіх трьох параметрів носить випадковий характер, що підтверджує прийняту раніше гіпотезу про їх незалежність. Для порівняння нижче наведено графік розподілів потоку платежів NCF і величини NPV (рис. 7).

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

Ми розглянули одну з технологій проведення імітаційних експериментів у середовищі Excel. Вона має дві вади:

вимагає значних зусиль та потребує забирає часу на її проведення;

обмежується випадком рівномірного розподілу досліджуваних змінних.

Більш ефективним способом вирішення таких завдань є використання спеціального інструменту Excel — «Генератор случайных чисел».




^ Рис. 6 — Розподіл значень параметрів V, P та Q



Рис. 7 — Залежність між NCF та NPV
^ Використання інструменту «Генератор случайных чисел»
Цей інструмент призначений для автоматичної генерації множини даних (генеральної сукупності) заданого обсягу, елементи якої характеризуються певним розподілом ймовірностей. Можна використовувати сім типів розподілу: рівномірне, нормальне, Бернуллі, Пуассона, біноміальне, модельне та дискретне. Застосування інструменту «Генератор случайных чисел» вимагає встановлення спеціального доповнення «Пакет анализа».

Змінимо умови прикладу, визначивши ймовірності для кожного сценарію розвитку подій, як вказано у табл. 8. Будемо вважати, що ключові змінні мають нормальний розподіл. Кількість імітацій залишимо без змін: 500.

Таблиця 8. Ймовірнісні сценарії реалізації проекту

Показники

Сценарій

Найгірший
P = 0.25

Найкращий
P = 0.25

Ймовірний
P = 0.5

Обсяг випуску - Q

150

300

200

Ціна за штуку - P

40

55

50

Змінні витрати - V

35

25

30



Приступимо до формування шаблона. Виділимо в робочій книзі два аркуші: «Имитация» и «Результаты анализа». Формування шаблона доцільно почати з аркуша «Результаты анализа» (рис. 8.).

Як випливає з рис. 8, цей аркуш практично відповідає раніше розробленому для рішення попереднього завдання (рис. 2). Відмінність становлять лише формули для розрахунку ймовірностей, які наведені в табл. 9. Власні імена осередків також узяті з аналогічного аркуша попереднього шаблона (табл. 7). Щоб швидко сформувати новий аркуш "Результаты анализа" виконайте такі дії:

завантажте шаблон SIMUL_1.XLT і збережіть його під іншим іменем, наприклад - SIMUL_2.XLT;

видаліть аркуш "Имитация", для цього встановите курсор миші на ярличок цього аркуша, натисніть праву кнопку; з’явиться контекстне меню, виберіть операцію "Удалить";

перейдіть на аркуш "Результаты анализа", видаліть рядки 17-18, відкоригуйте заголовок таблиці;

додайте формули з табл. 9, записавши їх до комірок блоку В17:В20, скопіюйте формули до блоку С17:F20, ведіть відповідні коментарі;

порівняйте отриману таблицю з рис. 8.

Перейдіть до наступного аркуша, надайте йому ім'я - "Имитация". Приступаємо до його формування (рис. 9).





^ Рис. 8 — Аркуш «Результаты анализа»

Таблиця 9. Формули аркуша "Результаты анализа"

Осередок

Формула

В17

=НОРМРАСП(0;B8;B9;1)

В18

=НОРМРАСП(B11;B8;B9;1)

В19

=НОРМРАСП(B12;B8;B9;1) -НОРМРАСП(B8+B9;B8;B9;1)

В20

=НОРМРАСП(B8;B8;B9;1) -НОРМРАСП(B8-B9;B8;B9;1)

З17

=НОРМРАСП(0;C8;C9;1)

З18

=НОРМРАСП(C11;C8;C9;1)

З19

=НОРМРАСП(C12;C8;C9;1) -НОРМРАСП(C8+C9;C8;C9;1)

З20

=НОРМРАСП(C8;C8;C9;1) -НОРМРАСП(C8-C9;C8;C9;1)

D17

=НОРМРАСП(0;D8;D9;1)

D18

=НОРМРАСП(D11;D8;D9;1)

D19

=НОРМРАСП(D12;D8;D9;1) -НОРМРАСП(D8+D9;D8;D9;1)

D20

=НОРМРАСП(D8;D8;D9;1) -НОРМРАСП(D8-D9;D8;D9;1)

E17

=НОРМРАСП(0;E8;E9;1)

E18

=НОРМРАСП(E11;E8;E9;1)

E19

=НОРМРАСП(E12;E8;E9;1) -НОРМРАСП(E8+E9;E8;E9;1)

E20

=НОРМРАСП(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)





^ Рис. 9 — Аркуш "Имитация"


Перша частина цього аркуша (блок А1:Е10) призначена для введення вихідних даних і розрахунку параметрів їх розподілу. Нагадаємо, що нормальний розподіл випадкової величини характеризується двома параметрами — математичним очікуванням (середнім) і стандартним відхиленням. Формули розрахунку вказаних параметрів для ключових змінних моделі задані в блоках В7:D7 та B8:D8 (див. табл. 11). Для зручності визначення формул задайте блоку Е3:Е5 ім'я "Ймовірності" (табл. 10).

Таблиця 10. Імена осередків аркуша "Имитация" (шаблон II)

Адреса осередку

Ім'я

Коментарі

Блок Е3:Е5

Вероятности

Ймовірність значення параметра

Блок A13:A512

Перем_расх

Змінні витрати

Блок B13:B512

Количество

Обсяг випуску

Блок C13:C512

Цена

Ціна виробу

Блок D13:D512

Поступления

Надходження від проекту NCF

Блок E13:E512

ЧСС

Чиста сучасна вартість NPV

Таблиця 11. Формули аркуша "Имитация" (шаблон II)

Осередок

Формула

В7

=СУММПРОИЗВ(B3:B5; Вероятности)

В8

{=КОРЕНЬ(СУММПРОИЗВ((B3:B5 - B7)^2; Вероятности))}

З7

=СУММПРОИЗВ(C3:C5; Вероятности)

З8

{=КОРЕНЬ(СУММПРОИЗВ((C3:C5 - C7)^2; Вероятности))}

D7

=СУММПРОИЗВ(D3:D5; Вероятности)

D8

{=КОРЕНЬ(СУММПРОИЗВ((D3:D5 - D7)^2; Вероятности))}

E10

=B10+13 –1

D13

=СУММПРОИЗВ(B3:B5; Вероятности)

E13

{=КОРЕНЬ(СУММПРОИЗВ((B3:B5 - B7)^2; Вероятности))}

Зверніть увагу, що для розрахунку стандартних відхилень використовуються формули–масиви. Для формування блоку формул треба визначити їх для блоку В7:В8 і скопіювати в блок С7:D8.

Формула в Е10 за заданим числом імітацій (комірка В10) знаходить номер останнього рядка для блоків, де будуть зберігатися згенеровані значення ключових змінних. Комірки D13:E13 містять уже знайомі нам формули для розрахунку величини потоку платежів NCF і його чистої сучасної вартості NPV.

Сформуйте елементи оформлення аркуша "Имитация", визначте необхідні імена для блоків (див. табл. 10) і задайте необхідні формули (табл. 11). Звірте отриману таблицю з рис. 9. Збережіть отриманий шаблон під ім'ям SIMUL_2.XLT.

Введіть вихідні значення постійних змінних (табл. 2) в комірки В2:В4 та D2:D4 аркушу "Результаты анализа". Перейдіть до аркушу "Имитация". Введіть значення ключових змінних та відповідні ймовірності (табл. 8). В результаті ви маєте отримати таблицю, що показана на рис. 10.



Рис. 10 — Аркуш "Имитация" після введення даних


Встановіть курсор на комірку А13. Приступимо до проведення імітаційного експерименту.

Виберіть з меню "Сервис\Анализ данных". З’явиться діалогове вікно "Анализ данных", що містить список інструментів аналізу.

Виберіть зі списку пункт "Генерация случайных чисел" і натисніть "ОК" (рис. 11).

З'явиться діалогове вікно "Генерация случайных чисел". Вкажіть у списку "Распределения" тип "Нормальное". Заповніть інші поля у вікні згідно рис. 12 і натисніть кнопку "ОК". Комірки блоку А13:А512 будуть заповнені випадковими значеннями.




^ Рис. 11 — Вибір інструмента "Генерация случайных чисел"





Рис. 12 — Заповнення полів вікна "Генерация случайных чисел"


Наведемо необхідні пояснення. Першим аргументом діалогового вікна "Генерация случайных чисел" є поле "Число переменных". Воно задає кількість стовпчиків таблиці, в яких будуть розміщуватися згенеровані ймовірнісні випадкові величини. У нашому прикладі аргумент "Число переменных" дорівнює 1, адже ми відвели під значення змінної V (змінні витрати) один стовпчик – "А". Якщо вказати параметр більше за 1, випадкові величини будуть розміщені у відповідній кількості сусідніх стовпчиків, починаючи з активного осередку. Якщо параметр не вказувати , будуть заповнені всі стовпчики у вихідному діапазоні.

Наступним обов'язковим аргументом є "Число случайных чисел" (кількість імітацій). За умовами прикладу воно має дорівнювати 500 (рис. 12). Excel автоматично підраховує необхідну кількість осередків для зберігання генеральної сукупності.

Вид розподілу треба вибрати зі списку "Распределения". Тут можна отримати 7 найпоширеніших типів розподілу. Кожний з них характеризується власними параметрами. Обраний тип розподілу визначає зовнішній вигляд діалогового вікна. У нашому прикладі вибираємо тип розподілу "Нормальное". Вводимо його параметри "Среднее" та "Стандартное отклонение". Ці параметри для досліджуваної змінної V ми визначили раніше в осередках В7 та В8 аркушу "Имитация". На жаль, ці аргументи можуть бути задані тільки у вигляді констант. Вказувати адреси осередків і власних імен не допускається!

Зазначення аргументу "Случайное рассеивание" дозволяє при декількох запусках генератора одержувати однакові послідовності випадкових величин. Таким чином можна одержати однакову генеральну сукупність випадкових чисел декілька разів. Це значно підвищує ефективність аналізу (зрівняйте з попереднім шаблоном!). Якщо цей аргумент не заданий або дорівнює 0, кожний наступний запуск генератора дасть нову генеральну сукупність. У нашому прикладі вказуємо 1. Це дозволить оперувати з однією і тією ж генеральною сукупністю та уникнути постійних перерахунків таблиці.

Останній аргумент діалогового вікна "Генерация случайных чисел" - "Параметры вывода" визначає місце розташування результатів (задається шляхом відповідним прапорцем). Excel пропонує три варіанти:

вихідний блок осередків на поточному аркуші — потрібно вказати адресу лівого верхнього осередку вихідного блоку; розмір блоку буде визначено автоматично;

новий робочий аркуш — у робочій книзі буде відкрито новий аркуш, що містить результати генерації випадкових величин, починаючи з осередку A1;

нова робоча книга — буде відкрита нова книга з результатами імітації на першому аркуші.

У нашому прикладі для проведення подальшого аналізу необхідно, щоб випадкові величини розміщувалися в спеціально відведених для них блоках (табл. 10). Так, для зберігання 500 значень першої змінної раніше було відведено блок А13:А512. Цей блок має власне ім'я — "Перем_расх", тому воно вказано як вихідний діапазон. Зазначимо, що при збільшенні або зменшенні кількості імітацій необхідно перевизначити вихідні блоки , в яких зберігаються результати.

Генерація значень змінних Q і Р здійснюється аналогічно. Приклад заповнення вікна "Генерация случайных чисел" для змінної Q (кількість) наведений на рис. 13.

Для одержання генеральної сукупності значень потоку платежів та їх чистої теперішньої вартості треба скопіювати формули базового рядка (комірки D13:E13) 499 разів. Щоб полегшити процес копіювання великого діапазону значень, виконайте такі дії.

Скопіюйте до буферу комірку D13.

Натисніть клавішу [F5]. На екрані з'явиться діалогове вікно "Переход".

Вкажіть у полі "Ссылка" ім'я блоку "Поступления" і натисніть кнопку "ОК". Блок буде виділено.

Натисніть клавішу ENTER.

Якщо було встановлено режим ручних обчислень, натисніть клавішу F9 для перерахування таблиці.




Рис — 13. Заповнення полів вікна для змінної Q


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

Результати рішення для нашого прикладу наведені на рис. 14 , 15.

За результатами проведеного імітаційного експерименту видно, що величина очікуваної NPV дорівнює 3412,14 при стандартному відхиленні 2556,83. Коефіцієнт варіації 0,75 менше за 1, тобто ризик проекту в цілому незначний і знаходиться у допустимих межах середнього ризику інвестиційного портфелю фірми. Результати ймовірнісного аналізу показують, що шанс одержати негативне значення NPV не перевищує 9%. Загальне число негативних значень NPV у вибірці становить 32 з 500. Тобто з ймовірністю близько 91% можна стверджувати, що чиста реальна вартість проекту буде позитивною 0. При цьому ймовірність того, що величина NPV виявиться більше за М(NPV) + , дорівнює 16% (осередок F19). Ймовірність влучення значення NPV в інтервал [М(NPV) - ; М(NPV)] дорівнює 34%.




Рис. 14 — Результати імітаційного експерименту (шаблон II)
^ Статистичний аналіз результатів імітації
В аналізі стохастичних процесів важливе значення мають статистичні взаємозв'язки між випадковими величинами. За кількісну характеристику подібних взаємозв'язків у статистиці використовують два показники: коваріацію та кореляцію.

Коваріація та кореляція

Коваріація виражає ступінь статистичної залежності між двома безлічами даних і визначається з наступного співвідношення:



де: X, Y — множини значень випадкових величин розмірності m;

M(X) — математичне очікування випадкової величини Х;

M(Y) — математичне очікування випадкової величини ^ Y.

Як випливає з (4), позитивна коваріація спостерігається у випадку, коли більшим значенням випадкової величини Х відповідають більші значення випадкової величини Y, тобто між ними існує прямий взаємозв'язок.



^ Рис. 15 — Результати аналізу (шаблон II)


Відповідно негативна коваріація буде мати місце при відповідності малим значенням випадкової величини Х більших значень випадкової величини Y. При слабко вираженій залежності значення коваріації близько до 0. Коваріація залежить від одиниць виміру досліджуваних величин, що