EAD350 II 2017 Aula5

Prévia do material em texto

EAD 350 
 Pesquisa Operacional 
Aula 05 
Prof. Hiroo Takaoka 
 
takaoka@usp.br 
FEA/USP 
Forma de Entrega do Trabalho 
• O trabalho deverá ser entregue via Moodle. 
• Grupo de até 3 alunos. 
• Apenas um aluno do grupo deverá enviar o trabalho. 
• Usar a primeira planilha para colocar as identificações (número 
USP e nome) completas dos membros do grupo inclusive a do 
aluno que vai enviar o trabalho. Chamar esta Planilha de Grupo. 
• Usar uma planilha para cada exercício como mostra a figura 
abaixo: 
 
 
Função SOMARPRODUTO - Definição 
Função SOMARPRODUTO 
Multiplica os componentes correspondentes nas 
matrizes fornecidas e retorna a soma destes produtos. 
Sintaxe 
SOMARPRODUTO(matriz1;matriz2) 
Comentários 
Os argumentos da matriz devem ter a mesma 
dimensão. Se não tiverem, SOMARPRODUTO 
fornecerá o valor de erro #VALOR!. 
SOMARPRODUTO trata as entradas da matriz não 
numéricas como se fossem zeros. 
Função SOMARPRODUTO - Exemplo 
A Função SOMARPRODUTO multiplica os componentes correspondentes 
das duas matrizes e depois soma os produtos ou seja, 
3x2 + 8x6 + 1x5 + 4x7 + 6x7 + 9x3 = 156 
B1* B2 + C1*C2 + D1*D2 + E1*E2 + F1*F2 + G1*G2 equivale 
Função SOMARPRODUTO - Exemplo 
Max Z = 2X1 + 6X2 + 5X3 + 7X4 + 7X5 + 3X6 
4X1 + 9X2 + 8X3 + 1X4 + 2X5 + 5X6 < 500 
LE LD 
Função Objetivo 
Restrições 
. . . 
. . . 
. . . 
. . . 
 LE – Lado Esquerdo 
LD – Lado Direito 
Função SOMARPRODUTO - Exemplo 
Z = 2X1 + 6X2 + 5X3 + 7X4 + 7X5 + 3X6 
B3*B2 + C3*C2 + D3*D2 + E3*E2 + F3*F2 + G3*G2 
Cálculo do valor de Z 
= 
Função SOMARPRODUTO - Exemplo 
4X1 + 9X2 + 8X3 + 1X4 + 2X5 + 5X6 < 500 
B6*B$2 + C6*C$2 + D6*D$2 + E6*E$2 + F6*F$2 + G6*G$2 
Cálculo do valor de LE 
LE LD 
Se as fórmulas copiadas contiverem referências relativas de célula, o Excel ajustará as 
referências (e as partes relativas das referências mistas de célula) nas fórmulas duplicadas. 
Por exemplo, suponha que a célula H6 contenha a fórmula: 
 =SOMARPRODUTO(B6:G6;B2:G2) 
Se você copiar a fórmula para a célula H7, a fórmula duplicada será ajustada às referências 
correspondentes nessa linha: 
 =SOMARPRODUTO(B7:G7;B3:G3) 
Se as fórmulas copiadas contiverem referências absolutas de célula ($), apenas as referências 
relativas nas fórmulas duplicadas serão ajustadas. As absolutas não serão alteradas. Por 
exemplo, suponha que a célula H6 contenha a fórmula: 
 =SOMARPRODUTO(B6:G6;B$2:G$2) 
Se você copiar a fórmula para a célula H7, as referências relativas serão ajustadas às 
referências correspondentes nessa linha (B7:G7) e as referências absolutas não serão 
alteradas (B$2:G$2). 
 =SOMARPRODUTO(B7:G7;B$2:G$2) 
Referência absoluta de célula: é o endereço exato de uma célula, 
independentemente da posição da célula que contém a fórmula. Uma 
referência de célula absoluta tem a forma $A$1, $A1 ou A$1. 
Referência Absoluta de Célula 
Atividade 5 – Exercício 5A - Resolver em Excel 
HOJE – Entregar pelo Moodle 
(Hillier e Lieberman, 2010) 
Modelo Matemático 
 
