Uvod v simulacijo Monte Carlo v Excelu

• Kdo uporablja simulacijo Monte Carlo?

• Kaj se zgodi, ko v celico vnesete =RAND( )?

• Kako lahko simulirate vrednosti diskretne naključne spremenljivke?

• Kako lahko simulirate vrednosti normalne naključne spremenljivke?

• Kako lahko podjetje, ki izdeluje voščilnice, določi, koliko kartic je treba izdelati?

Radi bi natančno ocenili verjetnosti negotovih dogodkov. Kakšna je na primer verjetnost, da bodo denarni pretoki novega izdelka imeli pozitivno neto sedanjo vrednost (NPV)? Kakšen je dejavnik tveganja našega naložbenega portfelja? Simulacija Monte Carlo nam omogoča model situacij, ki predstavlja negotovost in jih nato igrajo na računalniških tisoč krat.

V naslednjih petih poglavjih boste videli primere uporabe Excela za izvajanje simulacij Monte Carlo.

Številna podjetja uporabljajo Monte Carlo simulacijo kot pomemben del svojega procesa odločanja. Tukaj je nekaj primerov.

• General Motors, Proctor in Gamble, Pfizer, Bristol-Myers Squibb in Eli Lilly uporabljajo simulacijo uporabe za oceno tako povprečni donos in faktor tveganja novih izdelkov. V GM generalni direktor uporablja te podatke za določanje, kateri proizvodi se dajo na trg.

• GM uporablja simulacijo za dejavnosti, kot so napovedovanje neto dohodka za podjetje, predvidevanje strukturnih stroškov in nabavnih stroškov ter določanje njegove dovzetnosti za različne vrste tveganja (kot so spremembe obrestnih mer in nihanja menjalnih tečajev).

• Lilly si s simulacijo določi optimalno zmogljivost rastlin za vsako zdravilo.

• Proctor in Gamble uporablja simulacijo za model in optimalno hedge tujih menjalnih tveganj.

• Sears uporablja simulacijo, da ugotovi, koliko enot vsake linije izdelka je treba naročiti od dobaviteljev, na primer število parov hlače Dockers, ki je treba naročiti v tem letu.

• Naftne družbe in podjetja, ki uporabljajo drogo, uporabljajo simulacijo za vrednost »realnih možnosti«, kot je vrednost možnosti za razširitev, pogodbo ali odložitev projekta.

• Finančni načrtovalci uporabljajo Monte Carlo simulacijo za določitev optimalne naložbe strategije za ukinitev svojih strank.

Ko v celico vnesete formulo =RAND( ), dobite število, za katerega je enako verjetno, da bo prevzela katero koli vrednost med 0 in 1. Tako, okoli 25 odstotkov časa, bi morali dobiti število, manjše ali enako 0,25; približno 10 odstotkov časa, ko bi morali dobiti število, ki je vsaj 0,90 in tako naprej. Če želite prikazati, kako deluje funkcija RAND, si oglejte seznam datotekRanddemo.xlsx prikazan na sliki 60-1.

Najprej kopirajte formulo = RAND()iz celice C3 v C4:C402. Nato poimenujte obseg C3:C402 Podatki. Nato lahko v stolpcu F sledite povprečju 400 naključnih števil (celica F2) in s funkcijo COUNTIF določite ulomke med 0 in 0,25, 0,25 in 0,50, 0,50 in 0,75 ter 0,75 in 0,75 in 1. Ko pritisnete tipko F9, so naključna števila znova izračunana. Upoštevajte, da je povprečje 400 števil vedno približno 0,5 in da je približno 25 odstotkov rezultatov v intervalih 0,25. Ti rezultati so skladni z definicijo naključnega števila. Upoštevajte tudi, da so vrednosti, ki jih ustvari RAND v različnih celicah, neodvisne. Če je na primer naključno število, ustvarjeno v celici C3, veliko število (na primer 0,99), nam ne pove ničesar o vrednostih drugih ustvarjenih naključnih števil.

Recimo, da zahtevo za koledar urejajo te diskretne naključne spremenljivke:

