Skapa en Monte Carlo-simulering med Excel

En Monte Carlo-simulering kan utvecklas med Microsoft Excel och ett tärningsspel. Monte Carlo-simuleringen är en matematisk numerisk metod som använder slumpmässiga drag för att utföra beräkningar och komplexa problem. Idag används den mycket och spelar en viktig roll inom olika områden som finans, fysik, kemi och ekonomi.

Monte Carlo-simulering

Monte Carlo-metoden uppfanns av Nicolas Metropolis 1947 och syftar till att lösa komplexa problem med slumpmässiga och sannolikhetsmetoder. Termen "Monte Carlo" härstammar från det administrativa området Monaco, populärt känt som en plats där europeiska eliter spelar. Vi använder Monte Carlo-metoden när problemet är för komplicerat och svårt att göra genom direkt beräkning. Ett stort antal iterationer tillåter en simulering av normalfördelningen.

Monte Carlo-simuleringsmetoden beräknar sannolikheterna för integraler och löser partiella differentiella ekvationer och introducerar därmed en statistisk strategi för risk i ett probabilistiskt beslut. Även om det finns många avancerade statistiska verktyg för att skapa Monte Carlo-simuleringar är det lättare att simulera den normala lagen och den enhetliga lagen med Microsoft Excel och kringgå de matematiska underlag.

För Monte Carlo-simuleringen isolerar vi ett antal nyckelvariabler som styr och beskriver resultatet av experimentet och tilldelar sedan en sannolikhetsfördelning efter att ett stort antal slumpmässiga prover har utförts. Låt oss ta ett tärningsspel som modell.

Game of Dice

Så här rullar tärningspelet:

• Spelaren kastar tre tärningar som har 6 sidor 3 gånger.

• Om summan av de tre kasten är 7 eller 11 vinner spelaren.

• Om summan av de tre kasten är: 3, 4, 5, 16, 17 eller 18 förlorar spelaren.

• Om det totala resultatet är något annat spelar spelaren igen och rullar tärningarna igen.

• När spelaren kastar tärningarna igen, fortsätter spelet på samma sätt, förutom att spelaren vinner när summan är lika med summan som bestämts i första omgången.

Det rekommenderas också att använda en datatabell för att generera resultaten. Dessutom krävs 5 000 resultat för att förbereda Monte Carlo-simuleringen.

Steg 1: Tärningsrullningshändelser

Först utvecklar vi en mängd data med resultaten från var och en av de tre tärningarna för 50 rullar. För att göra detta föreslås det att använda funktionen "RANDBETWEEN (1, 6)". Således genererar vi en ny uppsättning rullresultat varje gång vi klickar på F9. "Utfall" -cellen är summan av resultaten från de tre rullarna.

Steg 2: Resultat av resultat

Sedan måste vi utveckla en rad data för att identifiera de möjliga resultaten för den första omgången och efterföljande omgångar. Det finns ett 3-kolumns dataområde. I den första kolumnen har vi siffrorna 1 till 18. Dessa siffror representerar de möjliga utfallen efter rullning av tärningarna 3 gånger: det maximala är 3 * 6 = 18. Du kommer att notera att för cellerna 1 och 2 är resultaten n / a eftersom det är omöjligt att få en 1 eller en 2 med tre tärningar. Minsta är 3.

I den andra kolumnen inkluderas de möjliga slutsatserna efter den första omgången. Som anges i det första uttalandet vinner antingen spelaren (Win) eller förlorar (Förlorar), eller de spelar upp (Re-roll), beroende på resultatet (totalt 3 tärningsrullar).

I den tredje kolumnen registreras de möjliga slutsatserna för efterföljande omgångar. Vi kan uppnå dessa resultat med funktionen "IF". Detta säkerställer att om det erhållna resultatet motsvarar resultatet som uppnåtts i första omgången, vinner vi, annars följer vi de ursprungliga reglerna för det ursprungliga spelet för att avgöra om vi rullar tärningarna igen.

Steg 3: Slutsatser

I detta steg identifierar vi resultatet av de 50 tärningsrullarna. Den första slutsatsen kan erhållas med en indexfunktion. Denna funktion söker efter möjliga resultat från den första omgången, den slutsats motsvarar det erhållna resultatet. Till exempel när vi får 6 spelar vi igen.

Man kan få resultat från andra tärningsrullar med hjälp av en "ELLER" -funktion och en indexfunktion som är kapslad i en "IF" -funktion. Den här funktionen säger till Excel, "Om föregående resultat är Win eller Lose", sluta rulla tärningarna eftersom när vi har vunnit eller förlorat är vi klara. Annars går vi till kolumnen med följande möjliga slutsatser och identifierar resultatet av resultatet.

Steg 4: Antal tärningar

Nu bestämmer vi antalet tärningar som krävs innan du tappar eller vinner. För att göra detta kan vi använda en "COUNTIF" -funktion, som kräver att Excel räknar resultaten av "Re-roll" och lägger till nummer 1 till den. Det lägger till en eftersom vi har en extra omgång, och vi får ett slutresultat (vinna eller förlora).

Steg 5: Simulering

Vi utvecklar ett sortiment för att spåra resultaten från olika simuleringar. För att göra detta skapar vi tre kolumner. I den första kolumnen är en av siffrorna som ingår 5 000. I den andra kolumnen letar vi efter resultatet efter 50 tärningar. I den tredje kolumnen, kolumnens titel, letar vi efter antalet tärningsrullar innan vi får den slutliga statusen (vinna eller förlora).

Sedan skapar vi en känslighetsanalysstabell med funktionsdata eller tabelldatatabell (denna känslighet kommer att infogas i den andra tabellen och den tredje kolumnen). I denna känslighetsanalys måste antalet händelser på 1 - 5 000 infogas i cellen A1 i filen. I själva verket kan man välja valfri tom cell. Tanken är helt enkelt att tvinga en omberäkning varje gång och därmed få nya tärningar (resultat av nya simuleringar) utan att skada formlerna på plats.

Steg 6: Sannolikhet

Vi kan äntligen beräkna sannolikheterna för att vinna och förlora. Vi gör detta med funktionen "COUNTIF". Formeln räknar antalet "vinna" och "förlora" dividerar sedan med det totala antalet händelser, 5 000, för att få respektive andel av det ena och det andra. Vi ser äntligen att sannolikheten för att få ett Win-resultat är 73, 2% och att få ett förlustresultat därför är 26, 8%.