Introduktion till Monte Carlo-simulering i Excel

Den här artikeln har anpassats från Microsoft Excel Dataanalys och affärsmodellering av Wayne L. Winston.

Översikt

• Vem använder Monte Carlo-simulering?
• Vad händer när du skriver =SLUMP() i en cell?
• Hur kan du simulera värden på en diskret slumpvariabel?
• Hur kan du simulera värden på en normal slumpvariabel?
• Hur kan ett gratulationskortföretag avgöra hur många kort som ska produceras?

Vi vill göra en exakt uppskattning av sannolikheten för osäkra händelser. Hur sannolikt är till exempel en ny produkts betalningar att ha ett positivt nettonuvärde? Vilken är riskfaktorn i vår placeringsportfölj? Monte Carlo-simulering gör det möjligt för oss att modellera situationer som ger osäkerhet och sedan spela upp dem på en dator tusentals gånger.

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

Vem använder Monte Carlo-simulering?

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

• General Motors, Proctor and Gamble, Pfizer, Bristol-Myers Squibb och Eli Lilly använder simulering för att uppskatta både den genomsnittliga avkastningen och riskfaktorn för nya produkter. På GM används denna information av VD:n för att avgöra vilka produkter som kommer ut på marknaden.
• GM använder simulering för aktiviteter som att prognostisera företagets nettoresultat, förutsäga struktur- och inköpskostnader och fastställa dess känslighet för olika typer av risker (såsom ränteförändringar och valutafluktuationer).
• Lilly använder simulering för att bestämma den optimala växtkapaciteten för varje läkemedel.
• Proctor and Gamble använder simulering för att modellera och på bästa sätt säkra valutarisker.
• Sears använder simulering för att avgöra hur många enheter av varje produktlinje som ska beställas från leverantörer – till exempel antalet par Dockers-byxor som ska beställas i år.
• Olje- och läkemedelsföretag använder simulering för att värdera "verkliga optioner", till exempel värdet av en option att expandera, dra ihop eller skjuta upp ett projekt.
• Finansiella planerare använder Monte Carlo-simulering för att fastställa optimala investeringsstrategier för sina kunders pension.

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

När du skriver formeln =SLUMP() i en cell får du ett tal som har lika stor chans att anta ett värde mellan 0 och 1. Således, cirka 25 procent av tiden, bör du få ett tal som är mindre än eller lika med 0.25; I ungefär 10 procent av fallen bör du få ett tal som är minst 0,90, och så vidare. För att demonstrera hur funktionen SLUMP fungerar, ta en titt på filen Randdemo.xlsx, som visas i figur 60-1.

Kopiera först formeln =SLUMP()) från cell C3 till C4:C402. Döp sedan området till C3:C402 Data. I kolumn F kan du sedan spåra medelvärdet av de 400 slumptalen (cell F2) och använda funktionen ANTAL.OM för att bestämma bråktalen 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 slumptalen om. Observera att medelvärdet för de 400 talen alltid är cirka 0,5 och att cirka 25 procent av resultaten är i intervall om 0,25. Dessa resultat överensstämmer med definitionen av ett slumptal. Observera också att värdena som genereras av RAND i olika celler är oberoende av varandra. Om slumptalet som genereras i cell C3 till exempel är ett stort tal (till exempel 0,99) säger det ingenting om värdena för de andra genererade slumptalen.

Hur kan du simulera värden på en diskret slumpvariabel?

Anta att efterfrågan på en kalender styrs av följande diskreta slumpvariabel:

Efterfrågan	Sannolikhet
10 000	0,10
20 000	0,35
40,000	0,3
60 000	0,25

Hur kan vi få Excel att spela ut, eller simulera, det här kravet på kalendrar många gånger? Tricket är att associera varje möjligt värde för funktionen RAND med ett möjligt behov av kalendrar. Följande tilldelning säkerställer att ett behov på 10 000 kommer att inträffa i 10 procent av fallen, och så vidare.

Efterfrågan	Tilldelat slumptal
10 000	Mindre än 0,10
20 000	Mer än eller lika med 0,10 och mindre än 0,45
40,000	Mer än eller lika med 0,45 och mindre än 0,75
60 000	Större än eller lika med 0,75

För att demonstrera simuleringen av efterfrågan, titta på filen Discretesim.xlsx, som visas i figur 60-2 på nästa sida.

Nyckeln till vår simulering är att använda ett slumptal för att initiera en sökning från tabellområdet F2:G5 ( namngivet uppslag). Slumptal som är större än eller lika med 0 och mindre än 0,10 ger en efterfrågan på 10 000; Slumptal som är större än eller lika med 0,10 och mindre än 0,45 ger en efterfrågan på 20 000; slumptal som är större än eller lika med 0,45 och mindre än 0,75 ger en efterfrågan på 40 000; och slumptal som är större än eller lika med 0,75 ger ett behov på 60 000. Du genererar 400 slumptal genom att kopiera formeln RAND() från C3 till C4:C402. Du genererar sedan 400 försök, eller iterationer, av kalenderefterfrågan genom att kopiera från B3 till B4:B402 formeln LETARAD (C3, uppslag, 2). Den här formeln säkerställer att ett slumptal som är mindre än 0,10 genererar en efterfrågan på 10 000, ett slumptal mellan 0,10 och 0,45 genererar en efterfrågan på 20 000 och så vidare. I cellområdet F8:F11 använder du funktionen ANTAL.OM för att bestämma hur stor del av våra 400 iterationer som ger varje efterfrågan. När vi trycker på F9 för att räkna om slumptalen ligger de simulerade sannolikheterna nära våra antagna behovssannolikheter.

