30 de octubre de 2011

6 Análisis de Sensibilidad: Tablas de Datos


Ejemplo: Tablas de datos


Las tablas de datos son una herramienta de Excel que nos permite calcular el valor que tomará una variable de salida de un modelo, ante valores asignados a una o dos variables de entrada.  En este orden de ideas podemos hablar de análisis de sensibilidad con una variable independiente o con dos variables independientes y una dependiente.

En esta ocasión les presento un sencillo ejemplo, el cual ilustra de manera fácil el proceso para efectuar un análisis de sensibilidad con la herramienta tabla de datos.

Vamos a suponer que tenemos los siguientes datos de una obligación financiera:



y queremos conocer el valor de la cuota mensual a pagar (la cual será nuestra variable de salida) para lo cual podemos utilizar la formula "PAGO()" de excel, que posee los siguientes argumentos:


Una vez hayamos formulado nuestra variable de salida, es decir el monto de la cuota mensual a pagar obtendremos el siguiente resultado:


Ya tenemos formulada nuestra variable de salida, ahora queremos saber como cambia dicha variable (cuota) ante cambios en un parámetro como por ejemplo la tasa de interés.  Es en una situación como esta donde la herramienta tabla de datos nos facilita el trabajo, veamos como hacerlo:

En primer lugar identificamos las variables de entrada y salida: en este caso las variables de entrada corresponden al valor del crédito, la tasa de interés y el plazo, y nuestra variable de salida es la cuota.

Digitamos verticalmente los valores que tomará la variable, en este caso definimos un delta o variación de 0,5% desde 1% hasta llegar al 5%.


Nos ubicamos en la celda A13 y digitamos "1%", luego bajamos una celda (A14) y formulamos el cambio en la tasa de interés, así: =B13+$G$3 y copiamos hasta llegar a la celda A21.  

En la celda B12 digitamos la siguiente formula =B7,  con lo cual referenciamos nuestra variable de salida.


Con lo anterior ya tenemos nuestra matriz lista para sensibilizar, con lo cual procederemos a seleccionar todo el rango (Desde A12 hasta B21) y llevamos a cabo el siguiente procedimiento: Pestaña Datos > Análisis Y Si > Tabla de Datos


Aparecerá entonces la siguiente ventana:


Diligenciamos únicamente el espacio de "Celda de Entrada (Columna):" (se utiliza cuando los valores que se asignan a la variable se han escrito a lo largo de una columna)  seleccionando la celda donde se encuentra nuestro parámetro de tasa de interés, es decir B4; hacemos click en aceptar y listo! tendremos una matriz de sensibilidad que nos indica el valor de la cuota mensual a pagar ante variaciones en la tasa de interés.


Ahora bien, que pasaría si no solo quisiéramos saber cual es el valor de la cuota ante cambios en la tasa de interés, sino también ante cambios en el plazo del crédito? tendríamos que hacer 2 tablas de datos? la respuesta a la pregunta es NO!  podemos hacer una sensibilidad en una sola tabla en la cual involucremos 2 variables independientes y 1 dependiente y podamos evaluar el impacto de dos parámetros en la variable de salida.

El procedimiento técnicamente es el mismo, con la diferencia que vamos a estructurar una matriz en la cual horizontalmente se encuentren los cambios en el parámetro de tasa de interés y verticalmente los cambios en el plazo (Variación = 6 meses), tal como se muestra a continuación:


Notese que en la celda A26 se ha referenciado nuestra variable de salida: Cuota, con el fin de poder sensibilizar el impacto de las variables independientes.  Bien, una vez tengamos la matriz y la referencia a la variable de salida, seleccionamos el rango A26 x G33 y nos dirigimos a Datos > Análisis Y Si > Tabla de datos, en esta ocasión vamos a diligenciar los datos solicitados tanto en la fila como en la columna, asi:


En la celda de entrada Fila, referenciamos la celda parámetro de la tasa de interés, y en la celda de entrada columna hacemos lo propio pero con el parámetro de plazo.  Hacemos click en aceptar y tendremos nuestra tabla de datos con 2 variables independientes y 1 dependiente!



Espero les guste y recuerden que cualquier comentario, sugerencia o inquietud la pueden manifestar comentando en el blog o en nuestra pagina en facebook: ExcelFinanciero/FacebookPage 












6 comentarios:

  1. Muy buena página!
    Gracias

    ResponderEliminar
    Respuestas
    1. Muchas gracias! trataré de publicar entradas con mas frecuencia.

      Eliminar
  2. Excelente material. Me ayudó bastante, ya que dentro de 10 horas tenía que presentar este análisis para tomar grandes decisiones.
    Adelante y éxitos.

    ResponderEliminar
    Respuestas
    1. Gracias! esa es la idea que la información sea de la mayor utilidad posible.

      Eliminar
  3. didactico totalmente...

    ResponderEliminar
  4. Hola.
    Sabes que tengo un problema con el último análisis: se me repite la cuota en cada una de las celdas que deberían variar en la tabla generada, ¿sabe alguien a qué se puede deber?

    Gracias!

    ResponderEliminar

 

Excel Financiero Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates