Usar Solver para determinar la mezcla de productos óptima

¿Cómo puedo determinar la mezcla de productos mensuales que maximiza la rentabilidad?

Las empresas a menudo necesitan determinar la cantidad de cada producto que debe producir mensualmente. En su forma más simple, el problema de mezcla de productos implica cómo determinar la cantidad de cada producto que se debe producir durante un mes para maximizar los beneficios. Generalmente, la mezcla de productos debe cumplir con las siguientes restricciones:

• La mezcla de productos no puede usar más recursos que los disponibles.

• Existe una demanda limitada por cada producto. No podemos producir más de un producto durante un mes en el que exige la demanda, ya que el exceso de producción se desperdicia (por ejemplo, un medicamento perecedero).

Ahora, vamos a resolver el siguiente ejemplo del problema de mezcla de productos. Puede encontrar la solución a este problema en el Prodmix.xlsx de archivo, que se muestra en la figura 27-1.

Supongamos que trabajamos para una empresa farmacéutica que genera seis productos diferentes en su planta. La producción de cada producto requiere mano de obra y materias primas. La fila 4 de la figura 27-1 muestra las horas de trabajo necesarias para producir una libra de cada producto y la fila 5 muestra los libras de materia prima necesarios para producir una libra de cada producto. Por ejemplo, si se produce una libra del producto 1, se necesitan seis horas de trabajo y 3,2 libras de materia prima. Para cada fármaco, el precio por libra se indica en la fila 6, el coste unitario por libra, en la fila 7, y la contribución de beneficios por libra se indica en la fila 9. Por ejemplo, producto 2 vende por $11,00 por libra, se produce un coste unitario de $5,70 por libra y se aporta $5,30 ganancias por libra. La demanda por mes de cada fármaco se indica en la fila 8. Por ejemplo, la demanda del producto 3 es 1041 libras. Este mes, se encuentran disponibles 4500 horas de mano de obra y 1600 libras de materia prima. ¿Cómo puede esta empresa maximizar su rentabilidad mensual?

Si no sabíamos que nada acerca de Excel Solver, podría atacar este problema creando una hoja de cálculo para realizar un seguimiento de las ganancias y el uso de los recursos asociados con la mezcla de productos. Luego, usaríamos la prueba y el error para variar la mezcla de productos con el fin de optimizar las ganancias sin usar más mano de obra o materias primas que las disponibles, y sin producir ningún fármaco en exceso de demanda. Solo usamos Solver en este proceso en el escenario de prueba y error. Esencialmente, Solver es un motor de optimización que realiza la búsqueda de prueba y error de forma perfecta.

Una clave para resolver el problema con la mezcla de productos es calcular de forma eficaz el uso de recursos y las ganancias asociadas a una mezcla de productos determinada. Una herramienta importante que podemos usar para hacer este cálculo es la función SUMAPRODUCTO. La función SUMAPRODUCTO multiplica los valores correspondientes de los rangos de celdas y devuelve la suma de esos valores. Cada rango de celdas que se usa en una evaluación de SUMAPRODUCTO debe tener las mismas dimensiones, lo que implica que puede utilizar SUMAPRODUCTO con dos filas o dos columnas, pero no con una columna y una fila.

Como ejemplo de cómo podemos usar la función SUMAPRODUCTO en nuestro ejemplo de mezcla de productos, vamos a intentar calcular nuestro uso de recursos. Nuestro uso de mano de obra es calculado por

(Mano de obra usada por libra del fármaco 1) * (libras del fármaco 1 producidas) +
(Mano de obra usada por libra del fármaco 2) * (fármaco 2 libras producidas) +...
(Mano de obra usada por libra del fármaco 6) * (libras del fármaco 6 producidas)

Podríamos calcular el uso de la mano de obra de forma más tediosa como D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * I4. Del mismo modo, el uso de materias primas se podría calcular como D2 * D5 + E2*E5 + F2 * F5 + G2 * G5 + H2 * i5. Sin embargo, introducir estas fórmulas en una hoja de cálculo para seis productos lleva mucho tiempo. Imagínese cuánto tomaría si estuviera trabajando con una empresa que ha producido, por ejemplo, productos de 50 en su planta. Una forma mucho más fácil de calcular la mano de obra y el uso de materias primas es copiar de D14 a D15 la fórmula SUMAPRODUCTO ($D $2: $I $2, D4: I4). Esta fórmula calcula D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * I4 (que es nuestro uso de mano de obra) pero es mucho más fácil de escribir. Observe que uso el signo $ con el rango D2: I2 para que cuando copie la fórmula siga capturando la combinación de productos de la fila 2. La fórmula de la celda D15 calcula el uso de materias primas.

De manera similar, nuestro beneficio viene determinado por

