Introduktion till Monte Carlo simulering i Excel

Obs!:  Vi vill kunna erbjuda dig bästa möjliga supportinnehåll så fort som möjligt och på ditt språk. Den här sidan har översatts med hjälp av automatiserad översättning och kan innehålla grammatiska fel eller andra felaktigheter. Vår avsikt är att den här informationen ska vara användbar för dig. Vill du berätta för oss om informationen är till hjälp längst ned på sidan? Här är artikeln på engelska som referens.

I den här artikeln bygger på Microsoft Excel Data Analysis and Business Modeling av Wayne L. Winston.

  • Vem använder Monte Carlo simulering?

  • Vad händer när du skriver =RAND() i en cell?

  • Hur kan du simulera värdena i en separat slumpvariabel?

  • Hur kan du simulera värdena i en normal slumpvariabel?

  • Hur kan ett hälsningskort företag styr hur många kort att producera?

Vi vill exakt uppskatta troliga osäkra händelser. Vad är till exempel sannolikheten att en ny produkt betalningar har ett positivt nuvärdet (NVLF)? Vad är vår investeringsportfölj riskfaktorn? Monte Carlo simulering gör att vi till modellen situationer som presentera osäkerhet och sedan spela upp dem på en dator tusentals gånger.

Obs!: Namnet Monte Carlo simulering hämtas från dator-simulering utförs under 1930-talet och 1940s att uppskatta sannolikheten kedjereaktion som krävs för ett atom bomb till detonerar skulle fungera. Physicists tas med i den här arbete har stor gillar spel, så att de gav simuleringarna kodnamnet Monte Carlo.

I följande sju kapitel visas exempel på hur du kan använda Excel för att utföra Monte Carlo-simulering.

Många företag använder Monte Carlo simulering som en viktig del av deras beslutsprocess. Här är några exempel.

  • Allmänna motorer, Proctor och lotteri, Pfizer, Bristol Persson Squibb och Eli Lilly Använd simulering för att uppskatta genomsnittlig avkastning och riskfaktorn av nya produkter. Vid modifierade används den här informationen av VD att ta reda på vilka produkter det gäller kommer att marknadsföra.

  • Modifierade används simulering för aktiviteter som prognostisering nettoinkomst för företaget, förutsäga strukturella och inköp kostnader och avgöra dess känslighet för olika typer av risken (till exempel räntesats ändringar och valutakursförändringar).

  • Lilly använder simulering för att fastställa den bästa möjliga växt kapaciteten för respektive läkemedel.

  • Proctor och lotteri används för simulering att utforma och optimalt kurssäkra utländsk valutarisken.

  • SEARS använder simulering för att avgöra hur många enheter av varje produktgrupp bör beställas från leverantörer – till exempel hur många par av Dockers långa som ska ordnas år.

  • & Winery och läkemedel företag använder simulering värdet ”reella alternativ”, till exempel värdet av ett alternativ för att visa, dra samman eller skjuta upp ett projekt.

  • Finansiella planerare använder Monte Carlo simulering för att fastställa optimal investeringsstrategier för sina klienter pensionen.

När du skriver formeln =RAND() i en cell, får du ett tal som är lika sannolika använda ett värde mellan 0 och 1. Därför ska cirka 25 procent av tid du få ett tal mindre än eller lika med 0,25; cirka är 10 procent av den tid som du ska få ett tal som minst 0.90 och så vidare. Ta en titt på filen Randdemo.xlsx, som visas i figur 60-1 om du vill visa hur funktionen SLUMP fungerar.

Bild av bok
Figur 60-1 visar funktionen SLUMP

Obs!: När du öppnar filen Randdemo.xlsx visas inte samma slumptal som visas i figur 60-1. Funktionen RAND beräknas alltid automatiskt tal som genereras när ett kalkylblad öppnas eller när ny information anges i kalkylbladet.

Då måste du först kopiera från cell C3 till C4:C402 formel =RAND(). Du namnger område C3:C402 Data. I kolumn F du sedan spåra medelvärdet för 400 slumptal (cell F2) och funktionen antal.om för att fastställa bråktal som är mellan 0 och 0,25, 0,25 och 0,50, 0,50 och 0,75, och 0,75 och 1. När du trycker på F9 räknas slumpmässiga tal. Meddelande om att 400 talens medel alltid är ungefär 0,5 och att cirka 25 procent av resultaten i intervall om 0,25. Resultaten är konsekventa med definitionen av ett slumptal. Observera också att de värden som genereras av SLUMP i olika celler är oberoende. Om kryssrutan slumptal skapas i cell C3 är ett stort antal (till exempel 0,99), den talar om för oss ingenting om värdena i den andra slumptal som genereras.

