Utilização da Planilha Eletrônica EXCEL para resolver problemas de LP

 

Existem dois modos de se formular um problema em programação linear: o modo tradicional, onde se usa o método gráfico (até duas variáveis) e o método Simplex (3 ou mais variáveis), ou o método computacional, onde existem programas prontos para resolver problemas de LP (como por ex. o programa "LINDO") ou então, utilizando as planilhas eletrônicas como EXCEL, LOTUS 1-2-3 ou Quattro Pró. O objetivo do presente texto é fornecer um roteiro para a resolução de um problema típico de LP utilizando o software EXCEL.

O exemplo a seguir é considerado um protótipo de problemas de LP, visto que muitos casos se reduzem a simples variações do mesmo. O problema básico se resume a achar a "quantidade otimizada de produtos" a serem fabricados de modo a maximizar o lucro ou a receita. Na Lista 2 existem vários problemas desse tipo. Examinemos então o problema do "dilema do fabricante", que foi resolvido em classe:
Um fabricante deseja maximizar a receita bruta. A tabela abaixo ilustra as composições das ligas, seus preços e as limitações na disponibilidade de matéria prima. Modele e resolva o problema.

 
Liga Tipo A
Liga Tipo B
Matéria prima disponível
Cobre
2
1
16
Zinco
1
2
11
Chumbo
1
3
15
Preço unitário
R$ 30,00
R$ 50,00
 
 

ELEMENTOS DA PLANILHA

  1. Dados de Entrada: são os dados fornecidos no problema, isto é, os dados da função objetivo e os dados das equações de restrição (maior igual ou menor igual, incluindo as condições de não-negatividade). Esses dados devem aparecer em algum lugar na planilha. Apesar de não ser absolutamente necessário, nossa convenção é envolver os dados de entrada com uma borda azul e fundo cinza. É aconselhável colocar o máximo de dados de entrada no canto superior esquerdo da planilha, apesar de que em alguns problemas específicos iremos mudar essa regra.
  2. Células variáveis: Ao invés de usarmos nomes de variáveis como x1 ou y1, utilizamos um conjunto de células pré-definidas que fazem o papel das variáveis de decisão. Os valores nessas células podem ser mudados a fim de otimizar a função objetivo. Para evidenciar essas células, convencionamos envolvê-las em uma borda vermelha.
  3. Célula destino: essa célula irá acumular o valor calculado da função objetivo. A ferramenta SOLVER sistematicamente varia os valores das células variáveis a fim de otimizar o valor da célula destino. Nossa convenção é envolver a célula destino em uma borda preta dupla.
  4. Restrições ou vínculos: no EXCEL, as restrições não aparecem diretamente na planilha. Ao invés disso, iremos especificar as desigualdades diretamente num quadro de diálogo da ferramenta SOLVER. Deve-se entrar todas as desigualdades, inclusive os vínculos de não-negatividade.
  Em geral, a solução completa do problema envolve dois estágios: O primeiro estágio é a entrada de todos os dados fornecidos no problema, os valores iniciais das células variáveis (que adotaremos como sendo 1) e as fórmulas que relacionam essas células com os dados de entrada e cujo resultado é armazenado na célula destino. Esse primeiro estágio é o mais importante pois é nele que todos os ingredientes do modelo são incluídos e relacionados entre si. No segundo estágio chamamos a ferramenta SOLVER no menu Ferramentas do Excel, que irá pedir a localização das células variáveis e da célula destino, bem como uma lista de todas as restrições envolvidas no problema, que são escritas em termos de endereços de células. Ao final é só pedir para que o SOLVER ache a solução otimizada.

 

DESENVOLVENDO O PROBLEMA NA PLANILHA

 

  1. Dados de entrada: Entre com os dados conforme mostrado no quadro abaixo. As quantidades de cada tipo de matéria prima nas células C5:D7, as quantidades disponíveis de cada tipo nas células G5:G7 e a receita de cada tipo de liga nas células C9:D9.
  2. Níveis de produção: entre com qualquer valor inicial nas células C14:D14. Usualmente utilizaremos o valor 1, mas isso não é uma regra, visto que o programa achará a solução qualquer que seja o valor inicial. Essas são as células variáveis, isto é, as células onde os valores das variáveis de decisão são colocados.
  3. Recursos utilizados: entre com a fórmula abaixo na célula E5
  4. =SOMARPRODUTO(C5:D5;$C$14:$D$14)

    e a copie para as células E6 e E7. Essa fórmula calcula as unidades de cobre, chumbo e zinco utilizadas pelas quantidades de ligas digitadas inicialmente. A função somarproduto é particularmente útil em modelos de LP. Aqui ela multiplica cada valor do intervalo de células C5:D5 pelos correspondentes valores nas células C14:D14 e depois soma esses produtos, do mesmo modo que é feito na multiplicação de matrizes. O propósito de colocar o dólar das células variáveis é o de fixá-las quando copiamos a mesma fórmula para as outras restrições.

  5. Receita obtida: entre com a fórmula abaixo na célula E9:
  6. =SOMARPRODUTO(C9:D9;C14:D14)

    Essa fórmula calcula o total de receita de acordo com o número de ligas presentes nas células variáveis. Se você fez tudo certo deverá obter uma planilha como a que está abaixo.
     

  7. Usar o SOLVER: a ferramenta SOLVER consegue atingir 2 objetivos: inicialmente ela pede que você especifique a célula destino (resultado da função objetivo), as células variáveis (valores das variáveis de decisão) e as restrições do problema, inclusive os vínculos de não-negatividade. Então a ferramenta resolve o problema através de ajustes nas células variáveis até que o máximo valor da célula destino seja encontrado. Para os problemas de PL, a ferramenta utiliza o chamado "Modelo SIMPLEX", que será visto em classe. Para chamar o Solver, clique em ferramentas e escolha Solver. Se você não encontrar essa ferramenta, escolha o item suplementos (d0 menu ferramentas) e procure o Solver e clique no respectivo quadrinho. Se ele não estiver presente, clique em procurar e ache o arquivo SOLVER.XLA e o selecione. Dê OK em tudo e feche as janelas e clique novamente em Ferramentas e agora sim o Solver estará lá. A janela do Solver se abrirá conforme mostrado abaixo:
  8. a) Selecione como célula destino a célula E9 e clique na opção Max.
    b) Selecione as células variáveis de acordo com a janela acima.
    c) Adicione cada restrição, com a respectiva desigualdade correta. Note que você deve dar corretamente os endereços de cada desigualdade e, por esse motivo, não importa muito onde você as coloque na planilha.

  9. Modelo Linear: antes de pedir para Resolver, clique em Opções e selecione "Presumir modelo Linear", pois afinal se trata de PL.
  10. Resolver: clique em resolver e então o Solver mostrará nas células variáveis o valor ótimo das quantidades de ligas e na célula destino o valor máximo da Receita. Antes ele diz que achou uma solução ótima e, se você selecionar nas opções de relatórios, ele criará até 3 tipos de relatórios diferentes, os quais serão muito úteis futuramente. Escolha os 3 relatórios e dê OK. Você verá que o Excel criará mais 3 pastas, cada uma com um tipo de relatório.
  11. SUCESSO: você conseguiu otimizar um problema. Tente o mesmo com todos os outros da lista.

  12.