Dit artikel is aangepast Microsoft Excel gegevensanalyse en bedrijfsmodelleren door Wayne L. Winston.

  • Wie gebruikt u Monte Carlo-simulatie?

  • Wat gebeurt er wanneer u =RAND() typt in een cel?

  • Hoe kunt u waarden van een afzonderlijke willekeurige variabele simuleren?

  • Hoe kunt u waarden van een normale willekeurige variabele simuleren?

  • Hoe kan een wenskaartbedrijf bepalen hoeveel kaarten er moeten worden gemaakt?

We willen de waarschijnlijkheid van onzekerheden nauwkeurig inschatten. Wat is bijvoorbeeld de kans dat de cashflows van een nieuw product een positieve netto huidige waarde (NHW) hebben? Wat is de risicofactor van onze investeringsportefeuille? Met een Monte Carlo-simulatie kunnen we situaties modelleren die onzekerheid opleveren en deze vervolgens duizenden keren op een computer afspelen.

Opmerking:  De naam Monte Carlo-simulatie komt van de computersimulaties die zijn uitgevoerd in de jaren 30 en 40 om de kans te schatten dat de kettingreactie die nodig is om een atomenbom te laten afgaan, goed werkt. De natuurkundigen die betrokken waren bij dit werk waren grote fans van het gokken, dus ze hebben de simulaties de codenaam Monte Carlo gegeven.

In de volgende vijf hoofdstukken ziet u voorbeelden van hoe u Excel Monte Carlo-simulaties kunt uitvoeren.

Veel bedrijven gebruiken Monte Carlo-simulatie als een belangrijk onderdeel van hun besluitvormingsproces. Hier zijn enkele voorbeelden.

  • General Motors, Proctor and Gamble, Pfizer, Bristol-Myers Squibb en Eli Lilly gebruiken een simulatie om zowel het gemiddelde rendement als de risicofactor van nieuwe producten te schatten. Bij GM wordt deze informatie door de CEO gebruikt om te bepalen welke producten op de markt worden gebracht.

  • GM gebruikt simulatie voor activiteiten zoals het voorspellen van netto-inkomsten voor de onderneming, het voorspellen van structurele en inkoopkosten en het bepalen van de gevoeligheid voor verschillende soorten risico's (zoals rentewijzigingen en wisselkoersfluctuaties).

  • Lilly gebruikt simulatie om de optimale plantcapaciteit voor elk medicijn te bepalen.

  • Proctor en Gamble gebruiken simulatie om valutarisico's te modelleren en optimaal af te dekken.

  • Sears gebruikt simulatie om te bepalen hoeveel eenheden van elke productlijn moeten worden besteld bij leveranciers, bijvoorbeeld het aantal paar Dockers-broeken dat dit jaar moet worden besteld.

  • Olie- en farmaceutische bedrijven gebruiken simulatie om 'reële opties' te waarderen, zoals de waarde van een optie om een project uit te vouwen, uit te breiden of uit te stellen.

  • Financiële planners gebruiken Monte Carlo-simulatie om optimale investeringsstrategieën te bepalen voor het pensioen van hun klanten.

Wanneer u de formule =RAND() in een cel typt, krijgt u een getal dat waarschijnlijk een waarde tussen 0 en 1 zal aannemen. Ongeveer 25 procent van de tijd krijgt u dus een getal kleiner dan of gelijk aan 0,25; ongeveer 10 procent van de tijd moet u een getal krijgen dat ten minste 0,90 is, en ga zo maar door. Als u wilt laten zien hoe de functie RAND werkt, bekijkt u de bestandsindeling Randdemo.xlsx, weergegeven in afbeelding 60-1.

Afbeelding van boek

Opmerking:  Wanneer u de bestandsindeling Randdemo.xlsx, ziet u niet dezelfde willekeurige getallen in afbeelding 60-1. Met de functie RAND worden altijd automatisch de getallen berekend die het genereert wanneer een werkblad wordt geopend of wanneer er nieuwe gegevens in het werkblad worden ingevoerd.

