Úvodné informácie o simulácii Monte Carlo v Exceli

Poznámka: Radi by sme vám čo najrýchlejšie poskytovali najaktuálnejší obsah Pomocníka vo vašom vlastnom jazyku. Táto stránka bola preložená automaticky a môže obsahovať gramatické chyby alebo nepresnosti. Naším cieľom je, aby bol tento obsah pre vás užitočný. Dali by ste nám v dolnej časti tejto stránky vedieť, či boli pre vás tieto informácie užitočné? Tu nájdete anglický článok pre jednoduchú referenciu.

Tento článok bol prispôsobený z analýzy údajov programu Microsoft Excel a modelovAnia podnikov prostredníctvom Wayne L. Winston.

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

  • Čo sa stane, keď do bunky zadáte výraz = Rand () ?

  • Ako môžete simulovať hodnoty diskrétnych náhodných premenných?

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

  • Ako môže spoločnosť s pozdravom zistiť, koľko kariet sa má vyrábať?

Chceli by sme presne odhadnúť pravdepodobnosť neistých udalostí. Aká je napríklad pravdepodobnosť, že peňažné toky nového produktu budú mať pozitívnu č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 prehratie v počítači tisíckrát.

Poznámka:  Simulácia názvu Monte Carlo pochádza z počítačových simulácií vykonaných počas rokov 1930 a 1940 s cieľom odhadnúť pravdepodobnosť, že reťazová reakcia potrebná na výbuch bomby na odpálenie by úspešne fungovala. Fyzici, ktorí boli zapojení do tejto práce, boli veľkými fanúšikmi hazardných hier, a tak dali simulácie názov kódu Monte Carlo.

V nasledujúcich piatich kapitolách uvidíte príklady toho, ako môžete pomocou Excelu vykonávať simulácie Monte Carlo.

Mnoho spoločností používa simuláciu Monte Carlo ako dôležitú súčasť 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 odhadnutie priemerného výnosu a rizikového faktora nových produktov. Pri GM tieto informácie používa CEO na určenie produktov, ktoré sú na trhu.

  • GM používa simuláciu pri činnostiach, akými sú napríklad prognózy čistého príjmu pre spoločnosť, predpovedanie štrukturálnych a nákupných nákladov a určenie jeho náchylnosti k rôznym druhom rizika (napríklad zmeny úrokovej sadzby a výkyvy výmenného kurzu).

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

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

  • Sears používa simuláciu na určenie počtu jednotiek jednotlivých produktových produktov, ktoré by mali byť zoradené od dodávateľov – napríklad počet párov nohavíc, ktoré je potrebné objednať v tomto roku.

  • Ropné a farmaceutické spoločnosti používajú simuláciu na hodnotu skutočné možnosti, ako je napríklad hodnota možnosti na rozbalenie, zmluvy 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.

Keď zadáte vzorec = Rand () v bunke, dostanete číslo, ktoré pravdepodobne prevezme ľubovoľnú hodnotu v rozsahu od 0 do 1. Teda okolo 25 percent času by ste mali mať číslo menšie alebo rovné 0,25; približne 10 percent času, Kedy by ste mali mať číslo, ktoré je aspoň 0,90, a tak ďalej. Ak chcete preukázať, ako funguje Funkcia RAND, pozrite si súbor Randdemo. xlsx, ktorý je znázornený na obrázku 60-1.

Book Image

Poznámka:  Pri otvorení súboru Randdemo. xlsx sa nezobrazia rovnaké náhodné čísla zobrazené na obrázku 60-1. Funkcia RAND vždy automaticky prepočíta čísla, ktoré generuje pri otvorení hárka, alebo keď sa do hárka vložia nové informácie.

Najskôr skopírujte z bunky C3 do C4: C402 vzorec = Rand (). Potom môžete pomenovať rozsah C3: C402 údaje. Potom v stĺpci F môžete sledovať priemer 400 náhodných čísiel (bunka F2) a použiť funkciu COUNTIF na určenie zlomkov, ktoré sú 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čítajú. Všimnite si, že priemer čísel 400 je vždy približne 0,5, a že približne 25 percent výsledkov je v intervaloch 0,25. Tieto výsledky sú konzistentné s definíciou náhodného čísla. Hodnoty vytvorené spoločnosťou 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), povie nám nič o hodnotách ostatných vygenerovaných náhodných čísel.