Anta att begäran för en kalender som styrs av följande separat slumpvariabel:

Begäran

Sannolikhet

10 000

0,10

20 000

0.35

40 000

0,3

60 000

0,25

Hur kan vi ha Excel spela eller simulera kan den här begäran för kalendrar många gånger? Själva Tricket är att associera varje möjligt värde av funktionen SLUMP med möjliga begäran för kalendrar. Följande tilldelning säkerställer att en begäran på 10 000 uppstå 10 procent av tiden och så vidare.

Begäran

Slumptal som tilldelats

10 000

Mindre än 0,10

20 000

Större än eller lika med 0,10 och mindre än 0,45

40 000

Större än eller lika med 0,45 och mindre än 0,75

60 000

Större än eller lika med 0,75

Om du vill visa begäran simulering, granska filen Discretesim.xlsx, som visas i bild 60-2 på nästa sida.

Bild av bok
Figur 60-2 simulera en separat slumpvariabel

Nyckeln till vår simulering är att använda ett slumptal starta en sökning från tabellområde F2:G5 (med namnet sökning). Slumptal större än eller lika med 0 och mindre än 0,10 ger en begäran på 10 000; slumptal större än eller lika med 0,10 och mindre än 0,45 ger en begäran av 20 000; slumptal större än eller lika med 0,45 och mindre än 0,75 ger en begäran av 40 000; och slumptal större än eller lika med 0,75 ger en begäran av 60 000. Du kan generera 400 slumptal genom att kopiera från C3 till C4:C402 formel RAND(). Du generera sedan 400 försök eller iterationer av kalender begäran genom att kopiera från B3 till B4:B402 formeln VLOOKUP(C3,lookup,2). Den här formeln säkerställer att alla slumptal som är mindre än 0,10 genererar en begäran på 10 000, ett slumptal mellan 0,10 och 0,45 genererar en begäran av 20 000 och så vidare. Använd funktionen antal.om i cellområdet F8:F11 att ta reda på del av våra 400 iterationer framställning varje begäran. När vi trycker på F9 för att beräkna om alternativet slumptal är simulerad troliga nära våra antagna begäran sannolikhet.

Om du skriver i en cell formeln NORMINV(rand(),mu,sigma), genereras en normal slumpvariabel med ett medelvärde MK och standardavvikelsen sigmasimulerad värdet. Den här proceduren illustreras i filen Normalsim.xlsx, som visas i bild 60-3.

Bild av bok
Figur 60-3 simulera en normal slumpvariabel

Låt oss anta att vi vill gärna simulera 400 försök eller iterationer för en normal slumpvariabel med 40 000 medelvärdet och standardavvikelsen för 10 000. (Du kan ange följande värden i cell E1 och E2 och namnge dessa celler skulle innebära och sigma.) Kopiera formeln =RAND() från C4 till C5:C403 genererar 400 olika slumptal. Kopiera från B4 till B5:B403 formeln NORMINV(C4,mean,sigma) genererar 400 olika utvärderingsversionen värden från en normal slumpvariabel med 40 000 medelvärdet och standardavvikelsen för 10 000. När vi tryck på F9 för att beräkna om alternativet slumptal förblir medelvärdet nära 40 000 och standardavvikelsen nära 10 000.

I huvudsak genererar ett slumptal xformeln NORMINV(p,mu,sigma)p: te percentilen av en normal slumpvariabel med ett medelvärde MK och standardavvikelsen sigma. Till exempel genererar slumptal 0,77 i cell C4 (se bild 60-3) i cell B4 ungefär 77th percentilen av en normal slumpvariabel med 40 000 medelvärdet och standardavvikelsen för 10 000.

I det här avsnittet visas hur Monte Carlo simulering kan användas som ett beslutsfattande verktyg. Anta att begäran om ett Alla hjärtans dag-kort styrs av följande separat slumpvariabel:

Begäran

Sannolikhet

10 000

0,10

20 000

0.35

40 000

0,3

60 000

0,25

