Analisis de Sensibilidad Usando Excel Solver

Tema 3: Programación Lineal

Prof. José Rangel

Escuela de Ingeniería de Procesos Industriales Departamento de Estadística Aplicada Universidad Central de Venezuela

Programación Lineal (8206)

Facultad de Ingeniería

Análisis de Sensibilidad usando Excel Solver Tema 3

Habilitando el complemento Solver dentro de la hoja de cálculo Excel: Instrucciones: 1. En la parte superior izquierda, hacer click, en el botón “Personalizar barra de herramientas de

acceso rápido”

. Una vez desplegado el menú opciones,

seleccionar la opción “Mas comandos” haciendo click.

2. Dentro de la opción “Más comandos” aparecerá la pantalla con las opciones de Excel, ingrese en la opción de menú de la parte izquierda y seleccione “Complementos”.


Procesos Industriales


Prof. José Rangel

3. Dentro de la administración de complementos, localizar en la parte inferior, la opción “administrar” y desplegando las opciones, seleccionar “complementos de Excel” y hacer click en el botón “Ir”

4. Una vez dentro del menú de complementos, habilitar la casilla “solver” y luego aceptar.

5. En la barra de menús de excel, seleccionar “Datos” y luego en la parte superior

derecha dentro de la barra de herramientas, estará habilitado un botón que ejecuta el complemento solver como se describe en la siguiente captura:




Prof. José Rangel

6. Seleccionando el botón solver, aparecerá una pantalla con los parámetros que este

complemento de programación lineal utiliza para resolver los datos ingresados en la hoja de cálculo, que a continuación se describen:

NOTA: La forma de activar el complemento Solver en Excel varia en las diferentes versiones del paquete Microsoft Office, por lo que es recomendable buscar la manera en que se activan los diferentes complementos en la barra de menús y seguir de forma intuitiva cada paso.




Prof. José Rangel

Creación de una plantilla para la escritura de un modelo lineal en la hoja de cálculo:

Esta parte consta de la escritura de un modelo de programación lineal usando la hoja

de cálculo electrónica de Excel para su resolución a través del complemento solver. Cabe destacar que los modelos de programación lineal se encuentran conformados por una función objetivo sujeta a restricciones, tal como se presenta en el siguiente ejemplo: Max z  2 x1  3 x2

Sujeta a : 3x1  2 x2  6  x1  x2  0 x1 , x2  0

Para su escritura en Excel, se procederá a crear una plantilla que permita la correcta

manipulación de los coeficientes y variables presentes en el modelo. El siguiente esquema describe cada uno de los componentes de la plantilla:

Descripción de las formulas: Valor de z: En esta celda se almacenara el valor numérico que adopta la función objetivo

en torno al punto óptimo factible calculado por el Solver, siendo la sumatoria del producto de las celdas de los coeficientes objetivos y del punto óptimo, de la siguiente manera: Z = C4*C5+D5*D5




Prof. José Rangel

Lado izquierdo: en estas celdas se almacenará el valor numérico del uso total de recursos,

en base al valor óptimo que maximice o minimice la función, a través de las siguientes formulas en base al ejemplo anterior:

Para la restricción 1: C5*C8 + D5*D8 Para la restricción 2: C5*C9 + D5*D9

Optimizando el modelo lineal con el Excel Solver: Una vez creada la plantilla con los coeficientes del modelo lineal que estamos

analizando en cuestión, se procede a optimizarlo usando el Solver, siguiendo los pasos:

1. Ejecute el Solver a través de la opción de la barra de herramientas, Datos y seleccionando el icono

.

2. Establecer el parámetro del Solver donde se encuentra la celda objetivo que contendrá el valor óptimo calculado para Z. Ejemplo:

3. Seleccionar el criterio de la función objetivo si fuere maximización o minimización. 4. Especificar las celdas en donde se depositara el valor numérico de las variables de decisión.

5. Agregar cada una de las restricciones, seleccionando el primer lugar la celda referenciada como “Lado Izquierdo” luego su relación de orden correspondiente y por último el “lado derecho”.

