Solver = Excel + Optimización

PROBLEMA: Un fabricante está tratando de decidir las cantidades de producción para dos artículos: mesas y sillas. Se cuenta con 96 unidades de material y con 72 horas de mano de obra. Cada mesa requiere 12 unidades de material y 6 horas de mano de obra. Por otra parte las sillas utilizan 8 unidades de material cada una y requieren 12 horas de mano de obra por silla. El margen de beneficio es el mismo para las mesas que para las sillas: 5 euros por unidad. El fabricante prometió construir por lo menos 2 mesas. A este fabricante le interesa maximizar sus beneficios pero teniendo en cuenta sus limitaciones de producción, debido al material y a la mano de obra disponible y la promesa realizada.

Se trata del típico problema de programación lineal consistente en optimizar una función objetivo teniendo en cuenta las restricciones que pudieran darse. Pero por muy típico que sea, resulta relativamente difícil de resolver mediante lápiz y papel a pesar de que el método que se emplea en estos casos (Simplex) parezca decir lo contrario. Gracias que tenemos a Microsoft Excel y su, a veces menospreciado, potencial. Concretamente, para resolver este problema podemos utilizar el complemento Solver. Para comprobar si lo tenéis activo id al menú Herramientas->Complementos.
Lo primero que hay que hacer, antes de nada, es plantear el problema identificando cuáles son las variables del problema, cuál es la función objetivo z, si buscamos maximizar o minimizar dicha función y, finalmente, las inecuaciones que reflejan las restricciones del problema. Después de mucho darle al “coco” llegaríamos al siguiente planteamiento:
Bueno, ahora sí podemos introducir esta información en una hoja de cálculo. La traducción, después de haber planteado adecuadamente el problema es más rápida y sencilla:

En la celda A2:
=SUMAPRODUCTO(B4:C4;B5:C5)

En la celda D8:
=SUMAPRODUCTO(B8:C8;$B$5:$C$5)

*Debéis copiar esta fórmula en D9, D10, D11 y D12 (arrastrando el cuadrito negro que aparece en la parte inferior derecha de la celda D8 cuando la seleccionáis después de haber introducido la fórmula)

Todos los demás datos son literales numéricos.

Abrimos Solver en el menú Datos (versión 2007) o en Herramientas (versiones anteriores). En el cuadro de diálogo que aparece establecemos como celda objetivo A2, en “Valor de la celda objetivo” seleccionamos “máximo” y en “Cambiando las celdas” seleccionamos el rango B5:C5. Luego agregamos las restricciones D8<=F8, D9<=F9, D10>=F10, D11>=F11 y D12>=F12. En “Opciones” debemos marcar “Adoptar modelo lineal”. Cuando todo está listo presionamos el botón "Resolver" y después "Aceptar".

Como resultado de ejecutar Solver tendremos en la celda A2 el valor 45, es decir, como máximo beneficio el fabricante obtendrá 45 euros. En las celdas B5 y C5 tendremos los valores 6 y 3 respectivamente, esto es, deben fabricarse 6 mesas y 3 sillas para obtener el máximo beneficio respetando las restricciones. Los valores de la columna D también son interesantes porque muestran que efectivamente va a usarse todo el material y la mano de obra disponible ya que lo calculado coincide con el tope estipulado.
¡Voilà! Ya hemos resuelto el problema, con la ventaja de que nuestro fabricante particular puede variar los parámetros o restricciones y recalcular los objetivos con el mínimo esfuerzo.

Referencias:

Cuando trato cualquier tema de investigación operativa siempre recuerdo con satisfacción el buen hacer de la profesora Leticia Lorenzo Picado al acercarme con sus lecciones magistrales a este apasionante mundo.

Sobre operaciones matemáticas avanzadas utilizando Excel os recomiendo el libro “Matemáticas con Microsoft Excel” de Miguel Barreras Alconchel. Ed. Ra-Ma, que además os proporciona un CD-ROM con los archivos Excel de todos los ejemplos comentados en el libro.

A día 16 de octubre de 2007 he revisado esta entrada y añado una nueva aportación: unas diapositivas sobre cómo aplicar manualmente el algoritmo del Simplex, que aquí sólo había sido nombrado. Están en inglés, lo que puede ser un inconveniente para algunos. No obstante, considero que se entienden sin mayor dificultad.

| View | Upload your own

A día 28 de enero de 2008 añado esta implementación en lenguaje C del algoritmo Simplex por si resulta de vuestro interés. Podéis ver el documento a pantalla completa haciendo clic en el control de la esquina superior derecha o descargándolo directamente en formato pdf desde Scribd: