Johdanto Monte Carlon simulointiin Excelissä

• Kuka käyttää Monte Carlon simulaatiota?

• Mitä tapahtuu, kun kirjoitat = SATUNNAISLUKU() soluun?

• Miten voit simuloida erillisen satunnaismuuttujan arvoja?

• Miten voit simuloida normaalin satunnaismuuttujan arvoja?

• Miten tervehdyskorttiyritys voi määrittää, kuinka monta korttia tuotetaan?

Haluamme arvioida tarkasti epävarmojen tapahtumien todennäköisyydet. Millä todennäköisyydellä esimerkiksi uuden tuotteen kassavirroilla on positiivinen nettonykyarvo? Mikä on sijoitussalkkumme riskitekijä? Monte Carlon simulaation avulla voimme mallintaa tilanteita, jotka aiheuttavat epävarmuutta, ja toistaa niitä sitten tietokoneella tuhansia kertoja.

Seuraavissa viidessä luvussa näet esimerkkejä siitä, miten voit käyttää Exceliä Monte Carlon simulointiin.

Monet yritykset käyttävät Monte Carlon simulointia tärkeänä osana päätöksentekoprosessiaan. Seuraavassa on joitakin esimerkkejä.

• General Motors, Proctor ja Gamble, Pfizer, Bristol-Myers Squibb ja Eli Lilly käyttävät simulointia arvioidakseen sekä uusien tuotteiden keskimääräisen tuoton että riskitekijän. GM:ssä toimitusjohtaja käyttää näitä tietoja sen määrittämiseen, mitkä tuotteet tulevat markkinoille.

• GM käyttää simulointia toimintoihin, kuten yrityksen nettotulojen ennustamiseen, rakenne- ja ostokustannusten ennustamiseen ja sen alttiutta erilaisille riskeille (kuten korkomuutoksille ja valuuttakurssien vaihteluille).

• Lilly käyttää simulointia määrittääkseen optimaalisen kasvikapasiteetin kullekin lääkkeelle.

• Proctor ja Gamble käyttävät simulointia valuuttariskien mallintamiseen ja optimaaliseen suojaamiseen.

• Sears määrittää simuloinnin avulla, kuinka monta yksikköä kunkin tuotelinjan tulisi tilata toimittajilta – esimerkiksi tänä vuonna tilattavien Dockers-housuparien määrä.

• Öljy- ja lääkeyhtiöt käyttävät simulointia arvostaakseen "todellisia vaihtoehtoja", kuten projektin laajentamis-, sopimus- tai lykkäysvaihtoehdon arvoa.

• Rahoitussuunnittelijat käyttävät Monte Carlon simulointia määrittääkseen optimaaliset sijoitusstrategiat asiakkaidensa eläkkeelle jäämistä varten.

Kun kirjoitat soluun kaavan =SATUNNAISLUKU( ), saat luvun, jossa oletetaan yhtä todennäköisesti olevan mikä tahansa arvo väliltä 0–1. Näin ollen noin 25 prosenttia ajasta, sinun pitäisi saada luku, joka on pienempi tai yhtä suuri kuin 0,25; noin 10 prosenttia ajasta sinun pitäisi saada luku, joka on vähintään 0,90, ja niin edelleen. Jos haluat havainnollistaa SATUNNAISLUKU-funktion toimintaa, tutustu kuvassa 60-1 esitettyyn tiedostoon Randdemo.xlsx.

Kopioi ensin solusta C3 soluun C4:C402 kaava =SATUNNAISLUKU(). Nimeä sitten alue C3:C402 Tiedot. Sarakkeessa F voit seurata 400 satunnaisluvun (solu F2) keskiarvoa ja määrittää LASKE.JOS-funktiolla murtoluvut, jotka ovat välillä 0–0,25, 0,25 ja 0,50, 0,50 ja 0,75 sekä 0,75 ja 1. Kun painat F9-näppäintä, satunnaisluvut lasketaan uudelleen. Huomaa, että 400 luvun keskiarvo on aina noin 0,5 ja että noin 25 prosenttia tuloksista on 0,25 väliajoin. Nämä tulokset ovat yhdenmukaisia satunnaisluvun määritelmän kanssa. Huomaa myös, että SATUNNAISLUKU-funktion eri soluissa luomat arvot ovat riippumattomia. Jos esimerkiksi solussa C3 luotu satunnaisluku on suuri luku (esimerkiksi 0,99), se ei kerro meille mitään muiden luotujen satunnaislukujen arvoista.

Oletetaan, että kalenterin kysyntään liittyy seuraava erillinen satunnaismuuttuja:

