Simulación de Monte Carlo en Excel: guía completa paso a paso

¿Alguna vez te has preguntado cómo tomar decisiones más acertadas frente a situaciones inciertas? ¿O cómo predecir el comportamiento futuro de una variable sin tener que depender de modelos deterministas rígidos? Pues bien, aquí es donde entra en juego la simulación de Monte Carlo, una herramienta poderosa que, combinada con Excel, puede facilitarnos el análisis y la toma de decisiones con un enfoque más probabilístico y realista.

La simulación de Monte Carlo en Excel se ha convertido en una técnica popular, accesible y extremadamente útil tanto en el mundo empresarial como académico. Desde la gestión de inventarios hasta el análisis financiero o la previsión de demanda, esta técnica permite evaluar múltiples escenarios posibles cuando las variables implicadas son inciertas. Vamos a ver, paso a paso, cómo aplicar todo esto en Excel, qué métodos existen, qué herramientas complementarias puedes usar y por qué es una opción tan potente para modelar la realidad.

¿Qué es la simulación de Monte Carlo?

La simulación de Monte Carlo es una técnica matemática que se basa en la generación de números aleatorios para simular distintas combinaciones posibles de variables que siguen distribuciones estadísticas concretas. Se utiliza para evaluar qué puede suceder en condiciones de incertidumbre, permitiendo prever una gama de resultados posibles y su probabilidad asociada.

Su origen se remonta a los trabajos de Stan Ulam y John von Neumann durante la década de 1940, cuando desarrollaban modelos asociados al movimiento aleatorio de neutrones. A día de hoy, esta técnica se aplica a una infinidad de campos: finanzas, logística, ingeniería, medicina, incluso en ámbitos sociales.

Uno de los grandes atractivos de esta técnica es que puede implementarse fácilmente utilizando herramientas como Excel. Gracias a funciones integradas como ALEATORIO(), BUSCARV() o PROMEDIO(), es posible construir modelos muy potentes que nos ayudan a tomar decisiones basadas en el análisis de riesgo.

Simulación de Montecarlo: Qué es, cómo funciona y aplicaciones

Cómo construir una simulación básica en Excel

Vamos a empezar desde lo más básico, ilustrando el proceso con un ejemplo sencillo. Supongamos que tenemos una tarjeta de felicitación que se vende a 4 € y cuyo coste de producción es de 1,5 €. Si no se venden, desecharlas cuesta 0,2 € por unidad. La demanda, sin embargo, es incierta y se comporta como una variable aleatoria discreta con ciertas probabilidades asociadas.

Estas demandas posibles pueden ser 10.000, 20.000, 40.000 y 60.000 unidades, con probabilidades del 10 %, 35 %, 30 % y 25 % respectivamente. Lo primero será replicar esta distribución en una hoja de Excel y asignarle probabilidades acumuladas que nos permitan simularla fácilmente.

Para generar la demanda aleatoriamente, puedes usar la función ALEATORIO() para obtener un valor entre 0 y 1. Luego, con BUSCARV(), puedes enlazar ese valor con un suceso concreto de demanda según el rango de probabilidades acumuladas que tengas configurado.

Modelo de ingresos y costes con variabilidad

Una vez hemos simulado la demanda, necesitamos establecer cómo se comportan los ingresos, los costes y el beneficio. Las fórmulas en Excel serían las siguientes:

• Ingresos: =MIN(producido, demanda)*precio_venta
• Coste de producción: =producido*coste_unitario
• Coste de desecho: =SI(producido>demanda, (producido-demanda)*coste_desecho, 0)
• Beneficio: =ingresos-coste_producción-coste_desecho

Estas fórmulas deben replicarse para cada iteración que quieras simular. Si haces 1000 simulaciones para cada cantidad de producción posible, puedes obtener una estimación muy buena de cuál te proporciona el mayor beneficio esperado.

Uso de tablas de datos para múltiple iteración

En Excel puedes usar una tabla de datos bidireccional para simular 1000 iteraciones para diferentes cantidades de producción. La clave está en enlazar esa tabla con una fórmula de beneficio calculada que dependa de una celda de entrada (la cantidad de producción).

Por ejemplo, puedes colocar las producciones en la parte superior de la tabla, enumerar del 1 al 1000 en la primera columna y referenciar la celda del beneficio en la esquina superior izquierda de la tabla. Luego vas al menú de Datos → Análisis Y si → Tabla de datos y configuras como celda de entrada de fila la cantidad de producción. Así Excel realizará automáticamente 1000 simulaciones por cada cantidad.

Al calcular el promedio con PROMEDIO() y la desviación estándar con DESVEST(), puedes evaluar no solo el beneficio esperado, sino también su variabilidad o riesgo.

Métodos de distribución con el complemento XLSTAT

Si necesitas algo más avanzado, puedes valerte del complemento XLSTAT-Sim. Este módulo permite implementar distribuciones complejas y realizar simulaciones con mayor precisión. XLSTAT permite trabajar con más de 20 tipos de distribuciones, desde la triangular hasta la lognormal o Weibull.

