1 An Lisis De Sensibilidad

4 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 yuna 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 laformula "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óncomo 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 ovariació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 parasensibilizar, 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 elplazo 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 cualhorizontalmente 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 desalida, 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...