6. Seleccionar el menú desplegable en “Método de resolución” y especificar “Simplex

LP”. La pantalla final con la salida de los datos, aparecerá como se muestra a continuación:




Prof. José Rangel

7. Finalizado el establecimiento de los parámetros, hacer click en “Resolver” y el Solver

arrojara la solución en las celdas donde se especificó el valor numérico de las variables de decisión y de la función objetivo, como se muestra a continuación:




Prof. José Rangel

Interpretación del análisis de sensibilidad arrojado por el complemento Solver de Excel:

Con fines de emitir el análisis de sensibilidad, una vez llenada la plantilla en la hoja

de cálculo electrónica y hacer el respectivo llamado al Solver, indicando cada uno de sus parámetros, en la pantalla final se seleccionara en el recuadro informes la opción “confidencialidad” o en otras versiones “sensibilidad”, como se describe:

Una vez aceptada la opción, aparecerá una nueva hoja adicional, denominada

“Informe de Confidencialidad”, donde se depositaran todos los datos necesarios para el análisis de esta última etapa:

En análisis o informe de sensibilidad, es una hoja que posee las siguientes

características:




Prof. José Rangel

Consideraciones generales del informe de sensibilidad: Intervalo de Optimalidad: se refiere al valor mínimo y máximo que puede variar

los coeficientes de la función objetivo para que el punto óptimo factible se siga manteniendo

constante. Es importante tomar en cuenta en que dichos cambios son unidimensionales, es decir, cada intervalo es válido si se hace a una sola variable de decisión manteniendo la

otra constante. De hacer dicho cambio de forma simultánea, el informe de sensibilidad actual ya no sería valido, lo que generaría nuevos intervalos para la nueva solución.

Intervalo de Factibilidad: se refiere al valor mínimo y máximo que puede variar el

lado derecho de las restricciones para que la solución siga siendo factible (requerimientos y/o disponibilidad de recursos para que el precio sombra se mantenga valido). El análisis se realiza de la siguiente manera:

El precio sombra: es la razón de cambio por unidad de recurso y/o requerimiento,

para lo cual se incrementara o disminuirá la función objetivo a medida que el lado derecho de cada restricción varié.

Los costos reducidos: se refiere al costo de los recursos consumidos con

respecto al ingreso. También puede definirse como cuánto tendría que cambiar el coeficiente de dicha variable, en la función objetivo, para tener un valor óptimo positivo. Por tanto, si una variable ya es positiva en la optimalidad, su costo reducido es cero. Si el valor Programación Lineal (8206)



Prof. José Rangel

óptimo de una variable es cero, entonces, según la definición de costo reducido, usted

puede ver que dicho costo es el incremento o el decremento permisible que corresponde a dicha variable.

Ejemplo. TOYCO utiliza tres operaciones para armar tres tipos de juguetes: trenes, camiones y carros. Los tiempos diarios disponibles para las tres operaciones son 430, 460 y 420 minutos, respectivamente, y los ingresos por unidad de tren, camión y auto de juguete son de $3, $2 y $5, respectivamente. Los tiempos de ensamble por tren en las tres operaciones son de 1, 3 y 1 minutos, respectivamente. Los tiempos correspondientes por tren y por auto son (2, 0,4) y (1, 2,0) minutos (un tiempo cero indica que la operación no se utiliza). a.- Construir el modelo de programación lineal correspondiente a la situación anterior.

b.- Determinar la solución óptima, utilizando el método más adecuado e interpretar dicha solución. c.- Realizar un análisis de los costos reducidos en caso de existir. Interpretar la solución. Solución. El modelo asociado es el siguiente: Variables de decisión:

Max Z=

= = =

3x1  2 x 2  5 x3 x1  2 x 2  x3

Sujeto a

3x1

 430

(Recurso 1, Operación 1)

 2 x3  460


 420


x1  4 x 2 x1 , x 2 , x3  0

Optimo x1  0, x 2  100, x3  230 Z  1350




Prof. José Rangel

El modelo escrito en la plantilla de Excel, queda de la siguiente manera:

