Inleiding tot Monte Carlo simulatie in Excel

Dit artikel is afgestemd op Microsoft Excel gegevensanalyse en bedrijfs modellering door Wayne L. Winston.

  • Wie maakt gebruik van Monte Carlo simulatie?

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

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

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

  • Hoe kan een bedrijf voor wenskaarten bepalen hoeveel kaarten ze produceren?

We willen de kansen van onbepaalde gebeurtenissen nauwkeurig ramen. Wat is de kans dat de cashflows van een nieuw product bijvoorbeeld een positieve nettowaarde hebben Wat is de risicofactor van ons investeringsportfolio? Met Monte Carlo simulatie kunnen we situaties modelnen die de onzekerheid presenteren en ze vervolgens op een computer duizenden keren afspelen.

Opmerking:  De simulatie van de naam Monte Carlo is afkomstig van de computer simulaties die zijn uitgevoerd tijdens de 1930s en 1940s om de kans te ramen dat de ketting reactie voor een Atom-Bomb zou werken. De physicists van dit werk waren grote waaiers van gokken, zodat ze de programmacode voor de simulatie van de code Monte Carlo.

In de volgende vier hoofdstukken ziet u voorbeelden van de manier waarop u met Excel Monte Carlo simulaties kunt uitvoeren.

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

  • General Motors, Proctor, Gamble, Pfizer, Bristol-Myers Squibb en Eli Lilly met behulp van simulatie om zowel het gemiddelde als de risicofactor van nieuwe producten te ramen. Bij GM wordt deze informatie door de directeur gebruikt om te bepalen welke producten aan de markt worden verkocht.

  • GM maakt gebruik van simulatie voor activiteiten, zoals het voor spelling van de netto-inkomsten van het bedrijf, het voorspellen van structurele en aankoopkosten, en het bepalen van de berekenings graad voor verschillende risico typen, zoals rentewijzigingen en wisselkoersschommelingen.

  • Lilly maakt gebruik van simulatie om de optimale capaciteit van de plant te bepalen voor elk medicijn.

  • Proctor en Gamble gebruikt simulatie voor model en optimaal hedge Foreign Exchange Risk.

  • Sears maakt gebruik van simulatie om te bepalen hoeveel eenheden van elke product regel moeten worden besteld voor leveranciers, zoals het aantal paren Dockers trousers dat dit jaar moet besteld worden.

  • Oliehoudende en drugs bedrijven gebruiken simulatie voor de waarde ' echte opties ', zoals de waarde van een optie voor het uitvouwen, contracten of uitstellen van een project.

  • Financiële planners gebruiken Monte Carloe simulatie om optimale investerings strategieën voor hun klant te bepalen.

Wanneer u de formule = ASELECT () typt in een cel, krijgt u een getal dat even waarschijnlijk een waarde tussen 0 en 1 opneemt. Daarom moet u ongeveer 25 procent van de tijd een getal kopen dat kleiner is dan of gelijk is aan 0,25. rond 10 procent van de tijd dat u een nummer moet aanvragen dat minimaal 0,90 is, enzovoort. Als u wilt zien hoe de RAND functie werkt, bekijkt u de Randdemo.xlsx bestand, weergegeven in afbeelding 60-1.

Afbeelding van boek

Opmerking:  Wanneer u het bestand Randdemo.xlsx opent, worden de willekeurige getallen niet weergegeven in afbeelding 60-1. Met de functie ASELECT worden de getallen die worden gegenereerd wanneer een werkblad wordt geopend of wanneer nieuwe informatie in het werkblad wordt ingevoerd, automatisch opnieuw berekend.

Kopieer eerst van cel C3 naar C4: C402 de formule = ASELECT (). Vervolgens noemen we het bereik C3: C402- gegevens. Vervolgens kunt u in kolom F het gemiddelde van de 400 willekeurige getallen (cel F2) bijhouden en de functie aantal. als gebruiken om te bepalen welke fracties tussen 0 en 0,25, 0,25 en 0,50, 0,50 en 0,75, en 0,75 en 1. Wanneer u op de toets F9 drukt, worden de willekeurige getallen opnieuw berekend. U ziet dat het gemiddelde van de 400-nummers altijd ongeveer 0,5 is en dat rond 25 procent van de resultaten in intervallen van 0,25. Deze resultaten zijn consistent met de definitie van een willekeurig getal. Houd er rekening mee dat de waarden die worden gegenereerd door ASELECT in verschillende cellen onafhankelijk zijn. Als het willekeurig getal dat is gegenereerd in cel C3 een groot getal is (bijvoorbeeld 0,99), geeft het ons niets aan de waarden van de andere willekeurige getallen.

