Inleiding tot Monte Carlo simulatie in Excel

Opmerking:  We willen u graag zo snel mogelijk de meest recente Help-inhoud in uw eigen taal bieden. Deze pagina is automatisch vertaald en kan grammaticale fouten of onnauwkeurigheden bevatten. Wij hopen dat deze inhoud nuttig voor u is. Kunt u ons onder aan deze pagina laten weten of de informatie nuttig voor u was? Hier is het Engelstalige artikel ter referentie.

In dit artikel is afkomstig uit Microsoft Excel Data Analysis and Business Modeling van Wayne L. Winston.

  • Wie Monte Carlo simulatie gebruikt?

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

  • Hoe kunt u de waarden van een aparte stochastische variabele simuleren?

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

  • Hoe kan een bedrijf wenskaart bepalen hoeveel kaarten zodat het eindresultaat?

Willen we de waarschijnlijkheid van onduidelijke gebeurtenissen nauwkeurig te schatten. Bijvoorbeeld: Wat is de kans dat een nieuw product cashflows een positief netto huidige waarde (NHW hebben)? Wat is de risicofactor van onze investeringsportfolio? Monte Carlo simulatie kunt ons model situaties waarin er onzekerheid presenteren en ze vervolgens af afspeel op een computer duizendtallen vaak.

Opmerking: De naam Monte Carlo simulatie is afkomstig van de computer simulaties uitgevoerd tijdens de 1930s en 1940s voor het schatten van de kans die de reactie van de ketting die u nodig hebt voor een atom-bomb temperatuurgevoeligheid goed werkt. De physicists betrokken bij deze werk zijn groot ventilatoren geldpelen, zodat ze de simulaties Monte Carloop de naam van de code gegeven.

In de volgende vier hoofdstukken ziet u voorbeelden van hoe u Excel gebruiken kunt om uit te voeren Monte Carlo-simulaties.

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

  • Algemene Motors, Proctor en weddenschap was Pfizer, Squibb Bristol-Hertog is opgeslagen en Eli Lilly gebruik simulatie zowel het gemiddelde rendement als de risicofactor van nieuwe producten. Deze informatie wordt op GM gebruikt door de CEO om te bepalen welke producten op de markt komt.

  • GM simulatie voor activiteiten zoals netto-inkomsten voor het bedrijf prognoses, voorspellingen te doen structurele en aanschaffen kosten en bepalen van de gevoeligheid voor verschillende soorten risico (zoals rentepercentage verandert en wisselkoersfluctuaties) wordt gebruikt.

  • Simulatie Lilly gebruikt om te bepalen de capaciteit optimale plant voor elke medicijnen.

  • Proctor en weddenschap was wordt gebruikgemaakt van simulatie naar model en optimaal hedge vreemde valuta risico.

  • Sears simulatie gebruikt om te bepalen hoeveel eenheden van elke productlijn geordend van leveranciers, bijvoorbeeld het aantal paren van Dockers lange die dit jaar moeten worden gerangschikt.

  • Olive Oil en medicijnen bedrijven gebruiken simulatie waarde "reële opties", zoals de waarde van een optie wilt uitbreiden, contract of een project uitstellen.

  • Monte Carlo simulatie financiële planners gebruiken om te bepalen optimale investeringsstrategieën voor hun clients buitengebruikstelling.

Als u de formule =ASELECT() in een cel typt, krijgt u een getal dat gelijkmatig waarschijnlijk wordt ervan uitgegaan dat elke waarde tussen 0 en 1. Dus ontvangt rond 25 procent van de tijd, u een getal kleiner is dan of gelijk is aan 0,25; 10 procent van de tijd dat u een getal dat krijgt moet is oplossing ten minste 0.90, enzovoort. U kunt zien hoe de functie ASELECT werkt, raadpleegt u het bestand Randdemo.xlsx, weergegeven in afbeelding 60-1.

Afbeelding van boek
Afbeelding 60-1 wilt zien waarin de functie ASELECT