Kysyntä	Todennäköisyys
10 000	0,10
20 000	0,35
40,000	0,3
60 000	0,25

Miten Excel voi toistaa tai simuloida tätä kalentereiden kysyntää monta kertaa? Jokainen SATUNNAISLUKU-funktion mahdollinen arvo yhdistetään kalentereiden mahdolliseen kysyntään. Seuraava tehtävä varmistaa, että 10 000:n kysyntä tapahtuu 10 prosenttia ajasta ja niin edelleen.

Kysyntä	Satunnaisluku määritetty
10 000	Pienempi kuin 0,10
20 000	Suurempi tai yhtä suuri kuin 0,10 ja pienempi kuin 0,45
40,000	Suurempi tai yhtä suuri kuin 0,45 ja pienempi kuin 0,75
60 000	Suurempi tai yhtä suuri kuin 0,75

Voit havainnollistaa kysynnän simulointia katsomalla tiedostoa Discretesim.xlsx, joka näkyy seuraavan sivun kuvassa 60-2.

Simulaatiomme avain on satunnaisluvun käyttäminen haun aloittamiseen taulukon alueelta F2:G5 (nimetty haku). Satunnaisluvut, jotka ovat suurempia tai yhtä suuria kuin 0 ja pienempiä kuin 0,10, tuottavat kysynnän 10 000; satunnaisluvut, jotka ovat suurempia tai yhtä suuria kuin 0,10 ja pienemmät kuin 0,45, tuottavat kysynnän 20 000; satunnaisluvut, jotka ovat suurempia tai yhtä suuria kuin 0,45 ja pienempiä kuin 0,75, tuottavat kysynnän 40 000; ja satunnaisluvut, jotka ovat suurempia tai yhtä suuria kuin 0,75, tuottavat kysynnän 60 000. Luot 400 satunnaislukua kopioimalla solusta C3 soluun C4:C402 kaavan SATUNNAISLUKU(). Tämän jälkeen luot 400 kokeiluversiota eli iteraatioita kalenterin kysynnästä kopioimalla kaavasta B3 kohteeseen B4:B402 PHAKU(C3,haku,2). Tämä kaava varmistaa, että mikä tahansa satunnaisluku, joka on pienempi kuin 0,10, tuottaa kysynnän 10 000, mikä tahansa satunnaisluku väliltä 0,10–0,45 tuottaa kysynnän 20 000 ja niin edelleen. Käytä solualueella F8:F11 LASKE.JOS-funktiota kunkin kysynnän tuottavan 400 iteraatiomme murto-osan määrittämiseen. Kun painamme F9-näppäintä satunnaislukujen laskemiseksi uudelleen, simuloidut todennäköisyydet ovat lähellä oletettuja kysynnän todennäköisyyksiä.

Jos kirjoitat mihin tahansa soluun NORMINV(rand(),mu,sigma)-kaavan, luot simuloidun arvon normaalista satunnaismuuttujasta, jolla on keskihajonta sigma. Tämä toimenpide näkyy kuvassa 60–3 olevassa Normalsim.xlsx tiedostossa.

Oletetaan, että haluamme simuloida 400 kokeilua eli iteraatioita normaalille satunnaismuuttujalle, jonka keskiarvo on 40 000 ja keskihajonta 10 000. (Voit kirjoittaa nämä arvot soluihin E1 ja E2 ja nimetä nämä solut keskiarvoksi ja sigmaksi.) Kaavan =SATUNNAISLUKU() kopioiminen solusta C4 soluun C5:C403 luo 400 erilaista satunnaislukua. Kaava NORMINV(C4,keskiarvo,sigma) tuottaa 400 erilaista koearvoa normaalista satunnaismuuttujasta, keskiarvo 40 000 ja keskihajonta 10 000. Kun painamme F9-näppäintä satunnaislukujen uudelleenlaskentaan, keskiarvo pysyy lähellä 40 000:aa ja keskihajontaa lähellä 10 000:aa.

Satunnaisluvun x kaava NORM.JAKAUMA.KÄÄNT(p,mu,sigma) luo normaalin satunnaismuuttujan p:nnen prosenttipisteen keskiarvolla mu ja keskihajonnan sigmalla. Esimerkiksi solussa C4 oleva satunnaisluku 0,77 (ks. kuva 60–3) luo soluun B4 noin normaalin satunnaismuuttujan 77. prosenttipisteen keskiarvolla 40 000 ja keskihajonnan 10 000.

