Stvaranje Monte Carlo simulacije pomoću Excela

Monte Carlo simulacija se može razviti pomoću Microsoftovog Excela i igre s kockicama. Simulacija Monte Carlo matematička je numerička metoda koja koristi slučajne crteže za proračun i složene probleme. Danas se široko koristi i igra ključnu ulogu u raznim područjima kao što su financije, fizika, kemija i ekonomija.

Monte Carlo simulacija

Metodu Monte Carlo izumio je Nicolas Metropolis 1947. godine i nastoji riješiti složene probleme slučajnim i vjerojatnim metodama. Izraz "Monte Carlo" potječe iz administrativnog područja Monaka popularno poznatog kao mjesto na kojem se kockaju europske elite. Monte Carlo metodu koristimo kada je problem previše složen i teško ga je izravnim proračunom učiniti. Veliki broj iteracija omogućuje simulaciju normalne distribucije.

Monte Carlo simulacijska metoda izračunava vjerojatnosti integrala i rješava parcijalne diferencijalne jednadžbe, čime se u vjerojatnu odluku uvodi statistički pristup riziku. Iako postoje mnogi napredni statistički alati za stvaranje simulacija Monte Carla, lakše je simulirati normalan i jedinstveni zakon pomoću Microsofta Excel i zaobići matematičke temelje.

Za simulaciju Monte Carla izdvojimo nekoliko ključnih varijabli koje kontroliraju i opisuju ishod pokusa, a zatim dodijelimo raspodjelu vjerojatnosti nakon što se provede veliki broj slučajnih uzoraka. Uzmimo kao igra model kockice.

Igra kockica

Evo kako igra kockica:

• Igrač baca tri kockice koje imaju 6 strana 3 puta.

• Ako je ukupno 3 bacanja 7 ili 11, igrač pobjeđuje.

• Ako je ukupno 3 bacanja: 3, 4, 5, 16, 17 ili 18, igrač gubi.

• Ako je u bilo kojem drugom ishodu, igrač ponovno igra i ponovo kotrlja kockice.

• Kada igrač ponovo baci kockice, igra se nastavlja na isti način, osim što igrač pobjeđuje kada je zbroj jednak zbroju utvrđenom u prvom krugu.

Preporučuje se upotreba tablice podataka za generiranje rezultata. Nadalje, potrebno je 5000 rezultata za pripremu Monte Carlo simulacije.

1. korak: valjanje kockica

Prvo razvijamo niz podataka s rezultatima svake od 3 kockice za 50 peciva. Da biste to učinili, predlaže se korištenje funkcije "RANDBETWEEN (1, 6)". Na taj način, svaki put kada kliknemo na F9 generiramo novi set rezultata roll-a. Stanica "Ishod" je zbroj rezultata iz 3 svitka.

2. korak: raspon rezultata

Zatim moramo razviti niz podataka da bismo utvrdili moguće ishode za prvi krug i naredne runde. Postoji raspon podataka u 3 stupaca. U prvom stupcu imamo brojeve 1 do 18. Ove brojke predstavljaju moguće rezultate nakon kotanja kockica 3 puta: maksimalni je 3 * 6 = 18. Primijetit ćete da je za stanice 1 i 2, rezultati n / A jer je nemoguće dobiti 1 ili 2 pomoću 3 kockice. Minimalno je 3.

U drugom su stupcu uključeni mogući zaključci nakon prvog kruga. Kao što je navedeno u početnoj izjavi, ili igrač pobjeđuje (Win) ili gubi (Lose), ili igraju (Re-roll), ovisno o rezultatu (ukupno 3 role kockica).

U trećem stupcu zabilježeni su mogući zaključci na slijedeće runde. Ove rezultate možemo postići upotrebom funkcije "IF". To osigurava da ako dobiveni rezultat ekvivalentan rezultatu dobivenom u prvom krugu, mi pobijedimo, a u suprotnom slijedimo početna pravila izvorne igre da bismo utvrdili hoćemo li ponovo baciti kockice.

Korak 3: Zaključci

U ovom koraku identificiramo ishod 50 valjaka s kockicama. Prvi zaključak možemo dobiti indeksnom funkcijom. Ova funkcija pretražuje moguće rezultate prvog kruga, zaključak koji odgovara dobivenom rezultatu. Na primjer, kada dobijemo 6, igramo ponovo.

Možete dobiti nalaze drugih kockica, koristeći funkciju "ILI" i indeksnu funkciju ugniježđenu u "IF" funkciji. Ova funkcija kaže Excelu: "Ako je prethodni rezultat Pobjeda ili gubitak", prestanite kotati kockice jer nakon što smo pobijedili ili izgubili, gotovi smo. U suprotnom, idemo na stupac sljedećih mogućih zaključaka i identificiramo zaključak rezultata.

Korak 4: Broj kolutova kockica

Sada određujemo koliko je kockica potrebno prije gubitka ili pobjede. Da bismo to učinili, možemo upotrijebiti funkciju "COUNTIF" koja zahtijeva da Excel broji rezultate "Re-roll" i doda broj 1. To dodaje jedno jer imamo jedno dodatno kolo i postižemo konačni rezultat (pobjeda ili poraz).

5. korak: Simulacija

Razvijamo raspon za praćenje rezultata različitih simulacija. Da bismo to učinili, stvorit ćemo tri stupca. U prvom stupcu jedna od uključenih figura je 5000. U drugom stupcu potražit ćemo rezultat nakon 50 kockica kockica. U trećem stupcu, naslovu stupca, tražit ćemo broj kolutova kockica prije dobivanja konačnog statusa (pobjeda ili poraz).

Zatim ćemo kreirati tablicu analize osjetljivosti pomoću podataka o značajkama ili tablice podataka tablice (ta će osjetljivost biti umetnuta u drugu tablicu i treći stupac). U ovoj analizi osjetljivosti, brojevi događaja od 1 do 5 000 moraju biti umetnuti u ćeliju A1 datoteke. U stvari, moglo bi se odabrati bilo koja prazna ćelija. Ideja je jednostavno svaki put prisilno preračunati i na taj način dobiti nove role kockica (rezultati novih simulacija), a da pritom ne oštetite formule na mjestu.

Korak 6: Vjerojatnost

Konačno možemo izračunati vjerojatnost pobjede i gubitka. To radimo pomoću funkcije "COUNTIF". Formula broji broj „pobjede“ i „gubitka“, a zatim dijeli s ukupnim brojem događaja, 5.000, kako bi se dobio odgovarajući omjer jednog i drugog. Konačno vidimo da je vjerovatnoća dobivanja dobitnog rezultata 73, 2% i gubitka rezultata stoga 26, 8%.