Office
Log på

Introduktion til Monte Carlo simulering 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.

I denne artikel er baseret på Microsoft Excel Data Analysis and Business Modeling 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 i en dedikeret tilfældig variabel?

  • Hvordan kan du simulere værdier i en almindelig tilfældig variabel?

  • Hvordan kan en kort virksomhed ud af, hvor mange kort til at producere?

Vi vil gerne præcist estimere sandsynligheden for usikker begivenheder. Hvad er for eksempel sandsynligheden for, at et nyt produkt pengestrømme har en positiv nettonutidsværdien (nutidsværdi)? Hvad er risikoen faktor på vores investering portefølje? Monte Carlo simulering giver os mulighed for at model situationer, som findes usikkerhed og derefter afspille dem på en computer tusindvis af gange.

Bemærk!: Navnet Monte Carlo simulering kommer fra de computer simulering udføres under 1930 og 1940s til at anslå sandsynligheden for, at den kædereaktion, der kræves for et atom under til detonerer fungerer korrekt. De physicists, der er involveret i dette arbejde er stor fans af hasardspil, så de har givet simuleringerne kode navnet Monte Carlo.

I de næste fem kapitler, får du vist eksempler på, hvordan du kan bruge Excel til at udføre Monte Carlo simulering.

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

  • Generelle konstruktion, Proctor og Gamble, Pfizer, kun Myers Squibb og Eli Lilly Brug simulering til at anslå både den gennemsnitlige retur og risikoen faktor af nye produkter. Disse oplysninger bruges på GM, efter den administrerende direktør til at bestemme, hvilke produkter kommer på markedet.

  • GM bruger simulering til aktiviteter som prognoser nettoindtægt for virksomheden, forudsigelser strukturelle og køb omkostninger og fastslå dens følsomhed over for forskellige typer af risikoen (såsom rente ændringer og valutakursfluktuationer).

  • Lilly anvender simulering til at bestemme den optimal plante kapacitet for hver drug.

  • Proctor og Gamble bruges simulering til at modellere og optimalt afdække udenlandsk valuta risikoen.

  • Sears bruger simulering til at finde ud af, hvor mange enheder af hver produktlinje skal være sorteret fra leverandører – for eksempel antallet af par af Dockers benklæder, der skal være sorteret år.

  • Oil og drug virksomheder bruge simulering til værdi "reelle indstillinger", som værdien af muligheden for at udvide, sammentrække eller udsætte et projekt.

  • Finansielle planlæggere bruge Monte Carlo simulering til at finde optimale investeringsstrategier til deres kunders pensionen.

Når du skriver formlen =SLUMP() i en celle, får du et tal, der er lige sandsynlige skal have en hvilken som helst værdi mellem 0 og 1. Derfor skal omkring 25 procent af tiden, du have et tal mindre end eller lig med 0,25; 10 procent af tiden skal vises et tal, der er omkring mindst 0.90 osv. Hvis du vil vise, hvordan funktionen SLUMP virker, skal du se nærmere på filen Randdemo.xlsx, vises i figur 60-1.

Billede af bog
Figur 60-1-demonstration af funktionen SLUMP

Bemærk!: Når du åbner filen Randdemo.xlsx, kan du ikke kan se det samme tilfældige tal, der er vist i figur 60-1. Funktionen SLUMP genberegner altid automatisk de tal, der oprettes, når et regneark er åbnet, eller når nye oplysninger er angivet i regnearket.

Først skal du kopiere fra celle C3 til C4:C402 formel =SLUMP(). Navngiv området C3:C402 Data. Derefter i kolonne F, kan du registrere gennemsnittet af de 400 vilkårlige tal (celle F2) og bruge funktionen TÆL.Hvis til at bestemme de brøker, der er 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å F9, genberegnes vilkårlige tal. Meddelelse om, at gennemsnittet af 400 tallene er altid cirka 0,5, og at omkring 25% af resultaterne er i intervaller af 0,25. Disse resultater er i overensstemmelse med definitionen af et tilfældigt tal. Bemærk også, at værdierne, der genereres af SLUMP i forskellige celler er uafhængig. Eksempelvis hvis tilfældigt tal, der er oprettet i celle C3 er et stort antal (for eksempel 0,99), får at vide noget om værdierne i de andre vilkårlige tal, der er oprettet.

