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:
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
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
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.
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)
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
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
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:
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.
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:
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
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
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.
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)
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
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
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:
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.
This post first appeared on EXCEL FORO: EJERCICIOS, EJEMPLOS, SOLUCIONES, DUDA, please read the originial post: here
