UNIVERSIDAD CARLOS III DE MADRID

Ingeniera Informtica
Investigacin Operativa
Prctica 6: Simulacin
Guin prctico: Generacin de Nmeros Aleatorios y Simulacin
Monte Carlo
Curso 08/09

Objetivo: Aprender a utilizar herramientas de simulacion en Excel

para resolver problemas con incertidumbre.
La mayoria de las decisiones que tomamos en nuestra vida involucran incertidumbre. Tomar en cuenta la probabilidad de los posibles escenarios es
fundamental si deseamos maximizar un benecio o minimizar una prdida en
cualquiera de nuestras actividades cotidianas. A gran escala, muchas compaias
utilizan simulacin Monte Carlo para mejorar sus ganacias u optimizar sus procesos. Por ejemplo, General Motors, Procter and Gamble y Elly Lilly usan simulacin para estimar el retorno promedio y el riesgo de lanzar un nuevo producto
al mercado, as como para determinar la capacidad ptima de las plantas que utilizan para fabricar sus diferentes productos. Excel es una poderosa herramienta
de simulacin y anlisis que permite resolver una gran cantidad de problemas,
incluyendo aquellos que involucran incertidumbre. En esta prctica aprenderemos a utilizar algunas de las funciones de Excel para el tratamiento de este tipo
de problemas. Para realizar la prctica haremos uso del chero practica6.xls
que se puede descargar de la pgina web de la asignatura o desde aula global.

Parte 1: Cmo generar nmeros aleatorios en excel?

En esta seccin de la prctica vamos a aprender a generar nmeros aleatorios
que se distribuyen uniformemente entre 0 y 1, es decir, la probabilidad de obtener cualquier nmero entre 0 y 1 ser la misma en cada momento Excel genera
esta tipo de nmeros a travs del comando ALEATORIO(). Para aprender
a utilizar esta funcin utilizaremos la hoja de trabajo "simula1". El objetivo es generar 100 nmeros aleatorios y determinar la fraccin de esos nmeros
que estan entre 0 y 0.25, 0.25 y 50, 0.50 y 0.75 y, nalmente, entre 0.75 y 1.0.
1

Figura 1

Para ello se deber generar un escenario similar al de la gura 1. En esta hoja