(Bfº bruto 1 por libra) * (libras del fármaco 1 producido) +
(Beneficio del fármaco 2 por libra) * (libras del fármaco 2 producidas) +..
. (Beneficio del fármaco 6 por libra) * (libras del fármaco 6 producidas)

Las ganancias se calculan fácilmente en la celda D12 con la fórmula SUMAPRODUCTO (D9: i9, $D $2: $I $2).

Ahora podemos identificar los tres componentes de nuestro modelo de Solver de mezcla de productos.

• Celda objetivo. Nuestro objetivo es maximizar el beneficio (calculado en la celda D12).

• Celdas cambiantes. El número de libras producidas de cada producto (enumeradas en el rango de celdas D2: I2)

• Restricciones. Tenemos las siguientes restricciones:

  • No use más mano de obra ni materia prima que la disponible. Es decir, los valores de las celdas D14: D15 (los recursos utilizados) deben ser menores o iguales que los valores de las celdas F14: F15 (los recursos disponibles).

  • No produzca más de un medicamento que el que se encuentra en la demanda. Es decir, los valores de las celdas D2: I2 (libras producidas de cada fármaco) deben ser menores o iguales que la demanda de cada fármaco (que se muestran en las celdas D8: i8).

  • No podemos producir un monto negativo de ningún fármaco.

Le mostraré cómo introducir la celda objetivo, las celdas cambiantes y las restricciones en Solver. Entonces todo lo que debe hacer es hacer clic en el botón resolver para encontrar una mezcla de productos con ganancias maximizadas.

Para empezar, haga clic en la pestaña datos y, en el grupo análisis, haga clic en Solver.

Aparecerá el cuadro de diálogo parámetros de Solver, como se muestra en la figura 27-2.

Haga clic en el cuadro establecer celda objetivo y, a continuación, seleccione nuestra celda de ganancias (celda D12). Haga clic en el cuadro cambiando las celdas y, a continuación, seleccione el rango D2: I2, que contiene las libras producidas de cada fármaco. El cuadro de diálogo debería tener ahora la figura 27-3.

Ya estamos listos para agregar restricciones al modelo. Haga clic en el botón Agregar. Verá el cuadro de diálogo Agregar restricción, que se muestra en la figura 27-4.

Para agregar las restricciones de uso de recursos, haga clic en el cuadro referencia de celda y, a continuación, seleccione el rango D14: D15. Seleccione <= de la lista central. Haga clic en el cuadro restricción y, a continuación, seleccione el rango de celdas F14: F15. El cuadro de diálogo Agregar restricción tendrá ahora un aspecto similar al de la figura 27-5.

Ahora ya hemos asegurado que, cuando Solver Pruebe diferentes valores para las celdas cambiantes, solo se tendrán en cuenta las combinaciones que cumplan los dos caracteres D14<= F14 (el trabajo usado es menor o igual que el trabajo disponible) y D15<= F15 (la materia prima usada es menor o igual que la materia prima disponible). Haga clic en Agregar para especificar las restricciones de demanda. Rellene el cuadro de diálogo Agregar restricción como se muestra en la ilustración 27-6.

Agregar estas restricciones garantiza que cuando Solver Pruebe distintas combinaciones para los valores de celda cambiantes, solo se tendrán en cuenta las combinaciones que cumplan los siguientes parámetros:

• D2<= D8 (la cantidad fabricada del fármaco 1 es menor o igual que la demanda del fármaco 1)

• E2<= E8 (la cantidad producida del fármaco 2 es menor o igual que la demanda del fármaco 2)

• F2<= F8 (la cantidad fabricada del fármaco 3 es menor o igual que la demanda del fármaco 3)

• G2<= G8 (la cantidad producida del fármaco 4 es menor o igual que la demanda del fármaco 4)

• H2<= H8 (la cantidad producida del fármaco 5 es menor o igual que la demanda del fármaco 5)

• I2<= i8 (la cantidad fabricada del fármaco 6 es menor o igual que la demanda del fármaco 6).

Haga clic en aceptar en el cuadro de diálogo Agregar restricción. La ventana de Solver debería tener un aspecto similar al de la figura 27-7.

Especificamos que la restricción de celdas cambiantes no debe ser negativa en el cuadro de diálogo Opciones de Solver. Haga clic en el botón opciones en el cuadro de diálogo parámetros de Solver. Active las casillas modelo lineal y suponer no negativo, como se muestra en la figura 27-8 de la página siguiente. Haga clic en Aceptar.

Al activar la casilla no negativo se garantiza que Solver solo tiene en cuenta las combinaciones de celdas cambiantes en las que cada celda cambia supone un valor no negativo. Se ha activado la casilla modelo lineal porque el problema de mezcla de productos es un tipo especial de problema de Solver denominado modelo lineal. Esencialmente, un modelo de Solver es lineal en las siguientes condiciones:

