Představení Monte Carlo simulace v aplikaci Excel

Poznámka: Snažíme se pro vás co nejrychleji zajistit aktuální obsah nápovědy ve vašem jazyce. Tato stránka byla přeložena automaticky a může obsahovat gramatické chyby nebo nepřesnosti. Naším cílem je to, aby pro vás byl její obsah užitečný. Mohli byste nám prosím dát ve spodní části této stránky vědět, jestli vám informace v článku pomohly? Pokud byste se rádi podívali na jeho anglickou verzi, najdete ji tady.

Tento článek byl upravený z analytické analýzy a modelování dat v Microsoft Excelu podle Wayne L. společností.

  • Kdo používá simulaci Monte Carlo?

  • Co se stane, když do buňky zadáte = rand () ?

  • Jak můžete simulovat hodnoty samostatné náhodné proměnné?

  • Jak můžete simulovat hodnoty normální náhodné proměnné?

  • Jak může společnost pro přání určit, kolik karet se má vytvořit?

Chtěli bychom přesně odhadnout pravděpodobnosti nejistých událostí. Například pravděpodobnost, že finanční toky nového produktu budou mít kladnou čistou současnou hodnotu (NPV)? Jaký je rizikový faktor investičního portfolia? Monte Carlo simulace umožňuje namodelovat situace, které nepředstavují nejistotu, a pak je přehrávat v počítači tisících časů.

Poznámka:  Simulaci name Monte Carlo simulace pochází z simulace počítače prováděné během 1930s a 1940s za účelem odhadu pravděpodobnosti, že by mohla reagovat na to, že je třeba, aby řetězová reakce bomb na Detonate byla úspěšná. Physicists účastnící se této práce byly velké ventilátory pro hraní hazardních her, takže jim byly přiděleny názvy kód Monte Carlo.

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

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

  • Obecné motory, Proctor a Gamble, Pfizer, Bristol-Myers Squibb a Eli Lilly používají simulaci k odhadu průměrného výnosu a rizikového faktoru nových produktů. V GM jsou tyto informace užívány VÝKONNÝm ŘEDITELem k určení produktů, které přicházejí na trh.

  • GM používá simulaci pro aktivity, jako je předpovídání čistého zisku pro podnik, předpověď nákladů na strukturální a nákup a stanovte jeho vnímavost pro různé druhy rizik (například změny úrokových sazeb a kolísání směnného kurzu).

  • Lilly používá simulaci k určení optimální kapacity rostlin pro každou drogu.

  • Proctor a Gamble používají simulaci k modelování a optimálnímu zajištění devizového rizika.

  • Sears používá simulaci k určení, kolik jednotek jednotlivých řádků produktu by mělo být objednáno od dodavatelů – například počet párů Dockických kalhoty, které by měly být objednány tento rok.

  • Ropné a farmaceutické společnosti používají simulaci na hodnotu "reálné možnosti", jako je hodnota možnosti pro rozšíření, smlouva nebo odklad projektu.

  • Finanční plánovači používají Monte Carlo simulaci k určení optimálních strategií investic pro své starobní důchody klientů.

Když do buňky zadáte vzorec = rand () , získáte číslo, které bude mít stejnou hodnotu v rozsahu 0 až 1. Přibližně 25 procent času by tedy mělo být číslo menší nebo rovno 0,25; zhruba 10 procent času, kdy byste měli obdržet číslo, které je aspoň 0,90 atd. Pokud chcete zjistit, jak funguje funkce NÁHČÍSLO, podívejte se na soubor Randdemo. xlsx, který je znázorněn na obrázku 60-1.

Book Image

Poznámka:  Když otevřete soubor Randdemo. xlsx, nezobrazí se stejná náhodná čísla na obrázku 60-1. Funkce NÁHČÍSLO vždy automaticky přepočítá čísla generovaná při otevření listu nebo při zadání nových informací do listu.

