Simulación del número de pasajeros

Puede descargar el archivo: simulapasajeros.xlsx

Teoría de Colas

Descargar el fichero: colas.xlsx

La Teoría de colas es una especialidad de la Investigación Operativa que nos permite estudiar el comportamiento de servicios atendidos en los que se producen esperas. Es el caso típico de una cola en un banco, o un microprocesador que debe atender una lista de trabajos. En este artículo vamos a considerar un servicio médico de una consulta donde un único médico ha de atender a una serie de pacientes que llegan a lo largo de la mañana. Se trata de determinar mediante Simulación de Montecarlo los usuarios que se encuentran en la cola en cada momento.

En primer lugar hemos de recoger datos. Realizamos un estudio observando dos variables:

1. Datos históricos de tiempo entre llegadas (minutos)
2. Datos de tiempos de servicio (minutos)

Organizamos esta información en forma de tabla de frecuencias.

Necesitaremos generar una serie de números aleatorios para poder efectuar la simulación de Montecarlo.

Con la información anterior procedemos a crear la tabla de la Hoja2.

Trabajamos con el formato de celda personalizado h:mm. El inconveniente que tiene esta forma de trabajar es que en realidad Excel trabaja con decimales. Así un minuto que en este formato sería 0:01 si lo mostramos en formato general arroja un valor de 0,000694444444444444. Esto supone que al establecer igualdades o desigualdades es frecuente obtener errores. Por ejemplo, si queremos saber si dos celdas contienen el mismo valor expresado en horas y minutos (hh:mm) es posible que veamos lo mismo, por ejemplo 0:23 (que son 23 minutos), pero si lo tuviéramos en formato general es posible que difieran en el último decimal y esto hace que Excel nos diga que no son iguales, lo cual acarrea errores importantes en nuestros cálculos finales.

Para evitar estos errores hemos creado las columnas K y L (de color azul) que en realidad son las mismas que las columnas D e I, pero con un redondeo a 7 decimales. De esta forma podemos comparar tiempos sin estar preocupados por el error del último decimal.

La columna que realmente supone una explicación detallada es la última columna que determina el número de usuarios que hay en la cola en cada momento.

Tomemos como ejemplo la celda M20 cuya fórmula es:

{=SUMA(--(K20<=($L$6:L19)))}

Los corchetes indican que es una fórmula matricial. Los corchetes no debemos ponerlos nosotros, los pondrá Excel al validar la expresión. Después de escribir la fórmula no debemos pulsar ENTER para validarla, sino que, por tratarse de una fórmula matricial, se han de pulsar simultáneamente las tres teclas siguientes:

CONTROL+MAYUSCULAS+ENTER

Para ampliar información sobre fórmulas matriciales puede consultar el siguiente post:

• Funciones matriciales en Excel

La celda M20 calcula cuantos usuarios hay en la cola en ese momento. Esto se hace considerando cuantos usuarios previos a él aún no han salido de la consulta cuando el llega.

Simulación de Montecarlo

Los aleatorios que se presentan en este ejemplo no cambian, esto es, no se generan nuevos aleatorios. Si quisiéramos efectuar el método de simulación de Montecarlo de una forma más dinámica deberíamos generar nuevos aleatorios, y con ellos calcular las diferentes filas de esta tabla. Cuantos más aleatorios generemos mejor establecidas quedaran luego las medias de tiempos de espera y usuarios en cola que es lo que realmente deseamos estudiar con estos casos.

Lluvia

Dispone del siguiente archivo de Excel: lluvia.xlsm

Hoja1

Vamos a generar un poco de lluvia con una macro. De forma aleatoria generamos la posición de una celda dentro del un area, y en ese punto hacemos caer una gota de lluvia representada por un asterisco (*). Esta idea tan simple es la introducción que nos permite entender cómo se utiliza el método de simulación de Monte Carlo para el cálculo de áreas.

Código

Sub puntos_aleatorios()
Dim i As Integer
For i = 1 To 100
    Cells(Int(Rnd * 30) + 1, Int(Rnd * 30) + 1) = "*"
Next i
End Sub

Sub Borra()
Range("A1:AD30").ClearContents
Range("AQ1").Select
End Sub

Rnd genera un número aleatorio entre 0 y 1. Equivale a la función =ALEATORIO().

Hoja2

En la Hoja2 vamos a calcular estimar áreas usando la lluvia.

Simulación del Máximo Beneficio

Archivo de Excel utilizado en el vídeo: MaxB.xlsm