Kopieer eerst van cel C3 naar C4:C402 de formule =RAND(). Vervolgens noemt u het bereik C3:C402 Data. Vervolgens kunt u in kolom F het gemiddelde van de 400 willekeurige getallen (cel F2) bijhouden en de functie AANTAL.ALS gebruiken om de breuken tussen 0 en 0,25, 0,25 en 0,50, 0,50 en 0,75 en 0,75 en 1 te bepalen. Wanneer u op de toets F9 drukt, worden de willekeurige getallen opnieuw berekend. Het gemiddelde van de 400 getallen is altijd ongeveer 0,5 en dat ongeveer 25 procent van de resultaten een intervallen van 0,25 heeft. Deze resultaten komen overeen met de definitie van een willekeurig getal. Houd er ook rekening mee dat de waarden die door RAND in verschillende cellen worden gegenereerd, onafhankelijk zijn. Als het willekeurige getal dat in cel C3 wordt gegenereerd bijvoorbeeld een groot getal is (bijvoorbeeld 0,99), wordt er niets verteld over de waarden van de andere gegenereerde willekeurige getallen.

Stel dat de vraag naar een agenda wordt bepaald door de volgende afzonderlijke willekeurige variabele:

Vraag

kans

10.000

0,10

20.000

0.35

40,000

0,3

60.000

0,25

Hoe kunnen we deze Excel vaak afspelen of simuleren? De truc is om elke mogelijke waarde van de functie RAND te koppelen aan een mogelijke vraag naar agenda's. De volgende opdracht zorgt ervoor dat een vraag van 10.000 10 procent van de tijd wordt uitgevoerd, en ga zo maar door.

Vraag

Willekeurig getal toegewezen

10.000

Kleiner dan 0,10

20.000

Groter dan of gelijk aan 0,10 en kleiner dan 0,45

40,000

Groter dan of gelijk aan 0,45 en kleiner dan 0,75

60.000

Groter dan of gelijk aan 0,75

Als u de simulatie van de vraag wilt laten zien, bekijkt u de Discretesim.xlsx, weergegeven in afbeelding 60-2 op de volgende pagina.

Afbeelding van boek

De sleutel tot onze simulatie is om een willekeurig getal te gebruiken om een opzoekactie te starten vanuit het tabelbereik F2:G5 (met de naam opzoekactie). Willekeurige getallen groter dan of gelijk aan 0 en kleiner dan 0,10 leveren een vraag op van 10.000; willekeurige getallen groter dan of gelijk aan 0,10 en kleiner dan 0,45 leveren een vraag op van 20.000; willekeurige getallen groter dan of gelijk aan 0,45 en kleiner dan 0,75 leveren een vraag op van 40.000; en willekeurige getallen groter dan of gelijk aan 0,75 leveren een vraag op van 60.000. U genereert 400 willekeurige getallen door van C3 naar C4:C402 de formule RAND()te kopiëren. Vervolgens genereert u 400 experimenten, of iteraties, van de kalendervraag door de formule VERT.ZOEKEN(C3;opzoekactie;2)te kopiëren van B3 naar B4:B402. Deze formule zorgt ervoor dat een willekeurig getal kleiner dan 0,10 een vraag genereert van 10.000, een willekeurig getal tussen 0,10 en 0,45 genereert een vraag van 20.000, en zo verder. Gebruik in het celbereik F8:F11 de functie AANTAL.ALS om de breuk van onze 400 iteraties te bepalen die elke vraag opleveren. Wanneer we op F9 drukken om de willekeurige getallen opnieuw te berekenen, zijn de gesimuleerde waarschijnlijkheden dicht bij de waarschijnlijkheid van de veronderstelde vraag.

Als u in een cel de formule NORMINV(rand(),mu,sigma)typt, genereert u een gesimuleerde waarde van een normale willekeurige variabele met een gemiddelde mu en standaarddeviatie sigma. Deze procedure wordt geïllustreerd in het bestand Normalsim.xlsx, weergegeven in afbeelding 60-3.

