Úvod do simulácie Monte Carlo v Exceli

• Kto používa simuláciu Monte Carlo?

• Čo sa stane po zadaní hodnoty =RAND() do bunky?

• Ako môžete simulovať hodnoty diskrétnej náhodnej premennej?

• Ako môžete simulovať hodnoty normálnej náhodnej premennej?

• Ako môže spoločnosť s pozdravom určiť počet vizitiek, ktoré sa majú vytvoriť?

Radi by sme presne odhadli pravdepodobnosti neistých udalostí. Aká je napríklad pravdepodobnosť, že hotovostné toky nového produktu budú mať kladnú čistú súčasnú hodnotu (NPV)? Aký je rizikový faktor nášho investičného portfólia? Simulácia Monte Carlo nám umožňuje modelovať situácie, ktoré predstavujú neistotu a potom ich tisícekrát hrajú na počítači.

V nasledujúcich piatich kapitolách uvidíte príklady použitia Excelu na vykonávanie simulácií v Monte Carle.

Mnohé spoločnosti používajú simuláciu Monte Carlo ako dôležitú súčasť svojho rozhodovacieho procesu. Tu je niekoľko príkladov.

• General Motors, Proctor a Gamble, Pfizer, Bristol-Myers Squibb a Eli Lilly používajú simuláciu na odhad priemerného výnosu aj rizikového faktora nových produktov. V gm tieto informácie používa generálny riaditeľ na určenie výrobkov, ktoré prichádzajú na trh.

• GM používa simuláciu pre činnosti, ako je prognóza čistého príjmu pre spoločnosť, predpovedanie štrukturálnych a nákupných nákladov a určovanie jej náchylnosti na rôzne druhy rizika (ako sú zmeny úrokových sadzieb a kolísanie výmenných kurzov).

• Lilly používa simuláciu na určenie optimálnej kapacity rastlín pre každý liek.

• Proctor a Gamble používajú simuláciu na modelovanie a optimálne zabezpečenie devízového rizika.

• Sears používa simuláciu určiť, koľko jednotiek každého radu produktov by mali byť objednané od dodávateľov- napríklad počet párov dockers nohavice, ktoré by mali byť objednané v tomto roku.

• Ropné a farmaceutické spoločnosti používajú simuláciu na hodnotu "skutočných možností", ako je hodnota možnosti rozšíriť, uzavrieť zmluvu alebo odložiť projekt.

• Finanční plánovači používajú simuláciu Monte Carlo na určenie optimálnych investičných stratégií na odchod do dôchodku svojich klientov.

Keď do bunky zadáte vzorec =RAND( ), dostanete číslo, ktoré bude mať rovnakú pravdepodobnosť, že bude predpokladať ľubovoľnú hodnotu v rozsahu 0 až 1. To znamená, že približne 25 percent času by ste mali dostať číslo menšie alebo rovné 0,25; približne 10 percent času by ste mali dostať číslo, ktoré je aspoň 0,90, a tak ďalej. Ak chcete ukázať, ako funkcia RAND funguje, pozrite sa na súbor Randdemo.xlsx, ktorý je znázornený na obrázku 60-1.

Najprv skopírujte vzorec =RAND() z bunky C3 do bunky C4:C402. Potom pomenujte rozsah C3:C402 Údaje. Potom môžete v stĺpci F sledovať priemer 400 náhodných čísel (bunka F2) a pomocou funkcie COUNTIF určiť zlomky medzi 0 a 0,25, 0,25 a 0,50, 0,50 a 0,75 a 0,75 a 1. Po stlačení klávesu F9 sa náhodné čísla prepočítavajú. Všimnite si, že priemer 400 čísel je vždy približne 0,5 a že približne 25 percent výsledkov je v intervaloch 0,25. Tieto výsledky sú v súlade s definíciou náhodného čísla. Všimnite si tiež, že hodnoty generované funkciami RAND v rôznych bunkách sú nezávislé. Ak je napríklad náhodné číslo vygenerované v bunke C3 veľkým číslom (napríklad 0,99), nehovorí nám nič o hodnotách ostatných vygenerovaných náhodných čísel.

Predpokladajme, že dopyt po kalendári sa riadi nasledujúcou diskrétnou náhodnou premennou:

Dopytu	Pravdepodobnosť
10 000	0,10
20 000	0.35
40,000	0,3
60 000	0,25