Stel dat de vraag voor een agenda onder de volgende aparte variabele valt:

Aanvraag

kans

10.000

0,10

20.000

0,35

40.000

0,3

60.000

0,25

Hoe kan ik in Excel afspeellijk uitchecken, of gesimuleerd, deze vraag voor agenda's een aantal keren? De truc is om elke mogelijke waarde van de functie ASELECT te koppelen met een mogelijke vraag voor agenda's. Met de volgende opdracht wordt gegarandeerd dat een vraag van 10.000 10 procent van de tijd voorkomt, enzovoort.

Aanvraag

Willekeurig nummer 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 demonstreren, bekijkt u het bestand Discretesim.xlsx, dat wordt weergegeven in afbeelding 60-2, op de volgende pagina.

Afbeelding van boek

De sleutel voor onze simulatie is het gebruik van een willekeurig getal om een zoekopdracht te starten vanuit het Tabelbereik F2: G5 (met de naam zoekactie). Een willekeurig getal groter dan of gelijk aan 0 en kleiner dan 0,10 levert een vraag van 10.000. willekeurige getallen met een waarde groter dan of gelijk aan 0,10 en kleiner dan 0,45 leveren een vraag van 20.000 op. willekeurige getallen met een waarde groter dan of gelijk aan 0,45 en kleiner dan 0,75 leveren een vraag van 40.000 op. met een willekeurig getal dat groter is dan of gelijk is aan 0,75, levert dit 60.000 op. U genereert 400 willekeurige getallen door te kopiëren van C3 naar C4: C402 de formule ASELECT (). Vervolgens genereert u 400-experimenten, of herhalingen, van de agenda vraag door het kopiëren van B3 naar B4: B402 de formule VERT. zoeken (C3, zoeken, 2). Met deze formule wordt gegarandeerd dat willekeurig een willekeurig getal kleiner is dan 10.000 0,10, wat willekeurig getal tussen 0,10 en 0,45 genereert, een vraag van 20.000, enzovoort. In het cellenbereik F8: F11, gebruikt u de functie aantal. als om het gedeelte van de 400 iteraties te bepalen, waarbij elke vraag wordt bepaald. Wanneer u op F9 drukt om de willekeurige getallen opnieuw te berekenen, zijn de gesimuleerde kansen bijna op de beproefde vraag waarschijnlijk.

Als u in een willekeurige cel typt, wordt de formule norm. INV ( ), mu, Sigma)gebruikt, wordt de gesimuleerde waarde van een normale willekeurige variabele gegenereerd met een gemiddelde mu en standaarddeviatie Sigma. Dit is een illustratie van de procedure in de Normalsim.xlsx bestand, weergegeven in afbeelding 60-3.

Afbeelding van boek

Stel dat we 400-proefversies of herhalingen willen simuleren voor een normale, willekeurige variabele met een gemiddelde van 40.000 en een standaarddeviatie van 10.000. (U kunt deze waarden in de cellen E1 en E2 typen, en de cellen punt en Sigmaeen naam geven.) Als u de formule = ASELECT () van C4 naar C5 kopieert, genereert 400 verschillende willekeurige getallen. Kopiëren van B4 naar B5: B403 de formule norm. voorwaarden (C4, gemiddelde, Sigma) genereert 400 verschillende proef waarden van een normale willekeurige variabele met een gemiddelde van 40.000 en een standaarddeviatie van 10.000. Wanneer u op F9 drukt om de willekeurige getallen opnieuw te berekenen, blijft het gemiddelde dicht bij 40.000 en wordt de standaarddeviatie van de 10.000.

In feite, voor een willekeurig getal x, genereert de functie norm. INV (p, mu, Sigma) het p-percentiel van een normale willekeurige variabele met een gemiddelde mu en een standaarddeviatie Sigma. Zo wordt het willekeurig getal 0,77 in cel C4 (zie afbeelding 60-3) gegenereerd in cel B4, met een gemiddelde van een willekeurige variabele met een gemiddelde van 40.000 en een standaarddeviatie van 10.000.

In deze sectie ziet u hoe u simulatie van Monte Carlo kunt gebruiken als hulpmiddel voor besluitvorming. Stel dat de vraag voor een Valentijnsdag voor de dag is onderworpen aan de volgende aparte, willekeurige variabele:

Aanvraag

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 een kaart is $1,50. Openstaande kaarten moeten worden afgestoten tegen een kosten van $0,20 per kaart. Hoeveel kaarten moet u afdrukken?