Afbeelding van boek

Stel dat we 400 experimenten of iteraties willen simuleren voor een normale willekeurige variabele met een gemiddelde van 40.000 en een standaarddeviatie van 10.000. (U kunt deze waarden typen in de cellen E1 en E2 en deze cellen respectievelijk gemiddelde en sigmanoemen.) Als u de formule =RAND() kopieert van C4 naar C5:C403, worden 400 verschillende willekeurige getallen gegenereerd. Als u van B4 naar B5:B403 kopieert, genereert de formule NORMINV(C4;gemiddelde,sigma) 400 verschillende proefwaarden van een normale willekeurige variabele met een gemiddelde van 40.000 en een standaarddeviatie van 10.000. Wanneer we op de F9-toets drukken om de willekeurige getallen opnieuw te berekenen, blijft het gemiddelde dicht bij 40.000 en de standaarddeviatie bijna 10.000.

Voor een willekeurig getal xwordt met de formule NORMINV(p,mu,sigma) het pth percentiel van een normale willekeurige variabele gegenereerd met een gemiddelde mu en een standaarddeviatie sigma. Het willekeurige getal 0,77 in cel C4 (zie Afbeelding 60-3) genereert bijvoorbeeld in cel B4 ongeveer het 77e percentiel van een normale willekeurige variabele met een gemiddelde van 40.000 en een standaarddeviatie van 10.000.

In deze sectie ziet u hoe Monte Carlo-simulatie kan worden gebruikt als een beslissingshulpmiddel. Stel dat de vraag naar een Valentijnskaart wordt bepaald door de volgende discrete willekeurige variabele:

Vraag

kans

10.000

0,10

20.000

0.35

40,000

0,3

60.000

0,25

De wenskaart wordt verkocht voor $ 4,00 en de variabele kosten voor het produceren van elke kaart zijn $ 1,50. Restkaarten moeten worden verwijderd tegen een prijs van $ 0,20 per kaart. Hoeveel kaarten moeten worden afgedrukt?

In feite simuleren we elke mogelijke productiehoeveelheid (10.000, 20.000, 40.000 of 60.000) vaak (bijvoorbeeld 1000 iteraties). Vervolgens bepalen we welke orderhoeveelheid de maximale gemiddelde winst oplevert over de 1000 iteraties. U vindt de gegevens voor deze sectie in de Valentine.xlsx, weergegeven in afbeelding 60-4. U wijst de bereiknamen in cellen B1:B11 toe aan cellen C1:C11. Aan het celbereik G3:H6 is de naamzoekactie toegewezen. Onze verkoopprijs- en kostenparameters worden ingevoerd in de cellen C4:C6.

Afbeelding van boek

U kunt een proefproductiehoeveelheid (40.000 in dit voorbeeld) invoeren in cel C1. Maak vervolgens een willekeurig getal in cel C2 met de formule =RAND(). Zoals eerder beschreven, simuleert u de vraag naar de kaart in cel C3 met de formule VERT.ZOEKEN(rand,opzoek,2). (In de formule VERT.ZOEKEN is rand de celnaam die is toegewezen aan cel C3, niet aan de functie RAND.)

Het aantal verkochte eenheden is de kleinste van onze productiehoeveelheid en -vraag. In cel C8 berekent u onze omzet met de formule MIN(produced,demand)*unit_price. In cel C9 berekent u de totale productiekosten met de formule die*unit_prod_cost.

Als we meer kaarten produceren dan de vraag, is het aantal eenheden dat overblijft gelijk aan de productie min de vraag. anders blijven er geen eenheden over. We berekenen onze verwijderingskosten in cel C10 met de formule unit_disp_cost*ALS(geproduceerd>vraag,geproduceerd-vraag,0). Ten slotte berekenen we in cel C11 onze winst als omzet, total_var_cost-total_disposing_cost.

