INDICE
5.3 EJEMPLOS DE SIMULACIÓN EN HOJA ELECTRONICA------------ 2-9
5.3.1 PROGRAMACIÓN: DISTRIBUCIÓN DEL MODELO EN LA HOJA DE
CÁLCULO ---------------------------------------------------------------------------10-11
5.3.2 EXPERIMENTACIÓN CON VARIAS CONFIGURACIONES
POSIBLES DEL SISTEMA SIMULADO -----------------------------------12-
14
BIBLIOGRAFIA--------------------------------------------------------------------14
1
5.3 EJEMPLOS DE SIMULACIÓN EN HOJA ELECTRONICA
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 dialogo
2
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.
3
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.
4
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 cuartiles).
5
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
6
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 n de saber cómo cada variable aleatoria y cada variable de
escenario afecta a la variable de resultado. Para una variable aleatoria, los valores
7
explorados pueden estar en torno a la mediana, o en torno al valor de celda por
defecto, con
8
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.
9
5.3.1 PROGRAMACIÓN: DISTRIBUCIÓN DEL MODELO EN LA HOJA DE
CÁLCULO
Solución de problemas de programación lineal (PL) con una hoja de cálculo En
este punto se demuestra con detalle la mecánica del uso del Solver en Excel
mediante la solución del siguiente problema.
Ejemplo.
En un inicio solo se presenta su enunciado y planteamiento.
La compañía de luz tiene tres centrales que cubren las necesidades de cuatro
ciudades. Cada central suministra las cantidades siguientes de kilowatts-hora:
planta 1, 35 millones; planta 2, 50 millones; planta 3, 40 millones. Las demandas de
potencia pico en estas ciudades que ocurren a la misma hora (2:00 p.m.) son como
sigue (en kw/h): ciudad 1, 45 millones; ciudad 2, 20 millones; ciudad 3, 30 millones
y ciudad 4, 30 millones. Los costos por enviar un millón de kw/h de la planta
dependen de la distancia que debe viajar la electricidad y se muestran en la tabla
A.1.
Este problema se resuelve a través del Solver de Excel, colocando celdas para las
variables de decisión, como se observa en la siguiente figura.
10
11
5.3
.2 EXPERIMENTACIÓN CON VARIAS CONFIGURACIONES
POSIBLES DEL SISTEMA SIMULADO
Supongamos que trabajamos en un gran almacén informático, y que nos piden
consejo para decidir sobre el número de licencias de un determinado sistema
operativo que conviene adquirir – las licencias se suministrarán con los
ordenadores que se vendan durante el próximo trimestre, y es lógico pensar que en
pocos meses habrá un nuevo sistema operativo en el mercado de características
superiores. Cada licencia de sistema operativo le cuesta al almacén un total de 75
Euros, mientras que el precio al que la vende es de 100 Euros. Cuando salga al
mercado la nueva versión del sistema operativo, el almacén podrá devolver al
distribuidor las licencias sobrantes, obteniendo a cambio un total del 25
Euros por cada una. Basándose en los datos históricos de los últimos meses, los
responsables del
almacén han sido capaces de determinar la siguiente distribución de
probabilidades por lo que a las ventas de licencias del nuevo sistema operativo
se refiere:
12
Construimos nuestro modelo usando las fórmulas que se muestran en la figura
inferior. En la casilla H2 usaremos la función ALEATORIO para generar el valor
pseudo-aleatorio que determinará el suceso resultante; en la celda I2 usamos la
función BUSCARV para determinar el suceso correspondiente asociado al valor
pseudo-aleatorio obtenido –notar que usamos también la función MIN, ya que en
ningún caso podremos vender más licencias que las disponibles. El resto de
fórmulas son bastante claras:
13
FUENTES CONSULTADAS
Fishman, George S., Monte Carlo: Concepts, Algorithms, and Applications,
García Dunna, Eduardo; García Reyes, Heriberto. Simulación y Análisis de Sistemas con PROMODEL.
Pearson
Banks J., Carson J., Nelson, B., Nicol, D., Discrete-Event System Simulation, 5th ed., Prentice Hall
(2009)
[Link]
14
15