Antag, at behovet for en kalender er underlagt følgende dedikeret tilfældig variabel:

Behov

Sandsynlighed

10.000

0,10

20.000

0,35

40.000

0,3

60.000

0,25

Hvordan kan vi have Excel afspille eller simulere, dette krav om kalendere mange gange? Hemmeligheden er at knytte hver mulig værdi af funktionen SLUMP med en mulig krav om kalendere. Følgende tildeling sikrer, at et behov på 10.000 vil forekomme 10 procent af tiden, og så videre.

Behov

Vilkårlige tal

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

For at vise simulering af behov, kan du se på filen Discretesim.xlsx, vises i figur 60-2 på den næste side.

Billede af bog
Figur 60-2-simulere en dedikeret tilfældig variabel

Nøgle til vores simulering er at bruge et tilfældigt tal for at starte et opslag fra området tabel F2:G5 (kaldet Opslag). Tilfældige numre for større end eller lig med 0 og mindre end 0,10 resulterer i et behov 10.000. tilfældige numre for større end eller lig med 0,10 og mindre end 0,45 resulterer i et behov 20.000; tilfældige numre for større end eller lig med 0,45 og mindre end 0,75 resulterer i et behov 40.000; og vilkårlige tal større end eller lig med 0,75 resulterer i et behov 60.000. Du kan generere 400 vilkårlige tal ved at kopiere fra C3 til C4:C402 formel funktionerne RAND(). Du kan derefter generere 400 forsøg eller gentagelser af kalender behov ved at kopiere fra B3 til B4:B402 formlen VLOOKUP(C3,lookup,2). Denne formel sikrer, at et tilfældigt tal, der er mindre end 0,10 genererer en demand på 10.000 og et tilfældigt tal mellem 0,10 og 0,45 genererer en demand 20.000 og så videre. Brug funktionen TÆL.Hvis til at bestemme brøken af vores 400 gentagelser fremstilling hver behovs i celleområdet F8:F11. Når vi trykker på F9 for at beregne det vilkårlige tal, er simuleret sandsynligheder tæt vores formodet demand sandsynligheder.

Hvis du skriver i en vilkårlig celle i formlen NORMINV(rand(),mu,sigma), opretter du en simuleret værdi på en almindelig tilfældig variabel, der har en middelværdi mu og standardafvigelse sigma. Denne fremgangsmåde er illustreret i filen Normalsim.xlsx, vises i figur 60-3.

Billede af bog
Figur 60-3-simulere en normal tilfældig variabel

Antag, at vi vil simulere 400 forsøg eller gentagelser, til en almindelig tilfældig variabel med et gennemsnit af 40.000 og en standardafvigelse på 10.000. (Du kan skrive disse værdier i celle E1 og E2 og navngive disse celler betyder og sigmahenholdsvis.) Kopiere formlen =SLUMP() fra C4 til C5:C403 genererer 400 forskellige vilkårlige tal. Kopiere fra B4 til B5:B403 formlen NORMINV(C4,mean,sigma) genererer 400 forskellige prøveversion værdier fra en almindelig tilfældig variabel med et gennemsnit af 40.000 og en standardafvigelse på 10.000. Når vi trykke på F9 for at beregne det vilkårlige tal, forbliver middelværdien tæt 40.000 og standardafvigelsen tæt 10.000.

Grundlæggende, til et tilfældigt tal xgenererer formel NORMINV(p,mu,sigma)pn'te fraktil for en normal tilfældig variabel med en middelværdien mu og en standardafvigelse sigma. For eksempel genererer det vilkårlige tal 0,77 i celle C4 (se figur 60-3) i celle B4 cirka den 77th fraktil for en normal tilfældig variabel med et gennemsnit af 40.000 og en standardafvigelse på 10.000.

I dette afsnit, skal se du hvordan nu simulering kan bruges som et beslutningstagning værktøj. Antag, at krav om en Valentinsdag kort er underlagt følgende dedikeret tilfældig variabel:

Behov

Sandsynlighed

10.000

0,10

20.000

0,35

40.000

0,3

60.000

0,25

Kortet sælger til $4.00, og variable Enhedsomkostninger ved at producere hvert kort er $1.50. Overskydende kort skal være solgt til en kostpris af $0,20 per kort. Hvor mange kort skal udskrives?

