Denne artikkelen ble tilpasset fra Microsoft Excel Data Analysis and Business Modeling av Wayne L. Winston.
Oversikt
- Hvem bruker Monte Carlo-simulering?
- Hva skjer når du skriver =TILFELDIG() i en celle?
- Hvordan kan du simulere verdier av en diskret stokastvariabel?
- Hvordan kan du simulere verdier av en normal stokastisk variabel?
- Hvordan kan et gratulasjonskortfirma bestemme hvor mange kort som skal produseres?
Vi ønsker å estimere sannsynligheten for usikre hendelser nøyaktig. Hva er for eksempel sannsynligheten for at kontantstrømmene fra et nytt produkt vil ha en positiv netto nåverdi (NNV)? Hva er risikofaktoren til vår investeringsportefølje? Monte Carlo-simulering gjør oss i stand til å modellere situasjoner som presenterer usikkerhet og deretter spille dem ut på en datamaskin tusenvis av ganger.
Obs!
Navnet Monte Carlo-simulering kommer fra datasimuleringene som ble utført på 1930- og 1940-tallet for å estimere sannsynligheten for at kjedereaksjonen som trengs for at en atombombe skal detonere, vil fungere vellykket. Fysikerne som var involvert i dette arbeidet var store fans av gambling, så de ga simuleringene kodenavnet Monte Carlo.
I de neste fem kapitlene ser du eksempler på hvordan du kan bruke Excel til å utføre Monte Carlo-simuleringer.
Hvem bruker Monte Carlo-simulering?
Mange selskaper bruker Monte Carlo-simulering som en viktig del av beslutningsprosessen. Her ser du noen eksempler.
- General Motors, Proctor and Gamble, Pfizer, Bristol-Myers Squibb og Eli Lilly bruker simulering for å estimere både gjennomsnittlig avkastning og risikofaktor for nye produkter. Hos GM brukes denne informasjonen av administrerende direktør for å bestemme hvilke produkter som kommer på markedet.
- GM bruker simulering for aktiviteter som å forutsi nettoinntekt for selskapet, forutsi strukturelle og innkjøpskostnader og bestemme dens mottakelighet for ulike typer risiko (som renteendringer og valutakurssvingninger).
- Lilly bruker simulering for å bestemme den optimale anleggskapasiteten for hvert medikament.
- Proctor and Gamble bruker simulering for å modellere og sikre valutarisiko optimalt.
- Sears bruker simulering for å bestemme hvor mange enheter av hver produktlinje som skal bestilles fra leverandører – for eksempel antall par Dockers-bukser som skal bestilles i år.
- Olje- og legemiddelselskaper bruker simulering for å verdsette «reelle opsjoner», for eksempel verdien av en opsjon for å utvide, kontrahere eller utsette et prosjekt.
- Finansielle planleggere bruker Monte Carlo-simulering for å bestemme optimale investeringsstrategier for kundenes pensjonisttilværelse.
Hva skjer når du skriver =TILFELDIG() i en celle?
Når du skriver inn formelen =TILFELDIG() i en celle, får du et tall som har like stor sannsynlighet for å anta alle verdier mellom 0 og 1. Dermed bør du rundt 25 prosent av tiden få et tall mindre enn eller lik 0,25; Rundt 10 prosent av tiden skal du få et tall som er minst 0,90, og så videre. For å demonstrere hvordan TILFELDIG-funksjonen fungerer, ta en titt på fil Randdemo.xlsx, vist i figur 60-1.
Obs!
Når du åpner filen Randdemo.xlsx, vil du ikke se de samme tilfeldige tallene som vises i figur 60-1. TILFELDIG-funksjonen omberegner alltid automatisk tallene som genereres når et regneark åpnes eller når ny informasjon registreres i regnearket.
Først kopierer du formelen =TILFELDIG() fra celle C3 til C4:C402. Deretter gir du området navnet C3:C402 Data. Deretter kan du i kolonne F spore gjennomsnittet av de 400 tilfeldige tallene (celle F2) og bruke ANTALL.HVIS-funksjonen til å bestemme 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 de 400 tallene alltid er omtrent 0,5, og at rundt 25 prosent av resultatene er i intervaller på 0,25. Disse resultatene samsvarer 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 tallene som genereres.
Hvordan kan du simulere verdier av en diskret stokastvariabel?
Anta at behovet etter en kalender styres av følgende diskrete vilkårlige variabel:
| Etterspørsel | Sannsynlighet |
|---|---|
| 10 000 | 0,10 |
| 20 000 | 0,35 |
| 40,000 | 0,3 |
| 60 000 | 0,25 |
Hvordan kan vi få Excel til å spille ut, eller simulere, dette behovet for kalendere mange ganger? Knepet er å knytte hver mulige verdi i TILFELDIG-funksjonen til et mulig behov for kalendere. Følgende tilordning sikrer at et behov på 10 000 oppstår 10 prosent av tiden, og så videre.
| Etterspørsel | Tilordnet tilfeldig nummer |
|---|---|
| 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 |
For å demonstrere simuleringen av etterspørselen, se på fil Discretesim.xlsx, vist i figur 60-2 på neste side.
Nøkkelen til simuleringen vår er å bruke et tilfeldig tall for å starte et oppslag fra tabellområ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 større enn eller lik 0,10 og mindre enn 0,45 vil gi et behov på 20 000; Tilfeldige tall større enn eller lik 0,45 og mindre enn 0,75 vil gi et behov på 40 000; og tilfeldige tall større enn eller lik 0,75 vil gi et krav på 60 000. Du genererer 400 tilfeldige tall ved å kopiere formelen TILFELDIG() fra C3 til C4:C402. Deretter genererer du 400 forsøk eller gjentakelser av kalenderbehov ved å kopiere formelen FINN.RAD(C3,slå.opp,2) fra B3 til B4:B402. Denne formelen sikrer at alle tilfeldige tall som er mindre enn 0,10, genererer et behov på 10 000, alle tilfeldige tall mellom 0,10 og 0,45 genererer et behov på 20 000 og så videre. I celleområdet F8:F11 bruker du ANTALL.HVIS-funksjonen til å bestemme delen av våre 400 gjentakelser som gir hvert behov. Når vi trykker F9 for å beregne de tilfeldige tallene på nytt, er de simulerte sannsynlighetene nær våre antatte etterspørselssannsynligheter.
Hvordan kan du simulere verdier av en normal stokastisk variabel?
Hvis du skriver inn formelen NORMINV(rand(),mu,sigma) i en celle, vil du generere en simulert verdi av en normal tilfeldig variabel som har en gjennomsnittlig mu og et standardavvik sigma. Denne fremgangsmåten er illustrert i fil Normalsim.xlsx, vist i figur 60-3.
La oss anta at vi ønsker å simulere 400 forsøk, eller gjentakelser, for en normal tilfeldig variabel med en gjennomsnitt på 40 000 og et standardavvik på 10 000. (Du kan skrive disse verdiene i celle E1 og E2 og gi disse cellene henholdsvis gjennomsnitt og sigma.) Hvis du kopierer formelen =TILFELDIG() fra C4 til C5:C403, genereres 400 forskjellige tilfeldige tall. Ved å kopiere fra B4 til B5:B403 genererer formelen NORMINV(C4,middelverdi,sigma) 400 ulike prøveverdier fra en vanlig tilfeldig variabel med et gjennomsnitt på 40 000 og et standardavvik på 10 000. Når vi trykker på F9-tasten for å beregne de tilfeldige tallene på nytt, forblir gjennomsnittet nær 40 000 og standardavviket nær 10 000.
I hovedsak, for et tilfeldig tall x, genererer formelen NORMINV(p,mu,sigma) p-persentilen til en normal tilfeldig variabel med en gjennomsnittlig mu og et standardavvikssigma. For eksempel genererer det tilfeldige tallet 0,77 i celle C4 (se figur 60-3) i celle B4 omtrent den 77. persentilen til en normal tilfeldig variabel med et gjennomsnitt på 40 000 og et standardavvik på 10 000.
Hvordan kan et gratulasjonskortfirma bestemme hvor mange kort som skal produseres?
I denne delen vil du se hvordan Monte Carlo-simulering kan brukes som et beslutningsverktøy. La oss anta at etterspørselen etter et valentinskort er underlagt følgende atskilte vilkårlige variabel:
| Etterspørsel | Sannsynlighet |
|---|---|
| 10 000 | 0,10 |
| 20 000 | 0,35 |
| 40,000 | 0,3 |
| 60 000 | 0,25 |
Gratulasjonskortet selges for USD 4,00, og den variable kostnaden for å produsere hvert kort er USD 1,50. Gavekort som er til overs, må kastes til en pris av 0,20 USD per kort. Hvor mange kort bør skrives ut?
Enkelt forklart simulerer vi hver mulige produksjonsmengde (10 000, 20 000, 40 000 eller 60 000) mange ganger (for eksempel 1000 gjentakelser). Deretter bestemmer vi hvilket ordreantall som gir maksimal gjennomsnittlig fortjeneste over de 1000 gjentakelsene. Du finner dataene for denne delen i fil Valentine.xlsx, vist i figur 60-4. Du tilordner områdenavnene i celle B1:B11 til cellene C1:C11. Celleområdet G3:H6 tilordnes navneoppslaget. Parameterne våre for salgspris og kostnad er angitt i celle C4:C6.
Du kan angi et prøveproduksjonsantall (40 000 i dette eksemplet) i celle C1. Opprett deretter et tilfeldig tall i celle C2 med formelen =TILFELDIG(). Som tidligere beskrevet kan du simulere etterspørselen etter kortet i celle C3 med formelen FINN.RAD(rand,lookup,2). (I FINN.RAD-formelen er tilfeldig cellenavnet som er tilordnet celle C3, ikke TILFELDIG-funksjonen.)
Antall solgte enheter er det minste av vår produksjonsmengde og etterspørsel. I celle C8 beregner du omsetningen med formelen MIN(produsert,etterspørsel)*unit_price. I celle C9 beregner du de totale produksjonskostnadene med formelen som produseres*unit_prod_cost.
Hvis vi produserer flere kort enn det som er etterspurt, er antall enheter som er til overs lik produksjon minus etterspørsel; Ellers blir det ingen enheter til overs. Vi beregner avhendingskostnadene i celle C10 med formelen unit_disp_cost*HVIS(produsert>etterspørsel,produsert–etterspørsel,0). Til slutt, i celle C11, beregner vi fortjenesten som omsetning – total_var_cost-total_disposing_cost.
Vi ønsker en effektiv måte å trykke F9 mange ganger (for eksempel 1000) for hvert produksjonsantall og beregne forventet fortjeneste for hvert antall. Dette er en situasjon der en toveis datatabell kommer til unnsetning. (Se kapittel 15, «Følsomhetsanalyse med datatabeller» for mer informasjon om datatabeller.) Datatabellen som brukes i dette eksemplet er vist i figur 60-5.
I celleområdet A16:A1015 skriver du inn tallene 1–1000 (som tilsvarer våre 1000 forsøk). En enkel måte å opprette disse verdiene på, er å starte med å skrive inn 1 i celle A16. Merk cellen, klikk Fyll i Redigering-gruppen på Hjem-fanen, og velg Serie for å vise dialogboksen Serie. I dialogboksen Serie , vist i figur 60-6, angir du en trinnverdi på 1 og en stoppverdi på 1000. Velg Kolonner-alternativet i Serie inn-området, og klikk deretter OK. Tallene 1–1000 angis i kolonne A og starter i celle A16.
Deretter fører vi inn mulige produksjonsmengder (10 000, 20 000, 40 000, 60 000) i cellene B15:E15. Vi ønsker å beregne fortjeneste for hvert prøvenummer (1 til 1000) og hvert produksjonsantall. Vi refererer til formelen for fortjeneste (beregnet i celle C11) i cellen øverst til venstre i datatabellen (A15) ved å skrive inn =C11.
Vi er nå klare til å lure Excel til å simulere 1000 gjentakelser av etterspørselen for hvert produksjonsantall. Merk tabellområdet (A15:E1014), klikk Hva-skjer-hvis-analyse i Data-kategorien i Dataverktøy-gruppen, og velg deretter Datatabell. Hvis du vil sette opp en toveis datatabell, velger du produksjonsantallet (celle C1) som radinndatacelle, og velger en tom celle (vi valgte celle I14) som inndatacelle for kolonne. Når du har klikket OK, simulerer Excel 1000 behovsverdier for hvert ordreantall.
For å forstå hvorfor dette fungerer, bør du vurdere verdiene som er plassert av datatabellen i celleområdet C16:C1015. Excel vil bruke en verdi på 20 000 i celle C1 for hver av disse cellene. I C16 plasseres verdien for inndatacellen 1 i en tom celle, og det tilfeldige tallet i celle C2 beregnes på nytt. Den tilsvarende fortjenesten blir deretter registrert i celle C16. Deretter plasseres kolonnecellens inndataverdi på 2 i en tom celle, og det tilfeldige tallet i C2 beregnes på nytt. Den tilsvarende overskuddet føres i celle C17.
Ved å kopiere formelen GJENNOMSNITT(B16:B1015) fra celle B13 til C13:E13, beregner vi gjennomsnittlig simulert fortjeneste for hver produksjonsmengde. Ved å kopiere formelen STDAV(B16:B1015) fra celle B14 til C14:E14, beregner vi standardavviket for den simulerte fortjenesten for hvert ordreantall. Hver gang vi trykker F9, simuleres 1000 gjentakelser av behov for hvert ordreantall. Å produsere 40 000 kort gir alltid størst forventet fortjeneste. Derfor ser det ut til at det å produsere 40 000 kort er den riktige avgjørelsen.
Virkningen av risiko på vår beslutning Hvis vi produserte 20 000 i stedet for 40 000 kort, faller vår forventede fortjeneste omtrent 22 prosent, men risikoen vår (målt ved standardavviket for fortjeneste) faller nesten 73 prosent. Derfor, hvis vi er ekstremt uvillige til risiko, kan det være den riktige avgjørelsen å produsere 20 000 kort. Å produsere 10 000 kort har forresten alltid et standardavvik på 0 kort, for hvis vi produserer 10 000 kort, vil vi alltid selge dem alle uten rester.
Obs!
I denne arbeidsboken er beregningsalternativet satt til Automatisk unntatt for tabeller. (Bruk Beregning-kommandoen i Beregning-gruppen på Formler-fanen.) Denne innstillingen sikrer at datatabellen ikke beregnes på nytt med mindre vi trykker F9, noe som er lurt fordi en stor datatabell vil redusere hastigheten på arbeidet hvis den blir regnet om hver gang du skriver noe i regnearket. Legg merke til at i dette eksemplet endres den gjennomsnittlige fortjenesten når du trykker F9. Dette skjer fordi hver gang du trykker F9, brukes en forskjellig sekvens på 1000 tilfeldige tall til å generere behov for hvert ordreantall.
Konfidensintervallet for gjennomsnittlig fortjeneste Et naturlig spørsmål å stille i denne situasjonen er, i hvilket intervall er vi 95 prosent sikre på at den sanne gjennomsnittlige fortjenesten vil falle? Dette intervallet kalles 95 prosent konfidensintervallet for gjennomsnittlig fortjeneste. Et 95 prosent konfidensintervall for gjennomsnittet av eventuelle simuleringsutdata beregnes av følgende formel:
I celle J11 beregner du den nedre grensen for 95 prosent konfidensintervallet av gjennomsnittlig fortjeneste når 40 000 kalendere produseres med formelen D13–1,96*D14/ROT(1000). I celle J12 beregner du den øvre grensen for 95 prosent konfidensintervallet med formelen D13+1,96*D14/ROT(1000). Disse beregningene er vist i figur 60-7.
Vi er 95 prosent sikre på at gjennomsnittlig fortjeneste når 40 000 kalendere er bestilt, er mellom kr 56 687 og kr 62 589.
Problemer
En GMC-forhandler mener at etterspørselen etter 2005 Envoys normalt vil bli fordelt med et gjennomsnitt på 200 og standardavvik på 30. Hans kostnad for å motta en utsending er 25 000 dollar, og han selger en utsending for 40 000 dollar. Halvparten av alle utsendinger som ikke selges til full pris kan selges for 30 000 dollar. Han vurderer å beordre 200, 220, 240, 260, 280 eller 300 utsendinger. Hvor mange skal han bestille?
Et lite supermarked prøver å finne ut hvor mange eksemplarer av magasinet People de skal bestille hver uke. De mener at deres etterspørsel etter People er styrt av følgende diskrete tilfeldige variabel:
Etterspørsel Sannsynlighet 15 0,10 20 0.20 25 0.30 30 0,25 35 0,15 Supermarkedet betaler $1.00 for hvert eksemplar av People og selger det for $1.95. Hvert usolgte eksemplar kan returneres for USD 0,50. Hvor mange eksemplarer av People skal butikken bestille?
Trenger du mer hjelp?
Du kan alltid spørre en ekspert i Excels tekniske fellesskap eller få støtte i fellesskap.