Úvod do simulácie Monte Carla v Exceli

Tento článok vychádza z článku Wayne L. Winston, ktorý vytvoril Analýza údajov v Microsoft Exceli a podnikové modelovanie .

Prehľad

• Kto používa simuláciu Monte Carlo?
• Čo sa stane, ak do bunky zadáte =RAND()?
• Ako možno simulovať hodnoty diskrétnej náhodnej premennej?
• Ako možno simulovať hodnoty normálnej náhodnej premennej?
• Ako môže spoločnosť, ktorá vyrába pozdravy, určiť, koľko pohľadníc vyrobiť?

Radi by sme presne odhadli pravdepodobnosť 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 prehrať na počítači tisíckrát.

V nasledujúcich piatich kapitolách uvidíte príklady, ako môžete použiť Excel na simulácie Monte Carla.

Kto používa simuláciu Monte Carlo?

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

• General Motors, Proctor and Gamble, Pfizer Bristol-Myers Squibb a Eli Lilly používajú simuláciu na odhad priemerného výnosu a rizikového faktora nových produktov. V spoločnosti GM tieto informácie používa generálny riaditeľ na určenie, ktoré produkty prichádzajú na trh.
• GM používa simuláciu na činnosti, ako je predpovedanie čistého príjmu spoločnosti, 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ého kurzu).
• Spoločnosť Lilly používa simuláciu na určenie optimálnej kapacity závodu pre každý liek.
• Proctor and Gamble používa simuláciu na modelovanie a optimálne zabezpečenie devízového rizika.
• Spoločnosť Sears pomocou simulácie určuje, koľko jednotiek každého produktového radu by sa malo objednať od dodávateľov – napríklad počet párov nohavíc Dockers, ktoré by si mali tento rok objednať.
• Ropné a farmaceutické spoločnosti používajú simuláciu na ocenenie "skutočných možností", ako je hodnota opcie na rozšírenie, zúženie alebo odloženie projektu.
• Finanční plánovači používajú simuláciu Monte Carlo na určenie optimálnych investičných stratégií pre odchod svojich klientov do dôchodku.

Čo sa stane, ak do bunky zadáte =RAND()?

Keď do bunky zadáte vzorec =RAND(), získate číslo, ktoré s rovnakou pravdepodobnosťou nadobudne akúkoľvek hodnotu medzi 0 a 1. Približne v 25 percentách prípadov by ste teda mali dostať číslo menšie alebo rovné 0,25; Približne v 10 percentách prípadov by malo byť číslo minimálne 0,90, a tak ďalej. Fungovanie funkcie RAND si môžete ukázať pomocou Randdemo.xlsx súboru znázorneného na obrázku 60-1.

Najprv skopírujte vzorec =RAND() z bunky C3 do bunky C4:C402. Potom pomenujete rozsah Údaje v rozsahu C3:C402. Potom môžete v stĺpci F sledovať priemer 400 náhodných čísel (bunka F2) a pomocou funkcie COUNTIF určiť zlomky v rozsahu od 0 do 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čítajú. Všimnite si, že priemer 400 čísel je vždy približne 0,5 a približne 25 % výsledkov sa nachádza v intervaloch po 0,25. Tieto výsledky sú konzistentné s definíciou náhodného čísla. Všimnite si tiež, že hodnoty generované funkciou RAND v rôznych bunkách sú nezávislé. Ak je napríklad náhodné číslo vygenerované v bunke C3 veľké číslo (napríklad 0,99), nehovorí nám to nič o hodnotách ostatných generovaných náhodných čísel.

Ako možno simulovať hodnoty diskrétnej náhodnej premennej?

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

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

Ako je možné, aby Excel mnohokrát simuloval 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 požiadavka 10 000 sa vyskytne v 10 percentách času, atď.

Dopyt	Náhodne priradené čí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 demonštrovať simuláciu dopytu, pozrite sa na Discretesim.xlsx súboru, ktorý je znázornený na obrázku 60-2 na ďalšej strane.

