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.

Den här artikeln har anpassats från Microsoft Excel data Analysis and Business Modeling av Wayne L. Winston.

  • Vem använder Monte Carlo-simulering?

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

  • Hur kan jag simulera värden för en diskret slump variabel?

  • Hur kan man simulera värden i en normal slump variabel?

  • Hur kan ett Gratulations kort få reda på hur många kort som ska framställas?

Vi skulle vilja uppskatta sannolikheten för osäkra händelser. Vad är sannolikheten för att en ny produkts kassa flöden får ett positivt netto värde (NETNUVÄRDE)? Vad är risk faktorn för vår investerings portfölj? Monte Carlo simulering gör att vi kan modellera situationer som visar osäkerhet och sedan spela upp dem på en dator tusentals gånger.

Obs!:  Namn Monte Carlo simulering kommer från dator simuleringar som utförs under 1930s och 1940s för att uppskatta sannolikheten att den kedje reaktion som behövs för att en atom tidsbomb ska fungera. Det physicists som är involverat i detta arbete blev stora fläktar med spel, så de gav simuleringen ett kod namn Monte Carlo.

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

Många företag använder Monte Carlo-simulering som en viktig del i sin besluts process. Här är några exempel.

  • Allmänna motorer, Proctor och Gamble, Pfizer, Bristol-Myers Squibb och Eli Lilly använda simulering för att uppskatta både genomsnittlig avkastning och risk faktor för nya produkter. På GM används denna information av VD för att fastställa vilka produkter som kommer att saluföras.

  • I GM används simulering för aktiviteter som att beräkna netto intäkter för företaget, för att förutsäga strukturella och köpa kostnader och fastställa dess känslighet för olika typer av risker (till exempel ränta ändringar och växelkurser).

  • Lilly använder simulering för att bestämma den optimala växt kapaciteten för varje läkemedel.

  • Proctor och Gamble använder simulering för att modell-och optimal säkring av utländsk Exchange-risk.

  • Sears använder simulering för att bestämma hur många enheter av varje produkt som ska beställas från leverantörer, till exempel antalet par med byxor som ska beställas det här året.

  • För olje-och läkemedels företag används simulering för att värdera "riktiga alternativ", till exempel värdet för ett alternativ för att visa, kontrakt eller senarelägga ett projekt.

  • Ekonomiska planeringar använder Monte Carlo-simulering för att fastställa optimala investerings strategier för kundernas upphör Ande.

När du skriver formeln = slump () i en cell får du ett tal som är lika troligt att anta ett värde mellan 0 och 1. Ungefär 25 procent av tiden får du alltså ett tal som är mindre än eller lika med 0,25; ungefär 10 procent av den tid du ska få ett nummer som är minst 0,90, och så vidare. För att demonstrera hur SLUMP-funktionen fungerar kan du ta en titt på filen Randdemo. xlsx, som visas i figur 60-1.

Bild av bok

Obs!:  När du öppnar filen Randdemo. xlsx ser du inte samma slumptal som visas i figur 60-1. Med funktionen slump beräknas automatiskt de tal som skapas när ett kalkyl blad öppnas eller när ny information anges i kalkyl bladet.

Kopiera först från cell C3 till C4: C402 formeln = slump (). Sedan namnger du området C3: C402 data. Sedan kan du i kolumn F spåra medelvärdet av 400 slump tals nummer (cell F2) och använda funktionen antal för att bestämma vilka bråktal som är mellan 0 och 0,25, 0,25 och 0,50, 0,50 och 0,75 samt 0,75 och 1. När du trycker på F9 beräknas slump numren om. Observera att medelvärdet av 400-numren alltid är cirka 0,5 och att cirka 25 procent av resultaten är i intervall om 0,25. De här resultaten stämmer överens med definitionen av ett slumptal. Observera också att värdena som genereras av slump i olika celler är oberoende av varandra. Till exempel, om det slumpmässiga numret som genereras i cell C3 är ett stort tal (till exempel 0,99), säger det ingenting om värdena på de andra slump tals talen som genereras.

Anta att efter frågan för en kalender lyder under följande diskreta slump variabel:

Belastning

Sannolikhet

10 000

0,10

20 000

0,35

40 000

0,3

60 000

0,25

Hur kan jag spela upp, eller simulera, det här behovet för kalendrar många gånger? Det här är att associera varje möjligt värde för funktionen slump med en eventuell efter frågan på kalendrar. Följande uppgift säkerställer att ett krav på 10 000 kommer 10 procent av tiden och så vidare.

Belastning

Slumpmässigt nummer tilldelat

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 demonstrera simuleringen av behovet kan du titta på filen Discretesim. xlsx, som visas i figur 60-2 på nästa sida.

Bild av bok

Nyckeln till vår simulering är att använda ett slumptal för att initiera ett uppslag från tabell området F2: G5 (namngiven sökning). Slumptal som är större än eller lika med 0 och mindre än 0,10 ger ett behov på 10 000; slumpmässiga nummer större än eller lika med 0,10 och mindre än 0,45 ger behovet av 20 000; slumpmässiga nummer större än eller lika med 0,45 och mindre än 0,75 ger behovet av 40 000; och slumpmässiga nummer större än eller lika med 0,75 ger en begäran om 60 000. Du skapar 400 slump tals nummer genom att kopiera från C3 till C4: C402 med formeln slump (). Du kan sedan generera 400-test och-iterationer av efter frågan efter behov genom att kopiera från B3 till B4: B402 formeln LETARAD (C3, LETAUPP, 2). Med den här formeln ser du till att slumptal som är mindre än 0,10 genererar ett behov på 10 000, vilket slumptal mellan 0,10 och 0,45 skapar ett behov av en 20 000 och så vidare. I cell området F8: F11 använder du funktionen antal för att bestämma bråkdelen av våra 400-iterationer för varje efter frågan. När vi trycker på F9 för att beräkna om slump tals talen är de simulerade sannolikheterna nära de förmodade behoven.

Om du skriver i en cell med formeln NORMINV (slump (), Mu, Sigma), skapas ett simulerat värde av en normal slump variabel som har ett medelvärde för My och standard avvikelse Sigma. Den här proceduren illustreras i filen Normalsim. xlsx, som visas i figur 60-3.

Bild av bok

Låt oss anta att vi vill simulera 400-test eller iterationer för en normal slump variabel med ett medelvärde på 40 000 och en standard avvikelse på 10 000. (Du kan skriva de här värdena i cellerna E1 och E2 och ge dessa celler medelvärden respektive Sigma.) Kopierar formeln = slump () från C4 till C5: C403 ger 400 olika slumptal. Kopierar från B4 till B5: B403 formeln NORMINV (C4, medelvärde, Sigma) genererar 400 olika prov värden från en normal slump variabel med ett medelvärde på 40 000 och en standard avvikelse på 10 000. När vi trycker på F9 för att beräkna om slump tals talen förblir medelvärdet nära 40 000 och standard avvikelsen nära 10 000.

För ett slumptal xskapar formeln NORMINV (p, My, Sigma) den ordinarie percentilen av en normal slump variabel med ett medelvärde My och en standard avvikelse Sigma. Slumptal 0,77 i cell C4 (se figur 60-3) skapas till exempel i cell B4 ungefär 77th percentil i en normal slump variabel med medelvärdet 40 000 och standard avvikelsen för 10 000.

I det här avsnittet ser du hur Monte Carlo simulering kan användas som ett besluts verktyg. Anta att behovet på alla hjärtans dag-kort lyder under följande diskreta slump variabel:

Belastning

Sannolikhet

10 000

0,10

20 000

0,35

40 000

0,3

60 000

0,25

