Uvod u simulaciju Monte Carlo u programu Excel

• Ko koristi simulaciju Monte Karla?

• Šta se dešava kada otkucate =RAND() u ćeliji?

• Kako možete da simulirate vrednosti diskretne nasumične promenljive?

• Kako možete da simulirate vrednosti normalne nasumične promenljive?

• Kako preduzeće za čestitke može da odredi koliko kartica treba da se proizvede?

Želimo precizno da utvrdimo verovatnoću neizvesnih događaja. Na primer, koja je verovatnoća da će tokovi novca novog proizvoda imati pozitivnu neto sadašnju vrednost (NPV)? Koji je faktor rizika našeg investicionog portfolija? Monte Carlo simulacija nam omogućava da modelizujemo situacije koje izlažeju nesigurnost, a zatim ih reprodukujemo na računaru hiljadama puta.

U sledećih pet poglavlja videćete primere kako možete da koristite Excel za izvršavanje Monte Carlo simulacija.

Mnoge kompanije koriste Monte Carlo simulaciju kao važan deo procesa donošenja odluka. Evo nekih primera.

• General Motors, Proctor i Gamble, Pfizer, Bristol-Myers Squibb i Eli Lilly koriste simulaciju za procenu prosečnog povraćaja i faktora rizika novih proizvoda. U GM-u te informacije ceo direktor koristi da bi utvrdio koji proizvodi dolaze na tržište.

• GM koristi simulaciju za aktivnosti kao što su predviđanje neto prihoda za korporaciju, predviđanje strukturnih i kubnih troškova i određivanje njene podložnosti različitim vrstama rizika (kao što su promene kamatne stope i fluktuacije kursa).

• Lili koristi simulaciju da odredi optimalan kapacitet biljke za svaki lek.

• Proctor i Gamble koriste simulaciju za modelovanje i optimalno korišćenje sporednog rizika.

• Sars koristi simulaciju kako bi odredio koliko jedinica svake linije proizvoda treba poručiti od dobavljača – na primer, broj parova Dockers pantalona koje bi trebalo poručiti ove godine.

• Kompanije za naftu i lekove koriste simulaciju da bi vrednoovale "stvarne opcije", kao što je vrednost opcije za proširenje, ugovor ili odlaganje projekta.

• Finansijski planeri koriste simulaciju Monte Carlo da bi odredili optimalne investicione strategije za povlačenje klijenata.

Kada otkucate formulu =RAND() u ćeliji, dobijate broj koji će verovatno pretpostaviti bilo koju vrednost između 0 i 1. Stoga bi oko 25 procenata vremena trebalo da dobijete broj manji ili jednak 0,25; oko 10 procenata vremena trebalo bi da dobijete broj koji je najmanje 0,90 i tako dalje. Da biste pokazali kako funkcioniše funkcija RAND, pogledajte datoteku Randdemo.xlsx prikazanu na slici 60-1.

Prvo kopirajte iz ćelije C3 u C4:C402 formulu =RAND(). Zatim opsegu imenujte C3:C402 podaci. Zatim u koloni F možete da pratite prosečnu vrednost 400 nasumičnih brojeva (ćelija F2) i koristite funkciju COUNTIF da biste utvrdili razlomke između 0 i 0,25, 0,25 i 0,50, 0,50 i 0,75 i 0,75 i 1. Kada pritisnete taster F9, nasumični brojevi se ponovo izračunavaju. Obratite pažnju na to da je prosek od 400 brojeva uvek približno 0,5 i da je oko 25 procenata rezultata u intervalima od 0,25. Ovi rezultati su u skladu sa definicijom nasumičnog broja. Imajte na umu i da su vrednosti koje je RAND generisati u različitim ćelijama nezavisne. Na primer, ako je nasumični broj generisan u ćeliji C3 veliki broj (na primer, 0,99), on nam ništa ne govori o vrednostima drugih nasumičnih brojeva generisanih.

Recimo da zahtev za kalendarom upravlja sledeća diskretna nasumična promenljiva:

Zahtev	Verovatnoća
10.000	0,10
20.000	0.35
40,000	0,3
60.000	0,25