Maximizar el beneficio empresarial es habitualmente el objetivo que se persigue en una compañía. Pueden existir muchos otros objetivos, pero cuando se trata de optimización uniobjetivo el beneficio generalmente es la variable elegida. En este caso vamos a diseñar un modelo empresarial muy sencillo, (pocas ecuaciones) y vamos a utilizar la simulación de Monte Carlo para la determinación del beneficio esperado.

Una empresa efectúa una previsión del beneficio que obtendrá el próximo ejercicio en base a un modelo, y a información histórica que establece las diversas distribuciones de probabilidad de las variables que intervienen en el modelo.

Modelo

El modelo es el siguiente.

B=I-G
I=pq
G=CF+CV
CV=cq

1ª Ecuación: B=I-G
El beneficio es igual a los ingresos menos los gastos.

2ª Ecuación: I=pq
Los ingresos son el producto de precio unitario por cantidad vendida.
Para simplificar suponemos que todo lo que se produce se vende. No existe almacén.

3ª Ecuación: G=CF+CV
Los gastos se calculan como suma de Costes Fijos más Costes Variables.
En este caso se confunden gastos con costes. Admitamos la equivalencia.

4ª Ecuación: CV=cq
Los Costes Variables son el producto de costes unitarios por cantidad producida.
Para simplificar suponemos que todo lo que se produce se vende. No existe almacén.

El precio se distribuye como una normal de media 100 y desviación típica 20.
Los coste fijos son una uniforme entre 120.000 y 160.000 euros.
El coste unitario se distribuye según una triangular de entre 50 y 70, con media en 60.
La cantidad vendida es una logonormal de media 10.000 unidades y desviación típica 1,2.

Variables

Existen cuatro variables de entrada INPUTS:

• Precio
• Coste Fijos
• Coste Unitario
• Cantidad (q)

Y dos variables de salida OUTPUTS:

• Beneficio
• Desviación Típica

Las variables de entrada vienen dadas por distribuciones de probabilidad conocidas en base a la información histórica, o a la experiencia del experto que está realizando el modelo.

Distribuciones

Precio

Se calcula en la celda C18 con la siguiente expresión

=REDONDEAR(INV.NORM(ALEATORIO();100;20);2)

Para versiones 2007 y anteriores usar la siguiente fórmula.

=REDONDEAR(DISTR.NORM.INV(ALEATORIO();100;20);2)

Se trata de una distribución normal de media 100 y desviación 20.

Costes Fijos

Se calculan en la celda C19 con la siguiente expresión

=ALEATORIO.ENTRE(120000;160000)

Es una distribución uniforme entre 120.000 y 160.000.

Coste Unitario

Se calcula en la celda C20 con la siguiente expresión.

Siendo D20 un número aleatorio uniforme entre cero y uno, que se obtiene con =ALEATORIO().

La explicación de esta expresión se encuentra en la Hoja2 donde se trabaja con una distribución triangular entre 50 y 70 con media en 60. Para determinar la distribución triangular se han de conseguir las ecuaciones de las dos rectas que la definen. Dividimos el triángulo en dos triángulos. El primero va entre 50 y 60 y sabemos que en él se encuentra el 50% de la probabilidad total. El segundo triángulo va entre 60 y 70 y en él se encuentra el otro 50% de la probabilidad total.

Para determinar la altura del triángulo consideramos que en primer triángulo la probabilidad es 0,5, y la base es 60-50. ¿Quíen será la altura?.

Superficie=base*altura/2

0,5=(60-50)*altura/2

De este expresión deducimos que la altura es 10.

Estamos en la Hoja 2. En la celdas C5 a C15 creamos una serie entre 50 y 70, de dos en dos. Vamos a deducir ahora los valores de la columa B que corresponde a cada valor de la columna C.

En 50 la probabilidad es cero.

En 60 la probabilidad es 0,5.

¿Cuál será la probabilidad en 58?.

Para contestar a esta pregunta hemos de calcular el área del triángulo hasta 58. Su área es:

Area= Base*Altura/2

La base es: Base= (58-50)

Al ser la altura igual a la base el área es: (58-50)^2/2

La celda B9 calcula esta expresión dividiendo entre 100 ya que se trata de probabilidad y la probabilidad total ha de ser 1.

=+(C9-50)^2/2/100

En la celda D9 calculamos el coste unitario que corresponde a la probabilidad anterior. Esto se consigue planteando la expresión anterior como una ecuación matemática y ahora se despeja la probabilidad en función del coste unitario.

=+(B9*200)^0,5+50