Predpokladajme, že dopyt po kalendári sa riadi nasledujúcim diskrétnym náhodným ukazovateľom:

Požiadanie

Pravdepodobnosť

10 000

0,10

20 000

0,35

40 000

0,3

60 000

0,25

Ako môžeme mať Excel Play out alebo simulovať tento dopyt po kalendároch mnohokrát? Trik je priradiť každú možnú hodnotu funkcie RAND s možným dopytom po kalendároch. Nasledujúce nasadenie zabezpečuje, že dopyt 10 000 bude mať 10 percent času a tak ďalej.

Požiadanie

Priradené náhodné číslo

10 000

Menej než 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 si súbor Discretesim. xlsx, ktorý je znázornený na obrázku 60-2 na ďalšej strane.

Book Image

Kľúčom k našej simulácii je použiť náhodné číslo na začatie vyhľadávania z rozsahu tabuľky F2: G5 (s názvom Lookup). Náhodné čísla väčšie alebo rovné 0 a menšie ako 0,10 prinesú dopyt vo výške 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 vo výške 60 000. Vygenerujete 400 náhodných čísel skopírovaním z C3 na C4: C402 vzorec Rand (). Potom vygenerujete skúšobné verzie 400 alebo iterácie z kalendára, a to skopírovaním z B3 na B4: B402 vzorec VLOOKUP (C3; Lookup; 2). Tento vzorec zabezpečuje, aby všetky náhodné čísla menšie ako 0,10 vygenerovali dopyt vo výške 10 000, akékoľvek náhodné číslo medzi 0,10 a 0,45 vygeneruje dopyt po 20 000 a tak ďalej. V rozsahu buniek F8: F11 použite funkciu COUNTIF na určenie zlomku našich iterácií 400, ktoré prinášajú jednotlivé požiadavky. Keď na prepočítanie náhodných čísel stlačíte kláves F9, simulované pravdepodobnosti sa približujú k predpokladaným pravdepodobným požiadavkám na dopyt.

Ak zadáte do ľubovoľnej bunky vzorec funkcia NORMINV (Rand (), mu; Sigma), vytvoríte simulovanú hodnotu normálnej náhodnej premennej s priemernou hodnotou mu a smerodajnou odchýlkou Sigma. Tento postup je znázornený v súbore Normalsim. xlsx, ktorý je znázornený na obrázku 60-3.

Book Image

Predpokladajme, že chceme simulovať skúšobné verzie 400 alebo iterácie pre normálnu náhodnú premennú s priemerom 40 000 a smerodajnou odchýlkou 10 000. (Tieto hodnoty môžete zadať v bunkách E1 a E2 a pomenovať tieto bunky znamenajú a Sigma, resp.) Kopírovanie vzorca = Rand () z C4 na C5: C403 generuje 400 rôznych náhodných čísel. Kopírovanie z B4 na B5: B403 funkcia NORMINV vzorca (C4; Mean; Sigma) generuje 400 rôznych skúšobných hodnôt z normálnej náhodnej premennej s priemerom 40 000 a smerodajnou odchýlkou 10 000. Keď stlačíte kláves F9 na prepočítanie náhodných čísel, stredná hodnota zostane v blízkosti 40 000 a smerodajná odchýlka sa blíži k 10 000.

V podstate pre náhodné číslo x, vzorec funkcia NORMINV (p, mu; Sigma) generuje pth percentil normálneho náhodného ukazovateľa s priemernou 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 77th percentil normálneho náhodného ukazovateľa s priemerom 40 000 a smerodajnou odchýlkou vo výške 10 000.

V tejto časti sa zobrazí, ako môže byť simulácia Monte Carlo použitá ako nástroj na rozhodovanie. Predpokladajme, že dopyt po pohľadnici za deň Valentína sa riadi nasledujúcim diskrétnym náhodným ukazovateľom:

Požiadanie

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 a variabilné náklady na výrobu jednotlivých kariet sú $1,50. Zvyšné karty sa musia likvidovať za cenu $0,20 na jednu kartu. Koľko kariet treba vytlačiť?

