ayudantia solver 1

Introducción al Solver de Excel

ICS1113 Optimización

1

Introducción al Solver

¿Qué es el Solver de Excel?

2

• Herramienta computacional para encontrar la mejor manera de asignar recursos escasos. • Resuelve problemas de optimización lineales y no lineales, con y sin dominio restringido.

• Permite variables de decisión reales, enteras y binarias.

• Optimiza utilizando hojas de cálculo de Excel.


¿Qué tipo de problemas resuelve? • Financieros y de inversión:

• Gestión de capital de trabajo. • Presupuesto de capital. • Optimización de portafolios.

• Manufactura: • Programación de trabajos en taller. • Mezcla óptima de insumos.

• Distribución y redes: • Ruteo óptimo. • Carga óptima. • Calendarización óptima.

3


¿Cómo se define un modelo?

• Variables de decisión: Cantidad de recursos a ser usados para algún propósito o actividad.

• Restricciones: Límites en la disponibilidad de los recursos.

• Función objetivo: Función de las variables de decisión que se debe optimizar.

4


• ¿Cuáles son sus limitaciones (para Excel 2003, al menos)? Solver maneja problemas con un máximo de:

• 200 variables de decisión.

• 100 restricciones explícitas.

• 400 restricciones simples.

(Con cotas superior e inferior o restricciones enteras sobre las variables de decisión).

5


• ¿Qué tipo de algoritmos usa? • Problemas Lineales

• Método Simplex.

6

Problema lineal con restricciones: Simplex busca inteligentemente la solución sólo en las intersecciones de las restricciones.


• ¿Qué tipo de algoritmos usa? • Problemas No Lineales

• Método del Gradiente Reducido Generalizado.

7

C.I.

Problema no lineal sin restricciones: El GRG parte de una condición inicial dada y busca su camino al óptimo por pasos tomando la dirección de máximo crecimiento (decrecimiento) del gradiente hasta encontrar el máximo (mínimo) de la función objetivo.

Activación del Solver

¿Cómo activa el Solver en Excel 2003?

8

1. Ir a Herramientas en el menú de Excel. Luego ir a Complementos.



9

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



10

3. Ahora Solver estará activado y listo para ejecutarse en el menú Herramientas de Excel.



11

1. Ir a Botón de Office (parte superior izquierda). Luego ir a Opciones de Excel.



12

2. Ir a Complementos y luego a Ir.



13

3. Seleccionar la opción Solver y luego Aceptar.

4. Solver quedará activado en el menú Datos (en la parte superior derecha de dicho menú).

Interfaz del Solver

14

Ubicación de la celda de la función objetivo

Ubicación de las celdas de las variables

de decisión

Conjunto de restricciones

Max o Min

Resolver el modelo

Opciones del Solver

Agregar o cambiar restricciones

Interfaz del Solver

15

Ubicación de las celdas de

restricciones

Tipo de restricción

Fórmula, referencia a

celdas o valores

Agregar las restricciones

Interfaz del Solver

16

Utiliza Simplex para problemas lineales

Opciones del Solver

Límite de tiempo y nº de iteraciones

Grado de exactitud que deben cumplir las

restricciones

% de tolerancia de solución óptima para

Problemas Enteros (0% encuentra la solución

óptima)

El problema converge si la diferencia de las últimas 5

iteraciones es menor a este valor

Variables o celdas cambiantes toman sólo valores positivos o cero

Para ajustar automáticamente problemas mal condicionados o mal escalados

Muestra iteraciones paso a paso

Cómo calcula el algoritmo la no linealidad

Opciones para problemas no lineales

Cómo calcula el algoritmo las tasas de cambio

Técnica de búsqueda utilizada (N. + memoria, G.C. + Iteraciones)

Interfaz del Solver

Mensaje de Finalización de Solver

17

Genera los informes que se especifiquen y los coloca en hojas independientes.

Reemplaza los valores originales, si es que habían, con la solución encontrada con Solver. Guarda los valores encontrados para utilizarlos en

el Administrador de escenarios.

Tipos de Celdas Excel

• Existen 3 tipos de celdas: – Celdas Fijas: Poseen un valor o un dato fijo.

– Celdas Variables: Pueden tomar distintos valores según lo

requiera el algoritmo de optimización. Se declaran en “Cambiando Celdas”.

– Celdas Función: Su valor está asociado a relaciones de otras celdas. Sirven para ingresar función objetivo y restricciones.

18

Ingreso de Variables y Funciones

19

Celda Función Objetivo

• Se ingresa la función

• Entrega valor óptimo

Celda Variable

• Se ingresa condición inicial

• Entrega solución óptima

Ingreso de Variables y Funciones

20

Celda Objetivo

Celda Variable (Condición inicial)

Maximizar

Informes del Solver

• Informe de Respuestas: Indica los valores iniciales como finales para las celdas variables y la función objetivo. Además indicará el estado de las restricciones, si están en Holgura o están Activas.

• Informe de Sensibilidad: Indica el valor del gradiente reducido para todas las variables (cuanto empeora la FO con el aumento en la variable) y además muestra el valor del multiplicador de Lagrange asociado a cada restricción.

• Informe de Límites: Indica el valor del límite inferior y superior que puede tomar una variable, dejando el valor de las demás constantes e igual al valor óptimo, además indica el valor de la función objetivo en estos límites.

Mas información, en los links de interés al final de esta presentación.

21

Mensajes de Finalización de Solver

• Mensaje de Finalización cuando solver ha encontrado una solución.

22

• Solver ha llegado a la solución actual. Todas las restricciones se han satisfecho.

• Solver ha encontrado una solución. Se han satisfecho todas las restricciones y condiciones.

