Symulacja Monte Carlo

Symulacja Monte Carlo (wyłącznie w aspekcie finansowym) w praktyce stanowi złożony proces przeprowadzania dużej liczby symulacji dla danego modelu finansowego przy założeniu, że nie znamy dokładnych wartości wybranych zmiennych tego modelu w danym okresie w przyszłości - znając natomiast kształtowanie się wartości tych zmiennych w przeszłości. Aby lepiej zrozumieć istotę problemu, posłużmy się przykładem; Młody inwestor planuje na podstawie podstawowych danych ze sprawozdania finansowego spółki, określić jej zysk w następnym roku funkcjonowania. Załóżmy, że inwestor posiada dokładne prognozy co do planowanych kosztów poniesionych przez te przedsiębiorstwo w przyszłym roku - niemniej wielkość przychodu nie jest mu znana. Jako, że inwestor posiada sprawozdania finansowe analizowanej spółki za 5 ostatnich lat - może dokonać prognozy przychodów na następny rok (np. wybranym modelem ekonometrycznym), lub wykorzystując dane historyczne przeprowadzić symulacje Monte Carlo dla tego modelu aby oszacować jakie jest prawdopodobieństwo tego, że spółka w następnym roku wygeneruje dodatni wynik finansowy ...

W jakim celu mogę wykorzystać symulacje Monte Carlo?

Wiedząc czym jest symulacja Monte Carlo, warto zdać sobie sprawę z tego, w jak wielu dziedzinach (m.in. wyceny przedsiębiorstw czy zarządzanie ryzykiem finansowym), analiza ta może znaleźć zastosowanie. W finansach symulacja Monte Carlo jest wykorzystywana m.in. do:

- Sporządzania wycen przedsiębiorstw (m.in. poprzez określanie zmienności współczynnika beta, wykorzystywanego do wyznaczania kosztu kapitału własnego modelem CAPM).

- Sporządzania wycen instrumentów pochodnych (w tym kontraktów futures czy opcji).

- Przygotowywania prognoz (m.in. zdyskontowanych przepływów pieniężnych, stóp procentowych, ryzyka rynkowego).

- Oceny opłacalności projektów inwestycyjnych.


Niemniej, im większa kreatywność analityka, tym więcej zastosowań może on znaleźć dla symulacji Monte Carlo - znając jej podstawowe założenia oraz silne i słabe storony.

Przykładowa symulacja Monte Carlo w excelu

Chociaż każda symulacja Monte Carlo dla tych samych danych może przybierać inny kształt, jeśli sporządzana jest przez różnych analityków, to w niniejszym amatorskim opracowaniu, postaram się przedstawić formę symulacji MC, która w moim odczuciu jest najczęściej stosowana przez praktyków rynkowych. Należy mieć jednak na uwadze, że prawdopodobnie bardzo dużo elementów tejże analizy można zrobić inaczej - lepiej lub gorzej, a każda metoda będzie cechowała się swoimi za i przeciw. Przejdźmy jednak do sedna sprawy ...

 

Wprowadzenie

Przeprowadzając przykładową symulacje Monte Carlo, posłużymy się wykorzystywanym już wcześniej przykładem młodego inwestora, który postanowił ustalić zysk netto analizowanej spółki (powiedzmy, że będzie to spółka MSN). Zakładamy więc, że inwestor posiada dane finansowe spółki MSN za 5 poprzednich lat, ponadto zna on prognozowaną wartość kosztów produkcji, która w roku 2016 wyniesie 4500 PLN, oraz wartość pozostałych kosztów (w tym kosztów zarządu), która we wszystkich analizowanych okresach jest równa 1000 PLN. 

 

Krok 1

Mając dane historyczne spółki MSN oraz prognozowane wybrane wielkości ze sprawozdań finansowych za rok 2016, należałoby nanieść wszystkie zmienne do modelu, pamiętając o tym, żeby wielkości wynikowe; takie jak wynik ze sprzedaży, zysk brutto czy netto były obliczone za pomocą formuły a nie wpisane ręcznie.

