Programación Lineal Con Solver de Excel

36

sección 1

ESTRATEGIA

capítulo 2A

PROGRAMACIÓN LINEAL LINE AL UTILIZANDO SOLVER DE EXCEL SUMARIO 37

Introducción Definición de programación lineal

38

Modelo de la programación lineal

39

Programación lineal gráﬁca Definición de programación lineal gráfica

41

Programación lineal utilizando Excel de Microsoft

L

A 2 o l u t í p a c

a clave de las operaciones rentables consiste en aprovechar al máximo los recursos disponibles de personas, materiales, planta, equipo y dinero. Hoy en día, el administrador tiene a su alcance

una potente herramienta en la programación lineal que le permite hacer modelos matemáticos. En este capítulo se demostrará que el uso de Solver de Excel de Microsoft para solucionar problemas de la PL le abre todo un mundo nuevo al administrador innovador y, a aquellos que piensan hacer carrera de asesores, les proporciona un valioso elemento más que podrán sumar a su conjunto de habilidades técnicas. En este capítulo, se utiliza un problema de planeación de productos para explicar cómo se usa esta herramienta. Se encontrará la mezcla óptima de productos que requieren diferentes recursos y tienen distintos costos. Por supuesto que el problema es relevant relevantee para el mercado me rcado competitivo de hoy.

36

Las compañías verdaderamente exitosas ofrecen una mezcla de productos que van desde los modelos

www.FreeLibros.com

The world’s largest digital library

Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.









PROGRAMACIÓN LINEAL UTILIZANDO SOLVER DE EXCEL

37

capítulo 2A

estándar hasta los de d e lujo de las clases altas. Todos ellos compiten por utilizar la producción que es limitada y otras capacidades. La emp resa que mantiene la mezcla correcta de estos productos a lo largo del tiempo podría elevar sustancialmente sus ganancias y el rendimiento de sus ac tivos. Se inicia el capítulo con una breve explicación de la programación lineal y de las condiciones en las que se puede pued e aplicar la técnica. A continuación, se resolverá un problema simple de la mezcla de produ ctos. A lo largo del libro aparecen otras aplicaciones de la p rogramación lineal.

→

INTRODUCCIÓN La programación lineal (o PL) se re�ere a varias técnicas matemáticas utilizadas para par a asignar, en forma óptima, los recursos limitados a distintas demandas que compiten por ellos. La PL es el más popular de los enfoques que caben dentro del título general de técnicas matemáticas para la optimización y se ha aplicado a muchos problemas de la administración de operaciones. Algunas aplicaciones típicas son: Planeación de operaciones y ventas agregadas: encontrar el programa de producción que tenga el

costo mínimo. El problema radica en preparar un plan para un u n periodo de entre tres y seis meses que, dadas las limitantes de la capacidad de producción esperada y el tamaño de la fuerza de trabajo, satisfaga la demanda esperada. Los costos relevantes considerados en el problema incluyen incluyen los salarios salar ios para el trabajo regular y las horas extra, las contrataciones y los despidos, la subcontratación y el costo de manejo de inventarios. Análisis Anális is de la producti productividad vidad en la produc producción/ ción/servic servicios ios:: considerar el grado de e�ciencia con el cual los establecimientos establecimient os de servicios y de manufactura están utilizando utiliza ndo sus recursos en comparación con la unidad que tiene mejor desempeño. Para ello se utiliza un enfoque llamado análisis envolvente de datos. Planeación de los productos: encontrar la mezcla óptima de productos, considerando que varios productos requieren diferentes recursos y tienen distintos costos. Algunos ejemplos son encontrar la mezcla óptima de elementos químicos para la gasolina, las pinturas, pintur as, las dietas humanas humana s y el alimento para animales. an imales. Este capítulo cubre algunos ejemplo ejemploss de este problema. Rutas de los productos: encontrar el camino óptimo para fabricar un producto que debe ser procesado en secuencia, pasando por varios centros de maquinado, donde cada máquina del centro tiene sus propios costos y características de producción. pa ra utilizar recursos recu rsos como aviones, Programación de vehículos/cuadrillas: encontrar la ruta óptima para autobuses o camiones y las cuadrillas que los tripulan para ofrecer servicios de transporte a clientes y llevar los materiales que se transportarán entre diferentes plazas. Control de procesos: minimizar el volumen de desperdicio de material generado cuando se corta acero, cuero o tela de un rollo o de una lámina de material. Control de inventarios: encontrar la combinación óptima de productos que se tendrán en existencia dentro de una red de almacenes o centros de almacenamiento. Programación de la distribución: encontrar el programa óptimo de embarques para distribuir los productos entre fábricas y almacenes o entre almacenes y detallistas. Estudios para ubicar la planta: pla nta: encontrar la ubicación óptima para una nueva planta evaluando los costos de embarque entre plazas alternativas y las fuentes de suministro y de demanda. Manejo de materiales: m ateriales: encontrar las rutas que impliquen el costo mínimo para el manejo de materiales y máquinas (como grúas) entre los departamentos de una planta o transportar materiales de un patio de almacén a los lugares de trabajo, por ejemplo, por medio de camiones. Cada camión podría

