Innføring i Monte Carlo-simulering i Excel

• Hvem bruker Monte Carlo simulering?

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

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

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

• Hvordan kan et kortselskap bestemme hvor mange kort som skal produseres?

Vi ønsker å beregne sannsynlighetene for usikre hendelser nøyaktig. Hva er for eksempel sannsynligheten for at kontantstrømmene til et nytt produkt vil ha en positiv netto nåverdi (NNV)? Hva er risikofaktoren for investeringsporteføljen vår? Monte Carlo simulering gjør det mulig for oss å modellere situasjoner som presenterer usikkerhet og deretter spille dem ut på en datamaskin tusenvis av ganger.

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 deres beslutningsprosess. Her er noen eksempler.

• General Motors, Proctor og Gamble, Pfizer, Bristol-Myers Squibb og Eli Lilly bruker simulering til å estimere både gjennomsnittlig avkastning og risikofaktoren for nye produkter. Hos bruttofortjeneste brukes denne informasjonen av administrerende direktør til å bestemme hvilke produkter som kommer på markedet.

• GM bruker simulering for aktiviteter som prognoser for nettoinntekt for selskapet, forutsi strukturelle og innkjøpskostnader, og bestemme sin mottakelighet for ulike typer risiko (for eksempel renteendringer og valutakurssvingninger).

• Lilly bruker simulering for å bestemme den optimale plantekapasiteten for hvert stoff.

• Proctor og Gamble bruker simulering til å modellere og optimalt sikre valutarisiko.

• Sears bruker simulering til å 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 til å verdsette «reelle alternativer», for eksempel verdien av en opsjon på å utvide, kontrakt eller utsette et prosjekt.

• Finansplanleggere bruker Monte Carlo simulering for å bestemme optimale investeringsstrategier for kundenes pensjonering.

Når du skriver inn formelen =TILFELDIG() i en celle, får du et tall som sannsynligvis vil anta en verdi mellom 0 og 1. Dermed, rundt 25 prosent av tiden, bør du få et tall som er mindre enn eller lik 0,25; rundt 10 prosent av tiden bør du få et tall som er minst 0,90, og så videre. Hvis du vil demonstrere hvordan RAND-funksjonen fungerer, kan du ta en titt på filen Randdemo.xlsx, som vist i figur 60-1.

Kopier først fra celle C3 til C4:C402 formelen =TILFELDIG(). Deretter navngir du området 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 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.

Anta at etterspørselen etter en kalender styres av følgende diskrete tilfeldige 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 av, eller simulere, denne etterspørselen etter kalendere mange ganger? Trikset er å knytte hver mulige verdi av RAND-funksjonen til en mulig etterspørsel etter kalendere. Følgende oppgave sikrer at et krav på 10 000 skjer 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å filen Discretesim.xlsx, vist i figur 60-2 på neste side.

Nøkkelen til simuleringen vår er å bruke et tilfeldig tall til å starte et oppslag fra tabellområdet F2:G5 (navngitt oppslag). Tilfeldige tall 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 en etterspørsel på 60 000. Du genererer 400 tilfeldige tall ved å kopiere fra C3 til C4:C402 formelen RAND(). Deretter genererer du 400 prøveversjoner, eller gjentakelser, av kalenderbehov ved å kopiere fra B3 til B4:B402 formelen FINN.RAD(C3,oppslag,2). Denne formelen sikrer at et vilkårlig tall som er mindre enn 0,10 genererer en etterspørsel på 10 000, et vilkårlig tall mellom 0,10 og 0,45 genererer en etterspørsel på 20 000 og så videre. I celleområdet F8:F11 bruker du ANTALL.HVIS-funksjonen til å bestemme brøkdelen av våre 400 gjentakelser som gir hver etterspørsel. Når vi trykker F9 for å beregne tilfeldige tall på nytt, er de simulerte sannsynlighetene nær sannsynlighetene for antatt etterspørsel.

