2014-04-222014187Clase_Solver

INTRODUCCIÓN AL SOLVER DE EXCEL

CONSTRUCCIÓN DE MODELOS DE OPTIMIZACIÓN PARA LA GESTIÓN

JAIME MIRANDA([email protected])

Facultad de Economía y NegociosUniversidad de Chile

¿Para quPara quPara quPara qué sirve el solver de excel?sirve el solver de excel?sirve el solver de excel?sirve el solver de excel?

Sirve para resolver problemas de optimización de carácterSirve para resolver problemas de optimización de carácterSirve para resolver problemas de optimización de carácterSirve para resolver problemas de optimización de carácter→ LinealLinealLinealLineal→ Entero y binariosEntero y binariosEntero y binariosEntero y binarios→ MixtosMixtosMixtosMixtos→ No linealesNo linealesNo linealesNo lineales

Solver es parte de una serie de comandos a veces denominadosSolver es parte de una serie de comandos a veces denominadosSolver es parte de una serie de comandos a veces denominadosSolver es parte de una serie de comandos a veces denominadosherramientas de análisis.herramientas de análisis.herramientas de análisis.herramientas de análisis.herramientas de análisis.herramientas de análisis.herramientas de análisis.herramientas de análisis.

Existe una celda objetivo que en el problema se desea minimizar o Existe una celda objetivo que en el problema se desea minimizar o Existe una celda objetivo que en el problema se desea minimizar o Existe una celda objetivo que en el problema se desea minimizar o maximizar.maximizar.maximizar.maximizar.

Se deben definir variables de decisión y restricciones que relacionan las Se deben definir variables de decisión y restricciones que relacionan las Se deben definir variables de decisión y restricciones que relacionan las Se deben definir variables de decisión y restricciones que relacionan las Variables de decisiónVariables de decisiónVariables de decisiónVariables de decisión

Elementos bElementos bElementos bElementos básicos del solversicos del solversicos del solversicos del solver

Celda objetivoCelda objetivoCelda objetivoCelda objetivo→Permite definir la función objetivo del problema de optimización.Permite definir la función objetivo del problema de optimización.Permite definir la función objetivo del problema de optimización.Permite definir la función objetivo del problema de optimización.

→Siempre se quiere aumentar o disminuir la celda objetivoSiempre se quiere aumentar o disminuir la celda objetivoSiempre se quiere aumentar o disminuir la celda objetivoSiempre se quiere aumentar o disminuir la celda objetivo

Celdas combinantesCeldas combinantesCeldas combinantesCeldas combinantesSon las celdas de la hoja de cálculo que podemos cambiar o Son las celdas de la hoja de cálculo que podemos cambiar o Son las celdas de la hoja de cálculo que podemos cambiar o Son las celdas de la hoja de cálculo que podemos cambiar o → Son las celdas de la hoja de cálculo que podemos cambiar o Son las celdas de la hoja de cálculo que podemos cambiar o Son las celdas de la hoja de cálculo que podemos cambiar o Son las celdas de la hoja de cálculo que podemos cambiar o ajustar para optimizar la celda objetivoajustar para optimizar la celda objetivoajustar para optimizar la celda objetivoajustar para optimizar la celda objetivo

RestriccionesRestriccionesRestriccionesRestricciones→Las restricciones son delimitaciones que se aplican a las celdas Las restricciones son delimitaciones que se aplican a las celdas Las restricciones son delimitaciones que se aplican a las celdas Las restricciones son delimitaciones que se aplican a las celdas

cambiantes cambiantes cambiantes cambiantes

¿CCCCómo encuentro el solver de excel?mo encuentro el solver de excel?mo encuentro el solver de excel?mo encuentro el solver de excel?En el menú “Datos” de Excel, haga clic en Solver.

Si el comando Solver no aparece en el menú Herramientas, deberá instalar el complemento o macro automática Solver

ParParParParámetros del solvermetros del solvermetros del solvermetros del solver

Especifica la celda objetivo que se desea maximizar o minimizar. La celda objetivo debe contener una fórmula. Corresponde a la función objetivo del modelo

Especifica si se desea maximizar o minimizar la celda objetivo, o bien definirla con un valor específico


Especifica las celdas que pueden ajustarse hasta que se satisfagan las restricciones en el problema. Estas celadas corresponden a las variables de decisión.


Especifica las restricciones que tendrá el problema. Son celdas que relacionan las variables de decisión o celdas combinantes.

Borra todas las relaciones y valores actuales del problema.

MMMMás opciones para el Solvers opciones para el Solvers opciones para el Solvers opciones para el Solver…

Limita el tiempo máximo para el proceso que busca la solución del problema. Puede introducirse un valor tan grande como 32.367, pero el valor predeterminado 100 (segundos).

Limita el número de cálculos utilizados para encontrar una solución al problema. Aunque puede introducirse un valor tan grande como 32.767, el valor predeterminado 100 iteraciones.


Controla la precisión numérica de las soluciones.

