Ustvarjanje simulacije Monte Carlo z Excelom

A Simulacija Monte Carla lahko razvijete s programom Microsoft Excel in igro na kocke. Simulacija Monte Carlo je matematična numerična metoda, ki uporablja naključne žrebe za izvajanje izračunov in zapletenih problemov. Danes se pogosto uporablja in igra ključno vlogo na različnih področjih, kot so finance, fizika, kemija itd ekonomije.

Simulacija Monte Carlo

Metodo Monte Carlo sta izumila John von Neumann in Stanislaw Ulam v štiridesetih letih prejšnjega stoletja in skuša rešiti zapletene probleme z uporabo naključnih in verjetnostnih metod. Izraz Monte Carlo se nanaša na upravno območje Monaka, ki je splošno znano kot kraj, kjer evropske elite igrajo na srečo.

Simulacijska metoda Monte Carlo izračuna verjetnosti za integrale in reši parcialne diferencialne enačbe ter tako v verjetnostno odločitev uvede statistični pristop k tveganju. Čeprav obstaja veliko naprednih statističnih orodij za ustvarjanje simulacij v Monte Carlu, je to lažje simulirati normalni zakon in enotni zakon z Microsoft Excelom in zaobiti matematično podlage.

Kdaj uporabiti simulacijo Monte Carlo

Metodo Monte Carlo uporabljamo, kadar je problem preveč zapleten in ga je težko narediti z neposrednim izračunom. Uporaba simulacije lahko pomaga rešiti situacije, ki se izkažejo za negotove. Veliko število ponovitev omogoča simulacijo normalna porazdelitev. Uporablja se lahko tudi za razumevanje delovanja tveganja in za razumevanje negotovosti pri modelih napovedovanja.

Kot je navedeno zgoraj, se simulacija pogosto uporablja v številnih različnih disciplinah, vključno s financami, znanostjo, inženiringom in upravljanje dobavne verige- zlasti v primerih, ko je v igri preveč naključnih spremenljivk. Analitiki lahko na primer uporabijo simulacije Monte Carlo za oceno izvedenih finančnih instrumentov, vključno z možnostmi, ali za določitev tveganja vključno z verjetnostjo, da bo podjetje neplačalo svoje dolgove.

Igra kocke

Za simulacijo v Monte Carlu izoliramo številne ključne spremenljivke, ki nadzorujejo in opišejo rezultat poskusa, nato dodelimo porazdelitev verjetnosti po velikem številu naključnih vzorcev. Za demonstracijo vzemimo za model igro kock. Evo, kako se igra s kockami:

• Igralec trikrat vrže tri kocke s šestimi stranicami.

• Če je skupaj tri mete sedem ali 11, zmaga igralec.

• Če je skupaj tri mete: tri, štiri, pet, 16, 17 ali 18, igralec izgubi.

• Če je vsota kakšen drug rezultat, igralec znova igra in ponovno vrže kocke.

• Ko igralec znova vrže kocke, se igra nadaljuje na enak način, le da igralec zmaga, ko je vsota enaka vsoti, določeni v prvem krogu.

Za ustvarjanje rezultatov je priporočljivo uporabiti tudi podatkovno tabelo. Poleg tega je za pripravo simulacije v Monte Carlu potrebno 5.000 rezultatov.

1. korak: Dogodki v kocki

Najprej razvijemo vrsto podatkov z rezultati vsake od treh kock za 50 udarcev. V ta namen je predlagana uporaba funkcije "RANDBETWEEN (1,6)". Tako vsakič, ko kliknemo F9, ustvarimo nov niz rezultatov. Celica "Izid" je vsota rezultatov treh zvitkov.

2. korak: Razpon rezultatov

Nato moramo razviti vrsto podatkov za opredelitev možnih rezultatov prvega kroga in naslednjih krogov. Obstaja obseg podatkov s tremi stolpci. V prvem stolpcu imamo številke od 1 do 18. Te številke predstavljajo možne rezultate po trikratnem bacanju kocke: največ 3 x 6 = 18. Opazili boste, da sta za celice ena in dve ugotovitve N/A, saj je nemogoče dobiti enega ali dva s tremi kockami. Najmanj tri.

V drugem stolpcu so vključeni možni zaključki po prvem krogu. Kot je navedeno v začetni izjavi, bodisi igralec zmaga (zmaga) ali izgubi (izgubi) ali pa ponovno igra (ponovni udarec), odvisno od rezultata (skupaj tri kocke).

V tretjem stolpcu so zabeleženi možni zaključki naslednjih krogov. Te rezultate lahko dosežemo s funkcijo "IF". To zagotavlja, da če je rezultat enakovreden rezultatu, dobljenemu v prvem krogu, smo win, sicer sledimo začetnim pravilom izvirne igre, da ugotovimo, ali bomo znova kocke.

3. korak: Zaključki

V tem koraku opredelimo izid 50 kock. Prvi zaključek je mogoče doseči z indeksno funkcijo. Ta funkcija išče možne rezultate prvega kroga, zaključek pa ustreza dobljenemu rezultatu. Na primer, ko zavrtimo šestko, spet igramo.

Z uporabo "OR" funkcije in indeksne funkcije, vložene v funkcijo "IF", lahko dobite ugotovitve drugih zvitkov kock. Ta funkcija Excelu sporoči: "Če je bil prejšnji rezultat zmaga ali izguba", prenehajte metati kocke, ker ko smo zmagali ali izgubili, smo končali. V nasprotnem primeru gremo na stolpec naslednjih možnih zaključkov in ugotovimo zaključek rezultata.

4. korak: Število zvitkov kock

Zdaj določimo število potrebnih kock pred izgubo ali zmago. Če želite to narediti, lahko uporabimo funkcijo "COUNTIF", ki zahteva Excel šteti rezultate "Re-roll" in mu dodati številko ena. Dodaja enega, ker imamo en dodaten krog in dobimo končni rezultat (zmaga ali poraz).

5. korak: Simulacija

Razvijamo paleto za sledenje rezultatom različnih simulacij. Če želite to narediti, bomo ustvarili tri stolpce. V prvem stolpcu je ena od vključenih številk 5000. V drugem stolpcu bomo po 50 udarcih kock iskali rezultat. V tretjem stolpcu, naslovu stolpca, bomo pred pridobitvijo končnega statusa (zmagali ali izgubili) poiskali število udarcev kock.

Nato bomo ustvarili analiza občutljivosti tabelo z uporabo podatkov o funkciji ali tabelo podatkov tabele (ta občutljivost bo vstavljena v drugo tabelo in tretji stolpec). Pri tej analizi občutljivosti je treba v celico A1 datoteke vnesti število dogodkov od enega do 5000. Pravzaprav bi lahko izbrali katero koli prazno celico. Zamisel je preprosto vsakič znova prisiliti v ponovni izračun in tako pridobiti nove kocke (rezultate novih simulacij), ne da bi pri tem poškodovali formule.

6. korak: Verjetnost

Končno lahko izračunamo verjetnost zmage in poraza. To naredimo s funkcijo "COUNTIF". Formula šteje število "zmag" in "porazov", nato pa jih deli s skupnim številom dogodkov 5.000, da dobimo ustrezen delež enega in drugega. Končno vidimo, da je verjetnost, da dobimo rezultat zmage, 73,2%, rezultat izgube pa 26,8%.