Ejemplo de Tabla de datos-Excel 2007.

Desarrollaremos un análisis de sensibilidad sobre dos variables mediante la herramienta, ya conocida, de la Tabla de datos; en esta ocasión emplearemos la versión Excel 2007.
Suponemos una inversión con una tasa de rentabilidad del 2,25% y los siguientes flujos:

en la situación actual el Valor actual de la operación es 1.038,14 eur
=VNA.NO.PER(C5;B3:G3;B2:G2)
con los siguientes argumentos
=VNA.NO.PER(tasa; valores; fechas)
Lo que nos interesa analizar distintos aspectos de la inversión según varíen dos valores, por ejemplo,la tasa de rentabilidad y el importe del último cobro. Como dato especial mencionar que al ser fecha no periódicas hemos empleado la función de VNA.NO.PER que tiene en cuenta esa circunstancia.
Para ejecutar esta herramienta desde Excel 2007 nos deberemos dirigir al Menú Datos > Herramientas de datos > Análisis Y si > Tabla de datos:

Marcaremos por tanto el rango donde pretendemos analizar esta variación de resultados, sabiendo que previamente hemos vinculado la celda superior izquierda del rango a seleccionar con el resultado que pretendemos estudiar, en nuestro caso, con el Valor actualizado (=VNA.NO.PER). Ver entrada con ejemplo de Tabla de datos.
Seleccionaremos, en la ventana diálogo de Tabla de datos, como Celda de entrada(fila) la celda C5, i.e., donde está la rentabilidad sobre la que se calcula el Valor actual; y como Celda de entrada (columna) la celda G3, que refleja el último cobro de la inversión.

El resultado final obtenido será:

La interpretación de los datos era muy obvia desde el planteamiento, a mayor rentabilidad y mayor fuera el último cobro, mayor es el valor actual de la operación.
Podríamos haber cruzado los datos del último cobro con el del primer desembolso... lo haremos en una entrada futura del blog.

Seguir leyendo...

Una explicación gráfica de Solver.

Recuperaremos hoy una entrada anterior en la que analizabamos con Solver cuál era el punto muerto de una cuenta de resultados propuesta por un productor de leche de oveja y cabra Ejemplo de Solver. En este ejercicio planteabamos el modo en que Solver solucionaba y encontraba las incognitas, las raices, del sistema de ecuaciones que habíamos planteado:
2,35 x + 1,85 y = z
1,35 x + 0,90 y = s
0,25 x + 0,65 y = t
z + s + t = 0
x / (x + y) = 0,5128 (igual a la recta y=0,95x).
Ya quedó explicado en el post Explicaciones sobre Solver.
Intentaremos plasmar esta misma idea en forma gráfica, utilizando los gráficos de superficie sobre una Tabla de datos aplicado sobre el modelo de cuenta de resultados propuesto. Lo haremos, debido a la limitación con la que me he encontrado, en dos tramos. En primer lugar expandiremos un plano en el espacio formado por lo litros de leche de oveja, los litros de leche de cabra y la cifra en euros del resultado (dada por las cuatro primeras ecuaciones del sistema). En segundo lugar interseccionaremos el plano obtenido con la recta que corresponde a la restricción dada a Solver (la quinta ecuación del sistema).
Recordemos nuestro modelo de cuenta de resultados:

Aplicaremos sobre las dos variables (litros de leche) el siguiente análisis de sensibilidad (ver ejemplo Tabla de datos); desplegando una Tabla de doble entrada por filas los litros de leche de oveja, y por columnas los litros de leche de cabra, vinculado al resultado combinación de ambas, nos quedaría:

haz click en la imagen

He dado formato a aquellas celdas de resultado próximas a un resultado cero. Es normal no obtener el resultado exacto, ya que la distribución por filas y columnas de los litros de leche se ha hecho en intervalos de 100 litros; por lo que se pierde sensibilidad.
Claro está que, en todo caso, esa tabla de datos nos muestra en su cruce con el plano imaginario de resultado cero, las infinitas soluciones existentes en el sistema de cuatro ecuaciones-indeterminación- (se ha añadido una línea adicional amarilla al gráfico para que se visualice mejor ese corte de planos):

Tan sólo nos quedaría entonces incluir en el gráfico una recta (y = 0,95 x) que se moverá por el plano de resultado cero y que en un único punto (y=litros leche de cabra=1.193; x=litros leche de oveja=1.256) interseccionará con el plano anterior:

Queda marcada en la imagen la intersección de la quinta ecuación-en verde- del sistema (la recta y=0,95x) con el plano obtenido de las cuatro primeras ecuaciones.

Seguir leyendo...

Gráfico Análisis sensibilidad: Gráfico de Superficie.

En una entrada anterior, sobre Tablas de datos, planteamos un ejemplo muy típico para conocer el funcionamiento de esta herramienta de Excel. Nos quedaría por analizar el gráfico que generaría este Análisis de sensibilidad sobre una cuota de préstamo.
El tipo de gráfico ideal para visualizar el resultado obtenido es, sin duda, el Gráfico de superficie. Con él, obtendremos una superficie por la cual se desplazan los valores resultantes según nos movemos en el espacio por los distintos ejes creados (un eje será la variable 'tipo de interés', otro la variable 'Plazo' y el último el 'importe' que alcanza la 'Cuota anual' calculada).

Si analizáramos este gráfico, percibiríamos en qué manera nuestra cuota anual de un préstamo varía según aumenta el Plazo o crece el Tipo de interés. Vemos que el crecimiento/decrecimiento no es lineal, tiene forma asintótica con los ejes. Analizaríamos que el efecto mayor sobre la cuota lo obtenemos de la variación en el Plazo, y que el Tipo de interés tiene un efecto menor sobre la variación.
Para alcanzar este gráfico tan solo hemos seleccionado el rango de resultados de la Tabla (rango C8:I15) y desde el Asistente para gráficos, en el paso Uno-Tipo de gráfico seleccionamos los de Tipo Superficie (son graficos de 3D); es en el paso Dos-Datos de origen, dentro de la pestaña-Serie, donde realizamos todo el trabajo.

Seleccionaremos como Rótulos del eje de categorías (eje X) los valores que definimos en un principio para alguna de las variables de estudio, en nuestro ejercicio, seleccionamos el rango B8:B15, que nos vincula con los valores asignados en nuestra Tabla de datos a la variable 'Plazo' (podríamos habernos decantado por la variable 'Tipo de interés', entonces seleccionaríamos el rango C7:I7). Como ya teníamos marcado el rango de valores (rango C8:I15), sólo nos queda asignar Nombre a cada una de las series generadas, por lo que en nuestro ejemplo iremos celda a celda, desde la C7 a la I7 (realmente este paso se podría omitir). Y ya tendríamos nuestro Gráfico de superficie creado, salvo que quisieramos delimitar algún punto más desde el Asistente.

Seguir leyendo...

Ejemplo de Tabla o Tabla de datos - I.

Ensayaremos hoy sobre el uso de la herramienta avanzada de Excel que nos ayuda en los momentos que necesitamos realizar un Análisis de sensibilidad. En la versión Excel 2003 se llama 'Tabla' y en la versión Excel 2007 'Tabla de datos', si bien es exactamente la misma utilidad. Es una herramienta muy sencilla de emplear, sólo debemos tener una hoja de cálculo con alguna formulación que nos vincule distintas celdas o variables, ya que con este análisis de sensibilidad podremos estudiar las variaciones de un resultado de acuerdo a un máximo de dos variables (al hablar de Tabla se no restringe las variables de estudio a la bidimensionalidad).
Practicaremos el uso con un ejercicio muy típico en distintas web. Analizaremos la variación de una cuota anual de un préstamo hipotecario según cambien dos de sus parámetros (tipo de interés y plazo). Es un ejercicio muy sencillo pero a la vez muy gráfico.
Conocidas las condiciones de un Préstamo:
Principal: 150.000,00 eur
Tipo interés: 3,36% anual
Plazo: 25 años
y aplicada la función financiera PAGO sobre éstos
=PAGO(tasa, número periodos, valor)
obtendríamos inicialmente una cuota anual de 8.963,36 eur.
Es a partir de este resultado desde donde vamos a obtener una Tabla de datos donde poder observar la sensibilidad al cambio de la cuota respecto de dos de sus tres variables.
Empezamos preparando lo que será nuestra Tabla; para lo que en primer lugar vinculamos la celda donde habíamos obtenido la cuota anual (calculada con la función PAGO) en el lugar elegido, a continuación proyectamos por filas y/o columnas ambas variables de estudio.

En las filas dispondremos distintos valores de la variable Tipo de interés y en la columna valores del Plazo (estos valores los podremos cambiar en cualquier momento, según nuestras necesidades). Llegados a este punto sólo nos queda aplicar sobre nuestro rango definido la herramienta de análisis, tan sólo remarcaremos el rango en cuestión y ejecutaremos desde el menú Datos la herramienta Tabla (para Excel 2003) o desde el menú Datos y Analisis y Sí - Tabla de datos (para Excel 2007), nos aparecerá una ventana con dos argumentos por definir:

donde seleccionaremos en el argumento 'Rango de entrada(fila)' aquella celda de nuestra formulación original la varible que represente los valores espeficicados en la fila; para nosotros la celda B3 que es la del Tipo de interés. De igual forma actuaremos sobre el argumento 'Rango de entrada(columna)', donde seleccionaremos la celda B4 que era en origen nuesta celda de Plazo. Sólo nos queda Aceptar para que se genere nuestra Tabla de datos sensible a las variaciones de dos variables.

Observar que nos ha generado una Función matricial llamada Tabla, que no será modificable.
Remarcar también que el análisis se podrá realizar sobre una sola variable.
MUY IMPORTANTE: NO CONFUNDIR NUNCA CON TABLAS DINÁMICAS!!!

Seguir leyendo...