Úvod do simulace Monte Carlo v Excelu

Tento článek byl převzat z knihy Microsoft Excel Data Analysis and Business Modeling od Wayna L. Winstona.

Přehled

• Kdo používá simulaci Monte Carlo?
• Co se stane, když do buňky zadáte =NÁHČÍSLO()?
• Jak můžete simulovat hodnoty diskrétní náhodné veličiny?
• Jak můžete simulovat hodnoty normální náhodné veličiny?
• Jak může společnost vyrábějící pohlednice určit, kolik přání vyrobit?

Rádi bychom přesně odhadli pravděpodobnosti nejistých událostí. Jaká je například pravděpodobnost, že peněžní toky nového produktu budou mít kladnou čistou současnou hodnotu? Jaký je rizikový faktor našeho investičního portfolia? Simulace Monte Carlo nám umožňuje modelovat situace, které představují nejistotu, a pak si je tisíckrát přehrát na počítači.

V dalších pěti kapitolách uvidíte příklady, jak můžete pomocí Excelu provádět simulace Monte Carlo.

Kdo používá simulaci Monte Carlo?

Mnoho společností používá simulaci Monte Carlo jako důležitou součást svého rozhodovacího procesu. Tady je několik příkladů.

• General Motors, Proctor and Gamble, Pfizer, Bristol-Myers Squibb a Eli Lilly používají simulaci k odhadu průměrné návratnosti i rizikového faktoru nových produktů. Ve společnosti GM tyto informace používá generální ředitel k určení, které produkty přicházejí na trh.
• GM používá simulaci pro činnosti, jako je předpovídání čistého příjmu společnosti, předpovídání strukturálních a nákupních nákladů a stanovení její náchylnosti k různým druhům rizik (jako jsou změny úrokových sazeb a kolísání směnných kurzů).
• Společnost Lilly používá simulaci k určení optimální kapacity závodu pro každé léčivo.
• Společnost Proctor and Gamble využívá simulaci k modelování a optimálnímu zajištění měnového rizika.
• Sears používá simulaci, aby určil, kolik kusů každé produktové řady by mělo být objednáno od dodavatelů – například počet párů kalhot Dockers, které by měly být objednány letos.
• Ropné a farmaceutické společnosti používají simulaci k ocenění "reálných možností", jako je hodnota opce na rozšíření, smrštění nebo odložení projektu.
• Finanční plánovači používají simulaci Monte Carlo k určení optimálních investičních strategií pro odchod svých klientů do důchodu.

Co se stane, když do buňky zadáte =NÁHČÍSLO()?

Zadáte-li do buňky vzorec =NÁHČÍSLO(), získáte číslo, u kterého je stejně pravděpodobné, že bude předpokládat jakoukoli hodnotu mezi 0 a 1. Přibližně ve 25 procentech případů byste tedy měli dostat číslo menší nebo rovno 0,25; Přibližně v 10 procentech případů byste měli dostat číslo, které je alespoň 0,90 a tak dále. Abychom si ukázali, jak funkce NÁHČÍSLO funguje, podívejme se na Randdemo.xlsx souborů znázorněnou na obrázku 60-1.

Nejprve zkopírujte z buňky C3 do C4:C402 vzorec =RAND(). Potom oblast pojmenujte C3:C402 Data. Pak můžete ve sloupci F sledovat průměr ze 400 náhodných čísel (buňka F2) a pomocí funkce COUNTIF určit zlomky, které jsou mezi 0 a 0,25, 0,25 a 0,50, 0,50 a 0,75 a 0,75 a 1. Když stisknete klávesu F9, přepočítají se náhodná čísla. Všimněte si, že průměr z těchto 400 čísel je vždy přibližně 0,5 a přibližně 25 procent výsledků je v intervalech 0,25. Tyto výsledky odpovídají definici náhodného čísla. Všimněte si také, že hodnoty generované funkcí NÁHČÍSLO v různých buňkách jsou nezávislé. Pokud je například náhodné číslo vygenerované v buňce C3 velké číslo (třeba 0,99), neříká nám nic o hodnotách ostatních vygenerovaných náhodných čísel.

Jak můžete simulovat hodnoty diskrétní náhodné veličiny?

Předpokládejme, že poptávka po kalendáři se řídí následující diskrétní náhodnou proměnnou:

