Análisis de sensibilidad en Excel

Al diseñar un modelo en una hoja de cálculo es posible que nos interese conocer cómo puede variar un resultado modificando el valor de alguna de las variables implicadas. Pues bien, ese proceso se puede realizar en Excel ejecutando un análisis de sensibilidad mediante la función Tabla de datos.

Manos a la obra

De cara a exponer el procedimiento a seguir, comenzaremos por crear una fórmula que calcule el recibo mensual a pagar para un préstamo. Por ejemplo, con los siguientes datos:

El resultado de dicha expresión será 1.265,30€. Sin embargo, nos gustaría conocer cómo puede cambiar dicho recibo ante variaciones del TAE y de los años. Pues bien, las respuestas serán dadas por el análisis de sensibilidad.

A continuación, vamos a preparar una tabla con los distintos valores que nos interesa probar. Eso sí, en la esquina superior izquierda de dicha tabla deberá aparecer la fórmula anterior o un vínculo a la misma.

Además, es aconsejable incorporar los valores originales para comprobar que hemos indicado bien los argumentos. Por ejemplo:

Una vez todo preparado, habrá que seleccionar la tabla anterior incluyendo los encabezados; es decir, el rango B7:I12. Después, accederemos a la cinta Datos. Allí, dentro de Análisis de hipótesis escogeremos Tabla de datos.

El cuadro que Excel muestra es muy sencillo pero requiere unas reglas estrictas. En la parte de arriba, hay que señalar el dato que en la tabla aparece en una sola fila; en esta ocasión, el TAE. Pues bien, indicaremos la celda original del interés para la fórmula; es decir, B2. Para el otro campo, nos fijamos en el dato que aparece como una sola columna en la tabla; para nuestro ejemplo, los años. Por lo tanto, especificaremos la celda original, B3.

Tras pulsar Aceptar Excel ejecutará el análisis y, dentro de los resultados de la tabla, será posible comprobar que el cruce entre 4,50% y 20 da el mismo resultado que la fórmula que está en la esquina. Esa es la señal de haber hecho bien el trabajo.

Como se ha demostrado, esta técnica de cálculo es muy rápida y nos permite hallar una gran cantidad de resultados cómodamente. Ahora bien, en caso de no haber introducido las celdas adecuadas y desear empezar de nuevo el proceso, sería necesario seleccionar solamente los resultados y suprimirlos.

Por último, vamos a fijarnos en lo que aparece en la barra de fórmula: {=TABLA(B2;B3)} ¿Te recuerda a algo?

Si practicaste con los diferentes tipos de cálculos para un mismo supuesto, recuerda que las llaves eran un signo de cálculo matricial y, efectivamente, la función Tabla de datos nos ha permitido obtener todos los resultados en un solo paso, tal y como ocurre con dicha técnica. Y ahora…

¿Te atreves a efectuar un análisis de sensibilidad?