Nejdřív zkopírujte z buňky C3 do C4: C402 vzorec = rand (). Potom pojmenujte oblast C3: C402 data. Potom můžete ve sloupci F sledovat průměrnou hodnotu náhodných čísel 400 (ve kyvetě 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. Po stisknutí klávesy F9 se přepočítají náhodná čísla. Povšimněte si, že průměr z čísel 400 je vždy přibližně 0,5 a že přibližně 25 procent výsledků je v intervalech 0,25. Tyto výsledky jsou konzistentní s definicí 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 generované v buňce C3 velké číslo (například 0,99), oznámí nám to, že neobsahují hodnoty ostatních náhodných čísel.

Předpokládejme, že poptávka za kalendář se řídí následující náhodnou náhodnou proměnnou:

Okamžit

Pravděpodobnost:

10 000

0,10

20 000

0,35

40 000

0,3

60 000

0,25

Jak se dá Excel v Excelu přehrát nebo ho simulovat mnohokrát? Štych je přidružit každou možnou hodnotu funkce NÁHČÍSLO s možným požadavkem pro kalendáře. Následující přiřazení zajistí, že bude k požadavku 10 000 docházet 10% času atd.

Okamžit

Přiřazené náhodné číslo

10 000

Menší než 0,10

20 000

Větší než nebo rovno 0,10 a menší než 0,45

40 000

Větší než nebo rovno 0,45 a menší než 0,75

60 000

Větší než nebo rovno 0,75

Chcete-li předvést simulaci požadavků, podívejte se na soubor Discretesim. xlsx, který je znázorněn na obrázku 60-2 na další stránce.

Book Image

Klíčem k simulaci je použít náhodné číslo pro inicializaci vyhledávání z oblasti tabulky F2: G5 (pojmenované vyhledávání). Náhodné číslo větší nebo rovno 0 a menší než 0,10 vyhodnotí požadavek 10 000; náhodná čísla větší nebo rovná 0,10 a menší než 0,45 budou mít za výsledek 20 000; náhodná čísla větší nebo rovná 0,45 a menší než 0,75 budou mít za výsledek 40 000; a náhodných čísel větších nebo rovných 0,75 vrátí požadavek 60 000. 400 náhodná čísla generujete z C3 do C4: C402 vzorec rand (). Testy 400 a iterace z kalendáře se pak generují zkopírováním z B3 na B4: B402 vzorec (C3, Lookup, 2). 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 můžete pomocí funkce COUNTIF určit zlomek našich 400 iterací, které mají každý požadavek. Když stisknete klávesu F9 a přepočítáte náhodná čísla, budou simulované pravděpodobnosti blízké předpokládaným pravděpodobnostem poptávky.

Pokud do libovolné buňky zadáte vzorec NORMINV (Rand (), mu, Sigma), vygeneruje se simulovaná hodnota normální náhodné proměnné s středním a směrodatnou odchylkou Sigma. Tento postup je znázorněn v souboru Normalsim. xlsx, který je znázorněn na obrázku 60-3.

Book Image

Předpokládejme, že chceme simulovat testy 400 nebo iterace 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 v uvedeném pořadí tyto buňky znamenají . ) Kopírování vzorce = rand () od C4 do C5: C403 generuje 400 různá náhodná čísla. Kopírování z B4 na H5: B403 vzorec NORMINV (C4, střed_hodn, Sigma) generuje 400 různé zkušební hodnoty od normální náhodné proměnné se střední hodnotou 40 000 a směrodatnou odchylkou 10 000. Když stisknete klávesu F9 k přepočtu náhodných čísel, znamená to, že střední hodnota zůstane blízko 40 000 a směrodatná odchylka blízko 10 000.

Ve vzorci pro náhodné číslo xvygeneruje vzorec NORMINV (p, mu, Sigma) hodnotu p-tého percentilu normální náhodné proměnné s středním 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ě 77th percentil normální náhodné proměnné se střední hodnotou 40 000 a směrodatnou odchylkou 10 000.

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

Okamžit

Pravděpodobnost:

10 000

0,10

20 000

0,35

40 000

0,3

60 000

0,25

Přání, které prodává za $4,00, a variabilní náklady na vytvoření každé karty jsou $1,50. Karty leftover musí být vyprodány za cenu $0,20 na každou kartu. Kolik karet se má vytisknout?

