Uvod u simulaciju Monte Carlo u programu Excel

• Tko koristi Monte Carlo simulaciju?

• Što se događa kada u ćeliju upišete =RAND( ?

• Kako oponašati vrijednosti samostalne slučajne varijable?

• Kako oponašati vrijednosti normalne slučajne varijable?

• Kako tvrtka za čestitke može odrediti koliko kartica treba proizvesti?

Želimo točno procijeniti vjerojatnost neizvjesnih događaja. Na primjer, koja je vjerojatnost da će novčani tokovi novog proizvoda imati pozitivnu neto sadašnju vrijednost (NPV)? Koji je faktor rizika našeg portfelja ulaganja? Simulacija Monte Carla nam omogućuje modeliranje situacija koje su prisutne nesigurnosti, a zatim ih igrati na računalu tisućama puta.

U sljedećih pet poglavlja vidjet ćete primjere kako pomoću programa Excel izvesti simulacije Monte Carlo.

Mnoge tvrtke koriste simulaciju Monte Carlo kao važan dio postupka odlučivanja. Evo nekoliko primjera.

• General Motors, Proctor i Gamble,Izer, Bristol-Myers Squibb, i Eli Lilly koriste simulaciju za procjenu prosječnog povrata i faktora rizika novih proizvoda. U GM-u te podatke koristi izvršni direktor da bi odredio koji se proizvodi isporučuju na tržište.

• GM koristi simulaciju za aktivnosti kao što su predviđanje neto prihoda za tvrtku, predviđanje strukturnih i kupovnih troškova te utvrđivanje njezine podložnosti različitim vrstama rizika (kao što su promjene kamatnih stopa i fluktuacije tečaja).

• Lilly koristi simulaciju za određivanje optimalnog kapaciteta biljke za svaki lijek.

• Proctor i Gamble koriste simulaciju za modeliranje i optimalno hedge valutni rizik.

• Sears koristi simulaciju da bi odredio koliko jedinica svake linije proizvoda treba naručiti od dobavljača– na primjer, broj parova dockers hlača koje treba naručiti ove godine.

• Nafta i droge tvrtke koriste simulaciju za vrijednost "stvarnih opcija", kao što je vrijednost opcije za proširenje, ugovor ili odgađanje projekta.

• Financijski planeri koriste simulaciju Monte Carlo za određivanje optimalnih investicijskih strategija za povlačenje svojih klijenata.

Kada u ćeliju upišete formulu =RAND(), dobit ćete broj koji će vjerojatno pretpostaviti bilo koju vrijednost između 0 i 1. Dakle, oko 25 posto vremena, trebali biste dobiti broj manji od ili jednak 0,25; oko 10 posto vremena potrebno je dobiti broj koji iznosi najmanje 0,90 itd. Da biste pokazali kako funkcija RAND funkcionira, pogledajte naziv datoteke Randdemo.xlsx na slici 60-1.

Najprije kopirajte iz ćelije C3 u C4:C402 formulu =RAND(). Zatim dodijelite naziv rasponu C3:C402 Podaci. Zatim u stupcu F možete pratiti prosjek 400 slučajnih brojeva (ćelija F2) i pomoću funkcije COUNTIF odrediti razlomke između 0 i 0,25, 0,25 i 0,50, 0,50 i 0,75 te 0,75 i 0,75 i 1. Kada pritisnete tipku F9, nasumični se brojevi ponovno izračunavaju. Primijetite da je prosjek 400 brojeva uvijek približno 0,5, a da je oko 25 posto rezultata u intervalima od 0,25. Ti su rezultati dosljedni definiciji slučajnog broja. Imajte na umu i da su vrijednosti koje je generirao RAND u različitim ćelijama neovisne. Na primjer, ako je slučajni broj generiran u ćeliji C3 velik broj (na primjer, 0,99), ne govori nam ništa o vrijednostima drugih generiranih slučajnih brojeva.

Pretpostavimo da zahtjev za kalendarom upravlja sljedeća samostalna slučajna varijabla:

Potražnje	Vjerojatnost
10 000	0,10
20 000	0.35
40,000	0,3
60 000	0,25

Kako možemo više puta reproducirati ili oponašati tu potražnju za kalendarima? Trik je povezati svaku moguću vrijednost funkcije RAND s mogućom potražnjom za kalendarima. Sljedeći zadatak osigurava da će se potražnja od 10.000 pojaviti 10 posto vremena i tako dalje.

Potražnje	Dodijeljen je slučajni broj
10 000	Manje od 0,10
20 000	Veće od ili jednako 0,10 i manje od 0,45
40,000	Veće od ili jednako 0,45 i manje od 0,75
60 000	Veće od ili jednako 0,75

Da biste pokazali simulaciju potražnje, pogledajte datoteku Discretesim.xlsx, prikazanu na slici 60-2 na sljedećoj stranici.

Ključ naše simulacije je korištenje slučajnog broja za pokretanje pretraživanja iz raspona tablica F2:G5 (imenovano pretraživanje). Slučajni brojevi veći od ili jednaki 0 i manji od 0,10 dobit će potražnju od 10 000; slučajni brojevi veći od ili jednaki 0,10 i manji od 0,45 dobit će potražnju od 20 000; slučajni brojevi veći od ili jednaki 0,45 i manji od 0,75 dobit će potražnju od 40 000; slučajni brojevi veći ili jednaki 0,75 dobit će potražnju od 60 000. Generirate 400 slučajnih brojeva kopiranjem iz C3 u C4:C402 formulu RAND(). Zatim generirate 400 pokušaja ili iteracija zahtjeva kalendara kopiranjem iz B3 u B4:B402 formulu VLOOKUP(C3,lookup,2). Ova formula osigurava da svaki slučajni broj manji od 0,10 generira potražnju od 10 000, svaki slučajni broj između 0,10 i 0,45 generira potražnju od 20 000 itd. U rasponu ćelija F8:F11 pomoću funkcije COUNTIF odredite razlomak od 400 iteracija koje rezultiraju svakom potražnjom. Kada pritisnemo F9 da bismo ponovno izračunali slučajne brojeve, simulirane vjerojatnosti blizu su naše pretpostavljene vjerojatnosti potražnje.

Ako u bilo koju ćeliju upišete formulu NORMINV(rand(),mu,sigma), generirat ćete simuliranu vrijednost normalne slučajne varijable koja ima srednju vrijednost mu i standardnu devijaciju. Taj je postupak prikazan u datoteci Normalsim.xlsx na slici 60-3.

Pretpostavimo da želimo oponašati 400 pokušaja ili iteracija za normalnu slučajnu varijablu s srednje vrijednosti 40.000 i standardnom devijacijom od 10 000. (Te vrijednosti možete upisati u ćelije E1 i E2, a tim ćelijama dodijeliti naziv srednja vrijednost, odnosno sigma.) Kopiranje formule =RAND() iz C4 u C5:C403 generira 400 različitih slučajnih brojeva. Kopiranjem iz ćelije B4 u B5:B403 formula NORMINV(C4;srednja;sigma) generira 400 različitih pokusnih vrijednosti iz normalne slučajne varijable sa srednju vrijednost od 40 000 i standardnom devijacijom od 10 000. Kada pritisnemo tipku F9 da bismo ponovno izračunali slučajne brojeve, srednja vrijednost ostaje blizu 40 000, a standardna devijacija blizu 10 000.

U biti, za slučajni broj x, formula NORMINV(p;mu,sigma) generira p-tipercentil normalne slučajne varijable sa srednjem mu i sigmom standardne devijacije. Na primjer, slučajni broj 0,77 u ćeliji C4 (vidi sliku 60-3) generira u ćeliji B4 približno 77. percentil normalne slučajne varijable sa srednje vrijednosti 40 000 i standardnom devijacijom od 10 000.

U ovom ćete odjeljku vidjeti kako se monte Carlo simulacija može koristiti kao alat za donošenje odluka. Pretpostavimo da se zahtjevom za čestitkom za Valentinovo upravlja sljedeća samostalna slučajna varijabla:

Potražnje	Vjerojatnost
10 000	0,10
20 000	0.35
40,000	0,3
60 000	0,25

Čestitka se prodaje za 4,00 USD, a varijabilni trošak proizvodnje svake kartice iznosi 1,50 USD. Leftover cards must be disposed of a cost of $0.20 per card. Koliko je kartica potrebno ispisati?

U osnovi, oponašamo svaku moguću količinu proizvodnje (10 000, 20 000, 40 000 ili 60 000) mnogo puta (na primjer, 1000 iteracija). Zatim utvrđujemo koja količina narudžbe daje maksimalnu prosječnu dobit u iznosu od 1000 iteracija. Podatke za ovaj odjeljak možete pronaći u datoteci Valentine.xlsx na slici 60-4. Nazive raspona u ćelijama B1:B11 dodjeljujete ćelijama C1:C11. Rasponu ćelija G3:H6 dodjeljuje se pretraživanje naziva. Naši parametri prodajne cijene i troškova unose se u ćelije C4:C6.

U ćeliju C1 možete unijeti probnu količinu proizvodnje (40 000 u ovom primjeru). Zatim u ćeliji C2 stvorite slučajni broj pomoću formule =RAND(). Kao što je prethodno opisano, simulirate potražnju za karticom u ćeliji C3 pomoću formule VLOOKUP(rand,lookup,2). (U formuli VLOOKUP rand je naziv ćelije dodijeljen ćeliji C3, a ne funkciji RAND.)

Broj prodanih jedinica manji je od naše proizvodne količine i potražnje. U ćeliji C8 izračunavate naš prihod pomoću formule MIN(proizvedena,potražnja)*unit_price. U ćeliji C9 izračunavate ukupne troškove proizvodnje pomoću formule *unit_prod_cost.

Ako proizvodimo više kartica nego što je u potražnji, broj jedinica koje su preotete jednak je produkcijskog minus potražnje; inače nijedna jedinica ne preolijeva. Izračunavamo troškove odlaganja u ćeliji C10 pomoću formule unit_disp_cost*IF(proizvedena>potražnja,proizvedena –potražnja;0). Konačno, u ćeliji C11 računamo dobit kao prihod – total_var_cost-total_disposing_cost.

Želimo učinkovit način pritiska na F9 mnogo puta (na primjer, 1000) za svaku količinu proizvodnje i za svaku količinu povezati očekivanu dobit. Ta je situacija jedna u kojoj nam dolazi do dvosmlazne podatkovne tablice. (Detalje o podatkovnim tablicama potražite u poglavlju 15., "Analiza osjetljivosti s tablicama podataka". Podatkovna tablica korištena u ovom primjeru prikazana je na slici 60-5.

U raspon ćelija A16:A1015 unesite brojeve od 1 do 1000 (što odgovara 1000 probnih verzija). Te vrijednosti možete jednostavno stvoriti tako da u ćeliju A16 unesete 1. Odaberite ćeliju, a zatim na kartici Polazno u grupi Uređivanje kliknite Ispuna, a zatim odaberite Niz da bi se prikazao dijaloški okvir Niz. U dijaloškom okviru Niz, prikazanom na slici 60-6, unesite vrijednost koraka 1 i stop vrijednost 1000. U području Niz u odaberite mogućnost Stupci , a zatim kliknite U redu. Brojevi od 1 do 1000 unijet će se u stupac A počevši od ćelije A16.

Zatim u ćelije B15:E15 unesete moguće količine proizvodnje (10 000, 20 000, 40 000, 60 000). Želimo izračunati dobit za svaki broj probne verzije (od 1 do 1000) i svaku količinu proizvodnje. Formulu za dobit (izračunatu u ćeliji C11) u gornjoj lijevoj ćeliji podatkovne tablice (A15) pozivamo unosom =C11.

Sada smo spremni na prijevaru programa Excel u objedu 1000 iteracija potražnje za svaku količinu proizvodnje. Odaberite raspon tablica (A15:E1014), a zatim u grupi Alati podataka na kartici Podaci kliknite Analiza što ako, a zatim odaberite Podatkovna tablica. Da biste postavili dvosmjesnu podatkovnu tablicu, kao ulaznu ćeliju retka odaberite našu proizvodnu količinu (ćeliju C1) i odaberite bilo koju praznu ćeliju (odabrali smo ćeliju I14) kao ulaznu ćeliju stupca. Kada kliknete U redu, Excel oponaša 1000 vrijednosti potražnje za svaku količinu narudžbe.

Da biste razumjeli zašto to funkcionira, razmotrite vrijednosti koje je postavili podatkovna tablica u rasponu ćelija C16:C1015. Za svaku od tih ćelija Excel će u ćeliji C1 koristiti vrijednost od 20 000. U ćeliji C16 ulazna ćelija stupca 1 smješta se u praznu ćeliju, a slučajni broj u ćeliji C2 ponovno izračunava. Odgovarajuća dobit zatim se bilježi u ćeliji C16. Zatim se ulazna vrijednost ćelije stupca 2 smješta u praznu ćeliju, a slučajni broj u ćeliji C2 ponovno se izračunava. Odgovarajuća dobit unosi se u ćeliju C17.

Kopiranjem iz ćelije B13 u C13:E13 formula AVERAGE(B16:B1015)izračunava prosječnu simuliranu dobit za svaku količinu proizvodnje. Kopiranjem iz ćelije B14 u C14:E14 formulu STDEV(B16:B1015)izračunavamo standardnu devijaciju simulirane dobiti za svaku količinu narudžbe. Svaki put kada pritisnemo F9, za svaku količinu narudžbe simulira se 1000 iteracija potražnje. Stvaranje 40.000 kartica uvijek rezultira najvećom očekivanom dobiti. Stoga se čini da je za proizvodnju 40.000 kartica ispravna odluka.

Utjecaj rizika na našu odluku      Ako umjesto 40 000 posjetnica proizvedemo 20 000 kartica, očekivana dobit pada otprilike 22 posto, ali naš rizik (mjeren standardnom devijacijom dobiti) pada gotovo 73 posto. Stoga, ako smo iznimno averse na rizik, proizvodnju 20.000 kartica može biti prava odluka. Slučajno, proizvodnja 10.000 kartica uvijek ima standardnu devijaciju od 0 kartica jer ako proizvodimo 10.000 kartica, uvijek ćemo ih prodati sve bez lijeve strane.

Interval pouzdanosti za srednju dobit      Prirodno pitanje koje treba postaviti u ovoj situaciji je, u kojem intervalu smo 95 posto sigurni da će prava srednja dobit pasti? Taj se interval naziva interval pouzdanosti od 95 posto za srednju dobit. Interval pouzdanosti od 95 posto za srednju vrijednost bilo kojeg rezultata simulacije izračunava se pomoću sljedeće formule:

U ćeliji J11 izračunavate donju granicu za interval pouzdanosti od 95 posto za srednju dobit kada se 40 000 kalendara stvara pomoću formule D13 –1,96*D14/SQRT(1000). U ćeliji J12 izračunavate gornju granicu za naš interval pouzdanosti od 95 posto pomoću formule D13+1,96*D14/SQRT(1000). Ti su izračuni prikazani na slici 60- 7.

95 % smo sigurni da je naša srednja dobit kada je naručeno 40 000 kalendara između 56 687 USD i 62 589 USD.

1. Prodavač GMC-a smatra da će se potražnja za izaslanikom iz 2005. obično distribuirati uz srednju vrijednost od 200 i standardnu devijaciju od 30. Njegov trošak primanja izaslanika iznosi 25.000 dolara, a on prodaje izaslanika za 40.000 dolara. Polovica svih izaslanika koji nisu prodani po punoj cijeni može se prodati za 30.000 dolara. Razmatra narudžbu od 200, 220, 240, 260, 280 ili 300 izaslanika. Koliko bi trebao naručiti?

2. Mali supermarket pokušava odrediti koliko primjeraka časopisa Osobe bi trebali naručiti svaki tjedan. Oni vjeruju da je njihov zahtjev Osobe upravlja sljedećim diskretnim slučajnim varijablama:

   Potražnje	Vjerojatnost
   15	0,10
   20	0.20
   25	0.30
   30	0,25
   35	0,15

3. Supermarket plaća 1,00 usd za svaki primjerak Osobe i prodaje ga za 1,95 usd. Svaka neprodana kopija može se vratiti za 0,50 USD. Koliko primjeraka Osobe bi trebao nalog za pohranu?