Introduktion til Monte Carlo simulation i Excel

Bemærk!: Vi vil gerne give dig den mest opdaterede hjælp, så hurtigt vi kan, på dit eget sprog. Denne side er oversat ved hjælp af automatisering og kan indeholde grammatiske fejl og unøjagtigheder. Det er vores hensigt, at dette indhold skal være nyttigt for dig. Vil du fortælle os, om oplysningerne var nyttige for dig, nederst på denne side? Her er artiklen på engelsk så du kan sammenligne.

Denne artikel er baseret på Microsoft Excel-data analyse og virksomheds modellering af Wayne L. Winston.

  • Hvem bruger Monte Carlo-simulering?

  • Hvad sker der, når du skriver = SLUMP () i en celle?

  • Hvordan kan du simulere værdier for en diskret vilkårlig variabel?

  • Hvordan kan du simulere værdier for en normal tilfældig variabel?

  • Hvordan kan en lykønskningskort virksomhed afgøre, hvor mange kort du skal producere?

Vi vil gerne vurdere sandsynligheder for ikke-bestemte hændelser nøjagtigt. F. eks. Hvad er sandsynligheden for, at et nyt produkts pengestrømme har en positiv nettonutidsværdi (NUTIDsværdi)? Hvad er risiko faktoren for vores investerings sortiment? Monte Carlo simulation gør det muligt for os at udforme situationer, der viser usikkerhedsmomenter, og derefter afspille dem på en computer tusind gange.

Bemærk!: Monte Carlo -simuleringen kommer fra de computer simulationer, der udføres under 1930s og 1940s for at vurdere sandsynligheden for, at den kæde, der er nødvendig for en Atom-Bomb, der skal Detoner, fungerer korrekt. Den physicists, der er involveret i dette arbejde, var store ventilatorer for hasardspil, så de gav simulererne kodenavnet Monte Carlo.

I de næste fem kapitler vises eksempler på, hvordan du kan bruge Excel til at udføre Monte Carlo-simulationer.

Mange virksomheder bruger Monte Carlo simulation som en vigtig del af deres beslutningsproces. Her er nogle eksempler.

  • Generelle motorer, Proctor og Gamble, Pfizer, Bristol-Myers Squibb og Eli Lilly brug simulation til at beregne både den gennemsnitlige forrentning og risiko faktoren for nye produkter. Disse oplysninger anvendes af den ADMINISTREREnde direktør til at bestemme, hvilke produkter der skal markedsføres.

  • Efterhånden bruger simulering til aktiviteter såsom prognosticering af nettoindtægterne i virksomheden, forudsigelse af strukturelle og Køb-omkostninger og bestemmelse af deres følsomhed over for forskellige former for risiko (f. eks. ændringer i rente satsen og valutakursudsving).

  • Lilly bruger simulering til at bestemme den optimale produktionskapacitet for hvert enkelt stof.

  • Proctor og gamble bruger simulering til at overføre og i optimalt udkurser udenlandsk Exchange-risiko.

  • Sears bruger simulering til at bestemme, hvor mange enheder af hver produktlinje der skal bestilles fra leverandører – f. eks er antallet af par af dockingstationer bukser, der skal bestilles dette år.

  • Olie-og narkotika virksomheder bruger simulering til at angive værdien "Real Options", f. eks værdien af en indstilling for at udvide, aftale eller udsætte et projekt.

  • Økonomi planlæggere bruger Monte Carlo simulation til at fastlægge optimale investerings strategier for deres kunders Pension.

Når du skriver formlen = SLUMP () i en celle, får du et tal, der har samme sandsynlighed for at antage en værdi mellem 0 og 1. Derfor skal du få et tal, der er mindre end eller lig med 0,25, omkring 25 procent af tiden. omkring 10 procent af tiden skal du have et nummer, der er mindst 0,90 osv. Hvis du vil demonstrere, hvordan funktionen SLUMP fungerer, kan du se på filen Randdemo. xlsx, der er vist i figur 60-1.

Billede af bog

Bemærk!:  Når du åbner filen Randdemo. xlsx, kan du ikke se de samme vilkårlige tal, der er vist i figur 60-1. Funktionen SLUMP genberegner altid de tal, der genereres automatisk, når et regneark åbnes, eller når nye oplysninger indtastes i regnearket.