Kako možemo da podstičemo Excel da reprodukuje ili simulira ovaj zahtev za kalendare više puta? Trik je da povežete svaku moguću vrednost funkcije RAND sa mogućim zahtevom za kalendare. Sledeći zadatak obezbeđuje da se zahtev od 10.000 javi u 10 procenata vremena i tako dalje.

Zahtev	Nasumični broj je dodeljen
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 zahteva, pogledajte datoteku Discretesim.xlsx, prikazanu na slici 60-2 na sledećoj stranici.

Ključ simulacije je da koristite nasumični broj da biste pokrenuli pronalaženje iz opsega tabele F2:G5 ( imenovano pronalaženje). Nasumični brojevi veći od ili jednaki 0 i manji od 0,10 daće zahtev od 10.000; nasumični brojevi veći od ili jednaki 0,10 i manji od 0,45 daće zahtev od 20.000; nasumični brojevi veći od ili jednaki 0,45 i manji od 0,75 daće zahtev od 40.000; i nasumični brojevi veći od ili jednaki 0,75 daće zahtev od 60.000. Generisanje 400 nasumičnih brojeva kopiranjem iz C3 u C4:C402 formulu RAND(). Zatim generišete 400 probnih verzija ili iteracija zahteva kalendara tako što kopirate iz B3 u B4:B402 formulu VLOOKUP(C3,lookup,2). Ova formula obezbeđuje da svaki nasumični broj manji od 0,10 generiše zahtev od 10.000, bilo koji nasumični broj između 0,10 i 0,45 generiše zahtev od 20.000 i tako dalje. U opsegu ćelija F8:F11 koristite funkciju COUNTIF da biste utvrdili razlomak od 400 iteracija koje daju svaki zahtev. Kada pritisnemo taster F9 da bismo ponovo izračunali nasumične brojeve, simulirane verovatnoće su blizu pretpostavljanih verovatnoća.

Ako u bilo kojoj ćeliji otkucate formulu NORMINV(rand(),mu,sigma), generisaćete simuliranu vrednost normalne nasumične promenljive sa srednjom mu i standardnom sigmom devijacije. Ova procedura je ilustrovana u datoteci Normalsim.xlsx prikazanoj u slici 60-3.

Pretpostavimo da želimo da simulirate 400 probnih verzija ili iteracija za normalnu nasumičnu promenljivu sa srednjom vrednost od 40.000 i standardnom devijacijom od 10.000. (Ove vrednosti možete da otkucate u ćelijama E1 i E2 i da ove ćelije imenujete znacima i sigmom, tim redom.) Kopiranjem formule =RAND() iz C4 u C5:C403 generiše se 400 različitih nasumičnih brojeva. Kopiranjem iz B4 u B5:B403 formula NORMINV(C4,prosek,sigma) generiše 400 različitih probnih vrednosti od normalne nasumične promenljive sa srednjom vrednosti od 40.000 i standardnom devijacijom od 10.000. Kada pritisnemo taster F9 da bismo ponovo izračunali nasumične brojeve, srednja vrednost ostaje blizu 40.000, a standardna devijacija blizu 10.000.

U suštini, za slučajni broj x formula NORMINV(p,mu,sigma) generiše p-tipercentil normalne nasumične promenljive sa srednjom mu i standardnom sigmom devijacije. Na primer, nasumični broj 0,77 u ćeliji C4 (pogledajte sliku 60-3) generiše u ćeliji B4 približno 77. percentil normalne nasumične promenljive sa srednjom vrednosti od 40.000 i standardnom devijacijom od 10.000.

U ovom odeljku ćete videti kako se simulacija Monte Carlo može koristiti kao alatka za donošenje odluka. Recimo da zahtev za karticu "Dan zaljubljenih" upravlja sledećom diskretnom nasumičnim promenljivom:

Zahtev	Verovatnoća
10.000	0,10
20.000	0.35
40,000	0,3
60.000	0,25

Čestitka se prodaje za 4,00 USD, a promenljiva cena proizvodnje svake kartice iznosi 1,50 USD. Kartice sa leve strane moraju biti odbačene po ceni od 0,20 USD po kartici. Koliko kartica treba odštampati?

