Saltar al contenido principal

Simulación Monte Carlo en Excel: Guía completa

Un tutorial completo y sencillo para principiantes sobre la realización de Simulaciones Monte Carlo en Microsoft Excel, junto con ejemplos, mejores prácticas y técnicas avanzadas.
Actualizado 30 jul 2024  · 9 min de lectura

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

Temas

¡Continúa hoy tu viaje a Excel!

curso

Data Analysis in Excel

3 hr
69.7K
Learn how to analyze data with PivotTables and intermediate logical functions before moving on to tools such as what-if analysis and forecasting.
Ver detallesRight Arrow
Comienza el curso
Ver másRight Arrow
Relacionado

tutorial

Regresión lineal en Excel: Guía completa para principiantes

Una guía paso a paso para realizar una regresión lineal en Excel, interpretar los resultados y visualizar los datos para obtener información práctica.
Natassha Selvaraj's photo

Natassha Selvaraj

11 min

tutorial

Formato condicional en Excel: Guía para principiantes

Explora el formato condicional en Excel con ejemplos de sencillos a avanzados y sus mejores prácticas.
Joleen Bothma's photo

Joleen Bothma

7 min

tutorial

Guía completa para utilizar ANOVA en Excel

Aprende el proceso simplificado de realizar ANOVA en Excel, e interpretar los resultados con instrucciones claras, paso a paso.
Arunn Thevapalan's photo

Arunn Thevapalan

10 min

tutorial

Cómo crear un cuadro de mando en Excel en 3 sencillos pasos

Aprende todo lo que necesitas saber sobre cómo crear un cuadro de mando en Excel, con consejos y ejemplos.
Joleen Bothma's photo

Joleen Bothma

12 min

Ver másVer más