Poptávka	Pravděpodobnost:
10 000	0,10
20 000	0.35
40,000	0,3
60 000	0,25

Jak můžeme využít nebo simulovat tuto poptávku po kalendářích mnohokrát? Vtip spočívá v tom, že každou možnou hodnotu funkce NÁHČÍSLO přidružíte k možné poptávce po kalendářích. Následující přiřazení zajistí, že poptávka 10 000 se vyskytne v 10 procentech případů atd.

Poptávka	Přiřazené náhodné číslo
10 000	Méně než 0,10
20 000	Větší nebo rovno 0,10 a menší než 0,45
40,000	Větší nebo rovno 0,45 a menší než 0,75
60 000	0,75 větší nebo rovno 0,75

Chcete-li demonstrovat simulaci poptávky, podívejte se na soubor Discretesim.xlsx, který je znázorněn na obrázku 60-2 na další stránce.

Klíčem k naší simulaci je použití náhodného čísla k zahájení vyhledávání z oblasti tabulky F2:G5 (pojmenované vyhledávání). Náhodná čísla větší nebo rovna 0 a menší než 0,10 vynesou požadavek 10 000; Náhodná čísla větší nebo rovna 0,10 a menší než 0,45 vynesou požadavek 20 000; Náhodná čísla větší nebo rovna 0,45 a menší než 0,75 vynesou požadavek 40 000; a náhodná čísla větší nebo rovna 0,75 vynesou požadavek 60 000. 400 náhodných čísel vygenerujete zkopírováním vzorce RAND() z buňky C3 do C4:C402. Potom vygenerujete 400 pokusů nebo iterací kalendáře zkopírováním vzorce SVYHLEDAT(C3;vyhledat;2)z buňky B4:B402. Tento vzorec zajistí, že jakékoli náhodné číslo menší než 0,10 vygeneruje požadavek 10 000, jakékoli náhodné číslo mezi 0,10 a 0,45 vygeneruje požadavek 20 000 atd. V oblasti buněk F8:F11 určete pomocí funkce COUNTIF zlomek z našich 400 iterací, které dávají každý požadavek. Když stiskneme klávesu F9 pro přepočet náhodných čísel, simulované pravděpodobnosti se blíží předpokládané pravděpodobnosti poptávky.

Jak můžete simulovat hodnoty normální náhodné veličiny?

Zadáte-li do libovolné buňky vzorec NORMINV(rand(),mu,sigma), vygenerujete simulovanou hodnotu normální náhodné veličiny se střední hodnotou mu a směrodatnou odchylkou sigma. Tento postup je znázorněn v souborovém Normalsim.xlsx, znázorněném na obrázku 60-3.

Předpokládejme, že chceme simulovat 400 pokusů (iterací) pro normální náhodnou proměnnou se střední hodnotou 40 000 a směrodatnou odchylkou 10 000. (Tyto hodnoty můžete zadat do buněk E1 a E2 a pojmenovat je střední hodnota asigma.) Zkopírováním vzorce =NÁHČÍSLO() z buňky C4 do C5:C403 se vygeneruje 400 různých náhodných čísel. Vzorec NORMINV(C4;střed;sigma) vygeneruje při zkopírování z buňky B4 do B5:B403 400 různých zkušebních hodnot z normální náhodné proměnné se střední hodnotou 40 000 a směrodatnou odchylkou 10 000. Když stiskneme klávesu F9, abychom přepočítali náhodná čísla, střední hodnota zůstane blízko 40 000 a směrodatná odchylka se blíží 10 000.

V podstatě pro náhodné číslo x vzorec NORMINV(p,mu,sigma) generuje p-tý percentil normální náhodné proměnné se střední hodnotou mu a směrodatnou odchylkou sigma. Například náhodné číslo 0,77 v buňce C4 (viz Obrázek 60-3) vygeneruje v buňce B4 přibližně 77. percentil normální náhodné proměnné se střední hodnotou 40 000 a směrodatnou odchylkou 10 000.

Jak může společnost vyrábějící pohlednice určit, kolik přání vyrobit?

V této části uvidíte, jak lze simulaci Monte Carlo použít jako nástroj pro rozhodování. Předpokládejme, že poptávka po valentýnském přání se řídí následující diskrétní náhodnou proměnnou:

Poptávka	Pravděpodobnost:
10 000	0,10
20 000	0.35
40,000	0,3
60 000	0,25

Přání se prodává za 4,00 USD a variabilní náklady na výrobu každého přání jsou 1,50 USD. Zbylé karty musí být zlikvidovány za cenu 0,20 USD za kartu. Kolik přání je třeba vytisknout?

V podstatě simulujeme každé možné výrobní množství (10 000, 20 000, 40 000 nebo 60 000) mnohokrát (například 1000 iterací). Poté určíme, které množství objednávky přináší maximální průměrný zisk během 1000 iterací. Data pro tuto sekci najdete v souborovém Valentine.xlsx, znázorněném na obrázku 60-4. Názvy oblastí v buňkách B1:B11 přiřadíte buňkám C1:C11. Oblasti buněk G3:H6 je přiřazen název vyhledávání. Naše parametry prodejní ceny a nákladů se zadávají do buněk C4:C6.

Do buňky C1 můžete zadat zkušební výrobní množství (v tomto příkladu 40 000). Dále vytvořte náhodné číslo v buňce C2 pomocí vzorce =RAND(). Jak jsme popsali dříve, simulujete poptávku po kartě v buňce C3 pomocí vzorce SVYHLEDAT(náhčíslo;vyhledat;2). (Ve vzorci SVYHLEDAT je název buňky C3 přiřazený k buňce C3 RAND , nikoli funkce NÁHČÍSLO.)

Počet prodaných kusů je menší než naše výrobní množství a poptávka. V buňce C8 vypočítáte naše výnosy pomocí vzorce MIN(vyrobeno;poptávka)*unit_price. V buňce C9 vypočítáte celkové výrobní náklady pomocí vzorce vyrobený*unit_prod_cost.

Pokud vyrobíme více karet, než je poptávka, počet zbylých jednotek se rovná výrobě minus poptávka; jinak nezbydou žádné jednotky. Náklady na odbyt vypočítáme v buňce C10 pomocí vzorce unit_disp_cost*KDYŽ(vytvořená>poptávka;vyrobená–poptávka;0). Nakonec v buňce C11 vypočítáme zisk jako výnosy total_var_cost-total_disposing_cost.

Chtěli bychom efektivní způsob, jak stisknout F9 mnohokrát (například 1000) pro každé výrobní množství a sečíst náš očekávaný zisk pro každé množství. V této situaci nám pomáhá obousměrná tabulka dat. (Podrobnosti o tabulkách dat viz kapitola 15 – "Analýza citlivosti pomocí tabulek dat".) Tabulka dat použitá v tomto příkladu je znázorněna na obrázku 60-5.

Do oblasti buněk A16:A1015 zadejte čísla 1–1000 (což odpovídá našim 1000 pokusům). Jedním ze snadných způsobů, jak tyto hodnoty získat, je začít zadáním 1 do buňky A16. Vyberte buňku a potom na kartě Domů ve skupině Úpravy klikněte na Výplň a výběrem možnosti Řady zobrazte dialogové okno Řady . V dialogovém okně Řady , znázorněném na obrázku 60-6, zadejte hodnotu kroku 1 a hodnotu ukončení 1000. V oblasti Vstupní řada vyberte možnost Sloupce a klepněte na tlačítko OK. Čísla 1–1000 se zadávají do sloupce A počínaje buňkou A16.

Dále do buněk B15:E15 zadáte naše možná výrobní množství (10 000, 20 000, 40 000, 60 000). Chceme vypočítat zisk pro každé zkušební číslo (1 až 1000) a každé výrobní množství. Na vzorec pro zisk (vypočítaný v buňce C11) v levé horní buňce naší tabulky dat (A15) odkazujeme zadáním =C11.

Nyní jsme připraveni oklamat Excel, aby simuloval 1000 iterací poptávky pro každé výrobní množství. Vyberte oblast tabulky (A15:E1014) a potom ve skupině Datové nástroje na kartě Data klikněte na položku Citlivostní analýza a potom vyberte položku Tabulka dat. Chcete-li nastavit obousměrnou tabulku dat, zvolte naše výrobní množství (buňka C1) jako vstupní buňku řádku a jako vstupní buňku sloupce vyberte libovolnou prázdnou buňku (zvolili jsme buňku I14). Po kliknutí na tlačítko OK aplikace Excel simuluje 1000 hodnot poptávky pro každé objednané množství.