Ako môžeme Excel viackrát prehrať alebo simulovať tento dopyt po kalendároch? Trik spočíva v priradení každej možnej hodnoty funkcie RAND k možnému dopytu po kalendároch. Nasledujúce priradenie zabezpečí, že dopyt po 10 000 bude 10 percent času a tak ďalej.

Dopytu	Priradené náhodné číslo
10 000	Menšie ako 0,10
20 000	Väčšie alebo rovné 0,10 a menšie ako 0,45
40,000	Väčšie alebo rovné 0,45 a menšie ako 0,75
60 000	Väčšie alebo rovné 0,75

Ak chcete preukázať simuláciu dopytu, pozrite sa na súbor Discretesim.xlsx, ako je znázornené na obrázku 60-2 na ďalšej strane.

Kľúčom k našej simulácii je použiť náhodné číslo na spustenie vyhľadávania z rozsahu tabuliek F2:G5 (pomenované vyhľadávanie). Náhodné čísla väčšie alebo rovné 0 a menšie ako 0,10 prinesú požiadavku 10 000; náhodné čísla väčšie alebo rovné 0,10 a menšie ako 0,45 prinesú dopyt 20 000; náhodné čísla väčšie alebo rovné 0,45 a menšie ako 0,75 prinesú dopyt 40 000; a náhodné čísla väčšie alebo rovné 0,75 prinesú požiadavku 60 000. Vygenerujete 400 náhodných čísel skopírovaním vzorca RAND()z bunky C3 do bunky C4:C402. Potom vygenerujete 400 skúšobných verzií alebo iterácií dopytu v kalendári skopírovaním vzorca VLOOKUP(C3; vyhľadávanie;2) z bunky B3 do rozsahu B4:B402. Tento vzorec zabezpečí, že akékoľvek náhodné číslo menšie ako 0,10 vygeneruje dopyt 10 000, akékoľvek náhodné číslo v rozsahu od 0,10 do 0,45 vygeneruje dopyt 20 000 atď. V rozsahu buniek F8:F11 použite funkciu COUNTIF na určenie zlomku našich 400 iterácií, ktoré prinášajú každú požiadavku. Keď stlačíme kláves F9 na prepočítanie náhodných čísel, simulované pravdepodobnosti sú blízko našej predpokladanej pravdepodobnosti dopytu.

Ak zadáte do ktorejkoľvek bunky vzorec NORMINV(rand();mu;sigma),vygenerujete simulovanú hodnotu normálnej náhodnej premennej so strednou mu a smerodajnou odchýlkou sigma. Tento postup je znázornený v Normalsim.xlsx súboru znázornenom na obrázku 60-3.

Predpokladajme, že chceme simulovať 400 pokusov alebo iterácií pre normálnu náhodnú premennú so strednou hodnotou 40 000 a smerodajnou odchýlkou 10 000. (Tieto hodnoty môžete zadať do buniek E1 a E2 a pomenovať tieto bunky strednou hodnotou a sigma.) Skopírovaním vzorca =RAND() z bunky C4 do bunky C5:C403 sa vygeneruje 400 rôznych náhodných čísel. Kopírovanie vzorca NORMINV(C4;stred;sigma) z B4 do rozsahu B5:B403 generuje 400 rôznych skúšobných hodnôt od normálnej náhodnej premennej so strednou hodnotou 40 000 a smerodajnou odchýlkou 10 000. Keď stlačíme kláves F9 na prepočítanie náhodných čísel, stredná hodnota zostane blízko 40 000 a smerodajná odchýlka sa blíži k 10 000.

V podstate pre náhodné číslo x vzorec NORMINV(p;mu;sigma) vygeneruje p-typercentil normálnej náhodnej premennej so strednou hodnotou mu a smerodajnou odchýlkou sigma. Náhodné číslo 0,77 v bunke C4 (pozri obrázok 60-3) napríklad vygeneruje v bunke B4 približne 77. percentil normálnej náhodnej premennej so strednou hodnotou 40 000 a smerodajnou odchýlkou 10 000.

V tejto časti uvidíte, ako možno simuláciu Monte Carlo použiť ako rozhodovací nástroj. Predpokladajme, že dopyt po valentínskej karte sa riadi nasledujúcou diskrétnou náhodnou premennou:

Dopytu	Pravdepodobnosť
10 000	0,10
20 000	0.35
40,000	0,3
60 000	0,25