Model finansowy

Powyżej przedstawiony został zbiór wszystkich znanych inwestorowi informacji o spółce MSN. Na pomarańczowo zaznaczona została jedyna wartość, której prognozy nie posiadamy na rok 2016. Chcąc zastosować w tym miejscu symulacje Monte Carlo, należy zastosować funkcję "ROZKŁ.NORMALNY.ODWR", która podaje nam odwrócone wartości dla rozkładu normalnego przy znanej średniej stopie zwrotu oraz odchyleniu standardowym prognozowanej zmiennej, do czego nawiązywać będziemy w następnym kroku. 

 

Krok 2

Aby ustalić średnią stopę zwrotu oraz odchylenie standardowe z przychodów ze sprzedaży, powinniśmy wprowadzić do naszego modelu dodatkowy wiersz, który będzie obrazował stopy zwrotu za poszczególne okresy dla badanej zmiennej (w naszym przypadku będą to przychody ze sprzedaży). Po obliczeniu stóp zwrotu (w zależności od preferencji analityka, mogą być to arytmetyczne, geometryczne oraz logarytmiczne stopy zwrotu), należy obliczyć średnią stopę zwrotu z okresów historycznych oraz ich odchylenie standardowe (co przedstawia rysunek poniżej).

 

Następnie, w komórce oznaczonej pomarańczowym kolorem dla stopy zwrotu przychodów ze sprzedaży za rok 2016, należy wprowadzić wcześniej nadmienioną funkcję z odwróconym rozkładem normalnym. W tym miejscu napotkacie się państwo na kolejny problem ... otóż znamy już średnią stopę zwrotu oraz odchylenie standardowe za lata 2011-2015, co jednak z okienkiem "prawdopodobieństwo", które zwraca prawdopodobieństwo dla normalnego rozkładu normalnego? Chcąc przeprowadzić symulacje Monte Carlo powinniśmy na tym etapie posłużyć się czynnikiem losowości (nie znając dokładnej wartości prawdopodobieństwa rozkładu dla naszego modelu) - wykorzystajmy więc funkcje "los()" w okienku prawdopodobieństwa. Cała formuła będzie więc przyjmowała następującą postać

"=ROZKŁ.NORMALNY.ODWR(LOS();D15;D16)"

Komórka stopy zwrotu dla przychodów ze sprzedaży ze względu na wprowadzenie do formuły czynnika losowego będzie przyjmować różne wartości przy każdym odświeżeniu arkusza. Na tym etapie, jedyną zmienną, której nie posiadamy jest wartość przychodów ze sprzedaży, którą obliczymy prostym wzorem "Przychody ze sprzedaży w roku 2015 * (1+ stopa zwrotu ze sprzedaży w roku 2016)". Pamiętajmy jednak, że zysk netto w roku 2016 musi zostać obliczony za pomocą formuły w excelu, która będzie opierała się na następującym wzorze: przychody ze sprzedaży - koszty sprzedaży - koszty zarządu i pozostałe - podatek dochodowy. Cały model powinien wyglądać teraz następująco:

 

 

Krok 3

Zazwyczaj na tym etapie zaczynają się schody, przez co wszystkie obliczenia powinniśmy wykonywać bardzo ostrożnie, gdyż późniejsze odszukanie ewentualnego błędu w arkuszu może okazać się bardzo czasochłonne. 

Po stworzeniu tabeli z wszystkimi danymi za lata poprzednie oraz prognozą (obarczoną czynnikiem losowości) za rok 2016, powinniśmy przenieść się do kolejnego arkusza w excelu, w którym stworzymy 2 kolumny. Kolumna 1 będzie składała się z liczb od 1 do X. Sam X natomiast - będzie liczbą symulacji, którą chcielibyśmy przeprowadzić. Na poczet niniejszej analizy załóżmy, że wykorzystamy 1000 symulacji do otrzymania rozkładu prawdopodobieństwa postulowanej wielkości zysku spółki MSN za rok 2016 - tak więc kolumna A będzie składała się z licz od 1 do 1000.

