sd 08 Aplicação do Solver Excel II

Prévia do material em texto

Pesquisa Operacional
Aplicação do Solver do Excel
• Problemas do Mundo Real
– Fazer ou comprar?
– Como investir?
– Escala de funcionários
– Problema de mistura
– Produção e estoque
– Fluxo de caixa multiperíodos
Conteúdo da Seção
• A LCL Liquidificadores Ltda recebeu recentemente um pedido para 
fornecimento de 5.500 unidades.
• Cada liquidificador necessita de um determinado número de horas de 
trabalho nos setores de montagem, teste e empacotamento. 
• A LCL pode terceirizar, a montagem ou o teste, de parte ou a totalidade 
de sua produção.
• A tabela a seguir resume essas informações necessárias para resolver o 
problema da necessidade de terceirização.
Fazer X Comprar
Caso LCL Liquidificadores Ltda.
Modelo 1 2 3 Capacidade
Demanda (unid) 2000 2000 1500
Montagem(h/unid) 1 2 0,5 6000 h
Teste(h/unid) 1 1 1 5000 h
Empacotamento 
(h/unid)
2,5 1 4 15000 h
Custo de Produzir (R$) 40 80 110
Custo de Terceirizar (R$) 60 90 140
• Variáveis de Decisão
– P1 – Qtde de liquidificadores do modelo 1 produzidos pela LCL
– P2 – Qtde de liquidificadores do modelo 2 produzidos pela LCL
– P3 – Qtde de liquidificadores do modelo 3 produzidos pela LCL
– T1 – Qtde de liquidificadores do modelo 1 terceirizados pela LCL
– T2 – Qtde de liquidificadores do modelo 2 terceirizados pela LCL
– T3 – Qtde de liquidificadores do modelo 3 terceirizados pela LCL
Caso LCL Liquidificadores Ltda.
• Função-objetivo – Custo Total
• Restrição – Capacidade de Montagem e de Teste
• Restrição – Capacidade de Embalagem
Caso LCL Liquidificadores Ltda.     1 2 3 1 2 3 40 80 110 60 90 140Min P P P T T T
  1 2 32 0,5 6000P P P  1 2 3 5000P P P
     1 1 2 2 3 32,5 2,5 4 4 15000P T P T P T
As quantidades terceirizadas não 
consomem as horas disponíveis de 
montagem e teste! 
As quantidades terceirizadas consomem 
as horas disponíveis de embalagem! 
• Quantidade total produzida do liquidificador do 
tipo 1:
• Quantidade total produzida do liquidificador do 
tipo 2:
• Quantidade total produzida do liquidificador do 
tipo 3:
• Condições de não-negatividade
Caso LCL Liquidificadores Ltda.
 1 1 2000P T
1 1 2 2 3 3, , , , , 0P T P T P T
 2 2 2000P T 3 3 1500P T
     
  
  
     
 
 
 

1 2 3 1 2 3
1 2 3
1 2 3
1 1 2 2 3 3
1 1
2 2
3 3
1 2 3 1 2 3
 40 80 110 60 90 140
. .
1 2 0,5 6000
1 1 1 5000
2,5 2,5 4 4 15000
 2000
 2000
 1500
, , , , , 0
Min Z P P P T T T
s r
P P P
P P P
P T P T P T
P T
P T
P T
P P P T T T
Caso LCL Liquidificadores Ltda. 
O Modelo Completo
Caso LCL Liquidificadores Ltda
modelo 1
Modelo 1
Parâmetros do Solver
Caso LCL Liquidificadores Ltda.
Resposta do Modelo 1
• Podemos criar uma estrutura alternativa no Excel, 
explorando as dimensões da planilha;
• Este novo modelo pode usar mais informações do 
negócio, e menos informações matemáticas.
Caso LCL Liquidificadores Ltda
Modelo 2
Caso LCL Liquidificadores Ltda
Modelo 2
Modelo 2
Parâmetros do Solver
Caso LCL Liquidificadores Ltda.
Resposta do Modelo 2
• A LCL Investimentos S.A.. gerencia
recursos de terceiros através da escolha
de carteiras de investimento para
diversos clientes, baseados em bonds de
diversas empresas. Um de seus clientes
exige que:
– Não mais de 25% do total seja
aplicado em um único investimento.
– Mais de 50% do total deve ser
aplicado em títulos de Bancos.
– O total aplicado em títulos de
telefonia deve ser no máximo de 30%
do total investido.
• A tabela ao lado mostra os dados dos
títulos selecionados
Caso LCL Previdência Privada S.A.
Opção
Retorno
Anual
Banco 1 9,7%
Banco 2 9,5%
Banco 3 10,0%
Telecom 1 8,0%
Telecom 2 9,0%
Telecom 3 10,0%
• Variáveis de Decisão
– B1 – Proporção do total aplicado no Banco 1
– B2 – Proporção do total aplicado no Banco 2
– B3 – Proporção do total aplicado no Banco 3
– T1 – Proporção do total aplicado no Telecom 1
– T2 – Proporção do total aplicado no Telecom 2
– T3 – Proporção do total aplicado no Telecom 3
• Cada variável assume um valor entre 0 e 1, sendo a 
representação decimal da proporção;
– Exemplo: 16% no Banco 1 significa B1 = 0,16
Caso LCL Previdência Privada S.A.
• Função Objetivo
• Restrição de Orçamento
• Restrições de Máximo de Aplicação em cada 
Título
Caso LCL Previdência Privada S.A.     1 2 3 1 2 30,097 0,095 0,010 0,080 0,090 0,010Max B B B T T T
     1 2 3 1 2 3 1B B B T T T
  
  
