Innføring i Monte Carlo-simulering i Excel

Obs!: Vi ønsker å gi deg det nyeste hjelpeinnholdet så raskt som mulig, på ditt eget språk. Denne siden er oversatt gjennom automatisering og kan inneholde grammatiske feil eller unøyaktigheter. Formålet vårt er at innholdet skal være nyttig for deg. Kan du fortelle oss om informasjonen var nyttig for deg nederst på denne siden? Her er den engelske artikkelen for enkel referanse.

Denne artikkelen er tilpasset fra Microsoft Excel data Analysis and Business Modeling by Wayne L. Winston.

  • Hvem bruker Monte Carlo-simulering?

  • Hva skjer når du skriver inn = tilfeldig () i en celle?

  • Hvordan kan du simulere verdier for en atskilt tilfeldig variabel?

  • Hvordan kan du simulere verdier av en vanlig tilfeldig variabel?

  • Hvordan kan et gratulasjons kort firma finne ut hvor mange kort du kan produsere?

Vi vil gjerne beregne sannsynligheten for ikke-bestemte hendelser nøyaktig. Hva er sannsynligheten for at kontant strømmene i et nytt produkt har en positiv netto nå verdi (NNV)? Hva er risiko faktoren til investerings porteføljen vår? Monte Carlo-simulering gjør det mulig for oss å modelle situasjoner som presenterer, og deretter spille dem av på en data maskin med tusenvis av ganger.

Obs!: Monte Carlo -simuleringen kommer fra data maskin simuleringene som utføres i løpet av 1930s og 1940s for å beregne sannsynligheten for at kjeden som er nødvendig for en Atom Reaction to Bomb, fungerer. Physicists som er involvert i dette arbeidet, var store fans av gambling, slik at de har fått simuleringen kode navn Monte Carlo.

I de neste fem kapitlene vil du se eksempler på hvordan du kan bruke Excel til å utføre Monte Carlo-simuleringer.

Mange selskaper bruker Monte Carlo-simulering som en viktig del av beslutnings prosessen. Her er noen eksempler.

  • Generelt Motors, Proctor og gamble, Pfizer, Bristol-Myers Squibb og Eli Lilly bruk simulering til å beregne både gjennomsnittlig retur og risiko faktor for nye produkter. På GM brukes denne informasjonen av ADMINISTREREnde direktør til å finne ut hvilke produkter som leveres til markedet.

  • GM bruker simulering for aktiviteter som for eksempel å beregne netto inntekt for bedriften, forutsi struktur-og kjøps kostnader og å finne susceptibility til forskjellige typer risikoer (for eksempel endring av rente satser og valuta kurs svingninger).

  • Lilly bruker simulering til å bestemme den optimale plante kapasiteten for hvert legemiddel.

  • Proctor og gamble bruker simulering til modell, og sikring av ekstern Exchange-risiko er optimal.

  • Sears bruker simulering til å bestemme hvor mange enheter av hver produkt linje som skal bestilles fra leverandører, for eksempel antall parere av bukse som skal bestilles i år.

  • Olje-og legemiddel selskaper bruker simulering til verdi «virkelige alternativer, for eksempel verdien av et alternativ for å utvide, kontrakt eller utsette et prosjekt.

  • Med Finans planleggere kan du bruke Monte Carlo-simulering til å bestemme optimale investerings strategier for deres kunders pensjon.

Når du skriver inn formelen = rand () i en celle, får du et tall som er svært sannsynlig at du antar en hvilken som helst verdi mellom 0 og 1. Derfor skal du få et tall som er mindre enn eller lik 0,25, rundt 25 prosent av tiden. rundt 10 prosent av tiden du bør få et nummer som er minst 0,90, og så videre. Hvis du vil vise hvordan funksjonen tilfeldig fungerer, kan du ta en titt på filen Randdemo. xlsx, som vises i figur 60-1.

Bilde av bok

Obs!:  Når du åpner filen Randdemo. xlsx, vil du ikke se de samme tilfeldige tallene som vises i figur 60-1. Funksjonen TILFELDIG beregner alltid automatisk tallene som genereres når et regne ark åpnes, eller når ny informasjon skrives inn i regne arket.

