Determinar la mezcla de productos óptima con Solver

Se aplica a
Microsoft Office Excel 2003
Microsoft Excel 2000 y 2002

Cubierta del libro Este artículo es una adaptación del artículo Microsoft Excel Data Analysis and Business Modeling (Modelos empresariales y análisis de datos de Microsoft Excel) escrito por Wayne L. Winston.

Este libro de texto se desarrolló a partir de una serie de presentaciones realizadas por Wayne Winston, un conocido estadístico y profesor de administración de empresas especializado en aplicaciones prácticas y creativas de Excel. Así que prepárese: va a tener que estrujar su cerebro.

Visite Microsoft Learning para adquirir este libro.

En este artículo

Archivos de ejemplo    Puede descargar los archivos de ejemplo relacionados con los pasajes del artículo Microsoft Excel Data Analysis and Business Modeling desde Microsoft Office Online. En este artículo se utilizan los archivos prodmix.xls y s25_1.xls hasta s25_5.xls.

¿Qué es la herramienta Solver de Excel?

Solver se utiliza cuando queremos encontrar la mejor manera de hacer algo. O dicho de un modo más formal: queremos encontrar los valores de determinadas celdas de una hoja de cálculo que optimicen (aumenten o disminuyan) un determinado objetivo.

Un modelo de optimización consta de tres partes: la celda objetivo, las celdas cambiantes y las restricciones.

  • La celda objetivo representa el objetivo como, por ejemplo, aumentar las ganancias mensuales.
  • Las celdas cambiantes son las celdas de la hoja de cálculo que podemos cambiar o ajustar para optimizar la celda objetivo como, por ejemplo, la cantidad de cada producto fabricada durante un mes.
  • Las restricciones son delimitaciones que se aplican a las celdas cambiantes como, por ejemplo, no usar más recursos que los disponibles y no producir más cantidad de un producto que la que pueda venderse.

¿Cómo puedo determinar qué mezcla de productos aumenta la rentabilidad?

Las empresas necesitan a menudo determinar el programa de producción mensual (o semanal) que proporciona la cantidad de fabricación de cada producto. En su forma más simple, el problema de la mezcla de productos implica cómo determinar la cantidad de cada producto que debe fabricarse durante un mes para aumentar las ganancias. La mezcla de productos debe satisfacer a menudo las siguientes restricciones:

  • En la mezcla de productos no se pueden utilizan más recursos que los disponibles
  • Hay una demanda limitada para cada producto. No podemos producir más cantidad de un producto durante un mes que la demandada porque el excedente de producción se desecha (como en el caso de los fármacos perecederos, por ejemplo).

Resolvamos ahora el siguiente ejemplo del problema de mezcla de productos. Encontrará la solución a este problema en el archivo prodmix.xls (que se incluye en la descarga de archivos de ejemplo), mostrado en la Figura 1.

Ejemplo de mezcla de productos.

Figura 1: El ejemplo de mezcla de productos.

Suponga que trabajamos para una empresa farmacéutica que puede producir seis productos en su planta. La producción de cada producto requiere mano de obra y materia prima.

  • La fila 4 de la Figura 1 contiene las horas de mano de obra necesarias para producir una libra (454 gramos) de cada producto, y la fila 5 indica las libras de materia prima necesarias para producir una libra de cada producto. Por ejemplo, para producir una libra del producto 1 se requieren seis horas de mano de obra y 3,2 libras (1.453 gramos) de materia prima.
  • Para cada fármaco, el precio por libra se indica en la fila 6, el coste unitario por libra se indica en la fila 7 y las ganancias por libra se indican en la fila 9. Por ejemplo, el producto 2 se vende a 11 dólares la libra, el coste unitario es de 5,7 dólares por libra y las ganancias son 5,3 dólares por libra.
  • La demanda de este mes de cada fármaco se indica en la fila 8. Por ejemplo, la demanda del producto 3 es de 1.041 libras (472,6 kilos).

Para este mes hay 4.500 horas de mano de obra y 1.600 libras (726,4 kilos) de materia prima disponibles. ¿Cómo puede esta empresa aumentar sus ganancias mensuales?