Programación lineal (PL)









38

sección 1

ESTRATEGIA

Para que una situación plantee un problema de programación lineal debe cumplir cumpli r con cinco condiciones básicas. En primer término, debe tener recursos limitados (como una cantidad limitada de trabajadores, equipamiento, dinero y materiales), porque de lo contrario no habría problema. En segundo, debe tener un objetivo explícito (como maximizar la utilidad o minimizar el costo). En tercero, debe existir (dos es el doble de uno; es decir, si se necesitan tres horas para hacer una pieza, entonces dos linearidad (dos piezas tomarían seis horas y tres t res piezas, nueve). nueve). En cuarto, debe existir homogeneidad (los (los productos fabricados en una máquina son idénticos o todas las horas que trabaja un obrero son igual de productivas). En quinto, debe existir divisibilidad : la programación lineal normal presupone que los productos y los recursos se pueden subdividir en fracciones. Si la subdivisión no es posible (como un vuelo con medio avión o la contratación de un cuarto de persona) se puede utilizar una modi�cación de la programación lineal llamada programación entera. Cuando el objetivo único es maximizar (por ejemplo las utilidades) o minimizar (por ejemplo, los costos), se puede utilizar la programación lineal. Cuando existen varios objetivos, entonces se utiliza la programación por metas. Si un problema se resuelve mejor por etapas o plazos de tiempo, entonces se utiliza la programación dinámica d inámica.. Otras restricciones debidas a la naturaleza del problema tal vez requieran que se resuelva utilizando otras variantes de la técnica, como la programación no lineal o la programación cuadrática. cuadrática .

MODELO DE LA PROGRAMACIÓN LINEAL En términos formales, el problema de la programación lineal entraña un proceso de optimización en el cual se eligen valores no negativos para una u na serie de variables va riables de la decisión X 1, X 2,..., X n de modo que se maximice (o minimice) una función objetivo con la fórmula: Maximizar (minimizar) Z = C 1 X 1 + C 2 X 2 + … + C n X n sujeto a las restricciones de los recursos con la fórmula: A11 X 1 + A12 X 2 + … + A1n X n < B1 A21 X 1 + A22 X 2 + … + A2n X n < B2 • • •

Am1 X 1 + Am2 X 2 + … + Amn X n < Bm

donde C n, Amn y Bm son constantes dadas. Dependiendo del problema, las restricciones se pueden expresar con signo de igualdad ( =) o con signo de mayor o igual que (>).

EJEMPLO 2A.1: Puck and Pawn Company Se describen los pasos para la solución de un modelo simple de programación lineal en el contexto de un problema de muestra: el caso de Puck and Pawn Company, fabricante de bastones de hockey y juegos de ajedrez. Cada bastón de hockey produce una utilidad incremental de $2 y cada juego de ajedrez una de $4. La fabricación de un bastón requiere 4 horas de trabajo en el centro de maquinado A y 2 horas en el centro de










39

capítulo 2A

SOLUCIÓN Plantee el problema en términos matemáticos. Si H es es el número de bastones de hockey y C es es el número de juegos de ajedrez, para maximizar maxim izar la utilidad la función fu nción objetivo objetivo se puede expresar como: Maximizar Z = $2 H + $4C La maximización estará sujeta a las restricciones siguientes: 4 H + 6C ≤ 120 (restricción del centro de maquinado A) 2 H + 6C ≤ 72 (restricción del centro de maquinado B) 1C ≤ 10 (restricción del centro de maquinado C) H , C ≥ 0

•

Este planteamiento cumple con los cinco requisitos de una PL estándar mencionados en la primera sección de este capítulo: 1. Los recursos son limitados (un número �nito de horas disponibles en cada centro de maquinado). 2. Hay una función objetiv objetivoo explícita (se conoce el valor de cada variable y la meta para resolver el problema). 3. Las ecuaciones son lineales (no hay exponentes ni productos cruzados) 4. Los recursos son homogéneos (todo se ajusta a una unidad de medida: las horas-máquina). 5. Las variables de la decisión son divisibles y no negativ negativas as (se puede fabricar una fracción de bastón de hockey o de juego de ajedrez, pero si se considerara que no es deseable, entonces se tendría que utilizar la programación entera).

PROGRAMACIÓN LINEAL GRÁFICA Si bien la aplicación de la programación lineal grá�ca se limita a problemas que incluyen dos variables en la decisión (o tres variables en el caso de grá�cas tridimensionales tr idimensionales), ), la programación lineal gráfica proporciona una visión inmediata de la índole de la programación lineal. Se describirán los pasos que implica el método grá�co en el contexto de Puck and Pawn Company. Company. Los pasos que se presentan a continuación ilustran el enfoque grá�co: 1. Plantee el problema en términos matemáticos. Las ecuaciones para el problema presentadas antes. 2. Trace las ecuaciones de las restricciones. Las ecuaciones de las restricciones se pueden trazar fácilmente si se deja que una variable sea igual a cero y se resuelve la intersección del eje de la otra. (En este paso no se consideran las fracciones de desigualdad de las restricciones.) En el caso de la ecuación de la restricción del centro de maquinado A, cuando H = 0, C = 20 y cuando C = 0, H = 30. En el caso de la ecuación de la restricción del centro de maquinado B, cuando H = 0, C = 12, y cuando C = 0, H = 36. En el caso de la ecuación de la restricción del centro de maquinado C, C = 10 para todos los valores de H . La ilustración 2A.1 presenta una grá�ca con estas líneas. 3. Determine el área de factibilidad. La dirección de los los signos signos de desigualdad desigualdad de cada restricción determina el área donde se encuentra una solución factible. En este caso, todas las desigualdades