U suštini simulirate svaku moguću količinu proizvodnje (10.000, 20.000, 40.000 ili 60.000) više puta (na primer, 1000 iteracija). Zatim određujemo koja količina porudžbine daje maksimalni prosečan profit preko 1000 iteracija. Podatke za ovaj odeljak možete da pronađete u datoteci Valentine.xlsx, prikazanoj na slici 60-4. Imena opsega iz ćelija B1:B11 dodeljujete ćelijama C1:C11. Opsegu ćelija G3:H6 se dodeljuje ime za pronalaženje. Naša prodajna cena i parametri troška unose se u ćelije C4:C6.

Možete uneti probnu količinu proizvodnje (40.000 u ovom primeru) u ćeliju C1. Zatim kreirajte nasumični broj u ćeliji C2 pomoću formule =RAND(). Kao što je prethodno opisano, zahtev za karticom u ćeliji C3 simulirate pomoću formule VLOOKUP(rand,lookup,2). (U formuli VLOOKUP, rand je ime ćelije dodeljeno ćeliji C3, a ne funkcija RAND.)

Broj prodatih jedinica je manji od naše proizvodne količine i potražnje. U ćeliji C8 prihod računate pomoću formule MIN(proizvod,zahtev)*unit_price. U ćeliji C9 računate ukupni trošak proizvodnje sa formulom proizvedenim*unit_prod_cost.

Ako proizvedemo više kartica nego što je potrebno, broj jedinica koje su ostao jednak je proizvodnoj minus zahtevi; U suprotnom, nijedna jedinica nije preostavna. Troškove odlaganja u ćeliji C10 računamo formulom unit_disp_cost*IF(proizvodi>zahtev,proizvod–zahtev,0). Na kraju, u ćeliji C11 računamo naš profit kao prihod– total_var_cost-total_disposing_cost.

Želimo da na efikasan način pritisnemo taster F9 više puta (na primer, 1000) za svaku količinu proizvodnje, a da u svakom broju imamo očekivanu dobit za svaku količinu. Ova situacija je situacija u kojoj nam dvosmerna tabela sa podacima pristiže u pomoć. (Pogledajte poglavlje 15, "Analiza osetljivosti sa tabelama podataka" za detalje o tabelama podataka.) Tabela podataka korišćena u ovom primeru prikazana je u slici 60-5.

U opseg ćelija A16:A1015 unesite brojeve 1–1000 (koji odgovaraju našim 1000 probnih verzija). Jedan jednostavan način da kreirate ove vrednosti jeste da počnete tako što ćete uneti 1 u ćeliju A16. Izaberite ćeliju, a zatim na kartici Početak u grupi Uređivanje kliknite na dugme Popuni i izaberite stavku Grupa da biste prikazali dijalog Grupa. U dijalogu Grupa , prikazanom na slici 60-6, unesite Vrednost koraka od 1 i Vrednost zaustavljanja od 1000. U oblasti Grupa po izaberite opciju Kolone , a zatim kliknite na dugme U redu. Brojevi od 1 do 1000 će biti uneti u kolonu A koja počinje u ćeliji A16.

Zatim unosimo svoje moguće količine proizvodnje (10.000, 20.000, 40.000, 60.000) u ćelije B15:E15. Želimo da izračunamo profit za svaki broj probne verzije (od 1 do 1000) i svaku količinu proizvodnje. Formulu za profit (izračunatu u ćeliji C11) u gornjoj levoj ćeliji tabele sa podacima (A15) unošenjem =C11.

Sada smo spremni da prevarimo Excel u simuliranje 1000 iteracija zahteva za svaku proizvodnu količinu. Izaberite opseg tabele (A15:E1014), a zatim u grupi Alatke za podatke na kartici Podaci izaberite stavku Analiza šta ako, a zatim izaberite stavku Tabela podataka. Da biste podesili dvosmernu tabelu sa podacima, odaberite količinu proizvodnje (ćelija C1) kao ulaznu ćeliju reda i izaberite bilo koju praznu ćeliju (odabrali smo ćeliju I14) kao ulaznu ćeliju kolone. Kada kliknete na dugme U redu, Excel simulira 1000 vrednosti zahteva za svaku količinu porudžbine.