Gratulations kortet säljer för $4,00 och den variabla kostnaden för att tillverka varje kort är $1,50. ÖverBlivna kort måste avsättas till en kostnad på $0,20 per kort. Hur många kort ska skrivas ut?

I stort sett simulerar vi varje möjlig produktionskvantitet (10 000, 20 000, 40 000 eller 60 000) många gånger (till exempel 1000 restores). Då bestämmer vi vilken orderkvantitet som ger maximal genomsnittlig vinst för 1000-iterationer. Du hittar informationen i det här avsnittet i filen Valentino. xlsx, som visas i figur 60-4. Du kan tilldela områdes namnen i cell B1: B11 till cellerna C1: C11. Cell området G3: H6 har tilldelats namn uppslaget. Våra pris-och kostnads parametrar anges i cell C4: C6.

Bild av bok

Du kan ange en prov period (40 000 i det här exemplet) i cell C1. Skapa sedan ett slumptal i cell C2 med formeln = slump (). Som du redan har beskrivit simulerar du efter frågan för kortet i cell C3 med formeln LETARAD (slump, LETAUPP, 2). (I LETARAD-formeln är slump det cell namn som tilldelats till cell C3, inte funktionen slump.)

Antalet sålda enheter är den mindre av produktions antalet och efter frågan. I cell C8 beräknar du vår intäkt med formeln min (producerad, efter frågan) * unit_price. I cell C9 beräknar du total produktionskostnad med formeln skapad * unit_prod_cost.

Om vi tillverkar fler kort än vad som är i behov, får antalet enheter kvar över lika med produktion minus efter frågan; annars lämnas inga enheter över. Vi beräknar vår kasse rings kostnad i cell C10 med formeln unit_disp_cost * IF (produced>demand, tillverkas – demand, 0). I cell C11 beräknas nu vinst som intäkt – total_var_cost-total_disposing_cost.

Vi skulle vilja ha ett effektivt sätt att trycka på F9 många gånger (till exempel 1000) för varje produktionskvantitet och ange förväntad vinst för varje kvantitet. Den här situationen är en i vilken en tvåvägs data tabell kommer till vår undsättning. (Se kapitel 15, "känslighets analys med data tabeller" för information om data tabeller.) Data tabellen som används i det här exemplet visas i figur 60-5.

Bild av bok

I cell området A16: A1015 anger du siffrorna 1 – 1000 (motsvarande våra 1000-test). Ett enkelt sätt att skapa dessa värden är att börja med att skriva in 1 i cell A16. Markera cellen och klicka på fyllningpå fliken Start i gruppen redigering och välj serie för att Visa dialog rutan serie . I dialog rutan serie , som visas i bild 60-6, anger du ett steg-värde på 1 och ett stoppfel för 1000. I området serie i väljer du alternativet kolumner och klickar sedan på OK. Talen 1 – 1000 anges i kolumn A från cell A16.

Bild av bok

Nästa steg är att ange antalet produktions mängder (10 000, 20 000, 40 000, 60 000) i cellerna B15: E15. Vi vill beräkna täckning för varje prov nummer (1 till 1000) och varje produktionskvantitet. Vi hänvisar till formeln för vinst (beräknat i cell C11) i den övre vänstra cellen i data tabellen (A15) genom att skriva = C11.

Nu är vi redo att lura Excel att simulera 1000-iterationer av efter frågan för varje produktionskvantitet. Markera tabell området (A15: E1014) och klicka sedan på analys i gruppen data verktyg på fliken data och sedan på data tabell. Om du vill skapa en tvåvägs data tabell väljer du vår produktionskvantitet (cell C1) som Radens indatacell och markerar en tom cell (vi valde cell I14) som Kolumnens indatacell. När du har klickat på OK simuleras 1000-värden för varje orderkvantitet.