Esto que hemos visto se hace para el primer triángulo y se ha de hacer lo propio para el segundo triángulo. De forma que la expresión que finalmente nos de el coste unitario llevará un condicional (SI) que aplicará una ecuación u otra en función de si el número aleatorio que se utiliza en la simulación es menos o mayor que 0,5.

Cantidad (q)

Se calcula en la celda C21 con la siguiente expresión.

=REDONDEAR(INV.LOGNORM(ALEATORIO();LN(10000);LN(1,2));0)

Para versiones 2007 y anteriores usar la siguiente fórmula.

=REDONDEAR(EXP(DISTR.NORM.INV(ALEATORIO();LN(10000);LN(1,2)));0)

Se trata de una distribución Logonormal o Lognormal de media 10.000 y desviación 1,2.

Las distribuciones lognormales se utilizan preferiblemente a las normales, ya que la campana de Gauss va desde menos infinito hasta más infinito, y no son válidas para trabajar con variables que no pueden tomar valores negativos. Por ejemplo, para precios, número de personas, distancia en kilómetros, coste, entre otras muchas no podemos, o mejor dicho, no deberíamos utilizar distribuciones normales ya que no estamos exentos de obtener valores negativos. En estos casos son preferibles las distribuciones lognormales.

En Excel la media y la desviación se han de introducir con la función LN, que es la de logaritmo neperiano.

Cálculo del Beneficio

• Calculamos el Coste Variable en la celda C24.
• Calculamos los Gastos en la celda C25.
• Calculamos los Ingresos en la celda C26.
• Calculamos el Beneficio en la celda C27.

Ya hemos calculado el Beneficio. Pulse la tecla F9 y compruebe que varía. Incluso en algunas ocasiones se obtiene un beneficio negativo. Cada una de estas concreciones de la variable Beneficio se supone que es un hipotético próximo año. Para hacer la simulación debemos generar un gran número de posibles beneficios y luego estudiar la distribución a la que se ajustan.

Sub ciclos()
Dim i As Long, c As Long
Dim Beneficio As Double
c = Val(InputBox("iteraciones?", , 1000))
Columns("K:K").Clear
Range("A1").Select
For i = 1 To c
    Beneficio = Range("C27")
    Range("I14") = i
    Cells(i, "K") = Beneficio
Next i
End Sub

Los beneficios obtenidos en cada iteración se apuntan en la columna K, con pegado especial valores. Esto se hace con una macro. Observar que hemos creado una sencilla macro que nos pregunta el número de iteraciones que deseamos, por ejemplo 5.000 y seguidamente se lanza a simular la variable Beneficio hasta completar los 5.000 valores de la columna K.

Histograma

Con los 5.000 valores del beneficio de la columna K, creamos un Histograma o Diagrama de Frecuencias. Para aprender a hacer histogramas con la función matricial FRECUENCIA, se puede ve en este mismo Blog u Post que lo explica: Histograma.

El histograma se asemeja a una campana de Gauss. No es muy perfecta debido a que hemos tomado únicamente 5.000 valores del beneficio, y en simulación se deben realizar muchas iteraciones para que los valores queden bien definidos. Sería interesante realizar 100.000 iteraciones, o un millón. Para efectuar un gran número de iteraciones es aconsejable utilizar una macro que maneje matrices. De esta forma evitamos utilizar la hoja de cálculo para anotar resultados intermedios, con lo que ganamos en velocidad y evitamos el límite del número de filas disponibles. También sería interesante diseñar una macro que haga el histograma.

Simulación de Montecarlo: aplicación financiera

Descargar el fichero: simulabono.xls

En este fichero de Excel realizamos un caso de simulación de Montecarlo aplicado a Renta Fija. Más concretamente, lo que hacemos es trabajar con bonos en los que el cupón anual no es de cuantía cierta sino aleatoria. El cupón se ajusta a una distribución normal de media 500 y desviación 50. N(500;50).

=DISTR.NORM.INV(ALEATORIO();500;50)

Conocida la ETTI (Estructura Temporal de los Tipos de Interes) o Curva de Tipos podemos calcular el precio de un bono. Para el bono A (celda amarilla) el precio se calcula con la siguiente expresión:

{=-SUMA((Flujosa/(1+ETTI)^Tiempo))}

Los corchetes indican que se trata de una fórmula matricial que se introduce no pulsando INTRO, sino pulsando Control+Mayúsculas+INTRO.

En la celda F5 (color azul) indicamos en cuantos años madura el Bono B, utilizamondo la fórmula:

=ALEATORIO.ENTRE(8;9)