In principe simuleert u elke mogelijke productiehoeveelheid (10.000, 20.000, 40.000 of 60.000) vaak (bijvoorbeeld de kans 1000 van een). Vervolgens bepalen welke Orderhoeveelheid de maximale gemiddelde winst van de 1000 iteraties levert. U vindt de gegevens voor deze sectie in het bestand Valentine.xlsx, weergegeven in afbeelding 60-4. U wijst de bereiknamen in de cellen B1: B11 toe aan de cellen C1: C11. Het celbereik G3: H6 is de naam zoekactietoegewezen. Onze verkoopprijs en kosten parameters worden ingevoerd in de cellen C4: C6.

Afbeelding van boek

U kunt een proef hoeveelheid voor de proefperiode (40.000 in dit voorbeeld) invoeren in cel C1. Maak vervolgens een willekeurig getal in cel C2 met de formule = rand (). Zoals hierboven is beschreven, simuleert u de vraag voor de kaart in cel C3 met de formule VERT. zoeken (ASELECT, zoeken, 2). (In de formule vert. zoeken is ASELECT de naam van de cel die is toegewezen aan cel C3, niet de functie ASELECT.)

Het aantal verkochte eenheden is de kleinste hoeveelheid en aanvraag voor de productiehoeveelheid. In cel C8 berekent u onze opbrengst met de formule min (geproduceerd, vraag) * unit_price. In cel C9 berekent u de totale productiekosten met de gemaakte formule * unit_prod_cost.

Als we meer kaarten produceren dan in de loop van de vraag, dan is het aantal resterende eenheden gelijk aan de productie min de vraag. anders zijn er geen eenheden. We berekenen onze buitengebruikstellings kosten in cel C10 met de formule unit_disp_cost * als (geproduceerd>vraag, geproduceerd – vraag, 0). Ten slotte berekent u in cel C11 onze winst als winst -total_var_cost-total_disposing_cost.

