Montecarlo-simulatie in Excel: een complete gids

Waarom Excel gebruiken voor een Montecarlo-simulatie?

Als je hebt gekozen om een Montecarlo-simulatie te implementeren, heb je meerdere tools tot je beschikking, zoals Excel, Python, R, SAS en MATLAB, om je bij de simulaties te helpen.

De belangrijkste factor om te overwegen, vooral wanneer je voor het eerst een Montecarlo-simulatie implementeert, is je algemene vertrouwdheid met de tool. Excel is een van de meest gebruikte tools in het bedrijfsleven, wat betekent dat veel mensen al vertrouwd zijn met de basisfuncties. Dit verkort de trainingstijd en elimineert de noodzaak om nieuwe software vanaf nul te leren.

Excel biedt ook gebruiksvriendelijke tools voor het maken van grafieken en diagrammen, wat handig kan zijn om de resultaten van simulaties te visualiseren. Daarnaast zijn er verschillende krachtige add-ins beschikbaar voor Excel die de mogelijkheden uitbreiden om complexe Montecarlo-simulaties uit te voeren.

Het is echter ook goed om te bedenken dat voor geavanceerdere simulaties, vooral die waarbij grote datasets moeten worden verwerkt of zeer veel simulaties moeten worden gedraaid, meer gespecialiseerde tools dan Excel wellicht geschikter zijn.

Belangrijke Excel-functies voor Montecarlo

Vervolgens bekijken we twee essentiële Excel-functies: RAND() en NORM.INV(), met hun syntaxis, parameters en typische use-cases. Deze functies helpen bij het genereren van willekeurige getallen en het definiëren van kansverdelingen, fundamentele onderdelen van elke simulatie.

De functie RAND()

RAND() genereert een willekeurig getal groter dan of gelijk aan 0 en kleiner dan 1. De getallen zijn uniform verdeeld, wat betekent dat elk getal binnen het opgegeven bereik even waarschijnlijk is.

De syntaxis voor RAND() is als volgt:

RAND()

De functie RAND() vereist geen argumenten. Je gebruikt het simpelweg als RAND().

In de context van de Montecarlo-simulatie kan RAND() worden gebruikt om het optreden van willekeurige gebeurtenissen te simuleren of om de inputs in je model te variëren.

De functie NORM.INV()

Terwijl RAND() uniforme willekeurige getallen genereert, wordt NORM.INV() gebruikt om willekeurige getallen uit een normale verdeling te genereren, wat vaak nodig is in een Montecarlo-simulatie. Deze functie retourneert de inverse van de normale cumulatieve verdeling voor een opgegeven gemiddelde en standaarddeviatie.

De syntaxis voor de functie NORM.INV() is als volgt:

NORM.INV(probability, mean, standard_deviation)

De parameters zijn:

• probability: Een kans die overeenkomt met de normale verdeling, en een waarde tussen 0 en 1 moet zijn. Dit wordt doorgaans gegenereerd door de functie RAND().

• mean: Het rekenkundig gemiddelde van de normale verdeling.

• standard_deviation: De standaarddeviatie van de normale verdeling, een maat voor hoe sterk de getallen rond het gemiddelde zijn verspreid.

De functie NORM.INV() wordt gebruikt om uniform verdeelde willekeurige getallen van de functie RAND() om te zetten in getallen die een specifieke normale verdeling volgen. Dit is nuttig voor het modelleren van variabelen die naar verwachting natuurlijke variabiliteit vertonen volgens een normale curve.

Nu we alle bouwstenen, functies en concepten achter een Montecarlo-simulatie hebben, gaan we er een implementeren in Microsoft Excel.

Een Montecarlo-simulatie implementeren in Microsoft Excel: een voorbeeld

Stel je bent een data-analist bij een dynamisch consumentenelektronicabedrijf en je hebt de taak gekregen om de financiële haalbaarheid te beoordelen van de lancering van een nieuwe draagbare fitnesstracker.

De markt voor dergelijke apparaten is competitief en de consumentenvraag kan sterk variëren, beïnvloed door seizoensgebonden trends, de effectiviteit van marketing en acties van concurrenten. Daarnaast zijn de kosten voor de productie van deze apparaten onderhevig aan schommelingen door veranderingen in materiaalkosten en onzekerheden in de toeleveringsketen.

Je hebt besloten om een Montecarlo-simulatie in Excel te gebruiken om deze uitdagingen aan te pakken. Je gelooft dat deze aanpak helpt om de potentiële winstgevendheid onder verschillende scenario’s te schatten, zodat het bedrijf weloverwogen beslissingen kan nemen over prijsstrategieën, productievolumes en marketinginvesteringen.

Je hebt ook historische gegevens geanalyseerd van vergelijkbare productlanceringen en marktonderzoeken binnen de consumentenelektronica. Uit deze analyse heb je bepaalde kengetallen afgeleid die je simulatie zullen sturen:

