Wat is Monte Carlo simulatie?

Monte Carlo-simulatie is een proces waarbij waarschijnlijkheidscurven worden gebruikt om de waarschijnlijkheid van een uitkomst te bepalen. Je mag hier je hoofd krabben en zeggen… “Hey Rick, een distributiecurve heeft een reeks waarden. Hoe bepaal ik de waarschijnlijkheid van een uitkomst?”En nog beter, hoe doe ik dat in Microsoft Excel zonder speciale add-inshought je nooit zou vragen.Dit wordt gedaan door de simulatie duizenden keren uit te voeren en de verdeling van de output te analyseren. Dit is vooral belangrijk wanneer u het analyseren van de output van verschillende distributie curves die voeden in elkaar.Voorbeeld:

• # van verkochte eenheden kan een distributiecurve
• vermenigvuldigd met de marktprijs hebben, die een andere distributiecurve
• minus variabele lonen die een andere curve
• enz.hebben., etc.

wanneer al deze distributies vermengd zijn, kan de output vrij complex zijn. Het uitvoeren van duizenden iteraties (of simulaties) van deze curve kan je wat inzichten geven. Dit is vooral nuttig bij het analyseren van potentiële risico ‘ s voor een beslissing.Bij het beschrijven van Monte Carlo simulatie, verwijs ik vaak naar de jaren 1980 film War Games, waar een jonge Mathew Broderick (voor Ferris Bueller) een hacker is die zijn inbelmodem gebruikt om de Pentagon computers te hacken en de derde wereldoorlog te beginnen. Soort. Hij liet de computers van het Pentagon vele simulaties doen van de spellen Tic Tac Toe om de computer te leren dat niemand een nucleaire oorlog zal voeren – en de wereld in het proces zal redden.Bedankt Ferris. Je bent een held.Hier is een glimp van de film te laten zien big time Monte Carlo in actie. Ik neem aan dat je de politiek over het hoofd ziet, de ongemakkelijke man die knuffelt en natuurlijk Dabney Coleman.

Distributiecurves

er zijn verschillende distributiecurves die u kunt gebruiken om uw Monte Carlo-simulatie in te stellen. En deze krommen kunnen worden verwisseld op basis van de variabele. Microsoft heeft geen formule genaamd “Do Monte Carlo Simulation” in de menubalk 🙂

uniforme distributie

in een uniforme distributie is er een gelijke kans tussen het minimum en het maximum. Een uniforme verdeling lijkt op een rechthoek.

normale (Gaussiaanse) verdeling

dit is ook uw standaard klokvormige kromme. Deze Monte Carlo Simulatieformule wordt gekenmerkt door een gelijkmatige verdeling aan elke kant (mediaan en gemiddelde is hetzelfde – en geen scheefheid). De staarten van de curve gaan tot in het oneindige. Dus dit kan niet de ideale curve voor huizenprijzen, waar een paar top end huizen verhogen het gemiddelde (gemiddelde) ruim boven de mediaan, of in gevallen waar er een harde minimum of maximum. Een voorbeeld hiervan kan het minimumloon in uw lokale. Houd er rekening mee dat de naam van de functie afhankelijk is van uw versie.

lognormale verdeling

een verdeling waarbij de logaritme normaal wordt verdeeld met het gemiddelde en de standaardafwijking. De setup is dus vergelijkbaar met de normale distributie, maar merk op dat de gemiddelde en standaard_dev variabelen bedoeld zijn om de logaritme te representeren.

Poisson distributie

dit is waarschijnlijk de meest onderbenutte distributie. Standaard gebruiken veel mensen een normale distributiecurve wanneer Poisson beter past bij hun modellen. Poisson is het best te beschrijven als er een grote verspreiding is in de buurt van het begin die snel verdwijnt naar een lange staart aan de ene kant. Een voorbeeld hiervan is een callcenter, waar geen oproepen worden beantwoord Voor tweede nul. Gevolgd door de meerderheid van de gesprekken beantwoord in de eerste 2 intervallen (zeggen 30 en 60 seconden) met een snelle drop off in volume en een lange staart, met zeer weinig gesprekken beantwoord in 20 minuten (naar verluidt).

