Desarrollaremos una simulación Monte Carlo usando Microsoft Excel y un juego de dados. La simulación de Monte Carlo es un método numérico matemático que utiliza sorteos aleatorios para realizar cálculos y problemas complejos. Hoy en día, es ampliamente utilizado y juega un papel clave en varios campos como las finanzas, la física, la química, la economía y muchos otros.

Monte Carlo Simulation

El método Monte Carlo fue inventado por Nicolas Metropolis en 1947 y busca resolver problemas complejos utilizando métodos aleatorios y probabilísticos. El término "Monte Carlo" se origina en el área administrativa de Mónaco conocida popularmente como un lugar donde las elites europeas juegan. Usamos el método de Monte Carlo cuando el problema es demasiado complejo y difícil de hacer por cálculo directo. Un gran número de iteraciones permite una simulación de la distribución normal.

El método de simulación Monte Carlo calcula las probabilidades para integrales y resuelve ecuaciones diferenciales parciales, introduciendo así un enfoque estadístico del riesgo en una decisión probabilística. Aunque existen muchas herramientas estadísticas avanzadas para crear simulaciones de Monte Carlo, es más fácil simular la ley normal y la ley uniforme usando Microsoft Excel y eludir los fundamentos matemáticos.

Para la simulación Monte Carlo, aislamos una serie de variables clave que controlan y describen el resultado del experimento y asignamos una distribución de probabilidad después de que se realiza una gran cantidad de muestras aleatorias. Tomemos un juego de dados como modelo.

Juego de dados

Así es como se desarrolla el juego de dados:

• El jugador lanza tres dados que tienen 6 lados 3 veces.

• Si el total de los 3 lanzamientos es 7 u 11, el jugador gana.

• Si el total de los 3 lanzamientos es: 3, 4, 5, 16, 17 o 18, el jugador pierde.

• Si el total es cualquier otro resultado, el jugador vuelve a jugar y vuelve a tirar el dado.

• Cuando el jugador tira nuevamente el dado, el juego continúa de la misma manera, excepto que el jugador gana cuando el total es igual a la suma determinada en la primera ronda.

También se recomienda usar una tabla de datos para generar los resultados. Además, se necesitan 5.000 resultados para preparar la simulación Monte Carlo.

Paso 1: eventos rodantes de dados

Primero, desarrollamos un rango de datos con los resultados de cada uno de los 3 dados para 50 rollos. Para hacer esto, se propone utilizar la función "RANDBETWEEN (1. 6)". Por lo tanto, cada vez que hacemos clic en F9, generamos un nuevo conjunto de resultados de desplazamiento. La celda "Resultado" es la suma total de los resultados de los 3 rollos.

Paso 2: rango de resultados

Luego, necesitamos desarrollar un rango de datos para identificar los posibles resultados para la primera ronda y las siguientes rondas. Se proporciona debajo de un rango de datos de 3 columnas.En la primera columna, tenemos los números del 1 al 18. Estas cifras representan los resultados posibles después de tirar los dados 3 veces: el máximo es 3 * 6 = 18. Observará que para las celdas 1 y 2, los resultados son N / A, ya que es imposible obtener 1 o 2 con 3 dados. El mínimo es 3.

En la segunda columna, se incluyen las posibles conclusiones después de la primera ronda. Como se indicó en la declaración inicial, o el jugador gana (gana) o pierde (pierde) o vuelve a jugar (repetir), dependiendo del resultado (el total de 3 tiradas de dados).

En la tercera columna, se registran las posibles conclusiones para rondas posteriores. Podemos lograr estos resultados usando una función "If. "Esto garantiza que si el resultado obtenido es equivalente al resultado obtenido en la primera ronda, ganamos, de lo contrario seguiremos las reglas iniciales de la jugada original para determinar si volvemos a tirar los dados.

Paso 3: Conclusiones

En este paso, identificamos el resultado de las 50 tiradas de dados. La primera conclusión se puede obtener con una función de índice. Esta función busca los resultados posibles de la primera ronda, la conclusión correspondiente al resultado obtenido. Por ejemplo, al obtener 6, como es el caso en la imagen de abajo, jugamos nuevamente.

Uno puede obtener los resultados de otras tiradas de dados, usando una función "O" y una función de índice anidada en una función "Si". Esta función le dice a Excel: "Si el resultado anterior es Ganar o Perder", dejen de tirar los dados porque una vez que hemos ganado o perdido hemos terminado. De lo contrario, vamos a la columna de las siguientes conclusiones posibles e identificamos la conclusión del resultado.

…

Paso 4: Número de rollos de dados

Ahora, determinamos el número de tiradas de dados requeridas antes de perder o ganar. Para hacer esto, podemos usar una función "Countif", que requiere que Excel cuente los resultados de "Re-roll" y agregue el número 1 a la misma. Agrega uno porque tenemos una ronda extra, y obtenemos un resultado final (ganar o perder).

Paso 5: Simulación

Desarrollamos una gama para rastrear los resultados de diferentes simulaciones. Para hacer esto, crearemos tres columnas. En la primera columna, una de las cifras incluidas es 5, 000. En la segunda columna buscaremos el resultado después de 50 tiradas de dados. En la tercera columna, el título de la columna, buscaremos la cantidad de dados antes de obtener el estado final (ganar o perder).

Luego, crearemos una tabla de análisis de sensibilidad utilizando los datos de características o la tabla de datos de tabla (esta sensibilidad se REPLACEará en la segunda y tercera columnas). En este análisis de sensibilidad, los números de eventos de 1 - 5, 000 deben REPLACEarse en la celda A1 del archivo. De hecho, uno podría elegir cualquier celda vacía. La idea es simplemente forzar un recálculo cada vez y así obtener nuevas tiradas de dados (resultados de nuevas simulaciones) sin dañar las fórmulas en su lugar.

Paso 6: Probabilidad

Finalmente podemos calcular las probabilidades de ganar y perder. Hacemos esto usando la función "Countif".La fórmula cuenta el número de "ganar" y "perder" y luego se divide por el número total de eventos, 5, 000, para obtener la proporción respectiva de uno y el otro. Finalmente vemos a continuación que la probabilidad de obtener un resultado Ganador es 73. 2% y obtener un resultado Perder es, por lo tanto, 26. 8%.