Opprette en Monte Carlo-simulering ved hjelp av Excel

En Monte Carlo-simulering kan utvikles ved hjelp av Microsoft Excel og et terningspill. Monte Carlo-simuleringen er en matematisk numerisk metode som bruker tilfeldige trekkplaster for å utføre beregninger og komplekse problemer. I dag er det mye brukt og spiller en nøkkelrolle på forskjellige felt som økonomi, fysikk, kjemi og økonomi.

Monte Carlo-simulering

Monte Carlo-metoden ble oppfunnet av Nicolas Metropolis i 1947 og søker å løse komplekse problemer ved bruk av tilfeldige og sannsynlige metoder. Begrepet "Monte Carlo" stammer fra det administrative området Monaco, populært kjent som et sted der europeiske eliter gamble. Vi bruker Monte Carlo-metoden når problemet er for komplekst og vanskelig å gjøre med direkte beregning. Et stort antall iterasjoner tillater en simulering av normalfordelingen.

Simuleringsmetoden Monte Carlo beregner sannsynlighetene for integraler og løser partielle differensialligninger, og introduserer dermed en statistisk tilnærming til risiko i en sannsynlig beslutning. Selv om det finnes mange avanserte statistiske verktøy for å lage Monte Carlo-simuleringer, er det lettere å simulere den normale loven og den enhetlige loven ved å bruke Microsoft Excel og omgå de matematiske underlagene.

For Monte Carlo-simuleringen isolerer vi et antall nøkkelvariabler som kontrollerer og beskriver utfallet av eksperimentet, og tilordner deretter en sannsynlighetsfordeling etter at et stort antall tilfeldige prøver er utført. La oss ta et spill av terninger som modell.

Game of Dice

Slik ruller terningspillet:

• Spilleren kaster tre terninger som har 6 sider 3 ganger.

• Hvis totalen av de 3 kastene er 7 eller 11, vinner spilleren.

• Hvis totalen av de 3 kastene er: 3, 4, 5, 16, 17 eller 18, taper spilleren.

• Hvis totalen er noe annet resultat, spiller spilleren igjen og ruller terningen igjen.

• Når spilleren kaster terningen igjen, fortsetter spillet på samme måte, bortsett fra at spilleren vinner når totalen er lik summen som ble bestemt i første runde.

Det anbefales også å bruke en datatabell for å generere resultatene. Videre er det nødvendig med 5000 resultater for å forberede Monte Carlo-simuleringen.

Trinn 1: Terningens rullende hendelser

Først utvikler vi en rekke data med resultatene fra hver av de 3 terningene for 50 ruller. For å gjøre dette foreslås det å bruke funksjonen "RANDBETWEEN (1, 6)". Dermed genererer vi et nytt sett med rulleresultater hver gang vi klikker på F9. "Utfall" -cellen er summen av resultatene fra de tre rullene.

Trinn 2: Utvalg av resultater

Deretter må vi utvikle et utvalg av data for å identifisere mulige utfall for første runde og påfølgende runder. Det er et 3-kolonnes dataområde. I den første kolonnen har vi tallene 1 til 18. Disse tallene representerer de mulige resultatene etter å ha rullet terningen 3 ganger: det maksimale er 3 * 6 = 18. Du vil merke deg at for celler 1 og 2, er funnene ikke tilgjengelige siden det er umulig å få en 1 eller en 2 ved å bruke 3 terninger. Minimum er 3.

I den andre kolonnen er de mulige konklusjonene etter første runde inkludert. Som det fremgår av den første uttalelsen, vinner enten spilleren (Vinn) eller taper (Mister), eller de spiller på nytt (Re-roll), avhengig av resultatet (totalt 3 terningeruller).

I den tredje kolonnen blir de mulige konklusjonene til påfølgende runder registrert. Vi kan oppnå disse resultatene ved å bruke "IF" -funksjonen. Dette sikrer at hvis det oppnådde resultatet tilsvarer resultatet oppnådd i første runde, vinner vi, ellers følger vi de opprinnelige reglene for det opprinnelige spillet for å avgjøre om vi kaster terningen på nytt.

Trinn 3: Konklusjoner

I dette trinnet identifiserer vi utfallet av de 50 terningkastene. Den første konklusjonen kan oppnås med en indeksfunksjon. Denne funksjonen søker i de mulige resultatene fra første runde, hvor konklusjonen tilsvarer det oppnådde resultatet. For eksempel når vi får 6 spiller vi igjen.

Man kan få funnene fra andre terningkast, ved å bruke en "ELLER" -funksjon og en indeksfunksjon som er nestet i en "IF" -funksjon. Denne funksjonen forteller Excel, "Hvis forrige resultat er Vinn eller tap, " slutte å rulle terningene fordi når vi først har vunnet eller tapt, er vi ferdige. Ellers går vi til kolonnen med følgende mulige konklusjoner og identifiserer konklusjonen av resultatet.

Trinn 4: Antall terningkast

Nå bestemmer vi antall terningkast som kreves før du taper eller vinner. For å gjøre dette, kan vi bruke en "COUNTIF" -funksjon, som krever at Excel teller resultatene av "Re-roll" og legger nummer 1 til den. Det legger en fordi vi har en ekstra runde, og vi får et endelig resultat (seier eller taper).

Trinn 5: Simulering

Vi utvikler et utvalg for å spore resultatene fra forskjellige simuleringer. For å gjøre dette vil vi opprette tre kolonner. I den første kolonnen er et av tallene som er inkludert 5.000. I den andre kolonnen skal vi se etter resultatet etter 50 terninger. I den tredje kolonnen, tittelen på kolonnen, vil vi se etter antall terningkast før vi får den endelige statusen (vinn eller tap).

Deretter oppretter vi en følsomhetsanalysetabell ved å bruke funksjonsdataene eller tabelldatatabellen (denne følsomheten vil bli satt inn i den andre tabellen og den tredje kolonnen). I denne følsomhetsanalysen må antall hendelser på 1 - 5 000 settes inn i cellen A1 i filen. Faktisk kunne man velge hvilken som helst tom celle. Tanken er ganske enkelt å tvinge til en ny beregning hver gang og dermed få nye terningkast (resultater av nye simuleringer) uten å skade formlene på plass.

Trinn 6: Sannsynlighet

Vi kan endelig beregne sannsynlighetene for å vinne og tape. Vi gjør dette ved å bruke "COUNTIF" -funksjonen. Formelen teller antall "vinn" og "taper" og deler deretter med det totale antall hendelser, 5 000, for å oppnå den respektive andelen av det ene og det andre. Vi ser endelig at sannsynligheten for å få et Win-utfall er 73, 2% og at det er 26, 8%.