Programación lineal gráfica









40

ilustración 2A.1

ESTRATEGIA

sección 1

Gráfica del problema de los bastones de hockey y los juegos de ajedrez

30 Zona infactible 4 H + + 6C = = 120 (1) 20 Juegos 16 de ajedrez por día 12 10 8

Líneas de la función objetivo

2 H + + 4C = = $64 2 H + + 4C = = $32

= 10 (3) =

C

(3) (2)

4

a

Zona factible

Óptimo 2 H + + 6C = = 72 (2) (1)

10

16 20 24 Bastones de hockey por día

30

32 32

36

4. Trac Tracee la función objetivo. La función objetiv objetivoo se puede trazar trazar suponiendo una cifra arbitraria para la utilidad total y, a continuación, resolviendo la ecuación con el �n de conocer las coordenadas del eje, como se hizo en el caso de las restricciones. Otros términos de la función objetivo objetivo cuando se usan en este contexto son la isoutilidad o línea de contribución igual, porque muestra todas las combinaciones posibles de la producción para una cifra de utilidad dada. Por ejemplo, si se toma la línea punteada más próxima al origen de la grá�ca, se pueden determinar todas las combinaciones posibles de bastones de hockey y de juegos de ajedrez que rinden 32 dólares eligiendo un punto en la línea y leyendo el número de cada producto que se puede fabricar en ese punto. Las combinaciones que producen 32 dólares en el punto a sería 10 bastones de hockey y tres juegos de ajedrez. Se puede constatar lo anterior sustituyendo = 10 y C = = 3 en la función objetivo: H = $2(10) + $4(3) = $20 + $12 = $32 H

C

0 120/44 = 30 120/ 0 72/2 = 36 0 0 32/2 = 16 0 64/2 32

120/6 120/ 6 = 20 0 72/6 = 12 0 10 32/44 = 8 32/ 0 64/44 = 16 64/ 0

E�� intersección de restricción (1) y eje C intersección de restricción (1) y eje H intersección de restricción (2) y eje C intersección de restricción (2) y eje H intersección de restricción (3) y eje C intersección de línea de isoutilidad $32 (función objetivo) y eje C intersección de línea de isoutilidad $32 y eje H intersección de línea de isoutilidad $64 y eje C intersección de línea de isoutilidad $64 eje H










capítulo 2A

Al sustituir C = 10 en 2 H + 6C = 72 se tendrá que 2 H + 6(10) = 72, 2 H = 12, o H = 6. Si se sustituye H = 6 y C = 10 en la función objetivo se tendrá: Utilidad = $2 H + $4C = $2(6) + $4(10) = $12 + $40 = $52 Una variante de este enfoque es leer las cantidades de H y y C directamente directamente en la grá�ca y sustituirlas en la función objetivo, como muestra el cálculo anterior. El inconveniente de este enfoque es que en problemas que tienen un número considerable de ecuaciones de restricción habrá muchos puntos posibles que se deban evaluar y el procedimiento de comprobar cada uno en términos matemáticos no es e�ciente. El segundo enfoque, generalmente preferido, entraña utilizar directamente la función objetivo, o línea de isoutilidad, para encontrar el punto óptimo. El procedimiento implica simplemente simplemente trazar una línea recta paralela a una línea de isoutilidad, elegida de forma arbitraria, de modo que la línea de isoutilidad es la más alejada del origen de la grá�ca. (En problemas de minimización de costos, el objetivo sería trazar la línea por el punto más cercano al origen.) En la ilustración 2A.1, la línea punteada marcada $2 H + $4C = $64 intersecta el punto más distante. Advierta Advierta que la línea de isoutilidad inicial escogida arbitrariamente es necesaria para presentar la pendiente de la función objetivo del problema particular. particular. 1 Esto es importante porque una función objetivo diferente (pruebe utilidad = 3 H + 3C ) podría indicar que algún otro punto está más lejos del origen. Dado que $2 H + $4C = $64 es óptimo, el monto de cada variable para producir se puede leer en la grá�ca: 24 bastones de hockey y cuatro juegos de ajedrez. Ninguna otra combinación de productos produce una utilidad mayor.