Kolumna obok, będzie natomiast zyskiem netto, otrzymanym na wskutek przeprowadzenia poszczególnej symulacji. Aby tego dokonać, powinniśmy w pierwszym wierszu kolumny B (po jej nagłówku) z wykorzystaniem znaku "=" stworzyć odnośnik do zysku netto z roku 2016, który obliczyliśmy w poprzednim arkuszu. Co ważne, aby wykonać 999 kolejnych symulacji nie wystarczy tradycyjnie przeciągnąć komórki B2 w dół ... należy więc zaznaczyć obie kolumny od obserwacji 1 do 1000 (kolumny z tytułami nr Symulacji i Zysk netto), następnie w zakładce dane --> analiza warunkowa ---> tabela danych zostawiamy puste okienko "wierszowa komórka wejściowa" a w okienku "Kolumnowa komórka wejściowa" zaznaczamy losową pustą komórkę w arkuszu po czym przyciskamy przycisk ok. Docelowo powinniśmy otrzymać tabelę z rysunku powyżej (z tą różnicą, że nasze obserwacje będą cechowały się numerami od 1 do 1000).


Krok 4

W tym, względnie prostym kroku, tworzymy małą tabelę, w której powinniśmy obliczyć:

1) W wierszu nr 1: minimum dla naszych symulacji (poprzez formułę MIN zaznaczając symulacje zysku netto przedsiębiorstwa od 1 do 1000)

2) W wierszu nr 2: maksimum dla naszych symulacji

3) W wierszu nr 3 "szerokość"

Szerokość obliczana w wierszu nr 3, jest po prostu różnicą między maximum a minimum podzieloną przez liczbę przedziałów (do czego dojdziemy w kolejnym roku). Zatrzymując się jednak na przedziałach - są one elementem, który w zależności od analityka można wyliczać na kilka sposobów. 

Niektórzy przyjmują, że liczba przedziałów w symulacjach Monte Carlo powinna zawsze mieścić się w granicy 30-40 (np 40 dla każdej symulacji). Inna szkoła mówi o tym, że liczba przedziałów powinna być równa pierwiastkowi z liczby symulacji - w naszym wypadku byłyby to ok. 32 przedziały (pierwiastek z 1000). W celu uproszczenia przykładu założymy natomiast, że liczba przedziałów będzie równa 40. dlatego też wiersz nr 3 przyjmie wartość (MAX - MIN)/40, a całość kroku nr 4 będzie przyjmować postać przedstawioną na powyższym rysunku. Co dla wyżej przedstawionej symulacji mówi nam o tym, że dla 1000 różnych symulacji przeprowadzonych dla zysku netto spółki MSN, jego najniższy poziom wyniósł -3060,43 zł natomiast najwyższa wartość 3653,54 zł, będzie to tzw. rozpiętość pomiędzy możliwym minimalnym a maksymalnym zyskiem spółki.


Krok 5

Zdecydowanie najbardziej złożony krok w procesie tworzenia naszej analizy. Zacznijmy jednak od tego, ze w kroku nr 4 zatwierdziliśmy liczbę przedziałów na poziomie 40. Dlatego też, w pierwszej kolumnie (nazwanej nr przedziałów lub nr) wpisujemy kolejno liczby od 1 do 40 (tak jak na poniższym rysunku). 