Først skal du kopiere fra celle C3 til C4: C402 formlen = SLUMP (). Derefter skal du navngive området C3: C402- data. I kolonne F kan du spore gennemsnittet af de 400-tilfældige tal (celle F2) og bruge funktionen TÆL til at bestemme brøkerne mellem 0 og 0,25, 0,25 og 0,50, 0,50 og 0,75, og 0,75 og 1. Når du trykker på tasten F9, genberegnes de tilfældige tal. Bemærk, at gennemsnittet af 400-numrene altid er cirka 0,5, og at ca. 25 procent af resultaterne er i intervaller på 0,25. Disse resultater stemmer overens med definitionen af et tilfældigt tal. Bemærk også, at de værdier, der genereres af SLUMP i forskellige celler, er uafhængige. Hvis det tilfældige tal, der genereres i celle C3, er et stort tal (f. eks 0,99), siger det ikke noget om værdierne af de andre tilfældige tal, der oprettes.

ForeStil dig, at et behov for en kalender er underlagt følgende diskrete variabel:

Filadgang

Sandsynlighed

10.000

0,10

20.000

0,35

40.000

0,3

60.000

0,25

Hvordan kan vi få Excel til at blive afspillet eller simulere dette behov for kalendere mange gange? Stikket er at knytte hver mulige værdi af funktionen SLUMP med et muligt behov for kalendere. Følgende tildeling sikrer, at et behov på 10.000 vil ske 10 procent af tiden osv.

Filadgang

Tildeles tilfældigt nummer

10.000

Mindre end 0,10

20.000

Større end eller lig med 0,10 og mindre end 0,45

40.000

Større end eller lig med 0,45 og mindre end 0,75

60.000

Større end eller lig med 0,75

Hvis du vil demonstrere simuleringen af efterspørgslen, skal du se på filen Discretesim. xlsx, der vises i figur 60-2 på den næste side.

Billede af bog

Nøglen til vores simulering er at bruge et tilfældigt tal til at starte et opslag fra tabellens område F2: G5 (kaldet Opslag). Vilkårlige tal, der er større end eller lig med 0 og mindre end 0,10, resulterer i en efterspørgsel på 10.000. tilfældige tal, der er større end eller lig med 0,10, og mindre end 0,45 resulterer i en efterspørgsel på 20.000. tilfældige tal, der er større end eller lig med 0,45, og mindre end 0,75 resulterer i en efterspørgsel på 40.000. og vilkårlige tal, der er større end eller lig med 0,75, resulterer i en efterspørgsel på 60.000. Du genererer 400-tilfældige tal ved at kopiere fra C3 til C4: C402 formel SLUMP (). Du genererer derefter 400-forsøg eller iterationer af kalender behov ved at kopiere fra B3 til B4: B402 formlen LOPSLAG (C3, opslag, 2). Denne formel sikrer, at ethvert tilfældigt tal, der er mindre end 0,10, genererer et krav på 10.000, hvilket vilkårligt tal mellem 0,10 og 0,45 genererer et krav fra 20.000 osv. I celleområdet F8: F11 skal du bruge funktionen TÆL til at bestemme brøken af vores 400-iterationer, der giver hvert behov. Når vi trykker på F9 for at genberegne de tilfældige tal, er de simulerede sandsynligheder tæt på vores forventede efterspørgsels sandsynlighed.

Hvis du skriver i en celle, som formlen norminver (SLUMP (), My, Sigma), får du en simuleret værdi af en normal tilfældig variabel, der har en middelværdi for min og standardafvigelse Sigma. Denne fremgangsmåde er vist i filen Normalsim. xlsx, der er vist i figur 60-3.

Billede af bog

Lad os antage, at vi vil simulere 400-forsøg eller-iterationer for en normal tilfældig variabel med en middelværdi på 40.000 og en standardafvigelse på 10.000. Du kan skrive disse værdier i cellerne E1 og E2 og navngive disse cellers middelværdi og Sigma. Kopiere formlen = SLUMP () fra C4 til C5: C403 genererer 400 forskellige vilkårlige tal. Kopiere fra B4 til B5: B403 formlen NORMINV (C4, middelværdi, Sigma) genererer 400 forskellige prøve værdier fra en normal tilfældig variabel med en middelværdi på 40.000 og en standardafvigelse på 10.000. Når vi trykker på tasten F9 for at genberegne de tilfældige tal, forbliver middelværdien tæt på 40.000 og standardafvigelsen tæt på 10.000.

For et vilkårligt tal xkan formlen NORMINV (p, MU, Sigma) generere den grundlæggende fraktil for en normal tilfældig variabel med en middelværdi og en standardafvigelse Sigma. For eksempel genererer det tilfældige tal 0,77 i celle C4 (se fig. 60-3) i celle B4 cirka 77th fraktil for en normal tilfældig variabel med en middelværdi på 40.000 og en standardafvigelse på 10.000.