rellenamos el conjunto de celdas C3:C102 con la formula ALEATORIO().
En la columna F aparecen las etiquetas correspondientes a los intervalos de inters, del lado derecho, para la celda F5, por ejemplo, emplearemos la funcin
CONTAR.SI(C3:C102;"<=0.25), lo que nos proporciona la cantidad de elementos entre 0 y 0.25; repetimos esta misma operacin para las celdas F6 a
F8 teniendo en cuenta en descontar los elementos ya contabilizados en el primer
intervalo.

Parte 2: Cmo simular valores de una variable aleatoria discreta?

Supongamos que la demanda de cierto tipo de producto esta dada por una
variable aleatoria discreta de acuerdo a la siguiente tabla:

Tabla1

Demanda
10000
20000
40000
60000

Probabilidad
0,10
0,35
0,30
0,25

La cuestin es como simular en excel la demanda de este producto las veces

que sea necesario para corroborar que sigue dicha dsitribucin de probabilidades.
En la primera parte de esta prctica aprendimos a generar nmeros aleatorios
con la propiedad de que su probabilidad se distribuye uniformemente entre el 0
y el 1, por lo tanto, si aprovechamos esta propiedad y asociamos cada posible
valor de probabilidad con una demanda, entonces la respuesta llega de manera
natural. As por ejemplo, en la siguiente tabla se especica que la demanda de
10000 unidades de producto ocurrira el 10 % de las veces y as para cada valor
de demanda indicado en la tabla 1.

Tabla2

Demanda
10,000
20,000
40,000
60,000

Nmero aleatorio
x<0.1
0.1 x < 0;45
0;45 x < 0;75
0;75 x

Para dar comienzo a esta simulacin es necesario abrir la hoja "simula2",

en este ejercicio se deber generar un escenario parecido al que se muestra en la
gura 2. Tal y como en simula1, generamos 100 numeros aleatorios en el conjunto
de celdas C3:C102, posteriormente, al lado izquierdo de esta columna, generaremos otra columna con la demanda correspondiente acorde al valor del nmero
aleatorio generado. As por ejemplo, nmeros aleatorios mayores o iguales que
0 y menores que 0.1, tendrn asociada una demanda de 10,000. Para asociar la
demanda correspondiente a cada nmero utilizaremos la funcin de excel BUSCARV(), por lo tanto generaremos 100 ensayos o repeticiones de la demanda
del producto copiando de la celda B3 al rango de celdas B4:B102 la frmula
BUSCARV(C3;lookup;2). Finalmente, en el rango de celdas G9:G11 utilizaremos de nuevo la funcin CONTAR.SI() para determinar la cantidad de
elementos (nmeros aleatorios) que caen en la categora de inters y a los que
se asignar el valor discreto de la variable demanda.

Parte 3: Cmo simular valores de una variable aleatoria que

se distribuye como una normal?
La distribucin normal es una de las ms importantes distribuciones de probabilidad por sus aplicaciones prcticas. Excel permite simular valores de una
distribucin normal con media y desviacin estandar ; a travs de la funcin
DISTR.NORM.INV(ALEATORIO();mean;sigma). En este ejercicio se
har uso de la hoja "simula3". Supongamos que deseamos simular 200 ensayos
de una variable aleatoria normal con media 40,000 y desviacin estandar de
10,000, para ello generamos 200 nmeros aleatorios, por ejemplo en el rango de
celdas C3:C200. Recuerda que estos numeros siguen una istribucin uniforme
en el intervalo [0,1] y que son generados a travs de la funcin ALEATORIO(). Ahora copiamos de la celda B3 al rango de celdas B4:B200 la frmula

Figura2

Figura3

DISTR.NORM.INV(C3;mean;sigma), con este procedimiento generamos

una columna de valores (200 ensayos) de una variale aleatoria normal con las
caractersticas deseadas. La funcin de excel genera un nmero aleatorio con
distribcin normal, tal que la probabilidad de obtener dicho valor corresponda
al valor de nuestro nmero aleatorio uniforme, en otras palabras, si tenemos un
nmero aleatorio uniforme, digamos 0.8466, este nmero representa el percentil
85 de una variable aleatoria normal con media 40,000 y desviacin estandar
10,000. El escenario que debe obtenerse es similar al de la gura 3.

Parte 4: Optimizacin bajo incertidumbre

Supon que eres el encargado de produccin en una compaia que se dedica
a producir tarjetas para el dia de San Valentn. Debido a la crisis el dueo de
la empresa desea producir las tarjetas tomando en cuenta la incertidumbre en
el mercado de ventas, de tal forma que sus ganancias no se vean afectadas por
este factor. Para ello te proporciona la siguiente tabla (tabla 3) con informacin
de la demanda del producto.

Tabla 3

Demanda
10000
20000
40000
60000

Probabilidad
0,10
0,35
0,30
0,25

Con dicha informacin el dueo te encarga la tarea de averiguar la cantidad

de tarjetas que deben ser producidas para el prximo San valentn. En otras
palabras, Cuntas tarjetas hay que producir para que la ganancia esperada sea
mxima?.
Para resolver el problema lo primero que se debe tener en cuenta es que,
de acuerdo a la tabla 3, la demanda se comporta como una variable aleatoria
discreta, el resto ser utilizar adecuadamente Excel y utilizar lo aprendido hasta
ahora para encontrar la respuesta. Como datos adicionales se sabe que el precio
de venta de cada tarjeta es de 4 euros y el costo por producir cada una de
ellas es de 1.50 euros. Adems, se sabe que el costo por cada tarjeta que no se
venda es de 0.20 euros, a este costo le llamaremos costo por inventario. La idea
bsica para resolver este problema es simular el escenario para cada valor de
demanda (10,000, 20,000, 40,000 y 60,000) un determinado nmero de veces. De
esta manera podremos determinar el escenario de produccin que nos permita
obtener en el mximo valor promedio de ganancia. Este ejercicio de simulacin
se llevar a cabo en la hoja "simula 4".
Vamos a generar una hoja de clculo similar a la de la gura 4, para ello
colocamos las etiquetas correspondientes a las variables del problema en el rango de celdas B1:B11 y los valores correspondientes en el rango C1:C11. En
el rango de celdas G3:G6 aparece el lmite inferior del valor de probabilidad
para cada valor de la demanda. El precio de venta y los parmetros de costo
pueden ser introducidos en el rango de celdas C4:C6. Ahora introducimos un
valor de produccin de 40,000 en la celda C1 como primer ensayo. Posteriormente en la celda C2 generamos un nmero aleatorio uniforme con ayuda de la
funcin ALEATORIO() y simulamos, en la celda C3, el valor de la demanda
correspondiente a travs de la frmula BUSCARV(C2;lookup;2).
Note que el nmero de unidades vendidas es la mnima cantidad entre la
cantidad de tarjetas producidas y la demanda. As, los ingresos por venta estn
calculads en la celda C8 con la frmula MIN(produccin, demanda), donde
produccin y demanda son los nombres asignados a las celdas C1 y C3, respectivamente. Por otro lado, si se producen ms tarjetas de las que se demandan, el
nmero de unidades sobrantes ( inventario) ser la diferencia entre produccin
menos demanda.
La expresin unidad_disp_cost*SI(produccion>demanda;produccindemanda;0) se utilizar para calcular el costo total por tarjetas sobrantes,
dicha expresin se introduce en la celda C10. Finalmente, en la celda C11,

Figura 4

el benecio total o ganancia es calculado con la frmula ingresos_totalescosto_total-costo_inventario.

Hasta ahora, con lo que hemos aprendido, necesitariamos oprimir F9 muchas

veces (digamos 100 para este ejercicio) para cada escenario de produccin y
calcular la ganancia esperada, sin embargo, utilizaremos una tabla de anlisis
como la mostrada en la gura 5 para ahorrarnos todo ese trabajo.

Para generar la tabla de anlisis, en el rango de celdas A16:A115 introducimos los nmeros de 1 a 100, correspondientes a nuestros 100 ensayos o
experimentos. Luego, en el rango de celdas B15:E15 introducimos los posibles
escenarios de produccin. Lo que deseamos es calcular la ganacia neta promedio
para cada ensayo y para cada escenario de produccin, para ello copiamos el
contenido de la celda C11 en la celda A15. A continuacin seleccionamos el
rango de celdas (tabla) A15:E114 y pinchamos el comando Datos para seleccionar tabla del men de opciones correspondiente. Aparecer una tabla de 2
7

Figura 5

Figura 6

entradas como en la gura 6, en ella seleccionamos cualquier celda en blanco

(digamos I14) como celda de entrada (columna) y la celda C1 como celda de
entrada (la). Una vez hecho esto, pinchamos OK y automticamente Excel
genera las 100 simulaciones para cada escenario de produccin.

Ahora introducimos en la celda B13 la frmula PROMEDIO(B16:B116)

y la copiamos sobre el rango de celdas C13:E13, con esta operacin se han calculado las ganancias promedio para cada escenario de produccin. Si repetimos
el mismo mecanismo, pero ahora utilizando la frmula DESVEST(), obtendremos la desviacin estandar correspondiente a la ganancia obtenida en la
simulacin. Si pinchamos F9, se calculan de manera automtica 100 iteraciones
(o las iteraciones que nosotros jemos) para cada nivel de produccin. Observa
que producir 40,000 tarjetas es la mejor decisin que podra tomarse, pues nos
reporta ganancia esperada, as que ya sabes que responder al jefe.
Con los valores de la media y de la desviacin estandar obtenidos en las simulaciones, es posible calcular intervalos de conanza para la ganancia promedio.
Por ejemplo, un intervalo de conanza al 95 % para la ganancia promedio, para
9

un determinado nmero de simulaciones, puede ser calculado con la siguiente

expresin:
p D:S
media= 1;96
NS

D.S = Desviacin estandar

NS = Nmero de simulaciones

Cuestionario
1. Supon que su jefe es algo despistado en probabilidades y desea saber
con exactitud la ganancia para la proxima temporada de San valentn.
Qu le responderan ustedes?. (Generar la respuesta en simula 4)
2. Un pequeo supermercado est tratando de determinar cuntas copias
de una revista de computacin deben ser pedidas al distribuidor cada semana. Se tiene conocimiento que la demanda de la revista esta
governada por la variable aleatoria discreta de acuerdo a la siguiente
tabla:
Demanda Probabilidad
15
0.1
20
0.20
25
0.30
30
0.25
35
0.15
Como informacin adicional se tiene que el supermercado paga 1
euro por cada copia de la revista y lo venden a un precio de 1.95
euros. Adems, ellos pueden retornar cada revista que nos se vende
a un precio de 0.50 euros. Cuntas copias de la revista deben ser
adquiridas para maximizar la ganancia promedio?.
Nota: Esta prctica esta basada en el libro Microsoft Excel Data
Analysis and Busines Modeling by Wayne L. Winston.

10