Solución actual no es óptima, se necesita mejor nivel de precisión en las opciones del Solver.

Mensajes de Finalización de Solver

Cuando Solver no encuentra una Solución.

23

• Solver no puede mejorar la solución actual. Todas las restricciones se han satisfecho.

• Cuando se ha alcanzado el límite de tiempo, se ha seleccionado Detener.

• Cuando se ha alcanzado el límite máximo de iteración, se ha seleccionado Detener.

• Los valores de la celda objetivo no convergen

• No se han satisfecho las condiciones para Adoptar modelo lineal.

• Solver ha encontrado un valor de error en una celda objetivo o restringida.

Falta Aumentar Precisión

Límite de Tiempo

Límite de iteraciones

Divergencia

Modelo mal definido

Modelo o restricciones mal definidas

Ejemplo 1

24

• Compañía manufacturera que produce 3 productos distintos. • Cada producto requiere 3 operaciones: ensamblado, pulido y

empaquetado. • Datos de las utilidades ($) por producto y tiempos por

operación (min.).

Producto Utilidades Ensamblado Pulido Empaquetamiento 1 1,5 2 3 2 2 2,5 4 2 3 3 3,0 3 3 2 4 4,5 7 4 5

• Se disponen de 100.000 min. de tiempo de ensamblado, 50.000 min. de pulido y 60.000 min. de empaquetado.

• Encontrar el plan de producción que maximiza la utilidad.

Ejemplo 2

25

• Una empresa necesita fijar el precio de un producto que se venderá en 3 regiones distintas.

• El departamento de marketing ha entregado los datos de un estudio de demanda para distintos precios en las distintas regiones.

• Determinar el precio a fijar, de entre las opciones posibles, que maximiza la utilidad.

Ejemplo 3

26

• Una empresa manufacturera necesita programar la producción en cada una de sus plantas de manera de minimizar el costo de transporte del producto a cada zona, satisfaciendo la demanda.

• Los datos de las demandas en cada zona y el costo de transporte desde cada planta a cada zona son los siguientes:

• Determinar la cantidad a producir en cada planta para cada una de las zonas.

Ejemplo 4

27

• Una empresa necesita determinar la cantidad de avisos de publicidad a publicar en cada periódico nacional.

• Los datos de los costos y las audiencias que posee cada periódico se muestran a continuación:

• Además, se deben cumplir las siguientes restricciones:

Ejemplo 5

28

• Una empresa debe reorganizar su fuerza de trabajo en los próximos 3 años. Actualmente posee 3 tipos de trabajadores: poco entrenados, medianamente entrenados y altamente entrenados.

• La empresa posee los siguientes datos:

• Encuentre la reorganización de menor costo que cumpla con el número estimado de trabajadores requeridos.

• ¿Cómo cambiaría el problema para considerar los despidos?

Ejemplo 6

29

• Una empresa debe asignar días de trabajo a sus empleados, dado que cada uno de ellos tiene que trabajar sólo 5 días seguidos a la semana, teniendo derecho a dos días seguidos de descanso.

• La empresa debe cumplir con una demanda estimada por día dada por:

• Cada empleado le cuesta a la firma 40 dólares a la semana. • Encuentre la asignación de horarios (es decir, la cantidad de

empleados con un determinado horario semanal) que minimice el costo en salarios de la empresa.

Ejemplo 7

30

• Una empresa fabrica 5 productos y utiliza para ello 3 recursos, los cuales tienen disponibilidades máximas conocidas.

• Existe la posibilidad de dejar producto en inventario de un período al siguiente.

• Problema: Realizar la planificación de la producción de modo de minimizar los costos asociados.

• Datos: • bit : disponibilidad del recurso i en el periodo t. • aij : cantidad de recurso i consumida en la producción de una unidad de

producto j. • djt : demanda por el producto j en el período t. • cjt : costo de producir una unidad de j en el período t. • hj : costo por dejar una unidad del producto j de un mes al siguiente en

inventario. • Kjt: costo fijo Kjt de producir algo del producto j en el periodo t.

Ejemplo 7 (cont.)

31

• El modelo resultante es,

{ } Ttnjtj yTtnjtj ITtnjtjxTtnjtjMyxTtnjtjdxII

Ttmitibxaas

yKIhxcMin

jt

jt

jt

jtjt

jtjtjtjt

it

n

jjtij

T

t

n

jjtjt

T

t

n

jjtj

T

t

n

jjtjt

,...,1,,...,1;, 1,0

,...,1,,...,1;, 0

,...,1,,...,1;, 0

,...,1,,...,1;,

,...,1,,...,1;,

,...,1,,...,1;, ..

1

1

1 11 11 1

==∀∈

==∀≥

==∀≥

==∀≤

==∀−+=

==∀≤

++

−

=

= == == =

∑

∑∑∑∑∑∑

32

Links de interés

Funciones y opciones del Excel Solver http://mit.ocw.universia.net/15.053/s02/pdf/usingexcelsolver.pdf

Página oficial de Solver con tutoriales http://www.solver.com

Cómo buscar recursos en Microsoft Excel Solver http://support.microsoft.com/kb/146606/es Ayuda del solver de Excel 2010. http://www.solver.com/excel2010/solverhelp.htm

Informes del Solver explicados http://people.brunel.ac.uk/~mastjjb/jeb/or/lpsens_solver.html

http://mit.ocw.universia.net/15.053/s02/pdf/usingexcelsolver.pdf

http://www.solver.com/

http://support.microsoft.com/kb/146606/es

http://www.solver.com/excel2010/solverhelp.htm

http://people.brunel.ac.uk/~mastjjb/jeb/or/lpsens_solver.html

ayudantia solver 1

Documents