1.simulacion monte carlo con excel

5/10/2018 1.Simulacion Monte Carlo Con Excel - slidepdf.com

http://slidepdf.com/reader/full/1simulacion-monte-carlo-con-excel 1/50

Método de “Montecarlo”



La simulación de Montecarlo es una técnica que combina conceptosestadísticos (muestreo aleatorio) con la capacidad que tienen los

ordenadores para generar números pseudo-aleatorios y automatizarcálculos. Los orígenes del método Montecarlo se remontan al año de 1946, donde,

mientras Stan Ulam se encontraba en su casa enfermo, empezó a desarrollareste método jugando Solitario. Ulam quería saber con que probabilidad secompletaba un juego de Solitario dada una combinación de cartas iniciales.

Descubrió que era extremadamente complicado encontrar la probabilidadexacta, y tuvo la idea de obtener un estimado de la probabilidad medianteun método sumamente práctico: realizar el juego cien veces y contar elnúmero de éxitos. Rápidamente se imaginó la posibilidad de aplicar estemétodo a situaciones complejas, como en la física matemática, y Ulam leplaticó de este método a John Von Neumann, con quien estaba trabajandoen un proyecto secreto denominado Manhattan.



A Ulam se le ocurrió que esta misma observación debía aplicarse a sutrabajo de Los Álamos (Proyecto Manhattan) sobre fisión de neutrones parala detonación de una bomba.



En años posteriores, la simulación de MC se ha venido aplicando a unainfinidad de ámbitos como alternativa a los modelos matemáticos exactoso incluso como único medio de estimar soluciones para problemascomplejos. Hoy en día el Método de MC se usa en gran variedad deproblemas que aparecen en ciencia y en ingeniería, que de otro modo

tomaría demasiado tiempo resolverlos por métodos alternativos. Así, en la actualidad es posible encontrar modelos que hacen uso de

simulación MC en las áreas informática, empresarial, económica,industrial e incluso social. En otras palabras, la simulación de MC estápresente en todos aquellos ámbitos en los que el comportamientoaleatorio o probabilístico desempeña un papel fundamental.

Precisamente, el nombre de Montecarlo proviene de la famosa ciudad deMónaco, donde abundan los casinos de juego y donde el azar, laprobabilidad y el comportamiento aleatorio conforman todo un estilo devida y girar una ruleta funciona como un generador simple de númerosaleatorios.



Son muchos los autores que han apostado por utilizar

hojas de cálculo para realizar simulación MC. La potenciade las hojas de cálculo reside en su universalidad, en sufacilidad de uso, en su capacidad para recalcular valoresy, sobre todo, en las posibilidades que ofrece conrespecto al análisis de escenarios (“what-if anaylisis”).Las últimas versiones de Excel incorporan, además, unlenguaje de programación propio, el Visual Basic forApplications, con el cual es posible crear auténticasaplicaciones de simulación destinadas al usuario final. Enel mercado existen varios complementos de Excel (Add-Ins) específicamente diseñados para realizar simulaciónMC, siendo los más conocidos: @Risk, Crystall Ball,Insight.xla, SimTools.xla, etc.



Introducir los conceptos e ideas clave de lasimulación MC.

Introducirse en las capacidades que ofreceExcel en los campos de modelado ysimulación.

Conocer algunas aplicaciones de lasimulación MC.



La simulación de Montecarlo es una técnicacuantitativa que hace uso de la estadística ylos ordenadores para imitar, mediantemodelos matemáticos, el comportamientoaleatorio de sistemas reales no dinámicos(por lo general, cuando se trata de sistemascuyo estado va cambiando con el paso deltiempo, se recurre bien a la simulación deeventos discretos o bien a la simulación desistemas continuos).



La clave de la simulación MC consiste en crear un modelomatemático del sistema, proceso o actividad que se quiereanalizar, identificando aquellas variables (inputs del modelo)cuyo comportamiento aleatorio determina elcomportamiento global del sistema. Una vez identificadosdichos inputs o variables aleatorias, se lleva a cabo un

experimento consistente en: (1) generar – con 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 este experimento,dispondremos de n observaciones sobre el comportamiento

del sistema, lo cual nos será de utilidad para entender elfuncionamiento del mismo –obviamente, nuestro análisisserá tanto más preciso cuanto mayor sea el número n deexperimentos que llevemos a cabo.



Las hojas de cálculo como Excel (y cualquier

lenguaje de programación estándar) son capaces degenerar números pseudo-aleatorios provenientes deuna distribución uniforme entre el 0 y el 1.

