XLSTAT: Modelo de simulación con variables de escenario en Excel

XLSTAT simula variables con distribuciones y escenarios, calcula resultados (p. ej., beneficio) y analiza sensibilidad con Tornado y Spider mediante Monte Carlo.

Jul 22, 2026
Knowledge

Title

XLSTAT: Modelo de simulación con variables de escenario en Excel

Answer

XLSTAT: Modelo de simulación con variables de escenario

Modelos de simulación

Los modelos de simulación nos permiten obtener información como la media, la mediana o los intervalos de confianza para variables que no tienen un valor exacto, pero para las que conocemos o suponemos una distribución. Si algunas variables de "resultado" dependen de estas variables "distribuidas" mediante fórmulas conocidas o supuestas, entonces las variables de "resultado" también tienen una distribución. Con Sim, puede definir las distribuciones y luego obtener una distribución empírica de las variables de entrada y salida y las estadísticas correspondientes mediante simulaciones.
Los modelos de simulación se utilizan en muchos campos, como las finanzas, los seguros, la medicina, la prospección de petróleo y gas, la contabilidad y la previsión de ventas.
Hay cuatro elementos a tener en cuenta al construir un modelo de simulación:

Las distribuciones se asignan a variables aleatorias. XLSTAT le ofrece la posibilidad de elegir entre más de 30 distribuciones para describir la incertidumbre de los valores que puede adoptar una variable. Por ejemplo, puede elegir una distribución triangular cuando tiene una variable que sabe que puede variar entre dos límites, pero con un valor que es más probable (una moda). En cada iteración del cálculo del modelo de simulación, se realiza un sorteo aleatorio para cada distribución definida.
Las variables de escenario le permiten incluir una cantidad que es fija en el modelo de simulación, excepto durante el análisis de tornados, donde puede variar entre dos límites.
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 asignado distribuciones y, si están disponibles, de las variables del escenario. El objetivo del cálculo del modelo de simulación es obtener la distribución de las variables de resultado.
Las estadísticas nos permiten realizar un seguimiento de una estadística particular para una variable de resultado. Por ejemplo, podríamos querer monitorear la desviación estándar de una variable de resultado.

Un modelo correcto debe contener al menos una variable de distribución y una variable de resultado. Los modelos pueden contener cualquier cantidad de estos cuatro elementos. Un modelo puede limitarse a una sola hoja de Excel o utilizar una carpeta completa de Excel.

Datos para el análisis

Nuestro modelo de simulación se basa en las ventas y los costos de una empresa. El beneficio en este caso simple es simplemente la diferencia entre ventas y costos. Con base en datos históricos de costos y ventas analizados con la herramienta de ajuste de distribución , descubrimos que los costos siguen una distribución normal (mu=120, sigma=10).

Las ventas también siguen una distribución normal (mu=80, sigma=20).
También asumimos que el 80% de los costos y el 30% de las ventas dependen de un tipo de cambio. El tipo de cambio se establece en 1 al principio. Durante el análisis de tornado, se simulan varios escenarios con un tipo de cambio de 0,8 a 1,2.
La fórmula del beneficio es más complicada porque ahora tenemos en cuenta el tipo de cambio: = (0,3 * E2 * B2 + 0,7 B2) -( 0,8 * E2 B3 + 0,2 * B3).

La variable de escenario y la estadística se añaden ahora al modelo creado en la hoja Modelo. Seleccione la celda E2, que contiene el valor del tipo de cambio, como celda activa. La variable de escenario se definirá allí.

Configuración

Abra XLSTAT.
Seleccione el comando XLSTAT > Simulaciones de Monte Carlo > Definir una variable de escenario . Aparecerá el cuadro de diálogo Definir una variable de escenario . Seleccione la celda de Excel que contiene “tipo de cambio” para definir el nombre de la variable.
Para el límite inferior , seleccione la celda con el valor 0,8 y para el límite superior , seleccione la celda con el valor 1,2. Esto definirá los límites entre los cuales variará el tipo de cambio.
Seleccione el tipo de datos continuos porque el tipo de cambio puede tomar cualquier valor entre 0,8 y 1,2.