V podstatě můžeme mnohokrát simulovat každé možné výrobní množství (10 000, 20 000, 40 000 nebo 60 000) (například 1000 iterací). Potom určíte, jaké množství objednávek vyhodnotí maximální průměrný zisk v iteracích 1000. Data pro tento oddíl najdete v souboru Valentýn. xlsx, který je znázorněn na obrázku 60-4. Názvy oblastí se přiřazují v buňkách B1: B11 do buněk C1: C11. Oblast buněk G3: h6 má přiřazený název. Naše prodejní ceny a parametry nákladů se zapisují do buněk C4: C6.

Book Image

Do buňky C1 můžete zadat výrobní množství (40 000 v tomto příkladu). Potom v buňce C2 vytvořte náhodné číslo se vzorcem = rand (). Jak již bylo popsáno, simulujte v buňce C3 požadavek na kartu se vzorcem SVYHLEDAT (Rand, Lookup, 2). (Ve vzorci SVYHLEDAT je název buňky přiřazený k buňce C3, nikoli k funkci NÁHČÍSLO.)

Počet prodaných jednotek 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 vytvořeného * unit_prod_cost.

Pokud nabízíme více karet, než je poptávka, počet kusů, které zbývá po stejnou výrobu, minus poptávka; v opačném případě nejsou žádné jednotky. Naše náklady na vyřazení v buňce C10 se počítají vzorcem unit_disp_cost * když (produced>demand; vyrobeno – poptávka; 0). Nakonec v buňce C11 vypočítáme zisk jako výnosy – total_var_cost-total_disposing_cost.

Chtěli bychom být účinným způsobem, jak stisknout F9 mnohokrát (například 1000) pro každé výrobní množství a získat očekávaný zisk za každé množství. Tato situace je ta, ve které přichází obousměrná tabulka dat do záchranných představ. (Podrobné informace o tabulkách dat najdete v kapitole 15, "citlivostní analýzy s tabulkami dat"). Tabulka dat použitá v tomto příkladu se zobrazuje na obrázku 60-5.

Book Image

V oblasti buněk A16: A1015 zadejte čísla 1 – 1000 (odpovídající našim zkušebním verzím 1000). Jedním ze způsobů, jak tyto hodnoty vytvořit, je začít zadáním hodnoty 1 do buňky A16. Vyberte buňku a potom na kartě Domů ve skupině Úpravy klikněte na výplňa vyberte řady a zobrazte tak dialogové okno řady . V dialogovém okně řady zobrazené na obrázku 60-6 zadejte hodnotu kroku 1 a hodnotu ukončení 1000. V části řady v oblasti vyberte možnost sloupce a potom klikněte na OK. Čísla 1 – 1000 se zadávají ve sloupci A od začátku do buňky A16.

Book Image

Potom zadáte naše možné výrobní množství (10 000, 20 000, 40 000, 60 000) do buněk B15: E15. Chceme vypočítat zisk za každé zkušební číslo (1 až 1000) a každé výrobní množství. Vzorec pro výpočet zisku (vypočtený v buňce C11) se používá v levé horní buňce tabulky dat (A15), když zadáte = C11.

Teď je připraveno přimět Excel na simulaci 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 co když analýza a pak vyberte tabulka dat. Pokud chcete nastavit obousměrnou tabulku dat, vyberte naše výrobní množství (buňka C1) jako vstupní buňku řádku a vyberte libovolnou prázdnou buňku (i14 buňky) jako vstupní buňka sloupce. Po kliknutí na tlačítko OK aplikace Excel simuluje hodnoty 1000 pro každé objednávky.

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

Zkopírováním z buňky B13 na C13: E13 průměr vzorce (B16: B1015)vypočteme průměrný zisk zisku pro každé výrobní množství. Zkopírováním z buňky B14 na C14: E14 vzorec SMODCH (B16: B1015)vypočítáme směrodatnou odchylku našich simulovaných zisků pro každé objednávky. PoKaždé když stisknete F9, 1000 se počet iterací poptávky simuluje pro každé objednávky. Výroba 40 000 karet vyProdukuje vždy největší očekávaný zisk. Proto se zdá, že výroba 40 000 Card je řádným rozhodnutím.