Este tipo de números pseudo-aleatorios son los

elementos básicos a partir de los cuales sedesarrolla cualquier simulación por ordenador. En Excel, es posible obtener un número

pseudo-aleatorio -proveniente de una distribución

uniforme entre el 0 y el 1- usando la funciónALEATORIO:



Los números generados mediante la funciónALEATORIO tienen dos propiedades que los hacenequiparables a números completamente aleatorios:

1. Cada vez que se usa la función ALEATORIO,cualquier número real mayor ó igual a 0 y menorque 1 tiene la misma probabilidad de ser generado(de ahí el nombre de distribución uniforme).

2. Los diferentes números generados sonestadísticamente independientes unos de otros (esdecir, el valor del número generado en un momentodado no depende de los generados conanterioridad).



La función ALEATORIO es una función volátilde Excel. Esto significa que cada vez quepulsamos la tecla F9 o cambiemos alguno delos inputs del modelo, todas las celdas donde

aparezca la función ALEATORIO seránrecalculadas de forma automática.

Se pueden encontrar ejemplos del uso deALEATORIO en el propio menú de ayuda deExcel.



Generación de números aleatoriosPara generar 10 enteros aleatorios que estén entre 000 y 999 en Excel.

1. Iniciar con una hoja de cálculo en blanco. Clic enTools (herramientas), Data analysis* (análisis dedatos). Clic en Random number generation(generación de números aletorios), OK (aceptar).

2. Escribir 1 en el cuadro Number of variables(número de variables). Escribir 10 en el cuadro

Number of random numbers (cantidad denúmeros aleatorios). Dentro del cuadroDistribution (distribución) seleccionar Uniform (uniforme). Escribir 0 en el cuatro Between (entre) y 1 en el cuadro and (y). Clic en Outputrange (rango de salida) y escribir A1 en elcuadro. Clic en OK.El resultado será 10 números en la columna A, ycada número será un decimal entre 0 y 1.