Haga clic en Aceptar y la llamada a la función XLSTAT_SimSVar correspondiente se insertará en la celda activa.

Definición de una estadística

Seleccione la celda de resultado ubicada a la derecha de la celda con "beneficio de desviación estándar" para definir una estadística y realizar un seguimiento de la desviación estándar de la variable de resultado "beneficio" durante la ejecución de la simulación. Esta estadística se calcula en cada iteración de la simulación.
Seleccione el comando XLSTAT > Simulaciones de Monte Carlo > Definir una estadística .
Aparece el cuadro de diálogo Definir una estadística . Seleccione la celda de Excel con el nombre “desv. estándar beneficio” (D6). Marque la opción Estadísticas descriptivas y seleccione desviación estándar (n-1) en la lista.

Haga clic en Aceptar y la llamada de función correspondiente a XLSTAT_SimStat se insertará en la celda activa (E6).

Ejecución de una simulación

La simulación de Monte Carlo le permite ejecutar la simulación paso a paso si lo desea. Esto le permite verificar que su modelo se comporta como se espera. En este ejemplo, puede seguir la evolución de las estadísticas definidas paso a paso.
Para inicializar el modelo de simulación y borrar la información almacenada de una simulación paso a paso anterior, haga clic en el comando XLSTAT > Simulaciones de Monte Carlo > Reinicializar el modelo de simulación .
Verá los resultados de la primera iteración de simulación en las celdas de "Ventas", "Costos" y "Beneficio". La variable de escenario solo se utiliza durante el análisis de tornado y no cambia su valor por ahora. La estadística aún no se calcula, porque debe haber al menos dos valores históricos para calcular la desviación estándar.
Para realizar más pasos de simulación, seleccione el comando XLSTAT > Simulaciones de Monte Carlo > Realizar un paso de simulación .
Puede ver cómo la estadística de desviación estándar converge rápidamente.

¿Cómo iniciar la ejecución de la simulación?

Para iniciar la ejecución de la simulación, seleccione el comando XLSTAT > Simulaciones de Monte Carlo > Ejecutar o haga clic en el botón apropiado de la barra de herramientas Simulaciones de Monte Carlo .
Aparecerá el cuadro de diálogo Ejecutar . Establezca el número de simulaciones en 1000 .

En la pestaña Salidas, ingrese los parámetros del análisis de Tornado y Araña.

El cálculo comenzará tan pronto como haga clic en Aceptar.

Interpretación de los resultados

Los primeros resultados son los mismos que los descritos en el primer artículo.
El análisis Tornado y Spider no se basan 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).
En el análisis Tornado, para cada variable de resultado, cada variable aleatoria de entrada y cada variable de escenario se examinan una por una. Permitimos que su valor varíe entre dos límites y registramos el valor de la variable de resultado para aprender cómo cada variable aleatoria y de escenario afecta a las variables de resultado. Para una variable aleatoria, los valores examinados pueden estar alrededor de la mediana o alrededor del valor estándar de la celda, con límites definidos por percentiles o porcentajes de desviación. Para una variable de escenario, el análisis se realiza entre los dos límites especificados cuando se definieron las variables. El número de puntos es una opción que puede cambiar el usuario antes de ejecutar el modelo de simulación.
En el análisis Tornado, se muestran los resultados para la variable de escenario. Sus efectos son tan importantes como los de las otras dos variables "Costos" y "Ventas".

Se pueden ver más detalles en el diagrama de araña. El tipo de cambio está claramente correlacionado negativamente. Un tipo de cambio en alza reduce el beneficio.

Finalmente, se muestran los detalles de la simulación. Para cada una de las variables del modelo "Costos", "Ventas", "Beneficio" y "Beneficio desv estándar", se muestra la evolución en cada iteración. Vemos claramente la evolución de la estadística y podemos concluir que la estadística converge rápidamente a 22 y luego solo oscila alrededor de ese valor.