het doel hier is niet om u elke mogelijke distributie in Excel te tonen, omdat dit buiten het toepassingsgebied van dit artikel valt. Eerder om ervoor te zorgen dat u weet dat er veel opties beschikbaar zijn voor uw Monte Carlo simulatie. Val niet in de val van de veronderstelling dat een normale distributie curve is de juiste pasvorm voor al uw Gegevens modellering. Als u meer curves wilt vinden, gaat u naar de statistische functies in uw Excel-werkmap en onderzoekt u deze. Als u vragen hebt, stel ze in de commentaren hieronder.

het bouwen van het Model

voor deze set-up zullen we uitgaan van een normale distributie en 1000 iteraties.

invoervariabelen

de setup gaat uit van een normale verdeling. Een normale verdeling vereist drie variabelen: waarschijnlijkheid, gemiddelde en standaardafwijking. We zullen de gemiddelde en standaardafwijking in onze eerste stap aanpakken. Ik ga uit van een financiële prognose probleem dat bestaat uit inkomsten, variabele en vaste kosten. Waarbij inkomsten minus variabele kosten minus vaste kosten gelijk zijn aan winst. De vaste kosten zijn verzonken kosten in installaties en apparatuur, dus er wordt geen distributiecurve aangenomen. Er wordt uitgegaan van distributiecurven voor inkomsten en variabele kosten.

eerste simulatie

het voorbeeld hieronder geeft de instellingen voor inkomsten aan. De formule kan worden gekopieerd en geplakt naar cel D6 voor variabele uitgaven. Voor inkomsten en uitgaven wij u de functie NORM.Inv () waarbij de parameters zijn:

• kans = de functie RAND () om een willekeurig getal uit te lokken op basis van de andere criteria binnen de verdeling.
• gemiddelde = Het gemiddelde dat in Stap 1 wordt gebruikt. Voor de inkomsten is het C3.
• standaardafwijking = de standaardafwijking die in Stap 1 wordt gebruikt. Voor de ontvangsten is het C4

omdat RAND () als waarschijnlijkheid wordt gebruikt, wordt een willekeurige waarschijnlijkheid gegenereerd bij verversen. We zullen dit in ons voordeel gebruiken in de volgende stap.

1.000 simulaties

er zijn verschillende manieren om 1.000 of meer variaties uit te voeren. De eenvoudigste optie is om de formule uit stap #2 en maken het absoluut. Kopieer en plak dan 1.000 keer. Dat is simpel, maar niet erg chique. En als Ferris Bueller de wereld kan redden door een nieuw Tic Tac Toe spel aan een computer te tonen, dan kunnen we deze analyse ook wat spannender maken. Laten we ons wagen in de wereld van de tafels.

• eerst willen we een omtrek voor een tabel maken. We doen dit door de nummers 1 tot 1.000 in rijen op te sommen. In de voorbeeldafbeelding hieronder begint de nummerlijst in B12.
• in de volgende kolom, in cel C12, verwijzen we naar de eerste iteratie.

• markeer vervolgens het gebied waar we de 1.000 iteraties
• willen huisvesten Selecteer Data > gegevenstabellen
• voor kolom invoercel: selecteer een lege cel. In het downloadbestand is cel D11 geselecteerd
• selecteer OK

• zodra OK is geselecteerd uit de vorige stap, wordt een tabel ingevoegd die de 1.000 simulaties automatisch uitvoert

samenvattende statistieken

zodra de simulaties zijn uitgevoerd, is het tijd om samenvattende statistieken te verzamelen. Dit kan op een aantal manieren worden gedaan. In dit voorbeeld heb ik de functie COUNTIF() gebruikt om het percentage simulaties te bepalen dat niet rendabel is, en de waarschijnlijkheid van een winst groter dan $1 miljoen. Zoals verwacht, de kans op meer dan $1M zweeft rond 50%. Dit komt omdat we normale distributiecurves gebruikten die gelijkmatig verdeeld zijn rond het gemiddelde, dat $1M was. de kans op geldverlies is 4,8%. Dit werd verzameld door de functie Aantal.als() te gebruiken om de simulaties te tellen die kleiner waren dan nul, en te delen door de 1000 totale iteraties.

Download

in de video hierboven vraagt Oz naar de verschillende toepassingen voor Monte Carlo simulatie. Waar heb je het voor gebruikt? Zijn er specifieke voorbeelden die u met de groep kunt delen? Zo ja, laat een notitie hieronder in de opmerkingen sectie. Voel je ook vrij om je aan te melden voor onze nieuwsbrief, zodat je als nieuw op de hoogte kunt blijven Excel.TV shows worden aangekondigd. Laat een bericht achter om contact te houden.