Úvod k typu 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 je upravit v Microsoft Excelu analýzy a modelování obchodních tak, že velkých společností Wayne Winston L..

  • Kdo je používá typu Monte Carlo simulace?

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

  • Jak lze simulovat hodnoty samostatné náhodná proměnná?

  • Jak lze simulovat hodnoty normální náhodná proměnná?

  • Jak společnost přání zjistit, kolik karet k vytvoření?

Chtěli jsme přesně odhad pravděpodobností jisti události. Co je třeba pravděpodobnost, že nového produktu peněžních toků bude mít kladné čistou současnou hodnotu (NPV)? Co je faktoru rizika naše investice portfolia? Typu Monte Carlo simulace nám umožňuje modelu situacích prezentovat nejistoty a jejich přehrávání v počítači tisíců časů.

Poznámka: Název typu Monte Carlo simulace pochází z počítače simulace provedených během 1930s a 1940s k odhadu pravděpodobnost, že řetězec reakce potřebné pro atom bomb k výbušnosti vhodná úspěšně. Zahrnuté do to fungovalo physicists byly velký ventilátory z hazardních, tak, aby se daly simulace typu Monte Carlonázev kódu.

V následujících pět kapitoly zobrazí se příklady použití aplikace Excel provádět simulace typu Monte Carlo.

Mnoho společností použít typu Monte Carlo simulace jako důležitou součástí rozhodovací proces. Tady je pár příkladů.

  • Obecné motory Proctor a Gamble, Pfizer, Bristol Horák Squibb a Eli Lilly umožňuje simulace odhad průměrný výnos a faktoru rizika nových produktů. Na GM tyto informace používá Výkonní a zjistit, které produkty přijít na trh.

  • GM používá simulace aktivity například prognózováním čistý příjem ve společnosti, odhad nákladů strukturální a nákupu a určení jeho citlivosti různé druhy rizika (například změny úrokové sazby a kolísání kurzu exchange).

  • Lilly používá simulace k určení kapacitu optimální zařízení pro každou obchodu.

  • Proctor a Gamble používá simulace model a optimálně zajistila cizích měn rizika.

  • Sears používá simulace a zjistit, kolik jednotek čárami produktu by měla být seřazena z dodavatele – třeba počet dvojice kalhoty Dockers, které by měla být seřazena tento rok.

  • Oil a obchod společnosti používat simulace hodnotu "skutečné možnosti" například hodnota možnost rozbalte, smlouvy nebo odložení projektu.

  • Finanční plánovacích pomůcek typu Monte Carlo simulace umožňuje určit optimální investice strategie pro svoje klienty vyřazování webů.

Při zadávání vzorce =RAND() v buňce se zobrazí číslo, které jsou rovnoměrně z vnější pravděpodobně předpokládá libovolná hodnota mezi 0 a 1. Proto přibližně 25 procent dobu, by měl dostanete číslo menší nebo rovna 0,25; kolem 10 % času by měla získat čísla, která je nejméně 0.90 atd. Která ukazuje, jak funkce NÁHČÍSLO funguje, podívejte se na tento soubor Randdemo.xlsx, zobrazené v obrázek 60-1.

Book Image
Obrázek 60-1, které demonstrují funkce NÁHČÍSLO

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

Nejdřív zkopírujte v buňce C3 do C4:C402 vzorce =RAND(). Potom název oblasti C3:C402 Data. Pak ve sloupci F, můžete sledovat průměr 400 náhodná čísla (buňka F2) a použití funkce COUNTIF k určení zlomků, které jsou mezi 0 a 0,25 0,25 a 0,50, 0,50 a 0,75 a 0,75 a 1. Při stisknutí klávesy F9, budou přepočítány náhodná čísla. Oznámení, že průměru 400 čísel je vždy přibližně 0,5 a přibližně 25 procent výsledků se nacházejí v intervalů 0,25. Výsledky jsou konzistentní s definici náhodné číslo. Navíc nezapomeňte, že jsou nezávislé hodnoty generovaných NÁHČÍSLO v různých buňkách. Například pokud generováno náhodné číslo v buňce C3 velké množství (například 0,99) je ho víme nic o hodnotách jiných náhodné číslo generované.

Předpokládejme, že služba pro kalendář podléhá následujícím samostatné náhodná proměnná:

Služba

Pravděpodobnost

10 000

0,10

20 000

0,35

40 000

0,3

60 000

0,25

