Análisis de hipótesis en Excel: buscar objetivo, escenarios, tablas de datos y Solver | Curso de Excel gratis: de básico a avanzado

Lo que lograrás en esta lección

Encontrar el valor que necesitas para llegar a una meta con Buscar objetivo.
Comparar escenarios (pesimista, base, optimista) con el Administrador de escenarios.
Ver de un vistazo cómo cambia un resultado con una tabla de datos de dos variables.
Resolver un problema de optimización con Solver.

Un modelo en Excel no sirve solo para calcular lo que pasó: su mayor valor es responder preguntas sobre lo que podría pasar. "¿Cuántas unidades tengo que vender para ganar 5.000?", "¿qué pasa si el precio baja un sol?", "¿qué combinación de productos me deja más ganancia?". Excel agrupa estas herramientas en Datos › Análisis de hipótesis, y añade un complemento muy potente: Solver.

El modelo de partida

Una panadería vende tortas a S/ 12 la unidad; cada una cuesta S/ 5 en insumos (costo variable) y los costos fijos del mes son S/ 8.400. Vende 1.500 unidades al mes. Arma este modelo, con las entradas separadas de los cálculos:

Celda	Concepto	Valor o fórmula
B1	Precio	12
B2	Costo variable unitario	5
B3	Unidades vendidas	1500
B4	Costos fijos	8400
B6	Margen de contribución	`=(B1-B2)*B3` → 10.500
B7	Utilidad	`=B6-B4` → 2.100

La regla de oro de los modelos: ningún número escrito dentro de las fórmulas. Si el precio está en B1 y todas las fórmulas lo leen de ahí, puedes cambiarlo y todo se recalcula. Todas las herramientas de esta lección dependen de esa disciplina.

Buscar objetivo: la meta manda

¿Cuántas tortas hay que vender para ganar S/ 5.000? En lugar de probar números a mano:

Datos › Análisis de hipótesis › Buscar objetivo.
Definir la celda: B7 (la utilidad). Con el valor: 5000. Cambiando la celda: B3 (unidades).
Aceptar. Excel escribe 1.914,29 en B3.

Como no se venden fracciones de torta, la respuesta práctica es 1.915 unidades. Con el mismo método, pidiendo utilidad 0, obtienes el punto de equilibrio: 1.200 unidades. Buscar objetivo funciona con una sola celda cambiante y un solo objetivo; si necesitas más, usarás Solver.

1. ¿Qué necesita Buscar objetivo para funcionar?

Varias celdas cambiantes y restricciones.
Una celda con fórmula, el valor que quieres que tome y una celda de entrada de la que dependa.
Una tabla dinámica con los datos.

Buscar objetivo ajusta una sola celda de entrada hasta que la fórmula elegida dé el valor deseado. La fórmula debe depender, directa o indirectamente, de esa celda.

Tabla de datos: todas las combinaciones de una vez

Quieres ver la utilidad para varios precios (10 a 14) y varios volúmenes (1.200, 1.500 y 1.800). Una tabla de datos de dos variables lo calcula sin copiar el modelo:

En D2 escribe =B7 (la celda resultado). Es la esquina de la tabla.
A su derecha, en E2:G2, las unidades: 1200, 1500, 1800. Debajo, en D3:D7, los precios: 10 a 14.
Selecciona D2:G7 y ve a Datos › Análisis de hipótesis › Tabla de datos.
Celda de entrada (fila): B3 (unidades). Celda de entrada (columna): B1 (precio). Aceptar.

Utilidad	1.200 u.	1.500 u.	1.800 u.
S/ 10	-2.400	-900	600
S/ 11	-1.200	600	2.400
S/ 12	0	2.100	4.200
S/ 13	1.200	3.600	6.000
S/ 14	2.400	5.100	7.800

Aplica un formato condicional de escala de colores (lección 4) y verás al instante la zona de pérdidas. Lectura de negocio: bajar el precio a S/ 10 para vender más solo tiene sentido si las ventas suben a cerca de 1.800; con 1.500 unidades, perderías dinero.

Si el libro se pone lento

Las tablas de datos se recalculan con cada cambio. En modelos grandes, ve a Fórmulas › Opciones para el cálculo › Automático excepto en las tablas de datos y pulsa F9 cuando quieras actualizarlas.

Administrador de escenarios: pesimista, base y optimista

Un escenario es un conjunto de valores para varias entradas a la vez. Por ejemplo:

Escenario	Precio	Costo variable	Unidades	Utilidad
Pesimista	11	5,50	1.300	-1.250
Base	12	5	1.500	2.100
Optimista	12,50	4,80	1.700	4.690

En Datos › Análisis de hipótesis › Administrador de escenarios, pulsa Agregar, ponle nombre, elige las celdas cambiantes (B1:B3) y escribe sus valores. Repite para cada escenario. Con Mostrar los aplicas al modelo, y con Resumen Excel crea una hoja que compara todos los escenarios con la celda de resultado (B7). Es un formato ideal para presentar a un jefe o a un banco: deja claro qué pasa si las cosas salen peor de lo previsto.