Una ventaja de XLSTAT es que puede tener en cuenta correlaciones entre variables. Si dos variables aleatorias están relacionadas mediante un coeficiente de correlación, XLSTAT permite respetar dicha dependencia en las simulaciones mediante matrices de covarianza o correlación.

El complemento permite configurar iteraciones, condiciones de parada por convergencia, control de variables clave y análisis de sensibilidad. Incluso puedes personalizar el color de fondo y texto para identificar claramente cada grupo de celdas según su función (distribución, resultado, escenario, etc.).

Diseño de simulación con variables discretas

Un ejemplo muy útil consiste en simular la venta de productos estacionales (como calendarios o licencias de software) donde una vez pasado el periodo previsto, los productos pierden su valor. Estos escenarios son idóneos para la simulación de Monte Carlo.

Se define una cantidad de pedido y se generan 1000 iteraciones para cada caso. Basándonos en la distribución de ventas (frecuencia relativa de valores históricos), generamos muestras aleatorias vinculando cada número aleatorio generado con un valor de la variable aleatoria según su intervalo. Así reproducimos la distribución inicial.

Usamos luego fórmulas similares a las anteriores para calcular ventas, beneficios, costes de reposición o sobrantes. Cada iteración refleja un escenario futuro posible.

Una vez finalizado el proceso podemos comparar el beneficio esperado de pedir 100, 150, 200, 250 o 300 unidades y decidir en consecuencia cuál es la mejor opción. Este método puede aplicarse prácticamente a cualquier negocio con demanda incierta.

Distribuciones más frecuentes: cómo generarlas en Excel

La función ALEATORIO() de Excel genera valores entre 0 y 1 con distribución uniforme. Pero para simular variables con otras distribuciones, necesitamos transformar ese valor. Eso se puede hacer mediante el uso de inversas de funciones de distribución o con funciones específicas de Excel como:

• =DISTR.NORM.INV(ALEATORIO();μ;σ) para normal
• =-LN(ALEATORIO())*β para exponencial
• =b*(-LN(ALEATORIO()))^(1/a) para Weibull
• =a+(b-a)*ALEATORIO() para uniforme entre a y b

Estas fórmulas aprovechan el método de la transformada inversa, que permite transformar una distribución uniforme en otra distribución cualquiera para la cual se conozca su inversa acumulada.

Simulación con variables continuas y dependientes

Otro ejemplo relevante tiene que ver con los tiempos de respuesta de servidores. Supón que varias bases de datos están alojadas en servidores distintos y cada uno responde con una variabilidad normal. Puedes simular 1000 peticiones y comparar en cuántas iteraciones el servidor A es más rápido que el B, qué tiempo medio esperarías y cuánta variabilidad hay.

Analizando el número de iteraciones en que un servidor es más rápido que otro, puedes tomar decisiones sobre migraciones, cambios de proveedor o ajustes en las configuraciones del sistema. Además, puedes estimar intervalos de confianza del 95 % para evaluar la robustez de tu estimación.

Potencia tu análisis de riesgo financiero con Excel y Monte Carlo

Si tu objetivo es evaluar el Valor en Riesgo (VaR), Excel te da muchas herramientas. El VaR mide cuánto podrías perder con cierta probabilidad (por ejemplo, 5 %) durante un periodo concreto. Pero si la distribución de tus retornos no es normal o contiene colas más pesadas, una simulación Monte Carlo será más precisa que métodos paramétricos.

Construyes distintas trayectorias simuladas de los precios o flujos de caja, calculas la pérdida en cada una y analizas el percentil deseado. Algunas plantillas gratuitas disponibles online ya hacen este cálculo incluyendo versiones con y sin macros.

Este enfoque también es útil en análisis de inversiones, flujos de caja, amortizaciones y otros aspectos financieros.

Herramientas externas y complementos recomendados

Existen diversas herramientas que mejoran y amplían la experiencia de simulación en Excel:

• @Risk: complemento para análisis de riesgo y simulación.
• Crystal Ball: simulación y modelado de incertidumbre.
• SimTools.xla: herramienta ligera para simulaciones rápidas.
• Insight.xla: facilita el modelado para usuarios sin conocimientos de programación.

Estas soluciones suelen tener soporte para generar gráficos avanzados, análisis de sensibilidad, y permiten gestionar modelos más complejos con mayor eficiencia.

Además, si tienes formación en VBA, puedes implementar tus propias funciones personalizadas de generación de distribuciones, permitiéndote llevar tus modelos al siguiente nivel.

La simulación de Monte Carlo con Excel es una habilidad poderosa, que permite modelar la incertidumbre y convertirla en decisiones mejor informadas. Gracias a las herramientas comentadas y los distintos enfoques aplicables, puedes poner en práctica esta metodología tanto para problemas simples como para proyectos complejos en los que el azar tenga un rol predominante. Excel, con su flexibilidad y capacidad de automatización, se convierte en una plataforma perfecta para integrar simulación estadística y análisis de riesgos en tu entorno de trabajo diario.