Pozdrav sa predáva za 4,00 $ a variabilné náklady na výrobu každej karty sú 1,50 USD. Zvyšky kariet je potrebné zlikvidovať za cenu 0,20 $ za kartu. Koľko kariet je potrebné vytlačiť?

V podstate simulujeme každé možné výrobné množstvo (10 000, 20 000, 40 000 alebo 60 000) mnohokrát (napríklad 1 000 iterácií). Potom určíme, ktoré množstvo objednávok prináša maximálny priemerný zisk nad 1 000 iterácií. Údaje pre túto sekciu nájdete v Valentine.xlsx súboru, ako je znázornené na obrázku 60-4. Názvy rozsahov v bunkách B1:B11 sa priradia k bunkám C1:C11. Rozsah buniek G3:H6 má priradené vyhľadávanie názvov. Parametre predajnej ceny a nákladov sa zadávajú do buniek C4:C6.

Do bunky C1 môžete zadať skúšobné výrobné množstvo (v tomto príklade 40 000). Potom vytvorte náhodné číslo v bunke C2 pomocou vzorca =RAND(). Ako už bolo popísané, simulujete dopyt po karte v bunke C3 pomocou vzorca VLOOKUP(rand,vyhľadávanie;2). (Vo vzorci VLOOKUP je rand názov bunky priradený k bunke C3, nie funkcia RAND.)

Počet predaných jednotiek je menší z nášho výrobného množstva a dopytu. V bunke C8 vypočítate naše výnosy pomocou vzorca MIN(vyrobené,dopyt)*unit_price. V bunke C9 vypočítate celkové výrobné náklady pomocou vzorca*unit_prod_cost.

Ak vyrábame viac kariet, ako je dopyt, počet zostávajúcich jednotiek sa rovná výrobe mínus dopyt; v opačnom prípade sa nezostali žiadne jednotky. Vypočítame naše náklady na vyradenie v bunke C10 pomocou vzorca unit_disp_cost*IF(vyrobené>dopytu,vyrobené–dopyt;0). Nakoniec v bunke C11 vypočítame náš zisk ako výnos – total_var_cost-total_disposing_cost.

Chceli by sme efektívny spôsob, ako stlačiť kláves F9 mnohokrát (napríklad 1 000) pre každé produkčné množstvo a skonsolvovať náš očakávaný zisk pre každé množstvo. Táto situácia je taká, v ktorej sa nám obojsmerná tabuľka údajov zachráni. (Podrobnosti o tabuľkách údajov nájdete v kapitole 15 Analýza citlivosti s tabuľkami údajov.) Tabuľka údajov použitá v tomto príklade je znázornená na obrázku 60-5.

Do rozsahu buniek A16:A1015 zadajte čísla 1 – 1 000 (zodpovedajúce našim 1 000 pokusom). Jedným zo jednoduchých spôsobov, ako vytvoriť tieto hodnoty, je začať zadaním hodnoty 1 do bunky A16. Vyberte bunku a potom na karte Domov v skupine Úpravy kliknite na položku Vyplniť a výberom položky Rad zobrazte dialógové okno Rad . V dialógovom okne Rad zobrazený na obrázku 60-6 zadajte hodnotu kroku 1 a hodnotu zastavenia 1 000. V oblasti Rad v vyberte možnosť Stĺpce a potom kliknite na tlačidlo OK. Čísla 1 – 1 000 sa zadajú do stĺpca A počnúc bunkou A16.

Ďalej zadáme do buniek B15:E15 naše možné výrobné množstvá (10 000, 20 000, 40 000, 60 000). Chceme vypočítať zisk pre každé skúšobné číslo (1 až 1 000) a každé výrobné množstvo. Vzorec pre zisk (vypočítaný v bunke C11) v ľavej hornej bunke tabuľky údajov (A15) odkazujeme zadaním vzorca =C11.

Teraz sme pripravení oklamať Excel, aby simuloval 1 000 iterácií dopytu po každom výrobnom množstve. Vyberte rozsah tabuľky (A15:E1014) a potom v skupine Nástroje pre údaje na karte Údaje kliknite na položku Analýza hypotéz a potom vyberte položku Tabuľka údajov. Ak chcete nastaviť obojsmernú tabuľku údajov, vyberte produkčné množstvo (bunku C1) ako vstupnú bunku riadka a ako vstupnú bunku stĺpca vyberte ľubovoľnú prázdnu bunku (vybrali sme bunku I14). Po kliknutí na tlačidlo OK Excel simuluje 1 000 hodnôt dopytu pre každé množstvo objednávok.

