Introduktion till Monte Carlo-simulering i Excel (2024)

I det här avsnittet kommer du att se hur Monte Carlo simulering kan användas som ett beslu*tsverktyg. Anta att efterfrågan på ett Alla hjärtans dag-kort styrs av följande diskreta slumpvariabel:

Hälsningskortet säljer 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 grund och botten simulerar vi varje möjlig produktionskvantitet (10 000, 20 000, 40 000 eller 60 000) många gånger (till exempel 1 000 iterationer). Sedan bestämmer vi vilken orderantal som ger den maximala genomsnittliga vinsten över de 1000 iterationerna. Du hittar data för det här avsnittet i filen Valentine.xlsx, som visas i Bild 60-4. Du tilldelar områdesnamnen i cellerna B1:B11 till cellerna C1:C11. Cellområdet G3:H6 tilldelas namnuppslag. Våra parametrar för försäljningspris och kostnad anges i cellerna C4:C6.

Du kan ange ett antal försöksproduktion (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(rand;uppslag;2). (I LETARAD-formeln är slump cellnamnet som tilldelats cell C3, inte funktionen SLUMP.)

Antalet sålda enheter är den mindre av vår produktionsmängd och efterfrågan. I cell C8 beräknar du våra intäkter med formeln MIN(producerad,efterfrågan)*unit_price. I cell C9 beräknar du den totala produktionskostnaden med formeln producerad*unit_prod_cost.

Om vi producerar fler kort än vad som efterfrågas är antalet enheter kvar lika med produktionen minus efterfrågan. annars finns inga enheter kvar. Vi beräknar vår avfallshanteringskostnad i cell C10 med formeln unit_disp_cost*OM(producerad>efterfrågan,producerad–efterfrågan,0). slu*tligen, i cell C11, beräknar vi vår vinst som intäkter – total_var_cost-total_disposing_cost.

Vi vill ha ett effektivt sätt att trycka på F9 många gånger (till exempel 1000) för varje produktionsantal och räkna vår förväntade vinst för varje kvantitet. Denna situation är en där en tvåvägsdatatabell kommer till vår räddning. (Mer information om datatabeller finns i Kapitel 15, "Känslighetsanalys med datatabeller".) Datatabellen som används i det här exemplet visas i Bild 60–5.

I cellområdet A16:A1015 anger du siffrorna 1–1 000 (motsvarande 1 000 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 sedan på Fyllning på fliken Start i gruppen Redigering och välj Serie för att visa dialogrutan Serie. I dialogrutan Serie , som visas i Bild 60–6, anger du stegvärdet 1 och stoppvärdet 1 000. I området Serie i väljer du alternativet Kolumner och klickar sedan på OK. Talen 1–1 000 anges i kolumn A med början i cell A16.

Därefter anger vi våra möjliga produktionsmängder (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 1 000) och varje produktionsantal. Vi refererar till formeln för vinst (beräknad 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 att simulera 1 000 iterationer av efterfrågan för varje produktionsantal. Markera tabellområdet (A15:E1014) och klicka sedan på Konsekvensanalys i gruppen Dataverktyg på fliken Data och välj sedan Datatabell. Om du vill skapa en tvåvägsdatatabell väljer du produktionsantalet (cell C1) som radindatacell och väljer en tom cell (vi valde cell I14) som kolumnindatacell. När du har klickat på OK simulerar Excel 1 000 efterfrågevärden för varje orderantal.

Ta hänsyn till värdena i datatabellen i cellområdet C16:C1015 för att förstå varför det fungerar. För var och en av dessa celler använder Excel värdet 20 000 i cell C1. I C16 placeras kolumnindatacellvärdet 1 i en tom cell och slumptalet i cell C2 beräknas om. Motsvarande vinst registreras sedan i cell C16. Sedan placeras kolumncellens indatavärde 2 i en tom cell och slumptalet i C2 beräknas om. Motsvarande vinst anges i cell C17.

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

Riskens inverkan på vårt beslu*t Om vi producerade 20 000 i stället för 40 000 kort minskade vår förväntade vinst cirka 22 procent, men vår risk (mätt med standardavvikelsen för vinst) sjönk nästan 73 procent. Därför, om vi är extremt motvilliga till risk, kan det vara rätt beslu*t att producera 20 000 kort. För övrigt producerar 10.000 kort alltid har en standardavvikelse på 0 kort eftersom om vi producerar 10.000 kort, kommer vi alltid att sälja alla utan några rester.

Obs!: I den här arbetsboken är alternativet Beräkning inställt på Automatisk utom tabeller. (Använd kommandot Beräkning i gruppen Beräkning på fliken Formler.) Den här inställningen säkerställer att vår datatabell inte beräknas om om vi inte trycker på F9, vilket är en bra idé eftersom en stor datatabell gör ditt arbete långsammare om den beräknas om varje gång du skriver något i kalkylbladet. Observera att när du trycker på F9 i det här exemplet ändras medelvärdets vinst. Detta inträffar eftersom varje gång du trycker på F9 används en annan sekvens med 1 000 slumptal för att generera krav för varje orderantal.

Konfidensintervall för genomsnittlig vinst En naturlig fråga att ställa i denna situation är, i vilket intervall är vi 95 procent säkra på att den verkliga genomsnittliga vinsten kommer att falla? Det här intervallet kallas konfidensintervallet 95 procent för medelvärdesvinst. Ett konfidensintervall på 95 procent för medelvärdet av simuleringsresultat beräknas med följande formel:

I cell J11 beräknar du den undre gränsen för konfidensintervallet på 95 procent för genomsnittlig vinst när 40 000 kalendrar skapas med formeln D13–1,96*D14/SQRT(1000). I cell J12 beräknar du den övre gränsen för konfidensintervallet på 95 procent med formeln D13+1,96*D14/SQRT(1000). Beräkningarna visas i Bild 60–7.

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