Análisis de hipótesis en Excel: Goal Seek, Scenario Manager y Data Tables

Le das tres cosas:

• Set cell: la celda que contiene tu fórmula (la salida)
• To value: el resultado objetivo que quieres
• By changing cell: la celda de entrada que Excel puede ajustar

Veámoslo en la práctica. 

Tienes un pequeño negocio de cuadernos. Tu facturación actual es de $25,000, pero tu objetivo es $50,000. El precio está fijado en $25, así que necesitas saber cuántas unidades debes vender. 

Cómo hacerlo:

1. Ve a Datos > What-If Analysis > Goal Seek

2. En Set cell, haz clic en la celda B4 (tu celda con la fórmula de ingresos)

3. En To value, escribe 50000

4. En By changing cell, selecciona B2 (unidades vendidas)

5. Haz clic en OK

Goal Seek ajusta B2 de 1.000 a 2.000 unidades: justo las necesarias para alcanzar $50,000 de ingresos a $25 por unidad.

Usar Goal Seek para lograr un resultado específico. Imagen del autor.

Cómo usar Scenario Manager en Excel

Scenario Manager es una herramienta de análisis de hipótesis que te permite guardar y alternar entre varios conjuntos de valores de entrada en la misma hoja. Cada versión guardada es un escenario. 

Puedes crear tantos como necesites y cambiar entre ellos al instante para comparar resultados. 

Es útil cuando quieres planificar resultados en el mejor, peor y caso esperado sin tener tres archivos distintos. 

Le das tres cosas:

• Scenario name: una etiqueta para cada conjunto de supuestos
• Changing cells: las celdas de entrada que varían entre escenarios
• Values: los números concretos de cada escenario

Crear un escenario

Imagina que estás planificando las ventas trimestrales de tu negocio de cuadernos.

Supón que tu beneficio actual es de $15,000. Quieres modelar tres escenarios: mejor caso, peor caso y caso esperado. 

Cómo crear un escenario:

1. Ve a Datos > What-If Analysis > Scenario Manager

2. En el cuadro de diálogo Scenario Manager, haz clic en Add

3. En Scenario name, escribe Best Case.

4. En Changing cells, selecciona B2:B4.

5. Aparecerá el cuadro de diálogo Scenario Value. En los campos, introduce estos valores:

• B2 = 2000, 

• B3 = $30

• B4 = $10

6. Haz clic en OK. 

Repite estos pasos para los otros dos escenarios con los valores siguientes:

Crea un escenario con tres casos. Imagen del autor.

Para cambiar de escenario, selecciona uno de la lista y haz clic en Show. Excel sustituye los valores al instante y se actualiza tu beneficio.

Haz clic en Show para ver los valores de cada escenario. Imagen del autor.

Editar escenarios

Selecciona cualquier escenario de la lista y haz clic en Edit. Puedes renombrarlo, cambiar qué celdas cubre o actualizar sus valores. No se cambia nada en tu hoja hasta que haces clic en Show

Generar informes de escenarios

Scenario Manager también puede crear un informe que compara todos los escenarios guardados en paralelo. 

Para generar un informe de escenarios:

1. Dentro de Scenario Manager, haz clic en Summary

2. En Scenario Summary, elige Scenario Summary

3. En Result cells, selecciona B7 (tu celda de beneficio)

4. Haz clic en OK

Excel genera en una hoja nueva una tabla resumen con los tres escenarios en paralelo

Genera un informe resumen de escenarios. Imagen del autor.

Cómo usar Data Tables en Excel

Data Tables automatiza cálculos repetidos en un rango de valores de entrada. En lugar de cambiar una entrada a mano, anotar la salida y repetir, defines el rango una vez y Excel completa todos los resultados de golpe.

Le das tres cosas:

• Input range: los valores que quieres probar
• Row/column input cell: la celda en tu fórmula que esos valores reemplazan
• Formula cell: el resultado alrededor del cual se construye tu tabla

Data Tables de una variable

Una Data Table de una variable prueba cómo cambia el resultado de una fórmula al modificar una entrada.

Por ejemplo, tienes una fórmula de cuota de préstamo y quieres ver cómo varía el pago mensual con tipos de interés del 5% al 10%.

Para crear una Data Table de una variable:

1. Prepara el conjunto de datos. Un detalle: en mi dataset, en la celda E1, justo encima del primer tipo —no al lado del encabezado de columna— introduzco =B5. Si E1 queda en blanco, o la referencia de fórmula va a otra celda, la tabla funcionará pero devolverá ceros en lugar de los pagos.

Crea una Data Table de una variable. Imagen del autor.

2. Después, selecciona todo el rango D1:E9 (incluida E1)

3. Ve a Datos > What-If Analysis > Data Table

4. Deja en blanco Row input cell

5. En Column input cell, selecciona B4 (tipo de interés)

6. Haz clic en OK.

Excel rellena la tabla con los resultados calculados para cada valor de entrada. Fíjate en que el pago no sube de forma uniforme. El salto del 3% al 4% es de $49, pero del 9% al 10% es de $61. Las subidas de interés impactan más cuanto más altas son.

Resultados calculados para cada valor de entrada. Imagen del autor.

Data Tables de dos variables

Una Data Table de dos variables prueba cómo cambia el resultado de una fórmula al modificar dos entradas a la vez.

Por ejemplo, un modelo de precios puede depender tanto del precio del producto como de las unidades vendidas. Una Data Table de dos variables puede mostrar el beneficio según distintos precios y volúmenes de ventas en una sola vista.

Primero, prepara el conjunto de datos. 

Observa el dataset que utilizo:

• En la celda D1, introduce =B5. Es la referencia de la fórmula y debe ir en la esquina superior izquierda de la cuadrícula, no al lado de una etiqueta

• A lo largo de la fila 1, desde E1, lista los valores de unidades vendidas a probar: 500, 1000, 1500, 2000

• En la columna D, desde D2, lista los precios a probar: $20, $25, $30, $35

Configura un dataset para una Data Table de dos variables. Imagen del autor.

Para crear una Data Table de dos variables:

1. Selecciona todo el rango D1:H5.

2. Ve a Datos > What-If Analysis > Data Table

3. En Row input cell, selecciona B3 (unidades vendidas)

4. En Column input cell, selecciona B2 (precio por unidad)

5. Haz clic en OK.

Excel completa el beneficio para cada combinación de precio y unidades vendidas

Resultados calculados para los valores de entrada. Imagen del autor.

Las Data Tables de dos variables son especialmente útiles cuando un único supuesto no cuenta toda la historia. Un precio puede parecer atractivo por sí solo, pero el beneficio también depende de cuántas unidades compren realmente los clientes. Esa segunda variable es la que hace que el modelo sea más útil.

Goal Seek vs. Scenario Manager vs. Data Tables

Goal Seek, Scenario Manager y Data Tables forman parte del conjunto de análisis de hipótesis de Excel, pero resuelven problemas distintos. 

Aquí tienes una rápida comparativa entre ellas 

Análisis de hipótesis vs. Solver

El análisis de hipótesis y Solver nos ayudan a explorar distintos resultados en Excel, pero resuelven tipos de problemas diferentes.

• Análisis de hipótesis: pone a prueba supuestos. Cambias una o más entradas y ves cómo afectan al resultado. El objetivo es entender los posibles escenarios, no encontrar el mejor.
• Solver funciona de otra manera. Le marcas una meta, como maximizar el beneficio o minimizar el coste, y busca la combinación de entradas que mejor cumpla ese objetivo. No prueba un supuesto: optimiza el óptimo. 

Por ejemplo, quien solicita un préstamo puede usar Goal Seek para determinar el importe que da una cuota mensual de $500. Pero una empresa industrial podría usar Solver para hallar la combinación de producción que genera el mayor beneficio cumpliendo límites de mano de obra y materiales. 

Nota: Solver no forma parte de las herramientas estándar de análisis de hipótesis. Es un complemento aparte. En la mayoría de versiones de Excel tendrás que activarlo manualmente en Opciones de Excel para que aparezca en la pestaña Datos.

Buenas prácticas de análisis de hipótesis

Estas son algunas buenas prácticas a seguir cuando trabajes con análisis de hipótesis: 

1. Separa los supuestos de las fórmulas

Guarda los valores de entrada en celdas dedicadas y referencia esas celdas en tus fórmulas. Evita meter números fijos directamente en los cálculos. 

Por ejemplo, esta fórmula es difícil de actualizar:

=(50*1000)-10000

Pero esta versión es mucho más fácil de mantener:

=(B2*B3)-B4

Cuando cambian los supuestos, solo actualizas las celdas de entrada en lugar de editar fórmulas por todo el libro.

2. Etiqueta los escenarios con claridad

Los nombres de escenario deben indicar de inmediato qué representan. «Escenario 1», «Escenario 2» y «Escenario 3» no te dirán nada dentro de seis meses. Nómbralos según lo que son, por ejemplo «Mejor caso: crecimiento del 15%». 

3. Valida las entradas

El análisis de hipótesis es tan fiable como las entradas que se prueban. Antes de ejecutar un análisis, comprueba que:

• Los porcentajes están introducidos correctamente
• Las fechas usan un formato coherente
• Las unidades de medida son consistentes
• Los valores de entrada caen dentro de rangos realistas

Una previsión de ingresos basada en un aumento del 500% en ventas puede ser técnicamente correcta pero inútil para decidir.

4. Documenta los supuestos

Un modelo que hoy te parece claro puede no tener sentido para ti —o para otra persona— dentro de tres meses. 

Una nota breve junto a cada supuesto, aunque sea «basado en los datos reales de Q3», evita muchas conjeturas después. Documentarlos ayuda a los demás a entender de dónde salen los números y facilita futuras actualizaciones.

Problemas comunes con el análisis de hipótesis

La mayoría de problemas del análisis de hipótesis provienen del diseño de la hoja, no de las herramientas. Si Goal Seek, Scenario Manager o Data Tables dan resultados inesperados, revisa unas cuantas áreas habituales: suele resolver el problema.

1. Las fórmulas no se actualizan 

Cambias una entrada y no pasa nada. Normalmente significa que el modo de cálculo de Excel se cambió a manual en algún momento. 

Para arreglarlo: ve a Fórmulas > Opciones de cálculo y asegúrate de que esté en Automático. Si ya lo está, pulsa F9 para forzar el recálculo.

2. Referencias de celda incorrectas

Las herramientas de análisis de hipótesis dependen de referencias de celda correctas. Si se seleccionan celdas erróneas, los resultados serán incorrectos aunque Excel complete el cálculo. 

Por ejemplo, Goal Seek puede cambiar la celda de entrada equivocada. Una Data Table puede referenciar mal la celda de entrada de fila o de columna. Scenario Manager puede guardar valores para celdas que no tocan.

Para arreglarlo: revisa tus celdas de entrada antes de ejecutar el análisis, comprueba bien qué está seleccionado en cada cuadro de diálogo y usa etiquetas descriptivas para que quede claro qué representa cada celda. 

3. Malinterpretar las limitaciones de Goal Seek

Muchos usuarios esperan que Goal Seek resuelva problemas con múltiples variables cambiantes. No puede. Goal Seek solo modifica una celda de entrada cada vez para alcanzar un valor objetivo. 

Por ejemplo, Goal Seek puede determinar el volumen de ventas necesario para generar $15,000 de beneficio. No puede ajustar simultáneamente volumen, precio y costes para alcanzar ese objetivo.

Para arreglarlo: usa Goal Seek cuando solo deba cambiar una variable. Usa Scenario Manager o Solver cuando intervengan varias. 

4. Modelos de escenarios demasiado complejos

Scenario Manager permite técnicamente hasta 32 celdas cambiantes por escenario, pero que se pueda no significa que se deba. 

A partir de cuatro o cinco variables, los escenarios son difíciles de comparar y, sobre todo, de explicar a otra persona. Si tu modelo necesita tantas piezas móviles, probablemente toque reestructurarlo en lugar de meterlo todo en un único escenario.

Reflexiones finales 

En toda hoja de cálculo hay supuestos en algún punto. Puede ser una tasa de crecimiento, un precio o un tipo de interés.

El análisis de hipótesis no elimina esos supuestos. Te muestra qué pasa si no se cumplen, que es en lo que se basa gran parte de una buena toma de decisiones.

Si estás empezando, no arranques con un modelo de cinco variables y tres escenarios anidados. Empieza por algo pequeño: una entrada, una salida, un Goal Seek, y entiende cómo se mueven la entrada y el resultado. Lo complejo ya vendrá cuando domines lo básico.