Modelo de simulación simple en Excel

Este tutorial le mostrará cómo configurar y ejecutar un simple modelo simple de simulación en Excel utilizando el software estadístico XLSTAT.

¿Qué son los modelos de Simulación?

Los modelos de simulación permiten obtener información de variables (por ejemplo, la media, la mediana o los intervalos de confianza) que no tienen un valor exacto, pero de las que, o bien conocemos, o bien suponemos una distribución. Si algunas variables “resultado” dependen de estas variables “distribuidas” por medio de fórmulas conocidas o supuestas, entonces las variables “resultado” también tendrán una distribución. Los modelos de simulación permiten definir las distribuciones, y luego obtener, a través de simulaciones, una distribución empírica de las variables de entrada y de salida, así como los estadísticos correspondientes.
Los modelos de simulación se utilizan en muchas áreas como las finanzas y los seguros, la medicina, la prospección de petróleo y gas, la contabilidad, o la predicción de ventas.
Cuatro elementos están involucrados en la construcción de un modelo de simulación:
1. Las distribuciones se asocian a variables aleatorias. XLSTAT ofrece una selección de más de 30 distribuciones para describir la incertidumbre en los valores que una variable puede tomar. Por ejemplo, se puede elegir una distribución triangular si tiene una cantidad por la que usted sabe que puede variar entre dos límites, pero con un valor que es más probable (un modo). En cada iteración del cálculo del modelo de simulación, se realiza un sorteo para cada distribución que se ha definido.
2. Las variables Escenario permiten incluir en el modelo de simulación de una cantidad que se fija en el modelo, excepto durante el análisis Tornado, en los que puede variar entre dos límites.
3. Las variables de Resultado corresponden a las salidas del modelo. Dependen directa o indirectamente, a través de una o más fórmulas de Excel, de las variables aleatorias a las que se han asociado las distribuciones y, en su caso, de las variables de escenario. El objetivo de calcular el modelo de simulación es obtener la distribución de las variables de resultados.
4. Los Estadísticos permiten realizar un seguimiento de un estadístico dado para una variable de resultado. Por ejemplo, podríamos querer controlar la desviación típica de una variable de resultado.

Un modelo correcto debería comprender al menos una distribución y una variable de resultado. Los modelos pueden contener cualquier número de los cuatro elementos de la lista. Un modelo puede limitarse a una sola hoja de Excel, o puede utilizar una carpeta completa de Excel.

Datos para crear y ejecutar un modelo simple de simulación

Nuestro modelo de simulación se basa en las ventas y los costos de una tienda. El beneficio es simplemente la diferencia entre las ventas y los costos en este sencillo caso. Sobre la base de los datos históricos de costos y ventas que fueron analizadas con la función “ajuste de la distribución” nos dimos cuenta de que los costes siguen una distribución normal (mu=120, sigma=10) y las ventas una distribución normal (mu=80, sigma=20) (ver tutorial tutorial sobre ajuste de distribuciones para más detalles).
Sobre la base de este modelo, se crean las diferentes variables del modelo:

Este modelo se puede encontrar en la hoja “Model”.

Creación de un modelo simple de simulación

Creación de la primera variable de distribución

Para crear la primera variable de distribución, seleccione la celda B2, que corresponde a la cantidad de ventas.

Una vez activado XLSTAT, seleccione el comando XLSTAT / Simulaciones de Monte Carlo / Definir una distribución, o haga clic en el botón correspondiente de la barra de herramientas de Sim (véase siguiente captura de pantalla).

Aparece el cuadro de diálogo Definir una distribución. A continuación, seleccione el Nombre de la variable como la celda A2 con el nombre “Sales”. Elija una distribución normal con mu = 120 y sigma = 10.

Una vez haya hecho clic en OK, se inserta la llamada a la función correspondiente en la celda activa.

Creación de la segunda variable de distribución

Ahora, se puede generar de la misma manera la segunda variable de distribución. Seleccione en este caso una distribución normal con mu = 80 y sigma = 20. Aquí está el correspondiente cuadro de diálogo:

Creación de la variable de resultado

Seleccione la celda de resultados que contiene el valor 40 como resultado de la fórmula = B2-B3 como celda activa. A continuación, seleccione el comando XLSTAT / Simulaciones de Monte Carlo / Definir una variable resultado, o haga clic en el botón correspondiente de la barra de herramientas de Sim

Se muestra el cuadro de diálogo Definir una variable resultado. A continuación, seleccione la celda A4 como Nombre de la variable.

Una vez haya hecho clic en OK, la llamada a la función correspondiente a XLSTAT_SimRes se inserta en la celda activa.

Esto se puede encontrar en la hoja de Excel “Model”.

Ejecución de un modelo simple de simulación

Para iniciar la ejecución de la simulación, seleccione el menú comando XLSTAT / Simulaciones de Monte Carlo / Iniciar los cálculos, o haga clic en el botón correspondiente de la barra de herramientas Sim.

Se muestra el cuadro de diálogo de ejecución de la simulación. Puede fijar el número de simulaciones a 1000.

En la pestaña Gráficos - Sensibilidad, introduzca los parámetros de los análisis Tornado y Araña.

Los cálculos empiezan una vez haya hecho clic en OK.

Interpretación de los resultados de un modelo simple de simulación

El primer resultado es un resumen del modelo de simulación.

A continuación, se muestran detalles sobre las dos variables de distribución y sobre la variable de resultado.

Las siguientes tablas muestran los detalles de las dos variables de distribución (estadísticos descriptivos, histogramas y cuantiles).

Las siguientes tablas muestran los detalles de la variable de resultado. Se muestran los estadísticos descriptivos, un histograma y estadísticos acerca de los intervalos. A continuación se muestran los resultados del análisis de sensibilidad. El análisis de sensibilidad se basa en las simulaciones contrarias al análisis Tornado que se presenta a continuación.

La siguiente sección contiene el análisis Tornado.

El análisis Tornado no se basa en las iteraciones de la simulación, sino en un análisis punto por punto de todas las variables de entrada (variables aleatorias con distribuciones y variables de escenario).

Durante el análisis Tornado, para cada variable de resultado, se estudia una por una cada variable aleatoria de entrada y cada variable de escenario. Hacemos que su valor varíe entre dos límites, y registramos el valor de la variable de resultado, con el fin de saber cómo cada variable aleatoria y cada variable de escenario afecta a la variable de resultado. Para una variable aleatoria, los valores explorados pueden estar en torno a la mediana, o en torno al valor de celda por defecto, con límites definidos por los percentiles o por la desviación. Para una variable de escenario, el análisis se lleva a cabo entre dos límites especificados cuando se definen las variables. El número de puntos es una opción que puede ser modificada por el usuario antes de ejecutar el modelo de simulación.

En el diagrama se ve que los costos tienen el mayor impacto en el beneficio. Estos resultados no se basan en las iteraciones de la simulación.

Finalmente, se muestran la matriz de correlaciones de las distribuciones y las variables de resultado. Vemos que los costos y las ventas no están correlacionados. Pero el beneficio está, obviamente, correlacionado con las ventas y los costos.