Grundlæggende, vi simulere hver mulige fremstilling mængde (10.000, 20.000, 40.000 eller 60.000) mange gange (for eksempel, 1000 gentagelser). Vi finde frem til, hvilken rækkefølge antal giver den maksimale gennemsnitlige fortjeneste over 1000 gentagelser. Du kan finde dataene for denne sektion i filen Valentine.xlsx, vises i figur 60-4. Du tildeler celler C1:C11 område navnene i celler B1:B11. Celleområdet G3:H6 tildeles navnet Opslag. Vores salgsprisen og omkostningsparametre er angivet i celler C4:C6.

Billede af bog
Figur 60-4 Valentinsdag kort simulering

Du kan angive et prøveabonnement fremstilling antal (40.000 i dette eksempel) i celle C1. Dernæst skal du oprette et tilfældigt tal i celle C2 med den formel =SLUMP(). Som beskrevet tidligere, kan du simulere krav om kortet i celle C3 med den formel VLOOKUP(rand,lookup,2). (I LOPSLAG-formel er SLUMP på cellenavn, der er tildelt til celle C3, ikke funktionen SLUMP.)

Antallet solgte enheder er den mindste af vores fremstilling antal og behov. I celle C8 skal du beregne vores indtægter med formlen MIN (produceret, behov) * unit_price. I celle C9 skal du beregne samlede kostpris med formlen produceret * unit_prod_cost.

Hvis vi giver flere kort end er i demand, antallet enheder venstre over er lig med fremstilling minus behov; Ellers nogen enheder er blevet tilovers. Vi beregne vores salg omkostninger i celle C10 med formlen unit_disp_cost * Hvis (produceret > behov, produceret – efterspørgsel, 0). I celle C11, skal vi til sidst skal beregne vores overskuddet som indtægter – total_var_cost-total_disposing_cost.

Vi vil gerne have en effektiv måde at trykke på F9 for mange gange (for eksempel, 1000) for hver fremstilling mængde og Afstem vores forventede overskud for hver mængde. Denne situation er et hvor tovejsdatatabellen leveres til vores mand. (Se kapitel 15, "Følsomhed analyse med datatabeller," Få mere at vide om datatabeller). Datatabellen, der bruges i dette eksempel vises i figur 60-5.

Billede af bog
Figur 60-5 tovejs-datatabel til kort simulering

Angiv afstand 1 – 1000 (svarende til vores 1000 forsøg) i celleområdet A16:A1015. En nem måde at oprette disse værdier er at starte ved at skrive 1 i celle A16. Markér cellen, og derefter klikke på udfyldning under fanen Startside i gruppen redigering, og markere serien for at få vist dialogboksen serie. I dialogboksen serie skal vises i figur 60-6, Angiv værdien trin 1 og 1000 stoppe værdien. Vælg indstillingen kolonner i området Serie i, og klik derefter på OK. Tallene 1 – 1000 er angivet i kolonnen en begyndende i celle A16.

Billede af bog
Figur 60-6 ved hjælp af dialogboksen serie udfylde de prøveversion tal 1 og 1000

Næste Indtast vi vores mulige fremstilling antal (10.000 20.000 40.000, og 60.000) i celler B15:E15. Vi vil beregne avance for hvert prøveversion tal (1 til 1000) og hver fremstilling antal. Vi henviser til formlen for profit (beregnet i celle C11) i den øverste venstre celle i vores datatabel (A15) ved at angive = C11.

Vi er nu klar til at runde Excel i simulere 1000 gentagelse af krav om hver fremstilling mængde. Vælg tabelområde (A15:E1014), og klik derefter på hvad hvis-analyse i gruppen Dataværktøjer under fanen Data, og vælg derefter datatabel. For at konfigurere tovejsdatatabellen skal du vælge vores fremstilling antal (celle C1) som Inputcelle for række og vælge en tom celle (Vi valgte celle I14) som Inputcelle for kolonne. Når du klikker på OK, simulerer Excel 1000 demand værdier for hver rækkefølge antal.

