Een Monte Carlo-simulatie maken met Excel

Een Monte Carlo-simulatie kan worden ontwikkeld met behulp van Microsoft Excel en een dobbelspel. De Monte Carlo-simulatie is een wiskundige numerieke methode die willekeurige trekkingen gebruikt om berekeningen en complexe problemen uit te voeren. Tegenwoordig wordt het veel gebruikt en speelt het een belangrijke rol op verschillende gebieden, zoals financiën, natuurkunde, scheikunde en economie.

Monte Carlo simulatie

De Monte Carlo-methode is uitgevonden door Nicolas Metropolis in 1947 en beoogt complexe problemen op te lossen met behulp van willekeurige en probabilistische methoden. De term "Monte Carlo" is afkomstig uit het administratieve gebied van Monaco, in de volksmond bekend als een plaats waar Europese elites gokken. We gebruiken de Monte Carlo-methode wanneer het probleem te complex en moeilijk te doen is door directe berekening. Een groot aantal iteraties maakt een simulatie van de normale verdeling mogelijk.

De Monte Carlo-simulatiemethode berekent de waarschijnlijkheden voor integralen en lost partiële differentiaalvergelijkingen op, waardoor een statistische benadering van risico wordt geïntroduceerd in een probabilistische beslissing. Hoewel er veel geavanceerde statistische hulpmiddelen bestaan ​​om Monte Carlo-simulaties te maken, is het eenvoudiger om de normale wet en de uniforme wet te simuleren met behulp van Microsoft Excel en de wiskundige onderbouwing te omzeilen.

Voor de Monte Carlo-simulatie isoleren we een aantal sleutelvariabelen die de uitkomst van het experiment regelen en beschrijven, en wijzen vervolgens een waarschijnlijkheidsverdeling toe nadat een groot aantal willekeurige monsters is uitgevoerd. Laten we een dobbelspel als model nemen.

Game of Dice

Dit is hoe het dobbelspel rolt:

• De speler gooit drie dobbelstenen met 3 zijden 3 keer.

• Als het totaal van de 3 worpen 7 of 11 is, wint de speler.

• Als het totaal van de 3 worpen is: 3, 4, 5, 16, 17 of 18, verliest de speler.

• Als het totaal een andere uitkomst is, speelt de speler opnieuw en gooit de dobbelstenen opnieuw.

• Wanneer de speler de dobbelstenen opnieuw gooit, gaat het spel op dezelfde manier verder, behalve dat de speler wint wanneer het totaal gelijk is aan de som bepaald in de eerste ronde.

Het wordt ook aanbevolen om een ​​gegevenstabel te gebruiken om de resultaten te genereren. Bovendien zijn 5.000 resultaten nodig om de Monte Carlo-simulatie voor te bereiden.

Stap 1: Dice Rolling Events

Eerst ontwikkelen we een reeks gegevens met de resultaten van elk van de 3 dobbelstenen voor 50 rollen. Om dit te doen, wordt voorgesteld om de functie "RANDBETWEEN (1, 6)" te gebruiken. Dus elke keer dat we op F9 klikken, genereren we een nieuwe set roll-resultaten. De cel "Resultaat" is de som van de resultaten van de 3 rollen.

Stap 2: Bereik van resultaten

Vervolgens moeten we een reeks gegevens ontwikkelen om de mogelijke resultaten voor de eerste ronde en de daaropvolgende rondes te identificeren. Er is een gegevensbereik met 3 kolommen. In de eerste kolom hebben we de cijfers 1 tot 18. Deze cijfers vertegenwoordigen de mogelijke uitkomsten na het 3 keer gooien van de dobbelstenen: het maximum is 3 * 6 = 18. U zult opmerken dat voor cellen 1 en 2 de bevindingen N / A zijn, omdat het onmogelijk is om een ​​1 of een 2 te krijgen met 3 dobbelstenen. Het minimum is 3.

In de tweede kolom zijn de mogelijke conclusies na de eerste ronde opgenomen. Zoals vermeld in de eerste verklaring, wint de speler (win) of verliest (verliest) of speelt hij opnieuw (re-roll), afhankelijk van het resultaat (het totaal van 3 dobbelstenen).

In de derde kolom worden de mogelijke conclusies voor volgende rondes geregistreerd. We kunnen deze resultaten bereiken met de functie "ALS". Dit zorgt ervoor dat als het behaalde resultaat gelijk is aan het behaalde resultaat in de eerste ronde, we winnen, anders volgen we de oorspronkelijke regels van het oorspronkelijke spel om te bepalen of we de dobbelstenen opnieuw gooien.

Stap 3: conclusies

In deze stap identificeren we de uitkomst van de 50 dobbelstenen. De eerste conclusie kan worden getrokken met een indexfunctie. Deze functie zoekt naar de mogelijke resultaten van de eerste ronde, waarbij de conclusie overeenkomt met het verkregen resultaat. Als we bijvoorbeeld 6 behalen, spelen we opnieuw.

Men kan de bevindingen van andere dobbelstenen krijgen met behulp van een "OF" -functie en een indexfunctie genest in een "ALS" -functie. Deze functie vertelt Excel: "Als het vorige resultaat Win of Lose is", stop met het gooien van de dobbelstenen, want als we eenmaal hebben gewonnen of verloren, zijn we klaar. Anders gaan we naar de kolom met de volgende mogelijke conclusies en identificeren we de conclusie van het resultaat.

Stap 4: Aantal dobbelstenen

Nu bepalen we het aantal vereiste dobbelstenen voordat we verliezen of winnen. Om dit te doen, kunnen we een "AANTAL" -functie gebruiken, die Excel vereist om de resultaten van "Opnieuw rollen" te tellen en er nummer 1 aan toe te voegen. Het voegt er een toe omdat we een extra ronde hebben en we een eindresultaat krijgen (winnen of verliezen).

Stap 5: Simulatie

We ontwikkelen een bereik om de resultaten van verschillende simulaties bij te houden. Hiervoor maken we drie kolommen. In de eerste kolom is een van de opgenomen cijfers 5.000. In de tweede kolom zoeken we het resultaat na 50 dobbelstenen. In de derde kolom, de titel van de kolom, zullen we zoeken naar het aantal dobbelstenen voordat we de uiteindelijke status verkrijgen (winnen of verliezen).

Vervolgens maken we een gevoeligheidsanalysetabel met behulp van de functiegegevens of de tabel Gegevenstabel (deze gevoeligheid wordt ingevoegd in de tweede tabel en derde kolommen). In deze gevoeligheidsanalyse moet het aantal gebeurtenissen van 1 - 5.000 worden ingevoegd in cel A1 van het bestand. Je zou eigenlijk elke lege cel kunnen kiezen. Het idee is gewoon om elke keer een herberekening te forceren en zo nieuwe dobbelstenen te krijgen (resultaten van nieuwe simulaties) zonder de formules te beschadigen.

Stap 6: Waarschijnlijkheid

We kunnen eindelijk de kansen op winnen en verliezen berekenen. We doen dit met behulp van de functie "AANTAL.ALS". De formule telt het aantal "winnen" en "verliezen" en deelt vervolgens door het totale aantal evenementen, 5.000, om het respectieve aandeel van de ene en de andere te verkrijgen. We zien eindelijk dat de kans op een Win-uitkomst 73, 2% is en dat een Lose-uitkomst dus 26, 8% is.