Ak chcete pochopiť, prečo to funguje, zvážte hodnoty, ktoré tabuľka údajov umiestnila do rozsahu buniek C16:C1015. Pre každú z týchto buniek excel použije hodnotu 20 000 v bunke C1. V bunke C16 sa hodnota vstupnej bunky v stĺpci 1 umiestni do prázdnej bunky a náhodné číslo v bunke C2 sa prepočíta. Príslušný zisk sa potom zaznamená v bunke C16. Potom sa vstupná hodnota bunky stĺpca 2 umiestni do prázdnej bunky a náhodné číslo v bunke C2 sa znova prepočíta. Zodpovedajúci zisk sa zadá do bunky C17.

Skopírovaním vzorca AVERAGE(B16:B1015) z bunky B13 do bunky C13:E13 vypočítame priemerný simulovaný zisk pre každé produkčné množstvo. Skopírovaním vzorca STDEV(B16:B1015) z bunky B14 do bunky C14:E14 vypočítame smerodajnú odchýlku simulovaného zisku pre každé množstvo objednávok. Pri každom stlačení klávesu F9 sa pre každé množstvo objednávok simuluje 1000 iterácií dopytu. Výroba 40 000 kariet vždy prináša najväčší očakávaný zisk. Preto sa zdá, že produkovať 40.000 kariet je správne rozhodnutie.

Vplyv rizika na naše rozhodnutie      Ak by sme vyrobili 20 000 kariet namiesto 40 000 kariet, náš očakávaný zisk klesne približne o 22 percent, ale naše riziko (merané smerodajnou odchýlkou zisku) klesne takmer o 73 percent. Preto, ak sme veľmi proti riziku, produkovať 20.000 kariet by mohlo byť správne rozhodnutie. Mimochodom, produkovať 10.000 kariet má vždy smerodajnú odchýlku 0 kariet, pretože ak budeme vyrábať 10.000 kariet, budeme vždy predávať všetky z nich bez zvyškov.

Interval spoľahlivosti pre stredný zisk      Prirodzená otázka sa opýtať v tejto situácii je, do ktorého intervalu sme 95 percent istý, že skutočný priemerný zisk bude klesať? Tento interval sa nazýva 95-percentný interval spoľahlivosti pre stredný zisk. Interval spoľahlivosti 95 % pre strednú hodnotu akéhokoľvek simulačného výstupu sa vypočíta pomocou nasledujúceho vzorca:

V bunke J11 vypočítate dolný limit intervalu spoľahlivosti 95 percent pre stredný zisk, keď sa vytvára 40 000 kalendárov pomocou vzorca D13–1,96*D14/SQRT(1000). V bunke J12 vypočítate hornú hranicu pre náš 95-percentný interval spoľahlivosti pomocou vzorca D13+1,96*D14/SQRT(1000). Tieto výpočty sú zobrazené na obrázku 60 -7.

Sme si na 95 percent istí, že náš priemerný zisk pri objednaní 40 000 kalendárov je medzi 56 687 a 62 589 USD.

1. Predajca GMC sa domnieva, že dopyt po vyslancoch za rok 2005 bude normálne rozdelený so strednou hodnotou 200 a smerodajnou odchýlkou 30. Jeho náklady na prijatie vyslanca sú 25.000 dolárov, a on predáva vyslanca za 40.000 dolárov. Polovica všetkých vyslancov nepredávaných za plnú cenu môže byť predaná za 30.000 dolárov. Zvažuje objednanie 200, 220, 240, 260, 280 alebo 300 vyslancov. Koľko by si mal objednať?

2. Malý supermarket sa snaží zistiť, koľko kópií časopisu Ľudia by si mali objednať každý týždeň. Veria, že ich dopyt po Ľudia sa riadi nasledujúcou diskrétnou náhodnou premennou:

   Dopytu	Pravdepodobnosť
   15	0,10
   20	0.20
   25	0.30
   30	0,25
   35	0,15

3. Supermarket platí 1,00 dolárov za každú kópiu Ľudia a predáva ju za 1,95 dolárov. Každá nepredaná kópia sa môže vrátiť za 0,50 USD. Koľko kópií Ľudia by mala objednávka ukladacieho priestoru?