Esta opción es solo valida si el problema es entero, binario o mixto. Es la diferencia porcentual etre la solución óptima del problema relajado con la mejor solución entera encontrada. Una tolerancia mayor tiende a acelerar el proceso de solución. Sin embargo la calidad de la solución es menor.

MMMMÁS OPCIONES PARA EL SOLVERS OPCIONES PARA EL SOLVERS OPCIONES PARA EL SOLVERS OPCIONES PARA EL SOLVER…

Selecciónelo para acelerar el proceso de solución cuando todas las relaciones en el modelo sean lineales y desee resolver un problema de optimización lineal.

Selecciónelo para utilizar la escala automática cuando haya grandes diferencias de magnitud entre los parámetros del modelo; por ejemplo, cuando se maximiza el porcentaje de beneficios basándose en inversiones de millones de dólares.


Selecciónelo para que Solver deje de mostrar temporalmente los resultados de cada iteración. No recomendado ya que aumenta los tiempos computacionales


Impone que todas las variables de decisión (celdas combinantes) son todas mayores o iguales a cero. Si no se utiliza esta opción, en el caso que sea necesario, se debe introducir en la sección de ingreso de restricciones.

Resultados arrojados por el SolverResultados arrojados por el SolverResultados arrojados por el SolverResultados arrojados por el Solver

Solver ha encontrado una soluciónSolver ha encontrado una soluciónSolver ha encontrado una soluciónSolver ha encontrado una solución→ La solución encontrada ha satisfecho todas las restricciones y condiciones del problema.La solución encontrada ha satisfecho todas las restricciones y condiciones del problema.La solución encontrada ha satisfecho todas las restricciones y condiciones del problema.La solución encontrada ha satisfecho todas las restricciones y condiciones del problema.

→ Se ha encontrado una solución con valor valor máximo o mínimo local para la celda objetivo. Se ha encontrado una solución con valor valor máximo o mínimo local para la celda objetivo. Se ha encontrado una solución con valor valor máximo o mínimo local para la celda objetivo. Se ha encontrado una solución con valor valor máximo o mínimo local para la celda objetivo.

Solver ha llegado a la solución óptimaSolver ha llegado a la solución óptimaSolver ha llegado a la solución óptimaSolver ha llegado a la solución óptima→ Todas las restricciones y condiciones se han satisfecho.Todas las restricciones y condiciones se han satisfecho.Todas las restricciones y condiciones se han satisfecho.Todas las restricciones y condiciones se han satisfecho.

→ El cambio relativo en la celda objetivo es menor que el valor de convergencia en el El cambio relativo en la celda objetivo es menor que el valor de convergencia en el El cambio relativo en la celda objetivo es menor que el valor de convergencia en el El cambio relativo en la celda objetivo es menor que el valor de convergencia en el cuadro de diálogo Opciones de Solver. cuadro de diálogo Opciones de Solver. cuadro de diálogo Opciones de Solver. cuadro de diálogo Opciones de Solver.

PlanificaciPlanificaciPlanificaciPlanificación de la produccin de la produccin de la produccin de la producción de automóvilesn de automóvilesn de automóvilesn de automóviles

Considere que la empresa de automóviles Considere que la empresa de automóviles Considere que la empresa de automóviles Considere que la empresa de automóviles FerrariFerrariFerrariFerrari desea desea desea desea estimar su pool de producción para sus 4 productos estimar su pool de producción para sus 4 productos estimar su pool de producción para sus 4 productos estimar su pool de producción para sus 4 productos estrella.estrella.estrella.estrella.

→ FERRARI 360 FERRARI 360 FERRARI 360 FERRARI 360 FERRARI 1260 FERRARI 1260 FERRARI 1260 FERRARI 1260

→ FERRARI 500FERRARI 500FERRARI 500FERRARI 500 FERRARI 6000 FERRARI 6000 FERRARI 6000 FERRARI 6000


Para la construcción de estos dos modelos la empresa Para la construcción de estos dos modelos la empresa Para la construcción de estos dos modelos la empresa Para la construcción de estos dos modelos la empresa tiene que utilizar los siguientes recursos:tiene que utilizar los siguientes recursos:tiene que utilizar los siguientes recursos:tiene que utilizar los siguientes recursos:

→ Fibra de vidrio (m2)Fibra de vidrio (m2)Fibra de vidrio (m2)Fibra de vidrio (m2)

→ Cortadoras (horasCortadoras (horasCortadoras (horasCortadoras (horas----máquina)máquina)máquina)máquina)

→ Pintura (horasPintura (horasPintura (horasPintura (horas----hombre)hombre)hombre)hombre)→ Pintura (horasPintura (horasPintura (horasPintura (horas----hombre)hombre)hombre)hombre)

Fibra de vidrio Cortadoras Pintura MODELO (m2) (horas-máquina) (horas-hombre) FERRARI 360 4,0 1,5 2,0

FERRARI 1260 2,5 1,0 1,2

FERRARI 500 3,0 2,0 1,5