Jak lze máme Excelu přehrát nebo simulovat, tato služba pro kalendáře opakovaně pokoušeli? Vtip je přidružit možné služba kalendáře pro každou možnou hodnotu funkce NÁHČÍSLO. Následující přiřazení zaručuje, že služba o hodnotě 10 000 dojít 10 % času a tak dál.

Služba

Náhodné číslo přiřazené

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 rovná hodnotě 0,75

Pokud chcete ukazují simulace služba, podívejte se na souboru Discretesim.xlsx, zobrazené v obrázek 60-2 na další stránce.

Book Image
Obrázek 60-2 simulace samostatné náhodná proměnná

Klíč k naše simulace, je použít náhodné číslo zahajte vyhledávání v oblasti tabulka F2:G5 (pojmenovali vyhledávání). Náhodná čísla větší než nebo rovno 0 a menší než 0,10 přinese služba 10 000; náhodná čísla větší než nebo rovno 0,10 a menší než 0,45 přinese služba 20 000; náhodná čísla větší než nebo rovno 0,45 a menší než 0,75 přinese služba 40 000; a náhodná čísla větší než nebo rovná hodnotě 0,75 přinese služba 60 000 Kč. Generovat 400 náhodná čísla zkopírováním z C3 C4:C402 vzorce RAND(). Můžete pak 400 pokusy nebo generovat iterace kalendář služba zkopírováním z B3 B4:B402 vzorec VLOOKUP(C3,lookup,2). Tento vzorec zaručuje, že náhodné číslo menší než 0,10 generuje požádání o hodnotě 10 000, náhodné číslo mezi 0,10 a 0,45 vygeneruje služba 20 000 a tak dál. V oblasti buněk F8:F11 použijte funkci COUNTIF k určení zlomek naše 400 iterací získávání každou službu. Důvody jsme jej stisknutím klávesy F9 náhodná čísla, můžou být simulovaný pravděpodobností zavřít naše pravděpodobnosti předpokládá, že služba.

Pokud zadáte do libovolné buňky vzorce NORMINV(rand(),mu,sigma), vygeneruje simulovaný hodnotu Normální náhodná proměnná s střed_hodn mu a směrodatnou odchylku sigma. Tento postup je znázorněn v souboru Normalsim.xlsx, zobrazené v obrázek 60-3.

Book Image
Obrázek 60-3 simulace normální náhodná proměnná

Předpokládejme, že chceme tak, aby napodobily 400 pokusy nebo iterací pro normální náhodná proměnná s střední hodnotu 40 000 a směrodatnou odchylkou 10 000. (Můžete do buňky E1 a E2 zadejte tyto hodnoty a pojmenujte tyto buňky nechtěli a sigma.) Kopírování vzorce =RAND() z C4 C5:C403 vygeneruje 400 různých náhodná čísla. Kopírování z B4 B5:B403 vzorec NORMINV(C4,mean,sigma) generuje 400 různé zkušební hodnoty z normální náhodná proměnná s střední hodnotu 40 000 a směrodatnou odchylkou 10 000. Když jsme stisknutím klávesy F9 přepočet náhodná čísla, střední hodnoty zůstane zavřít 40 000 a směrodatnou odchylku zavřít 10 000.

V podstatě pro náhodné číslo xvygeneruje vzorce NORMINV(p,mu,sigma)ptý percentil normální náhodná proměnná s střed_hodn mu a směrodatnou odchylku sigma. Například náhodné číslo 0,77 v buňce C4 (viz obrázek 60-3) vygeneruje do buňky B4 přibližně 77th percentilu normální náhodná proměnná s střední hodnotu 40 000 a směrodatnou odchylkou 10 000.

V této části zobrazí se použití Monte Carlo simulace jako prostředek rozhodovací. Předpokládejme, že služba Valentýna karty podléhá následujícím samostatné náhodná proměnná:

Služba

Pravděpodobnost

10 000

0,10

20 000

0,35

40 000

0,3

60 000

0,25

Přání k $4.00, prodává a náklady proměnné vytvářet každé kartě jsou $1.50. Přebývající karty musí být odstraněny s platbou 0,20 na karty. Kolik karet mají vytištěny?

