Modelización financiera8 min de lectura
Tabla de sensibilidad Excel: probar un modelo financiero
Una tabla de sensibilidad Excel recalcula el resultado de un modelo para varios valores de una o dos hipótesis, sin copiar el modelo. Veamos cómo construirla con Tabla de datos, comprobarla e interpretarla, sobre un DCF cuyo valor va de 12,7 a 19,9 M€.

Para qué sirve una tabla de sensibilidad Excel
Un modelo financiero descansa en unas pocas hipótesis que nadie conoce con certeza: el coste del capital, el crecimiento a largo plazo, un margen, un múltiplo de salida. Una tabla de sensibilidad Excel muestra cómo cambia el resultado del modelo (valor de empresa, TIR, precio por acción) cuando esas hipótesis se mueven. Responde a la pregunta que plantean un comité de inversión, un comprador o un banquero: «¿y si usted se equivoca en un punto?».
Excel la genera con una herramienta integrada, la Tabla de datos (menú Datos, y después Análisis de hipótesis). La herramienta sustituye sucesivamente el valor de una celda de hipótesis por cada valor de una fila o de una columna, recalcula todo el modelo y escribe el resultado en la cuadrícula. Así se evita copiar el modelo cinco o veinticinco veces.
El ejemplo: un DCF que arroja 15,4 M€
Tomemos Laboratorios Vasseur, una empresa ficticia de análisis industriales. Su DCF (descuento de flujos de caja futuros) parte de lo siguiente:
- una cifra de negocios de 15,0 M€, con un crecimiento del 6 %, 5 %, 4 %, 3 % y 2 % en cinco años;
- un margen de EBITDA (resultado de explotación antes de amortizaciones) del 14 %;
- amortizaciones del 3 % de la cifra de negocios, inversiones del 3,5 % y un fondo de maniobra (el dinero inmovilizado en existencias y cuentas a cobrar) del 15 % de la cifra de negocios;
- un impuesto del 25 %, un WACC (coste medio ponderado del capital) del 10 % y un crecimiento perpetuo del 2 %.
El flujo de caja libre pasa de 1,10 M€ el primer año a 1,36 M€ el quinto. Los cinco flujos descontados suman 4,64 M€; el valor terminal, 1,36 × 1,02 ÷ (10 % − 2 %) = 17,34 M€, vale 10,77 M€ una vez descontado. El valor de empresa resulta de 15,4 M€, de los cuales el 70 % procede del valor terminal. Esa dependencia es lo que hace indispensable la sensibilidad: el valor terminal reacciona con fuerza al WACC y al crecimiento, como explica nuestro artículo sobre el valor terminal de un DCF.
Tabla de sensibilidad de una variable
Empiece por una sola hipótesis, aquí el margen de EBITDA.
- En la hoja donde está la celda del margen (14 %), escriba los valores que quiere probar en una columna: 12 %, 13 %, 14 %, 15 %, 16 %.
- En la celda situada una fila por encima y una columna a la derecha del primer valor, escriba una fórmula que remita al resultado:
=C40si el valor de empresa está en C40. - Seleccione el bloque completo, valores y fórmula incluidos.
- Menú Datos > Análisis de hipótesis > Tabla de datos. Deje vacía «Celda de entrada (fila)» e indique la celda del margen en «Celda de entrada (columna)».
El resultado:
| Margen de EBITDA | Valor de empresa |
|---|---|
| 12 % | 12,3 M€ |
| 13 % | 13,8 M€ |
| 14 % | 15,4 M€ |
| 15 % | 17,0 M€ |
| 16 % | 18,5 M€ |
Un punto de margen equivale a unos 1,6 M€, es decir, el 10 % del valor. Para una empresa cuyo margen ha oscilado entre el 12 % y el 15 % en los tres últimos años, es la hipótesis que hay que justificar en primer lugar.
Tabla de sensibilidad de dos variables: WACC × crecimiento
La tabla más habitual de un DCF cruza el WACC y el crecimiento perpetuo.
- Escriba los WACC en fila (del 9 % al 11 %, de 0,5 en 0,5 puntos) y las tasas de crecimiento en columna (del 1 % al 3 %).
- En la celda de la esquina, en la intersección de la fila y la columna, coloque la fórmula
=C40. - Seleccione todo el bloque y abra Tabla de datos: la celda del WACC en «Celda de entrada (fila)» y la del crecimiento en «Celda de entrada (columna)».
Excel rellena la cuadrícula con una fórmula matricial {=TABLA(C12;C13)}. Valor de empresa de Laboratorios Vasseur, en M€:
| g \ WACC | 9,0 % | 9,5 % | 10,0 % | 10,5 % | 11,0 % |
|---|---|---|---|---|---|
| 1,0 % | 15,9 | 15,0 | 14,1 | 13,4 | 12,7 |
| 1,5 % | 16,7 | 15,7 | 14,7 | 13,9 | 13,1 |
| 2,0 % | 17,6 | 16,5 | 15,4 | 14,5 | 13,7 |
| 2,5 % | 18,7 | 17,4 | 16,2 | 15,2 | 14,3 |
| 3,0 % | 19,9 | 18,4 | 17,1 | 15,9 | 14,9 |
El valor va de 12,7 a 19,9 M€. Un punto más o menos de WACC lo desplaza entre 1,7 y 2,2 M€; medio punto de crecimiento, unos 0,8 M€. Con 4,0 M€ de deuda neta, el valor de los fondos propios va de 8,7 a 15,9 M€: la variación relativa es aún mayor para el accionista, porque la deuda no se mueve.
Reglas de construcción que evitan errores
La Tabla de datos tiene restricciones poco conocidas. Ignorarlas produce una cuadrícula errónea sin ningún mensaje de error.
- Las celdas de entrada deben estar en la misma hoja que la tabla. Si el WACC se calcula en una pestaña aparte, cree en la hoja del DCF una celda de hipótesis que lo alimente y apunte la tabla a ella.
- La celda de entrada debe contener un valor, no una fórmula. Excel sustituye en ella cada valor probado; si reemplaza una fórmula, el vínculo con el resto del modelo desaparece durante el cálculo.
- No invertir fila y columna. Es el error más frecuente: se prueba el WACC en lugar del crecimiento y la tabla sigue pareciendo verosímil. El control es sencillo: la celda central debe ser igual, al céntimo, al resultado del caso base (15,4 M€ en este caso).
- Un número impar de pasos, con el caso base en el centro. Cinco valores, con dos a cada lado de la hipótesis elegida, facilitan la lectura. Resalte la celda del caso base en negrita o con color mediante un formato condicional.
- Pasos coherentes con la incertidumbre real. 0,5 puntos para el WACC y el crecimiento, 1 punto para un margen, 0,5x para un múltiplo. Pasos demasiado amplios producen valores extremos que nadie defiende.
- Descartar las combinaciones absurdas. Un crecimiento perpetuo igual o superior al WACC hace que la fórmula de Gordon-Shapiro diverja. Mantenga al menos 5 puntos de diferencia, o muestre «n. s.» con una fórmula SI.
Cálculo, velocidad y modelos circulares
Cada celda de la tabla vuelve a lanzar un cálculo completo del modelo. Una cuadrícula de 5 × 5 relanza por tanto 25 cálculos a cada modificación, y un modelo pesado se vuelve lento. Dos ajustes:
- Archivo > Opciones > Fórmulas > «Automático excepto en las tablas de datos». Las tablas solo se recalculan con F9. Acuérdese de pulsar F9 antes de imprimir o de enviar el archivo; de lo contrario, la cuadrícula muestra valores desfasados.
- Limite el número de tablas. Tres tablas bien elegidas valen más que diez.
Los modelos LBO plantean un problema particular: los intereses, calculados sobre la deuda media del año, dependen del flujo que amortiza esa deuda. Esta referencia circular, resuelta con el cálculo iterativo de Excel, hace a veces inestables las tablas de datos. La solución habitual es un interruptor que calcule los intereses sobre la deuda de apertura durante la sensibilidad, o una pequeña macro que copie los valores uno a uno. En un modelo LBO, la tabla típica cruza el múltiplo de entrada y el múltiplo de salida, con la TIR en el centro; el paper LBO muestra el orden de magnitud de esta sensibilidad.
Qué hipótesis probar según el modelo
| Modelo | Resultado analizado | Tabla de dos variables más útil |
|---|---|---|
| DCF | Valor de empresa, precio por acción | WACC × crecimiento perpetuo, o WACC × múltiplo de salida |
| LBO | TIR y MOIC de los inversores | Múltiplo de entrada × múltiplo de salida, o crecimiento del EBITDA × apalancamiento |
| Modelo de fusión | Acreción o dilución del beneficio por acción | Precio pagado × parte pagada en acciones |
| Plan de tesorería | Tesorería mínima | Plazo de cobro a clientes × crecimiento de la cifra de negocios |
Elija las hipótesis que sean a la vez inciertas e influyentes. El tipo impositivo es influyente pero conocido; el crecimiento del quinto año es incierto pero pesa poco. Para ordenar las hipótesis basta con una tabla de una variable por hipótesis, con la misma amplitud (± 10 %, por ejemplo): la que más desplace el resultado merece la tabla de dos variables.
Leer y presentar una tabla de sensibilidad
Una tabla de sensibilidad no es un rango de valoración. Sus esquinas combinan dos hipótesis extremas a la vez, lo que rara vez es realista. Para presentar un valor, retenga más bien la zona central (aquí de 14,5 a 16,5 M€, es decir, medio punto de WACC a cada lado) y explique por qué.
Antes de difundir la tabla, verifique tres puntos: que la celda central sea igual al caso base; que los valores evolucionen en el sentido correcto (el valor baja cuando sube el WACC); y que la tabla esté recalculada (F9). Estos controles forman parte de las verificaciones de un modelo DCF que conviene hacer antes de fiarse de un resultado.
Nuestro modelo DCF en Excel incluye una tabla de sensibilidad WACC × crecimiento ya construida, con el caso base en el centro y el peso del valor terminal a la vista. Es una herramienta de cálculo: el resultado sigue siendo una estimación que depende de sus hipótesis, y no constituye una valoración certificada ni un consejo de inversión.