Povpraševanje	verjetnost
10.000	0,10
20.000	0.35
40,000	0,3
60.000	0,25

Kako se lahko Excel večkrat predvaja ali simulira to zahtevo za koledarje? Vsako možno vrednost funkcije RAND je treba povezati z morebitno zahtevo po koledarjev. Ta dodelitev zagotavlja, da se bo povpraševanje v višini 10.000 prikazalo 10 odstotkov časa in tako naprej.

Povpraševanje	Dodeljeno naključno število
10.000	Manj kot 0,10
20.000	Večje ali enako 0,10 in manjše od 0,45
40,000	Večje ali enako 0,45 in manjše od 0,75
60.000	Večje ali enako 0,75

Če želite prikazati simulacijo zahtev, si oglejte podatke Discretesim.xlsx, prikazane na sliki 60-2 na naslednji strani.

Ključ do naše simulacije je, da uporabite naključno število za začetek iskanja iz obsega tabele F2:G5 (imenovano iskanje). Naključna števila, ki so večja ali enaka 0 in manjša od 0,10, bodo prinesla povpraševanje 10.000; naključna števila, ki so večja ali enaka 0,10 in manjša od 0,45, bodo povlačevala povpraševanje v višini 20.000; naključna števila, ki so večja ali enaka 0,45 in manjša od 0,75, bodo povlačevala povpraševanje v višini 40.000; in naključna števila, ki so večja ali enaka 0,75, bodo prinesla povpraševanje v višini 60.000. 400 naključnih števil ustvarite tako, da kopirate formulo RAND()iz C3 v C4:C402. Nato ustvarite 400 preskusov ali ponovitev zahtev koledarja tako, da kopirate formulo VLOOKUP(C3,lookup,2) iz B3 v B4:B402. Ta formula zagotavlja, da vsako naključno število, manjše od 0,10, ustvari zahtevo 10.000, vsako naključno število med 0,10 in 0,45 pa ustvari povpraševanje 20.000 itn. V obsegu celic F8:F11 uporabite funkcijo COUNTIF, da določite del naših 400 iteracij, ki dajejo vsako zahtevo. Ko pritisnimo F9 za preračunavanje naključnih števil, so simulirane verjetnosti blizu naši domnevno zahtevni verjetnosti.

Če v katero koli celico vnesete formulo NORMINV(rand(),mu,sigma), boste ustvarili simulirano vrednost normalne naključne spremenljivke, ki ima srednjo vrednost mu in standardni odklon sigma. Ta postopek je prikazan v tabeli datoteke Normalsim.xlsx, prikazano na sliki 60-3.

Denimo, da želimo simulirati 400 poskusov ali iteracij za normalno naključno spremenljivko z srednjo možnostjo 40.000 in standardnim odklonom 10.000. (Te vrednosti lahko vnesete v celici E1 in E2 in poimenujte ti celici srednja vrednost in sigma.) Če kopirate formulo =RAND() iz C4 v C5:C403, ustvarite 400 različnih naključnih števil. Če kopirate iz celice B4 v B5:B403, formula NORMINV(C4; srednja_vrednost; sigma) ustvari 400 različnih preskusnih vrednosti iz normalne naključne spremenljivke z srednjo števko 40.000 in standardnim odklonom 10.000. Ko pritisnete tipko F9 za preračunavanje naključnih števil, srednja vrednost ostane blizu 40.000, standardni odklon pa blizu 10.000.

V bistvu formula NORMINV(p,mu,sigma) za naključno število x ustvari pth percentil normalne naključne spremenljivke z srednjo števko in sigmo standardnega odklona. Naključno število 0,77 v celici C4 (glejte sliko 60-3) na primer ustvari v celici B4 približno 77. percentil normalne naključne spremenljivke z aritmetična sredina 40.000 in standardnim odklonom 10.000.

V tem razdelku boste videli, kako se simulacija Monte Carlo lahko uporablja kot orodje za sprejemanje odločitev. Recimo, da zahtevo po kartici za valentinovo urejajo te diskretne naključne spremenljivke:

Povpraševanje	verjetnost
10.000	0,10
20.000	0.35
40,000	0,3
60.000	0,25

