Etiqueta: solver

Instalación del Complemento Solver en Excel 2003

El complemento de Solver de Excel es una poderosa herramienta que permite resolver modelos de optimización utilizando una planilla de cálculo (Excel). En artículos anteriores hemos mostrado Cómo resolver Modelos de Programación Lineal utilizando Solver y algunas aplicaciones clásicas como el Problema de Transporte y Problema de la Dieta.

Las características de Solver han evolucionado en las distintas versiones de Office y en esta oportunidad mostraremos cómo activar este complemento en la versión del año 2003 y 2007. Para ello se deben seguir los siguientes pasos:

Paso 1: Abrir el programa Excel y en el menú de Herramientas seleccionar Complementos. 

Complementos Excel

Paso 2: Seleccionar la opción Solver y luego Aceptar.

Complemento Solver

Paso 3: Puede ser necesario aprobar la ejecución del complemento como muestra la imagen a continuación. En este caso se debe seleccionar Sí

Instalar Solver

Paso 4: Una vez completada la activación de Solver el complemento estará disponible en el menú de Herramientas de Excel.

Solver Instalado

Una alternativa a Solver es el complemento de Excel OpenSolver el cual se puede descargar gratuitamente desde www.opensolver.org. En el artículo Cómo resolver un modelo de Programación Lineal con OpenSolver mostramos su utilización en un problema sencillo. Adicionalmente What’sBest! resulta ser otra excelente alternativa para Solver y desarrollado para Excel. En el siguiente tutorial detallamos cómo descargar e instalar What’sBest!.

Interpretación del Informe de Sensibilidad de Restricciones de Solver

Continuando con el Análisis de Sensibilidad (o Análisis Postoptimal) en la resolución de modelos de Programación Lineal, en este artículo analizaremos la interpretación del Informe de Sensibilidad (o Informe de Confidencialidad en algunas de las versiones de Office que datan del año 2010 a la fecha) de restricciones de Solver, comúnmente conocido como el análisis del Precio Sombra de cada una de las restricciones.

Por ejemplo, la versión de Solver disponible con Office 365 ofrece la siguiente interfaz para obtener el Informe de Sensibilidad (luego de alcanzar la solución óptima del problema en su escenario base).

informe sensibilidad solver office 365

En el caso de la versión de Solver compatible con Office 2007 y Office 2003, la interfaz es la siguiente:

Sensibilidad Solver

De modo de ilustrar su correcta interpretación, a continuación consideraremos nuevamente nuestro ejemplo de Programación Lineal:

Modelo Lineal 2

Con solución óptima X=14/5 Y=8/5 y valor óptimo V(P)=20,8. El Informe de Restricciones de Solver corresponde a:

Informe Restricciones

Las filas del Informe de Restricciones corresponden a las restricciones 1 y 2, respectivamente.

En el caso de la restricción 1 el Precio Sombra es de 1,2 y el valor de la restricción (lado derecho) es igual a 12. Para dicho parámetro (lado derecho) se permite un aumento de de 9,33 y una disminución de 4, es decir, el lado derecho de la restricción 1 puede variar entre [8, 21,33] (12-4, 12+9,33) y el Precio Sombra de magnitud 1,2 seguirá siendo válido (es decir, se conserva la base óptima).

Esto significa que si, por ejemplo, el lado derecho de la restricción 1 aumenta en 1 unidad y el resto de los parámetros del modelo permanecen constantes, el nuevo valor óptimo será: V(P)=20,8+1*1,2=22.

Ahora bien, si por ejemplo, el lado derecho de la restricción 1 disminuye a 10 el nuevo valor óptimo será: V(P)=20,8-2*1,2=18,4.

Finalmente si la variación del lado derecho esta fuera del intervalo [8, 21,33] NO se puede utilizar el Precio Sombra para poder predecir cuál será el nuevo valor óptimo. Esto se debe a que la nueva solución óptima ya no se encontrará con las mismas restricciones activas, es decir, cambia la base óptima.

Al respecto recomendamos ver el tutorial sobre Cómo calcular gráficamente el Precio Sombra de una Restricción.