Da biste razumeli zašto ovo funkcioniše, razmotrite vrednosti koje je postavila tabela podataka u opsegu ćelija C16:C1015. Za svaku od tih ćelija Excel će koristiti vrednost od 20.000 u ćeliji C1. U ćeliji C16 vrednost ulazne ćelije kolone 1 postavlja se u praznu ćeliju, a nasumični broj u ćeliji C2 se ponovo izračunava. Odgovarajući profit se zatim zapisuju u ćeliji C16. Zatim se ulazna vrednost za ćeliju kolone 2 postavlja u praznu ćeliju, a nasumični broj u ćeliji C2 ponovo se izračunava. Odgovarajući profit se unosi u ćeliju C17.

Kopiranjem iz ćelije B13 u C13:E13 formulu AVERAGE(B16:B1015)izračunavamo prosečnu simuliranu profit za svaku količinu proizvodnje. Kopiranjem iz ćelije B14 u C14:E14 formulu STDEV(B16:B1015)izračunavamo standardnu devijaciju simuliranih profita za svaku količinu porudžbine. Svaki put kada pritisnemo taster F9, 1000 iteracija zahteva se simulira za svaku količinu porudžbine. Proizvodnja 40.000 kartica uvek daje najveći očekivani profit. Prema tome, izgleda da je proizvodnja 40.000 kartica odgovarajuća odluka.

Uticaj rizika na našu odluku      Ako proizvedemo 20.000 umesto 40.000 kartica, očekivani profit pada otprilike 22 procenta, ali naš rizik (mereno standardnom devijacijom profita) pada skoro 73 procenta. Stoga, ako smo izuzetno protiv rizika, proizvodnja 20.000 kartica bi mogla biti pravi izbor. Na primer, proizvodnja 10.000 kartica uvek ima standardnu devijaciju od 0 kartica, jer ako proizvedemo 10.000 kartica, uvek ćemo ih sve prodavati bez ikakve zaliha.

Interval pouzdanosti za profit srednje vrednosti      Prirodno pitanje koje treba postaviti u ovoj situaciji je u kakvom intervalu smo mi 95 procenata sigurni da će nastati prava srednja vrednost profita? Ovaj interval se naziva interval pouzdanosti od 95 procenata za srednju dobit. Interval pouzdanosti od 95 procenata za srednju vrednost bilo kog izlaza simulacije izračunava sledeća formula:

U ćeliji J11 računate donje ograničenje za interval pouzdanosti od 95 procenata na osnovu srednje profita kada se pomoću formule D13–1.96*D14/SQRT(1000)dobije 40.000 kalendara. U ćeliji J12 gornju graničnu vrednost za interval pouzdanosti od 95 procenata računate pomoću formule D13+1.96*D14/SQRT(1000). Ova izračunavanja su prikazana na slici 60-7.

95 procenata smo sigurni da će naša srednja zarada kada je poručeno 40.000 kalendara biti između 56.687 i 62.589 USD.

1. Diler GMC-a veruje da će zahtev za izaslanike 2005. Njegova cena dobijanja izaslanika iznosi 25.000 DOLARA, a izaslanika prodaje za $40.000. Polovina svih izaslanika koji nisu prodati po punoj ceni može da se proda za 30.000 USD. Razmatra da naruči 200, 220, 240, 260, 280 ili 300 izaslanika. Koliko treba da naruиi?

2. Mali supermarket pokušava da odredi koliko kopija Osobe bi trebalo da naruče svake nedelje. Oni veruju da njihovim zahtevom Osobe upravlja sledeća diskretna nasumična promenljiva:

   Zahtev	Verovatnoća
   15	0,10
   20	0.20
   25	0.30
   30	0,25
   35	0,15

3. Supermarket plaća 1,00 USD za svaku kopiju Osobe i prodaje za 1,95 USD. Svaka neprodatana kopija može biti vraćena za 0,50 USD. Koliko kopija Osobe da poručite u skladištu?