Voščilnica je na voljo za 4,00 USD, spremenljivi stroški za izdelavo posamezne kartice pa 1,50 USD. Kartice za levo stran morate odstraniti za 0,20 USD na kartico. Koliko kartic je treba natisniti?

V osnovi simuliramo vsako možno proizvodno količino (10.000, 20.000, 40.000 ali 60.000) večkrat (na primer 1000 ponovitev). Nato določimo, katera količina naročila daje največji povprečni dobiček v 1000 iteracijah. Podatke za ta razdelek najdete v razdelku z Valentine.xlsx, prikazanega na sliki 60-4. Imena obsegov v celicah B1:B11 dodelite celicam C1:C11. Obsegu celic G3:H6 je dodeljeno iskanje imena. Naši parametri prodajnih cen in stroškov so vneseni v celice C4:C6.

V celico C1 lahko vnesete preskusno proizvodno količino (v tem primeru 40.000). Nato ustvarite naključno število v celici C2 s formulo =RAND(). Kot smo že opisali, simulirate zahtevo po kartici v celici C3 s formulo VLOOKUP(rand,lookup,2). (V formuli VLOOKUP je »rand « ime celice, ki je dodeljeno celici C3, ne funkciji RAND.)

Število prodanih enot je manjše od naše proizvodne količine in povpraševanja. V celici C8 izračunate naše prihodke s formulo MIN(proizvedeno,povpraševanje)*unit_price. V celici C9 izračunate skupne proizvodne stroške s formulo, ki je bila izdelana*unit_prod_cost.

Če izdelamo več kartic, kot jih je v povpraševanju, število enot, ki so preotečene, je enako proizvodnji minus povpraševanje; v nasprotnem primeru ne bodo preostale nobene enote. Stroške odstranjevanja izračunamo v celici C10 s formulo unit_disp_cost*IF(proizvedeno>,proizvedeno–povpraševanje,0). Na koncu v celici C11 naš dobiček izračunamo kot prihodek – total_var_cost-total_disposing_cost.

Želimo si učinkovit način za večkratno pritisniti F9 (na primer 1000) za vsako proizvodno količino in odšteti naš pričakovan dobiček za vsako količino. Ta situacija je primer, v katerem nam je v pomoč dvoste dostopna podatkovna tabela. (Za podrobnosti o podatkovnih tabelah glejte 15. poglavje »Analiza občutljivosti s podatkovno tabelo«.) Podatkovna tabela, uporabljena v tem primeru, je prikazana na sliki 60-5.

V obseg celic A16:A1015 vnesite števila od 1 do 1000 (kar ustreza našim 1000 preskusom). Te vrednosti lahko preprosto ustvarite tako, da v celico A16 vnesete 1 . Izberite celico, nato pa na zavihku Osnovno v skupini Urejanje kliknite Polnilo in izberite Nizi, da prikažete pogovorno okno Nizi. V pogovornem oknu Nizi, prikazano na sliki 60-6, vnesite vrednost koraka 1 in prekinitveno vrednost 1000. V območju Niz v izberite možnost Stolpci in nato kliknite V redu. Števila od 1 do 1000 bodo vnesena v stolpec A z začetkom v celici A16.

Nato v celice B15:E15 vnesemo naše možne količine proizvodnje (10.000, 20.000, 40.000, 60.000). Dobiček želimo izračunati za vsako poskusno številko (od 1 do 1000) in vsako proizvodno količino. Na formulo za dobiček (izračunano v celici C11) v zgornji levi celici naše podatkovne tabele (A15) se sklicujemo tako, da vnesete =C11.

Zdaj lahko ukanjamo Excel, da simuliranje 1000 ponovitev povpraševanju po vsaki proizvodni količini. Izberite obseg tabele (A15:E1014) in nato v skupini Podatkovna orodja na zavihku Podatki kliknite Analiza »Kaj če« in nato izberite Podatkovna tabela. Če želite nastaviti dvos smerno podatkovno tabelo, izberite našo proizvodno količino (celico C1) kot vhodno celico vrstice in izberite poljubno prazno celico (izbrali smo celico I14) kot vhodno celico stolpca. Ko kliknete V redu, Excel simulira 1000 vrednosti zahtev za vsako količino naročil.