Tässä osassa näet, miten Monte Carlon simulaatiota voidaan käyttää päätöksentekovälineenä. Oletetaan, että ystävänpäiväkortin kysyntään sovelletaan seuraavaa satunnaismuuttujaa:

Kysyntä	Todennäköisyys
10 000	0,10
20 000	0,35
40,000	0,3
60 000	0,25

Onnittelukortti myy 4,00 dollarilla, ja kunkin kortin tuottamisen muuttuva hinta on 1,50 dollaria. Ylijäämäkortit on hävitettävä hintaan 0,20 $ korttia kohti. Kuinka monta korttia tulostetaan?

Periaatteessa simuloimme jokaisen mahdollisen tuotantomäärän (10 000, 20 000, 40 000 tai 60 000) monta kertaa (esimerkiksi 1000 iteraatiota). Sen jälkeen määritämme, mikä tilausmäärä tuottaa suurimman keskimääräisen voiton 1000 iteraatiosta. Löydät tämän osan tiedot tiedoston Valentine.xlsx, joka näkyy kuvassa 60-4. Soluissa B1:B11 olevien alueiden nimet määritetään soluille C1:C11. Nimihaku määritetään solualueelle G3:H6. Myyntihinta- ja kustannusparametrimme syötetään soluihin C4:C6.

Voit syöttää kokeilutuotantomäärän (tässä esimerkissä 40 000) soluun C1. Luo seuraavaksi satunnaisluku soluun C2 kaavalla =SATUNNAISLUKU(). Kuten edellä on kuvattu, simuloit kortille kysyntää solussa C3 kaavalla PHAKU(rand,haku,2). (PHAKU-kaavassa randi on solun nimi, joka on määritetty soluun C3, ei SATUNNAISLUKU-funktioon.)

Myytyjen yksiköiden määrä on pienempi tuotantomäärästämme ja kysynnästämme. Solussa C8 lasket tuottomme kaavalla MIN(produced,demand)*unit_price. Solussa C9 lasketaan tuotannon kokonaiskustannukset kaavalla, joka on tuotettu*unit_prod_cost.

Jos tuotamme enemmän kortteja kuin on tarpeen, jäljelle jääneiden yksiköiden määrä on yhtä suuri kuin tuotanto miinus kysyntä; muussa tapauksessa yksiköitä ei jätetä. Laskemme hävittämiskustannusmme solussa C10 kaavalla unit_disp_cost*JOS(tuotettu>kysyntä,tuotettu–kysyntä,0). Lopuksi solussa C11 laskemme voittomme tulona – total_var_cost-total_disposing_cost.

Haluamme tehokkaan tavan painaa F9-näppäintä monta kertaa (esimerkiksi 1 000) kullekin tuotantomäärälle ja laskemaan kunkin määrän odotettu voitto. Tässä tilanteessa pelastamme kaksisuuntaisen datataulukon. (Lisätietoja arvotaulukoista on luvussa 15 "Luottamuksellisuusanalyysi ja arvotaulukot".) Tässä esimerkissä käytetty arvotaulukko näkyy kuvassa 60–5.

Kirjoita solualueeseen A16:A1015 luvut 1–1 000 (vastaa 1 000 koetta). Yksi helppo tapa luoda nämä arvot on aloittaa kirjoittamalla soluun A16 1 . Valitse solu ja valitse sitten Muokkaaminen-ryhmänAloitus-välilehdessäTäyttö ja valitse Sarja, jolloin Sarjat-valintaikkuna tulee näkyviin. Kirjoita Sarjat-valintaikkunan kuvassa 60–6 vaiheen arvo 1 ja Pysäytysarvo 1000. Valitse Sarjan osat -alueella Sarakkeet-vaihtoehto ja valitse sitten OK. Luvut 1–1 000 lisätään sarakkeeseen A alkaen solusta A16.

Seuraavaksi syötämme mahdolliset tuotantomäärämme (10 000, 20 000, 40 000, 60 000) soluihin B15:E15. Haluamme laskea kunkin kokeiluversion luvun (1–1 000) ja kunkin tuotantomäärän tuoton. Viittaamme arvotaulukon (A15) vasemmassa yläsolussa olevaan tuottokaavaan (laskettuna solussa C11) kirjoittamalla =C11.

