czym jest symulacja Monte Carlo?

Symulacja Monte Carlo jest procesem wykorzystującym krzywe prawdopodobieństwa do określenia prawdopodobieństwa wyniku. Możesz podrapać się po głowie i powiedzieć… „Hej Rick, krzywa rozkładu ma tablicę wartości. Jak dokładnie określić prawdopodobieństwo wyniku?”A jeszcze lepiej, jak to zrobić w Microsoft Excel bez specjalnego dodatku, którego nigdy nie zapytasz.Odbywa się to poprzez uruchamianie symulacji tysiące razy i analizowanie rozkładu wyjścia. Jest to szczególnie ważne, gdy analizuje się wyniki kilku krzywych rozkładu, które wzajemnie się przenikają.Przykład:

• # sprzedanych jednostek może mieć krzywą rozkładu
• pomnożoną przez cenę rynkową, która może mieć inną krzywą rozkładu
• minus płace zmienne, które mają inną krzywą
• itd., itp.

gdy wszystkie te dystrybucje są mieszane, wyjście może być dość złożone. Przeprowadzanie tysięcy iteracji (lub symulacji) tej krzywej może dać ci pewien wgląd. Jest to szczególnie przydatne w analizie potencjalnego ryzyka dla decyzji.

opisz Monte Carlo

opisując symulację Monte Carlo, często odwołuję się do filmowych gier wojennych z lat 80., w których Młody Mathew Broderick (wcześniej Ferris Bueller) jest hakerem, który używa swojego modemu do włamywania się do komputerów Pentagonu i rozpoczęcia trzeciej wojny światowej. Tak jakby. Następnie miał Komputery Pentagon zrobić wiele symulacji gry Tic Tac Toe nauczyć komputer, że nikt nie będzie wojny nuklearnej-i uratować świat w procesie.Dzięki Ferris. Jesteś bohaterem.Oto rzut oka filmu, aby pokazać wielki czas Monte Carlo w akcji. Zakładam, że przeoczysz politykę, krępującego mężczyznę i oczywiście Dabneya Colemana.

krzywe rozkładu

istnieją różne krzywe rozkładu, których możesz użyć do skonfigurowania symulacji Monte Carlo. I te krzywe mogą być wymienne w oparciu o zmienną. Microsoft nie ma w pasku menu formuły „Do symulacji Monte Carlo”:)

rozkład jednorodny

w rozkładzie jednorodnym istnieje równe prawdopodobieństwo pomiędzy minimum a maksimum. Jednolity rozkład wygląda jak prostokąt.

rozkład normalny (Gaussa)

jest to również standardowa krzywa w kształcie dzwonka. Ten wzór symulacji Monte Carlo charakteryzuje się równomiernym rozkładem po każdej stronie (mediana i średnia są takie same-i nie ma krzywizny). Ogony krzywej ciągną się do nieskończoności. Tak więc może to nie być idealna krzywa dla cen domów, gdzie kilka domów z najwyższej półki zwiększa średnią (średnią) znacznie powyżej mediany lub w przypadkach, gdy istnieje twarde minimum lub maksimum. Przykładem może być płaca minimalna w Twoim regionie. Należy pamiętać, że nazwa funkcji różni się w zależności od wersji.

rozkład Lognormalny

rozkład, w którym logarytm jest zwykle rozkładany ze średnią i odchyleniem standardowym. Tak więc konfiguracja jest podobna do rozkładu normalnego, ale proszę zauważyć, że zmienne średnia i standard_dev mają reprezentować logarytm.

Dystrybucja Poissona

jest to prawdopodobnie najbardziej niewykorzystana Dystrybucja. Domyślnie Wiele osób używa krzywej rozkładu normalnego, gdy Poisson jest lepiej dopasowany do ich modeli. Poissona najlepiej opisać, gdy na samym początku znajduje się Duży rozkład, który szybko rozprasza się do długiego ogona z jednej strony. Przykładem może być call center, w którym żadne połączenia nie są odbierane przed drugim zerem. Następnie większość połączeń odebranych w pierwszych odstępach 2 (powiedzmy 30 i 60 sekund) z szybkim spadkiem głośności i długim ogonem, z bardzo małą liczbą połączeń odebranych w ciągu 20 minut (rzekomo).

