simulación monte carlo en excel

7/31/2019 Simulacin Monte Carlo en Excel

1/14

Simulacin de Monte Carlo con Excel

Proyecto e-Math 1Financiado por la Secretara de Estado de Educacin y Universidades (MECD)

SIMULACIN DE MONTE CARLO CON EXCEL

Autores: Javier Fauln ([email protected]), ngel A. Juan([email protected]).

ESQUEMA DE CONTENIDOS ________________________

INTRODUCCIN ___________________

La simulacin de Monte Carlo es una tcnica que combina conceptos estadsticos (muestreoaleatorio) con la capacidad que tienen los ordenadores para generar nmeros pseudo-aleatorios yautomatizar clculos.

Los orgenes de esta tcnica estn ligados al trabajo desarrollado por Stan Ulam y John VonNeumann a finales de los 40 en el laboratorio de Los Alamos, cuando investigaban el movimientoaleatorio de los neutrones [W1]. En aos posteriores, la simulacin de Monte Carlo se ha venidoaplicando a una infinidad de mbitos como alternativa a los modelos matemticos exactos o inclusocomo nico medio de estimar soluciones para problemas complejos. As, en la actualidad es posibleencontrar modelos que hacen uso de simulacin MC en las reas informtica, empresarial,econmica, industrial e incluso social [5, 8]. En otras palabras, la simulacin de Monte Carlo estpresente en todos aquellos mbitos en los que el comportamiento aleatorio o probabilsticodesempea un papel fundamental -precisamente, el nombre de Monte Carlo proviene de la famosaciudad de Mnaco, donde abundan los casinos de juego y donde el azar, la probabilidad y elcomportamiento aleatorio conforman todo un estilo de vida.

Son muchos los autores que han apostado por utilizar hojas de clculo para realizar simulacin MC[1, 6, 7]. La potencia de las hojas de clculo reside en su universalidad, en su facilidad de uso, en sucapacidad para recalcular valores y, sobre todo, en las posibilidades que ofrece con respecto alanlisis de escenarios (what-if anaylisis). Las ltimas versiones de Excel incorporan, adems, unlenguaje de programacin propio, el Visual Basic for Applications, con el cual es posible crearautnticas aplicaciones de simulacin destinadas al usuario final. En el mercado existen de hechovarios complementos de Excel (Add-Ins) especficamente diseados para realizar simulacin MC,siendo los ms conocidos: @Risk, Crystall Ball, Insight.xla, SimTools.xla, etc. [W2 W5].

SimulacinMonte Carlocon Excel

La funcinALEATORIO()

Qu es lasimulacin MC?

Simulacin MCcon v.a. discretas

Simulacin MCcon v.a. continuas

Generacin dev.a. con Excel


2/14



OBJETIVOS ________________________

Introducir los conceptos e ideas clave de la simulacin MC.

Introducirse en las capacidades que ofrece Excel en los campos de modelado y simulacin. Conocer algunas aplicaciones de la simulacin MC.

CONOCIMIENTOS PREVIOS ___________________________________

Este math-blocksupone ciertos conocimientos bsicos de estadstica (inferencia y probabilidad), ascomo conocimientos -a nivel de usuario- de la hoja de clculo Excel.

CONCEPTOS FUNDAMENTALES ______________________________

La funcin ALEATORIO() de Excel

Las hojas de clculo como Excel (y cualquier lenguaje de programacin estndar) son capacesde generar nmeros pseudo-aleatorios provenientes de una distribucin uniforme entre el 0 y el 1.Este tipo de nmeros pseudo-aleatorios son los elementos bsicos a partir de los cuales sedesarrolla cualquier simulacin por ordenador.

En Excel, es posible obtener un nmero pseudo-aleatorio -proveniente de una distribucinuniforme entre el 0 y el 1- usando la funcin ALEATORIO:

Los nmeros generados mediante la funcin ALEATORIO tienen dos propiedades que los hacenequiparables a nmeros completamente aleatorios:

1. Cada vez que se usa la funcin ALEATORIO, cualquier nmero real entre 0 y 1 tiene lamisma probabilidad de ser generado (de ah el nombre de distribucin uniforme).

2. Los diferentes nmeros generados son estadsticamente independientes unos de otros(es decir, el valor del nmero generado en un momento dado no depende de losgenerados con anterioridad).

La funcin ALEATORIO es una funcin voltil de Excel. Esto significa que cada vez quepulsamos la tecla F9 o cambiemos alguno de los inputs del modelo, todas las celdas dondeaparezca la funcin ALEATORIO sern recalculadas de forma automtica.