PROGRAMACIÓN LINEAL UTILIZANDO EXCEL DE MICROSOFT Los problemas de programación lineal se pueden resolver utilizando hojas de cálculo. Excel de Microsoft cuenta con un instrumento relacionado con la optimización que se llama Solver y cuyo uso se demostrará resolviendo el problema de los bastones de hockey y los juegos de ajedrez. Se llama a Solver en la Barra de datos. Un cuadro de diálogo solicita la información que requiere el programa. El ejemplo siguiente describe cómo resolver el problema de muestra utilizando Excel. Si la opción Solver no aparece en su Barra de datos, haga clic en Opciones de Excel → Agregar, seleccione Agregar Agregar Solver y haga clic en Aceptar. Solver quedará disponible directamente en la Barra de datos para uso futuro. En el ejemplo siguiente se trabaja paso por paso, primero preparando una hoja de cálculo y después resolviendo el problema de Puck and Pawn Company. La estrategia básica es primero de�nir el problema dentro de la hoja de cálculo. A continuación se llama a Solver y se le alimenta la información requerida. Por último, se ejecuta Solver y se interpretan los resultados de los informes que presenta el programa.

Paso 1: Defina las celdas cambiantes Un punto conveniente conveniente para iniciar es identificar identificar las celdas que

se utilizarán para las variables de la decisión del problema. Se trata de H y y C , el número de bastones de hockey y el número de juegos de ajedrez que se producirán. En Solver, Excel se refiere a estas celdas como celdas cambiantes. Con relación a la pantalla de Excel (ilustración 2A.2), se ha designado la B4 como la ubicación para el número de bastones de hockey y la C4 para el número de juegos de ajedrez que se producirán. Advierta que, inicialmente, estas celdas están marcadas igual a 2. Se podría colocar cualquier valor en estas celdas, pero es aconsejable usar uno que no sea cero par a que ayude a comprobar que los cálculos están correctos.

Paso 2: Calcule la utilidad total (o el costo) Ésta es la función objetivo objetivo y se calcula multiplicando multiplicando

41











42

ilustración 2A.2

sección 1

ESTRATEGIA

Pantalla de Excel de Microsoft para el caso de Puckand Pawn Company

PL Solver Microsoft Excel Inicio Inser tar

Despl. pág

Fórmulas

Datos

Repaso

Ver Solver

Obtener datos externos

Refrescar todo

O rd rd en ena r

Conexiones

Fi ltlt ra ra r

Herr. datos

Esbozo

Ordenar y �ltrar

Bastones de hockey Jue go go s d e a jed re rez

Análisis

To ta ta l

Celdas cambiantes

Excel: PL Solver

Utilidad

Recursos Bastones de hockey Juegos de ajedrez

Usados

Capacidad

Máquina A Máquina B Máquina C

Bastones de hockey y juegos de ajedrez

Paso 3: Establezca el uso de recursos Los recursos son los los centros de maquinado A, B y C, como se definieron en el problema original. Se han establecido tres filas (9, 10 y 11) en la hoja de cálculo, una para cada restricción de los recursos. En el centro de maquinado A se emplean 4 horas de tiempo de procesamiento para producir cada bastón de hockey (celda B9) y 6 horas para cada juego de ajedrez (celda C9). Para una solución particular, el total del recurso del centro de maquinado A utilizado se calcula en D9 (B9*B4 + C9*C4). En la celda E9 se ha indicado que se quiere que este valor sea menor a la capacidad de 120 horas del centro de maquinado A, que está asentado en F9. El uso de recursos de los centros de maquinado B y C se anota exactamente de la misma manera en las filas 10 y 11.

Paso 4: Prepare Solver Va Vaya ya a la Barra de datos y seleccione seleccione la opción Solver Solver..










capítulo 2A

4. Sujetas a las siguientes siguientes restricciones: corresponde corresponde a la capacidad del centro centro de maquinado. Ahí se hace clic en Agregar y se indica que el total utilizado de un recurso es menor o igual a la capacidad disponible. A continuación se presenta un ejemplo para el centro de maquinado A. Haga clic en Aceptar después de especi�car cada restricción. Agregar Restricción Referencia de la celda:

Aceptar

Restricción:

Cancelar

Agregar

Ayuda

5. Un clic en Opciones permite indicar indicar a Solver qué tipo de problema problema se desea resolver y cómo cómo se desea solucionar. Solver tiene muchas opciones, pero aquí sólo se usarán unas cuantas. A continuación se muestra la pantalla: Agregar Restricción Tiempo:

segundos

Aceptar

Iteraciones:

Cancelar

Precisión:

Cargar modelo...

Tolerancia: To lerancia:

Guardar modelo...

Convergencia:

Ayuda

Convergencia:

Usar escala automática

Asumir no negativos

Mostrar resultado de iteraciones

Estimación

Derivadas

Lineal

Progresivas

Cuadrática

Centrales

Hallar por Newton Gradiente conjugado

La mayor parte de las opciones se re�eren a la manera en que Solver trata de solucionar problemas no lineales, los cuales pueden ser muy difíciles de resolver y las soluciones óptimas son difíciles de encontrar. Por fortuna el problema es lineal. Esto se sabe porque las restricciones y la función objetivo se pueden calcular utilizando ecuaciones lineales. Haga clic en Adoptar modelo lineal para indicar a Solver que se desea utilizar la opción de la programación lineal para resolver el problema. Además, se sabe que las celdas cambiantes (variables de la decisión) deben ser números mayores o igual a cero, porque

43









44

sección 1

ilustración 2A.3

Informes de respuestas y sensibilidad de Solver de Excel

ESTRATEGIA

Informe de respuestas

C�� (��) C��

N��

$D$5

Utilidad total

V��

V��

$12

$64

V��

V��

C�� C��

N��

$B$4

Celdas cambiantes bastones hockey

2

24

$C$4

Celdas cambiantes juegos ajedrez

2

4

R�� C��

N��

V��

F��

$D$11

E��

M��

Usando máquina C

4

$D$11<=$F$11

No vinculante

6

$D$10

Usando máquina B

72

$D$10<=$F$10

Vinculante

0

$D$9

Usando máquina A

120

$D$9<=$F$9

Vinculante

0

Informe de sensibilidad

�� V��

C�� N�� $B$4

$C$4

Celdas cambiantes bastones de hockey Celdas cambiantes juegos ajedrez

C��

C�� C��

I�� I�� D�� D��

24

0

2

0.666666667

0.666666667

4

0

4

2

1

R�� V��

P��

R�� .�.

I��

D��

C��

N��

$D$11

Usando máquina C

4

0

10

1E+30

6

$D$10

Usando máquina B

72

0.333333333 0.33333 3333

72

18

12










capítulo 2A

45

con el mismo cambio de 0.33 dólares para cada unidad de la función objetivo. En el caso del centro de maquinado C, el lado derecho podría incrementar al in�nito (1E+30 es una notación cientí�ca para una cifra muy alta) o disminuir a 4 unidades sin cambio alguno en la función objetivo.

VOCABULARIO BÁSICO Programación lineal (PL) Se re�ere a varias técnicas matemáticas utilizadas para asignar, en forma óptima, recursos limitados entre demandas que compiten por ellos.

Programación lineal gráfica Proporciona una visión rápida de la naturaleza de la programación lineal.

PROBLEMAS RESUELTOS PROBLEMA PR OBLEMA RESUELTO R ESUELTO 1 Una mueblería elabora tres productos: mesas, sofás y sillas. Estos productos son procesados en cinco departamentos: el de serrado de madera, el de corte de tela, el de lijado, el de entintado y el de montaje. Las mesas y las sillas sólo llevan madera, y los sofás llevan madera y tela. Se requiere mucho pegamento e hilo y éstos representan un costo relativamente insigni�cante que queda incluido en el gasto de operaciones. Los requerimientos especí�cos de cada producto son los siguientes: R�� (�� )

R��

R��

R��

Madera (4 350 pies de tablones) Tela (2 500 yardas)

10 pies de tablones a $10/pie = $100/ mesa Ninguno

4 pies de tablones a $10/pie = $40 Ninguna

Serrar la madera (280 horas) Cortar la tela (140 horas) Lijar (280 horas) Entintar (140 horas) Montar (700 horas)

30 minutos Ninguno 30 minutos 24 minutos 60 minutos

7.5 pies de tablones a $10/pie = $75 10 yardas a $17.50/yarda $17.50/ yarda = $175 24 minutos 24 minutos 6 minutos 12 minutos 90 minutos

30 minutos Ninguno 30 minutos 24 minutos 30 minutos

