En este artículo se describe el uso de Solver, un programa de complemento de Microsoft Excel que puede usar para análisis de hipótesis para determinar una 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.

Imagen del libro

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.

Nota:  Tal como se explica en el capítulo 26, "una introducción a la optimización con Excel Solver", Solver se instala haciendo clic en el botón de Microsoft Office, a continuación, opciones de Excel y, después, complementos. En la lista administrar, haga clic en complementos de Excel, active la casilla complemento Solver y, a continuación, haga clic en Aceptar.

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

Imagen del libro

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.

Imagen del libro

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.

Imagen del libro

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.

Imagen del libro

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.

Imagen del libro

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.

Imagen del libro

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.

Imagen del libro

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.

Imagen del libro

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.

Imagen del libro

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.

Imagen del libro

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.

Supongamos que debe cumplirse la demanda de cada producto. (Consulte la hoja de cálculo ninguna solución factible en el Prodmix.xlsx de archivos). Después, tenemos que cambiar nuestras restricciones de demanda de D2: i2<= D8: i8 a D2: I2>= D8: i8. Para ello, abra Solver, seleccione la restricción D2: I2<= D8: i8 y, a continuación, haga clic en cambiar. Aparece el cuadro de diálogo cambiar restricción, que se muestra en la figura 27-12.

Imagen del libro

Seleccione >= y, a continuación, haga clic en Aceptar. Ahora hemos asegurado que Solver puede cambiar solo los valores de celda que cumplan todas las demandas. Al hacer clic en resolver, verá el mensaje "Solver no pudo encontrar una solución factible". Este mensaje no significa que hemos cometido un error en nuestro modelo, sino más bien que con nuestros recursos limitados, no podemos satisfacer la demanda de todos los productos. Solver simplemente nos está indicando que si queremos satisfacer la demanda de cada producto, necesitamos agregar más mano de obra, más materias primas o más de ambos.

Veamos qué sucede si permitimos una demanda ilimitada de cada producto y permitimos que se produzcan cantidades negativas de cada fármaco. (Puede ver este problema de Solver en la hoja de cálculo los valores no convergen en el Prodmix.xlsx de archivo). Para encontrar la solución óptima para esta situación, abra Solver, haga clic en el botón Opciones y desactive la casilla no negativo. En el cuadro de diálogo parámetros de Solver, seleccione la restricción de demanda D2: I2<= D8: i8 y, a continuación, haga clic en eliminar para quitar la restricción. Al hacer clic en resolver, Solver devuelve el mensaje "establecer que los valores de la celda no convergen". Este mensaje significa que si la celda objetivo debe maximizarse (como en nuestro ejemplo), hay soluciones viables con valores de celda objetivo arbitrariamente grandes. (Si la celda objetivo va a estar minimizada, el mensaje "establecer que los valores de las celdas no converge" significa que hay soluciones viables con valores de celda objetivo arbitrariamente pequeños.) En nuestra situación, al permitir la producción negativa de un fármaco, aplicamos recursos "crear" que pueden usarse para producir grandes cantidades arbitrarias de otros fármacos. Dada nuestra demanda ilimitada, esto nos permite hacer ganancias ilimitadas. En realidad, no podemos hacer una cantidad infinita de dinero. En Resumen, si ve "establecer valores no convergen", el modelo tiene un error.

  1. Supongamos que nuestra empresa de medicamentos puede comprar hasta 500 horas de mano de obra a $1 más por hora que los costes de mano de obra actuales. ¿Cómo podemos maximizar el beneficio?

  2. En una planta de fabricación de chip, cuatro técnicos (A, B, C y D) generan tres productos (productos 1, 2 y 3). Este mes, el fabricante del chip puede vender 80 unidades del producto 1, 50 unidades del producto 2 y en la mayoría de las unidades 50 del producto 3. El técnico A puede hacer solo los productos 1 y 3. El técnico B solo puede hacer los productos 1 y 2. El técnico C solo puede hacer el producto 3. El técnico D solo puede hacer el producto 2. Para cada unidad fabricada, los productos contribuyen con las siguientes ganancias: producto 1, $6; Producto 2, $7; y producto 3, $10. El tiempo (en horas) que cada técnico necesita para fabricar un producto es el siguiente:

    Producto

    Técnico A

    Técnico B

    Técnico C

    Técnico D

    1

    2

    2,5

    No se puede hacer

    No se puede hacer

    2

    No se puede hacer

    3

    No se puede hacer

    3,5

    3

    3

    No se puede hacer

    4

    No se puede hacer

  3. Cada técnico puede funcionar hasta 120 horas por mes. ¿Cómo puede maximizar el fabricante del chip? Supongamos que se puede producir un número fraccionario de unidades.

  4. Una planta de fabricación de equipos informáticos produce ratones, teclados y joysticks con videojuegos. En la siguiente tabla se proporcionan las ganancias por unidad, el uso de mano de obra, la demanda mensual y el uso de tiempo de máquina por unidad:

    Ratón

    Teclados

    Joysticks

    Ganancias/unidad

    $8

    $11

    $9

    Uso de mano de obra/unidad

    .2 horas

    .3 hora

    .24 horas

    Hora/unidad de máquina

    .04 hora

    .055 hora

    .04 hora

    Demanda mensual

    15 000

    27.000

    11.000

  5. Cada mes, hay disponible un total de 13.000 horas de mano de obra y 3000 horas de tiempo de máquina. ¿Cómo puede maximizar el fabricante su contribución mensual de beneficios por la planta?

  6. Solucione el ejemplo de medicamentos suponiendo que debe cumplirse una demanda mínima de 200 unidades para cada fármaco.

  7. Jason crea Diamond bracelets, necklaces y mamá. Desea trabajar un máximo de 160 horas por mes. Tiene 800 onzas de diamantes. A continuación se indican el beneficio, el tiempo de trabajo y los onzas de diamantes necesarios para fabricar cada producto. Si la demanda de cada producto es ilimitada, ¿cómo puede Jason sacar el máximo provecho?

    Producto

    Ganancias unitarias

    Horas de mano de obra por unidad

    Onzas de diamantes por unidad

    Bracelet

    300 $

    .35

    1,2

    Necklace

    200 $

    .15

    .75

    Mamá

    100 $

    0,05

    5

¿Necesita más ayuda?

Ampliar sus conocimientos
Explorar los cursos
Obtener nuevas características primero
Unirse a Microsoft Insider

¿Le ha sido útil esta información?

¿Cuál es tu grado de satisfacción con la calidad de la traducción?
¿Qué ha afectado a tu experiencia?

¡Gracias por sus comentarios!

×