* En office 2007 esta opción está en el menú Datos – Análisisde datos (si no está habilitado: ir al Botón de office-Opcionesde Excel-Complementos-Administrar: complementos deExcel-Seleccionar la casilla Herramientas para análisis y

Aceptar.



Este paso y el siguiente convierten losnúmeros de la columna A a enterosentre 000 y 999 que aparecerán en la

columna C.3. Clic en la celda B1 y escribir laecuación =A1*1000. Clic en la esquinainferior derecha de la celda B1 paraarrastrar hasta B10 y llenar losvalores. Clic en la celda C1 y luego en

f x. Clic en Math Et trig (matemáticas ytrigonometría) en el cuadro Category (categoría). Clic en ROUNDOWN (redondear) en el cuadro Function (función) y luego en OK.

4. En el menú Redondear, escribir B1 en

el cuadro Number (número). Escribir 0 en el cuadro Num_digits (núm_decimales). Clic en OK y luegoen la esquina inferior derecha de C1para arrastrar hasta C10 y llenar losvalores.



Muestreo aleatorio simple (ATM)Estos procedimientos seleccionan una muestra aleatoria simple a partir deuna lista de valores existentes.



1. Iniciar con una hoja de cálculo en blanco. Suponer los valores de lacolumnas A, B y C que aparecen en el ejemplo. Los tiempos de uso de unATM estarán en la columna C.

2. Clic en Tools (herramientas) y luego en Data analysis (análisis de datos).Clic en Sampling (muestreo o muestra) y luego en OK.

3. Escribir C1:C51 en el cuadro Input range (rango de entrada). Clic paraponer una marca de verificación en el cuadro Labels (etiquetas o rótulos)(porque el rango en entrada tiene el nombre de la variable en la partesuperior). Seleccionar Random (aleatorio) y escribir 10 en el cuadroNumber of samples (número de muestras). Escribir E1 en el cuadro Outputrange (rango de salida). Clic en OK.

4. La muestra aleatoria simple de 10 valores de los 50 tiempos de uso de unATM se listan en la columna E.



En la muestra aleatoria simple, cada persona o elemento en lapoblación tiene una oportunidad igual de ser incluido en lamuestra. Este tipo de muestra equivale a “sacar nombres deun sombrero”, pero suele generarse mediante procedimientosmás prácticos que la conocida analogía del “sombrero”.La definición teórica dice que, para un tamaño de muestra

específico, cada posible muestra de ese tamaño tiene lamisma oportunidad de ser seleccionada que cualquier otramuestra de ese mismo tamaño.Una alternativa práctica de poner nombres en un sombrero ocaja es identificar a cada persona o elemento en la población

con un número y luego emplear una tabla de númerosaleatorios para seleccionar los que formarán la muestra.



Material de apoyo Encuesta



Una universidad realizó una encuesta sobre laspreferencias por carrera entre 8,364 alumnosinscritos. Los datos se capturaron en una hoja deExcel en donde se registró el rango de edad de cada

alumno y la carrera en la que se inscribieron. Se desea obtener el número de elementos

necesarios para seleccionar una muestra aleatoriasimple tal que los resultados de estudio tengan unamuestra de error del 5% y un porcentaje de

confianza del 95% considerando que Z = 1.96



1. Lo primero que haremos será insertar una columna a la izquierdapara enumerar la cantidad de alumnos que contestaron la encuesta.



Para completarautomáticamente la lista desde1 hasta 8364, después decapturar el #1 en la columna A,

en la función Rellenar , opciónSeries , completamos lossiguientes datos:



2. En la siguiente columna (columna D), utilizamos la fórmula

ALEATORIO.ENTRE para que nos dé valores aleatorios entre 1 y 8364(la cantidad de alumnos inscritos).

Nota: Recordemos que al pulsar F9 todas las celdas dondeaparezca la función ALEATORIO serán recalculadas de formaautomática.

(Aquí ya tenemos la primer muestraaleatoria, correspondiente al

alumno No. 5051. Sólo restaría

buscar en la columna A paraencontrar el rango de edad y

carrera. Pero hacerlo así para unamuestra de 367 alumnos, nos

llevaría mucho tiempo.)



3. En la siguiente columna pondremos los números consecutivos del1 al 367 (tamaño requerido de la muestra).



4. Generar los 367 números aleatorios.Para hacerlo más rápido, realizaremos lo siguiente:

D3:d400(Generar más de 367 números

aleatorios en virtud de la posibleduplicidad de valores)



Al sombrearse lacolumna indicada

presionamos Ctrl+vpara “pegar” los

valores aleatorios

Podemos observar que losvalores aleatorios continúanrecalculándose y posiblementeexista duplicidad de algunos de

ellos



5. Evitaremos que cambien los valores y quitaremos los datosduplicados.

- Seleccionar los valores aleatorios (Ctrl+c)



1

2

34



Eliminamos losvalores restantes



6. Ahora ya tenemos una muestra de 367 alumnos de los 8364 quecontestaron la encuesta. Sólo resta incluir la información correspondientepara cada uno (rango de edad y carrera).



Utilizaremos la función BUSCARV en cada columna

Para que no se cambie la dirección dela tabla, es conveniente dar un

direccionamiento absoluto con elsigno de pesos (seleccionar y dar F4)



Verificamos que seala información

correspondiente



7. Por último, seleccionamos las dos celdas que acabamos de crear,de las columnas EDAD y CARRERA, y arrastramos para copiar lafórmula en los 367 valores.



De esta manera, obtuvimos una Muestra Aleatoria Simple de 367 enuna población de 8364 estudiantes.



Muestreo sistemáticoEstos procedimientos seleccionan una muestra sistemática a partir deuna lista de valores de datos existentes.

1 Iniciar con una hoja de cálculo en blanco Suponer los valores de la columnas



1. Iniciar con una hoja de cálculo en blanco. Suponer los valores de la columnasA, B y C que aparecen en el ejemplo. Los 50 tiempos de uso de un ATMestarán en la columna C.

2. Clic en Tools (herramientas) y luego en Data analysis (análisis de datos). Clicen Sampling (muestreo o muestra) y luego en OK.

3. Escribir C1:C51 en el cuadro Input range (rango de entrada). Clic para poneruna marca de verificación en el cuadro Labels (etiquetas o rótulos) (porque elrango en entrada tiene el nombre de la variable en la parte superior).Seleccionar Periodic (periódico) y escribir 5 en el cuadro Period (período).Escribir E1 en el cuadro Output range (rango de salida). Clic en OK.

4. La muestra sistemática de la columna E consta de 10 valores. Con el períodok=5, Excel selecciona el 5° valor como punto inicial, y los valores en lamuestra sistemática son el 5°, 10°, 15°, …, 50° valores de los 50 tiempos deuso de un ATM.



Aunque con un aspecto similar al de la muestra aleatoriasimple, la muestra sistemática es más fácil de aplicar en lapráctica. En este método, seleccionamos de manera aleatoriaun punto inicial entre 1 y k , y luego muestreamos cada k -ésimo elemento de la población.Un problema potencial con las muestras sistemáticas es laperiodicidad, que es un fenómeno en el cual el orden deaparición de la población incluye una variación cíclica con ciclo

de la misma longitud que el valor de k empleado al seleccionarla muestra. Por ejemplo, si se ordenan los recibos de ventasdiarias del 1 de enero al 31 de diciembre, no sería pertinenteseleccionar k =7 al elegir una muestra sistemática. En estascondiciones, terminaríamos con una muestra formada

exclusivamente por el lunes, el miércoles u otro día de lasemana.



En la siguiente imagen se muestra un análisis históricode 200 días sobre el número de consultas diarias

realizadas a un sistema de información empresarial (EIS)residente en un servidor central.

La tabla incluye el número de consultas diarias (0 a 5) junto con las frecuencias absolutas (número de días que

se producen 0, 1, ..., 5 consultas), las frecuenciasrelativas (10/200 = 0,05, ...), y las frecuencias relativasacumuladas.



Podemos interpretar la frecuencia relativa como la probabilidad de queocurra el suceso asociado, en este caso, la probabilidad de undeterminado número de consultas (así, p.e., la probabilidad de que seden 3 consultas en un día sería de 0,30), por lo que la tabla anterior nosproporciona la distribución de probabilidad asociada a una variablealeatoria discreta (la variable aleatoria es el número de consultas al EIS,que sólo puede tomar valores enteros entre 0 y 5).



Supongamos que queremos conocer elnúmero esperado (o medio) de consultas por

día. La respuesta a esta pregunta es fácil sirecurrimos a la teoría de la probabilidad:Denotando por X a la variable aleatoria querepresenta el número diario de consultas al

EIS, sabemos que:



Por otra parte, también podemos usar simulaciónde Montecarlo para estimar el número esperadode consultas diarias (en este caso se ha podido

obtener el valor exacto usando teoría deprobabilidad, pero ello no siempre será factible).Veamos cómo:

Cuando se conozca la distribución deprobabilidad asociada a una variable aleatoriadiscreta, será posible usar la columna defrecuencias relativas acumuladas para obtenerlos llamados intervalos de números aleatoriosasociados a cada suceso. En este caso, losintervalos obtenidos 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 gráfico siguiente nos muestra cada una delas probabilidades sobre el número deconsultas. En él, se aprecia claramente larelación existente entre probabilidad de cadasuceso y el área que éste ocupa.

Esto significa que al generar un número



Esto significa que, al generar un númeropseudo-aleatorio con el ordenador(proveniente de una distribución uniformeentre 0 y 1), estaremos llevando a cabo unexperimento cuyo resultado, obtenido deforma aleatoria y según la distribución deprobabilidad anterior, estará asociado a unsuceso. Así por ejemplo, si el ordenador nos

proporciona el número pseudo-aleatorio0.2567, podremos suponer que ese día se hanproducido 2 consultas al EIS.



Asignamos pues la función ALEATORIO a unacasilla (la G1 en el caso de la imagen):

A i ió d l f ió



A continuación, podemos usar la función SIde Excel para asignar un suceso a cada unode los números pseudo-aleatorios

generados.



otra forma de hacer esta asignación puede ser usando la funciónBUSCARV.



Finalmente, usando la función PROMEDIO será posiblecalcular la media de los valores de la columna H:

En este caso, hemos obtenido un valor estimado que correspondeexactamente con el valor real anteriormente calculado vía la definiciónteórica de la media. Sin embargo, debido a la componente aleatoriaintrínseca al modelo, normalmente obtendremos valores “cercanos” alvalor real, siendo dichos valores diferentes unos de otros (cadasimulación proporcionará sus propios resultados). Se puede comprobar

este hecho pulsando repetidamente sobre la función F9 (cada vez quese pulsa dicha tecla, Excel genera nuevos valores aleatorios y, portanto, nuevos valorespara la columna H y la casilla I1*). *i uno



Si en lugar de usar una muestra aleatoriaformada por 100 observaciones hubiésemosusado una formada por 10, los valores queobtendríamos al pulsar repetidamente F9 no

serían estimaciones tan buenas al valor real. Por el contrario, es de esperar que si

hubiésemos usado 1,000 (o mejor aún10,000) observaciones, los valores queobtendríamos en la casilla I1 estarían todosmuy cercanos al valor real.

1.simulacion monte carlo con excel

Documents