V podstate sme simulovali každé možné množstvo produkcie (10 000, 20 000, 40 000 alebo 60 000) mnohokrát (napríklad 1000 iterácií). Potom určíme, ktoré množstvo výnosov je výsledkom maximálneho priemerného zisku z iterácií 1000. Údaje pre túto sekciu nájdete v súbore Valentine. xlsx, ktorý je znázornený na obrázku 60-4. Názvy rozsahov priraďujete v bunkách B1: B11 na bunky C1: C11. Rozsah buniek G3: H6 je priradený k vyhľadávaniunázvov. V bunkách C4: C6 sa zadávajú parametre predajnej ceny a nákladov.

Book Image

Do bunky C1 môžete zadať skúšobné výrobné množstvo (40 000 v tomto príklade). Potom v bunke C2 vytvorte náhodné číslo so vzorcami = Rand (). Ako už bolo popísané, môžete simulovať dopyt po karte v bunke C3 so vzorcami VLOOKUP (Rand, Lookup; 2). (Vo vzorci VLOOKUP, Rand je názov bunky priradený k bunke C3, nie na funkciu Rand.)

Počet predaných jednotiek je menší z množstva a dopytu v produkcii. V bunke C8 Vypočítajte naše príjmy pomocou vzorca min (produkovaný, dopyt) * unit_price. V bunke C9 Vypočítajte celkové výrobné náklady pomocou vzorca vyprodukovaného * unit_prod_cost.

Ak vyrábame viac kariet, než je dopyt, počet jednotiek, ktoré ostali rovná produkcii mínus dopyt; v opačnom prípade nie sú žiadne jednotky prenechané. Vypočítavame naše náklady na likvidáciu v bunke C10 so vzorcami unit_disp_cost * if (produced>demand, produkoval – dopyt; 0). Nakoniec sa v bunke C11 vypočítava náš zisk ako výnosy – total_var_cost-total_disposing_cost.