Hälsningskort säljer för $4.00, och rörlig enhetskostnad för att producera varje kort är $1.50. Överblivna kort måste avsättas med en kostnad på $0,20 per kortet. Hur många kort ska skrivas ut?

I princip simulera vi varje möjligt framställning kvantitet (10 000, 20 000, 40 000 och 60 000) många gånger (till exempel 1000 iterationer). Vi kontrollerar vilken ordning kvantitet ger den maximala genomsnittliga vinsten över 1000 antal iterationer. Du kan hitta data för det här avsnittet i filen Valentine.xlsx, som visas i bild 60-4. Du tilldelar celler C1:C11 områdesnamn i celler B1:B11. Cellområdet G3:H6 tilldelas namn uppslag. Vår försäljningspris och kostnadsparametrar anges i celler C4:C6.

Bild av bok
Bild 60-4 Alla hjärtans dag-kort simulering

Du kan ange en utvärderingsversion framställning kvantitet (40 000 i det här exemplet) i cell C1. Skapa sedan ett slumptal i cell C2 med formel =RAND(). Enligt tidigare beskrivning simulera begäran för kortet i cell C3 med formeln VLOOKUP(rand,lookup,2). (I LETARAD-formel är SLUMP cellnamnet tilldelats cell C3, inte funktionen SLUMP.)

Antal sålda enheter är det mindre av vår framställning kvantitet och begäran. I cell C8, beräkna vår intäkt med formeln MIN (produceras, begäran) * unit_price. I cell C9, beräkna sammanlagda tillverkning kostnad med formeln tillverkats * unit_prod_cost.

Om vi ger fler kort än finns i begäran maximalt antal enheter kvar lika med skarp minus begäran; Annars är inga enheter kvar. Vi beräkna vår tillgång kostnad i cell C10 med formeln unit_disp_cost * om (tillverkats > begäran tillverkats – begäran, 0). Slutligen i cell C11 beräkna vi vår vinst som intäkt – total_var_cost-total_disposing_cost.

Vi vill gärna ett effektivt sätt att trycka på F9 många gånger (till exempel 1000) för varje framställning kvantitet och räkna fram vår förväntade resultat för varje kvantitet. Detta är en där en tvåvägsdatatabell kommer vår självklara valet. (Se kapitel 15, ”känslighet analys med datatabeller”, information om datatabeller.) Datatabell som används i det här exemplet visas i bild 60-5.

Bild av bok
Figur 5-60 tvåvägsdatatabell för Hälsningskort simulering

Ange tal 1 – 1000 (motsvarande vår 1000 försök) i cellområdet A16:A1015. Ett enkelt sätt att skapa dessa värden är att börja genom att ange 1 i cell A16. Markera cellen, och sedan på fliken Start i gruppen Redigering, klickar du på Fyll och markera serien ska visas dialogrutan serie. I dialogrutan serie visas i bild 60-6, anger du värdet steg 1 och stoppa värdet 1000. Markera alternativet kolumner i området Serie i och klicka sedan på OK. Tal 1 – 1000 kommer att registreras i en kolumn en början i cell A16.

Bild av bok
Figur 60-6 med hjälp av dialogrutan serien ska fyllas i utvärderingsversionen talen 1 till och med 1000

Nästa anger vi våra möjliga framställning quantities (10 000, 20 000, 40 000, 60 000) i cellerna B15:E15. Vi vill beräkna vinst för varje utvärderingsversionen nummer (1 till 1000) och varje framställning kvantitet. Vi referera till i formeln för vinst (beräknade i cell C11) i den övre vänstra cellen i vår datatabell (A15) genom att ange = C11.

Vi är nu redo att lura Excel till simulera 1000 iterationer av begäran för varje framställning kvantitet. Markera tabellområde (A15:E1014), och klicka sedan på What om-analyser i gruppen Dataverktyg på fliken Data och välj datatabell. Om du vill konfigurera en tvåvägsdatatabell väljer du vår framställning antal (cell C1) som radens indatacell och markera en tom cell (vi valde cell I14) som kolumnens indatacell. Klicka på OK, simulerar Excel 1000 begäran värden för varje kvantitet.

Om du vill förstå varför det här fungerar, bör du värdena placerats av datatabell i cellområdet C16:C1015. För var och en av dessa celler använder Excel 20 000 värdet i cell C1. I C16 placeras kolumn indatacellen värdet 1 i en tom cell och slumptal i cell C2 ska beräknas. Motsvarande vinst registreras sedan i cell C16. Sedan kolumn indata cellvärdet 2 placeras i en tom cell och slumptal i C2 ska beräknas igen. Motsvarande vinst skrivs in i cell C17.

