Crear una simulación de Monte Carlo usando Excel

Se puede desarrollar una simulación de 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, se usa ampliamente y desempeña un papel clave en diversos campos, como las finanzas, la física, la química y la economía.

Simulación del Monte Carlo

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 juegan las élites europeas. Utilizamos 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 de 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 utilizando Microsoft Excel y evitar los fundamentos matemáticos.

Para la simulación de Monte Carlo, aislamos una serie de variables clave que controlan y describen el resultado del experimento, luego 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 funciona 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 juega de nuevo y vuelve a tirar los dados.

• Cuando el jugador lanza los dados nuevamente, 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 de Monte Carlo.

Paso 1: Eventos de lanzamiento de dados

Primero, desarrollamos un rango de datos con los resultados de cada uno de los 3 dados para 50 tiradas. 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. 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. Hay un rango de datos de 3 columnas. En la primera columna, tenemos los números del 1 al 18. Estas cifras representan los posibles resultados después de tirar los dados 3 veces: el máximo es 3 * 6 = 18. Notarás que para las celdas 1 y 2, los resultados son N / A ya que es imposible obtener un 1 o un 2 usando 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, el jugador gana (gana) o pierde (pierde) o repite (vuelve a tirar), dependiendo del resultado (el total de 3 tiradas de dados).

En la tercera columna, se registran las posibles conclusiones de las rondas posteriores. Podemos lograr estos resultados usando la función "SI". Esto asegura que si el resultado obtenido es equivalente al resultado obtenido en la primera ronda, ganamos, de lo contrario seguimos 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 posibles resultados de la primera ronda, la conclusión correspondiente al resultado obtenido. Por ejemplo, al obtener 6 jugamos de nuevo.

Uno puede obtener los resultados de otras tiradas de dados, usando una función "OR" 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", deje 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. 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 el número de tiradas 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 Tabla de datos (esta sensibilidad se insertará en la segunda tabla y en las terceras columnas). En este análisis de sensibilidad, los números de eventos de 1 a 5, 000 deben insertarse 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 otro. Finalmente vemos que la probabilidad de obtener un resultado Win es 73.2% y, por lo tanto, obtener un resultado Lose es 26.8%.