For at forstå, hvorfor dette virker, skal du overveje de værdier, der er placeret efter datatabellen i celleområdet C16:C1015. For hver af disse celler bruger Excel en værdi på 20.000 i celle C1. I C16, er kolonne inputcelle værdien 1 placeret i en tom celle og det tilfældige tal i celle C2 genberegnes. Den tilsvarende fortjeneste registreres derefter i celle C16. Derefter celle input værdi af 2 er placeret i en tom celle, og det tilfældige tal i C2 genberegnes igen. Den tilsvarende fortjeneste er angivet i celle C17.

Ved at kopiere fra celle B13 C13:E13 formlen AVERAGE(B16:B1015), beregne vi gennemsnitlige simuleret avance for hver fremstilling mængde. Ved at kopiere fra celle B14 C14:E14 formlen STDEV(B16:B1015), standardafvigelsen vi af vores simuleret overskud for hver ordre mængden. Hver gang vi trykker på F9, er 1000 gentagelse af demand simuleret for hver ordre mængden. Producerer 40.000 kort altid giver det største forventede overskud. Derfor, vises det, at producere 40.000 kort er stort beslutning.

Virkningen af risici i forbindelse med vores beslutning     Hvis vi produceret 20.000 i stedet for 40.000 kort, vores forventede overskud udelader cirka 22%, men vores risiko (målt efter standardafvigelsen for avance) udelader næsten 73 procent. Hvis vi er meget skadelige for risiko, kan producerer 20.000 kort derfor højre beslutning. Tilfældigvis, producerer 10.000 kort altid har en standardafvigelse på 0 kort, fordi Hvis vi giver 10.000 kort, men vi vil altid sælge dem uden en hvilken som helst rester alle.

Bemærk!: I denne projektmappe er beregningsindstillingen angivet til Automatisk med undtagelse af tabeller. (Brug kommandoen beregning i gruppen beregning under fanen formler). Denne indstilling sikrer, at vores datatabel ikke genberegnes, medmindre vi Tryk på F9, som er en god ide, fordi en stor datatabel bliver langsommere dit arbejde, hvis genberegnes, hver gang du skriver noget i regnearket. Bemærk, at i dette eksempel, når du trykker på F9, den gennemsnitlige fortjeneste vil ændre. Dette sker, fordi hver gang du trykker på F9, en anden sekvens af tilfældige numre for 1000 bruges til at skabe behov for hver ordre mængden.

Konfidensintervallet for middelværdien betyder avance     Et neutralt spørgsmål til at spørge i dette tilfælde er, i hvilke interval vi 95 procent for, at den sande middelværdien fortjeneste falder? Dette interval kaldes 95% konfidensintervallet for middelværdien avance. Et 95% konfidensintervallet for middelværdien i en hvilken som helst simulering output beregnes ved hjælp af følgende formel:

Billede af bog

I celle J11 skal beregne du den nedre grænse for et 95% konfidensinterval på middelværdien avance, når 40.000 kalendere er oprettet med den formel D13–1.96*D14/SQRT(1000). I celle J12 skal beregne du den øvre grænse for vores 95% konfidensinterval med formlen D13+1.96*D14/SQRT(1000). Disse beregninger vises i figur 60-7.

Billede af bog
Figur 60-7 95% konfidensintervallet for middelværdien profit når er sorteret på 40.000 kalendere

Vi arbejder 95 procent for, at vores middelværdien profit når 40.000 kalendere er sorteret er mellem $56,687 og $62,589.

  1. En GMC forhandler mener, at krav om 2005 Envoys normalt fordeles med et gennemsnit af 200 og standardafvigelse på 30. Hans omkostning modtage en Envoy er $25.000, og han sælger en Envoy for $40.000. Halvdelen af alle Envoys, der ikke er blevet solgt til fuld pris kan sælges til kr 30.000. Han overvejer at ordning 200, 220, 240, 260, 280 eller 300 Envoys. Hvor mange skal han bestille?

  2. Et lille supermarked forsøger at finde ud af, hvor mange kopier af personer magazine de skal bestille hver uge. De mener, at deres krav om personer er underlagt følgende dedikeret tilfældig variabel:

    Behov

    Sandsynlighed

    15

    0,10

    20

    0,20

    25

    0,30

    30

    0,25

    35

    0,15

  3. Supermarked betaler $1,00 for hver kopi af personer og sælger til $1.95. Hver Usolgt kopi kan returneres til $0,50. Hvor mange kopier af personer skal store rækkefølge?

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.

×