Esta fórmula genera números aleatorios enteros entre un mínimo y un máximo. En este caso hemos escrito entre 8 y 9. Se utiliza para indicar la duración variable en años del bono B.

Si la fórmula fuera =ALEATORIO.ENTRE(9;9) no supondría variación aleatoria, ya que el número de años de duración sería siempre 9.

Al pulsar la tecla F9 (recálculo manual) los valores aleatorios se recalculan. Esto supone que cambien el importe de los cupones de los bonos e incluso la duración del Bono B. Lo que implica que la TIR (Tasa Interna de Rentabilidad) que expresa la rentabilidad del bono sea una variable aleatoria.

Vamos a estudiar la TIR del Bono B. Al tratarse de una variable aleatoria la identificaremos estudiando su distribución de probabilidad. Por el Teorema Central del Límite sabemos que al trabajar con múltiples funciones de distribución, en el caso extremo (cuando n tiende a infinito, o en este caso cuando el número de iteraciones es suficientemente grande) casi siempre la distribución que se obtienen en una Normal o Campana de Gauss.

Para generar las iteraciones, recalculamos la TIR del Bono B un elevado número de veces, por ejemplo 10.000 y anotamos en al columna H las concrecciones de la TIR así obtenida. Con esos 10.000 valores de TIR efectuamos un análisis de frecuencias, creando un histograma de frecuencias tal y como se vió en un post anterior. El análisis del histograma nos permite ajustar la distribución a una Normal, cuya media y varianza podemos calcular.

En el caso de que la duración del Bono B sea aleatoria entre 8 y 9, obtenemos dos Campanas de Gauss.

Utilizamos una macro para ir anotando en la columna H las diferentes TIR que se obtienen en las iteraciones.

Sub montecarlo()
Dim n 'número de interaciones
Dim i
Application.ScreenUpdating = False
n = InputBox("Introduzca el número de iteraciones", _
"Entrada de datos", 10000)
For i = 1 To n
Cells(i + 5, 8) = Range("F19")
Next i
Application.ScreenUpdating = True
End Sub

Simulación de una Distribución Normal

Puede descargar el archivo: normalBoxMuller.xlsm

En Excel disponemos de una función de distribución que genera directamente valores ajustados a una distribución normal, pero en esta ocasión vamos a generarlos sin recurrir a las funciones de Excel. La denominada transformada de Box-Müller permite generar una distribución Normal. Para ello se utilizan dos distribuciones Uniformes [0;1], que en Excel se obtienen con la función:

=ALEATORIO()

La función es la siguiente:

=+RAIZ((-2*LN(ALEATORIO())))*SENO(2*PI()*ALEATORIO())

Enlace a la Wikipedia.

Podríamos programar una función que nos de una Normal de media mu y desviación típica sigma.

Código:

Function xNORMAL(mu, sigma)
Dim NORMAL01
Const Pi As Double = 3.14159265358979
Randomize
NORMAL01 = Sqr((-2 * LN(Rnd))) * Sin(2 * Pi * Rnd)
xNORMAL = mu + sigma * NORMAL01
End Function

Function LN(x)
LN = Log(x) / Log(Exp(1))
End Function

Por ejemplo, la función =xNORMAL(100;2) nos proporcionará un número aleatorio que se ajusta a una distribución normal de media 100 y desviación típica 2.

Método Monte Carlo de Simulación

Dispone de los siguientes archivos de Excel.

• 600montecarlo.xlsm
• Montecarlo.xlsm

Todo método de simulación pretende comprender el comportamiento de la realidad en base al estudio de un modelo simplificado que represente el comportamiento de la parcela objeto de estudio.

El método MonteCarlo de simulación permite estudiar el comportamiento de las variables de salida del modelo en base a dar valores a las variables de entrada, teniendo en cuenta sus distribuciones de probabilidad.

Cuanto mayor sea el número de iteraciones más estables serán los valores obtenidos. Mejor 10.000 iteraciones que 1.000, y aun mejor un millón. Por tanto, se precisa de un ordenador que realice los cálculos, y en este sentido Excel es una magnífica herramienta.

El fichero 600montecarlo.xls contiene un ejemplo en el que se estima el valor de PI. La estimación de PI no es muy buena, pero el procedimiento empleado es muy didáctico.

En la Hoja2 se generan los puntos interiores del primer cuadrante de la circunferencia.

Al inicio de este post dispone del enlace para poder descargar el archivo Montecarlo.xlsm que es otro ejemplo. En él se realiza una simulación de un caso discreto.