Vytvorenie simulácie Monte Carlo pomocou Excelu

Simulácia Monte Carlo sa dá vyvinúť pomocou programu Microsoft Excel a hry s kockami. Simulácia Monte Carlo je matematická numerická metóda, ktorá využíva náhodné losovania na vykonávanie výpočtov a zložitých problémov. Dnes sa bežne používa a hrá kľúčovú úlohu v rôznych oblastiach, ako sú financie, fyzika, chémia a ekonómia.

Simulácia Monte Carlo

Metódu Monte Carlo vynašiel Nicolas Metropolis v roku 1947 a snaží sa riešiť zložité problémy pomocou náhodných a pravdepodobnostných metód. Termín „Monte Carlo“ pochádza z administratívnej oblasti Monaka, ktorá je všeobecne známa ako miesto, kde hazardujú európske elity. Používame metódu Monte Carlo, keď je problém príliš komplexný a je ťažké ho urobiť priamym výpočtom. Veľký počet iterácií umožňuje simuláciu normálneho rozdelenia.

Simulačná metóda Monte Carlo počíta pravdepodobnosti pre integrály a rieši parciálne diferenciálne rovnice, čím zavádza štatistický prístup k riziku v pravdepodobnostnom rozhodnutí. Aj keď existuje veľa pokročilých štatistických nástrojov na vytváranie simulácií Monte Carlo, je jednoduchšie simulovať normálny zákon a jednotný zákon pomocou programu Microsoft Excel a obísť matematické základy.

Pri simulácii Monte Carlo izolujeme niekoľko kľúčových premenných, ktoré kontrolujú a opisujú výsledok experimentu, a potom, čo sa vykoná veľký počet náhodných vzoriek, priradíme pravdepodobnostnú distribúciu. Zoberme si kockovú hru ako model.

Hra s kockami

Ako sa hrá hra s kockami:

• Hráč hodí tromi kockami, ktoré majú 6 strán trikrát.

• Ak je celkový počet 3 hodov 7 alebo 11, hráč vyhrá.

• Ak je celkový počet troch hodov: 3, 4, 5, 16, 17 alebo 18, prehrávač prehrá.

• Ak je súčtom akýkoľvek ďalší výsledok, hráč hrá znova a hodí kockami.

• Keď hráč opäť hodí kockami, hra pokračuje rovnakým spôsobom s tým rozdielom, že hráč vyhrá, keď sa celková suma rovná súčtu určenému v prvom kole.

Na vygenerovanie výsledkov sa tiež odporúča použiť tabuľku údajov. Ďalej je potrebných 5 000 výsledkov na prípravu simulácie Monte Carlo.

Krok 1: Kockové valivé udalosti

Najprv vyvineme celý rad údajov s výsledkami každej z 3 kocky po 50 hodoch. Na tento účel sa navrhuje použiť funkciu „RANDBETWEEN (1, 6)“. Zakaždým, keď klikneme na F9, vygenerujeme novú sadu výsledkov roliek. Bunka „Výsledok“ je súčtom výsledkov z 3 roliek.

Krok 2: Rozsah výsledkov

Potom musíme vypracovať celý rad údajov, aby sme identifikovali možné výsledky pre prvé kolo a nasledujúce kolá. K dispozícii je rozsah údajov v 3 stĺpcoch. V prvom stĺpci sú čísla 1 až 18. Tieto čísla predstavujú možné výsledky po trojnásobnom prehadzovaní kociek: maximum je 3 * 6 = 18. Zistíte, že pre bunky 1 a 2 sú zistenia N / A, pretože nie je možné získať 1 alebo 2 pomocou 3 kocky. Minimálne sú 3.

V druhom stĺpci sú zahrnuté možné závery po prvom kole. Ako je uvedené v úvodnom vyhlásení, hráč buď vyhrá (výhra) alebo prehrá (prehra), alebo sa prehrá (opakuje hod) v závislosti od výsledku (celkom 3 hody kockami).

V treťom stĺpci sú zaznamenané možné závery z nasledujúcich kôl. Tieto výsledky môžeme dosiahnuť pomocou funkcie „IF“. To zaisťuje, že ak je dosiahnutý výsledok ekvivalentný výsledku dosiahnutému v prvom kole, vyhráme, inak sa riadime pôvodnými pravidlami pôvodnej hry, aby sme určili, či kocky znovu hodíme.

Krok 3: Závery

V tomto kroku identifikujeme výsledok 50 kockových hodov. Prvý záver je možné získať pomocou indexovej funkcie. Táto funkcia prehľadáva možné výsledky prvého kola, pričom záver zodpovedá dosiahnutému výsledku. Napríklad, keď získame 6, budeme hrať znova.

Dá sa nájsť nálezy iných kockových roliek pomocou funkcie „OR“ a indexovej funkcie vnorenej do funkcie „IF“. Táto funkcia povie Excelu: „Ak je predchádzajúcim výsledkom výhra alebo strata, “ zastavte kocky, pretože akonáhle vyhráme alebo prehráme, skončíme. Inak prejdeme do stĺpca nasledujúcich možných záverov a identifikujeme záver výsledku.

Krok 4: Počet roliek

Teraz určíme požadovaný počet hodov kocky predtým, ako prehráte alebo vyhráte. Na tento účel môžeme použiť funkciu „COUNTIF“, ktorá vyžaduje, aby program Excel spočítal výsledky funkcie „Obnoviť“ a pridal k nej číslo 1. Pridá jeden, pretože máme jedno extra kolo a dostaneme konečný výsledok (výhra alebo prehra).

Krok 5: Simulácia

Vyvíjame škálu na sledovanie výsledkov rôznych simulácií. Za týmto účelom vytvoríme tri stĺpce. V prvom stĺpci je jedným z 5 000 čísel. V druhom stĺpci budeme hľadať výsledok po 50 kockových hodoch. V treťom stĺpci, v názve stĺpca, pred získaním konečného stavu (vyhrať alebo prehrať) vyhľadáme počet hodov kockami.

Potom vytvoríme tabuľku analýzy citlivosti pomocou údajov funkcie alebo tabuľky Tabuľka údajov (táto citlivosť sa vloží do druhej tabuľky a tretieho stĺpca). V tejto analýze citlivosti musia byť do bunky A1 súboru vložené počty udalostí 1 - 5 000. V skutočnosti by bolo možné zvoliť akúkoľvek prázdnu bunku. Cieľom je jednoducho vynútiť prepočítanie zakaždým a získať tak nové kocky (výsledky nových simulácií) bez toho, aby sa poškodili vzorce.

Krok 6: Pravdepodobnosť

Konečne môžeme vypočítať pravdepodobnosť výhry a prehry. Robíme to pomocou funkcie „COUNTIF“. Vzorec počíta počet výhier a prehier a potom sa vydelí celkovým počtom 5 000 udalostí, aby sa získal príslušný pomer jednej a druhej. Konečne vidíme, že pravdepodobnosť dosiahnutia výherného výsledku je 73, 2% a zisk strateného výsledku je 26, 8%.