• La celda objetivo se calcula al sumar las condiciones del formulario (celda cambiante) * (constante).

• Cada restricción cumple los requisitos del modelo lineal. Esto significa que cada restricción se evalúa mediante la suma de las condiciones del formulario (celda cambiante) * (constante) y la comparación de las sumas con una constante.

¿Por qué este problema de Solver es lineal? Nuestra celda objetivo (bfº) se calcula como

(Bfº bruto 1 por libra) * (libras del fármaco 1 producido) +
(Beneficio del fármaco 2 por libra) * (libras del fármaco 2 producidas) +..
. (Beneficio del fármaco 6 por libra) * (libras del fármaco 6 producidas)

Este cálculo sigue un patrón en el que se deriva el valor de la celda objetivo al sumar las condiciones del formulario (celda cambiante) * (constante).

La restricción de mano de obra se evalúa comparando el valor derivado de (mano de obra usada por libra del fármaco 1) * (libras del fármaco 1 producidas) + (mano de obra usada por libra del fármaco 2) * (fármaco 2 libras producidas) +... (Mano de obra ) Ed por libra del fármaco 6) * (libras del fármaco 6 producidas) para la mano de obra disponible.

Por consiguiente, la restricción de mano de obra se evalúa mediante la suma de las condiciones del formulario (celda cambiante) * (constante) y la comparación de las sumas con una constante. Tanto la restricción de mano de obra como la restricción de materia prima cumplen con los requisitos del modelo lineal.

Nuestras restricciones de demanda tienen el formato

(Fármaco 1 producido) <= (demanda del fármaco 1)
(Fármaco 2 producido) <= (demanda de drogas 2)
§
(Fármaco 6 producido) <= (demanda de fármaco 6)

Cada restricción de demanda también cumple los requisitos del modelo lineal, ya que cada una de ellas se evalúa mediante la suma de las condiciones del formulario (celda cambiante) * (constante) y la comparación de las sumas con una constante.

Tras haber demostrado que nuestro modelo de mezcla de productos es un modelo lineal, ¿por qué debería interesarle?

• Si un modelo de Solver es lineal y seleccionamos adoptar modelo lineal, se garantiza que Solver encontrará la solución óptima al modelo de Solver. Si un modelo de Solver no es lineal, Solver puede encontrar o no la solución óptima.

• Si un modelo de Solver es lineal y seleccionamos adoptar un modelo lineal, Solver usa un algoritmo muy eficaz (el método simple) para encontrar la solución óptima del modelo. Si un modelo de Solver es lineal y no se selecciona adoptar un modelo lineal, Solver usa un algoritmo muy poco eficiente (el método GRG2) y podría tener dificultades para encontrar la solución óptima del modelo.

Después de hacer clic en aceptar en el cuadro de diálogo Opciones de Solver, se volverá al cuadro de diálogo Solver principal, que se muestra en la ilustración 27-7. Cuando hacemos clic en resolver, Solver calcula una solución óptima (si existe alguna) para nuestro modelo de mezcla de productos. Como mencionamos en el capítulo 26, una solución óptima para el modelo de mezcla de productos sería un conjunto de valores de celdas cambiantes (libras producidas de cada fármaco) que maximizan las ganancias sobre el conjunto de soluciones factibles. Una vez más, una solución factible es un conjunto de valores de celdas cambiantes que cumplen con todas las restricciones. Los valores de las celdas cambiantes mostrados en la figura 27-9 son una solución factible, ya que todos los niveles de producción no son negativos, los niveles de producción no superan la demanda y el uso de recursos no excede los recursos disponibles.

Los cambios en los valores de las celdas que se muestran en la figura 27-10 de la página siguiente representan una solución inviable por los siguientes motivos:

• Generamos más el fármaco 5 que la demanda.

• Usamos más mano de obra de lo que está disponible.

• Usamos más materias primas de las que están disponibles.

Después de hacer clic en resolver, Solver encuentra rápidamente la solución óptima que se muestra en la ilustración 27-11. Debe seleccionar mantener la solución de Solver para conservar los valores de solución óptimos en la hoja de cálculo.

Nuestra empresa de medicamentos puede maximizar sus beneficios mensuales a un nivel de $6.625,20 generando 596,67 libras del fármaco 4 1084, los libras del fármaco 5 y ninguno de los otros fármacos. No podemos determinar si podemos alcanzar el beneficio máximo de $6.625,20 de otra manera. Todo lo que podemos asegurar es que con nuestros recursos y demanda limitados, no hay ninguna forma de hacer más de $6.627,20 este mes.