I dette afsnit kan du se, hvordan Monte Carlo simulation kan bruges som et beslutningsværktøj. Det antages, at efterspørgslen efter et Valentinsdag kort er underlagt følgende diskrete variabel:

Filadgang

Sandsynlighed

10.000

0,10

20.000

0,35

40.000

0,3

60.000

0,25

Kortet sælger til $4,00, og de variable omkostninger ved produktion af hvert kort er $1,50. Leftover-kort skal sælges til en pris på $0,20 pr. kort. Hvor mange kort skal udskrives?

I de fleste tilfælde simulerer vi hvert muligt produktionsantal (10.000, 20.000, 40.000 eller 60.000) mange gange (f. eks. 1000-gentagelser). Derefter bestemmer vi, hvilken ordremængde der giver den maksimale overskud i 1000-gentagelser. Du kan finde dataene for denne sektion i filen til Valentinsdag. xlsx, der er vist i figur 60-4. Du tildeler områdenavne i cellerne B1: B11 til cellerne C1: C11. Celleområdet G3: H6 tildeles navne Opslag. Vores salgspris og omkostningsparametre indtastes i cellerne C4: C6.

Billede af bog

Du kan angive et prøve produktionsantal (40.000 i dette eksempel) i celle C1. Derefter skal du oprette et tilfældigt tal i celle C2 med formlen = SLUMP (). Som tidligere beskrevet simulerer du behovet for kortet i celle C3 med formlen LOPSLAG (SLUMP, opslag, 2). I formlen LOPSLAG er SLUMP det cellenavn, der er tildelt celle C3, ikke funktionen SLUMP.

Antallet af solgte enheder er det mindste af vores produktionsantal og efterspørgslen. I celle C8 kan du beregne vores indtjening med formlen min (produceret, efterspørgsel) * unit_price. I celle C9 beregner du samlede produktionsomkostninger med den formel, der er produceret * unit_prod_cost.

Hvis vi producerer flere kort, end der er behov for, er antallet af enheder, der er tilbage, over lig med produktionen minus efterspørgslen. ellers er der ingen enheder tilbage. Vi beregner vores salgsomkostninger i celle C10 med formlen unit_disp_cost *, hvis (produced>demand, produceret – efterspørgsel, 0). Til sidst i celle C11 beregne vi vores indtjening som indtjening – total_var_cost-total_disposing_cost.

Vi vil gerne have en effektiv metode til at trykke på F9 mange gange (f. eks 1000) for hvert produktionsantal og afstemme vores forventede fortjeneste for hvert antal. Denne situation er en, hvor en tovejs datatabel kommer til at redde. Se kapitel 15, "følsomheds analyse med data tabeller" for at få mere at vide om data tabeller. Den datatabel, der bruges i dette eksempel, vises i figur 60-5.

Billede af bog

I celleområdet A16: A1015 skal du angive tallene 1-1000 (svarende til vores 1000-prøveversioner). En nem måde at oprette disse værdier på er at starte ved at angive 1 i celle A16. Markér cellen, og klik derefter på Udfyldi gruppen redigering under fanen hjem , og vælg serie for at få vist dialogboksen serie . I dialogboksen serie , der vises i figur 60-6, skal du angive en trinværdi på 1 og en stop værdi på 1000. I området serie i skal du vælge indstillingen kolonner og derefter klikke på OK. Tallene 1 – 1000 indtastes i kolonne A, der starter i celle A16.

Billede af bog

Derefter skal du angive de mulige produktions mængder (10.000, 20.000, 40.000, 60.000) i cellerne B15: E15. Vi vil beregne avance for hvert prøve nummer (1 til og med 1000) og hvert produktionsantal. Vi refererer til formlen for fortjeneste (beregnet i celle C11) i cellen øverst til venstre i vores datatabel (A15) ved at skrive = C11.

Vi er nu klar til at narre Excel til at simulere 1000-gentagelser af behov for hvert produktionsantal. Markér tabelområdet (A15: E1014), og klik derefter i gruppen Dataværktøjer under fanen data, klik på hvad hvis-analyse, og vælg derefter data tabel. Hvis du vil konfigurere en datatabel med to måder, skal du vælge vores produktionsantal (celle C1) som række input cellen og vælge en tom celle (vi vælger celle I14) som kolonne input cellen. Når du har klikket på OK, simulerer Excel 1000-behovs værdier for hvert ordreantal.

For at forstå, hvorfor det fungerer, skal du overveje de værdier, der er placeret af datatabellen i celleområdet C16: C1015. For hver af disse celler vil Excel bruge en værdi på 20.000 i celle C1. I C16 er værdien for inputcellen for kolonnen 1 placeret i en tom celle og det tilfældige tal i celle C2 genberegner. Den tilsvarende avance registreres derefter i celle C16. Derefter anbringes cellens inputværdi for 2 i en tom celle, og det tilfældige tal i C2 genberegnes igen. Den tilsvarende avance er angivet i celle C17.

