martes, 19 de junio de 2018

Excel y la Maquina de Galton

Recientemente, leyendo el blog del maestro Adolfo Aparicio encontré un post sobre la máquina de Galton, donde desarrollaba mediante macros el experimento de Francis Galton.

Me pareció interesante replicar mediante funciones el comportamiento de esta caja de Galton (ver Wikipedia)


La idea es demostrar como un comportamiento aleatorio sobre la posibilidad de tomar dos caminos conduce a una estructura de campana, tendente a la forma de una campana de Gauss o de una distribución normal.



Inicialmente generaremos una estructura de pirámide de 11 niveles, comenzando en la cúspide de esta, celda L4 con la fórmula:
=ALEATORIO.ENTRE(0;1)
esta función nos aporta el componente de azar necesario...

Desde de esa celda L4, siguiendo la pendiente izquierda, celdas:
$K$5,$J$6,$I$7,$H$8,$G$9,$F$10,$E$11,$D$12,$C$13 y $B$14
añadimos la fórmula:
=SI(L4=0;ALEATORIO.ENTRE(0;1);"")
es decir, si la celda anterior fuera 0 generamos un nuevo aleatorio 0-1

Excel y la Maquina de Galton



Desde la celda L4 y siguiendo la pendiente derecha, celda:
$M$5,$N$6,$O$7,$P$8,$Q$9,$R$10,$S$11,$T$12,$U$13 y $V$14
añadimos la fórmula:
=SI(L4=1;ALEATORIO.ENTRE(0;1);"")
es decir, si la celda anterior fuera 1 generamos un nuevo aleatorio 0-1

Excel y la Maquina de Galton



Completamos el interior de nuestra pirámide aleatoria.
En las celdas alternas:
$L$6,
$K$7,$M$7,
$J$8,$L$8,$N$8,
$I$9,$K$9,$M$9,$O$9,
$H$10,$J$10,$L$10,$N$10,$P$10,
$G$11,$I$11,$K$11,$M$11,$O$11,$Q$11,
$F$12,$H$12,$J$12,$L$12,$N$12,$P$12,$R$12,
$E$13,$G$13,$I$13,$K$13,$M$13,$O$13,$Q$13,$S$13,
$D$14,$F$14,$H$14,$J$14,$L$14,$N$14,$P$14,$R$14,$T$14
con la fórmula en L6:
=SI(O(K5=1;M5=0);ALEATORIO.ENTRE(0;1);"")

Excel y la Maquina de Galton



Ahora vamos a convertir los 0 y los 1 resultantes en una bolita roja que simula el camino hasta la base.

Seleccionamos el rango $B$4:$V$14 y aplicamos un formato condicional tipo Conjunto de iconos de tres bolas con la siguiente configuración:
+presionamos el botón de 'Invertir criterio de ordenación'
+marcamos la opción para 'Mostrar el icono únicamente' (no mostrar el valor de la celda)
+cambiar a tipo número ambos tramos y asignarles valoro cero en los dos casos

Excel y la Maquina de Galton



En el siguiente paso obtendremos el dato acumulado de llegadas de las 'bolitas' a cada punto de la base de la pirámide.
Para ello tendremos que cambiar las opciones de Excel respecto al cálculo (a Manual) y las opciones de iteración.
Si accedemos a la ficha Archivo > Opciones de Excel> menú Fórmulas en la primera sección de Opciones de cálculo configuramos:
1-Cálculo de libro: Manual
2-Habilitar cálculo iterativo: marcado
3-Iteraciones máximas: 1

Excel y la Maquina de Galton



Tras aceptar, podremos incorporar la fórmula que nos permite acumular llegadas...
En las celdas B17,D17,F17,H17,J17,L17,N17,P17,R17,T17 y V17 añadimos la fórmula:
=SI(B14<>"";B17+1;B17)
que nos permite autosumar a la cantidad existente esas celdas, las nuevas llegadas de las bolitas a la base...

Excel y la Maquina de Galton



Finalmente seleccionando las celdas anteriores de la fila 17 podemos insertar un gráfico de líneas, con la propiedad de línea suavizada para mejorar su aspecto.

Y estamos listos para comprobar la teoría de Francis Galton... puedes empezar a presionar la tecla de función F9 para recalcular el libro de trabajo.
Comprobaremos que según aumentamos el número de simulaciones, el gráfico toma apariencia de una distribución normal, i.e., concentro en los puntos interiores la mayoría de las llegadas...

En mi ejemplo, con unas 7.000 simulaciones, el aspecto es el siguiente:

Excel y la Maquina de Galton

jueves, 14 de junio de 2018

Modelo de Montecarlo sobre un proyecto de inversión

En la entrada anterior del Blog hablamos de qué era y como operaba el Método de Montecarlo.
Hoy la daremos un uso más financiero, usándolo para analizar un proyecto de inversión...
en el ejemplo supondremos vamos a invertir en un pequeño hotel rural... y vamos a analizar nuestra inversión a cinco años.


Nuestros datos relevantes (y muy simplificados) son los siguientes:

Modelo de Montecarlo sobre un proyecto de inversión



A continuación desplegamos algunos tramos calculados para los cinco años de estudio:
+Ingresos
+Costes Variables
+Costes Fijos
+RDI (Resultados después de impuestos)
+Flujos de efectivos generados


Lanzaremos 10.000 simulaciones para nuestro proyecto (recuerda siempre que a mayor número de simulaciones más certeza con el resultado).