Przejdźmy więc do obliczenia kolumny o tytule "Próby". Otóż pierwszą wartością (-4070) jest nasze minimum obliczone już na etapie kroku nr 4. Dlatego też w komórce (Próby:1) tworzymy odnośnik poprzez znak "=" do komórki minimum. Następnie w komórce poniżej wpisujemy formułę "=F8+$F$5" przy czym  w moim przypadku komórka F8 jest komórką powyższą (-4070), a komórka F5 z wartościami bezwzględnymi to komórka z szerokością obliczoną w kroku 4. Mając obliczoną wartość drugiej próby przeciągamy ją po prostu do końca (do numeru 40). W ten sposób, nasze próby rozpoczynają się od wartości minimalnej, a każda kolejna jest wyższa o wartość "szerokości".

Teraz skupmy się na obliczeniu częstości, co z całą pewnością ułatwi nam formuła excela "CZĘSTOŚĆ". dla komórki z numerem wiersza 1 wprowadzamy formułę częstość, przy czym w okienku "Tablica_Dane" zaznaczamy nasze symulacje zysku netto (1000 symulacji, które przeprowadziliśmy w kroku nr 3, natomiast okienko "Tablica_przedziały" to nasze 40 przedziałów (tytuł próby), które obliczyliśmy przed chwilą, akceptując formułę otrzymamy wynik częstości dla pierwszego przedziału. Następnym etapem naszych prac analitycznych, będzie ustalenie częstości dla pozostałych przedziałów. Aby tego dokonać, zaznaczamy komórki od obliczonej już częstości do końca naszej tabeli +1! oznacza to, że nie zaznaczamy tylko kolumny odpowiadającej 40 przedziałów ale o jedną komórkę dalej - więc 41 przedziałów. Jest tak dlatego, że funkcja częstość pokazuje nam ile symulacji mieści się w danym przedziale przyjmując wartości równe lub mniejsze niż dany przedział - dlatego poza naszymi przedziałami musimy pamiętać, że symulacja, którą obliczyliśmy w wierszu MAXIMUM, może przyjąć wartość wyższą niżeli wartość ostatniego przedziału. Po zaznaczeniu 41 komórek przyciskami lewym przyciskiem myszy na pasek formuł i równocześnie wciskamy "lewy ctr + lewy shift + enter". Taka kombinacja powinna pomóc nam z ustaleniem częstości dla 40 przedziałów + częstości dla symulacji wyższej niż najwyższy przedział w naszej tabeli. Aby sprawdzić, czy wszystkie obliczenia wykonane do tej pory są poprawne, powinniśmy zsumować 41 komórek z częstością, przy czym wynik na poziomie 1000 będzie tutaj wartością pożądaną (ponieważ mieliśmy 1000 symulacji w modelu - krok nr 3). Jeżeli otrzymałeś/aś wynik inny niż 1000 powinieneś zweryfikować swój model szukając ewentualnych przeoczeń/błędów/literówek. Jeżeli natomiast Twój wynik jest równy 1000 ... Gratulacje! możemy kontynuować konstruowanie naszej symulacji Monte Carlo. 

Częstością względną nazwałem po prostu % symulacji, który znajduje się w danym przedziale Obliczyć go możemy dzieląc częstość w danej komórce przez sumę wszystkich symulacji, czyli przez 1000. Tak więc częstość względna [%] w komórce pierwszej wyniesie 1/1000 *100%, w drugiej 0/1000 * 100% itp.

Częstość skumulowana w pierwszym wierszu będzie równa komórce obok (0,1%) dla kolejnego wiersza natomiast, będzie to komórka poniżej + komórka obok tj. 0,1% + 0,0% itp. - w tym przypadku możemy przeciągnąć w dół formułę dla drugiej komórki. po 40 przedziale wynik częstości skumulowanej powinien być równy 100%.


Krok 6

Nie będący krokiem obligatoryjnym, stanowi graficzne przedstawienie dotychczas sporządzonych przez nas analiz. Najczęściej stosuje się tutaj dwa wykresy.

1) wykres częstości względnej [%] - który prezentuje w przybliżeniu jaki jest % wystąpienia danego wyniku finansowego spółki, a powinien on wyglądać mniej więcej tak: (będąc względnie zbliżonym do rozkładu normalnego).