Olemme nyt valmiita huijaamaan Excelin simuloimaan 1 000 iteraatiota kunkin tuotantomäärän kysynnästä. Valitse taulukkoalue (A15:E1014) ja valitse sitten Tiedot-välilehden Tietotyökalut-ryhmästä Entä jos analyysi ja valitse sitten Arvotaulukko. Jos haluat määrittää kaksisuuntaisen arvotaulukon, valitse tuotantomäärä (solu C1) Rivin syöttösoluksi ja valitse mikä tahansa tyhjä solu (valitsimme solun I14) Sarakkeen syöttösoluksi. Kun valitset OK, Excel simuloi 1 000 tilausmäärän kysyntäarvoa.

Jos haluat ymmärtää, miksi tämä toimii, ota huomioon arvot, jotka arvotaulukko on sijoittanut solualueeseen C16:C1015. Kussakin näistä soluista Excel käyttää solussa C1 arvoa 20 000. Solussa C16 sarakkeen syöttösolun arvo 1 sijoitetaan tyhjään soluun ja solun C2 satunnaisluku lasketaan uudelleen. Vastaava voitto kirjataan soluun C16. Tämän jälkeen sarakkeen solun syöttöarvo 2 sijoitetaan tyhjään soluun ja solun C2 satunnaisluku lasketaan uudelleen. Vastaava tuotto lisätään soluun C17.

Kopioimalla solusta B13 soluun C13:E13 kaavan KESKIARVO(B16:B1015)laskemme kunkin tuotantomäärän simuloidun keskiarvon. Kopioimalla solusta B14 soluun C14:E14 kaavan KESKIHAJONTA(B16:B1015)laskemme simuloitujen tuottojen keskihajonnan kullekin tilausmäärälle. Aina kun painamme F9-näppäintä, jokaiselle tilausmäärälle simuloidtaan 1 000 iteraatiota. 40 000 kortin tuottaminen tuottaa aina suurimman odotetun voiton. Siksi näyttää siltä, että 40 000 kortin tuottaminen on oikea päätös.

Riskin vaikutus päätökseemme      Jos tuotimme 20 000 korttia 40 000 kortin sijaan, odotettu tulos laskee noin 22 prosenttia, mutta riskimme (voiton keskihajonnan mukaan mitattuna) laskee lähes 73 prosenttia. Jos siis vastustamme riskejä, 20 000 kortin tuottaminen voi olla oikea päätös. Muuten, 10 000 kortin tuottamisen keskihajonta on aina 0 korttia, koska jos tuotamme 10 000 korttia, myymme ne kaikki aina ilman tähteitä.

Keskimääräinen tuotto -luottamusväli      Luonnollinen kysymys tässä tilanteessa on, mihin aikaväliin olemme 95 prosenttia varmoja siitä, että todellinen keskimääräinen voitto laskee? Tätä väliä kutsutaan keskivoiton 95 prosentin luottamusväliksi. Simuloinnin tuloksen keskiarvon 95 prosentin luottamusväli lasketaan seuraavalla kaavalla:

Solussa J11 lasketaan keskiarvon tuottovälin 95 prosentin luottamusvälin alaraja, kun kaavalla D13–1,96*D14/NELIÖJUURI(1000) tuotetaan 40 000 kalenteria. Solussa J12 lasketaan 95 prosentin luottamusvälin yläraja kaavalla D13+1,96*D14/SQRT(1000). Nämä laskutoimitukset näkyvät kuvassa 60–7.

Olemme 95-prosenttisen varmoja siitä, että keskimääräinen voittomme, kun tilataan 40 000 kalenteria, on 56 687–62 589 dollaria.

1. GMC-jälleenmyyjä uskoo, että vuoden 2005 lähettiläiden kysyntä jaetaan normaalisti keskiarvolla 200 ja keskihajonnan ollessa 30. Lähettilään vastaanotto maksaa 25 000 dollaria ja lähettilään 40 000 dollarilla. Puolet lähettiläistä, joita ei myydä täydellä hinnalla, voidaan myydä 30 000 dollarilla. Hän harkitsee 200, 220, 240, 260, 280 tai 300 lähettilään tilaamista. Montako hänen pitäisi tilata?

2. Pieni supermarket yrittää selvittää, kuinka monta kopiota Ihmiset-lehdestä heidän pitäisi tilata joka viikko. He uskovat, että heidän vaatimuksensa Ihmiset määräytyy seuraavan erillisen satunnaismuuttujan mukaan:

   Kysyntä	Todennäköisyys
   15	0,10
   20	0.20
   25	0.30
   30	0,25
   35	0,15

3. Supermarket maksaa 1,00 dollaria jokaisesta Ihmiset ja myy sen 1,95 dollarilla. Jokainen myymätön kopio voidaan palauttaa hintaan 0,50 $. Kuinka monta kopiota Ihmiset pitäisi tilata?