MODELOS DE SIMULACIÓN MONTE CARLO EN EXCEL: ESTRATEGIAS Y EJEMPLOS

DE LOS CASINOS DE MONTE CARLO A TU HOJA DE CÁLCULO: DESCUBRE CÓMO IMPLEMENTAR LA SIMULACIÓN MONTE CARLO EN EXCEL, PARA PREDECIR DEMANDA, OPTIMIZAR PRODUCCIÓN Y MINIMIZAR RIESGOS FINANCIEROS

La simulación Monte Carlo es una técnica utilizada para comprender el impacto de la incertidumbre y la variabilidad en modelos predictivos y de toma de decisiones. Es ampliamente utilizada en finanzas, gestión de riesgos, ingeniería, y muchas otras disciplinas.

ORIGEN DEL NOMBRE

El nombre «Monte Carlo» proviene del famoso distrito de Mónaco conocido por sus casinos. En la década de 1940, los matemáticos que trabajaban en el Proyecto Manhattan, incluido Stanislaw Ulam y John von Neumann, utilizaron esta técnica para resolver problemas complejos en física nuclear. La aleatoriedad y la probabilidad asociadas con los juegos de azar de Monte Carlo reflejan la naturaleza aleatoria y estocástica de la simulación.

¿DONDE SE UTILIZA LA SIMULACIÓN MONTE CARLO?

La simulación Monte Carlo es utilizada por profesionales en diversas áreas:

• Finanzas: Para valorar opciones, evaluar riesgos y pronosticar precios de activos.
• Gestión de Riesgos: Para modelar la incertidumbre en los proyectos y evaluar el impacto de diferentes riesgos.
• Ingeniería: Para simular procesos de producción y mejorar la calidad y eficiencia.
• Ciencias Actuariales: Para calcular probabilidades de eventos y determinar primas de seguros.
• Investigación Científica: Para simular fenómenos naturales y experimentos.

USANDO EXCEL PARA LA SIMULACIÓN MONTE CARLO

FUNCIÓN ALEATORIO()

En la función ALEATORIO, no hay argumentos necesarios. Simplemente escribe =ALEATORIO() y Excel generará un número aleatorio entre 0 y 1 cada vez que la hoja de cálculo se actualice o recalcula.

=ALEATORIO()

1. SIMULANDO VALORES DE UNA VARIABLE ALEATORIA DISCRETA

Para simular valores de una variable aleatoria discreta, por ejemplo, el resultado de lanzar un dado, puedes usar las funciones ENTERO y ALEATORIO en combinación:

• • Generamos un número aleatorio entre 0 y 1 con la función ALEATORIO. Esto generará un número decimal aleatorio entre 0 y 1 cada vez que se recalculen las fórmulas en Excel.
  • Multiplicamos el número aleatorio por 6. Esto escalará el número aleatorio para que esté entre 0 y 6 (exclusivo).
  • Con la función ENTERO convertimos el número decimal a un entero y luego sumamos 1 para ajustar el rango de 1 a 6. La fórmula completa será:

=ENTERO(ALEATORIO()*6 + 1

El resultado ahora simulará el lanzamiento de un dado, mostrando un número entero entre 1 y 6 cada vez que se recalcule la hoja de cálculo (puedes presionar F9 para recalcular).

2. SIMULANDO VALORES DE UNA VARIABLE ALEATORIA NORMAL

Para simular valores de una variable aleatoria normal con una media (μ) y desviación estándar (σ) específicas, puedes usar la función INV.NORM junto con ALEATORIO:

=INV.NORM(probabilidad; media; desviacion_estandar)

Por ejemplo, para una distribución normal con media 50 y desviación estándar 10 y utilizando la función ALEATORIO:

=INV.NORM(ALEATORIO(); 50; 10)

Cada vez que recalculas las fórmulas (por ejemplo, presionando F9), obtendrás un nuevo conjunto de valores que simulan una variable aleatoria normal con la media y desviación estándar que has definido. Puedes usar estos datos para análisis estadísticos o para modelar fenómenos que siguen una distribución normal en tus proyectos o estudios.

CASO PRÁCTICO: DETERMINACIÓN DE LA PRODUCCIÓN DE TARJETAS DE FELICITACIÓN

Supongamos que una empresa de tarjetas de felicitación quiere determinar cuántas tarjetas producir para la próxima temporada. La demanda de tarjetas es incierta y puede ser modelada usando una distribución normal con una media de 10,000 tarjetas y una desviación estándar de 2,000 tarjetas.

1. CONFIGURAR LA SIMULACIÓN

• • En la columna A, pon los números de simulación (1, 2, 3, …, 100).

Una forma sencilla de crear estos valores es comenzar escribiendo 1 en la celda A2. Luego en la pestaña Inicio, busca el grupo de comandos llamado Edición. Dentro del grupo Edición, haz clic en el botón Rellenar y en el menú desplegable que aparece, selecciona Serie.
En el cuadro de diálogo Serie, selecciona las siguientes opciones:

• • • Series en: Columna (asegúrate de que esta opción esté seleccionada para llenar hacia abajo en la columna).
    • Tipo: Lineal.
    • Valor de paso: 1 (esto indica que cada número se incrementará en 1).
    • Valor de parada: 100 (esto indica el último número que deseas en tu serie).
    • Haz clic en Aceptar.  Los números del 1 al 100 se introducirán en la columna A a partir de la celda A2.

• • En la columna B, genera los valores de demanda utilizando la función NORM.INV y ALEATORIO() y arrastramos la fórmula hacia abajo

=NORM.INV(ALEATORIO(); 10000; 2000)

2. CALCULAR COSTOS Y BENEFICIOS

Suponiendo que la tarjeta de felicitación se vende por $4.00, y el costo de producir cada tarjeta es $1.50.

• • En la columna C, calcula los ingresos basados en la demanda simulada.
  • En la columna D, calcula los costos de producción.

3. ANALIZAR LOS RESULTADOS

• • Usa la función PROMEDIO para encontrar el ingreso promedio.
  • Usa la función DESVEST para encontrar la desviación estándar de los ingresos.
  • Usa GRÁFICOS para visualizar la distribución de ingresos y otros KPIs.

Recuerda que el objetivo es encontrar la cantidad de impresión que, en promedio, te dará el mayor beneficio después de realizar todas las simulaciones. Puedes usar los resultados para tomar una decisión informada sobre cuántas tarjetas imprimir.

Este es un ejemplo muy básico de simulación de Monte Carlo que puedes intentar como introducción. Con práctica, podrás aplicar esta técnica a situaciones más complejas y con diferentes distribuciones de probabilidad. ¡Esperamos que este ejemplo te sea útil para comenzar!

CONCLUSIÓN

La simulación Monte Carlo en Excel es una poderosa herramienta para modelar la incertidumbre y tomar decisiones informadas en situaciones complejas. Al comprender y aplicar estas técnicas, las empresas y profesionales pueden mejorar sus estrategias y mitigar riesgos de manera efectiva. Utilizando funciones como ALEATORIO() y NORM.INV(), puedes crear modelos que reflejen la variabilidad del mundo real y optimizar tus procesos y decisiones financieras.

¡Explora estas técnicas en tu próximo proyecto de Excel y descubre el poder de la simulación Monte Carlo!

» Si te gustan nuestros blogs, compártelo con tus amigos. Puedes seguirnos en Facebook e Instagram.

» Déjanos tu comentario al final de esta página.

» Explora más sobre nosotros directo aquí: Curso de Excel Online