Częstość względna, rozkład pseudo normalny

2) wykres częstości skumulowanej 


Interpretacja otrzymanych wyników

Czyli to co najważniejsze w naszej symulacji- jakoby, że wykonaliśmy masę pracy z wyliczeniem prostego- i co warto sobie uświadomić najbardziej podstawowego modelu Monte Carlo, dobrze byłoby dowiedzieć się, jak możemy wykorzystać otrzymane wyniki analiz. Celem młodego inwestora, którego przykładem posługiwaliśmy się już na początku niniejszego artykułu - było oszacowanie najbardziej prawdopodobnego zysku dla przedsiębiorstwa MSN. Przedstawiony powyżej wykres częstości względnej [%] (z niebieskimi kolumnami) pokazuje nam, że najbardziej prawdopodobne jest uzyskanie zysku netto spółki MSN w granicach 96 PLN a szanse na to wynoszą ponad 7%, równie prawdopodobne jest osiągnięcie zysku w granicach 409 PLN (ok. 6,9%). 

Na podstawie ostatniego etapu naszej symulacji możemy określić również jakie jest przybliżone prawdopodobieństwo (dla 1000 symulacji) osiągnięcia straty dla analizowanego przedsiębiorstwa. Jak widać na rysunku przedstawionym obok, wyniki ujemne zostały odznaczone cienką czarną linią, a częstość skumulowana dla ostatniej wartości ujemnej wynosi 41,4% - innymi słowy, inwestor oszacował, że spółka ma ok. 41,4% na osiągnięcie straty i 58,6% szansy na osiągnięcie zysku w kolejnym roku funkcjonowania. Sumując odpowiednie wartości komórek częstości względnej możemy też ustalić, że szansa, iż zysk przedsiębiorstwa będzie kształtował się w przedziale od -1000 do 1000 wynosi ok. 65%. Ponadto szanse, że przedsiębiorstwo poniesie ponadprzeciętny zysk w wysokości ponad 3000 PLN lub ponadprzeciętnej straty w wysokości poniżej 3000 PLN straty wynosi łącznie ok 0,6%.

Podsumowanie

Znając podstawowe możliwości wykorzystania symulacji Monte Carlo, oraz będąc w posiadaniu zdolności jej samodzielnego sporządzania, jesteśmy w stanie obiektywnie ocenić, jakie znaczenie ten rodzaj analizy matematycznej będzie stanowił w sporządzanych przez nas analizach czy raportach. Nawet w przypadku kompletnego braku zainteresowania analizą MC, czytelnik powinien dostrzec te aspekty świata finansów, które symulacja ta może w względnie prosty sposób zarysować (m.in. prawdopodobieństwo wystąpienia niektórych zjawisk ekonomicznych czy finansowych). Zwolennicy opisanej powyżej metody powinni natomiast uświadomić sobie, że tak jak każda analiza, także Monte Carlo jest obarczona pewnymi niedoskonałościami - czego niezaprzeczalnym dowodem jest magiczny przycisk F9 w excelu, który to w przeciągu ułamka sekundy potrafi zmienić otrzymane przez nas ówcześnie wyniki, nie pozwalając na sformułowanie precyzyjnej odpowiedzi w zakresie kształtowania się przyszłej wielkości danego zjawiska - o ile taka  odpowiedź, może mieć w ogóle rację bytu w świecie finansów  ...


W załączniku gotowy plik z przeprowadzoną symulacją Monte Carlo z analizowanego przykładu.

Przykładowa symulacja Monte Carlo
Monte Carlo.xlsx
Microsoft Excel Table 51.5 KB

Write a comment

Comments: 1
  • #1

    Magda (Monday, 02 January 2017 22:46)

    Cześć, jeśli miałbyś wziąć pod uwagę dekompozycję Choleskiego to jakby to wyglądało?