Opmerking: Wanneer u het bestand Randdemo.xlsx opent, ziet u niet dezelfde aselecte getallen weergegeven in afbeelding 60-1. De functie ASELECT worden altijd automatisch de getallen die wordt gegenereerd wanneer een werkblad wordt geopend of wanneer u nieuwe gegevens zijn ingevoerd in het werkblad opnieuw berekend.

Eerst kopiëren uit cel C3 naar C4:C402 de formule =ASELECT(). Noem het bereik C3:C402 gegevens. Vervolgens in de kolom F, kunt u het gemiddelde van de 400 willekeurige getallen (cel F2) bijhouden en gebruiken van de functie aantal.Als om te bepalen de breuken die tussen 0 en 0,25, 0,25 en 0,50, 0,50 en 0,75, en 0,75 en 1. Als u op de toets F9 drukt, wordt de willekeurige getallen worden opnieuw berekend. Melding dat het gemiddelde van de 400 getallen altijd is ongeveer 0,5, en dat ongeveer 25 procent van de resultaten zijn in intervallen van 0,25. Deze resultaten komen overeen met de definitie van een willekeurig getal. Bedenk ook dat de waarden die zijn gegenereerd door ASELECT in verschillende cellen onafhankelijke zijn. Als het willekeurig getal gegenereerd cel C3 is een groot aantal (bijvoorbeeld 0,99) en bijvoorbeeld deze niets te weten over de waarden van de andere willekeurige getallen gegenereerd.

Stel dat de aanvraag voor een agenda wordt bepaald door de volgende aparte stochastische variabele:

Aanvraag

Kans

10.000

0,10

20.000

0,35

40.000

0,3

60.000

0,25

Hoe kan we Excel afspelen af of simuleren, deze vraag voor agenda's vaak hebben? Het is elke mogelijke waarde van de functie ASELECT koppelen aan een mogelijke aanvraag voor agenda's. De volgende toewijzing zorgt ervoor dat een aanvraag van 10.000 wordt 10 procent van de tijd optreden, enzovoort.

Aanvraag

Willekeurig getal toegewezen

10.000

Kleiner dan 0,10

20.000

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

40.000

Groter dan of gelijk is aan 0,45 en minder dan 0,75.

60.000

Groter dan of gelijk is aan 0,75.

Voorbeelden van de simulatie van de aanvraag, kijkt u naar het bestand Discretesim.xlsx, in figuur 60 grondtal 2 van de volgende pagina weergegeven.

Afbeelding van boek
Afbeelding 60-2-simuleren een aparte stochastische variabele

Het belangrijkste voor onze simulatie is een willekeurig getal gebruiken om te starten een opzoeken uit de tabelbereik F2:G5 (ook wel Opzoekengenoemd). Willekeurige getallen groter dan of gelijk is aan 0 en kleiner is dan 0,10 resulteert in een aanvraag van 10.000; willekeurige getallen groter dan of gelijk is aan 0,10 en kleiner is dan 0,45 resulteert in een aanvraag van 20.000; willekeurige getallen groter dan of gelijk aan 0,45 en minder dan 0,75 resulteert in een aanvraag van 40.000; en willekeurige getallen groter dan of gelijk is aan 0,75 resulteert in een aanvraag van 60.000. U 400 willekeurige getallen genereren door het kopiëren van C3 naar C4:C402 de formule ASELECT(). U hoofdidee 400 experimenten of iteraties, na het verzoek van de agenda van het kopiëren van B3 naar B4:B402 de formule VLOOKUP(C3,lookup,2). Deze formule zorgt ervoor dat elk willekeurig getal kleiner is dan 0,10 leidt tot een vraag van 10.000, een willekeurig getal tussen 0,10 en 0,45 leidt tot een vraag 20.000, enzovoort. Gebruik de functie aantal.Als om te bepalen het deel van onze 400 iteraties zodat elke vraag in het cellenbereik F8:F11. Als we op F9 om te berekenen van de willekeurige getallen drukt, wordt de gesimuleerd waarschijnlijkheid dicht bij onze kansen aangenomen dat aanvraag zijn.

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