Los gastos de trabajo directo de la compañía suman 75 000 dólares por mes por concepto de las 1 540 horas de trabajo, a 48.70 dólares por hora. Basándose en la demanda actual, la empresa puede vender 300 mesas, 180 sofás y 400 sillas por mes. Los precios de venta son 400 dólares para las mesas, 750 dólares para los sofás y 240 dólares para las sillas. Suponga que el costo de mano de obra es �jo y que, durante el próximo mes, la empresa no proyecta contratar ni despedir des pedir a empleados. Se desea saber: 1. ¿Cuál es el recurso más limitante para la compañía mueblera? 2. Determine la mezcla de productos necesaria para maximizar la utilidad de la compañía mueblera. ¿Cuál es el número óptimo de mesas, sofás y sillas que debe producir por mes?









46

sección 1

ESTRATEGIA

Paso 1: Establezca las celdas cambiantes

Éstas son B3, C3 y D3. Advierta que estas celdas han sido

establecidas en igual a cero.

Compañía mueblera Mesas

Sofás

Sillas

Total

Límite

Celdas Cambiantes Utilidad Madera Tela Serrar Cortar tela Lijar Entintar Montar Demanda mesas Demanda sofás Demanda sillas Problema resuelto

Listo

Ésta es E4 (es igual a B3 multiplicado por 300 dólares del ingreso asociado a cada mesa, más C3 multiplicado por 500 dólares del ingreso por cada sofá, más D3 multiplicado por 200 dólares del ingreso asociado a cada silla). Advierta que, para calcular la utilidad, el gasto �jo de 75 000 dólares se ha restado del ingreso. Paso 2: Calcule la utilidad total

En las celdas que van de la E6 a la E15, el uso de cada recurso se calcula multiplicando B3, C3 y D3 por el monto que se necesita para par a cada artículo y sumando el producto (por ejemplo, E6 = B3*B6 + C3*C6 + D3*D6). Los límites de estas restricciones están anotados en e n las celdas que van de la F6 a la F15. Paso 3: Establezca el uso de recursos

Solver.. Paso 4. Establezca Solver Vaya a Herramientas y seleccione la opción Solver Parámetros de Solver Celda objetivo: Valor de

Máximo Celdas cambiantes

Resolver Mínimo

Valores de d e:

Cerrar Estimar

Sujetas a las siguientes restricciones: restricciones:

Opciones Agregar












capítulo 2A

Aquí hay muchas opciones, pero para los propósitos pr opósitos que se buscan sólo se necesita señalar Adoptar modelo lineal y Asumir no negativos. Adoptar modelo lineal signi�ca que todas las fórmulas son simples ecuaciones lineales. Asumir no negativos indica que las celdas cambiantes deben ser mayores o iguales a cero. Con un clic en Aceptar estará listo para resolver el problema. Paso 5: Marque las opciones

Agregar Restricción Tiempo:

segundos

Aceptar Cancelar

Iteraciones: Precisión:

Cargar modelo...

Tolerancia: To lerancia:

Guardar modelo...

Convergencia:

Ayuda

Adoptar modelo lineal:

Usar escala automática

Asumir no negativos Estimación Derivadas

Mostrar resultado de iteraciones

Lineal

Progresivas

Cuadrática

Centrales

Hallar por Newton Gradiente conjugado

Haga clic en Resolver. Se puede ver la solución y dos informes especiales resaltando los elementos en el reconocimiento de Resultados de Solver que aparece después de que se encuentra una solución. Advierta Advierta que en el informe siguiente, Solver indica que ha encontrado una solución y que se han cumplido todas las restricciones y las condiciones de optimalidad. En el cuadro de Informes a la derecha, las opciones Respuestas, Sensibilidad y Límites han sido resaltadas, indicando así que se desea ver estos elementos. Tras resaltar los informes, haga clic en Aceptar para regresar a la hoja Paso 6: Resuelva el problema

47









48

sección 1

ESTRATEGIA

Restricciones

C��

N��

$E$6 $E$7 $E$8 $E$9 $E$10 $E$11 $E$12 $E$13 $E$14 $E$15

Total madera Total tela Total serrar Total cortar tela Total lijar Total entintar Total montar Total demanda mesas Total demanda sofás Total demanda sillas

V�� 3 950 1 800 202 72 148 140 530 260 180 0

F��

E��

$E$6 <=$F$6 $E$7<=$F$7 $E$8 <=$F$8 $E$9 <=$F$9 $E$10 <=$F$10 $E$11<=$F$11 $E$12<=$F$12 $E$13<=$F$13 $E$14<=$F$14 $E$15<=$F$15

No vinculante No vinculante No vinculante No vinculante No vinculante Vinculante No vinculante No vinculante Vinculante No vinculante

M�� 400 700 78 68 132 0 170 40 0 400

