Vytvoření simulace Monte Carlo pomocí Excelu

Simulace Monte Carlo může být vyvinuta pomocí aplikace Microsoft Excel a hry s kostkami. Simulace Monte Carlo je matematická numerická metoda, která používá náhodné losování k provádění výpočtů a složitých problémů. Dnes se široce používá a hraje klíčovou roli v různých oblastech, jako je finance, fyzika, chemie a ekonomie.

Simulace Monte Carlo

Metoda Monte Carlo byla vynalezena Nicolas Metropolis v roce 1947 a snaží se řešit složité problémy pomocí náhodných a pravděpodobnostních metod. Termín „Monte Carlo“ pochází z administrativní oblasti Monaka, která je známá jako místo, kde se evropské elity hazardují. Používáme metodu Monte Carlo, když je problém příliš složitý a přímý výpočet obtížný. Velký počet iterací umožňuje simulaci normálního rozdělení.

Simulační metoda Monte Carlo počítá pravděpodobnosti pro integrály a řeší parciální diferenciální rovnice, čímž zavádí statistický přístup k riziku v pravděpodobnostním rozhodnutí. Přestože existuje mnoho pokročilých statistických nástrojů pro vytváření simulací Monte Carlo, je jednodušší simulovat normální zákon a jednotné právo pomocí aplikace Microsoft Excel a obejít matematické základy.

Pro simulaci Monte Carlo izolováme řadu klíčových proměnných, které řídí a popisují výsledek experimentu, a poté, co se provede velký počet náhodných vzorků, přiřadíme rozdělení pravděpodobnosti. Vezměme si kostky jako model.

Hra kostky

Jak se hra kostky valí:

• Hráč hodí 3 kostky, které mají 6 stran 3krát.

• Pokud je celkový počet 3 hodů 7 nebo 11, hráč vyhraje.

• Pokud je celkový počet 3 hodů: 3, 4, 5, 16, 17 nebo 18, hráč prohraje.

• Pokud je součet jakýmkoli jiným výsledkem, hráč hraje znovu a hodí kostkami.

• Když hráč znovu hodí kostkou, hra pokračuje stejným způsobem s tou výjimkou, že hráč vyhraje, když se součet rovná součtu určenému v prvním kole.

Ke generování výsledků se také doporučuje použít tabulku údajů. Kromě toho je pro přípravu simulace Monte Carlo zapotřebí 5 000 výsledků.

Krok 1: Rolling Events

Nejprve vyvineme řadu dat s výsledky každé ze 3 kostek po 50 rolích. Za tímto účelem se navrhuje použít funkci „RANDBETWEEN (1, 6)“. Takže pokaždé, když klikneme na F9, vygenerujeme novou sadu výsledků válcování. Buňka „Výsledek“ je součtem výsledků z 3 rolí.

Krok 2: Rozsah výsledků

Pak musíme vyvinout řadu údajů, abychom identifikovali možné výsledky pro první kolo a následující kola. K dispozici je datový rozsah ve 3 sloupcích. V prvním sloupci máme čísla 1 až 18. Tato čísla představují možné výsledky po trojím házení kostkami: maximum je 3 * 6 = 18. Všimněte si, že pro buňky 1 a 2 jsou nálezy N / A, protože není možné získat 1 nebo 2 pomocí 3 kostek. Minimum je 3.

Ve druhém sloupci jsou uvedeny možné závěry po prvním kole. Jak je uvedeno v úvodním prohlášení, buď hráč vyhraje (Win) nebo prohraje (Prohraje), nebo se přehraje (Re-roll), v závislosti na výsledku (celkem 3 kostky).

Ve třetím sloupci jsou zaznamenány možné závěry následujících kol. Těchto výsledků můžeme dosáhnout pomocí funkce „IF“. Tím je zajištěno, že pokud je získaný výsledek rovnocenný výsledku získanému v prvním kole, vyhráváme, jinak se budeme řídit počátečními pravidly původní hry a určíme, zda kostky znovu hodíme.

Krok 3: Závěry

V tomto kroku identifikujeme výsledek 50 kostek. První závěr lze získat pomocí funkce indexu. Tato funkce prohledává možné výsledky prvního kola, přičemž závěr odpovídá získanému výsledku. Například, když získáme 6, hrajeme znovu.

Je možné získat nálezy dalších kostkových rolí pomocí funkce „OR“ a funkce indexu vnořenou ve funkci „IF“. Tato funkce říká Excelu: „Pokud je předchozí výsledek Win nebo Lose, “ zastavte kostky, protože jakmile jsme vyhráli nebo prohráli, skončili jsme. Jinak jdeme do sloupce následujících možných závěrů a identifikujeme závěr výsledku.

Krok 4: Počet kostek

Nyní určíme počet požadovaných kostek, než ztratíte nebo vyhrajete. K tomu můžeme použít funkci „COUNTIF“, která vyžaduje, aby Excel spočítal výsledky funkce „Převrátit“ a přidal do ní číslo 1. Přidá jeden, protože máme jedno kolo navíc a získáme konečný výsledek (vyhrajte nebo prohrajte).

Krok 5: Simulace

Vyvíjíme řadu pro sledování výsledků různých simulací. Za tímto účelem vytvoříme tři sloupce. V prvním sloupci je jedním z 5 000 čísel. Ve druhém sloupci budeme hledat výsledek po 50 kostkách. Ve třetím sloupci, nadpisu sloupce, budeme hledat počet hodů kostkami před získáním konečného stavu (vyhrát nebo prohrát).

Poté vytvoříme tabulku analýzy citlivosti pomocí dat funkce nebo tabulky Tabulka dat (tato citlivost bude vložena do druhé tabulky a třetího sloupce). V této analýze citlivosti musí být do buňky A1 souboru vloženy počty událostí 1 - 5 000. Ve skutečnosti by člověk mohl zvolit jakoukoli prázdnou buňku. Cílem je jednoduše vynutit přepočet pokaždé a získat tak nové kostky (výsledky nových simulací), aniž by došlo k poškození vzorců.

Krok 6: Pravděpodobnost

Můžeme konečně vypočítat pravděpodobnost výhry a prohry. Děláme to pomocí funkce „COUNTIF“. Vzorec počítá počet „vítězství“ a „prohry“, pak se vydělí celkovým počtem 5 000 událostí, aby se získal příslušný podíl jedné a druhé. Konečně vidíme, že pravděpodobnost dosažení výherního výsledku je 73, 2% a získání ztraceného výsledku je tedy 26, 8%.