Hvis du skriver inn formelen NORMINV(rand(),mu,sigma), vil du generere en simulert verdi for en normal tilfeldig variabel som har et gjennomsnittlig mu - og standardavvik sigma. Denne fremgangsmåten er illustrert i filen 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 et gjennomsnitt på 40 000 og et standardavvik på 10 000. (Du kan skrive inn disse verdiene i cellene E1 og E2, og gi navn til disse cellene henholdsvis middelverdi og sigma.) Kopiering av formelen =TILFELDIG() fra C4 til C5:C403 genererer 400 forskjellige tilfeldige tall. Kopiering fra B4 til B5:B403 formelen NORMINV(C4,mean,sigma) genererer 400 forskjellige prøveverdier fra en normal tilfeldig variabel med et gjennomsnitt på 40 000 og et standardavvik på 10 000. Når vi trykker 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 tepersentil av en normal tilfeldig variabel med en gjennomsnittlig mu og et standardavvik sigma. Det tilfeldige tallet 0,77 i celle C4 (se figur 60-3) genererer for eksempel i celle B4 omtrent den 77. persentilen til en normal tilfeldig variabel med et gjennomsnitt på 40 000 og et standardavvik på 10 000.

I denne delen skal du se hvordan Monte Carlo-simulering kan brukes som et beslutningsverktøy. Anta at etterspørselen etter et Valentinsdag-kort styres av følgende diskrete tilfeldige variabel:

Etterspørsel	Sannsynlighet
10 000	0,10
20 000	0,35
40,000	0,3
60 000	0,25

Gratulasjonskortet selger for USD 4,00, og den variable kostnaden ved å produsere hvert kort er USD 1,50. Restkort må avhendes til en kostnad på USD 0,20 per kort. Hvor mange kort skal skrives ut?