Dopad rizika na naše rozhodnutí     Pokud jsme společnost 20 000 místo 40 000, náš očekávaný pokles zisku přibližně 22 procent, ale naše riziko (měřené směrodatnou odchylkou zisku) klesne téměř 73 procent. Pokud je tedy velmi Averse riziko, může to být správné rozhodnutí vytvářející karty 20 000. Incidenty, vytváření karet 10 000 má vždycky standardní odchylku 0, protože když vyrábíte 10 000 karty, budeme vždy prodávat všechny, aniž byste zbyléi.

Poznámka:  V tomto sešitu je možnost výpočtu nastavena na automaticky s výjimkou tabulek. (Pomocí příkazu výpočet ve skupině výpočet na kartě vzorce) Toto nastavení zajišťuje, aby tabulka dat nebyla přepočítána, pokud nepoužíváte klávesu F9, což je dobrý nápad, protože Velká tabulka dat zpomaluje práci, pokud přepočítá při každém napsání do listu. Všimněte si, že v tomto příkladu se po stisknutí klávesy F9 změní střední zisk. To se může stát, když pokaždé stisknete F9, vygeneruje se požadavky pro každé množství objednávky pomocí odlišné posloupnosti 1000 náhodných čísel.

Interval spolehlivosti pro průměrný zisk     Přirozenou otázkou, která se v této situaci zeptá, v jakém intervalu je 95 procento, že bude skutečný zisk na zisku? Tento interval se označuje jako 95 procent spolehlivosti intervalu pro střední zisk. Interval spolehlivosti 95 se pro střední hodnotu každého simulace vypočte pomocí následujícího vzorce:

Book Image

V buňce J11 spočítáte dolní limit intervalu spolehlivosti 95 procent pro průměrnou hodnotu zisku, když se 40 000 kalendáře vytvářejí pomocí vzorce D13 – 1.96 * D14/SQRT (1000). V buňce J12 vypočítáte horní limit pro náš interval spolehlivosti 95% vzorce D13 + 1.96 * D14/SQRT (1000). Tyto výpočty jsou znázorněny na obrázku 60-7.

Book Image

V 95 je procento, které si zajistěte, aby byl průměrný zisk po objednání kalendářů 40 000 mezi $56 687 a $62 589.

  1. GMC obchodník se domnívá, že poptávka po 2005 Envoys bude normálně distribuována se střední hodnotou 200 a směrodatnou odchylkou 30. Pořizovací cena Envoy je $25 000 a prodává Envoy pro $40 000. Za $30 000 se dá prodat polovina všech Envoys, které se neprodávají za celou cenu. Zvažuje pořadí řazení 200, 220, 240, 260, 280 nebo 300 Envoys. Kolik objednávek má objednávka?

  2. Malý na trhu se snaží určit, kolik kopií časopisu pro lidi by mělo každý týden rozhodovat. Domnívají se, že jejich poptávka se řídí touto náhodnou náhodný proměnnou:

    Okamžit

    Pravděpodobnost:

    15

    0,10

    20

    0,20

    24

    0,30

    končí

    0,25

    35

    0,15

  3. Hotový trh platí $1,00 pro každou kopii a prodává se pro $1,95. Každou prodanou kopii je možné vrátit do $0,50. Kolik kopií osob by mělo mít úložiště?

Potřebujete další pomoc?

Kdykoli se můžete zeptat některého odborníka v technické komunitě Excelu, získat podporu v komunitě pro odpovědi, případně navrhnout novou funkci nebo vylepšení na fóru Excel User Voice.

Rozšiřte své dovednosti s Office
Projít školení
Získejte nové funkce jako první
Připojte se k účastníkům programu Office Insiders

Byly tyto informace užitečné?

Děkujeme vám za zpětnou vazbu.

Děkujeme vám za váš názor! Pravděpodobně bude užitečné, když vás spojíme s některým z našich agentů podpory Office.

×