Oprettelse af en Monte Carlo-simulering ved hjælp af Excel

En Monte Carlo-simulering kan udvikles ved hjælp af Microsoft Excel og et terningespil. Monte Carlo-simuleringen er en matematisk numerisk metode, der bruger tilfældige træk til at udføre beregninger og komplekse problemer. I dag er det meget udbredt og spiller en vigtig rolle inden for forskellige områder såsom finans, fysik, kemi og økonomi.

Monte Carlo-simulering

Monte Carlo-metoden blev opfundet af Nicolas Metropolis i 1947 og søger at løse komplekse problemer ved hjælp af tilfældige og sandsynlige metoder. Udtrykket "Monte Carlo" stammer fra det administrative område i Monaco, populært kendt som et sted, hvor europæiske eliter gamble. Vi bruger Monte Carlo-metoden, når problemet er for komplekst og vanskeligt at gøre ved direkte beregning. Et stort antal iterationer tillader en simulering af den normale distribution.

Monte Carlo-simuleringsmetoden beregner sandsynlighederne for integraler og løser partielle differentialligninger og introducerer derved en statistisk tilgang til risiko i en probabilistisk beslutning. Selvom der findes mange avancerede statistiske værktøjer til at oprette Monte Carlo-simuleringer, er det lettere at simulere den normale lov og den ensartede lov ved hjælp af Microsoft Excel og omgå de matematiske undergrunde.

For Monte Carlo-simuleringen isolerer vi et antal nøglevariabler, der kontrollerer og beskriver resultatet af eksperimentet, og tildeler derefter en sandsynlighedsfordeling, efter at der er udført et stort antal tilfældige prøver. Lad os tage et spil terninger som model.

Game of Dice

Sådan ruller terningens spil:

• Spilleren kaster tre terninger, der har 6 sider 3 gange.

• Hvis det samlede antal af 3 kast er 7 eller 11, vinder spilleren.

• Hvis det samlede antal af de 3 kast er: 3, 4, 5, 16, 17 eller 18, taber spilleren.

• Hvis det samlede antal er et andet resultat, spiller spilleren igen og ruller terningerne igen.

• Når spilleren kaster terningerne igen, fortsætter spillet på samme måde, bortset fra at spilleren vinder, når det samlede beløb er lig med summen, der er bestemt i første runde.

Det anbefales også at bruge en datatabel til at generere resultaterne. Derudover er der behov for 5.000 resultater for at forberede Monte Carlo-simuleringen.

Trin 1: Terning rullende begivenheder

Først udvikler vi en række data med resultaterne af hver af de 3 terninger for 50 ruller. For at gøre dette foreslås det at bruge funktionen "RANDBETWEEN (1, 6)". Hver gang vi klikker på F9 genererer vi således et nyt sæt rulleresultater. "Outcome" -cellen er summen af ​​resultaterne fra de 3 ruller.

Trin 2: række af resultater

Derefter skal vi udvikle en række data for at identificere de mulige resultater for den første runde og de efterfølgende runder. Der er et 3-søjles dataområde. I den første kolonne har vi numrene 1 til 18. Disse tal repræsenterer de mulige udfald efter at have kastet terningerne 3 gange: det maksimale er 3 * 6 = 18. Du vil bemærke, at for celler 1 og 2 er fundene ikke relevant, da det er umuligt at få en 1 eller en 2 ved hjælp af 3 terninger. Minimum er 3.

I den anden kolonne er de mulige konklusioner efter den første runde inkluderet. Som det fremgår af den indledende erklæring, vinder enten spilleren (Win) eller taber (Tab), eller de afspiller (Re-roll), afhængigt af resultatet (det samlede antal 3 terningeruller).

I den tredje kolonne registreres de mulige konklusioner for de efterfølgende runder. Vi kan nå disse resultater ved hjælp af funktionen "IF". Dette sikrer, at hvis det opnåede resultat svarer til resultatet opnået i første runde, vinder vi, ellers følger vi de oprindelige regler i det originale spil for at bestemme, om vi ruller terningerne igen.

Trin 3: Konklusioner

I dette trin identificerer vi resultatet af de 50 terningeruller. Den første konklusion kan opnås med en indeksfunktion. Denne funktion søger efter de mulige resultater fra den første runde, hvor konklusionen svarer til det opnåede resultat. For eksempel når vi får 6 spiller vi igen.

Man kan få resultaterne af andre terningeruller ved hjælp af en "ELLER" -funktion og en indeksfunktion indlejret i en "IF" -funktion. Denne funktion fortæller Excel, "Hvis det forrige resultat er Win eller Tab", skal du stoppe med at rulle terningerne, fordi når vi først har vundet eller tabt, er vi færdige. Ellers går vi til kolonnen med følgende mulige konklusioner, og vi identificerer konklusionen af ​​resultatet.

Trin 4: Antal terningeruller

Nu bestemmer vi antallet af terningeruller, der kræves, før du taber eller vinder. For at gøre dette kan vi bruge en "COUNTIF" -funktion, som kræver Excel for at tælle resultaterne af "Re-roll" og tilføje nummeret 1 til det. Det tilføjer en, fordi vi har en ekstra runde, og vi får et slutresultat (sejr eller tab).

Trin 5: Simulering

Vi udvikler en række til at spore resultaterne af forskellige simuleringer. For at gøre dette opretter vi tre kolonner. I den første kolonne er et af de inkluderede tal 5.000. I den anden kolonne ser vi efter resultatet efter 50 terninger. I den tredje kolonne, titlen på kolonnen, ser vi efter antallet af terningeruller, inden vi får den endelige status (vind eller tab).

Derefter opretter vi en følsomhedsanalysetabel ved hjælp af funktionsdataene eller Tabeldatatabellen (denne følsomhed indsættes i den anden tabel og tredje kolonne). I denne følsomhedsanalyse skal antallet af begivenheder på 1 - 5.000 indsættes i filens celle A1. Faktisk kunne man vælge enhver tom celle. Ideen er simpelthen at tvinge en genberegning hver gang og således få nye terningruller (resultater af nye simuleringer) uden at beskadige formlerne på plads.

Trin 6: Sandsynlighed

Vi kan endelig beregne sandsynligheden for at vinde og tabe. Vi gør dette ved hjælp af funktionen "COUNTIF". Formlen tæller antallet af "vinde" og "tab" divideres derefter med det samlede antal begivenheder, 5.000, for at opnå den respektive andel af det ene og det andet. Vi ser endelig, at sandsynligheden for at få et Win-resultat er 73, 2%, og at få et tabsresultat derfor er 26, 8%.