Análisis What-If, Escenarios y Buscar Objetivo

Al finalizar esta lección serás capaz de:

• Utilizar tablas de datos de una y dos variables para análisis de sensibilidad
• Crear y comparar escenarios para planificación estratégica
• Usar Buscar objetivo para encontrar valores que producen un resultado deseado
• Aplicar Solver para optimización con restricciones múltiples

El análisis What-If (¿Qué pasaría si...?) te permite explorar cómo cambiarían los resultados de tus modelos al modificar las variables de entrada. Es la herramienta fundamental para la planificación y toma de decisiones bajo incertidumbre.

En lugar de cambiar manualmente cada variable y anotar el resultado, Excel tiene herramientas que automatizan este proceso.

Ruta: Datos > Análisis de hipótesis

Tres herramientas disponibles:

1. Administrador de escenarios: Guarda y compara conjuntos de variables
2. Buscar objetivo: Encuentra el valor de entrada que produce un resultado deseado
3. Tabla de datos: Muestra resultados para múltiples valores de variables

Tabla de Datos de UNA Variable

Muestra cómo cambia un resultado al variar UNA entrada.

Ejemplo: ¿Cómo cambia la cuota del préstamo según la tasa de interés?

Setup:

• Celda B1: Monto = $1,000,000
• Celda B2: Tasa anual = 45%
• Celda B3: Plazo = 24 meses
• Celda B4: Cuota = =PAGO(B2/12,B3,-B1) → resultado con tasa actual

Para la tabla de datos:

1. En D1 escribí la referencia a la fórmula resultado: =B4
2. En D2:D8 escribí las diferentes tasas a evaluar: 30%, 35%, 40%, 45%, 50%, 55%, 60%
3. Seleccioná el rango D1:E8 (incluye la celda de fórmula y la columna de resultados vacía)
4. Datos > Análisis de hipótesis > Tabla de datos
5. Celda de entrada (columna): B2 (porque los valores están en columna)
6. Aceptar

Resultado:

Instantáneamente ves cómo la cuota varía entre $48K y $66K según la tasa.

Tabla de Datos de DOS Variables

Muestra resultados para combinaciones de DOS variables.

Ejemplo: Cuota según tasa de interés Y plazo

1. En E1 escribí =B4 (referencia a la fórmula de cuota)
2. En F1:J1 escribí los plazos: 12, 18, 24, 36, 48 (fila superior)
3. En E2:E8 escribí las tasas: 30% a 60% (columna izquierda)
4. Seleccioná todo el bloque E1:J8
5. Tabla de datos:
   • Celda de entrada (fila): B3 (plazo)
   • Celda de entrada (columna): B2 (tasa)
6. Aceptar

Resultado: Una matriz donde cada celda muestra la cuota para esa combinación de tasa y plazo. Formato con colores condicionales y tenés un mapa de calor de cuotas.

Crear Escenarios

1. Datos > Análisis de hipótesis > Administrador de escenarios
2. "Agregar" para crear un nuevo escenario
3. Nombre: "Escenario Optimista"
4. Celdas cambiantes: $B$1:$B$5 (las celdas de entrada de tu modelo)
5. Ingresá los valores para el escenario optimista
6. Repetí para "Base" y "Pesimista"

Ejemplo: Planificación de Ventas 2025

Modelo base:

• B1: Precio promedio = $5,000
• B2: Unidades/mes = 200
• B3: Costo unitario = $3,000
• B4: Gastos fijos/mes = $400,000
• B5: Empleados = 5

Mostrar Escenarios

• Hacé clic en el nombre del escenario > "Mostrar" → Excel cambia las celdas a los valores de ese escenario
• "Resumen" → Genera una tabla comparativa con todos los escenarios y las celdas resultado

El resumen muestra:

Este resumen es oro para presentaciones al directorio.

Buscar Objetivo

Encuentra el valor de UNA variable que produce un resultado específico.

Ruta: Datos > Análisis de hipótesis > Buscar objetivo

Ejemplo: ¿Cuántas unidades necesito vender para obtener una utilidad de $200,000?

• Definir celda: B10 (utilidad)
• Con el valor: 200000
• Cambiando celda: B2 (unidades/mes)

Excel itera hasta encontrar que necesitás vender 250 unidades para lograr $200,000 de utilidad.

Otros ejemplos de Buscar Objetivo:

Solver: Optimización con Restricciones

Solver es la versión avanzada de Buscar Objetivo. Puede optimizar (maximizar, minimizar o igualar) un objetivo cambiando MÚLTIPLES variables con RESTRICCIONES.

Activar Solver: Archivo > Opciones > Complementos > Complementos de Excel > Solver

Ruta: Datos > Solver

Ejemplo: Maximizar utilidad con restricciones de capacidad

• Objetivo: Maximizar celda Utilidad (B10)
• Variables: Unidades de cada producto (C2:C5)
• Restricciones:
  • Unidades ≥ 0 (no vender negativo)
  • Unidades ≤ capacidad máxima por producto
  • Total horas de producción ≤ horas disponibles
  • Presupuesto de marketing ≤ $500,000

Solver encuentra la combinación óptima de unidades por producto que maximiza la utilidad respetando todas las restricciones.

1. Tabla de datos da #N/A: El rango seleccionado no incluye la celda de fórmula en la esquina. Revisá la selección.

2. Buscar Objetivo no converge: Si el resultado dice "no se encontró solución", puede que no exista un valor que produzca ese resultado, o la relación no es lineal.

3. Escenarios no cambian las celdas correctas: Verificá que las celdas cambiantes sean las correctas. Si protegiste la hoja, Buscar Objetivo no funciona.

4. Solver no encuentra solución óptima: Probá con diferentes valores iniciales. Solver puede quedar atrapado en óptimos locales.

• Tablas de datos muestran sensibilidad de una o dos variables instantáneamente
• El Administrador de escenarios guarda y compara conjuntos de supuestos
• Buscar Objetivo encuentra la entrada que produce un resultado deseado
• Solver optimiza con múltiples variables y restricciones
• Estas herramientas son esenciales para planificación financiera y toma de decisiones

1. ¿Cuántas variables puede evaluar una tabla de datos de Excel?

   • a) 1
   • b) 2
   • c) 1 o 2 ✓
   • d) Ilimitadas

2. ¿Qué herramienta encontraría cuántas unidades vender para lograr X utilidad?

   • a) Tabla de datos
   • b) Escenarios
   • c) Buscar Objetivo ✓
   • d) Solver

3. ¿Cuál es la diferencia principal entre Buscar Objetivo y Solver?

   • a) Buscar Objetivo es más rápido
   • b) Solver puede cambiar múltiples variables con restricciones ✓
   • c) Buscar Objetivo trabaja con texto
   • d) No hay diferencia

Desafío: Modelo de Planificación Estratégica

1. Construí un modelo financiero simple con: Precio, Unidades, Costo, Gastos Fijos → Utilidad
2. Creá tres escenarios (Pesimista, Base, Optimista)
3. Generá una tabla de datos 2D: Utilidad según Precio (filas) x Unidades (columnas)
4. Usá Buscar Objetivo para encontrar el punto de equilibrio
5. Aplicá formato condicional a la tabla de datos para visualizar zonas de ganancia vs pérdida

Recurso en Video

📹 Análisis What-If en Excel — Escenarios y Buscar Objetivo — Excel y Más

Tutorial práctico que muestra las tres herramientas con un caso de negocio completo.