• Een gemiddelde vraag van 10.000 stuks voor nieuwe apparaten binnen het eerste jaar na lancering, met een standaarddeviatie van 2.000 stuks, wat de onzekerheid in consumentenacceptatie weerspiegelt.
• De verkoopprijs per stuk varieert doorgaans tussen $50 en $70, afhankelijk van concurrerende prijzen en marktsaturatie.
• De kostprijs per stuk, beïnvloed door volatiele materiaalkosten en productie-efficiëntie, bedraagt gemiddeld $30 per stuk met een standaarddeviatie van $5.

Deze historische gegevens vormen de onderliggende aannames van je simulatieparameters en helpen de simulatie actueler aan te laten sluiten op de marktomstandigheden.

De stappen die je kunt volgen om de Montecarlo-simulatie voor dit specifieke voorbeeld te implementeren, zijn als volgt:

Stap 1: Richt je Excel-blad in

Bereid eerst je Excel-werkblad voor met kolommen voor elke variabele en een kolom voor de berekende winst.

Zo ziet het er aanvankelijk uit:

Het Excel-blad inrichten.

Stap 2: Voer formules in voor variabelen

In elke rij voer je formules in om willekeurige waarden te genereren voor vraag, verkoopprijs en kostprijs op basis van de door jou vastgestelde verdelingen:

• Vraag: Normale verdeling (gemiddelde = 10.000 stuks, standaarddeviatie = 2.000 stuks)
• Verkoopprijs: Uniforme verdeling ($50 tot $70)
• Kostprijs: Normale verdeling (gemiddelde = $30, standaarddeviatie = $5)

Om deze formules één voor één in te voeren, selecteer je cel A2 en typ je het volgende:

=NORM.INV(RAND(), 10000, 2000)

De bovenstaande vergelijking creëert een normale verdeling met een gegeven gemiddelde en standaarddeviatie als volgt:

De verdeling voor vraag creëren.

Selecteer vervolgens cel B2 en typ het volgende:

=50 + (70-50) * RAND()

De bovenstaande vergelijking creëert een uniforme verdeling tussen $50 en $70 voor de verkoopprijs als volgt:

De verdeling voor de verkoopprijs creëren.

Selecteer cel C2 en typ het volgende:

=NORM.INV(RAND(), 30, 5)

De bovenstaande vergelijking, vergelijkbaar met de vraagvergelijking, creëert een normale verdeling met een gegeven gemiddelde en standaarddeviatie als volgt:

De verdeling voor de kostprijs creëren.

Stap 3: Bereken de afhankelijke variabele

Bereken nu de winst, de afhankelijke variabele, voor elke simulatie met de formule in kolom D:

=(B2 - C2) * A2

De winst berekenen.

Stap 4: Vul omlaag om meerdere scenario’s te simuleren

Wat we tot nu toe hebben gedaan, is één enkele simulatie maken. Laten we dit uitbreiden naar meerdere, laten we zeggen duizend simulaties.

Selecteer de cellen A2 tot en met D2 en sleep de vulgreep (een klein vierkantje rechtsonder in de selectie) omlaag om de formules door te trekken over zoveel rijen als je wilt simuleren (bijv. 1000 rijen voor 1000 simulaties).

Het zal er ongeveer zo uitzien:

De simulaties maken.

Stap 5: Analyseer de resultaten

Na het draaien van de simulaties kun je de resultaten analyseren met statistische functies zoals minimum, maximum, gemiddelde en standaarddeviatie. Aarzel niet om snel de Excel-cheat sheet te raadplegen voor een opfrisser van de ingebouwde Excel-functies die we zo gebruiken.

Om de gemiddelde maandelijkse winst te vinden, typ je het volgende in een cel, bijvoorbeeld G6:

=AVERAGE(D2:D1001)

Om de minimale maandelijkse winst te vinden, typ je het volgende in een cel, bijvoorbeeld G7:

=MIN(D2:D1001)

Om de maximale maandelijkse winst te vinden, typ je het volgende in een cel, bijvoorbeeld G8:

=MAX(D2:D1001)

Om de standaarddeviatie van de winst te vinden, typ je het volgende in een cel, bijvoorbeeld G9:

=STDEV.P(D2:D1001)

Na uitvoering zou het Excel-blad er ongeveer zo uitzien:

De simulatieresultaten analyseren.

We kunnen de geschatte resultaten en de implicaties voor de productlancering als volgt interpreteren:

• De gemiddelde winst geeft de verwachte winst weer van de lancering van de nieuwe fitnesstracker. Dit suggereert dat, gemiddeld genomen, elke simulatieronde voorspelt dat we ongeveer $298.278,67 winst kunnen verwachten. Deze waarde is nuttig als centrale schatting van de winstgevendheid onder de gegeven aannames.
• Een minimale winst van $67.598,78 is de laagste winst die we in al onze simulaties hebben waargenomen. Dit geeft het worstcasescenario aan onder de aannames van je model, dat nog steeds winstgevend is maar beduidend lager dan het gemiddelde. Dit kan het gevolg zijn van bijzonder lage vraag of ongunstige kostcondities in die specifieke simulatie.
• Een maximale winst van $641.955,42 vertegenwoordigt het bestcasescenario, waarin vraag en prijs waarschijnlijk het hoogst waren en de kosten het laagst in alle simulaties. Dit toont het potentiële opwaartse scenario als de omstandigheden zeer gunstig uitpakken.

