Імітаційне моделювання інвестиційних ризиків засобами MS Excel та MathCAD
Saved in:
| Date: | 2007 |
|---|---|
| Main Authors: | , |
| Format: | Article |
| Language: | Ukrainian |
| Published: |
Міжнародний науково-навчальний центр інформаційних технологій та систем НАН і МОН України
2007
|
| Online Access: | https://nasplib.isofts.kiev.ua/handle/123456789/10877 |
| Tags: |
Add Tag
No Tags, Be the first to tag this record!
|
| Journal Title: | Digital Library of Periodicals of National Academy of Sciences of Ukraine |
| Cite this: | Імітаційне моделювання інвестиційних ризиків засобами MS Excel та MathCAD / В.В. Гавриленко, О.А. Шумейко // Екон.-мат. моделювання соц.-екон. систем. — 2007. — Вип. 12. — С. 211-220. — Бібліогр.: 3 назв. — укp. |
Institution
Digital Library of Periodicals of National Academy of Sciences of Ukraine| _version_ | 1859723612757426176 |
|---|---|
| author | Гавриленко, В.В. Шумейко, О.А. |
| author_facet | Гавриленко, В.В. Шумейко, О.А. |
| citation_txt | Імітаційне моделювання інвестиційних ризиків засобами MS Excel та MathCAD / В.В. Гавриленко, О.А. Шумейко // Екон.-мат. моделювання соц.-екон. систем. — 2007. — Вип. 12. — С. 211-220. — Бібліогр.: 3 назв. — укp. |
| collection | DSpace DC |
| first_indexed | 2025-12-01T10:41:38Z |
| format | Article |
| fulltext |
Економіко-математичне моделювання соціально-економічних систем
Збірник наукових праць МННЦ ІТіС
_____________________________________________________________________
__________________________________________________________________________
Київ 2007, випуск 12
211
УДК 658.012 В.В. Гавриленко, О.А.Шумейко
ІМІТАЦІЙНЕ МОДЕЛЮВАННЯ ІНВЕСТИЦІЙНИХ РИЗИКІВ
ЗАСОБАМИ MS EXCEL ТА MATHCAD
У загальному випадку під ризиком розуміють можливість настання
деякої несприятливої події, що несе за собою різного роду втрати. Існуван-
ня ризику пов'язане з неможливістю з точністю до 100% прогнозувати
майбутнє.
Оцінка ефективності інвестицій це практично завжди оцінка проек-
тів з ризиком. Це обумовлено тим, що критерії оцінки ефективності інвес-
тицій залежать від прогнозованих грошових потоків, які у свою чергу в
майбутньому можуть змінюватись: зростати чи зменшуватися в залежнос-
ті від ситуації, що склалася. Фактори ризику і невизначеності підлягають
обліку в розрахунках ефективності, якщо при різний можливих умовах ре-
алізації проекту результати різні.
Одним з методів оцінки ефективності інвестиція є імітаційне моде-
лювання. Імітаційне моделювання (sіmulatіon) є одним з поширених мето-
дів аналізу економічних систем. При оцінці ризику інвестиційних проектів
збір інформації потребує значних витрат, а іноді взагалі неможливий. Од-
нак, для адекватної оцінки ризику необхідно мати достатню кількість да-
них. У випадках коли фізичні дані отримати важко або неможливо їх замі-
нюють величинами, що були отримані під час імітаційного експерименту.
Методика оцінки ризику інвестиційного проекту за допомогою
імітаційного аналізу. Під імітацією розуміють проведення на комп’ютері
випробувань з математичними моделями реальних систем. Імітаційне мо-
делювання засноване на теорії ігор і являє собою серію чисельних експе-
риментів, за допомогою яких можливо отримати емпіричну оцінку ступе-
ню впливу різних факторів (вихідних величин) на деякі залежні від них ре-
зультати. Метою імітаційного моделювання є одержання емпіричної оцін-
ки ступеня впливу вихідних величин на залежні від них величини-
результати.
Імітаційне моделювання припускає значні обсяги обчислень, тому
здійснюється винятково за допомогою комп'ютерів. Числові значення сто-
хастичних параметрів для кожного експерименту беруться як випадкові
числа з довірчих діапазонів значень. Довірчий діапазон – діапазон обмеже-
ний оптимістичним і песимістичним значенням параметра. Випадкові чис-
ла генеруються за допомогою спеціальних програм-функцій які мають на-
зву генератори випадкових чисел. Отримані результати аналізуються як
статистичні дані.
Постановка задачі. Розглядається інвестиційний проект по
виробництву продукту „А”. В процесі попереднього експертного аналізу
були визначені три ключові параметри проекту та визначені межі їх змін
(табл. 1).
Економіко-математичне моделювання соціально-економічних систем
Збірник наукових праць МННЦ ІТіС
_____________________________________________________________________
__________________________________________________________________________
Київ 2007, випуск 12
212
Таблиця 1
Мінімум Максимум
Змінні витрати на одиницю продукції (V), грн 25 35
Кількість реалізованої продукції (Q), од 150 300
Ціна за одиницю продукції (P), грн 40 55
Інші параметри (табл. 2) вважаємо постійними величинам.
Таблиця 2
Початкові інвестиції (I0) грн 2000
Постійні витрати (F) грн 500
Амортизація (А) грн 100
Норма (ставка) дисконту (r ) 0,1
Податок (Т) 0,6
Термін реалізації проекту (n) роки 5
Завдання: проаналізувати ефективність інвестиційного проект та оцінити ризик
його реалізації шляхом імітаційного моделювання.
Реалізація алгоритму в Excel. На новому листі Excel вводимо вихідні дані у комірках:
Комірка Значення Ім’я комірки Пояснення
B:3 2000 інвест Початкові інвестиції
B:4 500 Пост_витрат Постійні витрати
B:5 100 аморт Амортизація
D:3 0,1 норма Норма (ставка) дисконту
D:4 0,6 податок Ставка оподаткування
D:5 5 терм Термін реалізації (тривалість)
проекту
Ім’я комірки чи діапазону комірок вводиться у полі Имя, що
знаходиться лівіше строки формул. Використання імен комірок та
діапазонів є зручною можливістю, яку надає Excel. Для посилання на
комірку чи діапазон, якому присвоєно ім’я достатньо натиснути F3 та
вибрати його ім’я у списку. Це дозволяє прискорити введення формул та
запобігати помилок.
Вводимо вихідні умови для імітаційного моделювання процесу
інвестування:
Комірка Значення Пояснення
B:9 25 змінні витрати на одиницю продукції (мінімум)
B:10 150 об’єм реалізації продукції (мінімум)
B:11 40 ціна одиниці продукції (мінімум)
C:9 35 змінні витрати на одиницю продукції (максимум)
C:10 300 об’єм реалізації продукції (максимум)
C:11 55 ціна одиниці продукції (максимум)
Приклад оформлення вихідних даних представлено рис.1.
Економіко-математичне моделювання соціально-економічних систем
Збірник наукових праць МННЦ ІТіС
_____________________________________________________________________
__________________________________________________________________________
Київ 2007, випуск 12
213
Рис.1 Вихідні дані
Для проведення першого експерименту вводимо у комірки А17:Е17
формули математичної моделі інвестиційного процесу. У комірках
А17:В17 генеруємо випадкові величини відповідно змінних витрат, об’єму
реалізації, та ціни (рис.2).
Генерування випадкової величини виконується за допомогою
вбудованої функції Excel СЛУЧМЕЖДУ(параметр1;параметр2), де
параметр_1 та параметр_2 відповідно визначають максимальне та
мінімальне значення діапазону чисел які генеруються. Ці параметри
беруться з вихідних умов експерименту і вводяться у формули як
абсолютні посилання на відповідні комірки. Нагадуємо, що абсолютне
посилання вводиться за допомогою введення символу $ перед номером
строки та літерою стовпця.
Функція СЛУЧМЕЖДУ буде обчислювати значення кожного разу як
будуть вводитись або корегуватися дані на листі. Щоб уникнути цієї
незручності, треба переключити Excel з режиму автоматичного у режим
ручного обчислювання, для цього виконуємо Сервис – Параметры –
Вычисления та встановлюємо перемикач блоку Вычисления у позицію
Вручную. Після цього Excel буде обчислювати значення формул лише
після натискання F9.
Рис.2
Економіко-математичне моделювання соціально-економічних систем
Збірник наукових праць МННЦ ІТіС
_____________________________________________________________________
__________________________________________________________________________
Київ 2007, випуск 12
214
Рис.2а
У комірку D17 вводимо формула для обчислення Cash-flow проекту, який
знаходиться за формулою (рис.3):
CF = (Об’єми реалізації * (Ціна за одиницю продукції – Змінні витрати на
одиницю продукції)) – Постійні витрати – Амортизація)(1 – Податки) +
Амортизація
Значення змінних витрат, об’єму реалізації та ціни вносимо посилаючись
на комірки що містять їх значення, відповідно А17, А18, А19. Інші
значення, щоб уникнути помилок, вносимо за допомогою імен, що були
надані відповідним коміркам. Щоб внести у формулу відповідне ім’я
необхідно натиснути F3 та вибрати ім’я з списку.
Рис.3. Формули для розрахунку CF та NPV
У комірку Е17 вводимо формулу для розрахунку NPV. Припускаємо,
що cash-flow проекту однаковий на кожен рік реалізації. Тому NPV
доцільно визначати за допомогою вбудованої функції Excel ПС.
Синтаксис функції:
ПС(ставка;кпер;плт)
Ставка – процентна ставка дисконтування (норма) за період.
Кпер – загальне число періодів платежів. У нашому випадку 5 періодів, кі-
лькість періодів дорівнює кількості років реалізації.
Плт – виплата, вироблена в кожен період і постійна за увесь час виплат
(вводиться як негативна величина).
Таким чином у діапазоні А17:Е17 формується математична модель інвес-
тиційного проекту, яка одночасно є першою імітацією процесу імітаційно-
го моделювання ризику. Для проведення наступних імітацій необхідно ви-
конати наступні кроки:
1. Визначити кількість імітацій, наприклад 500
2. Визначити номер строки листа в якій буде знаходитися остання
імітація. (Якщо перша імітація знаходиться у 17 стрічки то 500-та
відповідно буде знаходитися у 516)
Економіко-математичне моделювання соціально-економічних систем
Збірник наукових праць МННЦ ІТіС
_____________________________________________________________________
__________________________________________________________________________
Київ 2007, випуск 12
215
3. Скопіювати діапазон А17:Е17.
4. Встановити курсор у комірку А17 та натиснути F5.
5. У діалозі Переход у віконці Ссылка набрати А516 (див. пункт 2)
та натиснути <Shift+Enter> (одночасно натиснути <Shift> та
<Enter>). Крок п’ять виділяє діапазон А17:А516.
6. Натиснути <Ctrl+V) чи кнопку Вставить на робочій панелі. Крок
6 вставляє формули діапазону А17:Е17 у діапазон А18:Е516.
7. Після виконання кроку 6 отримаємо 500 однакових строк. Натис-
каємо F9 для перерахування формул генерації випадкових вели-
чин, як результат отримаємо 500 імітацій з різними вхідними ве-
личинами (Q,P,V).
Після побудови імітаційної моделі процесу інвестування необхідно про-
аналізувати результати експерименту. Присвоюємо імена діапазонам з да-
ними моделі:
Таблиця 3
Діапазон Ім’я Пояснення
А17:А516 зм_витр Діапазон генерованих значень змінних витрат
В17:В516 кількість Діапазон генерованих значень об’ємів реалізації
С17:С516 Ціна Діапазон генерованих значень цін
D17:D516 CF Діапазон розрахованих cash-flow
Е17:Е516 NPV Діапазон розрахованих значень NPV
Проводимо статистичне дослідження діапазонів отриманих значень (рис.4):
Для кожного діапазону знаходимо:
Таблиця 4
Значення Формула для знаходження
Середнє значення величини =СРЗНАЧ(діапазон)
Стандартне відхилення =СТАНДОТКЛОНП(діапазон)
Коефіцієнт варіації =H10/H9
Мінімум =МИН(діапазон)
Максимум =МАКС(діапазон)
Замість діапазон заносимо справжні імена діапазонів, що досліджуються, які беремо з
таблиці 3. Необхідно дослідити всі діапазони. Приклад дослідження наведено рис 5.
Рис.4
Економіко-математичне моделювання соціально-економічних систем
Збірник наукових праць МННЦ ІТіС
_____________________________________________________________________
__________________________________________________________________________
Київ 2007, випуск 12
216
Рис.4а
Додатково для діапазону „NPV” знаходимо показники:
Показник Формула
Число випадків NPV<0 =СЧЁТЕСЛИ(NPV;"<0")
Загальна сума збитків по всіх імітаціям =СУММЕСЛИ(NPV;"<0")
Загальна сума доходів по всіх імітаціям =СУММЕСЛИ(NPV;">0")
Ймовірність появи NPV<0 =ABS(L15/(ABS(L15)+L16))
З наведених показників най важливим є Ймовірність появи NPV<0,
який визначає ймовірність, що проект буде збитковим. Показник дає змогу
інвестору прийняти рішення про інвестування, зважаючи на власну
схильність до ризику.
Сума всіх негативних значень NPV в отриманій генеральній сукуп-
ності (комірка L15) може бути інтерпретована як чиста вартість невизна-
ченості для інвестора у випадку прийняття проекту. Аналогічно сума всіх
позитивних значень NPV (комірка L16) може трактуватися як чиста вар-
тість невизначеності для інвестора у випадку відхилення проекту. Незва-
жаючи на всю умовність цих показників, у цілому вони являють собою ін-
дикатори доцільності проведення подальшого аналізу.
Економіко-математичне моделювання соціально-економічних систем
Збірник наукових праць МННЦ ІТіС
_____________________________________________________________________
__________________________________________________________________________
Київ 2007, випуск 12
217
Рис.5 Аналіз результатів імітаційного моделювання
Дослідження взаємозв’язків величин за допомогою Кореляції. На прак-
тиці одним з найважливіших етапів аналізу результатів імітаційного експе-
рименту є дослідження взаємозалежності (кореляції) між ключовими па-
раметрами. Для дослідження кореляції вихідних значень та результатів імі-
таційної моделі проводимо Кореляційний аналіз.
Рис 6. Кореляційний аналіз результатів
Для проведення Кореляційного аналізу, необхідно інсталювати та підклю-
чити надбудову Excel Пакет анализа. Для проведення кореляційного аналі-
зу необхідно:
1. активувати інструмент аналізу Кореляція (Сервис – Анализ данных
– Корреляция);
2. Вказати вхідний діапазон – А16:Е516
3. Задати Группировка данных у нашому випадку необхідно по сто-
лбцам
4. Вимкнути перемикач Метки в первой строке, що дозволить мати
підписи у таблиці результатів.
5. Задати Вихідний діапазон, це може бути пуста комірка на робочому
листі (у прикладі G19), чи новий лист.
Коефіцієнт кореляції є безрозмірною величиною і приймає значення
від -1 (характеризує лінійний зворотний взаємозв'язок) до +1 (характеризує
лінійний прямий взаємозв'язок). Для незалежних випадкових величин зна-
чення коефіцієнта кореляції знаходиться близько до 0.
Побудова гістограми розподілу значень NPV. Аналізувати ряди
розподілу значень NPV зручніше всього за допомогою графічного зобра-
ження, що дозволяє судити о формі розподілу. Наочне уявлення о характе-
рі зміни варіаційного ряду дає гістограма. Гістограма – це графік на осі аб-
сцис якого відкладаються значення інтервалів, частоти попадання значень
до цих інтервалів відкладаються у вигляді прямокутників, що будуються
на цих інтервалах, у вигляді суміжних одна з одною областей.
Економіко-математичне моделювання соціально-економічних систем
Збірник наукових праць МННЦ ІТіС
_____________________________________________________________________
__________________________________________________________________________
Київ 2007, випуск 12
218
0
20
40
60
-1
23
4,
26
10
73
-2
27
,2
90
25
98
77
9,
68
05
52
9
17
86
,6
51
36
6
27
93
,6
22
17
8
38
00
,5
92
99
1
48
07
,5
63
80
4
58
14
,5
34
61
7
68
21
,5
05
42
9
78
28
,4
76
24
2
88
35
,4
47
05
5
Е
щ
е
npv
Частота
0%
20%
40%
60%
80%
100%
Рис 7. Гістограма розподілу значень NPV
В Excel побудова гістограми реалізується за допомогою спеціального
інструменту надбудови Пакет аналізу Гистограмма. Виклик інструменту
відбувається за допомогою меню: Сервис – Анализ данных – Гистогра-
мма. У діалозі Гистограмма задаємо наступні парамметри:
Входной интервал – діапазон, що містить значення варіаційного ря-
ду, у нашому випадку це діапазон Е17:Е516, який містить значення NPV
усіх імітацій експерименту.
Интервал карманов – параметр відповідає за розміри інтервалів
(кишень) діаграми, залишаємо без змін, дозволяємо програмі автоматично
визначити їх значення.
Параметры вывода – вибираємо місце виводу гістограми, можливі
наступні варіанти: на поточний лист, на інший лист, в іншу книгу); для
безпосереднього виводу гістограми включаємо перемикач Вывод графи-
ка.
Результат побудови гістограми засобами Excel представлено на
рис.6. Аналогічним чином ця задача може бути вирішена за допомогою
математичного пакету MathCad. Наведемо приклад рішення тієї ж задачі
засобами математичного пакету MathCad, рис.8.
Незважаючи на відзначені недоліки, у даний час імітаційне моделювання є осно-
вою для створення нових перспективних технологій керування і прийняття рі-
шень у сфері бізнесу, а розвиток обчислювальної техніки і програмного забезпе-
чення робить цей метод усе більш доступним для широкого кола фахівців-
практиків.
Представлена імітаційна модель дозволяє особі, що приймає рішення оцінити
можливу невизначеність результатів власного рішення. Базуючись на приведе-
них результатах імітації можливо визначити значення критерію доцільності при-
йняття рішення (наприклад вірогідність NPV<0) та зіставити його з власною но-
рмою, що базується на індивідуальній схильності до ризику.
Економіко-математичне моделювання соціально-економічних систем
Збірник наукових праць МННЦ ІТіС
_____________________________________________________________________
__________________________________________________________________________
Київ 2007, випуск 12
219
Рис. 8.
Рис.8. Гістограма та полігон розподілу NPV побудовані за допомогою MathCad
Економіко-математичне моделювання соціально-економічних систем
Збірник наукових праць МННЦ ІТіС
_____________________________________________________________________
__________________________________________________________________________
Київ 2007, випуск 12
220
Результати імітації можуть бути доповнені імовірнісним і статистичним аналі-
зом і в цілому забезпечують менеджера найбільш повною інформацією про ступінь
впливу ключових факторів на очікувані результати і можливі сценарії розвитку подій.
Незважаючи на відзначені недоліки, у даний час імітаційне моделювання є ос-
новою для створення нових перспективних технологій керування і прийняття рішень у
сфері бізнесу, а розвиток обчислювальної техніки і програмного забезпечення робить
цей метод усе більш доступним для широкого кола фахівців-практиків.
Література.
1. Лукасевич И.Я. Имитационное моделирование инвестиционных рисков /
http://www.cfin.ru
2. Дмитриев М. Н., Кошечкин С.А. Количественный анализ риска инвестиционных
проектов / http://www.cfin.ru
3. Лудченко Я.О.. Кононенко І.В. Економічна ефективність інвестицій. – К.: Вища
школа, 2002. – 191 с.
|
| id | nasplib_isofts_kiev_ua-123456789-10877 |
| institution | Digital Library of Periodicals of National Academy of Sciences of Ukraine |
| issn | XXXX-0009 |
| language | Ukrainian |
| last_indexed | 2025-12-01T10:41:38Z |
| publishDate | 2007 |
| publisher | Міжнародний науково-навчальний центр інформаційних технологій та систем НАН і МОН України |
| record_format | dspace |
| spelling | Гавриленко, В.В. Шумейко, О.А. 2010-08-09T13:06:33Z 2010-08-09T13:06:33Z 2007 Імітаційне моделювання інвестиційних ризиків засобами MS Excel та MathCAD / В.В. Гавриленко, О.А. Шумейко // Екон.-мат. моделювання соц.-екон. систем. — 2007. — Вип. 12. — С. 211-220. — Бібліогр.: 3 назв. — укp. XXXX-0009 https://nasplib.isofts.kiev.ua/handle/123456789/10877 658.012 uk Міжнародний науково-навчальний центр інформаційних технологій та систем НАН і МОН України Імітаційне моделювання інвестиційних ризиків засобами MS Excel та MathCAD Article published earlier |
| spellingShingle | Імітаційне моделювання інвестиційних ризиків засобами MS Excel та MathCAD Гавриленко, В.В. Шумейко, О.А. |
| title | Імітаційне моделювання інвестиційних ризиків засобами MS Excel та MathCAD |
| title_full | Імітаційне моделювання інвестиційних ризиків засобами MS Excel та MathCAD |
| title_fullStr | Імітаційне моделювання інвестиційних ризиків засобами MS Excel та MathCAD |
| title_full_unstemmed | Імітаційне моделювання інвестиційних ризиків засобами MS Excel та MathCAD |
| title_short | Імітаційне моделювання інвестиційних ризиків засобами MS Excel та MathCAD |
| title_sort | імітаційне моделювання інвестиційних ризиків засобами ms excel та mathcad |
| url | https://nasplib.isofts.kiev.ua/handle/123456789/10877 |
| work_keys_str_mv | AT gavrilenkovv ímítacíinemodelûvannâínvesticíinihrizikívzasobamimsexceltamathcad AT šumeikooa ímítacíinemodelûvannâínvesticíinihrizikívzasobamimsexceltamathcad |