1 2 3
1 2 3
0,25 0,25 0,25
0,25 0,25 0,25
B B B
T T T
• Restrição de Mínimo de Aplicação em Título de 
Banco
• Restrição de Máximo de Aplicação em Títulos de 
Telecomunicação
• Condições de não negatividade
Caso LCL Previdência Privada S.A.
  1 2 3 50B B B
  1 2 3 30T T T 1 2 3 1 2 3, , , , , 0B B B T T T
Caso LCL Previdência Privada S.A.
Modelo
Caso LCL Previdência Privada S.A.
Parâmetros do Solver
Caso LCL Previdência Privada S.A.
Solução do Solver
• A LCL Shopping Ltda está se preparando para iniciar suas
atividades. Foi feita uma estimativa com o mínimo
seguranças por dia da semana:
• O sindicato dos seguranças mantém um acordo trabalhista
que determina que cada empregado deve trabalhar três
dias consecutivos por semana e que os shopping devem ter
apenas empregados em regime de horário integral (24hs).
• O shopping só abrirá de 2ª à sábado.
• Qual a quantidade mínima de empregados que a LCL
Shopping deve contratar de maneira a atender o acordo
sindical e as quantidades mínimas nos dias que irá
funcionar?
Caso LCL Shopping Ltda 
Dia da
Semana
Segunda Terça Quarta Quinta Sexta Sábado
Quantidade
Mínima
10 12 13 14 16 17
• Os funcionários serão agrupados segundo o dia 
da semana em que eles começam a trabalhar:
– N2 – qtde de funcionários que começam na segunda
– N3 – qtde de funcionários que começam na terça
– N4 – qtde de funcionários que começam na quarta
– N5 – qtde de funcionários que começam na quinta
• Veja que nenhum funcionário começa a trabalhar 
na sexta, no sábado ou no próprio domingo, pois 
o shopping não abre neste dia
Caso LCL Shopping Ltda
Variáveis de Decisão
• A tabela abaixo mostra quais funcionários estarão 
trabalhando em cada dia da semana:
Caso LCL Shopping Ltda
Variáveis de Decisão
Dia seg ter qua qui sex sab
N2 x x x
N3 x x x
N4 x x x
N5 x x x
Jornada dos 
funcionários que 
começam na 
segunda
Quais funcionários estão trabalhando na quarta
• Função Objetivo
• Restrições de quantidade mínima de funcionários 
por dia da semana:
Caso LCL Shopping Ltda.
Modelo Matemático    2 3 4 5 Min Z N N N N

 
  
  
 

2
2 3
2 3 4
3 4 5
4 5
5
10
12 
13
14 
16
17
N
N N
N N N
N N N
N N
N
segunda
terça
quarta
quinta
sexta
sábado
▪ Condições de não-
negatividade: 2 3 4 5, , , 0N N N N
Caso LCL Shopping Ltda.
Modelo no Excel
Caso LCL Shopping Ltda.
Parâmetros do Solver
Caso LCL Shopping Ltda.
Solução do Solver
Caso LCL Shopping Ltda.
Modelo 2
Como C3 = 1 e C4, C5 e C6 são 
iguais a zero, o resultado desta 
função é apenas o valor de N2
Como D3 e D4 são iguais a 
um e C5 e C6 são iguais a 
zero, o resultado desta 
função é igual a N2 + N3
Caso LCL Shopping Ltda. (modelo 2)
Parâmetros do Solver
Caso LCL Shopping Ltda. (modelo 2)
Solução do Solver
As duas formas de resolver 
apontaram para a mesma 
solução ótima.
• Ao suprimir as variáveis referentes aos 
funcionários que trabalhariam no domingo 
usamos um racional simplificador
• Senão usássemos, teríamos o mesmo resultado 
final:
Caso LCL Shopping Ltda.
Modelo 3