We willen een efficiënte manier om vaak op F9 te drukken (bijvoorbeeld 1000) voor elke productiehoeveelheid en de verwachte winst voor elke hoeveelheid bij elkaar te houden. Deze situatie is een situatie waarin een gegevenstabel met twee punten ons te hulp komt. (Zie Hoofdstuk 15, 'Gevoeligheidsanalyse met gegevenstabellen', voor meer informatie over gegevenstabellen.) De gegevenstabel die in dit voorbeeld wordt gebruikt, wordt weergegeven in afbeelding 60-5.

Afbeelding van boek

Voer in het celbereik A16:A1015 de getallen 1-1000 in (die overeenkomen met onze 1000 experimenten). U kunt deze waarden eenvoudig maken door te beginnen met het invoeren van 1 in cel A16. Selecteer de cel en klik op het tabblad Start in de groep Bewerken op Opvullenen selecteer Reeks om het dialoogvenster Reeks weer te geven. Typ in het dialoogvenster Reeks, weergegeven in afbeelding 60-6, een stapwaarde van 1 en een stopwaarde van 1000. Selecteer in het gebied Reeks in de optie Kolommen en klik vervolgens op OK. De getallen 1-1000 worden ingevoerd in kolom A vanaf cel A16.

Afbeelding van boek

Vervolgens voeren we onze mogelijke productiehoeveelheden (10.000, 20.000, 40.000, 60.000) in cellen B15:E15 in. We willen de winst berekenen voor elk proefnummer (1 tot en met 1000) en elke productiehoeveelheid. We verwijzen naar de formule voor winst (berekend in cel C11) in de cel linksboven in onze gegevenstabel (A15) door =C11 in te geven.

We zijn nu klaar om de Excel in het simuleren van 1000 iteraties van de vraag voor elke productiehoeveelheid. Selecteer het tabelbereik (A15:E1014) en klik vervolgens in de groep Hulpmiddelen voor gegevens op het tabblad Gegevens op Wat als-analyse en selecteer vervolgens Gegevenstabel. Als u een gegevenstabel in twee delen wilt instellen, kiest u onze productiehoeveelheid (cel C1) als rijinvoercel en selecteert u een lege cel (we hebben cel I14 gekozen) als kolominvoercel. Nadat u op OK hebt geklikt, worden Excel 1000 vraagwaarden voor elke orderhoeveelheid gesimuleerd.

Als u wilt weten waarom dit werkt, moet u rekening houden met de waarden die door de gegevenstabel in het celbereik C16:C1015 worden geplaatst. Voor elk van deze cellen Excel een waarde van 20.000 in cel C1. In C16 wordt de kolominvoercelwaarde van 1 in een lege cel geplaatst en wordt het willekeurige getal in cel C2 herberekend. De bijbehorende winst wordt vervolgens opgenomen in cel C16. Vervolgens wordt de kolomcelinvoerwaarde van 2 in een lege cel geplaatst en wordt het willekeurige getal in C2 opnieuw berekend. De bijbehorende winst wordt ingevoerd in cel C17.

Door van cel B13 naar C13:E13 de formule GEMIDDELDE(B16:B1015)te kopiëren, berekenen we de gemiddelde gesimuleerde winst voor elke productiehoeveelheid. Door van cel B14 naar C14:E14 de formule STDEV(B16:B1015)te kopiëren, berekenen we de standaarddeviatie van onze gesimuleerde winst voor elke orderhoeveelheid. Telkens als we op F9 drukken, worden 1000 iteraties van de vraag gesimuleerd voor elke orderhoeveelheid. Het produceren van 40.000 kaarten levert altijd de grootste verwachte winst op. Het lijkt er dus op dat het produceren van 40.000 kaarten de juiste beslissing is.