Si no supiéramos nada sobre Solver, podríamos resolver este problema creando una hoja de cálculo en la que pudiéramos realizar un seguimiento de las ganancias y el uso de recursos asociados a cada mezcla de productos. A continuación, utilizaríamos pruebas de ensayo y error para variar la mezcla de productos con el fin de optimizar las ganancias sin utilizar más mano de obra o materia prima que la disponible, y sin producir más cantidad de un determinado fármaco que la demandada. En este proceso sólo utilizamos Solver en la etapa de ensayo y error. Básicamente, Solver es un motor de optimización que realiza eficazmente la búsqueda por ensayo y error.

Un elemento clave para solucionar el problema de la mezcla de productos es calcular eficazmente el uso de recursos y las ganancias asociadas a cada mezcla de productos. Una herramienta importante que podemos utilizar para realizar este cálculo es la función SUMAPRODUCTO. La función SUMAPRODUCTO multiplica los valores correspondientes en los rangos de celdas y devuelve la suma de esos valores. Todos los rangos de celdas utilizados en una evaluación de SUMAPRODUCTO deben tener las mismas dimensiones, lo que implica que se puede utilizar SUMAPRODUCTO con dos filas o dos columnas, pero no con una columna y una fila.

Para ejemplificar el uso de la función SUMAPRODUCTO en nuestro ejemplo de mezcla de productos, tratemos de calcular nuestro uso de recursos. Para ello, necesitamos realizar el siguiente cálculo:

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

En nuestra hoja de cálculo, podríamos calcular el uso de mano de obra (de un modo tedioso) como D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4. De igual forma, el uso de materia prima se podría calcular como D2*D5+E2*E5+F2*F5+G2*G5+H2*H5+I2*I5. Si escribir estas fórmulas en una hoja de cálculo para seis productos es una tarea laboriosa, imagínese cómo sería si trabajara con una empresa que fabricara, por ejemplo, 50 productos en su planta.

Una forma mucho más sencilla de calcular el uso de mano de obra y materia prima consiste en 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) y es mucho más fácil de escribir.

Observe que hemos utilizado el signo $ con el rango D2:I2, de forma que cuando copiemos la fórmula podamos seguir obteniendo la mezcla de productos de la fila 2. La fórmula de la celda D15 calcula el uso de materia prima.

De forma similar, podemos obtener nuestras ganancias calculando:

(Ganancias del fármaco 1 por libra) *
(Libras del fármaco 1 producidas) +
(Ganancias del fármaco 2 por libra) *
(Libras del fármaco 2 producidas) +
...
(Ganancias 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 las tres partes de nuestro modelo Solver de mezcla de productos:

Celda objetivo Celdas cambiantes Restricciones
Nuestro objetivo es aumentar las ganancias (calculadas en la celda D12). El número de libras producidas de cada producto (indicado en el rango de celdas D2:I2).
  • No utilizar más mano de obra y 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 producir más cantidad de un fármaco que la demandada. Es decir, los valores de las celdas D2:I2 (las libras producidas de cada fármaco) deben ser menores o iguales que la demanda de cada fármaco (indicada en las celdas D8:I8).
  • No podemos producir una cantidad negativa de ningún fármaco.

¿Cómo puedo especificar este modelo en Solver?

Ahora explicaremos cómo especificar la celda objetivo, las celdas cambiantes y las restricciones en Solver. Después, no tendrá más que hacer clic en el botón Resolver y Solver encontrará una mezcla de productos que suponga un aumento de las ganancias.

  1. Para empezar, seleccione Solver en el menú Herramientas. (Para obtener instrucciones sobre cómo instalar Solver, vea Introducción a la optimización con la herramienta Solver de Excel.)

Aparecerá el cuadro de diálogo Parámetros de Solver.

El cuadro de diálogo Parámetros de Solver.

  1. Para especificar la celda objetivo, haga clic en el cuadro Definir celda objetivo y, a continuación, seleccione nuestra celda de ganancias (la celda D12). Para especificar nuestras celdas cambiantes, haga clic en el cuadro Cambiando las celdas y, después, elija el rango D2:I2, que contiene las libras producidas de cada fármaco. El cuadro de diálogo debe ser similar al que se muestra en la figura siguiente.

El cuadro de diálogo Parámetros de Solver con la celda objetivo y las celdas cambiantes definidas.

  1. Ahora podemos agregar restricciones al modelo. Haga clic en el botón Agregar. Aparecerá el cuadro de diálogo Agregar restricción.

El cuadro de diálogo Agregar restricción.

  1. Para agregar las restricciones de uso de recursos, haga clic en el cuadro Referencia de celda y, después, seleccione el rango D14:D15. Elija <= de la lista en el centro del cuadro de diálogo. 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 con las restricciones de uso de recursos especificadas.

Ahora nos hemos asegurado de que cuando Solver pruebe distintos valores para las celdas cambiantes, sólo considerará las combinaciones que satisfagan D14 <= F14 (la mano de obra utilizada es menor o igual que la mano de obra disponible) y D15 <= F15 (la materia prima utilizada es menor o igual que la materia prima disponible).

  1. Ahora haga clic en Agregar en el cuadro de diálogo Agregar restricción para especificar las restricciones de demanda. Sólo tiene que rellenar el cuadro de diálogo Agregar restricción tal como se muestra en la siguiente figura.

El cuadro de diálogo Agregar restricción con las restricciones de demanda especificadas.

Al agregar estas restricciones nos aseguramos de que cuando Solver pruebe distintas combinaciones para los valores de las celdas cambiantes, sólo considerará las combinaciones que satisfagan las siguientes condiciones:

  • D2 <= D8 (la cantidad del fármaco 1 es menor o igual que la demanda del fármaco 1)
  • E2 <= E8 (la cantidad del fármaco 2 es menor o igual que la demanda del fármaco 2)
  • F2 <= F8 (la cantidad del fármaco 3 es menor o igual que la demanda del fármaco 3)
  • G2 <= G8 (la cantidad del fármaco 4 es menor o igual que la demanda del fármaco 4)
  • H2 <= H8 (la cantidad del fármaco 5 es menor o igual que la demanda del fármaco 5)
  • I2 <= I8 (la cantidad del fármaco 6 es menor o igual que la demanda del fármaco 6)
  1. Haga clic en Aceptar en el cuadro de diálogo Agregar restricción. El cuadro de diálogo Parámetros de Solver debe ser similar al que se muestra en la figura siguiente.

La ventana de Solver final para el problema de mezcla de productos.

  1. Especificamos la restricción de que ninguna de las celdas cambiantes sea negativa en el cuadro de diálogo Opciones de Solver, que se abre haciendo clic en el botón Opciones del cuadro de diálogo Parámetros de Solver.

Configuración de las opciones de Solver.

Seleccione las opciones Adoptar modelo lineal y Adoptar no-negativo y, a continuación, haga clic en Aceptar.

Mostrar¿Por qué seleccionamos estas opciones?

Al seleccionar la opción Adoptar no-negativo nos aseguramos de que Solver sólo considere las combinaciones de celdas cambiantes en las que cada celda cambiante adopte un valor no negativo.

Hemos seleccionado Adoptar modelo lineal porque el problema de mezcla de productos es un tipo especial de problema de Solver denominado modelo lineal. Básicamente, un modelo de Solver es lineal si se cumplen las siguientes condiciones:

  • La celda objetivo se calcula sumando términos de la forma (celda cambiante)*(constante).
  • Cada restricción satisface los requisitos del modelo lineal. Esto significa que cada restricción se evalúa sumando términos de la forma (celda cambiante)*(constante) y comparando estas sumas con una constante.

¿Por qué es lineal este problema de Solver?

Nuestra celda objetivo (ganancias) se calcula del modo siguiente:

(Ganancias del fármaco 1 por libra) * (Libras del fármaco 1 producidas) + (Ganancias del fármaco 2 por libra) * (Libras del fármaco 2 producidas) + ... (Ganancias del fármaco 6 por libra) * (Libras del fármaco 6 producidas)

Este cálculo sigue un modelo en el que el valor de la celda objetivo se obtiene sumando términos de la forma (celda cambiante)*(constante).

Nuestra restricción de mano de obra se evalúa comparando la mano de obra disponible con el valor obtenido de:

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

Por tanto, la restricción de mano de obra se evalúa sumando términos de la forma (celda cambiante)*(constante) y comparando estas sumas con una constante. Tanto la restricción de mano de obra como la restricción de materia prima satisfacen los requisitos del modelo lineal.

Nuestras restricciones de demanda adoptan la forma

(Fármaco 1 producido)<=(Demanda de fármaco 1)
(Fármaco 2 producido)<=(Demanda de fármaco 2)
...
(Fármaco 6 producido)<=(Demanda de fármaco 6)

Cada restricción de demanda satisface también los requisitos del modelo lineal, porque cada una de ellas se evalúa sumando términos de la forma (celda cambiante)*(constante) y comparando estas sumas con una constante.

Una vez demostrado que nuestro modelo de mezcla de productos es lineal, ¿por qué preocuparse?

  • Si un modelo de Solver es lineal y seleccionamos Adoptar modelo lineal, nos aseguramos de que Solver busque la solución óptima al modelo de Solver. Si no es lineal, puede que Solver encuentre o no la solución óptima.
  • Si un modelo de Solver es lineal y seleccionamos Adoptar modelo lineal, Solver utiliza un algoritmo muy eficaz (el método más simple) para encontrar la solución óptima del modelo. Si el modelo de Solver es lineal y no seleccionamos Adoptar modelo lineal, Solver utiliza un algoritmo muy poco eficaz (el método GRG2) y puede resultar difícil encontrar la solución óptima del modelo.
  1. Después de hacer clic en Aceptar en el cuadro Opciones de Solver, volvemos al cuadro de diálogo Solver principal. Cuando hagamos clic en Resolver, Solver calculará una solución óptima (si existe) para nuestro modelo de mezcla de productos.

Una solución óptima al modelo de mezcla de productos sería un conjunto de valores de celdas cambiantes (libras producidas de cada fármaco) que aumentara las ganancias entre el conjunto de todas las soluciones viables. Como ya hemos explicado, una solución viable es un conjunto de valores de celdas cambiantes que satisfacen todas las restricciones. Los valores de las celdas cambiantes mostrados en la Figura 2 son una solución viable, ya que ninguno de los niveles de producción es negativo, ninguno de ellos excede la demanda y el uso de recursos no es mayor que los recursos disponibles.

Una solución viable al problema de mezcla de productos satisface las restricciones.

Figura 2: Una solución viable al problema de mezcla de productos satisface las restricciones.

Los valores de las celdas cambiantes mostrados en la Figura 2 representan una solución inviable por los siguientes motivos:

  • Producimos más cantidad del fármaco 5 que la demandada.
  • Utilizamos más mano de obra que la disponible.
  • Utilizamos más materia prima que la disponible.

Una solución inviable al problema de mezcla de productos no satisface las restricciones que hemos definido.

Figura 3: Una solución inviable al problema de mezcla de productos no satisface las restricciones que hemos definido.

Después de hacer clic en Resolver, Solver encuentra rápidamente la solución óptima mostrada en la Figura 4. Debe seleccionar Conservar la solución de Solver para conservar los valores de la solución óptima en la hoja de cálculo.

La solución óptima al problema de mezcla de productos.

Figura 4: La solución óptima al problema de mezcla de productos.

Nuestra empresa farmacéutica puede aumentar sus ganancias mensuales a un nivel de 6.625,20 dólares produciendo 596,67 libras (270,8 kilos) del fármaco 4, 1.084 libras (492,1 kilos) del fármaco 5 y ninguno de los demás fármacos. No podemos determinar si somos capaces de obtener el beneficio máximo de 6.625,20 dólares de otras maneras. De lo único de lo que estamos seguros es de que con nuestra demanda y nuestros recursos limitados no hay forma de ganar este mes más de 6.625,20 dólares.

¿Un modelo Solver tiene siempre una solución?

Supongamos que debe satisfacerse la demanda de cada producto. En ese caso, tendremos que cambiar nuestras restricciones de demanda de D2:I2 <= D8:I8 a D2:I2 >= D8:I8. Para cambiar esta restricción:

  1. Abra Solver.
  2. Haga clic en la restricción D2:I2 <= D8:I8 y luego en Cambiar.

Aparecerá el cuadro de diálogo Cambiar restricción.

El cuadro de diálogo Cambiar restricción.

  1. En el cuadro del centro, elija >= y, a continuación, haga clic en Aceptar.

Con esto nos aseguramos de que Solver sólo considere los valores de las celdas cambiantes que satisfacen todas las demandas.

Al hacer clic en Resolver, aparecerá el mensaje Solver no ha podido encontrar una solución factible. Este mensaje significa que con nuestros recursos limitados no podemos satisfacer la demanda de todos los productos. No hemos cometido ningún error en nuestro modelo. Solver nos indica simplemente que si queremos satisfacer la demanda de cada producto, debemos agregar más mano de obra, más materia prima o ambas cosas.

¿Qué ocurre si los valores definidos no convergen?

Veamos qué sucede si permitimos una demanda ilimitada para cada producto y permitimos también que se produzcan cantidades negativas de cada fármaco. Para encontrar la solución óptima para esta situación:

  1. Abra Solver.
  2. Haga clic en el botón Opciones y, a continuación, desactive la casilla de verificación Adoptar no-negativo.
  3. En el cuadro de diálogo Parámetros de Solver, haga clic en la restricción de demanda D2:I2 <= D8:I8 y luego en Eliminar para quitar la restricción.

Al hacer clic en Resolver, Solver muestra el mensaje Los valores de la celda objetivo no convergen. Este mensaje significa que si debe aumentarse el valor de la celda objetivo (como en nuestro ejemplo), hay soluciones viables con valores en la celda objetivo arbitrariamente grandes. (Si debe reducirse el valor de la celda objetivo, este mensaje significa que hay soluciones viables con valores en la celda objetivo arbitrariamente pequeños.)

En nuestra situación, al permitir la producción negativa de un fármaco, lo que de hecho estamos "creando" son recursos que se pueden utilizar para producir cantidades arbitrariamente grandes de otros fármacos. Dada nuestra demanda ilimitada, esto nos permite obtener ganancias ilimitadas. En una situación real, no podemos ganar una cantidad infinita de dinero. En resumen, si aparece el mensaje Los valores de la celda objetivo no convergen, el modelo contiene un error.

Ponga a prueba sus conocimientos

Las soluciones a estos problemas se muestran en los archivos s25_1.xls hasta s25_5.xls, que se incluyen en la descarga de archivos de ejemplo.

  1. Suponiendo que nuestra empresa farmacéutica pudiera adquirir hasta 500 horas de mano de obra a 1 dólar por hora, ¿debería aprovechar esta oportunidad?
  2. En una planta de fabricación de microprocesadores, cuatro técnicos (A, B, C y D) fabrican tres productos (productos 1, 2 y 3). La fábrica de microprocesadores puede vender este mes 80 unidades del producto 1, 50 unidades del producto 2 y 50 unidades del producto 3 como máximo. El técnico A sólo puede fabricar los productos 1 y 3. El técnico B sólo puede fabricar los productos 1 y 2. El técnico C sólo puede fabricar el producto 3. El técnico D sólo puede fabricar el producto 2. Por cada unidad producida, los productos aportan las siguientes ganancias: el producto 1, 6 dólares; el producto 2, 7 dólares; el producto 3, 10 dólares. El tiempo (en horas) que cada técnico necesita para fabricar un producto se indica en la tabla siguiente.
Producto Técnico A Técnico B Técnico C Técnico D
1 2 2,5 No lo puede fabricar No lo puede fabricar
2 No lo puede fabricar 3 No lo puede fabricar 3,5
3 3 No lo puede fabricar 4 No lo puede fabricar
  1. Cada técnico puede trabajar un máximo de 120 horas al mes. ¿Cómo puede esta fábrica de microprocesadores aumentar sus ganancias mensuales?
  2. Una planta de fabricación de equipos informáticos produce mice (ratones), teclados y joysticks para videojuegos. En la tabla siguiente se indican las ganancias por unidad, el uso de mano de obra por unidad, la demanda mensual y el uso de tiempo de máquina por unidad:
Mice Teclados Joysticks
Ganancias/unidad 8 dólares 11 dólares 9 dólares
Uso de mano de obra/unidad 0,2 horas 0,3 horas 0,24 horas
Tiempo de máquina/unidad 0,04 horas 0,055 horas 0,04 horas
Demanda mensual 15.000 25.000 11.000
  1. Cada mes hay un total de 13.000 horas de mano de obra y 3.000 horas de tiempo de máquina disponibles. ¿Cómo puede la fábrica aumentar su contribución a las ganancias mensuales de la planta?
  2. Resuelva nuestro ejemplo de fármacos bajo el supuesto de que debe satisfacerse una demanda mínima de 200 unidades de cada fármaco.
  3. Jason, un joyero que fabrica pulseras, collares y pendientes de diamantes, desea trabajar un máximo de 160 horas al mes. Dispone de 800 onzas (22,7 kilos) de diamantes. A continuación, se indican las ganancias, el tiempo de mano de obra y las onzas de diamantes necesarias para fabricar cada producto. Si la demanda de cada producto es ilimitada, ¿cómo puede Jason aumentar sus ganancias?
Producto Ganancias por unidad Horas de mano de obra por unidad Onzas de diamantes por unidad
Pulsera 300 dólares 0,35 1,2
Collar 200 dólares 0,15 0,75
Pendientes 100 dólares 0,05 0,5
 
 
Corresponde a:
Excel 2003