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 folosește extrageri aleatorii pentru a efectua calcule și probleme complexe. Astăzi, este utilizat pe scară largă și joacă un rol cheie în diverse domenii, cum ar fi finanțele, fizica, chimia și economia.

Chei de luat masa

• Metoda Monte Carlo caută să rezolve probleme complexe folosind metode aleatorii și probabilistice.
• O simulare Monte Carlo poate fi dezvoltată folosind Microsoft Excel și un joc de zaruri.
• Un tabel de date poate fi utilizat pentru a genera rezultatele – sunt necesare un total de 5.000 de rezultate pentru a pregăti simularea Monte Carlo. 

Simulare Monte Carlo

Metoda Monte Carlo a fost inventată de John von Neumann și Stanislaw Ulam în anii 1940 și caută să rezolve probleme complexe folosind metode aleatorii și probabilistice. Termenul Monte Carlo se referă la zona administrativă din Monaco cunoscută popular ca un loc în care elitele europene joacă. 

Metoda de simulare Monte Carlo calculează probabilitățile pentru integrale și rezolvă ecuații parțiale diferenț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 să ocoliți bazele matematice.

Când se folosește simularea Monte Carlo

Folosim metoda Monte Carlo atunci când o problemă este prea complexă și dificil de realizat prin calcul direct. Utilizarea simulării poate ajuta la furnizarea de soluții pentru situații care se dovedesc incerte. Un număr mare de iterații permite o simulare a distribuției normale. Poate fi folosit și pentru a înțelege modul în care funcționează riscul și pentru a înțelege incertitudinea în modelele de prognoză.

După cum sa menționat mai sus, simularea este adesea utilizată în multe discipline diferite, inclusiv finanțe, științe, inginerie și gestionarea lanțului de aprovizionare – mai ales în cazurile în care există mult prea multe variabile aleatorii în joc. De exemplu, analiștii pot utiliza simulări Monte Carlo pentru a evalua instrumentele financiare derivate, inclusiv opțiuni sau pentru a determina riscurile, inclusiv probabilitatea ca o companie să își retragă datoriile.

Joc de zaruri

Pentru simularea Monte Carlo, izolăm o serie de variabile cheie care controlează și descriu rezultatul experimentului, apoi atribuim o  distribuție de probabilitate  după efectuarea unui număr mare de eșantioane. Pentru a demonstra, să luăm ca model un joc de zaruri. Iată cum rulează jocul zarurilor:

• Jucătorul aruncă trei zaruri care au șase laturi de trei ori.

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

• Dacă totalul celor trei aruncări este: trei, patru, cinci, 16, 17 sau 18, jucătorul pierde.

• Dacă totalul este orice alt rezultat, jucătorul joacă din nou și aruncă din nou 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 prima rundă.

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

---

Pentru a pregăti simularea Monte Carlo, aveți nevoie de 5.000 de rezultate.

Pasul 1: Evenimente de rulare a zarurilor

În primul rând, dezvoltăm o serie de date cu rezultatele fiecăruia dintre cele trei zaruri pentru 50 de aruncări. 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. Celula „Rezultat” este suma totală a rezultatelor din cele trei rulouri.

Pasul 2: Gama de rezultate

Apoi, trebuie să dezvoltăm o serie de date pentru a identifica rezultatele posibile pentru prima rundă și rundele următoare. Există un interval de date cu trei coloane. În prima coloană, avem numerele de la unu la 18. Aceste cifre reprezintă rezultatele posibile după lansarea zarurilor de trei ori: Maximul fiind 3 x 6 = 18. Veți observa că pentru celulele una și două, constatările sunt N / A, deoarece este imposibil să obții unul sau doi folosind trei zaruri. Minimul este de trei.

În a doua coloană sunt incluse posibilele concluzii după prima rundă. Așa cum se menționează în declarația inițială, fie jucătorul câștigă (Câștigă), fie pierde (Pierde), fie redă (Redă), în funcție de rezultat (totalul a trei aruncări de zaruri).

În a treia coloană, sunt înregistrate posibilele concluzii la rundele ulterioare. Putem obține aceste rezultate folosind funcția „IF”. Acest lucru asigură că, dacă rezultatul obținut este echivalent cu rezultatul obținut în prima rundă, câștigăm, altfel urmăm regulile inițiale ale jocului original pentru a determina dacă relansăm zarurile.

Pasul 3: Concluzii

În acest pas, identificăm rezultatul celor 50 de aruncări de zaruri. Prima concluzie poate fi obținută cu o funcție index. Această funcție caută posibilele rezultate ale primei runde, concluzia corespunzătoare rezultatului obținut. De exemplu, când rulăm un șase, jucăm din nou.

Se pot obține rezultatele altor aruncări de zaruri, folosind o funcție „SAU” și o funcție index cuibărită într-o funcție „IF”. Această funcție îi spune lui Excel: „Dacă rezultatul anterior este Câștigă sau pierde”, încetează să arunci zarurile pentru că odată ce am câștigat sau pierdut am terminat. În caz contrar, mergem la coloana următoarelor posibile concluzii și identificăm concluzia rezultatului.

Pasul 4: numărul de rulouri de zaruri

Acum, determinăm numărul de aruncări de zaruri necesare înainte de a pierde sau de a câștiga. Pentru a face acest lucru, putem folosi o funcție „COUNTIF”, care necesită ca Excel să numere rezultatele „Re-roll” și să adauge numărul unu la ea. Se adaugă unul pentru că avem o rundă suplimentară și obținem un rezultat final (câștig sau pierdere).

Pasul 5: Simulare

Dezvoltăm o gamă pentru a urmări rezultatele 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 aruncări de zaruri. În a treia coloană, titlul coloanei, vom căuta numărul de aruncări de zaruri înainte de a obține statutul final (câștiga sau pierde).

Apoi, vom crea un tabel de analiză a sensibilității folosind datele caracteristice sau tabelul Date tabel (această sensibilitate va fi inserată în al doilea tabel și în a treia coloană). În această analiză de sensibilitate, numărul de evenimente de la unu la 5.000 trebuie inserat în celula A1 a fișierului. De fapt, s-ar putea alege orice celulă goală. Ideea este pur și simplu să forțăm un recalcul de fiecare dată și astfel să obținem noi aruncări de zaruri (rezultate ale unor noi simulări) fără a deteriora formulele existente.

Pasul 6: Probabilitate

Putem calcula în cele din urmă probabilitățile de a câștiga și a pierde. Facem acest lucru folosind funcția „COUNTIF”. Formula contorizează numărul „câștigă” și „pierde”, apoi se împarte la numărul total de evenimente, 5.000, pentru a obține proporția respectivă dintre una și alta. Vedem în cele din urmă că probabilitatea de a obține un rezultat Win este de 73,2% și obținerea unui rezultat Pierdere este, prin urmare, de 26,8%.