Genom att kopiera från cell B13 till C13:E13 formeln AVERAGE(B16:B1015)beräkna vi genomsnittliga simulerad vinsten för varje framställning kvantitet. Genom att kopiera från cell B14 till C14:E14 formeln STDEV(B16:B1015)vi för att beräkna standardavvikelsen för vår simulerad vinster för varje kvantitet. Varje gång vi trycker på F9, simulerat 1000 iterationer av begäran för varje kvantitet. Producerar 40 000 kort alltid ger den största förväntade vinsten. Därför visas det att producera 40 000 kort är rätt beslut.

Effekterna av risken på vår beslut     Om vi tillverkats 20 000 i stället för 40 000 kort vår förväntade vinst tappar cirka 22 procent men vår risk (mätt med standardavvikelsen för vinst) tappar nästan 73 procent. Om vi är mycket averse till risken kan producerar 20 000 kort därför rätt beslut. Tillfällig natur, producerar 10 000 kort alltid har en standardavvikelse 0 kort eftersom om vi ger 10 000 kort kan vi alltid säljer samtliga utan någon leftovers.

Obs!: I den här arbetsboken är beräkningsalternativet inställt på Automatisk utom tabeller. (Använd kommandot beräkningen i gruppen beräkning på fliken formler.) Den här inställningen gör att våra datatabell inte beräknas om inte vi trycker på F9, vilket är en bra idé eftersom en datatabell med stora tar lång tid att ditt arbete om den räknas om varje gång du skriver något i kalkylbladet. Observera att i det här exemplet när du trycker på F9, medelvärde vinst ändras. Detta beror på att varje gång du trycker på F9, en annan sekvens av 1000 slumptal används för att generera krav för varje kvantitet.

Konfidensintervallet för det skulle innebära vinst     En fysisk fråga att fråga i det här fallet är i vilka intervall är vi 95% att SANT medelvärde vinst faller? Det här intervallet kallas 95 procent konfidensintervallet för medelvärde vinst. 95 procent konfidensintervallet för medelvärdet av alla simulering utdata beräknas med följande formel:

Bild av bok

I cell J11 beräkna nedre gräns för 95 procent konfidensintervallet på medelvärde vinst när 40 000 kalendrar produceras med formeln D13–1.96*D14/SQRT(1000). I cell J12 beräkna den övre gränsen för vår 95 procent konfidensintervallet med formeln D13+1.96*D14/SQRT(1000). Dessa beräkningar visas i bild 60-7.

Bild av bok
Figur 60 – 7 95 procent konfidensintervallet för medelvärde vinst när 40 000 kalendrar ordnas

Vi är 95% till att vår medelvärde vinst när 40 000 kalendrar ordnas är mellan $56,687 och $62,589.

  1. En GMC återförsäljare anser att begäran för 2005 sändebud normalt ska distribueras med 200 medelvärdet och standardavvikelsen för 30. Hans kostnaden för att ta emot ett Envoy är 25 000 och han säljer en Envoy för 40 000 kr. Hälften av alla sändebud inte säljs till fullständig priset kan säljas för 30 000 $. Han överväger ordning 200, 220, 240, 260, 280 eller 300 sändebud. Hur många bör han beställa?

  2. Ett litet storlager försöker att ta reda på hur många kopior av personer magasin de bör beställa varje vecka. De tror deras begäran för personer styrs av följande separat slumpvariabel:

    Begäran

    Sannolikhet

    15

    0,10

    20

    0,20

    25

    0,30

    30

    0,25

    35

    0,15

  3. Storlager ger 1,00 $ för varje kopia av personer och säljer för $1.95. Varje osålda kopia kan returneras för 0,50 $. Hur många kopior av personer ska butiken ordning?

Behöver du mer hjälp?

Du kan alltid fråga en expert i Excel Tech Community, få support i Answers-communityn eller föreslå en ny funktion eller förbättringar på Excel User Voice.

Utöka dina Office-kunskaper
Utforska utbildning
Få nya funktioner först
Anslut till Office Insiders

Hade du nytta av den här informationen?

Tack för din feedback!

Tack för din feedback! Det låter som att det kan vara bra att koppla dig till en av våra Office-supportrepresentanter.

×