Først kopierer du fra celle C3 til C4: C402 formelen = rand (). Deretter navngir du området C3: C402- data. I kolonne F kan du deretter spore gjennomsnittet av 400 tilfeldige tall (celle F2) og bruke antall. Hvis-funksjonen til å finne brøkene som er mellom 0 og 0,25, 0,25 og 0,50, 0,50 og 0,75 og 0,75 og 1. Når du trykker F9-tasten, beregnes de tilfeldige tallene på nytt. Legg merke til at gjennomsnittet av 400-numrene alltid er omtrent 0,5, og at rundt 25 prosent av resultatene er i intervaller på 0,25. Disse resultatene Sams varer med definisjonen av et tilfeldig tall. Vær også oppmerksom på at verdiene som genereres av tilfeldig i forskjellige celler, er uavhengige. Hvis for eksempel det tilfeldige tallet som genereres i celle C3, er et stort tall (for eksempel 0,99), forteller det oss ingenting om verdiene til de andre tilfeldige numrene som genereres.

La oss si at etterspørselen etter en kalender er underlagt følgende atskilte tilfeldige variabler:

Behov

Sannsynlighet

10 000

0,10

20 000

0,35

40 000

0,3

60 000

0,25

Hvordan kan vi få Excel til å spille av eller simulere dette behovet for kalendere mange ganger? Trikset er å knytte hver mulige verdi av funksjonen tilfeldig med et mulig behov for kalendere. Følgende tilordning sikrer at et behov på 10 000 vil skje 10 prosent av tiden, og så videre.

Behov

Tilfeldig nummer tilordnet

10 000

Mindre enn 0,10

20 000

Større enn eller lik 0,10, og mindre enn 0,45

40 000

Større enn eller lik 0,45, og mindre enn 0,75

60 000

Større enn eller lik 0,75

Hvis du vil vise simuleringen av etterspørselen, kan du se på filen Discretesim. xlsx, som vist i figur 60-2 på neste side.

Bilde av bok

Nøkkelen til simuleringen er å bruke et tilfeldig nummer til å starte et oppslag fra tabell området F2: G5 (kalt Oppslag). Tilfeldige tall som er større enn eller lik 0 og mindre enn 0,10, vil gi et behov på 10 000. tilfeldige tall som er større enn eller lik 0,10 og mindre enn 0,45, vil gi et behov på 20 000; tilfeldige tall som er større enn eller lik 0,45 og mindre enn 0,75, vil gi et behov på 40 000; og tilfeldige tall som er større enn eller lik 0,75, vil gi et behov på 60 000. Du genererer 400 tilfeldige tall ved å kopiere fra C3 til C4: C402 formelen rand (). Deretter genererer du 400 prøve versjoner eller gjentakelser av kalender behov ved å kopiere fra B3 til B4: B402 formel Finn. rad (C3, oppslag, 2). Denne formelen sikrer at alle tilfeldige tall som er mindre enn 0,10, genererer et behov på 10 000, tilfeldig tall mellom 0,10 og 0,45 genererer et behov på 20 000 og så videre. I celle området F8: F11 kan du bruke antall. Hvis-funksjonen til å finne ut hvor mange 400-gjentakelser som gir hvert behov. Når vi trykker F9 for å beregne tilfeldige tall på nytt, er de simulerte sannsynlighetene i nærheten av våre antatte etterspørsels sannsynlighet.

Hvis du skriver inn i en celle formel NORMINV (rand (), MU, Sigma), genererer du en simulert verdi av en vanlig tilfeldig variabel som har en " middel verdi" og standard avvik Sigma. Denne Fremgangs måten illustreres i filen Normalsim. xlsx, som vises i figur 60-3.

Bilde av bok

La oss anta at vi vil simulere 400 prøve versjoner eller gjentakelser, for en vanlig tilfeldig variabel med en median på 40 000 og et standard avvik på 10 000. (Du kan skrive inn disse verdiene i celle E1 og E2, og gi disse cellene gjennomsnittet og Sigma.) Kopiering av formelen = rand () fra C4 til C5: C403 genererer 400 ulike tilfeldige tall. Kopiering fra B4 til B5: B403 formel NORMINV (C4, median, Sigma) genererer 400 ulike prøve verdier fra en vanlig tilfeldig variabel med en gjennomsnitt på 40 000 og et standard avvik på 10 000. Når vi trykker på F9-tasten for å beregne tilfeldige tall på nytt, forblir gjennomsnittet nær 40 000 og standard avviket nær 10 000.

For et tilfeldig tall x, genererer formelen NORMINV (p; MU; Sigma) den pte persentilen av en vanlig tilfeldig variabel med en median, og en standard avvik Sigma. Det tilfeldige tallet 0,77 i celle C4 (se figur 60-3) genereres for eksempel i celle B4 omtrent 77th-persentilen til en vanlig tilfeldig variabel med en gjennomsnitt av 40 000 og et standard avvik på 10 000.

I denne delen vil du se hvordan Monte Carlo-simulering kan brukes som et beslutnings verktøy. La oss si at etterspørselen etter et Valentins dags kort er underlagt følgende diskrete tilfeldige variabler:

Behov

Sannsynlighet

10 000

0,10

20 000

0,35

40 000

0,3

60 000

0,25

Gratulasjons kortet selger for $4,00, og variabel kostnaden for å produsere hvert kort er $1,50. Leftover-kort må være fjernet fra en kostnad på $0,20 per kort. Hvor mange kort skal skrives ut?

Vi simulerer hvert mulige produksjons antall (10 000, 20 000, 40 000 eller 60 000) mange ganger (for eksempel 1000 gjentakelser). Deretter bestemmer vi hvilket ordre antall som gir maksimal gjennomsnittlig fortjeneste over 1000-gjentakelsene. Du kan finne dataene for denne delen i filen elskede. xlsx, som vises i figur 60-4. Du tilordner område navnene i celle B1: b11 til celler C1: C11. Celle området G3: H6 er tilordnet navne oppslaget. Parameterne for salgs prisen og kostnadene registreres i cellene C4: C6.

Bilde av bok

Du kan angi et produksjons antall for prøve versjoner (40 000 i dette eksemplet) i celle C1. Deretter oppretter du et tilfeldig tall i celle C2 med formelen = rand (). Som tidligere beskrevet kan du simulere behovet for kortet i celle C3 med formel Finn. rad (tilfeldig, oppslag, 2). (I Finn. rad-formelen er tilfeldig celle navnet tilordnet til celle C3, ikke funksjonen tilfeldig.)

Antall solgte enheter er den minste av produksjons antallet og behov. I celle C8 kan du beregne inntektene vår med formelen min (produsert, etterspørsel) * unit_price. I celle C9 beregner du totale produksjons kostnader med formelen som er produsert * unit_prod_cost.

Hvis vi produserer flere kort enn det som er i etterspørsel, er antall enheter igjen over tilsvarer produksjon minus behov. ellers blir ingen enheter igjen. Vi beregner salgs kostnadene i celle C10 med formelen unit_disp_cost * hvis (produced>demand, produsert – etterspørsel, 0). Til slutt beregner vi fortjenesten som inntekt – total_var_cost-total_disposing_costi celle C11.

Vi vil gjerne ha en effektiv måte å trykke F9 mange ganger (for eksempel 1000) for hvert produksjons antall og si opp vår forventede fortjeneste for hvert antall. Denne situasjonen er en to veis data tabell som hører til vår Berg. (Se kapittel 15, "sensitiv analyse med data tabeller", for detaljer om data tabeller.) Data tabellen som brukes i dette eksemplet, vises i figur 60-5.

Bilde av bok

I celle området A16: A1015 skriver du inn tallene 1 – 1000 (korresponderer med våre 1000 prøve versjoner). En enkel måte å opprette disse verdiene på, er å begynne med å skrive inn 1 i celle A16. Merk cellen, og klikk deretter Fylli redigering -gruppen på hjem -fanen, og velg serie for å vise dialog boksen serie . I dialog boksen serie , som vises i figur 60-6, angir du en trinn verdi på 1 og en stopp-verdi på 1000. Velg kolonner -alternativet i seriene i område, og klikk deretter OK. Tallene 1 – 1000 blir lagt inn i kolonne A fra og med celle A16.

Bilde av bok

Deretter angir du de mulige produksjons antallene (10 000, 20 000, 40 000, 60 000) i cellene B15: E15. Vi vil beregne fortjeneste for hvert prøve nummer (1 til 1000) og hvert produksjons antall. Vi refererer til formelen for fortjeneste (beregnet i celle C11) i cellen øverst til venstre i data tabellen (A15) ved å skrive = C11.

Vi er nå klar til å lure på Excel til å simulere 1000 gjentakelser av etterspørsel for hvert produksjons antall. Velg Tabell området (A15: E1014), og klikk deretter på hva hvis-analyse i dataVerktøy-gruppen på data-fanen, og velg deretter data tabell. Hvis du vil konfigurere en to veis data tabell, velger du vårt produksjons antall (celle C1) som rad innSettings celle og merker en tom celle (vi valgte celle I14) som Kol onne innSettings celle. Når du klikker OK, simuleres 1000 behovs verdier for hvert ordre antall.

For å forstå hvorfor dette fungerer, bør du vurdere verdiene som er plassert av data tabellen i celle området C16: C1015. For hver av disse cellene bruker Excel en verdi på 20 000 i celle C1. I C16 plasseres verdien 1 for Kol onne innsettings celle i en tom celle, og det tilfeldige tallet i celle C2 beregnes på nytt. Den tilhørende fortjenesten blir deretter registrert i celle C16. Da blir Kol onne celle inn data verdien 2 plassert i en tom celle, og det tilfeldige tallet i C2 omberegnes på nytt. Den tilsvarende fortjenesten er angitt i celle C17.