2. Quieres comparar en un informe tres combinaciones de precio, costo y volumen. ¿Qué herramienta es la más adecuada?

Buscar objetivo
El Administrador de escenarios, con su resumen
BUSCARV

Los escenarios guardan varios valores de entrada a la vez y su resumen compara los resultados en una sola tabla.

Solver: la mejor decisión con restricciones

Solver busca el valor óptimo (máximo o mínimo) de una celda cambiando varias celdas a la vez y respetando restricciones. Se activa una sola vez en Archivo › Opciones › Complementos › Administrar: Complementos de Excel › Ir… › Solver, y aparece en la pestaña Datos.

Caso: una carpintería fabrica mesas (ganancia S/ 120 cada una) y sillas (S/ 50). Cada mesa necesita 4 horas de carpintería y 2 de acabado; cada silla, 1,5 y 1,5. Este mes hay 400 horas de carpintería y 260 de acabado, y como máximo se venderán 80 mesas. ¿Cuántas de cada una conviene fabricar?

B2  Mesas a fabricar          (celda cambiante)
B3  Sillas a fabricar         (celda cambiante)
B5  Horas de carpintería      =4*B2+1,5*B3
B6  Horas de acabado          =2*B2+1,5*B3
B8  Ganancia total            =120*B2+50*B3

En Datos › Solver:

Establecer objetivo: B8, Para: Máx.
Cambiando las celdas: B2:B3.
Sujeto a las restricciones: B5 <= 400; B6 <= 260; B2 <= 80; B2:B3 = entero.
Marca Convertir variables sin restricciones en no negativas y elige el método Simplex LP (el modelo es lineal).

Solver responde: 70 mesas y 80 sillas, con una ganancia de S/ 12.400, usando exactamente las 400 horas de carpintería y las 260 de acabado. La intuición ("las mesas dejan más, hagamos 80") da 80 mesas y 53 sillas: S/ 12.250. Solver encontró 150 soles más al mes simplemente combinando mejor los recursos.

Elige bien el método

Simplex LP es para modelos lineales (sumas y productos por constantes). Si tu modelo tiene fórmulas no lineales, como precios que dependen del volumen, usa GRG Nonlinear; si tiene SI, BUSCARV u otras funciones con saltos, usa Evolutionary. Un método equivocado puede devolver una solución que no es la mejor.

3. ¿Por qué se agrega la restricción "B2:B3 = entero" en el caso de la carpintería?

Porque Solver solo funciona con enteros.
Porque no se pueden fabricar fracciones de mesa o de silla.
Para que el cálculo sea más rápido.

Sin esa restricción, Solver podría proponer 70,4 mesas. La restricción de entero obliga a soluciones que se pueden ejecutar en la realidad.

Ejercicio práctico: tu modelo "¿y si…?"

Arma el modelo de la panadería y usa Buscar objetivo para hallar el precio que da una utilidad de 4.000 con 1.500 unidades.
Crea los tres escenarios de la tabla y genera el resumen.
Resuelve la carpintería con Solver. Luego cambia la ganancia de la silla a 40 y vuelve a resolver: ¿cambia la mezcla óptima?

Ver solución

1) Precio para utilidad 4.000:
   (P - 5) × 1500 - 8400 = 4000  →  P = 13,27 (Buscar objetivo da 13,2667)

2) Utilidades del resumen: pesimista -1.250 · base 2.100 · optimista 4.690
   Pesimista: (11 - 5,5) × 1300 - 8400 = -1.250
   Optimista: (12,5 - 4,8) × 1700 - 8400 = 4.690

3) Silla a S/ 40: la mezcla óptima pasa a 80 mesas y 53 sillas
   Ganancia: 80 × 120 + 53 × 40 = 11.720
   (70 mesas y 80 sillas darían 11.600)

Al bajar la ganancia de la silla, ahora conviene producir el máximo de mesas y usar las horas sobrantes en sillas. Por eso los modelos se revisan cuando cambian los precios o los costos: la mejor decisión de este mes puede no serlo el próximo.

Resumen

Separa entradas y cálculos: un buen modelo nunca tiene números fijos dentro de las fórmulas.
Buscar objetivo encuentra el valor de una entrada para alcanzar una meta.
La tabla de datos muestra el resultado para muchas combinaciones de una o dos variables.
El Administrador de escenarios compara combinaciones completas y genera un resumen presentable.
Solver optimiza con varias variables y restricciones; elige Simplex LP si el modelo es lineal.

En la próxima lección vas a romper el límite de una sola tabla: con Power Pivot y el modelo de datos relacionarás varias tablas y crearás medidas con DAX dentro de Excel.