Tworzenie symulacji Monte Carlo za pomocą programu Excel

Symulację Monte Carlo można opracować za pomocą Microsoft Excel i gry w kości. Symulacja Monte Carlo jest matematyczną metodą numeryczną, która wykorzystuje losowe losowania do wykonywania obliczeń i skomplikowanych problemów. Obecnie jest szeroko stosowany i odgrywa kluczową rolę w różnych dziedzinach, takich jak finanse, fizyka, chemia i ekonomia.

Symulacja Monte Carlo

Metoda Monte Carlo została wynaleziona przez Nicolasa Metropolis w 1947 roku i ma na celu rozwiązywanie złożonych problemów za pomocą metod losowych i probabilistycznych. Termin „Monte Carlo” pochodzi od obszaru administracyjnego Monako znanego jako miejsce, w którym grają europejskie elity. Używamy metody Monte Carlo, gdy problem jest zbyt złożony i trudny do wykonania na podstawie bezpośrednich obliczeń. Duża liczba iteracji pozwala na symulację rozkładu normalnego.

Metoda symulacji Monte Carlo oblicza prawdopodobieństwa dla całek i rozwiązuje równania różniczkowe cząstkowe, wprowadzając w ten sposób statystyczne podejście do ryzyka w decyzji probabilistycznej. Chociaż istnieje wiele zaawansowanych narzędzi statystycznych do tworzenia symulacji Monte Carlo, łatwiej jest symulować prawo normalne i prawo jednolite za pomocą programu Microsoft Excel i omijać podstawy matematyczne.

W przypadku symulacji Monte Carlo wyodrębniamy szereg kluczowych zmiennych, które kontrolują i opisują wynik eksperymentu, a następnie przypisujemy rozkład prawdopodobieństwa po wykonaniu dużej liczby losowych próbek. Weźmy grę w kości jako model.

Gra w kości

Oto jak toczy się gra w kości:

• Gracz rzuca 3 kośćmi, które mają 6 stron 3 razy.

• Jeśli suma 3 rzutów wynosi 7 lub 11, gracz wygrywa.

• Jeśli suma 3 rzutów wynosi: 3, 4, 5, 16, 17 lub 18, gracz przegrywa.

• Jeśli suma jest jakikolwiek inny wynik, gracz gra ponownie i ponownie rzuca kośćmi.

• Gdy gracz ponownie rzuca kostką, gra jest kontynuowana w ten sam sposób, z tym wyjątkiem, że gracz wygrywa, gdy suma jest równa sumie określonej w pierwszej rundzie.

Zaleca się również użycie tabeli danych do wygenerowania wyników. Ponadto potrzeba 5000 wyników, aby przygotować symulację Monte Carlo.

Krok 1: Wydarzenia rzucania kostką

Najpierw opracowujemy zakres danych z wynikami każdej z 3 kości na 50 rzutów. W tym celu proponuje się użycie funkcji „RANDBETWEEN (1, 6)”. Dlatego za każdym razem, gdy klikamy F9, generujemy nowy zestaw wyników rzutów. Komórka „Wynik” jest sumą wyników z 3 rzutów.

Krok 2: Zakres wyników

Następnie musimy opracować szereg danych w celu zidentyfikowania możliwych wyników dla pierwszej rundy i kolejnych rund. Istnieje 3-kolumnowy zakres danych. W pierwszej kolumnie mamy liczby od 1 do 18. Liczby te przedstawiają możliwe wyniki po trzykrotnym rzucie kostką: maksymalnie 3 * 6 = 18. Zauważysz, że dla komórek 1 i 2 ustalenia są N / A, ponieważ nie można uzyskać 1 lub 2 za pomocą 3 kości. Minimum to 3.

W drugiej kolumnie zawarte są możliwe wnioski po pierwszej rundzie. Jak stwierdzono we wstępnym oświadczeniu, albo gracz wygrywa (wygrywa), albo przegrywa (przegrywa), albo ponownie gra (przerzuca), w zależności od wyniku (łącznie 3 rzuty kostkami).

W trzeciej kolumnie zarejestrowano możliwe wnioski do kolejnych rund. Możemy osiągnąć te wyniki za pomocą funkcji „JEŻELI”. Zapewnia to, że jeśli uzyskany wynik jest równoważny z wynikiem uzyskanym w pierwszej rundzie, wygrywamy, w przeciwnym razie postępujemy zgodnie z początkowymi zasadami oryginalnej gry, aby ustalić, czy ponownie rzuć kostką.

Krok 3: Wnioski

W tym kroku określamy wynik 50 rzutów kostką. Pierwszy wniosek można uzyskać za pomocą funkcji indeksu. Ta funkcja wyszukuje możliwe wyniki pierwszej rundy, a wniosek odpowiada uzyskanemu wynikowi. Na przykład, otrzymując 6, gramy ponownie.

Można uzyskać wyniki innych rzutów kostką, używając funkcji „LUB” i funkcji indeksu zagnieżdżonej w funkcji „JEŻELI”. Ta funkcja mówi Excelowi: „Jeśli poprzedni wynik to Wygrana lub Przegrana”, przestań rzucać kostką, ponieważ po wygraniu lub przegraniu jesteśmy skończeni. W przeciwnym razie przechodzimy do kolumny następujących możliwych wniosków i identyfikujemy wnioski z wyniku.

Krok 4: Liczba rzutów kostką

Teraz określamy liczbę wymaganych rzutów kostką przed przegraną lub wygraną. Aby to zrobić, możemy użyć funkcji „LICZ.JEŻELI”, która wymaga, aby Excel policzył wyniki „Ponowne rzutowanie” i dodał do niej liczbę 1. Dodaje jedną, ponieważ mamy jedną dodatkową rundę i otrzymujemy końcowy wynik (wygrany lub przegrany).

Krok 5: Symulacja

Opracowujemy zakres do śledzenia wyników różnych symulacji. Aby to zrobić, utworzymy trzy kolumny. W pierwszej kolumnie jedna z zawartych liczb to 5000. W drugiej kolumnie szukamy wyniku po 50 rzutach kostką. W trzeciej kolumnie, tytule kolumny, sprawdzimy liczbę rzutów kostką, zanim uzyskamy ostateczny status (wygrana lub przegrana).

Następnie utworzymy tabelę analizy wrażliwości przy użyciu danych funkcji lub tabeli danych tabeli (ta czułość zostanie wstawiona do drugiej tabeli i trzeciej kolumny). W tej analizie wrażliwości liczba zdarzeń 1–5 000 musi zostać wstawiona do komórki A1 pliku. W rzeczywistości można wybrać dowolną pustą komórkę. Chodzi o to, aby za każdym razem wymusić ponowne obliczenie, a tym samym uzyskać nowe rzuty kostką (wyniki nowych symulacji) bez uszkadzania formuł.

Krok 6: Prawdopodobieństwo

Możemy w końcu obliczyć prawdopodobieństwo wygranej i przegranej. Robimy to za pomocą funkcji „COUNTIF”. Formuła zlicza liczbę „wygranych” i „przegranych”, a następnie dzieli przez całkowitą liczbę zdarzeń, 5000, aby uzyskać odpowiednią proporcję jednego i drugiego. W końcu widzimy, że prawdopodobieństwo uzyskania wyniku wygranej wynosi 73, 2%, a zatem uzyskanie wyniku przegranego wynosi 26, 8%.