Radi by sme mali účinný spôsob, ako stlačiť kláves F9 mnohokrát (napríklad 1000) pre každé výrobné množstvo a zhodujú sa s naším očakávaným ziskom pre každé množstvo. Táto situácia je tá, v ktorej sa na záchranu nachádza obojsmerná Údajová tabuľka. (Podrobné informácie 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 sa zobrazuje na obrázku 60-5.

Book Image

Do poľa rozsah buniek A16: A1015 zadajte čísla 1 až 1000 (zodpovedajúce našim 1000 skúškam). Jedným z jednoduchých spôsobov, ako vytvoriť tieto hodnoty, je začať zadaním čísla 1 do bunky A16. Vyberte bunku a potom na karte domov v skupine úpravy kliknite na položku výplňa vyberte položku rad , čím sa zobrazí dialógové okno rad . V dialógovom okne rad , ktoré je znázornené na obrázku 60-6, zadajte hodnotu kroku 1 a hodnotu zarážky 1000. V časti Rady v oblasti vyberte možnosť stĺpce a potom kliknite na tlačidlo OK. Čísla 1 – 1 000 sa zadajú do stĺpca A začínajúce v bunke A16.

Book Image

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

Teraz sme pripravení vystačiť Excel na simuláciu 1000 iterácií dopytu po jednotlivých výrobných množstvách. Vyberte rozsah tabuľky (A15: E1014) a potom v skupine Nástroje pre údaje na karte údaje kliknite na položku čo je potrebné analyzovať a potom vyberte položku Tabuľka údajov. Ak chcete nastaviť obojsmernú údajovú tabuľku, vyberte výrobné množstvo (bunka C1) ako vstupnú bunku riadka a vyberte ľubovoľnú prázdnu bunku (I14 bunky) ako Vstupná bunka stĺpca. Po kliknutí na tlačidlo OK Excel simuluje 1000 hodnoty dopytu pre každé poradové množstvo.

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

Skopírovaním z bunky B13 do C13: E13 priemeru vzorca (B16: B1015)vypočítame priemerný simulovaný zisk pre každé výrobné množstvo. Skopírovaním z bunky B14 do C14: E14 vzorec STDEV (B16: B1015)vypočítame smerodajnú odchýlku našich simulovaných ziskov pre každé množstvo objednávky. ZaKaždým, keď stlačíte kláves F9, 1000 iterácie dopytu sú simulované pre každú objednávkové množstvo. Produkovať 40 000 karty vždy prináša najväčší očakávaný zisk. Preto sa zdá, že produkovať 40 000 karty je správne rozhodnutie.

Vplyv rizika na naše rozhodnutie     Ak by sme vyprodukovali 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 sme veľmi odmietavý rizikom, produkovať 20 000 karty môžu byť správnym rozhodnutím. Mimochodom, produkovať 10 000 karty vždy má štandardnú odchýlku 0 kariet, pretože ak vyrábame 10 000 kariet, budeme vždy predávať všetky z nich bez akýchkoľvek zvyškov.

Poznámka:  V tomto zošite je možnosť výpočtu nastavená na možnosť automaticky okrem tabuliek. (Pomocou príkazu výpočet v skupine výpočet na karte Vzorce.) Toto nastavenie zabezpečí, že tabuľka údajov sa neprepočíta, pokiaľ nebudeme stláčať kláves F9, čo je dobrý nápad, pretože veľká tabuľka údajov spomaľuje prácu, ak sa prepočíta pri každom zadaní do hárka. Všimnite si, že v tomto príklade sa pri každom stlačení klávesu F9 zmení priemerný zisk. Stáva sa to preto, lebo zakaždým, keď stlačíte kláves F9, sa na generovanie požiadaviek pre každé množstvo objednávok použije iný sled náhodných čísel 1000.

Interval spoľahlivosti pre stredný zisk     Prirodzená otázka, ktorá sa má v tejto situácii spýtať, je, do akého intervalu sme 95 percenta, či sa skutočný priemerný zisk bude klesať? Tento interval sa nazýva interval spoľahlivosti 95 percenta pre priemerný zisk. Interval spoľahlivosti 95 percenta pre strednú hodnotu akéhokoľvek simulačného výstupu sa vypočíta podľa tohto vzorca:

Book Image

V bunke J11 vypočítate dolnú hranicu intervalu spoľahlivosti 95 percentuálneho podielu na priemernom zisku pri 40 000 kalendárov, ktoré sú vyrobené so vzorcami D13 – 1.96 * D14/SQRT (1000). V bunke J12 vypočítate horný limit pre náš interval spoľahlivosti 95 percenta so vzorcami D13 + 1.96 * D14/SQRT (1000). Tieto výpočty sú znázornené na obrázku 60-7.

Book Image

Sme 95 percenta istí, že náš priemerný zisk pri objednávke 40 000 kalendárov je medzi $56 687 a $62 589.

  1. Predajca GMC je presvedčený, že dopyt po 2005 vySlanci sa zvyčajne distribuuje s priemerom 200 a smerodajnou odchýlkou 30. Jeho náklady na prijatie vySlanca je $25 000 a on predáva vySlanec za $40 000. Polovica všetkých vyslancov, ktoré sa nepredávajú za plnú cenu, sa môže predať za $30 000. Uvažuje o objednaní 200, 220, 240, 260, 280 alebo 300 vyslancov. Koľko by ste mali objednať?

  2. Malý supermarket sa snaží zistiť, koľko kópií časopisu People by mali každý týždeň nariadiť. Domnievajú sa, že ich dopyt po ľuďoch sa riadi týmito diskrétnymi náhodnými premennými:

    Požiadanie

    Pravdepodobnosť

    15

    0,10

    20

    0,20

    25

    0,30

    30

    0,25

    35

    0,15

  3. Supermarket platí $1,00 pre každú kópiu ľudí a predáva ho za $1,95. Každá Nepredaná kópia sa môže vrátiť na $0,50. Koľko kópií osôb by malo uložiť objednávku?

Potrebujete ďalšiu pomoc?

Vždy sa môžete opýtať odborníka v komunite technikov pre Excel, získať podporu v rámci komunity lokality Answers alebo navrhnúť novú funkciu či vylepšenie na lokalite Excel User Voice.

Rozšírte svoje zručnosti práce s balíkom Office
Preskúmať školenie
Buďte medzi prvými, ktorí získajú nové funkcie
Pridajte sa k insiderom pre Office

Boli tieto informácie užitočné?

Ďakujeme za vaše pripomienky!

Ďakujeme vám za pripomienky. Pravdepodobne vám pomôže, ak vás spojíme s pracovníkom podpory pre Office.

×