Ved at kopiere fra celle B13 til C13: E13 formel gennemsnit (B16: B1015)beregner vi gennemsnitlig simuleret overskud for hvert produktionsantal. Ved at kopiere fra celle B14 til C14: E14 formlen StDev (B16: B1015)beregner vi standardafvigelsen for vores simulerede fortjeneste for hvert ordreantal. Hver gang vi trykker på F9, simuleres en efterspørgsel på 1000 for hvert ordreantal. Produktion af 40.000-kort giver altid den største forventede fortjeneste. Det ser derfor ud til, at det er en god beslutning at producere 40.000-kort.

Risikoen for risiko ved beslutningen     Hvis vi har produceret 20.000 i stedet for 40.000-kort, falder vores forventede fortjeneste på omkring 22%, men vores risiko (målt ved standardafvigelsen for fortjeneste) falder på næsten 73 procent. Hvis vi derfor er særdeles averse, kan det være en god beslutning at producere 20.000-kort. Ved en hændelse har fremtiden 10.000-kort en standardafvigelse på 0 kort, da vi fremstiller 10.000-kort, så vi sælger alle dem uden rester.

Bemærk!:  I denne projektmappe er indstillingen beregning angivet til automatisk, undtagen for tabeller. Brug kommandoen beregning i gruppen beregning under fanen formler. Denne indstilling sikrer, at vores datatabel ikke genberegnes, medmindre vi trykker på F9, hvilket er en god ide om, at en stor datatabel kan reducere dit arbejde, hvis den genberegnes, hver gang du skriver noget i regnearket. Bemærk, at den gennemsnitlige fortjeneste ændres, når du trykker på F9. Dette sker, fordi du trykker på F9, hver gang du trykker på F9, bruges en anden rækkefølge på 1000 vilkårlige numre til at generere behov for hvert ordreantal.

Konfidensinterval for middel fortjeneste     Et naturligt spørgsmål, der kan stilles i denne situation, er, i hvilket interval vi 95 procent på, at den gældende gennemsnitlige fortjeneste falder? Dette interval kaldes 95% konfidensinterval for gennemsnitlig fortjeneste. Et sikkerheds interval på 95% for middelværdien for en simulering beregnes af følgende formel:

Billede af bog

I celle J11 beregner du den nedre grænse for 95-procent konfidensinterval i den gennemsnitlige indtjening, når 40.000-kalendere produceres med formlen D13 – 1.96 * D14/KVROD (1000). I celle J12 beregner du den øvre grænse for sikkerheds intervallet på 95 procent med formlen D13 + 1.96 * D14/KVROD (1000). Disse beregninger vises i figur 60-7.

Billede af bog

Vi er 95 procent sikre på, at vores gennemsnitlige overskud, når 40.000-kalendere er bestilt, er mellem $56.687 og $62.589.

  1. En GMC forhandler mener, at efterspørgslen efter 2005-Envoys vil normalt blive fordelt med en middelværdi på 200 og standardafvigelsen på 30. Udgifterne til at modtage en Envoy er $25.000, og han sælger en Envoy til $40.000. Halvdelen af alle de Envoys, der ikke er solgt til fuld pris, kan sælges for $30.000. Han overvejer at bestille 200, 220, 240, 260, 280 eller 300 Envoys. Hvor mange skal vedkommende bestille?

  2. Et lille supermarked forsøger at finde ud af, hvor mange kopier af folk , de bør bestille hver uge. De mener, at deres behov for personer er underlagt følgende diskrete variable:

    Filadgang

    Sandsynlighed

    15

    0,10

    20

    0,20

    1,25

    0,30

    mindst

    0,25

    35

    0,15

  3. Super brugerne betaler $1,00 for hver kopi af mennesker og sælger det til $1,95. Hver ikke-solgt kopi kan returneres for $0,50. Hvor mange kopier af personer skal butiks ordren?

Har du brug for mere hjælp?

Du kan altid spørge en ekspert i Excel Tech Community, få support i Answers-community'et eller foreslå en ny funktion eller forbedring i Excel User Voice.

Udvid dine Office-færdigheder
Gå på opdagelse i kurser
Få nye funktioner først
Bliv Office Insider

Var disse oplysninger nyttige?

Tak for din feedback!

Tak for din feedback! Det lyder, som om det vil kunne hjælpe, hvis du bliver sat i forbindelse med en af vores Office-supportteknikere.

×