De impact van risico's op onze beslissing      Als we 20.000 in plaats van 40.000 kaarten hebben geproduceerd, daalt de verwachte winst ongeveer 22 procent, maar daalt ons risico (zoals gemeten aan de standaarddeviatie van winst) met bijna 73 procent. Als we dus zeer afschuw hebben tegen risico's, is het mogelijk dat 20.000 kaarten worden geproduceerd. Het produceren van 10.000 kaarten heeft overigens altijd een standaarddeviatie van 0 kaarten, want als we 10.000 kaarten produceren, verkopen we ze altijd allemaal zonder restjes.

Opmerking:  In deze werkmap is de optie Berekening ingesteld op Automatisch behalve voor tabellen. (Gebruik de opdracht Berekening in de groep Berekening op het tabblad Formules.) Deze instelling zorgt ervoor dat onze gegevenstabel niet opnieuw wordt berekend, tenzij we op F9 drukken. Dit is een goed idee, omdat een grote gegevenstabel uw werk vertraagt als deze elke keer wordt herberekend wanneer u iets in uw werkblad typt. Houd er rekening mee dat in dit voorbeeld, wanneer u op F9 drukt, de gemiddelde winst verandert. Dit gebeurt omdat telkens wanneer u op F9 drukt, een andere reeks van 1000 willekeurige getallen wordt gebruikt om eisen te genereren voor elke orderhoeveelheid.

Betrouwbaarheidsinterval voor gemiddelde winst      Een natuurlijke vraag die u in deze situatie moet stellen, is: in welk interval weten we 95 procent zeker dat de werkelijke gemiddelde winst zal dalen? Dit interval wordt het betrouwbaarheidsinterval van 95 procent genoemd voor de gemiddelde winst. Een betrouwbaarheidsinterval van 95 procent voor het gemiddelde van een simulatieuitvoer wordt berekend met de volgende formule:

Afbeelding van boek

In cel J11 berekent u de ondergrens voor het betrouwbaarheidsinterval van 95 procent op de gemiddelde winst wanneer 40.000 kalenders worden geproduceerd met de formule D13–1,96*D14/SQRT(1000). In cel J12 berekent u de bovengrens voor ons betrouwbaarheidsinterval van 95 procent met de formule D13+1,96*D14/SQRT(1000). Deze berekeningen worden weergegeven in afbeelding 60-7.

Afbeelding van boek

We zijn er 95 procent zeker van dat onze gemiddelde winst bij het bestellen van 40.000 agenda's tussen $ 56.687 en $ 62.589 ligt.

  1. Een GMC-leverancier is van mening dat de vraag naar 2005 Envoys normaal gesproken wordt verdeeld met een gemiddelde van 200 en een standaarddeviatie van 30. Zijn kosten voor het ontvangen van een envoy zijn $ 25.000 en hij verkoopt een envoy voor $ 40.000. De helft van alle envoys die niet tegen volledige prijs worden verkocht, kan worden verkocht voor $ 30.000. Hij overweegt 200, 220, 240, 260, 280 of 300 envoys te bestellen. Hoeveel moet hij bestellen?

  2. Een kleine supermarkt probeert te bepalen hoeveel exemplaren van people magazine ze elke week moeten bestellen. Ze zijn van mening dat hun vraag naar Personen wordt bepaald door de volgende discrete willekeurige variabele:

    Vraag

    kans

    15

    0,10

    20

    0.20

    25

    0.30

    30

    0,25

    35

    0,15

  3. De supermarkt betaalt $ 1,00 voor elk exemplaar van Personen en verkoopt deze voor $ 1,95. Elke niet-verkochte kopie kan worden geretourneerd voor $ 0,50. Hoeveel exemplaren van Personen moet de winkel bestellen?

Meer hulp nodig?

U kunt altijd uw vraag stellen aan een expert in de Excel Tech Community of ondersteuning vragen in de Answers-community.

Meer hulp nodig?

Uw vaardigheden uitbreiden
Training verkennen
Als eerste nieuwe functies krijgen
Deelnemen aan Microsoft Office Insiders

Was deze informatie nuttig?

Hoe tevreden bent u met de taalkwaliteit?
Wat heeft uw ervaring beïnvloed?

Bedankt voor uw feedback.

×