Afbeelding van boek
Afbeelding 60-3 een normale stochastische variabele simuleren

Stel dat we wilt simuleren 400 experimenten of iteraties, voor een normale stochastische variabele met een gemiddelde van 40.000 en een standaarddeviatie van 10.000. (U kunt deze waarden Typ in cel E1 en E2 en naam van deze cellen gemiddelde en sigma, respectievelijk.) De formule =ASELECT() kopiëren van C4 naar C5:C403 genereert 400 verschillende willekeurige getallen. Kopiëren van B4 naar B5:B403 de formule NORMINV(C4,mean,sigma) genereert 400 verschillende proefabonnement waarden op basis van een normale stochastische variabele met een gemiddelde van 40.000 en een standaarddeviatie van 10.000. Als we op de toets F9 om te berekenen van de willekeurige getallen en drukt, wordt het gemiddelde dicht bij 40.000 en de standaarddeviatie dicht bij 10.000 blijft.

Voor een willekeurig getal x, wordt de formule NORMINV(p,mu,sigma) in wezen de p-percentiel van een normale stochastische variabele met een gemiddelde GM en een standaarddeviatie sigmagegenereerd. Het willekeurig getal 0,77 in cel C4 (Zie afbeelding 60-3) genereert bijvoorbeeld in cel B4 ongeveer het 77th percentiel van een normale stochastische 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 besluitvorming hulpmiddel. Stel dat de aanvraag voor een Valentijnsdag moet voldoen aan de volgende aparte stochastische variabele:

Aanvraag

Kans

10.000

0,10

20.000

0,35

40.000

0,3

60.000

0,25

De wenskaart verkoopt voor $4.00, en de variabele kosten van elke kaart produceren $1,50 is. Resterende kaarten moeten worden verwijderd met een waarde van €0,20 per kaart. Hoeveel kaarten moeten worden afgedrukt?

We simuleren in principe elke mogelijke productiehoeveelheid (10.000, 20.000, 40.000 of 60.000) vaak (bijvoorbeeld 1000 herhalingen). We vervolgens bepalen welke bestelhoeveelheid levert de maximale gemiddelde winst via de iteraties 1000. U vindt de gegevens voor deze sectie in het bestand Valentine.xlsx, in afbeelding 60-4 wordt weergegeven. U kunt de bereiknamen in cellen B1:B11 toewijzen aan cellen C1:C11. Het celbereik G3:H6 krijgt de naam Opzoeken. Onze verkoopprijs en Kostenparameters worden ingevoerd in cellen C4:C6.

Afbeelding van boek
Afbeelding 60-4 Valentijnsdag kaart simulatie

U kunt een proefabonnement productiehoeveelheid (40.000 in dit voorbeeld) in cel C1. Maak vervolgens een willekeurig getal in cel C2 met de formule =ASELECT(). U simuleren aanvraag voor het kaartje in cel C3 met de formule VLOOKUP(rand,lookup,2)zoals eerder is beschreven. (In de formule VERT.zoeken is ASELECT de naam van de cel die zijn toegewezen aan de cel C3, niet de functie ASELECT.)

Het aantal verkochte eenheden is de kleinste van onze productiehoeveelheid en de vraag. Klik in cel C8 u onze opbrengst met de formule berekenen MIN (geproduceerd, vraag) * unit_price. Klik in cel C9 u totale kosten met de formule berekenen geproduceerd * unit_prod_cost.

Als we meer kaarten dan in aanvraag oplevert, wordt het aantal eenheden links is gelijk aan productie min aanvraag; anders worden geen eenheden via naar links. We onze kosten verwijdering in cel C10 met de formule berekenen unit_disp_cost * als (geproduceerd > aanvraag, geproduceerd – vraag, 0). Tot slot in cel C11 berekenen we onze winst als inkomsten – total_var_cost-total_disposing_cost.