Nótese que aparece conjuntamente con la solución óptima, esto quiere decir que

una vez escrito el modelo de PL en la plantilla correspondiente, se deben haber escrito las

fórmulas para la función objetivo, los lados izquierdos y la Holgura. La fórmula para la holgura consiste en restar el valor asociado en las celdas del lado derecho, restándolos con el lado izquierdo.

Interpretación: La solución indica que deben producirse ningún tren de juguete, 100 camiones y 230 autos para obtener una ganancia de 1350 $.

Los datos arrojados por el análisis de sensibilidad en el Solver, son:

En si los resultados del análisis de sensibilidad, pueden resumirse de la siguiente manera:




Restricción

Capacidad de operación 1 Capacidad de operación 2 Capacidad de operación 3

Coeficiente en Z = = =

Prof. José Rangel

Intervalo de Factibilidad Intervalo

Precio sombra

440,860

2$

230,440 400, ∞

Intervalo de Optimalidad

1$ 0

Intervalo

Costo reducido

0, 10

0$

−∞, 7

2.33, ∞

4$ 0$

Una vez obtenido dicho informe, pueden responderse algunas preguntas

importantes en base al contexto del problema, como se muestra en los siguientes ejemplos: a) Suponga que cualquier tiempo adicional para la operación 1 por encima de su

capacidad actual de 430 minutos por día deba hacerse con base en tiempo extra a $50 por hora. El costo por hora incluye tanto la mano de obra como la operación de

la máquina. ¿Es económicamente ventajoso utilizar tiempo extra con la operación 1? Respuesta. Sí, porque el ingreso adicional por min=$1 (hasta por 10 minutos de tiempo extra) excede el costo adicional de $.83/min.

b) Suponga que el encargado de la operación 2 ha acordado trabajar 2 horas de tiempo extra diarias a $45 por hora. Adicionalmente, el costo de la operación propiamente dicha es de $10 por hora. ¿Cuál es el efecto neto de esta actividad en el ingreso diario?

Respuesta. El ingreso adicional es de $2/min (por hasta 400 min de tiempo extra) = $240 por 2 horas. Costo adicional por 2 horas 5 $110. Ingreso neto 5 $130. c) ¿Es necesario el tiempo extra para la operación 3? Programación Lineal (8206)



Prof. José Rangel

Respuesta. No, su precio dual es cero porque el recurso ya es abundante. d) La compañía desea conocer ¿cuál será el máximo margen de ganancia por cada producto, manteniendo constante el plan de producción actual?

Respuesta. Para los trenes de juguete máximo 7 $, para los camiones 10 $ y para los autos, sin límite máximo.

Importancia del Análisis de Sensibilidad: Uno de los aspectos más importantes de la modelización utilizando la programación

lineal, es el análisis de los parámetros del modelo en base a alteraciones en el mundo real. Cuando se formula un modelo matemático de programación lineal, se utilizan situaciones reales que son tomadas como punto de partida para ser transformadas en un lenguaje

formal, con el fin de permitir encontrarle, mediante métodos algorítmicos como es el caso del método simplex, una solución óptima, que satisfaga, restricciones. Estas situaciones del

mundo real pueden ser diversas, entre las cuales se encuentran los problemas de mezclas

de componentes para la realización de algún producto, de producción artículos, finanzas, logística etc., de esta manera un modelo matemático basado en cualquiera de las situaciones descritas no se mantiene estático en lo que a la práctica se refiere, por lo que

el análisis de sensibilidad es una herramienta importantísima, que proporciona intervalos que establecen la permanencia de la solución óptima si el problema se encuentra sujeto a variaciones de diferente índole, como lo son las producidas a causa de la inflación, el

aumento o disminución en la disponibilidad de recursos y/o requerimientos, los cambios en

las composiciones de cierto producto, etc, es por ello que el estudio de los valores obtenidos

en el resultado final ayudaran a un mejor enfoque para la toma de decisiones que satisfagan a las necesidades de cualquier organización o empresa.



Analisis de Sensibilidad Usando Excel Solver

Recommend Documents