Nuestro primer bloque servirá para calcular los ingresos esperados de los cinco años.
Asi en el rango C16:G10015 añadimos la fórmula:
=$C$8*$C$10*ALEATORIO.ENTRE(45;75)/100

fórmula que contiene el componente de aleatoriedad necesario para el correcto funcionamiento de este modelo de Montecarlo

Modelo de Montecarlo sobre un proyecto de inversión



En el segundo bloque calcularemos los Costes variables que, de acuerdo a nuestras hipótesis serán del 35% de los ingresos.
Así pues, en el rango H16:L10015 añadimos la fórmula:
=C16*$C$6

Modelo de Montecarlo sobre un proyecto de inversión



En el tercer bloque trabajaremos sobre los Costes Fijos.
En el rango M16:Q10015 añadimos la fórmula/vínculo:
=$C$7
que recupera los costes fijos anuales estimados.

Modelo de Montecarlo sobre un proyecto de inversión



Otro bloque nos servirá para obtener el dato calculado del Resultado de la empresa después de impuestos.
En el rango R16:V10015 añadimos:
=(C16-H16-M16)*(1-$C$5)

esto es, (ingresos-costes variables-costes fijos)x(1-tasa Impuesto)

Modelo de Montecarlo sobre un proyecto de inversión



Llegamos al paso final.
Proyectamos los flujos de tesorería esperados para los cinco años, contando el desembolso inicial de la inversión, que según nuestros datos de partida es de 200.000,00 euros.
Los flujos de los cinco años corresponderán a los Resultados (RDI) de esos años.
En el rango W16:W10015 añadimos la fórmula/vínculo:
=-$C$4
A continuación seleccionamos X16:AB10015 y en X16 añadimos
=R16
y presionamos Ctrl+Enter

Modelo de Montecarlo sobre un proyecto de inversión



Solo nos queda el cálculo del Valor Actual Neto (función VNA en Excel).
OJO!! para el cálculo del VAN hay que usar la función VNA de los flujos positivos más la celda del desembolso inicial.
Por tanto en el rango AC16:AC10015 añadimos:
=VNA($C$11;X16:AB16)+W16

Modelo de Montecarlo sobre un proyecto de inversión



Y finalmente para representar el modelo, y obtener un resultado global, aplicamos un PROMEDIO corrido sobre esos VAN calculados...
En AD16:AD1015 añadimos
=PROMEDIO($AC$16:AC16)


Para la muestra gráfica de hacia donde tiende nuestro VAN, para nuestro proyecto, creamos un gráfico de dispersión.
Seleccionamos nuestro numeral de 10.000 celdas (rango B16:B10015) y también seleccionamos este último rango de promedio corrido (rango AD16:AD10015) e insertamos un gráfico de dispersión:

Modelo de Montecarlo sobre un proyecto de inversión



El gráfico muestra la estabilización hacia un VAN del proyecto cercano a los 12.000 euros... lo que indica que sería nuestro VAN más probable, de acuerdo al modelo de Montecarlo.

lunes, 11 de junio de 2018

El método de Montecarlo en Excel

Trataremos hoy el tan extendido Método de Montecarlo.
La esencia del método es demostrar como mediante simulaciones tendentes al infinito podemos aproximar una solución a un modelo de cualquier tipo.

En el ejemplo de hoy calcularemos, al hilo de la entrada anterior, la integral definida de una función:
f(x)=2x2
para el intervalo entre 1 y 3.


Para ello comenzaremos a trabajar sobre un rango alto de celdas, en concreto 20.000.
Así pues nos aprovecharemos de la función ALEATORIO() con la que conseguiremos una serie de números distribuidos uniformemente entre 1 y 3.
Excel genera estos aleatorios a partir del algoritmo Mersenne Twister.

En nuestro ejemplo en el rango A9:A20008 insertamos:
=ALEATORIO.ENTRE(1;2)+ALEATORIO()

Por otra parte, empleando estos valores como las x de nuestra función nos permite, en el rango B9:B20008, incluir la fórmula:
=2*POTENCIA(A9;2)



Para realizar el estudio comparativo generamos una nueva serie aleatoria en el rango C9:C20008 con la fórmula:
=ALEATORIO.ENTRE(0;17)+ALEATORIO()
que devolverá un valor entre 0 y 18, valores entre los que se puede mover nuestra función a estudio.


En último termino comparamos estos valores aleatorios entre 0 y 18 con los obtenidos con nuestra función, y en D9:D20008 insertamos el condicional:
=SI(C9<=B9;C9;0)
que nos dirá cuantos de los puntos distribuidos en el rectángulo entre 1-3 y 0-18 caen por debajo de la línea de nuestra ecuación
f(x)=2x2


Si llevamos estas series a un gráfico de dispersión tendríamos:

El método de Montecarlo en Excel



Al conocer el número de puntos por debajo de la línea de nuestra función, podemos extrapolar la proporción del área total del rectángulo general (1-3 y 0-18) que correspondería precisamente con el área buscada, esto es, con el cálculo de la integral definida.

Mediante un sencillo cálculo:
=CONTAR.SI(D9:D20008;">0")
conoceremos el número de puntos por debajo de la línea, dentro del área a calcular.
Como sabemos el total de puntos del estudio: 20000
la proporción es fácil de calcular dividiendo uno entro otro...

Esta proporción la multiplicaremos por el área total del rectángulo general (base x altura): (3-1)x(18-0) = 36
Llegando a la integral definida entre 1 y 3 de la función...



El valor obtenido se aproximará bastante a esos 17,333 conseguido con el cálculo integral...

Recuerda que a mayor número de simulaciones más aproximado será el valor conseguido!!