FERRARI 6000 1,8 0,8 0,8

Tabla 1: Recursos necesarios para la construcción de los autos


Considere que la empresa cuenta con el siguiente stock de Considere que la empresa cuenta con el siguiente stock de Considere que la empresa cuenta con el siguiente stock de Considere que la empresa cuenta con el siguiente stock de recursos:recursos:recursos:recursos:

Fibra de vidrio (m2) 800

Cortadoras (horas-máquina) 400Pintura (horas-hombre) 300

Suponga que la empresa tiene vendidas o con compromiso Suponga que la empresa tiene vendidas o con compromiso Suponga que la empresa tiene vendidas o con compromiso Suponga que la empresa tiene vendidas o con compromiso de entrega de entrega de entrega de entrega

→ 60 unidades del modelo Ferrari 126060 unidades del modelo Ferrari 126060 unidades del modelo Ferrari 126060 unidades del modelo Ferrari 1260→ 20 unidades del modelo Ferrari 50020 unidades del modelo Ferrari 50020 unidades del modelo Ferrari 50020 unidades del modelo Ferrari 500

Pintura (horas-hombre) 300

PROBLEMA NPROBLEMA NPROBLEMA NPROBLEMA Nº 1: PLANIFICACI1: PLANIFICACI1: PLANIFICACI1: PLANIFICACIÓN DE LA N DE LA N DE LA N DE LA PRODUCCIPRODUCCIPRODUCCIPRODUCCIÓNNNNConsidere los siguientes costos y precios de venta Considere los siguientes costos y precios de venta Considere los siguientes costos y precios de venta Considere los siguientes costos y precios de venta unitarios para cada modelo (Miles de dólares):unitarios para cada modelo (Miles de dólares):unitarios para cada modelo (Miles de dólares):unitarios para cada modelo (Miles de dólares):

MODELO PRECIO COSTOFERRARI 360 120 80FERRARI 1260 80 50FERRARI 500 100 75FERRARI 6000 60 30FERRARI 6000 60 30

¿Cuál es el problema de la empresa?¿Cuál es el problema de la empresa?¿Cuál es el problema de la empresa?¿Cuál es el problema de la empresa?

Se desea determinar un plan de producción óptimo para el Se desea determinar un plan de producción óptimo para el Se desea determinar un plan de producción óptimo para el Se desea determinar un plan de producción óptimo para el período tal que maximice el beneficio total de la empresa y período tal que maximice el beneficio total de la empresa y período tal que maximice el beneficio total de la empresa y período tal que maximice el beneficio total de la empresa y se cumpla con los compromisos de entrega adquiridos, se cumpla con los compromisos de entrega adquiridos, se cumpla con los compromisos de entrega adquiridos, se cumpla con los compromisos de entrega adquiridos, suponiendo que todo lo que se produce se vende.suponiendo que todo lo que se produce se vende.suponiendo que todo lo que se produce se vende.suponiendo que todo lo que se produce se vende.

AsignaciAsignaciAsignaciAsignación de personas a tareasn de personas a tareasn de personas a tareasn de personas a tareas

Una empresa desea saber cuál es la asignación óptima de Una empresa desea saber cuál es la asignación óptima de Una empresa desea saber cuál es la asignación óptima de Una empresa desea saber cuál es la asignación óptima de Las siguientes personas para un conjunto de tareasLas siguientes personas para un conjunto de tareasLas siguientes personas para un conjunto de tareasLas siguientes personas para un conjunto de tareas

1 2 3 4 5

A 3 6 9 10 3

B 8 9 8 4 5

C 5 5 5 2 9

D 10 15 5 7 8

E 9 4 9 8 9

TAREAS

PE

RS

ON

AS

Selección de proyectos del área de procesosSelección de proyectos del área de procesosSelección de proyectos del área de procesosSelección de proyectos del área de procesos

Una empresa desea qué proyectos del área de procesos Una empresa desea qué proyectos del área de procesos Una empresa desea qué proyectos del área de procesos Una empresa desea qué proyectos del área de procesos debo seleccionar para maximizar el beneficio total, debo seleccionar para maximizar el beneficio total, debo seleccionar para maximizar el beneficio total, debo seleccionar para maximizar el beneficio total, considerando un presupuesto de 13 UM.considerando un presupuesto de 13 UM.considerando un presupuesto de 13 UM.considerando un presupuesto de 13 UM.

Presupuesto 13Presupuesto 13

Ide Costo del proyecto Beneficio del proyecto

1 4 7

2 5 8

3 2 10

4 3 7

5 4 4

6 3 7

7 3 4

8 5 6

9 2 3

10 5 6

36

INTRODUCCIÓN AL SOLVER DE EXCEL

MODELOS DE OPTIMIZACIÓN PARA LA GESTIÓN

JAIME MIRANDA([email protected])

Departamento de Control de Gestión y Sistemas de InformaciónUniversidad de Chile

2014-04-222014187Clase_Solver

Documents

Transcript of 2014-04-222014187Clase_Solver