Če želite razumeti, zakaj to deluje, razmislite o vrednostih, ki jih je postavila podatkovna tabela v obsegu celic C16:C1015. Za vsako od teh celic bo Excel uporabil vrednost 20.000 v celici C1. V C16 se vrednost vhodne celice stolpca 1 premakne v prazno celico, naključno število v celici C2 pa se znova izračuna. Ustrezen dobiček se nato zapiše v celico C16. Nato je vhodna vrednost celice stolpca 2 postavljena v prazno celico, naključno število v celici C2 pa se znova preračuna. Ustrezen dobiček je vnesen v celico C17.

Če kopiramo formulo AVERAGE(B16:B1015) iz celice B13 v C13:E13, izračunamo povprečni simuliran dobiček za vsako proizvodno količino. S kopiranjem formule STDEV(B16:B1015) iz celice B14 v C14:E14 izračunamo standardni odklon simuliranega dobička za vsako količino naročil. Vsakič, ko pritisnemo F9, se za vsako količino naročila simulira 1000 ponovitev zahtev. Izdelava 40.000 kartic vedno donosi največji pričakovan dobiček. Zato se zdi, da je izdelava 40.000 kartic pravilna odločitev.

Vpliv tveganja na našo odločitev      Če smo namesto 40.000 kartic proizvedli 20.000 vizitk, naš pričakovan dobiček spusti približno 22 odstotkov, naše tveganje (izmerjeno s standardnim odklonom dobička) pa se spusti skoraj 73 odstotkov. Zato, če smo izjemno proti tveganju, je izdelava 20.000 kartic prava odločitev. Mimogrede, ki proizvaja 10.000 kartic vedno standardnega odklona 0 kartice, ker če smo proizvodnjo 10.000 kartic, bomo vedno prodati vse od njih brez leftovers.

Interval zaupanja za srednjo vrednost dobička      Naravno vprašanje zastaviti v tem položaju je, v kakšnem intervalu smo 95 odstotkov prepričani, da bo res pomeni dobiček padec? Ta interval se imenuje 95-odstotni interval zaupanja za srednjo vrednost dobička. 95-odstotni interval zaupanja za srednjo vrednost katerega koli simulacijskega rezultata se izračuna s to formulo:

V celici J11 izračunate spodnjo omejitev za 95-odstotni interval zaupanja pri poslovnem dobičku, ko je 40.000 koledarjev izdelanih s formulo D13–1,96*D14/SQRT(1000). V celici J12 izračunate zgornjo mejo za naš 95-odstotni interval zaupanja s formulo D13+1,96*D14/SQRT(1000). Ti izračuni so prikazani na sliki 60-7.

95 odstotkov smo prepričani, da je naš skupni dobiček, ko je naročenih 40.000 koledarjev, med 56.687 $ in 62.589 $.

1. Gmc trgovci meni, da bo povpraševanje po letu 2005 Odposlalec običajno porazdeljena s srednjo 200 in standardni odklon 30. Njegov strošek prejemanja odposlanega je 25.000 $ in proda odposlanega za 40.000 $. Polovico odposlanih odposlanih, ki niso prodani po polni ceni, je mogoče prodati za 30.000 $. Razmišlja o naročanju 200, 220, 240, 260, 280 ali 300 Envoys. Koliko jih naj naroči?

2. Majhna supermarket poskuša ugotoviti, koliko izvodov revije Ljudje naročiti vsak teden. Menijo, da njihovo povpraševanje Ljudje urejajo te diskretne naključne spremenljivke:

   Povpraševanje	verjetnost
   15	0,10
   20	0.20
   25	0.30
   30	0,25
   35	0,15

3. Supermarket plača 1,00 USD za vsak izvod izvoda Ljudje in ga proda za 1,95 USD. Vsako neprodajono kopijo je mogoče vrniti za 0,50 USD. Koliko kopij izvodov Ljudje bi bilo treba shraniti?