Abyste pochopili, proč to funguje, podívejte se na hodnoty, které tabulka dat umístí do oblasti buněk C16:C1015. Excel pro každou z těchto buněk použije hodnotu 20 000 v buňce C1. V buňce C16 je vstupní buňka sloupce s hodnotou 1 umístěna do prázdné buňky a náhodné číslo v buňce C2 je přepočítáno. Odpovídající zisk se pak zaznamená v buňce C16. Potom se vstupní hodnota buňky sloupce 2 umístí do prázdné buňky a náhodné číslo v buňce C2 se znovu přepočítá. Odpovídající zisk se zadá do buňky C17.

Zkopírováním vzorce PRŮMĚR(B16:B1015) z buňky B13 do C13:E13 vypočítáme průměrný simulovaný zisk pro každé výrobní množství. Zkopírováním vzorce SMODCH.VÝBĚR(B16:B1015) z buňky B14 do C14:E14 vypočítáme směrodatnou odchylku simulovaných zisků pro každé množství objednávky. Pokaždé, když stiskneme klávesu F9, je pro každé objednané množství simulováno 1000 iterací poptávky. Výroba 40 000 karet vždy přináší největší očekávaný zisk. Proto se zdá, že výroba 40 000 karet je správné rozhodnutí.

Vliv rizika na naše rozhodnutí Pokud bychom místo 40 000 karet vyrobili 20 000, náš očekávaný zisk klesne přibližně o 22 procent, ale naše riziko (měřené směrodatnou odchylkou zisku) klesne téměř o 73 procent. Pokud se tedy extrémně vyhýbáme riziku, může být správným rozhodnutím vyrobit 20 000 karet. Mimochodem, výroba 10 000 karet má vždy směrodatnou odchylku 0 karet, protože pokud vyrobíme 10 000 karet, vždy je všechny prodáme bez zbytků.

Interval spolehlivosti pro průměrný zisk Přirozená otázka, kterou si v této situaci musíme položit, je, do jakého intervalu jsme si na 95 procent jisti, že skutečný průměrný zisk klesne? Tento interval se nazývá 95% interval spolehlivosti pro střední zisk. 95% interval spolehlivosti pro střední hodnotu jakéhokoli výstupu simulace se vypočítá podle následujícího vzorce:

V buňce J11 vypočítáte dolní mez 95% intervalu spolehlivosti průměrného zisku, když je vytvořeno 40 000 kalendářů pomocí vzorce D13–1,96*D14/ODMOCNINA(1000). V buňce J12 vypočítáte horní mez našeho 95% intervalu spolehlivosti pomocí vzorce D13+1,96*D14/ODMOCNINA(1000). Tyto výpočty jsou znázorněny na obrázku 60-7.

Jsme si na 95 procent jistí, že náš průměrný zisk při objednání 40 000 kalendářů je mezi 56 687 a 62 589 Kč.

Problémy

1. Prodejce GMC věří, že poptávka po Envoyích 2005 bude normálně rozložena s průměrem 200 a směrodatnou odchylkou 30. Jeho náklady na přijetí vyslance jsou 25 000 dolarů a vyslance prodává za 40 000 dolarů. Polovina všech vyslanců, kteří nebudou prodáni za plnou cenu, může být prodána za 30 000 dolarů. Uvažuje o tom, že nařídí 200, 220, 240, 260, 280 nebo 300 vyslanců. Kolik by si jich měl objednat?

2. Malý supermarket se snaží určit, kolik výtisků časopisu People by si měli každý týden objednat. Věří, že jejich poptávka po People je řízena následující diskrétní náhodnou proměnnou:

   Poptávka	Pravděpodobnost:
   15	0,10
   20	0.20
   25	0.30
   30	0,25
   35	0,15

3. Supermarket zaplatí 1,00 dolaru za každý výtisk People a prodá ho za 1,95 dolaru. Každý neprodaný výtisk lze vrátit za 0,50 USD. Kolik kopií aplikace People by si měl Store objednat?

Potřebujete další pomoc?

Kdykoli se můžete zeptat odborníka z technické komunity Excelu nebo získat podporu v komunitách.