Hur kan du simulera värden på en normal slumpvariabel?

Om du i någon cell skriver formeln NORMINV(rand(),mu,sigma) kommer du att generera ett simulerat värde av en normal slumpvariabel med medelvärdet mu och standardavvikelsen sigma. Denna procedur illustreras i filen Normalsim.xlsx, som visas i figur 60-3.

Anta att vi vill simulera 400 försök, eller iterationer, för en normal slumpvariabel med ett medelvärde på 40 000 och en standardavvikelse på 10 000. (Du kan skriva dessa värden i cell E1 och E2 och ge cellerna namnen medel respektive sigma.) Om formeln =SLUMP() kopieras från C4 till C5:C403 genereras 400 olika slumptal. Om vi kopierar från B4 till B5:B403 genererar formeln NORMINV(C4;medel;sigma) 400 olika testvärden från en normal slumpvariabel med medelvärdet 40 000 och en standardavvikelse på 10 000. När vi trycker på F9 för att räkna om slumptalen förblir medelvärdet nära 40 000 och standardavvikelsen nära 10 000.

För ett slumptal x är det i praktiken formeln NORMINV(p,mu,sigma) som genererar den p:te percentilen av en normal slumpvariabel med medelvärdet mu och standardavvikelsen sigma. Exempel: Slumptalet 0,77 i cell C4 (se figur 60-3) genererar i cell B4 ungefär den 77:e percentilen av en normal slumpvariabel med ett medelvärde på 40 000 och en standardavvikelse på 10 000.

Hur kan ett gratulationskortföretag avgöra hur många kort som ska produceras?

I det här avsnittet får du se hur Monte Carlo-simulering kan användas som ett verktyg för beslutsfattande. Anta att efterfrågan på ett Alla hjärtans dag-kort styrs av följande diskreta slumpmässiga variabel:

Efterfrågan	Sannolikhet
10 000	0,10
20 000	0,35
40,000	0,3
60 000	0,25

Gratulationskortet säljs för $4.00 och den rörliga kostnaden för att producera varje kort är $1.50. Överblivna kort måste kasseras till en kostnad av $0.20 per kort. Hur många kort ska skrivas ut?

I princip simulerar vi varje möjlig produktionskvantitet (10 000, 20 000, 40 000 eller 60 000) många gånger (till exempel 1000 iterationer). Sedan bestämmer vi vilken orderkvantitet som ger den maximala genomsnittliga vinsten under de 1000 iterationerna. Du hittar data för det här avsnittet i filen Valentine.xlsx, som visas i figur 60-4. Du tilldelar områdesnamnen i cellerna B1:B11 till cellerna C1:C11. Cellområdet G3:H6 tilldelas namnet uppslag. Parametrarna för försäljningspris och kostnad anges i cellerna C4:C6.

Du kan ange en provproduktionskvantitet (40 000 i det här exemplet) i cell C1. Skapa sedan ett slumptal i cell C2 med formeln =SLUMP(). Som tidigare beskrivits simulerar du efterfrågan på kortet i cell C3 med formeln LETARAD(slump,letaupp,2). (I LETARAD-formeln är rand cellnamnet som tilldelats cell C3, inte funktionen SLUMP.)

Antalet sålda enheter är det mindre av vår produktionskvantitet och efterfrågan. I cell C8 beräknar du intäkterna med formeln MIN(producerad,efterfråga)*unit_price. I cell C9 beräknar du den totala produktionskostnaden med formeln som produceras*unit_prod_cost.

Om vi producerar fler kort än vad som efterfrågas, är antalet enheter som blir över lika med produktionen minus efterfrågan; Annars blir inga enheter över. Vi beräknar vår avyttringskostnad i cell C10 med formeln unit_disp_cost*OM(producerad>efterfrågan;producerad–efterfrågan;0). Slutligen beräknar vi vinsten i cell C11 som intäkt – total_var_cost–total_disposing_cost.

Vi skulle vilja ha ett effektivt sätt att trycka på F9 flera gånger (till exempel 1000) för varje produktionskvantitet och räkna ihop vår förväntade vinst för varje kvantitet. I den här situationen kommer en tvåvägsdatatabell till undsättning. (Mer information om datatabeller finns i kapitel 15, "Känslighetsanalys med datatabeller".) Datatabellen som används i det här exemplet visas i figur 60-5.

