1. Funciones y herramientas de Excel que utilizaremos
Herramienta de análisis de ¿Qué sucede si… que resuelve problemas de optimización (maximizar, minimizar o igualar una celda objetivo) cambiando celdas variables sujetas a restricciones. Se activa en Archivo → Opciones → Complementos → Solver.
Multiplica los componentes correspondientes de dos matrices y suma los productos. En programación lineal es ideal para calcular la función objetivo (utilidad total) y el consumo de recursos (horas totales) sin escribir fórmulas largas.
Ejemplo: =SUMAPRODUCTO(B2:C2;B3:C3) multiplica B2·B3 + C2·C3.
Captura errores y devuelve un mensaje alternativo. Útil cuando las celdas de decisión están vacías antes de ejecutar Solver, evitando que #¡VALOR! o #¡DIV/0! aparezcan en los informes.
Devuelven el valor máximo o mínimo de un rango. Los usaremos para identificar, tras la optimización, cuál restricción está saturada (cuello de botella) y cuál tiene holgura.
2. Datos de ejemplo: Planteamiento del problema
La siguiente tabla resume los parámetros del modelo. En Excel la escribiremos en el rango B2:D6 para tener los coeficientes a la vista.
| B | C | D | |
|---|---|---|---|
| 1 | Parámetro | Producto A | Producto B |
| 2 | Utilidad ($/unidad) | 145 | 75 |
| 3 | Horas máquina/unidad | 5 | 3 |
| 4 | Demanda máxima (unid.) | 1,200 | 1,500 |
| 5 | Cantidad a producir | ? | ? |
| 6 | Producción mínima total | 1,850 unidades | |
| 7 | Horas disponibles | 9,000 horas | |
Convención de celdas: Las cantidades a producir (variables de decisión) irán en C5 (Producto A) y D5 (Producto B). Inicialmente puedes dejarlas en cero o escribir valores tentativos.
3. Desarrollo paso a paso
Multiplicamos la cantidad de cada producto por su utilidad unitaria y sumamos. Usamos SUMAPRODUCTO con los rangos C2:D2 (utilidades) y C5:D5 (cantidades).
Resultado antes de optimizar (con C5=D5=0): $0
De forma análoga, multiplicamos las cantidades por las horas unitarias para conocer el consumo de capacidad.
Resultado antes de optimizar: 0 h
Sumamos las unidades de ambos productos para verificar la restricción de producción mínima.
Resultado antes de optimizar: 0 unid.
Ve a la pestaña Datos y haz clic en Solver. Completa el cuadro de diálogo con los siguientes valores:
- Objetivo: $C$8 (Utilidad total)
- Para: Max
- Cambiando las celdas de variables: $C$5:$D$5
Luego presiona Agregar e introduce las restricciones una por una:
| Restricción | Celda de Excel | Operador | Límite | |
|---|---|---|---|---|
| 1 | Capacidad horaria | $C$9 | <= | $C$7 |
| 2 | Producción mínima | $C$10 | >= | $C$6 |
| 3 | Máximo Producto A | $C$5 | <= | $C$4 |
| 4 | Máximo Producto B | $D$5 | <= | $D$4 |
| 5 | No negatividad | $C$5:$D$5 | >= | 0 |
Marca la casilla «Hacer que las variables sin restricciones sean no negativas» o agrégala manualmente como se muestra en la fila 5.
Presiona Resolver. Excel calculará la combinación óptima.
Tras ejecutar Solver, la hoja de cálculo muestra la solución óptima:
| B | C | D | |
|---|---|---|---|
| 1 | Parámetro | Producto A | Producto B |
| 2 | Utilidad ($/unidad) | 145 | 75 |
| 3 | Horas máquina/unidad | 5 | 3 |
| 4 | Demanda máxima (unid.) | 1,200 | 1,500 |
| 5 | Cantidad óptima | 1,200 | 1,000 |
| 6 | Producción mínima total | 1,850 unidades | |
| 7 | Horas disponibles | 9,000 horas | |
| 8 | UTILIDAD TOTAL | $249,000 | |
| 9 | Horas consumidas | 9,000 h | |
| 10 | Producción total | 2,200 unid. | |
Interpretación: Para maximizar la utilidad se deben fabricar 1,200 unidades de A (tope de demanda) y 1,000 unidades de B. La utilidad máxima es $249,000. Se saturan las 9,000 horas de capacidad y se supera ampliamente el mínimo de 1,850 unidades.
Creamos una tabla auxiliar para ver qué tan «ajustada» está cada restricción. Esto se hace restando el consumo del límite.
| B | C | D | E | |
|---|---|---|---|---|
| 12 | Restricción | Límite | Consumo | Holgura |
| 13 | Horas máquina | 9,000 | 9,000 | 0 |
| 14 | Producción mínima | 1,850 | 2,200 | 350 |
| 15 | Máximo Producto A | 1,200 | 1,200 | 0 |
| 16 | Máximo Producto B | 1,500 | 1,000 | 500 |
Fórmula de holgura en E13 (arrastrar hasta E16):
La holgura 0 indica que la restricción está saturada (cuello de botella). Las restricciones de horas y demanda máxima de A son activas.
4. Variantes del ejercicio
Variante A: Umbral dinámico de producción mínima con SI.ERROR
Supón que el contrato puede fluctuar y la producción mínima se introduce en una celda de entrada C18. Queremos que el informe no se rompa si alguien borra el valor o escribe texto.
Tabla de validación dinámica:
| B | C | |
|---|---|---|
| 18 | Producción mínima (input) | 1,850 |
| 19 | ¿Cumple restricción? | VERDADERO |
| 20 | Unidades faltantes | 0 |
Fórmula para unidades faltantes en C20:
Si cambias el valor de C18 a 2,500, la celda C20 mostrará 300 unidades faltantes, indicando que la solución actual ya no es viable y debes volver a ejecutar Solver.
Variante B: Cambio de condiciones — precio del Producto B sube a $95
El departamento de ventas negocia un aumento de precio para el Producto B, elevando su utilidad a $95 por unidad. Actualizamos D2 y volvemos a correr Solver.
| B | C | D | |
|---|---|---|---|
| 1 | Parámetro | Producto A | Producto B |
| 2 | Utilidad ($/unidad) | 145 | 95 |
| 5 | Cantidad óptima (nueva) | 1,200 | 1,000 |
| 8 | UTILIDAD TOTAL | $269,000 | |
En este caso particular la mezcla óptima no cambia porque la restricción de horas sigue siendo el cuello de botella y la demanda máxima de A ya está saturada. Sin embargo, la utilidad total sube a $269,000. Si la utilidad de B subiera por encima de $145, Solver recomendaría invertir la prioridad.
5. Resumen visual del modelo final
| B | C | D | |
|---|---|---|---|
| 1 | Parámetro | Producto A | Producto B |
| 2 | Utilidad ($/unidad) | 145 | 75 |
| 3 | Horas máquina/unidad | 5 | 3 |
| 4 | Demanda máxima (unid.) | 1,200 | 1,500 |
| 5 | Cantidad óptima | 1,200 | 1,000 |
| 6 | Producción mínima total | 1,850 unidades | |
| 7 | Horas disponibles | 9,000 horas | |
| 8 | UTILIDAD TOTAL | $249,000 | |
| 9 | Horas consumidas | 9,000 h | |
| 10 | Producción total | 2,200 unid. | |
| 12 | Restricción | Límite | Holgura |
| 13 | Horas máquina | 9,000 | 0 |
| 14 | Producción mínima | 1,850 | 350 |
| 15 | Máximo Producto A | 1,200 | 0 |
| 16 | Máximo Producto B | 1,500 | 500 |
6. Ejercicio propuesto
Práctica: Mix de producción de muebles de oficina
Una carpintería fabrica dos modelos de escritorios:
- Escritorio Ejecutivo (E): utilidad de $220 por unidad, consume 8 horas de taller.
- Escritorio Estándar (S): utilidad de $130 por unidad, consume 4 horas de taller.
La planta dispone de 6,400 horas de taller por mes. Por acuerdo comercial debe entregar al menos 900 escritorios en total. El mercado no absorbe más de 600 unidades del modelo Ejecutivo ni más de 800 del Estándar.
Instrucciones:
- Construye la tabla de parámetros en Excel (rango B2:D6).
- Usa SUMAPRODUCTO para calcular la utilidad total y las horas consumidas en celdas separadas.
- Configura Solver para maximizar la utilidad total respetando todas las restricciones.
- Identifica cuáles restricciones quedan saturadas (holgura = 0) y cuáles tienen holgura positiva.
- Agrega una celda de entrada dinámica para la producción mínima y usa SI.ERROR para validar si la solución cumple el nuevo umbral sin romper la hoja.
- Responde: ¿Qué pasaría si la utilidad del modelo Estándar subiera a $160? ¿Cambia el mix óptimo? Justifica con los resultados de Solver.
Pregunta de reflexión: Si la gerencia decide aumentar la capacidad horaria a 7,200 horas contratando un turno extra, ¿cuál sería el nuevo mix óptimo y cuánto aumentaría la utilidad? Compara la utilidad marginal de cada hora adicional.
Deja una respuesta