We willen een efficiënte manier willen op F9 te drukken (bijvoorbeeld 1000) voor elke productiehoeveelheid en een hoge winst voor de verwachte winst voor elke hoeveelheid. Deze situatie is een functie waarbij een gegevenstabel met twee manieren naar onze hulpverlenings gegevens verstuurt. (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 cellenbereik A16: A1015 de getallen 1 – 1000 (overeenkomen met onze proefversies van 1000) in. U kunt deze waarden op een eenvoudige manier maken door eerst 1 in te voeren in cel A16. Selecteer de cel, klik op het tabblad Start in de groep bewerken op door voerenen selecteer reeks om het dialoogvenster reeks weer te geven. Voer in het dialoogvenster reeks , die wordt weergegeven in afbeelding 60-6, een stapwaarde van 1 en een eindwaarde van 1000 in. Selecteer in het gebied reeks in de optie kolommen en klik vervolgens op OK. De getallen 1-1000 worden ingevoerd in kolom A beginnend in cel A16.

Afbeelding van boek

Vervolgens voer ik onze mogelijke productiehoeveelheden (10.000, 20.000, 40.000, 60.000) in de cellen B15: E15. We willen winst berekenen voor elk proef nummer (1 tot en met 1000) en elke productiehoeveelheid. We verwijzen naar de formule voor winst (berekend in cel C11) in de cel in de linkerbovenhoek van de gegevenstabel (A15) door = C11in te voeren.

We gaan nu een oplossing maken voor de samenvoeging 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 gegevenstabel. Als u een gegevenstabel met twee kolommen wilt instellen, kiest u de cel in de cel C1 en selecteert u een lege cel (we kiezen cel I14) als de invoercel. Nadat u op OK hebt geklikt, simuleert Excel 1000 aanvraag waarden voor elke Orderhoeveelheid.

Als u meer wilt weten over de manier waarop dit werkt, moet u rekening houden met de waarden die zijn geplaatst door de gegevenstabel in het cellenbereik C16: C1015. Voor elk van deze cellen wordt in Excel de waarde 20.000 in cel C1 gebruikt. In C16 wordt de waarde 1 voor de rij-invoercel van 1 in een lege cel en het willekeurig getal in cel C2 opnieuw berekend. De overeenkomstige winst wordt vervolgens opgenomen in cel C16. Vervolgens wordt de rij-invoerwaarde van 2 in een lege cel geplaatst en wordt het willekeurig getal in C2 opnieuw berekend. De overeenkomstige winst wordt ingevoerd in cel C17.

Door te kopiëren van cel B13 naar C13: E13 de formule gemiddelde (B16: B1015), berekent de gemiddelde gesimuleerde winst voor elke productiehoeveelheid. Door te kopiëren van cel B14 naar C14: E14 de formule StDev (B16: B1015), wordt de standaarddeviatie van de gesimuleerde winst voor elke Orderhoeveelheid berekend. Telkens wanneer u op F9 drukt, worden 1000 iteraties van de vraag gesimuleerd voor elke Orderhoeveelheid. Bij het produceren van 40.000-kaarten wordt altijd de grootste verwachte winst geoogst. Daarom blijkt dit dat het opleveren van 40.000-kaarten de juiste beslissing oplevert.

Impact van risico op onze beslissing      Als we 20.000 in plaats van 40.000-kaarten hebben vervaardigd, zakte de verwachte winst met ongeveer 22 procent af, maar het risico (zoals gemeten door de standaarddeviatie van de winst) zakt bijna 73 procent. En als we zeer averse zijn, is het belangrijk dat u 20.000-kaarten produceert. Voor incidentele 10.000-kaarten wordt altijd een standaarddeviatie van 0 kaarten weergegeven, aangezien, als we 10.000-kaarten produceren, zullen we ze altijd allemaal verkopen zonder dat ze overgebleven zijn.

Opmerking:  In deze werkmap is de berekenings optie ingesteld op Automatisch behalve voor tabellen. (Gebruik de opdracht berekening in de groep berekening op het tabblad Formules). Met deze instelling wordt gegarandeerd dat de gegevenstabel niet opnieuw wordt berekend, tenzij we op F9 drukken, wat een goed idee is omdat een grote gegevenstabel de hoeveelheid werk vertraagt als deze wordt herberekend wanneer u iets typt in het werkblad. Wanneer u op F9 drukt, wordt de gemiddelde winst gewijzigd als u in dit voorbeeld drukt. Dit gebeurt omdat telkens wanneer u op F9 drukt, een andere reeks 1000 willekeurige getallen worden gebruikt voor het genereren van aanvragen voor elke Orderhoeveelheid.

Betrouwbaarheids interval voor gemiddelde winst      Een natuurlijke vraag om in deze situatie te vragen, is in welk interval we 95 procent garanderen dat de werkelijke gemiddelde winst vervalt? Dit interval wordt aangeduid met het betrouwbaarheidsinterval voor 95 procent voor gemiddelde winst. Voor het gemiddelde van een simulatie uitvoer wordt een 95 procent betrouwbaarheidsinterval berekend met de volgende formule:

Afbeelding van boek

In cel J11 berekent u de ondergrens voor het interval voor het percentage vertrouwen op 95 de gemiddelde winst wanneer 40.000-agenda's worden geproduceerd met de formule D13 – 1.96 * D14/sqrt (1000). In cel J12 berekent u de bovenlimiet voor ons percentage betrouwbaarheidsinterval van 95 met de formule D13 + 1.96 * D14/sqrt (1000). Deze berekeningen worden weergegeven in afbeelding 60-7.

Afbeelding van boek

We zijn 95 procent dat onze gemiddelde winst wanneer 40.000-agenda's besteld liggen tussen $56.687 en $62.589.

  1. Een GMC-dealer beoordeelt dat vraag voor 2005 Envoys normaal wordt verdeeld met een gemiddelde van 200 en de standaarddeviatie van 30. De kosten voor het ontvangen van een Envoy is $25.000 en hij verkoopt een Envoy voor $40.000. De helft van alle Envoys niet voor de volledige prijs verkocht, kan worden verkocht voor $30.000. Hij overweegt 200, 220, 240, 260, 280 of 300 Envoys te bestellen. Hoeveel moet hij onze bestelling doen?

  2. Een kleine supermarkt probeert vast te stellen hoeveel kopieën van personen in het magazijn elke week moeten bestellen. Ze geloven dat hun vraag voor mensen onder de volgende afzonderlijke willekeurige variabele valt:

    Aanvraag

    kans

    15

    0,10

    20

    0,20

    25

    0,30

    dertig

    0,25

    35

    0,15

  3. De supermarketing betaalt $1,00 voor elk exemplaar van mensen en verkoopt het voor $1,95. Elke onverkochte kopie kan worden geretourneerd voor $0,50. Hoeveel exemplaren van personen moet de winkel bestelling doen?

Meer hulp nodig?

U kunt altijd uw vraag stellen aan een expert in de Excel Tech Community, ondersteuning vragen in de Answer-community of een nieuwe functie of verbetering voorstellen in Excel User Voice.

Meer hulp nodig?

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

Was deze informatie nuttig?

Bedankt voor uw feedback.

Hartelijk dank voor uw feedback! Het lijkt ons een goed idee om u in contact te brengen met een van onze Office-ondersteuningsagenten.

×