Willen we graag een efficiënte manier om drukt u op F9 vaak (bijvoorbeeld 1000) voor elke productiehoeveelheid en tevens onze verwachte winst voor elke hoeveelheid. Deze situatie is een waarin een gegevenstabel met twee variabelen onze al het gemak wereld wordt geleverd. (Zie hoofdstuk 15, "Gevoeligheid analyse met gegevenstabellen," voor meer informatie over gegevenstabellen.) De tabel met gegevens die worden gebruikt in dit voorbeeld wordt weergegeven in figuur 60-5.

Afbeelding van boek
Afbeelding 60 tot en met 5 tweerichtingsvertrouwensrelatie tussen gegevenstabel voor wenskaart simulatie

Voer in het celbereik A16:A1015, de getallen 1 – 1000 (overeenkomt met onze experimenten 1000). Een eenvoudige manier om u te maken van deze waarden is om te beginnen door te voeren 1 in cel A16. Selecteer de cel, klikt u vervolgens op het tabblad Start in de groep bewerken , klikt u op Opvullingen selecteert u de reeks om het dialoogvenster reeks weer te geven. In het dialoogvenster reeks , weergegeven in afbeelding 60-6, voer een waarde stap 1 en een waarde stoppen 1000. Selecteer de optie kolommen in het gebied Reeks In en klik vervolgens op OK. De getallen 1 – 1000 worden ingevoerd in de kolom begin in cel A16.

Afbeelding van boek
Afbeelding 60-6-met het dialoogvenster reeks het proefabonnement getallen 1 tot en met 1000 invullen

Naast vullen we onze mogelijke productiehoeveelheden (10.000, 20.000, 40.000, 60.000) in cellen B15:E15. We winst wilt berekenen voor elk proefabonnement nummer (1 tot en met 1000) en elke productiehoeveelheid. We raadpleegt u de formule voor winst (berekend in cel C11) in de cel in de linkerbovenhoek van onze gegevenstabel (A15) door in te voeren C11 =.

We gaan nu wel altijd Excel in de 1000 herhalingen van de aanvraag voor elke productiehoeveelheid simuleren. Selecteer het gegevensbereik dat tabel (A15:E1014), en klik in de groep hulpmiddelen voor gegevens op het tabblad gegevens op What-If-analyses en selecteer vervolgens gegevenstabel. Als u een gegevenstabel met twee variabelen instelt, kiest u onze productiehoeveelheid (cel C1) als de rij-invoercel en selecteert u een lege cel (we hebben gekozen cel I14) als de kolominvoercel. Wanneer u op OK, simuleert Excel 1000 aanvraag waarden voor elke order-aantal.

Als u wilt weten over waarom dit werkt, kunt u de waarden die door de tabel met gegevens in het bereik C16:C1015 geplaatst. Voor elk van deze cellen, worden gebruikt een waarde van 20.000 in cel C1. In C16, wordt de waarde van de invoercel van 1 in een lege cel en het willekeurig getal in cel die C2 wordt herberekend geplaatst. De bijbehorende winst is vervolgens in cel C16 opgeslagen. Klik in de kolom cel-invoerwaarde van 2 in een lege cel wordt geplaatst en het willekeurig getal in C2 opnieuw wordt herberekend. De bijbehorende winst wordt ingevoegd in cel C17.

Door het kopiëren van cel B13 naar C13:E13 de formule AVERAGE(B16:B1015), berekenen we gemiddelde gesimuleerd winst voor elke productiehoeveelheid. Door het kopiëren van cel B14 naar C14:E14 de formule STDEV(B16:B1015), wordt de standaarddeviatie van onze gesimuleerd winst voor elke bestelhoeveelheid berekenen. Telkens wanneer we drukt u op F9, zijn 1000 herhalingen van de aanvraag gesimuleerd voor elke order-aantal. Produceren 40.000 kaarten altijd oplevert de grootste verwachte winst. Daarom blijkt dat de juiste beslissing produceren 40.000 kaarten is.