De forma análoga, para la restricción 2 el Precio Sombra es de 0,4 y este valor es válido si su lado derecho varía entre [9, 24] (16-7, 16+8). Por ejemplo, si el lado derecho de la restricción 2 aumenta en 3 unidades (y el resto de los parámetros permanece constante) el nuevo valor óptimo será: V(P)=20,8+3*0,4=22.

Interpretación del Informe de Sensibilidad de Celdas Cambiantes (Solver)

Cuando Resolvemos un modelo de Programación Lineal con Solver de Excel utilizamos una estimación de los parámetros (constantes) los cuales generalmente hacen referencia a disponibilidad de recursos, precios, costos, etc. En este contexto nos interesa simular el impacto en los resultados ante variaciones marginales de dichos parámetros sin la necesidad de resolver un nuevo modelo de optimización.

Este objetivo se puede alcanzar a través de los Informes de Sensibilidad de Solver los cuales se pueden generar una vez resuelto un escenario base para un modelo de optimización lineal, seleccionando la opción “Sensibilidad” (o Confidencialidad en versiones recientes de Office) según se muestra a continuación:

Análisis de Sensibilidad Solver

El siguiente análisis explica cómo interpretar el Informe de Sensibilidad de Celdas Cambiantes de Solver para el siguiente modelo de Programación Lineal:

Modelo Lineal 2

Con solución óptima X=14/5 Y=8/5 y valor óptimo V(P)=20,8. El Informe de Celdas Cambiantes corresponde a:

Informe Sensibilidad Celdas Cambiantes

Notar que en la última columna se ha marcado con color rojo la palabra Aumento que debiese decir Disminución (este tipo de error se observa generalmente en las versiones más antiguas de Office).

El Informe de Sensibilidad de Celdas Cambiantes nos indica el intervalo de variación para cada parámetro de la función objetivo que permite mantener la actual solución óptima (asumiendo que el resto de los parámetros permanece constante).

Por ejemplo, el coeficiente que actualmente pondera a la variable X en la función objetivo de maximización es 4. El aumento permisible de 4 nos indica que el actual parámetro podría aumentar en dicha magnitud y la solución óptima actual se mantendría.

Análogamente se podría disminuir en 1 unidad (disminución permisible) y se conserva la solución actual.

En conclusión, el Intervalo de Variación para el parámetro que pondera a la variable X en la función objetivo que conserva la actual solución óptima es entre [3,8].

Siguiendo un procedimiento similar se puede demostrar que el intervalo de variación para el parámetro asociado a la variable Y en la función objetivo que conserva la actual solución óptima es entre [3,8] (sólo es una coincidencia que sean los mismos intervalos para cada parámetro).

Por ejemplo, un cambio en uno de los parámetros de la función objetivo afecta la pendiente de ésta (curvas de nivel) que en la medida que se encuentre en el intervalo de variación previamente determinado mantendrá al vértice C como solución óptima del problema.

Resolución Gráfica PL

Una forma sencilla de corroborar estos resultados es mediante el Método Gráfico en Programación Lineal. Adicionalmente en el artículo Análisis de Sensibilidad Método Gráfico se detalla el procedimiento para obtener los intervalos de variación para los parámetros tanto en la función objetivo como en las restricciones del problema. Recomendamos revisar ambos artículos de modo de favorecer la comprensión de este tipo de análisis.

Problema de la Dieta con variables enteras resuelto con Solver de Excel

En un artículo previo tratamos el Problema de la Dieta como una aplicación característica de la Programación Lineal discutido ampliamente en los Cursos de Investigación de Operaciones. El problema consiste básicamente en encontrar una combinación de alimentos óptima que permita satisfacer ciertos requerimientos nutricionales mínimos y adicionalmente tenga el menor costo asociado a la selección de los mismos.

Una vez obtenida la solución óptima y valor óptimo de dicho modelo nos podemos enfrentar al escenario donde todas o algunas de las variables de decisión adoptan valores fraccionarios. Si bien esta situación es aceptada en los modelos de Programación Lineal (en efecto constituye un supuesto básico de la Programación Lineal), puede resultar de interés simular una nueva solución donde las variables de decisión adopten valores enteros.