För att förstå varför det här fungerar kan du tänka på värdena i data tabellen i cell området C16: C1015. För var och en av de här cellerna används värdet 20 000 i cell C1. I C16 placeras värdet för kolumn inmatning i en tom cell och det slumpmässiga talet i cell C2 beräknas om. Motsvarande vinst registreras sedan i cell C16. Då beräknas värdet för kolumn-cellen i en tom cell och det slumpmässiga numret i C2 igen. Motsvarande vinst anges i cell C17.

Genom att kopiera från cell B13 till C13: E13 medelvärdet för formeln (B16: B1015)beräknas medelvärdet för den simulerade vinsten för varje produktionskvantitet. Genom att kopiera från cell B14 till C14: E14 formeln STDAV (B16: B1015)beräknar vi standard avvikelsen för de simulerade vinsterna för varje orderkvantitet. Varje gång vi trycker på F9 simuleras efter frågan för varje orderkvantitet. När du tillverkar 40 000-kort behålls alltid den största förväntade vinsten. Därför verkar det som att tillverka 40 000 kort är rätt beslut.

Följderna av en risk för vårt beslut     Om vi har genererat 20 000 i stället för 40 000 kort, kommer vår förväntade vinst att minskas till cirka 22 procent men vår risk (enligt standard avvikelsen för vinst) minskar nästan 73 procent. Om vi har ytterst AVERT kan det vara bra att tillverka 20 000 kort. Det är bara att tillverka 10 000 kort med en standard avvikelse på 0 kort eftersom vi producerar 10 000-kort, säljer vi alltid alla utan restprodukter.

Obs!:  I den här arbets boken är alternativet beräkning inställt på Automatisk utom för tabeller. (Använd kommandot beräkning i gruppen beräkning på fliken formler.) Den här inställningen säkerställer att data tabellen inte räknas om såvida vi inte trycker på F9, vilket är en bra idé eftersom en stor data tabell gör att arbetet blir långsammare om den beräknas varje gång du skriver något i kalkyl bladet. I det här exemplet ändras medel vinsten när du trycker på F9. Det beror på att varje gång du trycker på F9 används en annan sekvens med 1000 slump tals nummer för att generera behov för varje orderkvantitet.

Konfidens intervallet för medel vinst     En naturlig fråga som ska ställas i den här situationen är att i det intervall vi är 95 procent är det så att den faktiska medelvärdet kommer att falla? Det här intervallet kallas för 95 procent på medel täckning. Medelvärdet av en simulerings utmatning beräknas med följande formel i 95 procent konfidens intervallet:

Bild av bok

I cell J11 beräknar du den nedre gränsen för täcknings intervall för 95 procent på medel vinst när 40 000-kalendrar produceras med formeln D13 – 1.96 * D14/sqrt (1 000). I cell J12 beräknar du den övre gränsen för vårt konfidens intervall 95 med formeln D13 + 1.96 * D14/sqrt (1000). Dessa beräkningar visas i figur 60-7.

Bild av bok

Vi är 95 procent säkra på att vår genomsnittliga vinst när 40 000-kalendrar beställs är mellan $56 687 och $62 589.

  1. En GMC handlare tror att behovet av 2005 envoys vanligt vis fördelas med medelvärdet 200 och standard avvikelsen för 30. Hans kostnad för att få en Envoy är $25 000 och han säljer en Envoy för $40 000. Hälften av alla envoys som inte såldes till full pris kan säljas för $30 000. Han planerar att beställa 200, 220, 240, 260, 280 eller 300 envoys. Hur många bör han beställa?

  2. En liten övermarknad försöker ta reda på hur många kopior av personer som är på sig varje vecka. De anser att användare lyder under en diskret slump variabel:

    Belastning

    Sannolikhet

    15

    0,10

    20

    0,20

    250

    0,30

    halvtimme

    0,25

    35

    0,15

  3. Supermarknaden betalar $1,00 för varje kopia av personer och säljer den för $1,95. Alla sålda kopior kan returneras för $0,50. Hur många kopior av människor bör butiks beställningen?

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.

×