ExcelSolver

(1)

Pesquisa Operacional com

uso da Informática

(2)

Pesquisa Operacional

A Pesquisa Operacional é uma ciência aplicada voltada para a resolução de problemas reais. Tendo como foco a tomada de decisões, aplica conceitos e métodos de outras áreas científicas para concepção, planejamento ou operação de sistemas para atingir seus objetivos.

(3)

Multidisciplinaridade

Face ao seu caráter multidisciplinar, a Pesquisa Operacional é uma disciplina científica de características horizontais com suas contribuições estendendo-se por praticamente todos os domínios da atividade humana, da Engenharia à Medicina, passando pela Economia e a Gestão Empresarial.

(4)

Introdução

A utilização do ferramental da “Pesquisa Operacional” na promoção da eficiência e eficácia organizacional em todos os níveis da gestão é uma realidade tornada viável pelo microcomputador e pelo avanço do estado da arte.

(5)

Principais áreas

Dentro desse cenário torna-se cada vez mais crucial o pleno domínio desse ferramental, especialmente pelos profissionais das áreas de ciências exatas e de administração.

(6)

Alguns Problemas

• Problema do Caixeiro Viajante • Problema da Mochila

(7)

Problema do Caixeiro Viajante

O problema do caixeiro viajante é um dos mais tradicionais e conhecidos problemas de programação matemática. O desafio consiste em encontrar uma rota entre cidades que inicie e termine em uma mesma cidade sem nunca repetir uma visita.

(8)

TSP para 15.112 cidades

Em abril de 2001, David Applegate, Robert Bixby, Vašek Chvátal, and William Cook anunciaram a solução do TSP para 15.112 cidades na alemanha.

(9)

TSP para 24.978 cidades

Em maio de 2004 foi anunciada a solução do TSP para 24.978 cidades na Suíça.

(10)

Problema da Mochila

Podemos entender o Problema da Mochila como o desafio de encher uma mochila sem ultrapassar um determinado limite de peso, otimizando o valor do produto carregado.

(11)

Aplicações

Além do aspecto matemático, o modelo em si pode ser aplicado diretamente em casos

práticos como:

• Investimento de capital;

• No problema de corte e empacotamento • Carregamento de veículos

(12)

NP-Completo

Os problemas da classe NP-Completo são os problemas mais difíceis da classe NP: não se conhece (provavelmente não exista) solução para eles em tempo menor que exponencial e se conseguirmos resolver um deles, resolvemos todos (são equivalentes).

(13)

Tempo Exponencial

Atualmente todos os algoritmos conhecidos para problemas NP-completos utilizam tempo exponencial quanto ao tamanho da entrada.

A curva ex_{jamais toca o eixo x,}

embora apresente tendência a se aproximar deste.

(14)

Algoritmos

Para resolução destes problemas são utilizados alguns enfoques: • Aproximação • Probabilístico • Casos Particulares • Heurísticas • Algoritmos Genéticos

(15)

Informática

Atualmente os meios computacionais nos permitem trabalhar enormes volumes de dados sobre as atividades das empresas e, através de adequados modelos de base quantitativa, simular e avaliar linhas de ação alternativas e encontrar as soluções que melhor servem aos objetivos dos indivíduos ou organizações.

(16)

(17)

SOLVER

O Solver faz parte de um conjunto de programas algumas vezes chamado de ferramentas de análise hipotética.

(18)

Solver – Microsoft Excel

• Para instalar o recurso Solver, clique em Suplementos no menu Ferramentas e marque a caixa de seleção Solver

• Clique em OK e o Excel instalará o recurso Solver

• Após a instalação do suplemento, você poderá executá-lo clicando em Solver no menu Ferramentas.

(19)

Problema

A Indústria Maximóveis fabrica dois tipos de produtos: cadeiras e mesas. Os produtos apresentam as seguintes margens de contribuição por unidade:

Produto Margem de Contribuição por Unidade (R$)

Cadeiras 10

(20)

Consumo de tempo nos Deptos.

Os produtos são processados por dois departamentos: montagem e acabamento. Ao passar por esses departamentos, cada unidade do produto consome determinado número de horas:

Departamentos Consumo de horas pelos produtos Cadeiras Mesas

Montagem 3 3

(21)

Capacidade produtiva dos deptos.

Os departamentos apresentam contudo, limitação em sua capacidade produtiva:

Departamento Capacidade máxima disponível em horas

Montagem 30

(22)

Maximização

Desejamos saber qual é a melhor combinação possível de cadeiras e mesas a serem produzidas, de forma a obter a maior margem de contribuição total. Função objetivo: Maximizar lucro Lucro = 10C + 8M Restrições: 3C +3M ≤ 30 6C +3M ≤ 48

(23)

Solução Computacional

O primeiro passo é transpor para uma planilha os elementos que compõem o modelo do

problema: variáveis, função-objetivo e restrições e suas respectivas relações matemáticas.

(24)

(25)

Dados iniciais

• B5 e C5 – margens de contribuição unitárias dos produtos;

• B8 a B9 – tempo gasto pelos produtos em cada departamento;

• E8 e E9 – capacidade produtiva dos departamentos;

(26)

As fórmulas das restrições são:

Departamento de Montagem = 3C + 3M <=30 Departamento de Acabamento = 6C + 3M <=48

Estas fórmulas estão reproduzidas nas células D8 e D9 da planilha:

D5: =(B5*B4)+(C5*C4) D8: = (B8*B4) + (C8*C4) D9: = (B9*B4) + (C9*C4)

(27)

• Após digitar os valores, clique no menu

Ferramentas > Opção Solver...

Resolvendo o modelo pelo Solver

(cont.)

Selecionar a célula da função objetivo (D5) Em “Igual a”: Escolha a opção Máx Na caixa “células variáveis” – inserir os valores das variáveis de decisão

(28)

Resolvendo o modelo pelo Solver

(cont.)

• Na caixa “Submeter às restrições” devem ser inseridas as restrições do problema

• Clique no botão “Adicionar” e a janela abaixo aparecerá

Selecione a célula contendo a 1a restrição (B8) Escolha a opção que corresponde ao tipo de restrição Selecione a célula que contém a restrição correspondente Por último, clique no botão “OK”

(29)

• Após adicionar todas as restrições, clique no botão “Resolver” • A janela abaixo aparecerá

• Nesta janela, clique no botão “OK”

Resolvendo o modelo pelo Solver

(cont.)

Para criar um relatório

(planilha) na pasta atual

(30)

(31)

Problema

Uma fábrica de computadores produz 2 modelos de computador: A e B. O modelo A fornece um lucro de R$ 180,00 e B de R$ 300,00. O modelo A requer, na sua produção, um gabinete pequeno e uma unidade de disco. O modelo B requer 1 gabinete grande e 2 unidades de disco. Existem no estoque: 60 unidades do gabinete pequeno, 50 do gabinete grande e 120 unidades de disco. Pergunta-se: qual deve ser o esquema de produção que maximiza o lucro?

(32)

Função objetivo: Maximizar lucro Lucro = 180x1 + 300x2 Restrições: x1 + 2x2 ≤ 120 x1≤ 60 x2 ≤ 50 x1 ≥ 0; x2 ≥ 0;

Solução

(33)

Resolvendo o modelo pelo Solver

Para definir o problema na planilha, devemos definir células para representar as variáveis de decisão, uma célula para representar o valor da função objetivo e também devemos representar as restrições:

(34)

Resolvendo o modelo pelo Solver

(cont.) Na célula B5: =180*B2+300*B3 Célula B8: =B2+ 2*B3 Célula B9: =B2 Célula B10: =B3 Célula B11: =B2 Célula B12: =B3

(35)

• Após digitar os valores, clique no menu

Ferramentas > Opção Solver...

Resolvendo o modelo pelo Solver

(cont.)

Selecionar a célula da função objetivo (b5) Em “Igual a”: Escolha a opção Máx Na caixa “células variáveis” – inserir os valores das variáveis de decisão

(36)

Resolvendo o modelo pelo Solver

(cont.)

• Na caixa “Submeter às restrições” devem ser inseridas as restrições do problema

• Clique no botão “Adicionar” e a janela abaixo aparecerá

Selecione a célula contendo a 1a restrição (B8) Escolha a opção que corresponde ao tipo de restrição Selecione a célula que contém a restrição correspondente Por último, clique no botão “OK”

(37)

• Após adicionar todas as restrições, clique no botão “Resolver” • A janela abaixo aparecerá

• Nesta janela, clique no botão “OK”

Resolvendo o modelo pelo Solver

(cont.)

Para criar um relatório

(planilha) na pasta atual

(38)

(39)

Para resolver no Solver ...

Uma micro-empresa produz dois tipos de jogos para adultos e sua capacidade de trabalho é de 50 horas semanais. O jogo A requer 3 horas para ser confeccionado e propicia um lucro de R$ 30,00, enquanto o jogo B precisa de 5 horas para ser produzido e acarreta um lucro de R$ 40,00. Função objetivo: Maximizar lucro Lucro = 30x1 + 40x2 Restrições: 3x1 + 5x2  50 x1 ≥ 0 x2 ≥ 0

Quantas unidades de cada jogo devem ser produzidas semanalmente a fim de maximizar o lucro?

(40)

Uma empresa fabrica 2 modelos de caixões. O modelo M1 requer 4 horas para ser fabricado, enquanto que o modelo M2 requer 6 horas. O no total de horas disponíveis é 60. O no mínimo de caixões a serem fabricados é 12. Os lucros unitários são 2 dólares para M1 e 3 dólares para M2. Qual é o modelo que maximiza o lucro?

Para resolver no Solver ...

(cont.)

Função objetivo: Maximizar lucro Lucro = 2x1 + 3x2 Restrições: 4x1 + 6x2  60 x1 + x2 ≥ 12 x1 ≥ 0 x2 ≥ 0

(41)

Sabe-se que uma pessoa necessita, em sua alimentação diária, de um mínimo de 15 unidades de proteínas e 20 unidades de carboidratos. Supondo que, para satisfazer esta necessidade, ela disponha dos produtos A e B. Um kg do produto A contém 3 unidades de proteínas, 10 unidades de carboidratos e custa R$ 2,00. Um kg do produto B contém 6 unidades de proteínas, 5 unidades de carboidratos e custa R$ 3,00. Que quantidade deve-se comprar de cada produto de modo que as exigências da alimentação sejam satisfeitas a um custo mínimo?

Para resolver no Solver ...

(cont.)

Restrições: 3x1 + 6x2 ≥ 15 10x1 + 5x2 ≥ 20 x1 ≥ 0 ; x2 ≥ 0

Função objetivo: