Cómo hacer un análisis de sensibilidad en Excel: paso a paso
Para realizar un análisis de sensibilidad en Excel, coloque cada entrada de su modelo en su propia celda. Luego, cambie esas entradas y registre cómo se mueve el resultado. Use una tabla de datos de una variable para una entrada, una tabla de datos de dos variables para dos, y el Administrador de escenarios para casos completos. Termine con un gráfico de tornado que clasifique las entradas por impacto.
Esta guía explica qué es un análisis de sensibilidad, cómo configurar el modelo y cómo usar cada herramienta de Excel. Incluye un ejemplo práctico con fórmulas reales, un método más rápido con AI y los errores que debe evitar.
Qué es un análisis de sensibilidad
Un análisis de sensibilidad muestra cuánto cambia un resultado cuando cambia un supuesto. Responde a preguntas como "¿Qué pasa con las ganancias si el precio baja un 10%?"
La mayoría de los modelos de negocio se basan en suposiciones. El volumen de ventas, el precio, los costos y las tasas de crecimiento son estimaciones. Un análisis de sensibilidad pone a prueba cuáles de esas suposiciones importan más.
Excel agrupa estas herramientas bajo Análisis de hipótesis (What-If Analysis). Microsoft lo define como "el proceso de cambiar los valores en las celdas" para ver cómo cambian los resultados de las fórmulas.
Microsoft enumera tres herramientas integradas: "Escenarios, Buscar objetivo y Tablas de datos". Cada una tiene una función diferente:
- Tablas de datos muestran un resultado a través de muchos valores de una o dos entradas.
- Administrador de escenarios guarda conjuntos completos de entradas, como un caso pesimista y un caso optimista.
- Buscar objetivo funciona a la inversa. Encuentra la entrada necesaria para alcanzar un resultado objetivo.
Un análisis de sensibilidad suele utilizar las dos primeras. Buscar objetivo ayuda con una pregunta relacionada, como el precio de equilibrio.
Configure el modelo antes de empezar
Las herramientas de Excel solo funcionan si el modelo está construido de forma limpia. Verifique estos puntos primero.
Ponga cada entrada en su propia celda. Las unidades, el precio, los costos y las tasas necesitan cada uno una celda. No escriba números dentro de las fórmulas.
Vincule el resultado a las entradas. La celda del resultado, como la ganancia, debe ser una fórmula que haga referencia a las celdas de entrada. Si no es así, nada cambiará cuando Excel intercambie los nuevos valores.
Mantenga las entradas juntas. Agrúpelas en un bloque en la parte superior de la hoja. Etiquete cada una y dé color a las celdas de entrada para que resalten.
Elija un resultado para probar. Un análisis de sensibilidad es más fácil de leer cuando rastrea una sola salida, como la ganancia, el valor actual neto o el efectivo al final del año.
Anote su caso base. Escriba los valores iniciales. Cada resultado en el análisis se compara con esta base.
Aquí hay un modelo de ganancias simple para seguir el ejemplo. Es una tienda de bicicletas ficticia, Northgate Cycles:
| Celda | Etiqueta | Valor |
|---|---|---|
| B1 | Unidades vendidas | 2,400 |
| B2 | Precio por unidad | $850 |
| B3 | Costo variable por unidad | $510 |
| B4 | Costos fijos | $520,000 |
| B5 | Ganancia | =B1*(B2-B3)-B4 |
Con estos valores base, la ganancia es de $296,000.
Cómo hacer un análisis de sensibilidad de una variable con una tabla de datos
Una tabla de datos de una variable prueba un rango de valores para una entrada. Microsoft dice que muestra "cómo diferentes valores de una variable en una o más fórmulas cambiarán los resultados".
Así es como se prueba el precio en el modelo de ejemplo:
- En D2 a D6, escriba los precios a probar: $800, $825, $850, $875 y $900.
- En E1, una fila arriba y una columna a la derecha del primer precio, escriba =B5.
- Seleccione D1:E6.
- En la pestaña Datos, seleccione Análisis de hipótesis > Tabla de datos.
- En Celda de entrada (columna), ingrese B2, la celda del precio. Deje Celda de entrada (fila) en blanco.
- Haga clic en Aceptar.
Excel llena E2:E6 con la ganancia para cada precio. La ubicación de la fórmula es importante. Microsoft dice que debe escribirse "una fila por encima y una celda a la derecha de la columna de valores".
Los resultados para Northgate Cycles:
| Precio | Ganancia |
|---|---|
| $800 | $176,000 |
| $825 | $236,000 |
| $850 | $296,000 |
| $875 | $356,000 |
| $900 | $416,000 |
Cada cambio de $25 en el precio mueve la ganancia en $60,000. Esa es la sensibilidad de la ganancia al precio.
Cómo hacer un análisis de sensibilidad de dos variables
Una tabla de datos de dos variables prueba dos entradas a la vez. Microsoft dice que muestra "cómo diferentes valores de dos variables en una fórmula cambiarán los resultados de esa fórmula".
Así es como se prueban el precio y las unidades juntos:
- En una celda en blanco, como D10, escriba =B5.
- Debajo, en D11 a D13, escriba los volúmenes de unidades: 2,000, 2,400 y 2,800.
- A su derecha, en E10 a G10, escriba los precios: $800, $850 y $900.
- Seleccione D10:G13.
- Seleccione Análisis de hipótesis > Tabla de datos.
- En Celda de entrada (fila), ingrese B2. En Celda de entrada (columna), ingrese B1.
- Haga clic en Aceptar.
El resultado es una cuadrícula de ganancias:
| Unidades \ Precio | $800 | $850 | $900 |
|---|---|---|---|
| 2,000 | $60,000 | $160,000 | $260,000 |
| 2,400 | $176,000 | $296,000 | $416,000 |
| 2,800 | $292,000 | $432,000 | $572,000 |
La cuadrícula muestra que la tienda sigue siendo rentable en cada combinación probada. El rincón más débil, 2,000 unidades a $800, todavía genera $60,000.
Hay un límite estricto. Microsoft dice: "Una tabla de datos no puede acomodar más de dos variables". Para más entradas, use el Administrador de escenarios.
Las tablas de datos también se recalculan cada vez que se recalcula la hoja. En un libro de trabajo grande, eso puede ralentizar Excel. Microsoft sugiere configurar el cálculo para que las tablas de datos se recalculen solo cuando presione F9.
Cómo comparar casos completos con el Administrador de escenarios
El Administrador de escenarios guarda conjuntos completos de entradas y los intercambia. Microsoft describe un escenario como "un conjunto de valores que Excel guarda y puede sustituir automáticamente en su hoja de cálculo".
Así es como se configuran tres casos:
- En la pestaña Datos, seleccione Análisis de hipótesis > Administrador de escenarios.
- Haga clic en Agregar, nombre el escenario Base e ingrese B1:B4 como las celdas cambiantes. Mantenga los valores actuales.
- Agregue un caso Peor: 2,000 unidades, precio de $825, costo variable de $530 y costos fijos de $540,000.
- Agregue un caso Mejor: 2,800 unidades, precio de $875, costo variable de $495 y costos fijos de $510,000.
- Seleccione un escenario y haga clic en Mostrar para cargar sus valores.
- Haga clic en Resumen y elija B5 como la celda de resultado para generar un informe.
Para Northgate Cycles, el resumen muestra tres ganancias. El peor caso genera $50,000, el caso base $296,000 y el mejor caso $554,000.
Microsoft señala algunos límites. Cada escenario puede contener hasta 32 valores cambiantes. El informe de resumen no es automático: "Los informes de escenario no se recalculan automáticamente". Si cambia un escenario, cree un nuevo resumen.
Cómo encontrar un punto de equilibrio con Buscar objetivo
Un análisis de sensibilidad muestra cuánto se mueve el resultado. Buscar objetivo responde a la pregunta inversa: ¿qué valor de entrada produce un resultado objetivo?
Microsoft dice que Buscar objetivo "toma un resultado y determina los posibles valores de entrada que producen ese resultado". Su ejemplo encuentra la tasa de interés necesaria para el pago de un préstamo.
Así es como se encuentra el precio de equilibrio para Northgate Cycles:
- En la pestaña Datos, seleccione Análisis de hipótesis > Buscar objetivo.
- En Definir celda, ingrese B5, la celda de ganancia.
- En Con el valor, ingrese 0.
- En Cambiando la celda, ingrese B2, la celda del precio.
- Haga clic en Aceptar.
Excel cambia el precio hasta que la ganancia llega a cero. Con 2,400 unidades y los costos base, el precio de equilibrio es de aproximadamente $726.67. Eso es aproximadamente un 15% por debajo del precio actual de $850.
Buscar objetivo tiene un límite. Microsoft dice que "funciona con un solo valor de entrada variable". Para más de una entrada, Microsoft señala el complemento Solver.
Cómo construir un gráfico de tornado
Un gráfico de tornado clasifica las entradas según cuánto mueven el resultado. Es la forma más clara de presentar un análisis de sensibilidad.
Así es como se construye uno:
- Elija un rango de prueba, como cada entrada un 10% más baja y un 10% más alta.
- Cambie una entrada a la vez y registre el resultado bajo y alto. Mantenga las otras entradas en la base.
- Ponga los resultados en una tabla con una fila por entrada.
- Ordene las filas de modo que la entrada con el rango más amplio esté en la parte superior.
- Inserte un gráfico de barras de los resultados bajos y altos.
Para Northgate Cycles, con cada entrada movida un 10% arriba y abajo:
| Entrada | Ganancia al −10% | Ganancia al +10% | Rango |
|---|---|---|---|
| Precio | $92,000 | $500,000 | $408,000 |
| Costo variable | $418,400 | $173,600 | $244,800 |
| Unidades vendidas | $214,400 | $377,600 | $163,200 |
| Costos fijos | $348,000 | $244,000 | $104,000 |
El precio es lo que más importa, por un amplio margen. Una caída del 10% en el precio reduce la ganancia de $296,000 a $92,000. Los costos fijos son los que menos importan. Eso le indica al propietario dónde dedicar tiempo a revisar los supuestos.
Un gráfico de barras en Excel dibuja barras de izquierda a derecha. Para obtener la forma de tornado, grafique el cambio de cada entrada desde la ganancia base en lugar de los valores brutos. Entonces, las barras del caso bajo apuntan a la izquierda y las del caso alto apuntan a la derecha.
Cómo hacer un análisis de sensibilidad con AI
Construir tablas de datos, escenarios y un gráfico de tornado para un modelo grande lleva tiempo. Una herramienta de AI puede ejecutar las mismas pruebas desde su libro de trabajo.
Aquí está el método en tres pasos, usando Powerdrill Bloom.
Paso 1: Cargue su modelo
Comience con la hoja de cálculo que contiene su modelo. El AI Sensitivity Analysis Generator le pide que cargue un "DCF, caso de negocio, plan operativo o cualquier modelo de hoja de cálculo".
Asegúrese de que las entradas y la celda de resultado estén claramente etiquetadas. La herramienta necesita saber qué números son suposiciones.
Paso 2: Elija la vista de sensibilidad en lenguaje natural
Describa la prueba que desea. Por ejemplo: "Muestra un gráfico de tornado para la ganancia con cada entrada movida un 10% arriba y abajo. Agrega una tabla de doble entrada para el precio y las unidades vendidas".
La página de la herramienta enumera cuatro vistas: "tornado, doble entrada, Monte Carlo o equilibrio". Describe la salida del tornado como un gráfico que "clasifica las entradas que más mueven la respuesta". También ofrece una "Tabla de sensibilidad sobre los dos supuestos que importan".
Paso 3: Verifique los resultados y exporte el análisis
Compare uno o dos resultados con su propia tabla de datos antes de compartirlos. El caso base debe coincidir exactamente con su modelo.
Luego exporte. La página de la herramienta enumera un "libro de Excel, PDF con marca o enlace de lienzo". Para una presentación, la herramienta Excel to PPT tool convierte el mismo archivo en diapositivas. La página de AI Financial Modeling cubre la construcción del modelo en sí.
Cómo leer y presentar los resultados
Un análisis de sensibilidad solo es útil si las personas actúan en consecuencia. Estos hábitos ayudan.
Comience con la clasificación. Abra con el gráfico de tornado. Muestra en una sola imagen qué supuestos impulsan el resultado.
Traduzca los rangos en decisiones. "La ganancia cae a $92,000 si el precio baja un 10%" es un hecho. "Deberíamos probar el precio antes de firmar el contrato de arrendamiento" es una decisión.
Muestre el punto de equilibrio. Combine el análisis con el valor donde el resultado llega a cero. Para Northgate Cycles, el equilibrio es de unas 1,530 unidades al precio y costos base. Nuestra guía sobre análisis de equilibrio en Excel cubre ese cálculo.
Use rangos realistas. Un movimiento del 10% es adecuado para entradas estables. El volumen de un nuevo producto puede necesitar un rango mucho más amplio.
Indique qué se mantuvo constante. Cada prueba individual mantiene las otras entradas en la base. Dígalo en la diapositiva o en una nota al pie. Así, los lectores sabrán que un cambio de precio del 10% supone el mismo volumen, costos y gasto fijo. Esta línea evita la mayoría de las preguntas de seguimiento sobre el método.
Vuelva a revisar después de que lleguen los resultados. Una vez que lleguen los números reales, compárelos con sus rangos. Un informe de presupuesto vs. real es un seguimiento natural.
Errores comunes en el análisis de sensibilidad
Estos errores son comunes y fáciles de evitar.
Números fijos en las fórmulas. Si una fórmula contiene 850 en lugar de B2, la tabla de datos no puede cambiarlo. Mantenga cada entrada en una celda.
Probar demasiadas entradas a la vez. Una tabla de datos maneja una o dos. Use el Administrador de escenarios o un gráfico de tornado para más.
Usar el mismo rango para cada entrada. El precio y el volumen rara vez conllevan la misma incertidumbre. Elija rangos que reflejen lo que realmente sabe.
Ignorar los vínculos entre las entradas. Un precio más bajo puede aumentar el volumen. Una prueba individual pasa por alto eso, así que agregue un escenario que mueva ambos.
Resúmenes de escenarios desactualizados. Los informes de escenarios no se actualizan solos. Vuelva a construirlos después de cualquier cambio.
Olvidar el caso base. Cada tabla debe mostrar el resultado base, para que los lectores puedan ver la magnitud de cada cambio.
Cuando esté listo para poner a prueba su próximo modelo, pruebe Powerdrill Bloom con el libro de trabajo que usa para la planificación.
Preguntas frecuentes
¿Qué es un análisis de sensibilidad en Excel?
Es una forma de probar cómo cambia un resultado cuando cambian sus entradas. En Excel, se usan las herramientas de Análisis de hipótesis, principalmente las tablas de datos y el Administrador de escenarios, para intercambiar nuevos valores de entrada y registrar los resultados.
¿Cómo hago un análisis de sensibilidad de una variable en Excel?
Enumere los valores de entrada en una columna. Coloque una fórmula que haga referencia al resultado una fila arriba y una columna a la derecha. Seleccione el rango, elija Análisis de hipótesis > Tabla de datos e ingrese la celda de entrada como la celda de entrada de columna.
¿Cuál es la diferencia entre una tabla de datos y el Administrador de escenarios?
Una tabla de datos prueba una o dos variables a través de muchos valores y muestra todos los resultados en una cuadrícula. El Administrador de escenarios guarda conjuntos completos de entradas, de hasta 32 valores cambiantes cada uno, y los intercambia uno a la vez.
¿Cómo hago un gráfico de tornado en Excel?
Mueva cada entrada hacia arriba y hacia abajo en la misma cantidad, una a la vez, y registre el resultado. Ordene las entradas por el tamaño del rango, luego grafique el cambio de cada entrada desde el resultado base como barras horizontales.
¿Puede la AI hacer un análisis de sensibilidad?
Sí. Una herramienta de AI puede leer su modelo de hoja de cálculo, ejecutar pruebas de tornado y de doble entrada, y exportar los resultados como un libro de trabajo o informe. Aún así, debe verificar el caso base y elegir rangos realistas.
Fuentes: Soporte de Microsoft, Introducción al análisis de hipótesis · Soporte de Microsoft, Calcular varios resultados mediante una tabla de datos · Soporte de Microsoft, Cambiar entre varios conjuntos de valores mediante escenarios.