celem tutaj nie jest pokazanie każdej możliwej dystrybucji w Excelu, ponieważ jest to poza zakresem tego artykułu. Raczej, aby upewnić się, że wiesz, że istnieje wiele opcji dostępnych dla symulacji Monte Carlo. Nie wpadaj w pułapkę zakładania, że krzywa rozkładu normalnego jest właściwym dopasowaniem do wszystkich modeli danych. Aby znaleźć więcej krzywych, przejdź do funkcji statystycznych w skoroszycie programu Excel i zbadaj. Jeśli masz pytania, umieść je w komentarzach poniżej.

budując Model

dla tej konfiguracji przyjmiemy rozkład normalny i 1000 iteracji.

zmienne wejściowe

konfiguracja zakłada rozkład normalny. Rozkład normalny wymaga trzech zmiennych; prawdopodobieństwo, średnia i odchylenie standardowe. W pierwszym kroku zajmiemy się średnią i odchyleniem standardowym. Zakładam, że problem prognozowania finansów, który składa się z przychodów, kosztów zmiennych i stałych. Gdzie przychód minus koszty zmienne minus koszty stałe równa się zysk. Koszty stałe są kosztami utopionymi w zakładzie i sprzęcie, więc nie zakłada się krzywej rozkładu. Dla przychodów i kosztów zmiennych przyjmuje się krzywe rozkładu.

pierwsza symulacja

poniższy przykład wskazuje ustawienia przychodu. Wzór można skopiować i wkleić do komórki D6 w celu uzyskania zmiennych wydatków. Dla przychodów i wydatków mamy Ci normę funkcji.INV (), gdzie parametry są:

• prawdopodobieństwo = funkcja RAND() do wywołania liczby losowej na podstawie innych kryteriów w rozkładzie.
• średnia = średnia zastosowana w Kroku 1. Dla przychodów jest to C3.
• odchylenie standardowe = odchylenie standardowe stosowane w Kroku 1. Dla przychodów to C4

ponieważ rand () jest używane jako prawdopodobieństwo, losowe prawdopodobieństwo jest generowane przy odświeżaniu. Wykorzystamy to na naszą korzyść w następnym kroku.

1000 symulacji

istnieje kilka sposobów wykonania 1000 lub więcej wariantów. Najprostszą opcją jest wzięcie wzoru z kroku # 2 i uczynienie go absolutnym. Następnie skopiuj i wklej 1000 razy. To proste, ale niezbyt wymyślne. A jeśli Ferris Bueller może uratować świat, pokazując komputerowi nową grę w kółko i krzyżyk, to możemy urozmaicić tę analizę. Zagrajmy w świat stolików.

• najpierw chcemy utworzyć kontur tabeli. Robimy to, wymieniając liczby od 1 do 1000 w wierszach. Na poniższym przykładzie lista numerów zaczyna się od B12.
• w następnej kolumnie, w komórce C12, odwołamy się do pierwszej iteracji.

• następnie zaznacz obszar, w którym chcemy umieścić 1000 iteracji
• Wybierz dane > tabele danych
• dla komórki wejściowej kolumny: wybierz pustą komórkę. W pobranym pliku komórka d11 jest zaznaczona
• wybierz OK

• Po wybraniu opcji OK z poprzedniego kroku wstawiana jest tabela, która autopopuluje 1000 symulacji

statystyki zbiorcze

po uruchomieniu symulacji nadszedł czas na zebranie statystyk zbiorczych. Można to zrobić na wiele sposobów. W tym przykładzie użyłem funkcji COUNTIF (), aby określić procent nieopłacalnych symulacji i prawdopodobieństwo zysku większego niż 1 milion dolarów. Zgodnie z oczekiwaniami prawdopodobieństwo powyżej 1 mln USD wynosi około 50%. To dlatego, że użyliśmy krzywych rozkładu normalnego, które są równomiernie rozmieszczone wokół średniej, która wynosiła $1m. prawdopodobieństwo utraty pieniędzy wynosi 4,8%. Zostało to zebrane za pomocą funkcji COUNTIF() do zliczania symulacji, które były mniejsze niż zero, i dzielenia przez 1000 całkowitych iteracji.

Pobierz plik

w powyższym filmie Oz pyta o różne zastosowania symulacji Monte Carlo. Do czego go użyłeś? Czy są jakieś konkretne przykłady, którymi możesz podzielić się z grupą? Jeśli tak, zostaw notatkę poniżej w sekcji komentarzy. Zachęcamy również do zapisania się do naszego newslettera, aby być na bieżąco jako nowy Excel.TV pokazy są ogłaszane. Zostaw mi wiadomość poniżej, aby pozostać w kontakcie.