Por supuesto que esta solución podría no complacer demasiado dado que no se está satisfaciendo toda la demanda de mesas y podría no ser aconsejable descontinuar del todo la producción de sillas. El Informe de sensibilidad (que se presenta a continuación) brinda más información de la solución. La sección de las Celdas cambiantes de este informe muestra el valor �nal de cada celda y el costo reducido. Éste indica cuánto cambiaría el valor de la Celda objetivo si una celda que estuviera actualmente establecida en cero fuera introducida a la solución. Dado que las mesas (B3) y los sofás (C3) están en la solución actual, su costo reducido es cero. Por cada silla (D3) que se fabrique, la Celda objetivo disminuiría 100 dólares (sólo redondee estas cifras para efectos de interpretación). Las tres columnas �nales de la sección de celdas ajustables son el Coe�ciente objetivo de la hoja de cálculo original y las columnas c olumnas tituladas Incremento permitido y Decremento permitido. Estas dos muestran cuánto cambiaría el valor del coe�ciente correspondiente de modo que no cambiaran los valores de las celdas cambiantes (por supuesto que el valor de la Celda objetivo cambiaría). Por ejemplo, el ingreso por cada mesa podría ser tan alto como 1 000 dólares (300 + 700 dólares) o tan bajo como 200 dólares (300 − 100 dólares) y no obstante se querrían producir 260 mesas. Recuerde que estos valores presuponen que no hay ningún otro cambio en el problema. En el caso










capítulo 2A

entintado, con un valor de 750 dólares por hora. Por otro lado, la columna Decremento permitido muestra la cantidad en que se podría disminuir el recurso sin cambiar el precio sombra. El informe presenta algo de información valiosa. El Informe de límites proporciona más información acerca de la solución. C��

N��

V��

$E$4

Totall utilidad Tota

$93 000

C�� N��

V��

L��

R��

L�� L��

R�� R��

$B$3

Celdas cambiantes mesas

260

0

15 000

260.00000 02

93 000

$C$3

Celdas cambiantes sofás

180

0

3 000

180

93 000

$D$3

Celdas cambiantes sillas

0

0

93 000

0

93 000

La utilidad total de la solución actual es de 93 000 dólares. El valor actual de B3 (mesas) es de 260 unidades. Si se redujera a 0 unidades, la utilidad bajaría bajaría a 15 000 dólares. En el límite superior de 260, la utilidad es de 93 000 dólares (la solución actual). Por otro lado, para C3 (sofás), si se redujera a 0, la utilidad bajaría a 3 000 dólares. En el límite superior de 180, la utilidad es 93 000 dólares. Para D3 (sillas), si se redujera a 0, la utilidad es 93 000 dólares (solución actual) y en este caso, el límite superior de las sillas también es de 0 unidades. Las respuestas aceptables a las preguntas son las siguientes: 1. ¿Cuál es el recurso más limitante para la compañía mueblera? En términos de los los recursos de producción, la capacidad capacidad de entintado está afectando realmente la utiliutilidad en este momento. Se podrían utilizar otras 16 horas de capacidad. 2. Determin Determinee la mezcla mezcla de pro productos ductos que que se necesita necesita para para maximizar maximizar la utilidad de la compañía compañía muebler mueblera. a. La mezcla de productos sería fabricar 260 mesas, 180 sofás y ninguna silla.

49









50

sección 1

ESTRATEGIA

Joe Bob quiere maximizar el ingreso porque ya ha comprado todos los materiales, los cuales están en el congelador. Se desea saber: 1. ¿Cuál es la mejor mezcla de los especiales del viernes por la noche para maximizar el ingreso de Joe Bob? 2. Si un proveedor ofreciera surtir un pedido extra de panes a $1.00 la pieza, ¿vale la pena invertir ese dinero?

Solución De�na X 1 como el número de hamburguesas con queso, X 2 como el número de albóndigas, X 3 como el número de tacos y X 4 como el número de porciones de picadillo que se prepararán para los especiales del viernes. Ingreso = $2.25 X 1 + $2.00 X 2 + $1.75 X 3 + $2.50 X 4 Las restricciones son las siguientes: Carne molida: 0.30 X 1 + 0.25 X 2 + 0.25 X 3 + 0.40 X 4 < 100 Queso: 0.10 X 1 + 0.30 X 3 + 0.20 X 4 < 50 Frijoles: 0.20 X 3 + 0.30 X 4 < 50 Lechuga: 0.10 X 1 + 0.20 X 3 < 15 Tomate: 0.10 X 1 + 0.30 X 2 + 0.20 X 3 + 0.20 X 4 < 50 Panes: X 1 + X 2 < 80 Tortillas: X 3 < 80 Demanda Hamburguesa con queso X 1 < 75 Albóndigas X 2 ≤ 60 Tacos X3 ≤ 100










capítulo 2A

Paso 4: Establezca Solver y seleccione la opción de Solver Parámetros de Solver Celda objetivo: Valor de Máximo Celdas cambiantes:

Resolver Mínimo

Valores de:

Cerrar Estimar

Sujetas a las siguientes restricciones:

Opciones Agregar Cambiar Restablecer Eliminar Ayuda

objetivo: se establece en la ubicación donde se calcula el valor que se desea optimizar. El a) Celda objetivo: ingreso se calcula en F7 en esta hoja de cálculo. celda objetivo: objetivo: se establece en Máximo Máximo porque el objetivo objetivo es maximizar maximiza r el ingreso. b) Valor de la celda c) Celdas Cambiantes: son las que indican la cantidad de cada platillo que se debe producir. siguientes restricciones: es donde se añaden dos restricciones separadas, una para la d ) Sujeto a las siguientes demanda y otra para el uso de los alimentos. Agregar Restricción Referencia de la celda

Restricción

51











52

sección 1

ESTRATEGIA

Haga clic en Resolver. Aparecerá el recuadro de Resultados de Solver. Asegúrese que dice lo siguiente: “Solver encontró una solución. Todas las restricciones y condiciones de optimización están satisfechas”. Paso 6: Resuelva el problema

Resultados de Solver Solver encontró una solución. Todas las restricciones Informes y condiciones de optimalidad están satisfechas. Utilizar solución de Solver

Respuestas Sensibilidad Límites

Restaurar valores originales Aceptar

Cancelar

Guardar escenario...

Ayuda

En el lado derecho del cuadro, aparece una opción para tres informes: Respuestas, Sensibilidad y Límites. Haga clic en los tres informes y después haga clic en Aceptar, esto le volverá a llevar a la hoja de










capítulo 2A

Celdas ajustables

V��

C��

N��

$B$3

Celdas cambiantes Hamburguesa s con queso Celdas cambiantes albóndigas Celdas cambiantes tacos Celdas cambiantes picadillo

$C$3 $D$3 $E$3

C��

C��

I��

D��

20

0

2.25

0.625

1.375

60 65 55

0.625 0 2.5

2 1.75 2.5

1E+30 2.75 1E+30

0.625 1.25 2.5

Restricciones

C��

N��

V��

P��

$F$11 $F$12 $F$13 $F$14

Total carne molida (lbs.) Totall queso (lbs.) Tota Total frijoles (lbs.) Totall lechuga (lbs.) Tota

59.25 35.50 29.50 15.00

0.00 0.00 0.00 8.75

R�� LD 100 50 50 15

I�� 1E+30 1E+30 1E+30 3

D�� 40.75 17.5 20.5 13

53









54

sección 1

ESTRATEGIA

Las horas máquina requeridas por unidad son:

P�� T��

X 1

X 2

X 3

Molino Torno Trituradora

8 4 2

2 3 0

3 0 1

El tiempo disponible de horas máquina por semana es: H�� Molinos Tornos Trituradoras

800 480 320

Los vendedores estiman que podrán vender todas las unidades de X 1 y X 2 que se fabriquen. Pero el potencial de ventas de X es cuando mucho de 80 unidades por semana.










capítulo 2A

7. Quiere preparar un presupuesto que optimice el uso de una fracción de su ingreso disponible. Cuenta con un máximo de 1 500 dólares al mes para asignar a comida, vivienda y entretenimiento. La cantidad que gaste en alimento y vivienda juntos no debe pasar de 1 000 dólares. La cantidad que gaste sólo en vivienda no puede pasar de $700. El entretenimiento no puede pasar de $300 al mes. Cada dólar que gaste en comida tiene un valor de satisfacción de 2, cada dólar que gaste en vivienda tiene un valor de satisfacción de 3 y cada dólar que gaste en entretenimiento tiene un valor de satisfacción de 5. Suponiendo una relación lineal, utilice Solver de Excel para determinar la asignación óptima de sus fondos. 8. La cervecería C-town produce dos marcas: Expansion Draft y Burning River. River. Expansion Draft tiene un precio de venta de $20 por barril, mientras que Burning River tiene un precio de venta de 8 dólares por barril. La producción de un barril de Expansion Draft requiere 8 libras de maíz y 4 libras de lúpulo. La producción de un barril de Burning River requiere de 2 libras de maíz, 6 libras de arroz y 3 libras de lúpulo. La cervecería tiene 500 libras de maíz, 300 libras de arroz y 400 libras de lúpulo. Suponga una relación lineal y use Solver de Excel para determinar la mezcla óptima de Expansion Draft y Burning River que maximice el ingreso de C-town. 9. BC Petrol fabrica tres productos en su planta química en Kentucky: BCP1, BCP2 y BCP3. Estos productos se elaboran con dos procesos de producción llamados zona y hombre. La operación del proceso zona durante una hora cuesta 48 dólares y produce tres unidades de BCP1, una unidad de BCP2 y una

55

Programación Lineal Con Solver de Excel

Recommend Documents