Zastosowanie Informatyki w Finansach i Bankowości

Zastosowanie metody MonteCarlo w analizie ryzyka inwestycji rzeczowych.

Typowa analiza wrażliwości bada wpływ zmiany jednego czynnika ekonomicznego (np. zmiany przychodów ze sprzedaży)

na opłacalność przedsięwzięcia inwestycyjnego przy założeniu, że pozostałe czynniki ekonomiczne pozostają stałe. Można

sprawdzić w jaki sposób zachowa się inwestycja, gdy wszystkie czynniki ulegną zmianie. Utrudnieniem w takiej analizie
jest problem przeprowadzenia takiej symulacji, która pokazałaby maksymalną liczbę ewentualnych scenariuszy. Ręczne

ustalanie kolejnych scenariuszy jest jak najbardziej możliwe, ale bardzo pracochłonne. Uproszczenie procesu symulacji

polega przede wszystkim na scharakteryzowaniu w uproszczony sposób ewentualnego zachowania zmiennych

wejściowych, a dokładnie ich ewentualnego odchylenia od poziomu zakładanego (prognozowanego). Na podstawie

danych ex-post dotyczących podobnych przedsięwzięć inwestycyjnych możemy opisać potencjalne zachowanie czynników

ekonomicznych za pomocą rozkładów statystycznych. Przykładowo możemy opisać poziom przychodów operacyjnych za
pomocą rozkładu normalnego o średniej 1,00 i odchyleniu standardowym 0,05 a koszty operacyjne za pomocą rozkładu

normalnego o średniej 1,00 ale o odchyleniu standardowym na poziomie 0,1. Takie uproszczenie powinno być odczytane

w następujący sposób – zarówno przychody operacyjne jak i koszty najprawdopodobniej wystąpią na poziomie

zakładanym, natomiast prawdopodobieństwo odchylenia od poziomu zakładanego większe jest dla kosztów niż dla

przychodów. Aby dokonać oceny ryzyka należy wylosować kolejne wartości zmian przychodów i kosztów oraz zebrać dane

charakteryzujące opłacalność np. NPV, IRR. Wynikiem takiej symulacji będzie zbiór prawdopodobnych wartości
wskaźników opłacalności, który można opisać za pomocą histogramu oraz statystyki opisowej a następnie na podstawie

miar korelacji zbadać związek pomiędzy zmianami poszczególnych czynników ekonomicznych a zmianami opłacalności,

charakteryzowanej np. przez NPV.

Problemem zasadniczym przy próbie symulacji ryzyka metodą MonteCarlo jest uzyskanie narzędzia, które rozlosuje

warianty poziomu czynników ekonomicznych i zbierze dla nich informacje o kształtowaniu się opłacalności.

Do przeprowadzenia analizy ryzyka ponownie wykorzystamy model z ćwiczenia 6 wzbogacając go o kolejne narzędzia.

Zanim przystąpimy do utworzenia makra analizującego ryzyko metodą MonteCarlo spróbujmy tak przekształcić arkusz

kalkulacyjny, aby sam zwracał różny poziom zmiennych wejściowych do modelu na podstawie założonych rozkładów

statystycznych o określonych parametrach.

1. W komórkach nazwanych zm_p, zm_k itp. umieszczamy rozkłady statystyczne normalne poprzez użycie funkcji

rozkład.normalny.odw

w następujący sposób Æ

=rozkład.normalny.odw(los();1;0,1)

. Taka formuła

oznacza zwracanie losowej wartości ze zbioru opisanego rozkładem normalnym o średniej 1 i odchyleniu

standardowym 0,1 przy każdym przeliczeniu arkusza. Podobnie scharakteryzujmy komórki zm_p, zm_k, zm_n,

zm_d, zm_t.

2. Przeliczanie arkusza, a dokładnie formuł w komórkach następuje automatycznie po każdej zmianie wprowadzonej

w arkuszu lub poprzez użycie polecenia

PRZELICZ

(klawisz

F9

)

3. Każdorazowe przeliczenie to zwrócenie scenariusza opisanego przez poziom zmian kluczowych czynników modelu

oraz wskaźniki opłacalności.

Poniżej przedstawiono makro, które przypisuje rozkłady normalne do komórek z kolumny „Zmiana” oraz dokonuje 1000

losowań i zbiera wyniki w formie arkusza.

1. Należy utworzyć Arkusz o nazwie

MonteCarlo

.

2. Dodać w arkuszu Model Uproszczony kolumnę zawierającą odchylenia zmiennych wejściowych do modelu

uproszczonego i nazwać poszczególne komórki w następujący sposób

dev_p

,

dev_k

,

dev_n

,

dev_t

,

dev_d

.

3. Dodać moduł zawierający poniższe makro.

Sub MonteCarlo()

'Przypisanie rozkładów zmian do odpowiednich komórek

'Poniższe polecenia wprowadzają formuły w komórkach pobierając odpowiednie dane o odcyleniu standardowym

'z odpowiednio nazwanych komórek.

Range("zm_p").Formula = "=NORMINV(RAND(),1,dev_p)"

Range("zm_k").Formula = "=NORMINV(RAND(),1,dev_k)"
Range("zm_n").Formula = "=NORMINV(RAND(),1,dev_n)"

Range("zm_t").Formula = "=NORMINV(RAND(),1,dev_t)"

Range("zm_d").Formula = "=NORMINV(RAND(),1,dev_d)"

'Utworzenie petli zwracajacej warianty

'-------------------------------------

'Wyłączenie automatycznego przeliczania komórek
'Przyspieszenie Makra oraz zapewnienie jednorazowego losowania poszczególnych zmian

With Application

.Calculation = xlManual

.MaxChange = 0.001

End With

'Utworzenie petli zwracajacej warianty zmian czynników i wartości NPV do nowego arkusza

For n = 1 To 1000

Calculate

Sheets("MonteCarlo").Cells(n, 1).Value = Range("zm_p").Value
Sheets("MonteCarlo").Cells(n, 2).Value = Range("zm_k").Value

Sheets("MonteCarlo").Cells(n, 3).Value = Range("zm_n").Value

Sheets("MonteCarlo").Cells(n, 4).Value = Range("zm_t").Value

Sheets("MonteCarlo").Cells(n, 5).Value = Range("zm_d").Value

Sheets("MonteCarlo").Cells(n, 6).Value = Range("NPV").Value

Next n

'Włączenie automatycznego przeliczania w Excelu

With Application

.Calculation = xlAutomatic

.MaxChange = 0.001

End With

'Usunięcie formuł funkcji statystycznych

Range("zm_p").Value = 1

Range("zm_k").Value = 1

Range("zm_n").Value = 1

Range("zm_t").Value = 1

Range("zm_d").Value = 1