Função Objetivo 
 
Max Z (lucro)= 3X1 + 5X2 
 
 
Sujeito à (restrições): 
 
1X1 + 0X2 < 4 
0X1 + 2X2 < 12 
3X1 + 2X2 < 18 
X1, X2 > 0 
 
Variáveis Decisórias 
X1- Quantidade de Produto 1 
X2- Quantidade de Produto 2 
Wyndor Glass Co. 
• Preparar a planilha com os dados do modelo de PL. 
– Células para Rótulos (Alguns serão utilizados no Relatório de 
Sensibilidade) 
– Células para Variáveis de Decisão Xi 
– Célula para Variável Z (Função Objetivo) 
– Células para os coeficientes da Função Objetivo 
– Células para os coeficientes das Restrições 
– Células para lado esquerdo (LE) das Restrições 
– Células para lado direito (LD) das Restrições 
– Fórmula para calcular o valor de Z 
– Fórmulas para calcular o lado esquerdo (LE) das restrições 
Exercício 5A – Planilha Excel 
Exercício 5A – Planilha Excel 
Os rótulos destacados em vermelho serão utilizados no Relatório de 
Sensibilidade. 
Células para Rótulos 
Exercício 5A – Planilha Excel 
Variável Z 
Células para Variável Z (Função Objetivo) 
Exercício 5A – Planilha Excel 
Variáveis de 
Decisão 
Células para Variáveis de Decisão 
Exercício 5A – Planilha Excel 
Coeficientes da 
Função Objetivo 
Células para coeficientes da Função Objetivo 
Coeficientes das Restrições 
Exercício 5A – Planilha Excel 
Células para coeficientes das Restrições 
LE das Restrições 
Exercício 5A – Planilha Excel 
Células para LE das Restrições 
LD das Restrições 
Exercício 5A – Planilha Excel 
Células para LD das Restrições 
Fórmula para calcular o 
valor de Z 
Exercício 5A – Planilha Excel 
Fórmulas para calcular Z 
=B3*B4+C3*C4 
equivale 
Fórmulas para calcular 
LE das Restrições 
Exercício 5A – Planilha Excel 
Fórmulas para calcular LE das Restrições 
=B$3*B7+C$3*C7 
=B$3*B9+C$3*C9 
equivale 
=B$3*B8+C$3*C8 
equivale equivale 
• Chamar o SOLVER. 
– Selecionar a guia de comandos Dados da Faixa de Opções e, 
em seguida, selecionar o botão Solver (Esta sequência será 
representada por (Dados/Solver). 
Exercício 5A – Solver - Parâmetros 
Exercício 5A – Solver - Parâmetros 
Valor de Z 
Max 
Variáveis de Decisão 
Opções 
• Especificar os Parâmetros do Solver. 
– Definir célula de destino: Célula 
referente ao valor de Z 
– Igual a: Selecionar Max 
– Células variáveis: Células 
referentes a Variáveis de Decisão 
• Especificar as Restrições. 
– Dar clique no botão Adicionar para chamar a janela de 
Adicionar Restrições. 
Exercício 5A – Solver - Restrições 
Adicionar 
Exercício 5A – Solver - Restrições 
Valor do Lado 
Esquerdo (LE) 
Valor do Lado 
Direito (LD) 
– Após especificar as restrições, dar um clique no OK. 
• Especificar as Restrições. 
Na caixa Referência de Célula, 
marque a área correspondente ao 
lado esquerdo (LE) das restrições 
(D7:D9). 
Na caixa de dropdown para o tipo de 
restrição, selecione <=. 
Na caixa Restrições, marque a área 
correspondente ao lado direito (LD) 
das restrições (F7:F9). 
Exercício 5A – Solver - Opções 
• Especificar as Opções. 
– Dar clique em Opções para chamar a janela de Opções do 
Solver. 
– Presumir modelo linear: Sim 
– Presumir não negativo: Sim 
– Dar clique em OK. 
 
• Resolver o modelo de PL. 
– Dar clique em Resolver. 
 
 
Exercício 5A – Solver - Resolver 
Resolver 
Mensagem do 
resultado do 
Solver 
Exercício 5A – Solver - Resultado 
• No Resultado do Solver não esquecer de verificar se o Solver 
encontrou a solução. Solução 
Exercício 5A - Solver - Resultado 
Valor de Z Valores das Variáveis de 
Decisão 
Valores Finais 
da Restrição 
Exercício 5A – Solver 
Relatório de Sensibilidade 
• Selecionar o relatório (não é obrigatório). Solver vai criar uma 
planilha para cada relatório que for selecionado. 
 
 
Relatório de 
Sensibilidade 
Exercício 5A – Solver 
Relatório de Sensibilidade 
• Após clicar OK, surgirá uma nova aba referente a Relatório de 
Sensibilidade. 
 
 
Exercício 5A – Solver 
Relatório de Sensibilidade 
• Note que os rótulos foram usados no Relatório de Sensibilidade. 
Daí a importância de defini-los com termos significativos. 
 
Exercício 5A – Solver 
Relatório de Sensibilidade 
• Observe como o Solver usa os rótulos (Mesmo exemplo com 
rótulos diferentes). 
 
Relatório de Sensibilidade 
• Quadro de Células Variáveis 
– Célula 
Célula onde estão as variáveis. 
– Nome 
Nome das variáveis. 
– Reduzido Custo 
É o valorque o coeficiente da Função Objetiva 
deveria ser modificado para que o valor da variável 
seja diferente de zero, caso esta seja zero. (Como 
no nosso problema os valores são diferentes de 
zero para as duas variáveis, o Custo Reduzido não 
se aplica). 
Relatório de Sensibilidade 
• Quadro de Células Variáveis 
– Final Valor 
Valores finais de variáveis de decisão (xi). 
– Objetivo Coeficiente 
São os coeficientes (ci) da Função Objetivo. 
– Permitido Aumentar 
Acréscimo que pode ser feito no coeficiente da 
função objetivo sem mudar a solução ótima. 
– Permitido Reduzir 
Decréscimo que pode ser feito no coeficiente da 
função objetivo sem mudar a solução ótima. 
Relatório de Sensibilidade 
• Quadro de Restrições 
– Célula 
Célula onde estão as variáveis. 
– Nome 
Nome das variáveis. 
– Valor Final 
Valor efetivamente usado na solução do modelo. 
– Preço Sombra 
Mostra a taxa em que Z poderia ser aumentada 
elevando-se uma unidade o valor da restrição, 
mantidos os demais recursos constantes. 
Relatório de Sensibilidade 
• Quadro de Restrições 
– Restrição Lateral RH 
Valor do lado direito da restrição 
– Permitido Aumentar 
O preço sombra permanecerá válido até este 
acréscimo no valor da restrição. 
– Permitido Reduzir 
O preço sombra permanecerá válido até este 
decréscimo no valor da restrição. 
 
Solver – Excel 2010 - Parâmetros 
Selecionar o método 
LP Simplex 
Antes de dar clique 
no botão Resolver 
verifique na janela de 
opções se a opção 
Ignorar restrições de 
números inteiros está 
marcada (clique no 
botão Opções na 
janela de Parâmetros 
do Solver). 
 
Solver – Excel 2010 - Opções 
Verifique se a opção Ignorar 
restrições de números 
inteiros está marcada 
Atividade 5 – Exercício 5B - Resolver em Excel 
HOJE – Entregar pelo Moodle 
Uma empresa de engenharia está considerando o tempo disponível de 
máquinas para a produção de três produtos: 1, 2 e 3. As horas 
requeridas para cada unidade de produto e o tempo disponível em 
uma semana por máquina são: 
Máquina 1 2 3 
Tempo 
horas/semana 
Produto 
A 4 h 1 h 1,5 h 100 h 
B 2 h 1,5 h - 50 h 
C 1 h - 0,5 h 25 h 
Os produtos 1 e 2 podem ser vendidos em qualquer quantidade , mas o 
produto 3 pode ser vendido até no máximo 10 unidades por semana. O 
lucro unitário é de R$10, R$3 e R$4 para os produtos 1, 2 e 3 
respectivamente. Qual será o mix de produtos que a empresa deve 
fabricar para obter o lucro máximo? 
Exercício 5B - Modelo de PL 
Variáveis Decisão 
x1 = Quantidade Produto 1 
x2 = Quantidade Produto 2 
x3 = Quantidade Produto 3 
Função Objetivo 
 Max Z = 10x1 + 3x2 + 4x3 
Restrições 
 4,0x1 + 1,0x2 + 1,5x3 < 100 (Máquina A) 
 2,0x1 + 1,5x2 < 50 (Máquina B) 
 1,0x1 + 0,5x3 < 15 (Máquina C) 
 1,0x3 < 10 (Limite Venda Produto 3) 
 x1, x2, x3 > 0 
Exercício 5B - PLanilha 
Exercício 5B - Solver 
Exercício 5B - Resultado 
Atividade 5 – Exercício 5C - Resolver em Excel 
HOJE – Entregar pelo Moodle 
Sabe-se que os alimentos, leite, carne e ovo fornecem 
as quantidades de vitaminas dadas abaixo: 
Vitamina Leite (l) Carne (kg) Ovo (dz) 
Quantidade 
diária mínima 
A 0,25mg 2,00mg 10,00mg 1,00mg 
C 25,00mg 20,00mg 10,00mg 50,00mg 
D 2,50mg 200,00mg 10,00mg 10,00mg 
Custo 
unitário 
R$2,20/l R$17,00/kg R$4,20/dz 
Deseja-se calcular quais as quantidades de leite, carne e 
ovo, a fim de satisfazer as quantidades diárias mínimas 
de vitaminas a um custo mínimo. 
Exercício 5C- Modelo de PL 
Min Custo = 2,20x1 + 17,00x2 + 4,20x3 
xi 0 i =1, 2, 3 
Função Objetiva 
Restrições 
Variáveis Decisórias 
x1: quantidade diária de leite 
x2: quantidade diária de carne 
x3: quantidade diária de ovo 
 0,25x1 + 2,00x2 + 10,00x3 1,00 (Vitamina A) 
 25,00x1 + 20,00x2 + 10,00x3 50,00 (Vitamina C) 
 2,50x1 + 200,00x2 + 10,00x3 10,00 (Vitamina D) 
Exercício 5C - Resultado 
Petróleo 
Máxima 
quantidade 
disponível 
Custo 
unitário 
A 100 6 
B 200 3 
Gasolina 
Mínima 
% A 
requerida 
Preço de 
venda 
unitária 
1 60 8 
2 30 5 
Deseja-se saber a quantidade de cada gasolina que deve ser fabricada 
de tal maneira que o lucro seja máximo. 
Uma refinaria fabrica dois tipos de gasolina (1 e 2) a partir de dois tipos de 
petróleo bruto (A e B). 
Os custos, os preços de venda e matéria-prima para fabricar as gasolinas são: 
Atividade 5 – Exercício 5D - Resolver em Excel 
HOJE – Entregar pelo Moodle 
Exercício 5D- Modelo PL 
 Função Objetiva 
 Max L = 2XA1 -1XA2 +5XB1 + 2XB2 
 Restrições 
 XA1 + XA2 < 100 
 XB1 + XB2 < 200 
 0,4 XA1 - 0,6 XB1 > 0 
 0,7 XA2 - 0,3 XB2 > 0 
 Xij > 0 
xA1: quantidade de petróleo A p/ produzir gasolina 1 
xA2: quantidade de petróleo A p/ produzir gasolina 2 
xB1: quantidade de petróleo B p/ produzir gasolina 1 
xB2: quantidade de petróleo B p/ produzir gasolina 2 
Variáveis 
decisórias 
Exercício 5D - Resultado 
A empresa ABC está envolvida na preparação de medicamentos sofisticados que 
requerem o emprego de técnicos especializados. A empresa trabalha em turnos de 
oito horas cada, mas para haver continuidade no trabalho, a cada quatro horas, os 
técnicos são adicionados para trabalhar com as pessoas que já tenham 
completado quatro horas. Um técnico deve trabalhar continuamente por oito 
horas. Considerando o quadro abaixo, encontre a escala que minimiza a mão de 
obra a ser utilizada pela empresa. 
Período do dia Número mínimo necessário de 
técnicos 
02:00 às 06:00h 10 
06:00 às 10:00h 25 
10:00 às 14:00h 40 
14:00 às 18:00h 50 
18:00 às 22:00h 20 
22:00 às 02:00h 15 
Atividade 5 – Exercício 5E - Resolver em Excel 
HOJE – Entregar pelo Moodle 
Problema de Escala - ABC 
Período 2h 6h 10h 14h 18h 22h 
1 
2 
3 
4 
5 
6 
Numero mínimo 
de técnicos 
10 25 40 50 20 15 
X1 
X2 
X3 
X4 
X5 
X6 X6 
Exercício 5E - Modelo 
xi : número de técnicos adicionados no início do período i (i = 1 a 6) 
Problema de Escala - ABC 
 Função Objetivo 
 Min Z = 1X1 + 1X2 + 1X3 + 1X4 + 1X5 + 1X6 
 Sujeito a 
 X1 + X6 > 10 
 X1 + X2 > 25 
 X2 + X3 > 40 
 X3 + X4 > 50 
 X4 + X5 > 20 
 X5 + X6 > 15 
 X1 > 1 
 X2 > 1 
 X3 > 1 
 X4 > 1 
 X5 > 1 
 X6 > 1 
Número mínimo 
necessário de técnicos 
por período 
Pelo menos um 
técnico por período 
 
Variáveis de Decisão 
Xi : número de técnicos 
adicionados no início do 
período i (i = 1 a 6) 
Problema de Escala - ABC 
Exercício 5E – Modelo de PL 
Problema de Escala - ABC 
Exercício 5E - Resultado 
Uma fábrica é constituída por quatro centros de processamento S1, S2, S3 e 
S4 e produz três produtos finais F1, F2 e F3, cada um deles tendo apenas um 
processo de fabricação. O centro S1 recebe a matéria-prima, podendo 
processar, no máximo, K1 unidades a um custo unitário C1. Na saída do 
centro S1, é possível enviar o resultado do primeiro processamento, tanto 
para os centros S2 como S3. Os centros S2 e S3 têm custo unitário de 
processamento C2 e C3 e capacidades máximas K2 e K3, respectivamente. A 
saída do centro S2 pode constituir o produto final F1 ou servir de entrada 
para o centro S4. A saída S3 tem que obrigatoriamente, passar por S4. O 
centro S4 pode processar qualquer uma, ou ambas as entradas, com uma 
capacidade total de K4 unidades e um custo unitário de processamento, para 
qualquer entrada, de C4. As saídas de S4 resultarão nos produtos finais F2 e 
F3. Os preços unitários de venda são P1, P2 e P3.Utilizando como variáveis de decisão, o quanto fabricar de cada produto, 
resolva o problema de maximização do lucro como programação linear. 
P1=8, P2=12, P3=14; C1=4, C2=2, C3=1, C4=3; K1=90, K2=50, K3=30, 
K4=70 
Atividade 5 – Exercício 5F - Resolver em Excel 
HOJE – Entregar pelo Moodle 
Exercício 5F – Modelo 
S1 
S2 S3 
S4 
Matéria prima 
K1 = 90, C1 = 4 
K3 = 30, C3 = 1 K2 = 50, C2 = 2 
K4 = 70, C4 = 3 
F1 
P1 = 8 
F2 
P2 = 12 
F3 
P3= 14 
X1, X2 X3 
X3 X1 X2 
X3 X2 
P1=8, P2=12, P3=14 (Preço) 
K1=90, K2=50, K3=30, K4=70 (Capacidade) 
C1=4, C2=2, C3=1, C4=3 (Custo) 
Variáveis de decisão 
X1 quantidade do produto F1 
X2 quantidade do produto F2 
X3 quantidade do produto F3 
P1=8, P2=12, P3=14 (preço) 
C1=4, C2=2, C3=1, C4=3 (custo) 
K1=90, K2=50, K3=30, K4=70 (capacidade) 
Função objetiva 
Max Lucro = 8X1 + 12X2 + 14X3 – (4X1 + 2X1) - (4X2 + 2X2 + 3X2) - (4X3 + 1X3 + 3X3) 
 
 
 
 = 2X1+ 3X2 + 6X3 
 
Sujeito a 
X1 + X2 + X3 < 90 Centro de processamento S1 
X1 + X2 < 50 Centro de processamento S2 
 X3 < 30 Centro de processamento S3 
 X2 + X3 < 70 Centro de processamento S4 
X1, X2, X3 > 0 
Receita Custo F1 Custo F2 Custo F3 
Exercício 5F – Modelo de PL 
Exercício 5F – Resultado 
Um determinado investidor tem três opções de investimento, 
denominados A, B e C, disponíveis no próximo ano. Essas três opções não 
são mutuamente excludentes. Qualquer dinheiro recebido de qualquer 
opção poderá ser reinvestido, imediatamente, em qualquer uma das três 
opções. 
A opção A está disponível no princípio de cada um dos quatro trimestres 
seguintes. Cada real investido em A no princípio de um trimestre lhe 
devolve R$1,10 no final daquele trimestre. 
A opção B está disponível no princípio de cada um dos dois semestres 
seguintes. Cada real investido em B no princípio de um semestre lhe 
devolve R$1,20 no final daquele semestre. 
A opção C só está disponível no princípio do primeiro ano. Cada real 
investido em C lhe devolve R$1,40 um ano mais tarde. 
O capital inicial do investidor é de R$500.000,00. Deseja-se formular um 
modelo de programação linear para fornecer o plano de investimento que 
maximize a quantidade de dinheiro que o investidor pode acumular no 
final do próximo ano. 
(Sugestão: usar o modelo de fluxo de caixa) 
Atividade 5 – Exercício 5G - Resolver em Excel 
HOJE – Entregar pelo Moodle 
Exercício 5G – Modelo Fluxo de Caixa 
XA1 XA2 XA3 XA4 
XB1 XB3 
1,1XA1 1,1XA2 1,1XA3 1,1XA4 
1 2 3 4 5 
A 
B 
C 
1 2 3 4 5 
1 2 3 4 5 
1 2 3 4 5 
1,2XB1 1,2XB3 
XC1 
1,4XC1 
R1 R2 R3 R4 
R1 R2 R3 
Xij o valor investido na opção i (i = A, B, C) no início do trimestre j (j = 1, 2, 3, 4) 
Rj o valor não investido no início do trimestre j (j = 1, 2, 3, 4) 
500.000 
Não 
Investidos 
Saída 
Entrada 
Exercício 5G – Modelo de PL 
Função Objetiva 
Max Z = 1,1XA4 + 1,2XB3 + 1,4XC1 
Sujeito a 
XA1 + XB1 + XC1 + R1 = 500.000 XA1 + XB1 + XC1 + R1 = 500.000 
XA2 + R2 = R1 + 1,1XA1 XA2 + R2 - R1 - 1,1XA1 = 0 
XA3 + xB3 + R3 = R2 + 1,1XA2 + 1,2XB1 XA3 + XB3 + R3 - R2 - 1,1XA2 – 1,2XB1 = 0 
XA4 + R4 = R3 + 1,1XA3 XA4 + R4 - R3 - 1,1XA3 = 0 
Xij > 0 para todo i e j Xij > 0 para todo i e j 
Rj > 0 para todo j Rj > 0 para todo j 
 
Não poderia ser deixadas 
formuladas assim, pois 
infringe as regras de PL. 
Xij os valores investidos nas alternativas i (i = A, B, C) no início dos trimestres j (j = 1, 2, 3, 4) 
Rj os valores não investidos no início dos trimestres j (j = 1, 2, 3, 4) 
entradasaída
Exercício 5G – Resultado