El siguiente tutorial muestra cómo incorporar las condiciones de integralidad al Problema de la Dieta, lo que da origen a un modelo de Programación Entera.

Se puede observar que hemos utilizado un formato similar al modelo de Programación Lineal, sin embargo, se incorpora la condición de integralidad para las variables de decisión como si fuese una restricción adicional. Adicionalmente en las Opciones de Solver debemos desactivar la selección de «Adoptar modelo lineal» debido a que ahora el modelo es de Programación Entera (esta indicación es válida para las versiones de Office 2007 y anteriores).

La tabla a continuación resume los resultados del Problema de la Dieta resuelto como un modelo de optimización lineal o entero:

Resultados del Problema de la Dieta

Se puede observar que el valor óptimo del Problema Entero es superior al del Problema Lineal. Siendo éste un problema de minimización esta situación es natural dado que el dominio de soluciones factibles del problema entero está contenido en el dominio del problema lineal (es un subconjunto) y por tanto no podríamos encontrar nada mejor (más económico en este caso) que el valor óptimo del problema lineal.

Es importante destacar adicionalmente que para obtener la solución óptima de un problema entero NO es suficiente con aproximar los resultados fraccionarios del problema lineal asociado, por ejemplo, al entero superior o entero inferior más cercano. En consecuencia se requiere de algoritmos específicos para la resolución de modelos de Programación Entera, siendo el Algoritmo de Ramificación y Acotamiento (Branch & Bound) uno de los más populares.

Problema de la Dieta en Programación Lineal resuelto con Solver de Excel

Una de las aplicaciones clásicas de la Programación Lineal es el Problema de la Dieta. El objetivo es seleccionar un conjunto de alimentos dados que permitan satisfacer ciertos requerimientos nutricionales y preferencias y que adicionalmente tenga un costo mínimo.

En este contexto en el Servidor NEOS se puede encontrar un conjunto de antecedentes que permiten comprender el contexto histórico del Problema de la Dieta y cómo se puede abordar de forma eficiente a través de modelos de optimización. Al igual que varias de las aplicaciones de la Investigación de Operaciones este problema tiene un origen militar.

Para efectos de este tutorial y con el objetivo de ilustrar esta aplicación consideremos el siguiente listado de alimentos con su perfil nutricional y costo monetario:

Tabla Alimentos

Se desea proponer una dieta que contenga al menos 2.000 (Kcal) , al menos 55 gramos de proteína y 800 (mg) de calcio. Adicionalmente para garantizar cierta variedad en la dieta se establece límites de porciones por día en los alimentos. Con esta información se requiere encontrar la dieta que tenga el menor costo asociado y permita satisfacer los requerimientos anteriores.

Para ello definimos el siguiente modelo de Programación Lineal:

1. Variables de Decisión: Xi : Porciones de alimentos a consumir durante el día del alimento i (Con i=1 ==> Avena, …. i=6 ==> Porotos).

2. Función Objetivo: Minimizar 30X1+240X2+130X3+90X4+200X5+60X6

3. Restricciones:

  • Mínimo de Calorias (KCal): 110X1+205X2+160X3+160X4+420X5+260X6 >= 2.000
  • Mínimo de Proteínas: 4X1+32X2+13X3+8X4+4X5+14X6 >= 55
  • Mínimo de Calcio: 2X1+12X2+54X3+285X4+22X5+80X6 >= 800
  • Variedad de la Dieta: X1<=4   X2<=3   X3<=2   X4<=8   X5<=2   X6<=2
  • No Negatividad: Xi>=0 Para todo i.

La implementación de este modelo en Solver de Excel para obtener su solución óptima y valor óptimo se muestra en el siguiente tutorial:

La Solución Óptima es X1=4, X2=0, X3=0, X4=2,08, X5=1,68, X6=2 y el Valor Óptimo (costo de la dieta) es $764,07.

Como el modelo es de Programación Lineal se permiten valores fraccionarios para las variables de decisión. Por tanto si buscamos solo valores enteros para las variables de decisión en ese caso debemos definir un modelo de Programación Entera el cual revisamos en el siguiente artículo: Problema de la Dieta en Programación Entera resuelto con Solver de Excel.