Ved å kopiere fra celle B13 til C13: E13 formelen gjennomsnitt (b16: B1015), beregner vi gjennomsnittlig simulert fortjeneste for hvert produksjons antall. Ved å kopiere fra celle B14 til C14: E14 formelen STDAV (b16: B1015), beregner vi standard avviket for den simulerte fortjenesten for hvert ordre antall. Hver gang vi trykker F9, simuleres 1000 gjentakelser av behov for hvert ordre antall. Det å produsere 40 000-kort gir deg alltid den største forventede fortjenesten. Det ser derfor ut til at det å produsere 40 000-kort er den riktige avgjørelsen.

Betydningen av risiko for vår avgjørelse     Hvis vi har produsert 20 000 i stedet for 40 000-kort, faller vår forventede fortjeneste omtrent 22 prosent, men vår risiko (målt etter standard avviket i fortjeneste) faller nesten 73 prosent. Hvis vi derfor er svært aversee for risiko, kan det være lurt å produsere 20 000-kort. Hvis vi produserer 10 000-kort, har det alltid et standard avvik på 0 kort, fordi vi får opp 10 000 kort, vil vi alltid selge alle dem uten leftovers.

Obs!:  I denne arbeids boken er beregnings alternativet satt til Automatisk unntatt for tabeller. (Bruk kommandoen beregning i beregning-gruppen på formler-fanen.) Denne innstillingen sikrer at data tabellen ikke beregnes på nytt med mindre vi trykker F9, noe som er en god idé fordi en stor data tabell vil redusere arbeidet ditt hvis det beregnes på nytt hver gang du skriver noe inn i regne arket. Vær oppmerksom på at i dette eksemplet vil brutto fortjeneste bli endret når du trykker F9. Dette skjer fordi hver gang du trykker F9, brukes en annen sekvens av 1000 tilfeldige tall til å generere behov for hvert ordre antall.

Konfidensintervallet til middel verdi for fortjeneste     Et naturlig spørsmål du kan stille i denne situasjonen, i hvilket intervall er vi 95 prosent sikker på at den sanne middel verdien kommer til å falle? Dette intervallet kalles 95 prosent visshets intervall for middel fortjeneste. Et 95 prosent visshets intervall for gjennomsnittet av alle simulerings resultatet beregnes av følgende formel:

Bilde av bok

I celle J11 beregner du den nedre grensen for 95 prosent visshets intervallet på middel fortjeneste når 40 000-kalendere produseres med formelen D13 – 1.96 * D14/SQRT (1000). I celle J12 beregner du den øvre grensen for 95 prosent visshets intervallet med formelen D13 + 1.96 * D14/SQRT (1000). Disse beregningene vises i figur 60-7.

Bilde av bok

Vi er 95 prosent sikker på at gjennomsnitts fortjenesten når 40 000-kalendere er bestilt mellom $56 687 og $62 589.

  1. En GMC-forhandler mener at etterspørselen etter 2005 envoys vanligvis fordeles med gjennomsnittet av 200 og standard avvik på 30. Hans/hennes mottak av en Envoy er $25 000, og han selger en Envoy for $40 000. Halv parten av alle envoys som ikke selges til full pris, kan selges for $30 000. Han vurderer å bestille 200, 220, 240, 260, 280 eller 300 envoys. Hvor mange bør han/hun bestille?

  2. En liten Supermarket prøver å finne ut hvor mange eksemplarer av personer som skal bestille hver uke. De tror deres behov for personer som er underlagt følgende atskilte tilfeldige variabler:

    Behov

    Sannsynlighet

    15

    0,10

    20

    0,20

    23

    0,30

    innen

    0,25

    35

    0,15

  3. Supermarket betaler $1,00 for hver kopi av personer og selger den til $1,95. Hver solgte kopi kan returneres for $0,50. Hvor mange eksemplarer av personer skal lagre bestillingen?

Trenger du mer hjelp?

Du kan alltid spørre en ekspert i Excel tekniske fellesskap, få støtte i Svar-fellesskapet, eller foreslå en ny funksjon eller forbedring på Excel User Voice.

Bli bedre på Office
Utforsk opplæring
Vær først ute med de nye funksjonene
Bli med i Office Insiders

Var denne informasjonen nyttig?

Takk for tilbakemeldingen!

Takk for tilbakemeldingen! Det høres ut som det kan være lurt å sette deg i kontakt med én av våre Office-kundestøtteagenter.

×