Creació d'una simulació de Montecarlo mitjançant Excel

Es pot desenvolupar una simulació de Montecarlo utilitzant Microsoft Excel i un joc de daus. La simulació de Montecarlo és un mètode numèric matemàtic que utilitza sortejos aleatoris per realitzar càlculs i problemes complexos. Actualment, és molt utilitzat i té un paper clau en diversos camps com les finances, la física, la química i l’economia.

Simulació de Montecarlo

El mètode de Montecarlo va ser inventat per Nicolas Metropolis el 1947 i busca solucionar problemes complexos mitjançant mètodes aleatoris i probabilístics. El terme "Monte Carlo" prové de la zona administrativa de Mònaco coneguda popularment com a lloc on es juguen les elits europees. Utilitzem el mètode Monte Carlo quan el problema és massa complex i difícil de fer mitjançant càlcul directe. Un gran nombre de iteracions permet simular la distribució normal.

El mètode de simulació de Montecarlo calcula les probabilitats d’integrals i resol equacions diferencials parcials, introduint així un enfocament estadístic del risc en una decisió probabilística. Tot i que existeixen moltes eines estadístiques avançades per crear simulacions de Montecarlo, és més fàcil simular la llei normal i la llei uniforme mitjançant Microsoft Excel i obviar els sotmesos matemàtics.

Per a la simulació de Montecarlo, aïllem una sèrie de variables clau que controlen i descriuen el resultat de l’experiment, i després assignem una distribució de probabilitats després de realitzar un gran nombre de mostres aleatòries. Prenem com a model un joc de daus.

Joc de daus

A continuació, es mostra com es juga el joc de daus:

• El jugador llança tres daus que tenen 6 cares 3 vegades.

• Si el total dels 3 llançaments és de 7 o 11, el jugador guanya.

• Si el total dels tres tirs és: 3, 4, 5, 16, 17 o 18, el jugador perd.

• Si el total és qualsevol altre resultat, el jugador torna a jugar i torna a rodar el dau.

• Quan el jugador torna a llançar els daus, el joc continua de la mateixa manera, tret que el jugador guanyi quan el total és igual a la suma determinada a la primera volta.

També es recomana utilitzar una taula de dades per generar els resultats. A més, es necessiten 5.000 resultats per preparar la simulació de Montecarlo.

Pas 1: Esdeveniments mòbils a daus

Primer, desenvolupem una gamma de dades amb els resultats de cadascun dels 3 daus de 50 rotlles. Per fer-ho, es proposa utilitzar la funció "RANDBETWEEN (1, 6)". Així, cada cop que fem clic a F9, generem un nou conjunt de resultats del rotlle. La cel·la "Resultat" és la suma total dels resultats dels 3 rotllos.

Pas 2: gamma de resultats

Aleshores, hem de desenvolupar una sèrie de dades per identificar els possibles resultats per a la primera ronda i les següents rondes. Hi ha un interval de dades de tres columnes. A la primera columna, tenim els números 1 a 18. Aquestes xifres representen els possibles resultats després de fer rodar el dau tres vegades: el màxim és de 3 * 6 = 18. Notareu que per a les cel·les 1 i 2, les troballes són N / A, ja que és impossible obtenir un 1 o un 2 amb 3 daus. El mínim és de 3.

A la segona columna s’inclouen les possibles conclusions després de la primera volta. Com es va dir a la declaració inicial, el jugador guanya (Guanya) o perd (Perdre), o es reprodueixen (Torna a rodar), segons el resultat (el total de 3 rotllos de daus).

A la tercera columna, es registren les possibles conclusions a les rondes posteriors. Podem obtenir aquests resultats mitjançant la funció "IF". Això garanteix que si el resultat obtingut equival al resultat obtingut a la primera ronda, guanyem, si no, seguim les regles inicials de la jugada original per determinar si tornem a enrotllar els daus.

Pas 3: Conclusions

En aquest pas, identificem el resultat dels 50 rotlles de daus. La primera conclusió es pot obtenir amb una funció índex. Aquesta funció busca els possibles resultats de la primera ronda, la conclusió corresponent al resultat obtingut. Per exemple, en obtenir-ne 6 tornem a jugar.

Es poden obtenir les troballes d'altres rotlles de daus, utilitzant una funció "O" i una funció d'índex enclavada en una funció "IF". Aquesta funció diu a Excel, "Si el resultat anterior és Guanyar o Baixar", deixi de llançar els daus, ja que un cop hem guanyat o hem perdut ja ens queda. En cas contrari, anem a la columna de les conclusions possibles següents i identifiquem la conclusió del resultat.

Pas 4: nombre de rotlles de daus

Ara, determinem el nombre de rodets de daus necessaris abans de perdre o guanyar. Per fer-ho, podem utilitzar una funció "COUNTIF", que requereix que Excel compti els resultats de "Tornar a rodar" i afegir-hi el número 1. Afegeix un perquè tenim una ronda addicional i obtenim un resultat final (guanyem o perdem).

Pas 5: Simulació

Desenvolupem una gamma per fer el seguiment dels resultats de diferents simulacions. Per fer-ho, crearem tres columnes. A la primera columna, una de les xifres incloses és de 5.000. A la segona columna, buscarem el resultat després de 50 rotlles de daus. A la tercera columna, el títol de la columna, buscarem el nombre de rotlles de daus abans d'obtenir l'estat final (guanyar o perdre).

A continuació, crearem una taula d’anàlisi de sensibilitat mitjançant les funcions de dades o la taula de dades de taula (aquesta sensibilitat s’inserirà a la segona taula i a les terceres columnes). En aquesta anàlisi de sensibilitat, s'ha d'inserir el nombre d'esdeveniments d'1 - 5.000 a la cel·la A1 del fitxer. De fet, es podria triar qualsevol cel·la buida. La idea és simplement forçar una recalculació cada vegada i obtenir així nous rotllos de daus (resultats de noves simulacions) sense danyar les fórmules al seu lloc.

Pas 6: probabilitat

Per fi podem calcular les probabilitats de guanyar i perdre. Ho fem mitjançant la funció "COUNTIF". La fórmula compta el nombre de "guanyar" i "perdre" després es divideix en el nombre total d'esdeveniments, 5.000, per obtenir la proporció respectiva d'un i de l'altre. Finalment, veiem que la probabilitat d'obtenir un resultat Win és del 73, 2% i obtenir un resultat Lose és, per tant, del 26, 8%.