Cómo maximizar la utilidad con Solver en una planta de ensamble
En la gestión de operaciones, la toma de decisiones óptima es crucial para la rentabilidad. Este instructivo técnico detalla cómo utilizar la herramienta Solver de Excel para resolver problemas complejos de optimización en un entorno de producción real.
Definición del problema
Imaginemos la planta de ensamblaje «TechBuild S.A.», que produce tres modelos de dispositivos electrónicos: el Modelo A (Gama Alta), el Modelo B (Gama Media) y el Modelo C (Gama Económica). La gerencia necesita determinar la cantidad óptima de cada modelo a producir semanalmente para maximizar la utilidad total, dadas ciertas restricciones operativas.
El reto operativo es el siguiente:
- Restricción de Mano de Obra: Solo hay 1200 horas de ensamblaje disponibles por semana.
- Restricción de Componentes: El suministro de la placa base (componente crítico) es limitado a 400 unidades semanales.
- Demanda Mínima: Se debe producir al menos 50 unidades del Modelo B para cumplir con contratos de servicio.
- Objetivo: Maximizar la utilidad total, considerando que cada modelo tiene diferentes márgenes de ganancia y requiere distintos recursos.
Explicación técnica
Para resolver este problema de optimización lineal, utilizaremos la herramienta Solver de Excel. Solver es un complemento que permite encontrar el valor óptimo de una función objetivo (en este caso, la Utilidad Total) ajustando las variables de decisión (las cantidades a producir de A, B y C), mientras se respetan un conjunto de restricciones definidas por los recursos disponibles (horas, componentes) y las políticas de negocio (demanda mínima).
Conceptos clave:
- Función Objetivo:
- Variables de Decisión: Las celdas que Solver modificará (Cantidad de A, Cantidad de B, Cantidad de C).
- Restricciones:
Guía paso a paso
Paso 1: Configuración de los datos iniciales
Primero, debemos estructurar todos los datos del caso práctico en una hoja de cálculo de Excel. Esto incluye los datos de entrada (utilidades, requerimientos de recursos) y las celdas donde se ingresarán las variables de decisión.
Acción: Ingresa los datos en la hoja de cálculo siguiendo la estructura mostrada a continuación.
| Producto | Utilidad Unit. ($) | Horas/Unidad | Placa/Unidad | |
|---|---|---|---|---|
| A | Modelo A | $150 | 4 | 2 |
| B | Modelo B | $100 | 3 | 1 |
| C | Modelo C | $50 | 1 | 1 |
Paso 2: Definición de Variables de Decisión y Función Objetivo
Debemos designar celdas para las cantidades a producir (Variables de Decisión) y luego construir la fórmula de la utilidad total.
Acción: En las celdas D2, D3 y D4, ingresa 0 (o cualquier valor inicial). Estas serán las cantidades a producir. En la celda F2, construye la fórmula de la utilidad total.
| Producto | Utilidad Unit. ($) | Horas/Unidad | Placa/Unidad | Cantidad a Producir (Variable) | Contribución a Utilidad | |
|---|---|---|---|---|---|---|
| 1 | … | … | … | … | D2 (0) | =C2*D2 |
| 2 | … | … | … | … | D3 (0) | =C3*D3 |
| 3 | … | … | … | … | D4 (0) | =C4*D4 |
| 4 | UTILIDAD TOTAL | =SUMA(F2:F4) |
Resultado: La celda F5 (o la celda donde definiste la suma) contendrá la utilidad total inicial, que será $0.
Paso 3: Definición de Restricciones (Recursos)
Ahora, calculamos el consumo total de recursos y definimos los límites.
Acción: En una sección separada, calcula el consumo total de horas y placas. Por ejemplo, en la celda H2, ingresa la fórmula: =SUMA(D2:D4 * C2:C4) (ajustando rangos). Luego, establece las restricciones en las celdas adyacentes (H3 y H4).
| Recurso | Consumo Total (Fórmula) | Límite Disponible |
|---|---|---|
| Horas de Ensamblaje | =SUMA(D2:D4 * 4, D3:D4 * 3, D4:D4 * 1) | 1200 |
| Placas Base | =SUMA(D2:D4 * 2, D3:D4 * 1, D4:D4 * 1) | 400 |
| Demanda Mínima B | D3 | 50 |
Paso 4: Ejecución de Solver
Este es el paso central. Debes activar el complemento Solver si aún no lo tienes (Archivo > Opciones > Complementos > Administar: Complementos de Excel > Ir… y marcar Solver).
Acción: Ve a la pestaña Datos y haz clic en Solver.
- Establecer objetivo: Selecciona la celda de la Utilidad Total (F5). Configúralo como Max (Maximizar).
- Cambiando las celdas de variables: Selecciona el rango de cantidades a producir (D2:D4).
- Añadir restricciones: Haz clic en «Agregar» y define cada límite:
- Consumo de Horas $le 1200$
- Consumo de Placas $le 400$
- Cantidad B $ge 50$
- Cantidad A, B, C $ge 0$ (Restricción de no negatividad, usualmente predeterminada)
- Resolver: Asegúrate de que el método de resolución sea «Simplex LP» (Lineal). Haz clic en Resolver.
Resultado: Solver ajustará automáticamente los valores en D2, D3 y D4 para que la Utilidad Total (F5) sea la más alta posible sin violar ninguna de las restricciones.
Ejercicio propuesto
Una vez que hayas resuelto el caso inicial, introduce una nueva restricción: debido a problemas logísticos, la producción total (A + B + C) no puede exceder las 350 unidades semanales. Vuelve a ejecutar Solver con esta nueva restricción. ¿Cómo cambia la distribución óptima de producción y cuál es la nueva utilidad máxima? Analiza si la restricción de capacidad total es más limitante que la de horas de mano de obra.
Errores habituales
La implementación de Solver puede ser engañosa si no se entienden sus premisas. Aquí se detallan tres errores comunes:
- Error: Olvidar la restricción de no negatividad ($ge 0$).
Solución: Aunque Solver a menudo lo incluye por defecto, siempre debe asegurarse de que las variables de decisión (cantidades) no sean negativas. Si no lo hace, el modelo podría sugerir producir cantidades negativas, lo cual es físicamente imposible.
- Error: Definir mal la función objetivo.
Solución: El objetivo debe ser la celda que contiene la fórmula de la utilidad total, no una de las variables de decisión. Si seleccionas una variable, Solver intentará maximizar *esa* variable, no el resultado financiero.
- Error: Usar el método incorrecto.
Solución: Para problemas de producción con recursos lineales (como este), el método correcto es Simplex LP. Si el problema involucrara funciones cuadráticas (ej. costos de producción que aumentan exponencialmente), se debería usar «GRG Nonlinear».
Deja una respuesta