Se pueden encontrar ejemplos del uso de ALEATORIO en el propio men de ayuda de Excel.


3/14



Qu es la simulacin de Monte Carlo?

La simulacin de Monte Carlo es una tcnica cuantitativa que hace uso de la estadstica y losordenadores para imitar, mediante modelos matemticos, el comportamiento aleatorio de

sistemas reales no dinmicos (por lo general, cuando se trata de sistemas cuyo estado vacambiando con el paso del tiempo, se recurre bien a la simulacin de eventos discretos o bien ala simulacin de sistemas continuos).

La clave de la simulacin MC consiste en crear un modelo matemtico del sistema, proceso oactividad que se quiere analizar, identificando aquellas variables ( inputs del modelo) cuyocomportamiento aleatorio determina el comportamiento global del sistema. Una vez identificadosdichos inputs o variables aleatorias, se lleva a cabo un experimento consistente en (1) generarcon ayuda del ordenador- muestras aleatorias (valores concretos) para dichos inputs, y (2)analizar el comportamiento del sistema ante los valores generados. Tras repetir n veces esteexperimento, dispondremos de n observaciones sobre el comportamiento del sistema, lo cual nosser de utilidad para entender el funcionamiento del mismo obviamente, nuestro anlisis sertanto ms preciso cuanto mayor sea el nmero n de experimentos que llevemos a cabo.

Veamos un ejemplo sencillo:

En la imagen inferior se muestra un anlisis histrico de 200 das sobre el nmero de consultasdiarias realizadas a un sistema de informacin empresarial (EIS) residente en un servidor central.La tabla incluye el nmero de consultas diarias (0 a 5) junto con las frecuencias absolutas(nmero de das que se producen 0, 1, ..., 5 consultas), las frecuencias relativas (10/200 = 0,05,...), y las frecuencias relativas acumuladas.

Podemos interpretar la frecuencia relativa como la probabilidad de que ocurra el suceso asociado,en este caso, la probabilidad de un determinado nmero de consultas (as, p.e., la probabilidad deque se den 3 consultas en un da sera de 0,30), por lo que la tabla anterior nos proporciona ladistribucin de probabilidad asociada a una variable aleatoria discreta (la variable aleatoria es el

nmero de consultas al EIS, que slo puede tomar valores enteros entre 0 y 5).

Supongamos que queremos conocer el nmero esperado (o medio) de consultas por da. Larespuesta a esta pregunta es fcil si recurrimos a la teora de la probabilidad:

Denotando por X a la variable aleatoria que representa el nmero diario de consultas al EIS,sabemos que:

[ ] 95,215,05...10,0105,00)xX(PxXE5

0i

ii =+++====

Por otra parte, tambin podemos usar simulacin de Monte Carlo para estimar el nmeroesperado de consultas diarias (en este caso se ha podido obtener el valor exacto usando teorade probabilidad, pero ello no siempre ser factible). Veamos cmo:


4/14



Cuando se conozca la distribucin de probabilidad asociada a una variable aleatoria discreta,ser posible usar la columna de frecuencias relativas acumuladas para obtener los llamadosintervalos de nmeros aleatorios asociados a cada suceso. En este caso, los intervalosobtenidos son:

[0,00 , 0,05) para el suceso 0 [0,05 , 0,15) para el suceso 1 [0,15 , 0,35) para el suceso 2 [0,35 , 0,65) para el suceso 3 [0,65 , 0,85) para el suceso 4 [0,85 , 1,00) para el suceso 5

El grfico siguiente nos muestra cada una de las probabilidades sobre el nmero de consultas. Enl, se aprecia claramente la relacin existente entre probabilidad de cada suceso y el rea queste ocupa.

n Consultas EIS

0,20 0,30

0,05

0,10

0,20

0,15

0,00 0,20 0,40 0,60 0,80 1,00

Probabilidad

0

1

2

3

4

5

Esto significa que, al generar un nmero pseudo-aleatorio con el ordenador (proveniente de unadistribucin uniforme entre 0 y 1), estaremos llevando a cabo un experimento cuyo resultado,obtenido de forma aleatoria y segn la distribucin de probabilidad anterior, estar asociado a unsuceso. As por ejemplo, si el ordenador nos proporciona el nmero pseudo-aleatorio 0,2567,podremos suponer que ese da se han producido 2 consultas al EIS.

Asignamos pues la funcin ALEATORIO a una casilla (la G1 en el caso de la imagen):

Seleccionando la celda y arrastrando con el ratn desde el borde inferior derecho de la mismapodemos obtener un listado completo de nmeros pseudo-aleatorios:


5/14



A continuacin, podemos usar la funcin SI de Excel para asignar un suceso a cada uno de losnmeros pseudo-aleatorios generados (como veremos, otra forma de hacer esta asignacin serusando la funcin BUSCARV):

Repitiendo el proceso de seleccionar y arrastrar obtendremos algo similar a:

Finalmente, usando la funcin PROMEDIO ser posible calcular la media de los valores de lacolumna H:

En este caso, hemos obtenido un valor estimado que corresponde exactamente con el valor realanteriormente calculado va la definicin terica de la media. Sin embargo, debido a lacomponente aleatoria intrnseca al modelo, normalmente obtendremos valores cercanos al valorreal, siendo dichos valores diferentes unos de otros (cada simulacin proporcionar sus propiosresultados). Se puede comprobar este hecho pulsando repetidamente sobre la funcin F9 (cadavez que se pulsa dicha tecla, Excel genera nuevos valores aleatorios y, por tanto, nuevos valores

para la columna H y la casilla I1).

Si en lugar de usar una muestra aleatoria formada por 100 observaciones hubisemos usado unaformada por 10, los valores que obtendramos al pulsar repetidamente F9 no seran estimacionestan buenas al valor real. Por el contrario, es de esperar que si hubisemos usado 1.000 (o mejoran 10.000) observaciones, los valores que obtendramos en la casilla I1 estaran todos muycercanos al valor real.


6/14



CASOS PRCTICOS CON SOFTWARE___________________________________

Simulacin MC con variables discretas

Veamos un ejemplo algo ms complejo del uso de Excel para construir modelos de simulacin

MC cuando las variables aleatorias sean discretas:Supongamos que trabajamos en un gran almacn informtico, y que nos piden consejo paradecidir sobre el nmero de licencias de un determinado sistema operativo que conviene adquirir las licencias se suministrarn con los ordenadores que se vendan durante el prximo trimestre, yes lgico pensar que en pocos meses habr un nuevo sistema operativo en el mercado decaractersticas superiores. Cada licencia de sistema operativo le cuesta al almacn un total de 75Euros, mientras que el precio al que la vende es de 100 Euros. Cuando salga al mercado lanueva versin del sistema operativo, el almacn podr devolver al distribuidor las licenciassobrantes, obteniendo a cambio un total del 25 Euros por cada una. Basndose en los datoshistricos de los ltimos meses, los responsables del almacn han sido capaces de determinar lasiguiente distribucin de probabilidades por lo que a las ventas de licencias del nuevo sistemaoperativo se refiere:

Construimos nuestro modelo usando las frmulas que se muestran en la figura inferior. En lacasilla H2 usaremos la funcin ALEATORIO para generar el valor pseudo-aleatorio quedeterminar el suceso resultante; en la celda I2 usamos la funcin BUSCARV para determinar elsuceso correspondiente asociado al valor pseudo-aleatorio obtenido notar que usamos tambinla funcin MIN, ya que en ningn caso podremos vender ms licencias que las disponibles. Elresto de frmulas son bastante claras:

En la imagen anterior se muestra cmo construir el modelo con una observacin (iteracin). A finde generar nuevas observaciones, deberemos seleccionar el rango H2:N2 y "arrastrar" haciaabajo (tantas casillas como iteraciones deseemos realizar):


7/14



Finalmente, es posible estimar el valor esperado de la variable aleatoria que proporciona losbeneficios sin ms que hallar la media de las 100 observaciones que acabamos de realizar.Asimismo, usaremos las funciones DESVEST e INTERVALO.CONFIANZA para hallar,respectivamente, la desviacin estndar de la muestra obtenida y el intervalo de confianza (a unnivel del 95%) para el valor esperado:

La apariencia final de nuestro modelo ser:


8/14



A partir del modelo anterior es posible tambin realizar what-if anlisis (anlisis de escenarios opreguntas del tipo qu pasara si cambiamos tal o cual input?). Para ello es suficiente con ircambiando los valores de las celdas con fondo amarillo o rojo ( inputs del modelo en esteejemplo). Asimismo, podemos ampliar fcilmente el nmero de iteraciones (observacionesmuestrales) sin ms que repetir los procesos de seleccionar y arrastrar.

En el caso actual, hemos optado por tomar 1.000 iteraciones para cada una de los posibles inputsasociados a la cantidad de pedido (estos posibles inputs son: 100, 150, 200, 250, y 300). Si serealizase el experimento, se obtendran unos resultados similares a los que se muestran acontinuacin (ya que 1.000 es un nmero ya bastante considerable para este ejemplo):

Beneficio esperado

-3.000

-2.000

-1.000

0

1.000

2.000

3.000

4.000

100 150 200 250 300

N Licencias adquiridas

Euros

A partir de los resultados, parece claro que la decisin ptima es hacer un pedido de 150unidades, ya que con ello se consigue el beneficio mximo.


9/14



Generacin de nmeros aleatorios provenientes de otras distribuciones

Las ltimas versiones de Excel incorporan un Add-In llamado Anlisis de datos. Estecomplemento proporciona nuevas funcionalidades estadsticas a la hoja de clculo. Entre ellas,nos interesa destacar la de Generacin de nmeros aleatorios:

Con esta opcin, es posible generar fcilmente observaciones provenientes de diversasdistribuciones de variable discreta (Bernoulli, Binomial, Poisson, Frecuencia relativa, y Discreta) ode variable continua (Uniforme y Normal).

Independientemente del complemento Anlisis de datos, es posible usar un resultado muyconocido de la teora estadstica, llamado mtodo de la transformada inversa, para derivar lasfrmulas que permiten obtener valores pseudo-aleatorios provenientes de distribuciones como laWeibull o la Lognormal.

En la tabla siguiente se muestran algunas frmulas que, implementadas en celdas de Excel, nospermiten obtener valores pseudo-aleatorios de algunas de las distribuciones continuas msusadas:


10/14



Distribucin Parmetros Frmula ExcelExponencial Media = b = -LN(ALEATORIO())*bWeibull Escala = b

Forma = a= b*(-LN(ALEATORIO())^(1/a)

Normal Media = Desv. Estndar = = DISTR.NORM.INV(ALEATORIO(),,)Lognormal Media de Ln(X) =

Desv. Estndar de Ln(X) =

= DISTR.LOG.INV(ALEATORIO(),,)

Uniforme entre a y b Extremo inferior = aExtremo superior = b

= a+(b-a)*ALEATORIO()

Aadir, finalmente, que es relativamente sencillo implementar funciones VBA que, haciendo usodel mtodo de la transformada inversa o de otros mtodos similares, permitan la generacin devalores provenientes de casi cualquier distribucin terica.

Simulacin MC con variables continuas

Como hemos comentado, es posible usar las frmulas anteriores para generar, a partir de lafuncin ALEATORIO(), valores pseudo-aleatorios provenientes de otras distribuciones continuas.En las pginas siguientes, veremos dos ejemplos de modelos que hacen uso de la distribucinnormal (la distribucin estadstica ms importante y utilizada):

Ejemplo 1: Tiempo de consultas a servidores en paralelo

Supongamos que desde un ordenador cliente se realiza consultas SQL a bases de datos situadasen dos servidores distintos. Nuestro objetivo ser estimar el tiempo esperado (tiempo medio) quedeberemos esperar para recibir la respuesta de ambos servidores. Dada la complejidad de laconsulta que queremos realizar, y basndonos en experiencias anteriores, se calcula que eltiempo necesario para que cada uno de los servidores responda a la misma sigue unadistribucin normal con los parmetros (media y desviacin estndar, en minutos) que se indicana continuacin:

Pediremos a Excel que genere valores pseudo-aleatorios provenientes de dichas distribuciones.

Asimismo, usaremos la funcin MAX para obtener el tiempo de respuesta (que ser el mximo delos tiempos de respuesta de cada servidor), y la funcin SI para determinar qu servidor ha sidoel ms rpido en responder:

Usaremos tambin las funciones CONTAR y CONTAR.SI para contar el nmero de iteraciones yel nmero de veces que un servidor es ms rpido que el otro:


11/14



Finalmente, las funciones PROMEDIO, DESVEST, e INTERVALO.CONFIANZA nos servirn paraobtener, respectivamente, el tiempo muestral medio (esperado) de respuesta, la desviacinestndar de la muestra (observaciones que generaremos), y un intervalo de confianza, a un niveldel 95%, para el tiempo medio (este intervalo nos permitir saber si nuestra estimacin es buenao si, por el contrario, necesitaremos ms iteraciones).

Una vez introducidas las frmulas anteriores, bastar con seleccionar y arrastrar hacia abajo elrango de celdas G3:J3, con lo que se generarn nuevas iteraciones. En la imagen siguiente semuestra el resultado obtenido al generar 2.077 iteraciones. Observar que el tiempo medioestimado de respuesta es de 22,9 minutos, y podemos asegurar, con un nivel de confianza del95%, que dicho tiempo medio estar entre 22,8 y 23,0 minutos.

Finalmente, se observa tambin que el servidor 1 ha respuesto ms rpido que el servidor 2 en el67% de las iteraciones.

Ejemplo 2: Inversin inicial y flujo de caja

Consideremos ahora un nuevo problema: supongamos que disponemos de un capital inicial de

250 Euros que deseamos invertir en una pequea empresa. Supondremos tambin que los flujosde caja -tanto los de entrada como los de salida- son aleatorios, siguiendo stos una distribucinnormal.


12/14



Para el primer mes, el valor esperado del flujo de entrada es de 500 Euros, mientras que el valoresperado para el flujo de salida es de 400 Euros. En meses posteriores, el valor esperado ser elvalor obtenido para en el mes anterior. Por su parte, las desviaciones estndar valdrn, en todoslos casos, un 25% del valor medio (esperado) asociado. En base a lo anterior, podemos construirun modelo como se muestra en las siguientes imgenes:

Seleccionando y arrastrando hacia abajo el rango G3:O3, hemos obtenido los siguientes

resultados para 5.859 iteraciones:

Observamos que el valor esperado para el capital final es de unos 544 Euros, y que podemosafirmar, con un nivel de confianza del 95%, que dicho valor estar entre 528 y 560 Euros.


13/14



BIBLIOGRAFA ___________________________________

[1] Seila, A.F. (2001): Spreadsheet Simulation. Proceedings of the 2001 Winter Simulation

Conference, pp. 74 78.

[2] Savage, S.L. (1998): Insight.xla: Business Analysis Software for Microsoft Excel. Duxbury Press.

[3] Camm, J.D.; Evans, J.R. (1996): Management Science: Modelling, Analysis and Interpretation.

South Western College Publishing.

[4] Winston, W.L.; Albright, S.C. (1997): Practical Management Science: Spreadsheet Modeling and

Applications. Duxbury Press.

[5] Gedam, S.G.; Beaudet, S.T. (2000): Monte Carlo Simulation using Excel Spreadsheet for

Predicting Reliability of a Complex System. Proceedings Annual Reliability and Maintainability

Symposium.

[6] Evans, J.R. (2000): Spreadsheets as a Tool for Teaching Simulation. Informs Transactions On

Education Volume 1, Number 1. http://ite.informs.org/Vol1No1/evans/evans.html

[7] Eckstein, J; Riedmueller, S.T. (2002):YASAI: Yet Another Add-in for Teaching Elementary Monte

Carlo Simulation in Excel. Informs Transactions On Education. Volume 2, Number 2.

http://ite.informs.org/vol2no2/EcksteinRiedmueller/

[8] Judge, G. (1999):Simple Monte Carlo studies on a spreadsheet. Computers in Higher Education

Economics Review (CHEER). Volume 13, Issue 2.

http://www.economics.ltsn.ac.uk/cheer/ch13_2/ch13_2p12.htm

[9] Hwarng, H.B. (2001): A Modern Simulation Course for Business Students. Interfaces, 31:3, Part

1 of 2, May-June 2001, pp. 66-75.

[10] Nance, R.E., Sargent, R.G. (2002): Perspectives on the Evolution of Simulation. Operations

Research, Vol. 50, No. 1, January-February 2002, pp. 161-172.


14/14



ENLACES ___________________________________

[W1] http://www.geocities.com/CollegePark/Quad/2435/index.htmlBreve historia de los orgenes del mtodo Monte Carlo.

[W2] http://www.palisade.com/Pgina web de @Risk

[W3] http://www.crystalball.com/Pgina web de Crystal Ball

[W4] http://www.kellogg.nwu.edu/faculty/myerson/ftp/addins.htmPgina web de SimTools.xla

[W5] http://analycorp.com/stan/Pgina web de Insight.xla

[W6] http://www.projectware.com.au/tutorials/Tu08.pdfBreve artculo sobre simulacin MC con Excel

[W7] http://www.barringer1.com/MC.htmPgina web donde se comentan algunas aplicaciones de Excel y simulacin MC para elestudio de la fiabilidad de sistemas

[W8] http://random.mat.sbg.ac.at/links/index.htmlPgina web de la WWW Virtual Library dedicada a nmeros aleatorios y simulacin MC

[W9] http://csep1.phy.ornl.gov/mc/mc.htmlLibro electrnico sobre simulacin MC

[W10] http://www.geocities.com/WallStreet/9245/vba.htmPgina web donde se muestran algunos ejemplos de simulacin MC con Excel y VBA

[W11] http://www.csun.edu/~vcmgt0j3/Ch12Notes.pdfApuntes sobre simulacin MC con Excel

[W12] http://www.wabash.edu/depart/economic/EconExcel/home.htmModelos econmicos y economtricos con Excel (algunos usan simulacin MC)

simulación monte carlo en excel

Documents