I utgangspunktet simulerer vi hvert mulige produksjonsantall (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 filen Valentine.xlsx, som vist i figur 60-4. Du tilordner områdenavnene i celle B1:B11 til celle C1:C11. Celleområdet G3:H6 er tilordnet navneoppslaget. Våre salgspris- og kostnadsparametere angis i celle C4:C6.

Du kan angi et prøveversjonsproduksjonsantall (40 000 i dette eksemplet) i celle C1. Deretter oppretter du et tilfeldig tall i celle C2 med formelen =TILFELDIG(). Som tidligere beskrevet simulerer du etterspørselen etter kortet i celle C3 med formelen FINN.RAD(rand,oppslag,2). (I FINN.RAD-formelen er rand cellenavnet som er tilordnet celle C3, ikke TILFELDIG-funksjonen.)

Antall solgte enheter er det minste av produksjonsantallet og etterspørselen. I celle C8 beregner du omsetningen med formelen MIN(produsert,etterspørsel)*unit_price. I celle C9 beregner du den totale produksjonskostnaden med formelen produsert*unit_prod_cost.

Hvis vi produserer flere kort enn det som er etterspurt, er antall enheter igjen lik produksjon minus etterspørsel; Ellers er det ingen enheter igjen. Vi beregner disponeringskostnadene i celle C10 med formelen unit_disp_cost*HVIS(produsert>behov,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 på mange ganger (for eksempel 1000) for hvert produksjonsantall og telle det forventede fortjenesten for hvert antall. Denne situasjonen er en der en toveis datatabell kommer til vår redning. (Se kapittel 15, "Følsomhetsanalyse med datatabeller", for mer informasjon om datatabeller.) Datatabellen som brukes i dette eksemplet, vises i figur 60-5.

I celleområdet A16:A1015 skriver du inn tallene 1–1000 (tilsvarende våre 1000 forsøk). Én enkel måte å opprette disse verdiene på, er å begynne med å skrive inn 1 i celle A16. Merk cellen, og klikk Fyll på Hjem-fanen i Redigering-gruppen, og velg Serier for å vise dialogboksen Serier. Skriv inn en trinnverdi på 1 og en stoppverdi på 1000 i dialogboksen Serie , som vises i figur 60-6. Velg alternativet Kolonneri området Serier, og klikk deretter OK. Tallene 1–1000 legges inn i kolonne A med start i celle A16.

Deretter angir vi mulige produksjonsantall (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 etter hvert produksjonsantall. Velg tabellområdet (A15:E1014), og klikk deretter Hva hvis analyse i Dataverktøy-gruppen på Data-fanen, og velg deretter Datatabell. Hvis du vil konfigurere en toveis datatabell, velger du produksjonsantallet (celle C1) som radinndatacelle og merker en tom celle (vi valgte celle I14) som inndatacelle for kolonne. Når du har klikket OK, simulerer Excel 1000 behovsverdier for hvert ordreantall.

Hvis du vil forstå hvorfor dette fungerer, bør du vurdere verdiene som er plassert av datatabellen i celleområdet C16:C1015. Excel bruker en verdi på 20 000 i celle C1 for hver av disse cellene. I C16 plasseres verdien for inndatacelle for kolonne på 1 i en tom celle, og det tilfeldige tallet i celle C2 beregnes på nytt. Den tilsvarende fortjenesten registreres deretter i celle C16. Deretter plasseres inndataverdien for kolonnecellen på 2 i en tom celle, og det tilfeldige tallet i C2 beregnes på nytt. Den tilsvarende fortjenesten angis i celle C17.

Ved å kopiere fra celle B13 til C13:E13 formelen GJENNOMSNITT(B16:B1015), beregner vi gjennomsnittlig simulert fortjeneste for hvert produksjonsantall. Ved å kopiere fra celle B14 til C14:E14 formelen STDAV(B16:B1015), beregner vi standardavviket for vår simulerte fortjeneste for hvert ordreantall. Hver gang vi trykker F9, simuleres 1000 gjentakelser av etterspørselen for hvert ordreantall. Å produsere 40.000 kort gir alltid den største forventede fortjenesten. 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årt forventede resultat omtrent 22 prosent, men risikoen (målt ved standardavviket for fortjeneste) faller nesten 73 prosent. Derfor, hvis vi er ekstremt uvillige til risiko, produsere 20.000 kort kan være den riktige avgjørelsen. Forresten, produsere 10.000 kort har alltid et standardavvik på 0 kort fordi hvis vi produserer 10.000 kort, vil vi alltid selge dem alle uten rester.

Konfidensintervall 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 konfidensintervallet på 95 prosent for gjennomsnittlig fortjeneste. Et konfidensintervall på 95 prosent for gjennomsnittet av enhver simuleringsutgang beregnes av følgende formel:

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

Vi er 95 prosent sikre på at vår gjennomsnittlige fortjeneste når 40.000 kalendere er bestilt er mellom $ 56.687 og $ 62.589.

1. En GMC forhandler mener at etterspørselen etter 2005 Envoys vil normalt distribueres med et gjennomsnitt på 200 og standardavvik på 30. Hans kostnad for å motta en utsending er $ 25.000, og han selger en utsending for $ 40.000. Halvparten av alle utsendinger som ikke selges til full pris kan selges for $ 30.000. Han vurderer å bestille 200, 220, 240, 260, 280 eller 300 utsendinger. Hvor mange skal han bestille?

2. Et lite supermarked prøver å finne ut hvor mange kopier av Folk magasin de skal bestille hver uke. De mener at deres etterspørsel etter Folk styres av følgende diskrete tilfeldige variabel:

   Etterspørsel	Sannsynlighet
   15	0,10
   20	0.20
   25	0.30
   30	0,25
   35	0,15

3. Supermarkedet betaler $ 1,00 for hver kopi av Folk og selger den for $ 1,95. Hver usolgte kopi kan returneres for USD 0,50. Hvor mange kopier av Folk bør lagre bestillingen?