Het effect van risico op onze beslissing     Als we geproduceerd 20.000 in plaats van 40.000 kaarten, onze verwachte winst worden ongeveer 22 procent, maar onze risico (gemeten door de standaarddeviatie van de winst) worden bijna 73 procent. Als we zeer averse naar risico zijn, kan 20.000 kaarten produceren daarom zijn de juiste beslissing. Tussen twee haakjes, 10.000 kaarten altijd produceren heeft een standaarddeviatie van 0 kaarten omdat als we 10.000 kaarten oplevert, wordt alle labels zonder eventuele leftovers altijd wordt verkocht.

Opmerking: In deze werkmap is de berekeningsoptie ingesteld op Automatisch behalve voor tabellen. (Gebruik de opdracht berekening in de groep berekening op het tabblad formules.) Deze instelling zorgt ervoor dat de tabel van onze gegevens niet wordt herberekenen tenzij we druk op F9, die is een goed idee omdat een grote gegevenstabel met uw werk vertragen als u dit telkens wanneer u iets in het werkblad typt wordt herberekend. Houd er rekening mee dat in dit voorbeeld, wanneer u op F9 drukt, de gemiddelde winst worden gewijzigd. Dit gebeurt omdat telkens wanneer u op F9 drukt, een andere volgorde van 1000 willekeurige getallen vraag voor elke bestelhoeveelheid wordt gebruikt.

Betrouwbaarheidsinterval voor gemiddelde winst     Een natuurlijke vraag om te vragen in dit geval is, in welke interval zijn we 95 procent ervoor dat de waar gemiddelde winst valt? Dit interval heet het betrouwbaarheidsinterval van 95 procent voor gemiddelde winst. Een percentage van 95-betrouwbaarheidsinterval voor het gemiddelde van de simulatie uitvoer wordt berekend door de volgende formule:

Afbeelding van boek

Klik in cel J11 berekenen u de ondergrens voor het betrouwbaarheidsinterval van 95 procent van de gemiddelde winst wanneer 40.000 agenda's met de formule D13–1.96*D14/SQRT(1000)worden geproduceerd. In cel J12, moet u de bovengrens voor het betrouwbaarheidsinterval van onze 95 procent met de formule D13+1.96*D14/SQRT(1000)berekenen. Deze berekeningen worden weergegeven in afbeelding 60 tot en met 7.

Afbeelding van boek
Afbeelding 60 tot en met 7 95 procent betrouwbaarheidsinterval voor de gemiddelde winst wanneer 40.000 agenda's worden geordend

We zijn 95 procent ervoor dat de gemiddelde winst wanneer 40.000 agenda's worden geordend tussen $56,687 en $62,589.

  1. Een leverancier GMC is van mening dat aanvraag voor 2005 Envoys wordt worden normaal wordt verdeeld met een gemiddelde van 200 en de standaarddeviatie van 30. Zijn kosten van het ontvangen van een Envoy is $25.000 en hij een Envoy verkoopt voor fl 40.000. De helft van alle Envoys die niet zijn verkocht op volledige prijs kan worden verkocht voor fl 30.000. Hij is afgezien van ordening 200, 220, 240, 260, 280 of 300 Envoys. Hoeveel moet hij bestellen?

  2. Een kleine supermarkt probeert te bepalen hoeveel exemplaren van personen tijdschrift ze wekelijks moeten bestellen. Ze denkt dat hun vraag naar personen moet voldoen aan de volgende aparte stochastische variabele:

    Aanvraag

    Kans

    15

    0,10

    20

    0,20

    25

    0,30

    30

    0,25

    35

    0,15

  3. De supermarkt $1,00 betaalt voor elk exemplaar van personen en voor $1,95 wordt verkocht. Elk exemplaar niet verkocht kan worden geretourneerd voor $0,50. Hoeveel exemplaren van personen moet de store volgorde?

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.

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-ondersteuningsagents.

×