Book Kľúčom k našej simulácii je použitie náhodného čísla na spustenie vyhľadávania z rozsahu tabuľky F2:G5 (s názvom vyhľadávanie). Náhodné čísla väčšie alebo rovné 0 a menšie ako 0,10 prinesú dopyt 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ú dopyt 60 000. 400 náhodných čísel vygenerujete skopírovaním vzorca RAND() z bunky C3 do rozsahu C4:C402. Potom vygenerujete 400 pokusov alebo iterácií dopytu kalendára skopírovaním vzorca VLOOKUP(C3;vyhľadávanie;2) z B3 do B4:B402. Tento vzorec zabezpečí, že ľubovoľné náhodné číslo menšie ako 0,10 vygeneruje dopyt 10 000, ľubovoľné náhodné číslo medzi 0,10 a 0,45 vygeneruje dopyt 20 000 a tak ďalej. V rozsahu buniek F8:F11 pomocou funkcie COUNTIF určte zlomok 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 sa budú blížiť k predpokladaným pravdepodobnosťám dopytu.

Ako možno simulovať hodnoty normálnej náhodnej premennej?

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

Book 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ť ich ako mean asigma.) Skopírovaním vzorca =RAND() z bunky C4 do bunky C5:C403 sa vygeneruje 400 rôznych náhodných čísel. Vzorec NORMINV(C4;stred;sigma) sa po skopírovaní z bunky B4 do B5:B403 vygeneruje 400 rôznych skúšobných hodnôt z 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 blízko 10 000.

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

Ako môže spoločnosť, ktorá vyrába pozdravy, určiť, koľko pohľadníc vyrobiť?

V tejto časti uvidíte, ako možno simuláciu Monte Carla použiť ako nástroj rozhodovania. Predpokladajme, že dopyt po valentínskom pozdrave sa riadi nasledujúcou samostatnou náhodnou premennou:

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

Pohľadnica sa predáva za 4,00 USD a variabilné náklady na výrobu každého pozdravu sú 1,50 EUR. Zvyšky kariet je potrebné zlikvidovať za cenu 0,20 USD za jednu kartu. Koľko vizitiek treba vytlačiť?

V podstate simulujeme každé možné množstvo výroby (10 000, 20 000, 40 000 alebo 60 000) mnohokrát (napríklad 1000 iterácií). Potom určíme, ktoré množstvo objednávky prinesie maximálny priemerný zisk za 1 000 iterácií. Údaje pre túto časť nájdete v súbore Valentine.xlsx znázornené na obrázku 60-4. Názvy rozsahov v bunkách B1:B11 priradíte k bunkám C1:C11. Rozsahu buniek G3:H6 sa priradí vyhľadávanie názvov. Parametre predajnej ceny a nákladov sa zadávajú do buniek C4:C6.

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

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

