Crearea unei simulări Monte Carlo folosind Excel

O simulare Monte Carlo poate fi dezvoltată folosind Microsoft Excel și un joc de zaruri. Simularea Monte Carlo este o metodă numerică matematică care utilizează trageri aleatorii pentru a efectua calcule și probleme complexe. Astăzi, este utilizat pe scară largă și joacă un rol cheie în diverse domenii precum finanța, fizica, chimia și economia.

Simularea Monte Carlo

Metoda Monte Carlo a fost inventată de Nicolas Metropolis în 1947 și încearcă să rezolve probleme complexe folosind metode aleatorii și probabilistice. Termenul "Monte Carlo" provine din zona administrativă din Monaco cunoscută popular ca un loc în care jocurile de elite europene. Folosim metoda Monte Carlo când problema este prea complexă și dificil de făcut prin calcul direct. Un număr mare de iterații permite o simulare a distribuției normale.

Metoda de simulare Monte Carlo calculează probabilitățile pentru integrale și rezolvă ecuațiile diferențiale parțiale, introducând astfel o abordare statistică a riscului într-o decizie probabilistică. Deși există multe instrumente statistice avansate pentru a crea simulări Monte Carlo, este mai ușor să simulați legea normală și legea uniformă folosind Microsoft Excel și ocoliți fundamentele matematice.

Pentru simularea Monte Carlo, izolăm o serie de variabile cheie care controlează și descrie rezultatul experimentului, apoi atribuim o distribuție a probabilității după ce se efectuează un număr mare de eșantioane aleatorii. Să luăm un model de zaruri ca model.

Joc de zaruri

Iată cum se rulează jocul zarurilor:

• Jucătorul aruncă trei zaruri care au 6 laturi de 3 ori.

• Dacă totalul celor 3 aruncări este de 7 sau 11, jucătorul câștigă.

• Dacă totalul celor 3 aruncări este: 3, 4, 5, 16, 17 sau 18, jucătorul pierde.

• Dacă totalul este alt rezultat, jucătorul joacă din nou și re-rulează zarurile.

• Când jucătorul aruncă din nou zarurile, jocul continuă în același mod, cu excepția faptului că jucătorul câștigă atunci când totalul este egal cu suma determinată în primul tur.

De asemenea, se recomandă utilizarea unui tabel de date pentru a genera rezultatele. Mai mult, sunt necesare 5.000 de rezultate pentru pregătirea simulării Monte Carlo.

Pasul 1: Evenimente rulante pe zaruri

În primul rând, dezvoltăm o serie de date cu rezultatele fiecăreia dintre cele 3 zaruri pentru 50 de rulouri. Pentru a face acest lucru, se propune utilizarea funcției „RANDBETWEEN (1, 6)”. Astfel, de fiecare dată când facem clic pe F9, generăm un nou set de rezultate pe listă. Celula „Rezultat” este suma totală a rezultatelor din cele 3 rulouri.

Pasul 2: gama de rezultate

Apoi, trebuie să dezvoltăm o serie de date pentru a identifica rezultatele posibile pentru prima rundă și runde ulterioare. Există un interval de date cu 3 coloane. În prima coloană, avem numerele de la 1 la 18. Aceste cifre reprezintă rezultatele posibile după rularea zarurilor de 3 ori: maximul este de 3 * 6 = 18. Veți observa că pentru celulele 1 și 2, rezultatele sunt N / A, deoarece este imposibil să obțineți un 1 sau 2 folosind 3 zaruri. Minimul este de 3.

În a doua coloană sunt incluse concluziile posibile după prima rundă. Așa cum s-a spus în declarația inițială, jucătorul câștigă (câștigă) sau pierde (pierde), sau reda (re-rulează), în funcție de rezultat (totalul a 3 rulouri de zar).

În cea de-a treia coloană, sunt înregistrate concluziile posibile pentru runde ulterioare. Putem obține aceste rezultate folosind funcția „IF”. Acest lucru se asigură că dacă rezultatul obținut este echivalent cu rezultatul obținut în prima rundă, vom câștiga, altfel urmăm regulile inițiale ale jocului inițial pentru a determina dacă re-rulăm zarurile.

Pasul 3: Concluzii

În acest pas, identificăm rezultatul celor 50 de rulouri de zaruri. Prima concluzie poate fi obținută cu ajutorul unei funcții de index. Această funcție caută rezultatele posibile ale primei runde, concluzia corespunzând rezultatului obținut. De exemplu, când obținem 6 jucăm din nou.

Se pot obține constatările altor rulouri de zar, folosind o funcție „OR” și o funcție de index cuibărită într-o funcție „IF”. Această funcție spune Excel, „Dacă rezultatul anterior este Câștigare sau Pierdere”, încetați să rulați zarurile, deoarece odată ce am câștigat sau am pierdut am terminat. În caz contrar, mergem la coloana următoarelor concluzii posibile și identificăm concluzia rezultatului.

Pasul 4: numărul de role de zaruri

Acum, determinăm numărul de rulouri de zar necesare înainte de a pierde sau câștiga. Pentru a face acest lucru, putem folosi o funcție „COUNTIF”, care necesită Excel să numere rezultatele „Re-roll” și să adăugăm numărul 1 la acesta. Se adaugă una pentru că avem o rundă suplimentară și obținem un rezultat final (câștigăm sau pierdem).

Pasul 5: Simulare

Dezvoltăm o gamă de urmărire a rezultatelor diferitelor simulări. Pentru a face acest lucru, vom crea trei coloane. În prima coloană, una dintre cifrele incluse este 5.000. În a doua coloană, vom căuta rezultatul după 50 de rulouri de zaruri. În a treia coloană, titlul coloanei, vom căuta numărul de rulouri de zaruri înainte de a obține statutul final (câștig sau pierdere).

Apoi, vom crea un tabel de analiză a sensibilității prin utilizarea datelor caracteristice sau a tabelului de date (această sensibilitate va fi introdusă în cel de-al doilea tabel și a treia coloane). În această analiză a sensibilității, numărul de evenimente de la 1 la 5.000 trebuie introdus în celula A1 a fișierului. De fapt, s-ar putea alege orice celulă goală. Ideea este pur și simplu să forțezi o recalculare de fiecare dată și astfel să obții noi rulouri de zaruri (rezultate ale noilor simulări) fără a deteriora formulele la locul lor.

Pasul 6: Probabilitate

În sfârșit, putem calcula probabilitățile de a câștiga și de a pierde. Facem acest lucru folosind funcția „COUNTIF”. Formula numără numărul de „câștiga” și „pierde” apoi se împarte la numărul total de evenimente, 5.000, pentru a obține proporția respectivă de unul și de altul. În sfârșit, vedem că probabilitatea obținerii unui rezultat Win este de 73, 2% și obținerea unui rezultat Pierdere este, prin urmare, de 26, 8%.