I cellområdet A16:A1015 anger du talen 1–1000 (vilket motsvarar våra 1000 försök). Ett enkelt sätt att skapa dessa värden är att börja med att ange 1 i cell A16. Markera cellen och klicka på Fyll i gruppen Redigering på fliken Start och välj Serier för att visa dialogrutan Serier. I dialogrutan Serier , som visas i figur 60-6, anger du ett stegvärde på 1 och ett stoppvärde på 1000. I området Serie i väljer du alternativet Kolumner och klickar sedan på OK. Talen 1–1000 anges i kolumn A med början i cell A16.

Därefter anger vi våra möjliga produktionskvantiteter (10 000, 20 000, 40 000, 60 000) i cellerna B15:E15. Vi vill beräkna vinsten för varje försöksnummer (1 till 1000) och varje produktionskvantitet. Vi refererar till formeln för vinst (beräknad i cell C11) i cellen längst upp till vänster i datatabellen (A15) genom att ange =C11.

Vi är nu redo att lura Excel att simulera 1 000 iterationer av efterfrågan för varje produktionskvantitet. Markera tabellområdet (A15:E1014) och klicka på konsekvensanalys i gruppen Dataverktyg på fliken Data och välj sedan Datatabell. Om du vill ställa in en tvåvägsdatatabell väljer du vår produktionskvantitet (cell C1) som radindatacell och markerar en tom cell (vi väljer cell I14) som kolumnindatacell. När du har klickat på OK simulerar Excel 1 000 efterfrågevärden för varje orderkvantitet.

För att förstå varför det här fungerar bör du tänka på de värden som datatabellen har placerat i cellområdet C16:C1015. För var och en av de här cellerna använder Excel ett värde på 20 000 i cell C1. I C16 placeras värdet 1 i kolumnens indatacell i en tom cell och slumptalet i cell C2 beräknas om. Motsvarande vinst bokförs sedan i cell C16. Sedan placeras kolumncellens indatavärde 2 i en tom cell och slumptalet i C2 räknas om igen. Motsvarande vinst anges i cell C17.

Genom att kopiera från cell B13 till C13:E13 formeln MEDEL(B16:B1015) beräknar vi den genomsnittliga simulerade vinsten för varje produktionskvantitet. Genom att kopiera formeln STDAV (B16:B1015) från cell B14 till C14:E14 beräknar vi standardavvikelsen för vår simulerade vinst för varje orderkvantitet. Varje gång vi trycker på F9 simuleras 1000 iterationer av efterfrågan för varje orderkvantitet. Att producera 40 000 kort ger alltid den största förväntade vinsten. Därför verkar det som att det är rätt beslut att producera 40 000 kort.

Riskens inverkan på vårt beslut Om vi producerade 20 000 istället för 40 000 kort sjunker vår förväntade vinst med cirka 22 procent, men vår risk (mätt som standardavvikelsen för vinst) sjunker med nästan 73 procent. Därför, om vi är extremt ovilliga att riskera, kan det vara rätt beslut att producera 20 000 kort. Att producera 10 000 kort har för övrigt alltid en standardavvikelse på 0 kort, för om vi producerar 10 000 kort kommer vi alltid att sälja dem alla utan några rester.

Konfidensintervall för medelvinst En naturlig fråga att ställa sig i denna situation är, i vilket intervall är vi 95 procent säkra på att den verkliga medelvinsten kommer att falla? Detta intervall kallas 95-procents konfidensintervall för genomsnittlig vinst. Ett konfidensintervall på 95 procent för medelvärdet av alla simuleringsresultat beräknas med följande formel:

I cell J11 beräknar du den undre gränsen för 95 procents konfidensintervall på medelvinsten när 40 000 kalendrar produceras med formeln D13–1,96*D14/SQRT(1000). I cell J12 beräknar du den övre gränsen för vårt 95-procentiga konfidensintervall med formeln D13+1,96*D14/SQRT(1000). Dessa beräkningar visas i figur 60-7.

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

Problem

1. En GMC-återförsäljare tror att efterfrågan på 2005 Envoys kommer att fördelas normalt med ett medelvärde på 200 och en standardavvikelse på 30. Hans kostnad för att ta emot ett sändebud är 25 000 dollar, och han säljer ett sändebud för 40 000 dollar. Hälften av alla sändebud som inte säljs till fullt pris kan säljas för 30 000 dollar. Han överväger att beordra 200, 220, 240, 260, 280 eller 300 sändebud. Hur många ska han beställa?

2. En liten stormarknad försöker bestämma hur många exemplar av tidningen People de ska beställa varje vecka. De tror att deras efterfrågan på People styrs av följande diskreta slumpmässiga variabel:

   Efterfrågan	Sannolikhet
   15	0,10
   20	0.20
   25	0.30
   30	0,25
   35	0,15

3. Stormarknaden betalar $ 1,00 för varje kopia av People och säljer den för $ 1,95. Varje osålt exemplar kan returneras för 0,50 USD. Hur många exemplar av People ska butiken beställa?

Behöver du mer hjälp?

Du kan alltid fråga en expert i Excel Tech Community eller få support i Communities.