Ak vyrobíme viac kariet, ako je v dopyte, počet zostávajúcich jednotiek sa rovná výrobe mínus dopyt; inak nezostane žiadne jednotky. Náklady na likvidáciu vypočítame v bunke C10 pomocou vzorca unit_disp_cost*IF(vyrábaný>dopyt;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 viackrát (napríklad 1000) pre každé produkčné množstvo a spočítať náš očakávaný zisk pre každé množstvo. V tejto situácii nám príde na pomoc obojsmerná tabuľka údajov. (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.

Book Do rozsahu buniek A16:A1015 zadajte čísla od 1 do 1 000 (zodpovedajúce našim 1 000 pokusom). Tieto hodnoty jednoducho vytvoríte tak, že začnete zadaním čísla 1 do bunky A16. Vyberte bunku a potom na karte Domov v skupine Úpravy kliknite na položku Vyplniť a výberom Rady zobrazte dialógové okno Rad. V dialógovom okne Rady na obrázku 60-6 zadajte hodnotu kroku 1 a hodnotu konca 1000. V oblasti Rady V vyberte možnosť Stĺpce a potom kliknite na tlačidlo OK. Čísla 1 – 1000 sa zadajú do stĺpca A od bunky A16.

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

Teraz sme pripravení oklamať Excel tak, aby simuloval 1 000 iterácií dopytu po každom výrobnom množstve. Vyberte rozsah tabuľky (A15:E1014), potom v skupine Nástroje pre údaje na karte Údaje kliknite na položku Analýza potenciálnych riešení a potom vyberte položku Tabuľka údajov. Ak chcete nastaviť obojsmernú tabuľku údajov, vyberte ako vstupnú bunku riadku množstvo výroby (bunka C1) 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ávky.

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

Skopírovaním vzorca AVERAGE(B16:B1015) z bunky B13 do rozsahu C13:E13 sa vypočíta priemerný simulovaný zisk pre každé množstvo výroby. Skopírovaním vzorca STDEV(B16:B1015) z bunky B14 do buniek C14:E14 vypočítame smerodajnú odchýlku našich simulovaných ziskov pre každé množstvo objednávky. Pri každom stlačení klávesu F9 sa pre každé množstvo objednávky simuluje 1000 iterácií dopytu. Výroba 40 000 kariet vždy prinesie najväčší očakávaný zisk. Preto sa zdá, že výroba 40 000 kariet je správne rozhodnutie.

Vplyv rizika na naše rozhodnutie Ak by sme vyrobili 20 000 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 máme extrémnu averziu k riziku, výroba 20 000 kariet môže byť správnym rozhodnutím. Mimochodom, výroba 10 000 kariet má vždy smerodajnú odchýlku 0 kariet, pretože ak vyrobíme 10 000 kariet, vždy predáme všetky bez zvyškov.

Interval spoľahlivosti pre stredný zisk Prirodzenou otázkou, ktorú si treba položiť v tejto situácii, je, do akého intervalu sme si na 95 percent istí, že skutočne priemerný zisk klesne? Tento interval sa nazýva 95-percentný interval spoľahlivosti pre stredný zisk. 95-percentný interval spoľahlivosti pre strednú hodnotu ľubovoľného výstupu simulácie sa vypočíta pomocou nasledujúceho vzorca:

V bunke J11 vypočítate dolnú hranicu 95-percentného intervalu spoľahlivosti pre stredný zisk, ak je vytvorených 40 000 kalendárov pomocou vzorca D13–1,96*D14/SQRT(1000). V bunke J12 vypočítate hornú hranicu 95-percentného intervalu spoľahlivosti pomocou vzorca D13+1,96*D14/SQRT(1000). Tieto výpočty sú znázornené na obrázku 60-7.

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

Problémy

1. Predajca GMC verí, že dopyt po vyslancoch z roku 2005 bude normálne rozdelený so strednou hodnotou 200 a smerodajnou odchýlkou 30. Jeho cena na prijatie vyslanca je 25 000 dolárov a vyslanca predáva za 40 000 dolárov. Polovica všetkých vyslancov, ktorí sa nepredajú za plnú cenu, sa môže predať za 30 000 dolárov. Zvažuje, že nariadi 200, 220, 240, 260, 280 alebo 300 vyslancov. Koľko by si mal objednať?

2. Malý supermarket sa snaží určiť, koľko výtlačkov časopisu People by si mali každý týždeň objednať. Veria, že ich dopyt po People sa riadi nasledujúcou diskrétnou náhodnou premennou:

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

3. Supermarket platí 1,00 dolára za každú kópiu People a predáva ju za 1,95 dolára. Každú nepredanú kópiu môžete vrátiť za 0,50 $. Koľko kópií People by si mal obchod objednať?

Potrebujete ďalšiu pomoc?

Vždy sa môžete opýtať odborníka v komunite Excel Tech Community alebo získať podporu v komunitách.