curso
Simulación Monte Carlo en Excel: Guía completa
Los métodos de Montecarlo, originalmente llamados así por el Casino de Montecarlo en Mónaco, se utilizan ampliamente en campos como las finanzas, la ingeniería, la cadena de suministro y la ciencia para modelar fenómenos con una incertidumbre significativa en sus entradas.
Pero, ¿qué es la simulación de Montecarlo? ¿Cómo funciona? ¿Y cómo puedo realizar la simulación y analizar los resultados?
Este tutorial te introducirá en la simulación de Montecarlo y en los conceptos estadísticos relevantes que subyacen a la técnica. También pondremos en práctica la simulación de Montecarlo en Excel, familiarizándote con las funciones integradas pertinentes de Excel.
Por último, el tutorial te dejará con las mejores prácticas, técnicas avanzadas y recursos adicionales, haciendo de este tutorial tu guía única para aprender todo sobre la simulación Monte Carlo en Microsoft Excel.
¿Qué es la Simulación de Montecarlo?
La Simulación de Montecarlo es una técnica matemática utilizada para modelizar la probabilidad de distintos resultados en un proceso que no puede predecirse fácilmente debido a la intervención de variables aleatorias.
Es una poderosa herramienta para comprender el impacto del riesgo y la incertidumbre en diversos campos. El método se basa en el muestreo aleatorio repetido para simular el comportamiento de sistemas y procesos complejos.
El problema se modela primero mediante una distribución de probabilidad para cada variable que tiene incertidumbre inherente. A continuación, se extraen grandes cantidades de muestras aleatorias de estas distribuciones de probabilidad, y estas muestras se utilizan para calcular los resultados. Este proceso se repite muchas veces para crear una distribución de posibles resultados, que puede analizarse estadísticamente para proporcionar predicciones sobre cómo se comportará un sistema.
Así que, en términos sencillos. La simulación de Montecarlo es una técnica que predice cómo se comportarán los sistemas complejos simulando sus resultados muchas veces utilizando valores aleatorios. Utiliza varios pasos:
- Incertidumbre del modelo: Define cómo puede variar cada variable utilizando distribuciones de probabilidad.
- Muestreo aleatorio: Selecciona aleatoriamente valores para estas variables basándote en sus distribuciones.
- Simula los resultados: Utiliza estos valores para simular el comportamiento del sistema.
- Analiza los resultados: Repite el proceso muchas veces para obtener un abanico de posibles resultados, y luego analízalos para predecir los escenarios más probables.
A continuación, ampliaremos nuestros conocimientos básicos sobre la simulación de Montecarlo profundizando en algunos conceptos estadísticos relevantes.
Comprender las variables aleatorias y las distribuciones de probabilidad
Las variables aleatorias y sus distribuciones de probabilidad asociadas son fundamentales para la simulación de Montecarlo, ya que proporcionan el marco matemático para modelar y simular la aleatoriedad y la variabilidad inherentes a los sistemas complejos.
Variables aleatorias
Una variable aleatoria es una variable cuyos valores son resultados de un fenómeno aleatorio.
Las variables aleatorias se clasifican en dos tipos:
- Variables aleatorias discretas: Estas variables toman un número contable de valores distintos. En las simulaciones, las variables discretas pueden modelar escenarios como el número de artículos defectuosos en un lote, llegadas de clientes por hora u otros sucesos contables.
- Variables aleatorias continuas: Estas variables pueden tomar cualquier valor en un intervalo continuo. Las variables continuas se utilizan para simulaciones que tratan de medidas físicas o duraciones de tiempo.
Las variables aleatorias se utilizan en las simulaciones porque contienen la incertidumbre que las técnicas de Montecarlo están diseñadas para explorar y cuantificar.
Distribuciones de probabilidad
Las distribuciones de probabilidad describen cómo se distribuyen las probabilidades sobre los valores de una variable aleatoria.
Las distribuciones de probabilidad se utilizan en la simulación de Montecarlo para definir cómo se espera que se comporten las distintas entradas o escenarios, lo que es esencial para un modelado y una toma de decisiones precisos.
La distribución normal es la más utilizada en estadística y simulaciones porque muchos fenómenos naturales y de origen humano tienden a seguir esta distribución debido al Teorema Central del Límite.
Distribución normal(Fuente)
La distribución normal se utiliza para modelizar variables influidas por muchos efectos pequeños e independientes, como los errores de medición o los rendimientos de la bolsa.
Otras distribuciones de probabilidad son las distribuciones uniformes, que se utilizan cuando cualquier resultado dentro de un intervalo especificado es igual de probable -una suposición habitual en las simulaciones cuando no se dispone de datos previos-, y las distribuciones binomiales, que se utilizan cuando se modelan escenarios con dos resultados posibles (éxito/fracaso) en una serie de experimentos, como pruebas de aprobado/no aprobado o comprobaciones de control de calidad.
Ahora que hemos comprendido los conceptos y la teoría que hay detrás de las simulaciones de Montecarlo, pasemos a la parte de la implementación.
¿Por qué utilizar Excel para la simulación de Montecarlo?
Una vez que hayas decidido aplicar una simulación de Montecarlo, dispones de múltiples herramientas, como Excel, Python, R, SAS y MATLAB, para ayudarte con las simulaciones.
El factor más importante que debes tener en cuenta, sobre todo al aplicar la simulación Monte Carlo por primera vez, es tu familiaridad general con la herramienta. Excel es una de las herramientas más utilizadas en el mundo empresarial, lo que significa que mucha gente ya está familiarizada con sus operaciones básicas. Esto reduce el tiempo de formación y elimina la necesidad de aprender un nuevo software desde cero.
Excel también proporciona herramientas fáciles de usar para crear tablas y gráficos, que pueden ser útiles para visualizar los resultados de las simulaciones. Además, existen varios complementos potentes para Excel, que mejoran su capacidad para realizar complejas simulaciones Monte Carlo.
Sin embargo, también merece la pena señalar que para simulaciones más avanzadas, especialmente las que requieren manejar grandes conjuntos de datos o ejecutar un número muy elevado de simulaciones, podrían ser más adecuadas herramientas más especializadas distintas de Excel.
Visión general de las funciones relevantes de Excel
A continuación, exploraremos dos funciones esenciales de Excel: RAND y NORM.INV, cubriendo su sintaxis, parámetros y casos de uso típicos. Estas funciones ayudan a generar números aleatorios y a definir distribuciones de probabilidad, que son aspectos fundamentales de cualquier simulación.
La sintaxis y los parámetros de la función RAND
RAND genera un número aleatorio mayor o igual que 0 y menor que 1. Los números están distribuidos uniformemente, lo que significa que cualquier número dentro del intervalo especificado tiene la misma probabilidad de ocurrir.
La sintaxis de RAND es la siguiente:
RAND()
La función RAND
no necesita argumentos. Se utiliza simplemente como RAND()
.
En el contexto de la Simulación de Montecarlo, RAND()
puede utilizarse para simular la aparición de sucesos aleatorios o para variar las entradas de tu modelo.
La sintaxis y los parámetros de la función NORM.INV
Mientras que RAND genera números aleatorios uniformes, NORM.INV se utiliza para generar números aleatorios a partir de una distribución normal, que es un requisito habitual en una Simulación de Montecarlo. Esta función devuelve la inversa de la distribución acumulativa normal para una media y una desviación típica especificadas.
La sintaxis de la función NORM.INV es la siguiente:
NORM.INV(probability, mean, standard_deviation)
Los parámetros son:
- probabilidad: Probabilidad correspondiente a la distribución normal, que debe ser un valor comprendido entre 0 y 1. Suele generarlo la función
RAND()
. - media: La media aritmética de la distribución normal.
- standard_deviation: La desviación típica de la distribución normal, una medida de la dispersión de los números en torno a la media.
La NORM.INV
se utiliza para transformar los números aleatorios distribuidos uniformemente de la función RAND
en números que siguen una distribución normal especificada. Esto resulta útil para modelizar variables que se espera que presenten una variabilidad natural siguiendo una curva normal.
Ahora que ya tenemos todos los elementos básicos, funciones y conceptos que hay detrás de una simulación de Montecarlo, vamos a implementar una en Microsoft Excel.
Aplicación de la simulación Monte Carlo en Microsoft Excel: Un ejemplo
Imagina que eres un analista de datos que trabaja en una empresa dinámica de electrónica de consumo y te han encargado que evalúes la viabilidad financiera del lanzamiento de un nuevo dispositivo portátil de seguimiento del estado físico.
El mercado de estos aparatos es competitivo y la demanda de los consumidores puede ser muy variable, influida por las tendencias estacionales, la eficacia del marketing y las acciones de la competencia. Además, los costes asociados a la fabricación de estos dispositivos están sujetos a fluctuaciones debidas a cambios en los costes de los materiales e incertidumbres en la cadena de suministro.
Has decidido utilizar la simulación Monte Carlo en Excel para afrontar estos retos. Crees que este enfoque te ayudará a estimar la rentabilidad potencial en distintos escenarios, lo que permitirá a la empresa tomar decisiones bien informadas sobre estrategias de precios, volúmenes de producción e inversiones en marketing.
También has analizado datos anteriores de lanzamientos de productos similares y estudios de mercado dentro del sector de la electrónica de consumo. A partir de este análisis, has concluido ciertas métricas que informarán tu simulación:
- Una demanda media de 10.000 unidades de nuevos aparatos durante el primer año de lanzamiento, con una desviación típica de 2.000 unidades, que refleja la incertidumbre en la aceptación por parte de los consumidores.
- El precio de venta por unidad suele oscilar entre 50 y 70 dólares, dependiendo de los precios de la competencia y de la saturación del mercado.
- El coste unitario, influido por la volatilidad de los precios de los materiales y la eficacia de la fabricación, tiene una media de 30 $ por unidad, con una desviación típica de 5 $.
Estos datos históricos forman los supuestos subyacentes de tus parámetros de simulación, ayudando a crear la simulación para reflejar con mayor precisión las condiciones actuales del mercado.
Los pasos que podrías seguir para aplicar la simulación de Montecarlo a este ejemplo concreto son los siguientes:
Paso 1: Configura tu hoja Excel
Primero, prepara tu hoja de cálculo Excel para incluir columnas para cada variable y una columna para el beneficio calculado.
Así es como quedaría inicialmente:
Configurar la hoja Excel.
Paso 2: Fórmulas de introducción de variables
En cada fila, introducirás fórmulas para generar valores aleatorios para la demanda, el precio de venta y el coste en función de las distribuciones que hayas identificado:
- Demanda: Distribución normal (media = 10.000 unidades, desviación típica = 2.000 unidades)
- Precio de venta: Distribución de uniformes (de 50 a 70 $)
- Coste: Distribución normal (media = 30 $, desviación típica = 5 $)
Para introducir estas fórmulas una a una, selecciona la celda A2 y escribe lo siguiente:
=NORM.INV(RAND(), 10000, 2000)
La ecuación anterior crea una distribución normal con una media y una desviación típica dadas, como se indica a continuación:
Crear la distribución de la demanda.
A continuación, selecciona la celda B2 y escribe lo siguiente:
=50 + (70-50) * RAND()
La ecuación anterior crea una distribución uniforme entre 50 y 70 $ para el precio de venta, como se indica a continuación:
Crear la distribución del precio de venta.
Selecciona la celda C2 y escribe lo siguiente:
=NORM.INV(RAND(), 30, 5)
La ecuación anterior, similar a la ecuación de la demanda, crea una distribución normal con una media y una desviación típica dadas, como se indica a continuación:
Crear la distribución por coste.
Paso 3: Calcula la variable dependiente
Ahora, calcula el beneficio, que es la variable dependiente, de cada simulación utilizando la fórmula de la columna D:
=(B2 - C2) * A2
Calcula el beneficio.
Paso 4: Rellena hacia abajo para simular múltiples escenarios
Lo que hemos hecho hasta ahora es crear una única simulación. Ampliémoslo a múltiples, digamos mil simulaciones.
Selecciona las celdas A2 a D2 y arrastra el tirador de relleno (un pequeño cuadrado en la parte inferior derecha de la selección) hacia abajo para rellenar las fórmulas a través de tantas filas como quieras simular (por ejemplo, 1000 filas para 1000 simulaciones).
Tendrá un aspecto parecido a éste:
Crear las simulaciones.
Paso 5: Analiza los resultados
Tras ejecutar las simulaciones, puedes analizar los resultados utilizando funciones estadísticas como mín., máx., media y desviaciones típicas. No dudes en consultar rápidamente la hoja de trucos de Excel para refrescar las funciones integradas de Excel que utilizaremos a continuación.
Para hallar el beneficio medio esperado cada mes, escribe lo siguiente en una celda, digamos G6:
=AVERAGE(D2:D1001)
Para hallar el beneficio mínimo esperado cada mes, escribe lo siguiente en una celda, digamos G7:
=MIN(D2:D1001)
Para hallar el beneficio máximo esperado cada mes, escribe lo siguiente en una celda, digamos G8:
=MAX(D2:D1001)
Para hallar la desviación típica del beneficio, escribe lo siguiente en una celda, digamos G9:
=STDEV.P(D2:D1001)
Una vez ejecutada, la hoja de Excel debería tener este aspecto:
Analizar los resultados de la simulación.
Podemos interpretar los resultados estimados y las implicaciones para el lanzamiento del producto como se indica a continuación:
- La cifra del beneficio medio representa el beneficio esperado del lanzamiento del nuevo rastreador de fitness. Sugiere que, por término medio, cada simulación predice que podríamos obtener unos beneficios de unos 298.278,67 $. Este valor es útil como estimación central de la rentabilidad según los supuestos dados.
- Un beneficio mínimo de 67.598,78 $ es el beneficio más bajo observado en todas nuestras simulaciones. Indica el peor escenario posible según los supuestos de tu modelo, que sigue siendo rentable pero bastante menos que la media. Esto podría deberse a una demanda especialmente baja o a unas condiciones de costes desfavorables en esa simulación concreta.
- Un beneficio máximo de 641.955,42 $ representa el mejor de los casos, en el que la demanda y el precio fueron probablemente máximos y los costes mínimos en todas las simulaciones. Esto muestra el potencial alcista si las condiciones resultan muy favorables.
Dada la amplia horquilla entre los beneficios mínimos y máximos y la considerable desviación típica, el lanzamiento del nuevo producto conlleva un riesgo financiero considerable.
Los responsables de la toma de decisiones deben considerar si la empresa se siente cómoda con este nivel de incertidumbre y la posibilidad de obtener beneficios inferiores a la media.
Además, aunque es opcional, te animamos a crear visualizaciones como Histogramas para tener una comprensión visual de los resultados de las simulaciones.
Buenas prácticas y técnicas avanzadas para mejorar las simulaciones
Cuando vuelvas a ejecutar la misma simulación anterior, podrás observar una ligera diferencia en los cálculos, como se muestra a continuación:
Variación de los resultados de la simulación.
Esto se debe a que los valores de la simulación original pueden cambiar entre iteraciones, influyendo en las estimaciones resultantes. Aunque la variación es pequeña, cuando cambia el valor estimado, surge en la mente de los responsables de la toma de decisiones una preocupación sobre la precisión y fiabilidad de la simulación.
Exploremos algunas técnicas avanzadas que podríamos utilizar para mejorar la precisión y fiabilidad de las simulaciones.
Aumentar el número de simulaciones
Ejecutar un mayor número de simulaciones ayuda a promediar las fluctuaciones aleatorias y proporciona una estimación más estable y precisa de los resultados.
En el ejemplo anterior, podemos aumentar el número de ejecuciones de simulación (por ejemplo, de 1.000 a 10.000 o más), sobre todo cuando se trata de parámetros muy variables.
Determinar el número "correcto" de simulaciones depende de varios factores.
Cuanto más complejo sea el modelo (es decir, cuantas más variables y más amplia sea la gama de sus interacciones), más simulaciones se necesitarán normalmente para capturar todos los resultados posibles y garantizar que los resultados no se deben al azar.
Si las entradas tienen una gran variabilidad o están muy sesgadas, serán necesarias más simulaciones para estimar con precisión las colas (valores extremos) de las distribuciones de resultados.
Para análisis más detallados, sobre todo en finanzas o gestión de riesgos, no es raro realizar entre 10.000 y 100.000 simulaciones. Este intervalo se suele utilizar para garantizar unos resultados sólidos en varios escenarios e insumos. Por supuesto, como hemos mencionado anteriormente, para este tipo de análisis a gran escala, Excel no siempre es la mejor elección de herramienta, sino más bien R o Python.
Afinar las distribuciones de entrada
La precisión de las simulaciones depende en gran medida de lo bien que las distribuciones de probabilidad de entrada reflejen la verdadera incertidumbre y el comportamiento de las variables subyacentes. En nuestro ejemplo anterior, hemos supuesto una distribución normal para la demanda y el coste y una distribución uniforme para el precio de venta.
Además, podríamos analizar datos históricos más completos para parametrizar mejor las distribuciones. Podemos comprender mejor los comportamientos del coste, la venta y la demanda en función de factores externos, basándonos en las aportaciones de los expertos en la materia. También podemos considerar el uso de distribuciones como log-normal, beta o gamma, o crear distribuciones personalizadas basadas en datos empíricos.
Realizar un análisis de sensibilidad
Este análisis se realiza para comprender qué variables de entrada tienen el impacto más significativo en la salida, variando sistemáticamente cada entrada mientras se mantienen constantes las demás.
En nuestro ejemplo anterior, podemos mantener constantes dos variables y cambiar la distribución de una para comprender los cambios en las estimaciones. A continuación, repite el mismo proceso con las dos variables restantes, una a una. Finalmente, esta técnica ayuda a comprender en qué variable hay que centrar los esfuerzos para mejorar la precisión.
Emplear las técnicas anteriores de forma iterativa y analizar los resultados puede conducir a resultados más precisos y fiables.
Conclusión
Este tutorial te ha introducido en la Simulación de Montecarlo y en los conceptos estadísticos relevantes. Tras introducir las funciones de Excel pertinentes, el tutorial proporcionaba una guía paso a paso para aplicar la Simulación de Montecarlo en Excel utilizando un ejemplo del mundo real.
Por último, has aprendido algunas buenas prácticas y técnicas avanzadas para garantizar que tus resultados sean más precisos y fiables.
Si estás especialmente interesado en implementar la Simulación de Montecarlo anterior utilizando otras herramientas como Python o R, estos dos recursos te serán de utilidad:
Alternativamente, si prefieres ceñirte al conocido Microsoft Excel, y te gustaría dominar tus habilidades utilizando la herramienta ampliamente adoptada, te convendría consultar el tema Fundamentos de Excel.
¡Continúa hoy tu viaje a Excel!
curso
Financial Modeling in Excel
curso
Case Study: Net Revenue Management in Excel
tutorial
Regresión lineal en Excel: Guía completa para principiantes
tutorial
Formato condicional en Excel: Guía para principiantes
tutorial
Guía completa para utilizar ANOVA en Excel
tutorial