Excel proporciona una solución rápida y eficiente para la resolución de problemas de Programación Lineal (PL), razón por la cual, se hace imprescindible conocer la mecánica del uso del complemento SOLVER de Excel.
El Complemento SOLVER de Excel es una herramienta de optimización que emplea rutinas de programación que permiten calcular varios valores que cumplen condiciones que conocemos como restricciones.
La Programación Lineal
La programación lineal es un método matemático para encontrar la mejor solución posible a un problema de optimización lineal, donde se busca maximizar o minimizar una función lineal sujeta a un conjunto de restricciones lineales. (Chase, 2000).
En términos generales, la programación lineal se utiliza para maximizar o minimizar una función objetivo lineal (como el beneficio o el costo) sujeto a una serie de restricciones lineales (como la disponibilidad de recursos o las limitaciones de producción).
La programación lineal se utiliza en una amplia variedad de aplicaciones, como la planificación de la producción, la gestión de inventarios, la asignación de recursos, la asignación de personal, la gestión de proyectos, la planificación financiera y muchas otras.
El método de programación lineal utiliza herramientas matemáticas como la geometría vectorial y la teoría de matrices para encontrar la solución óptima de manera eficiente.
Solución a un problema clásico de PL
Por supuesto que para resolver un problema de PL, es necesario un enunciado, asi que, en primera instancia se tomará el ejercicio propuesto por CHASE (2000) en la página 292, relacionado con las decisiones sometidas a unas restricciones, cuyo contenido parafraseado es: Una compañia fabrica palos de Hockey y Juegos de Ajedrez, los palos tardan 6 horas en la máquina A, y 2 horas en la máquina B; mientras que los juegos de Ajedrez, tardan 6 horas en la máquina A, 6 en la B y 1 en la máquina C. Los palos de Hockey producen una utilidad marginal de U$2 mientras que el juego de ajedrez una de U$4.
La disponibilidad de tiempo de las máquinas es de 120, 72 y 10 horas respectivamente; ¿Cuántos palos de Hockey y juegos de Ajedrez debe prodicirse para maximizar la utilidad?
Para solucionar el anterior problema, se procede a diseñar un modelo matemático que luego se escribirá en Excel; El modelo de PL que debe trabajarse en Excel se muestra en la siguiente gráfica:
De la gráfica anterior podemos decir lo siguiente:
- Las celdas B5 y C5 recibiran el valor calculado por SOLVER y representan los valores que deben tener las variables para que la utilidad sea máxima.
- La celda D4 tiene la fórmula que calcula la utilidad, esta fórmula víncula los posibles valores de las variables con sus coeficientes expresados en las celdas B4 y C4. La fórmula que se maneja en esta celda puede ser =SUMAPRODUCTO(B4:C4;B5:C5) o =B4*B5+C4*C5
- Las celdas D7, D8 y D9 tienen las formulas que representan las restricciones y es por eso que ellas son menor que las celdas E7, E8 y E9, que representan el valor restrictivo.
- Las fórmulas de las restricciones son como esta de la celda D7, asi: =SUMAPRODUCTO(B$5:C$5;B7:C7).
Para resolver el ejercicio, debe activarse la casilla de verificación "Adoptar modelo lineal" de las "opciones de Solver", haga clic en el botón "opciones..." y lo llevará a la siguiente ventana:
Cuando regresé al cuadro de dialogo de Solver, haga clic en el botón "Resolver" y de inmediato el ejercicio estará resuelto, los resultados se muestran asi:
La interpretación de estos resultados es la siguiente: Para obtener una Utilidad máxima de U$56 (celda D4), deben producirse 12 palos de Hockey (celda B5) y 8 juegos de Ajedrez (celda C5).
Solución al Problema de Transporte
El Método de Transporte es un caso especial del Método Simplex, que fué el método usado anteriormente para resolver el problema de PL.Se ilustra la solución del Método de Transporte usando el siguiente ejemplo de CHASE (2000), página 317.
Para resolver la tabla de transporte, se escribe un esquema elaborado de tablas y fórmulas en Excel, la ilustración es la siguiente:
De la anterior figura, deben tenerse en cuenta las siguientes consideraciones:
- La primera tabla es el mismo modelo del enunciado escrito en filas y columnas de excel
- La segunda tabla representa el espacio donde SOLVER calculará los valores que se deben asignar a todas las variables para que se resuelva el problema de PL, El rango B12:E14, representa el espacio para esos valores.
- La tercera tabla representa los cálculos de los costos, los cuales se obtienen multiplicando los costos referenciados en la primera tabla por los valores que calculará SOLVER en la segunda tabla. Un ejemplo de las fórmulas en esta segunda tabla es Celda B19, cuya fórmula es =B12*B5.
- La celda F22 representa la sumatoria total de los costos, fórmula =SUMA(B22:E22).
Después de las anteriores consideraciones, se procede a diligenciar el cuadro de diálogo de SOLVER, de la siguiente manera:
Note que la Celda objetivo es F22 pues representa la sumatoria de los costos y que el rango B12:E14 será el espacio donde SOLVER calculará los valores que deben asignarse a las variables. Asimismo la casilla del valor de la celda objetivo es mínimo.
Las consideraciones adicionales, como B8, C8, D8 y E8 iguales a B15, C15, D15, E15 significa que deben cumplirse con los requerimientos del problema. También, las celdas F12, F13, F14 deben ser menores que F5, F6 y F7 porque no puede asignarse mas de lo que se tiene disponible en inventario.
Después de asegurarse de adoptar un modelo lineal en las "Opciones de Solver", se hace clic en el botón "Resolver" y el resultado es como sigue:
La interpretación es que Al asignar los valores como se indican en el rango B12:E14 el costo mínimo de transporte es U$720 (celda F22).
Para descargar el documento completo, haga clic aqui
Fuente
- CHASE, Richard (2000). Administración de Producción y Operaciones. Bogotá: Mc Graw Hill.