V podstatě jsme simulovat každou možnou výrobní množství (10 000, 20 000, 40 000 nebo 60 000 Kč) opakovaně pokoušeli (například 1 000 iterací). Jsme zjistěte, jaké množství pořadí dává maximální průměrný zisk přes iterací 1 000. Najít data pro tento oddíl v souboru Valentine.xlsx, vidíte na obrázku 60-4 při instalaci. Názvy oblastí v buňkách B1:B11 přiřadíte C1:C11 buněk. Oblast buněk G3:H6 se přiřadí názvu vyhledávání. Náš prodejní ceny a náklady parametrů jsou zadány v buňkách C4: C6 buňky.

Book Image
Obrázek 60-4 při instalaci Valentýna karty simulace

Vyzkoušení výrobní množství (40 000 v tomto příkladu) můžete zadat do buňky C1. Dále vytvořte náhodné číslo v buňce C2 s vzorce =RAND(). Postupem uvedeným dříve simulovat služba karty v buňce C3 s vzorce VLOOKUP(rand,lookup,2). (V vzorce funkce SVYHLEDAT je funkce NÁHČÍSLO název buňky přiřazené k buňce C3, nejsou funkce NÁHČÍSLO.)

Počet prodané jednotky je menší z našich výrobní množství a služba. Do buňky C8, výpočet naše výnosy se vzorcem MIN (výroby, služba) * unit_price. V buňce C9, výpočet celkové výrobních nákladů se vzorcem vyrobeno * unit_prod_cost.

Pokud máme další karty než služba, počet jednotek zleva rovná výrobní mínus služba; v opačném případě se žádné jednotky zleva. Jsme výpočet naše odstraňování náklady na buňku C10 se vzorcem unit_disp_cost * Pokud (vyrobeno > služba, vyrobeno – služba, 0). Nakonec v buňce C11, jsme výpočet naše zisk jako výnosy – total_var_cost total_disposing_cost.

Budeme rádi efektivně stisknutím klávesy F9 opakovaně pokoušeli (například 1 000) pro každé výrobní množství a shromáždění naše očekávaný zisk pro každé množství. Tato situace je taková ve kterém tabulku dat mezi dvěma stranami Doručená naše zachrání. (Viz kapitola 15 "Utajení analýzy pomocí tabulek dat" podrobnosti o tabulek dat.) Tabulka dat v tomto příkladě použita se zobrazuje v obrázek 60-5.

Book Image
Tabulka dat mezi dvěma stranami obrázek 60 – 5 pro přání simulace

V oblasti buněk A16:A1015 zadejte číslo 1 – 1000 (odpovídající naše pokusy 1000). Jeden snadný způsob, jak vytvořit tyto hodnoty je začít zadáním 1 v buňce A16. Vyberte buňku, klikněte na kartě Domů ve skupině Úpravy, klikněte na Výplň a vyberte řadu, kterou chcete zobrazit dialogové okno řady. V dialogovém okně řady, ukazuje obrázek 60-6 zadejte hodnoty kroku 1 a zastavit hodnotu 1 000. V oblasti Řady v vyberte možnost sloupce a potom klikněte na OK. Čísla 1 až 1 000 budou zadané ve sloupci uzavřená v buňce A16.

Book Image
Obrázek 60-6 pomocí dialogového okna řady Chcete-li vyplnit zkušební číslem od 1 až 1000

Další jsme naše možné výrobní množství (10 000, 20 000, 40 000 60 000 Kč) zadejte do buňky B15:E15. Chceme Vypočítat zisk pro každé zkušební číslo (1 až 1000) a každý množství výroby. Zadáním = C11označovány vzorec pro profit (počítaných v buňce C11) v levé horní buňky naše tabulku dat (A15).

Jsme jste připraveni vtip Excelu do simulace 1 000 iterací služba pro každou množství výroby. Vyberte oblast tabulky (A15:E1014) a ve skupině datové nástroje na kartě Data klikněte na tlačítko Analýza What If a pak vyberte tabulku dat. Nastavit tabulku mezi dvěma stranami dat, zvolte naše výrobní množství (buňka C1) jako vstupní buňka řádku a vyberte libovolné prázdné buňky (zvolili jsme buňky I14) jako sloupec vstupní buňky. Po kliknutí na OK, Excel napodobuje 1000 služba hodnoty pro každou objednávku množství.

Pokud chcete zjistit, proč to funguje, zvažte hodnoty umístěná v tabulce dat v oblasti buněk C16:C1015. U každé z těchto buněk Excel použije hodnotu 20 000 do buňky C1. V C16 hodnota vstupní buňka sloupce 1 umístí do prázdné buňky a náhodné číslo v buňce C2 přepočítá. Odpovídající zisk je potom zaznamená do buňky C16. Klikněte na buňku vstupní hodnotu sloupce 2 umístí do prázdné buňky a znovu přepočítá náhodné číslo v buňce C2. Odpovídající hrubá zadává v buňce C17.

Zkopírováním z buňku B13 na C13:E13 vzorec AVERAGE(B16:B1015)jsme vypočítat průměr simulovaný zisk za každou výrobní množství. Zkopírováním z buňky B14 na C14:E14 vzorec STDEV(B16:B1015)jsme výpočet směrodatná odchylka naše simulovaný zisků každé množství pořadí. Pokaždé, když nám stisknutím klávesy F9, 1 000 iterací služba jsou simulovaného pro každou objednávku množství. Vytváření 40 000 karty vždy dává největší očekávaný zisk. Proto zdá, že vytváření 40 000 karty správné rozhodnutí.

Jaký vliv mají na rizika na naše rozhodnutí     Pokud jsme vyrobeno 20 000 místo 40 000 karty, náš očekávaný zisk vynechává přibližně 22 procent, ale naše rizika (jak měřit standardní odchylku zisk) vynechává téměř 73 procent. Proto jsme velmi projeví, nepříznivé rizika, vytváření 20 000 karty pravděpodobně správné rozhodnutí. Náhodně vytváření 10 000 karty vždy má směrodatnou odchylkou 0 karet protože pokud máme 10 000 karty, jsme bude vždy prodávat všem poznámkám bez všechny zbylé.

Poznámka: V tomto sešitu možností výpočtu nastavenou Automaticky s výjimkou tabulek. (Příkazem výpočtů ve skupině výpočet na pásu karet aplikace Excel.) Tak nastavíte, že naše tabulka dat se přepočítají, pokud jsme stisknutím klávesy F9, které je dobré, protože tabulku velkých dat bude zpomalit práci, pokud přepočítá pokaždé, když něco napíšete do listu. Všimněte si, že v tomto příkladu po stisknutí F9, střed_hodn zisk dojde ke změně. To je způsobeno pokaždé, když stisknutí klávesy F9, jiné posloupnost náhodná čísla 1 000 bude použito k vygenerování požadavky pro každou objednávku množství.

Interval spolehlivosti pro nechtěli zisk     Přírodní otázku pokládat v takovém případě se do jaké interval jsou jsme 95 %, že PRAVDA střed_hodn zisk spadá? Tento interval se nazývá 95 % interval spolehlivosti pro střední profit. Následující vzorec počítá 95 % interval spolehlivosti pro střední hodnotu všech simulace výstup:

Book Image

V buňce J11 výpočet dolní mez intervalu spolehlivosti 95 % na střed_hodn zisku až 40 000 kalendářů je vyrobeno s vzorce D13–1.96*D14/SQRT(1000). V buňce J12 výpočet horní mez pro naše interval spolehlivosti 95 % se vzorcem D13+1.96*D14/SQRT(1000). Těchto výpočtů jsou zobrazené v obrázek 60-7.

Book Image
Obrázek 60-7 95 % interval spolehlivosti pro střední zisk při objednávce 40 000 kalendáře

Je nám 95 % jistotu, že naše střed_hodn zisk při objednávce 40 000 kalendářů mezi 56,687 $ a $62,589.

  1. Prodejce GMC názoru, že služba 2005 Envoys bude normální rozdělení s střední hodnotu 200 a směrodatnou odchylkou 30. Jeho náklady přijímat Envoy jsou $ 25 000 a mu prodává Envoy pro 40 000 Kč. Polovina roku všechny Envoys není prodeje za celou cenu můžete prodeje za 30 000 Kč. Tahle je zvažte možnost řazení 200 220, 240, 260, 280 nebo 300 Envoys. Kolik měli mu objednat?

  2. Malé zásobníku materiálu pokouší zjistit, kolik kopií lidé katalogu mají objednejte každý týden. Budou myslíte, že jejich služba pro uživatele se řídí následující samostatné náhodná proměnná:

    Služba

    Pravděpodobnost

    15

    0,10

    20

    0,20

    25

    0,30

    30

    0,25

    35

    0,15

  3. Zásobníku materiálu platí $1,00 pro jednotlivé kopie lidí a prodává pro $1.95. Každá Neprodán kopie může být vrácen pro $0,50. Kolik kopií Uživatelé měli úložišti pořadí?

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. Vypadá to, že bude užitečné, když vás spojíme s některým z našich agentů z podpory Office.

×