Gezien het grote verschil tussen de minimale en maximale winst en de aanzienlijke standaarddeviatie is er aanzienlijk financieel risico verbonden aan de lancering van het nieuwe product.

Beslissers moeten overwegen of het bedrijf zich comfortabel voelt bij dit niveau van onzekerheid en de kans op lagere dan gemiddelde winsten.

Verder raden we je, hoewel optioneel, aan visualisaties zoals histogrammen te maken om de resultaten van de simulaties visueel te begrijpen.

Technieken om Montecarlo-simulaties in Excel te verbeteren

Wanneer je dezelfde simulatie opnieuw draait zoals hierboven, kun je een klein verschil in de berekeningen zien, zoals hieronder weergegeven:

Variërende simulatieresultaten.

Dit komt doordat de waarden van de oorspronkelijke simulatie tussen iteraties kunnen veranderen, wat de resulterende schattingen beïnvloedt. Hoewel de variatie klein is, kan een veranderende schatting zorgen oproepen over de nauwkeurigheid en betrouwbaarheid van de simulatie bij beslissers.

Laten we een paar geavanceerde technieken verkennen die we kunnen gebruiken om de nauwkeurigheid en betrouwbaarheid van de simulaties te verbeteren.

Het aantal simulaties verhogen

Een groter aantal simulaties uitvoeren helpt willekeurige fluctuaties uit te middelen en zorgt voor een stabielere en nauwkeurigere schatting van de uitkomsten.

Voor het bovenstaande voorbeeld kunnen we het aantal simulatieruns verhogen (bijv. van 1.000 naar 10.000 of meer), vooral wanneer we met sterk variabele parameters te maken hebben.

Het bepalen van het “juiste” aantal simulaties hangt van meerdere factoren af.

Hoe complexer het model (dus hoe meer variabelen en hoe groter de onderlinge interacties), hoe meer simulaties doorgaans nodig zijn om alle mogelijke uitkomsten vast te leggen en te zorgen dat de resultaten niet op toeval berusten.

Als de inputs hoge variabiliteit hebben of sterk scheef verdeeld zijn, zijn meer simulaties nodig om de staarten (extreme waarden) van de uitkomstverdelingen nauwkeurig te schatten.

Voor meer gedetailleerde analyses, met name in finance of risicobeheer, is het niet ongebruikelijk om 10.000 tot 100.000 simulaties te draaien. Dit bereik wordt doorgaans gebruikt om robuuste resultaten te waarborgen over verschillende scenario’s en inputs. Uiteraard, zoals eerder genoemd, is Excel voor zulke grootschalige analyses niet altijd de beste keuze, maar eerder R of Python.

De inputverdelingen verfijnen

De nauwkeurigheid van de simulaties hangt grotendeels af van hoe goed de invoer-kansverdelingen de echte onzekerheid en het gedrag van de onderliggende variabelen weerspiegelen. In ons voorbeeld hierboven namen we een normale verdeling aan voor vraag en kosten en een uniforme verdeling voor de verkoopprijs.

Daarnaast kunnen we uitgebreidere historische data analyseren om de verdelingen beter te parametriseren. We kunnen op basis van input van domeinexperts beter begrijpen hoe kosten, prijs en vraag zich gedragen onder externe factoren. We kunnen ook overwegen om verdelingen zoals lognormaal, beta of gamma te gebruiken of aangepaste verdelingen te maken op basis van empirische data.

Een gevoeligheidsanalyse uitvoeren

Deze analyse wordt gedaan om te begrijpen welke inputvariabelen de grootste impact hebben op de output door systematisch elke input te variëren terwijl de andere constant blijven.

In ons voorbeeld kunnen we twee variabelen constant houden en de verdeling van één variabele veranderen om de veranderingen in de schattingen te begrijpen. Herhaal dit vervolgens voor de overige twee variabelen één voor één. Uiteindelijk helpt deze techniek te bepalen op welke variabele je je inspanningen moet richten om de nauwkeurigheid te verbeteren.

Door bovenstaande technieken iteratief toe te passen en de resultaten te analyseren, kom je tot nauwkeurigere en betrouwbaardere uitkomsten.

Conclusie

Deze tutorial heeft je kennis laten maken met de Montecarlo-simulatie en de relevante statistische concepten. Na het introduceren van relevante Excel-functies bood de tutorial een stapsgewijze handleiding om de Montecarlo-simulatie in Excel te implementeren met een praktijkvoorbeeld.

Tot slot heb je enkele best practices en geavanceerde technieken geleerd om je resultaten nauwkeuriger en betrouwbaarder te maken.

Als je vooral geïnteresseerd bent in het implementeren van de bovenstaande Montecarlo-simulatie met andere tools zoals Python of R, zijn deze twee bronnen nuttig:

• Montecarlo-simulaties in R
• Montecarlo-simulaties in Python

Wil je liever bij het vertrouwde Microsoft Excel blijven en je vaardigheden in deze veelgebruikte tool naar een hoger niveau tillen? Bekijk dan onze Excel Fundamentals-track.