Programac¸˜ao Linear com o Microsoft Excel R
4. Programac¸˜ao Linear com o Solver
Para melhor entendimento do processo, a construc¸˜ao ser´a feita seguindo o seguinte exemplo, extra´ıdo do livro Introduc¸˜ao `a Programac¸˜ao Linear de Paulo F. Bregalda. Antonio F. de Oliveira e Cl´audio T. Borstein, veja [BREGALDA, OLIVEIRA & BORNSTEIN, 1983].
Uma nutricionista precisa estabelecer uma dieta contendo, pelo menos, 10 uni-dades de vitamina A, 30 uniuni-dades de vitamina B e 18 uniuni-dades de vitamina C. Essas vitaminas est˜ao contidas em quantidades variadas em cinco alimentos que vamos chamar de s1, s2, s3, s4, s5. O quadro seguinte d´a o n´umero de unidades das vitaminas A, B e C em cada unidade desses cinco alimentos como o seu custo por unidade.
s1 s2 s3 s4 s5
A 0 1 5 4 3
B 2 1 0 3 2
C 3 1 0 9 0
Custo 4 2 1 10 5
O nosso problema est´a em calcular a quantidade dos cinco alimentos que devem ser inclu´ıdas na dieta di´aria, a fim de garantir os teores de cada vitamina e com o menor custo poss´ıvel.
MODELO
Sejam x1, x2, x3, x4, x5 o n´umero de unidades dos alimentos s1, s2, s3, s4, s5, respectivamente, de uma dieta di´aria.
O teor de, pelo menos, 10 unidades de vitamina A por ser expresso, da seguinte forma:
x2+ 5x3 + 4x4+ 3x5 ≥ 10
Analogamente, indicamos os outros teores m´ınimos, respectivamente, da seguinte forma:
2x1+ x2 + 3x4+ 2x5 ≥ 30 3x1 + x2+ 9x4 ≥ 18
Como n˜ao podemos consumir uma quantidade negativa de unidades dos alimentos tamb´em:
O custo por dia da desta dieta, ser´a expresso pela Func¸˜ao Linear: Q(x) = 4x1+ 2x2+ x3+ 10x4+ 5x5
Nosso problema ´e portanto, determinar o ponto ´otimo x = (x1, x2, x3, x4, x5) tal que satisfac¸a todas as restric¸˜oes e minimize, ao mesmo tempo, o valor da func¸˜ao objetivo Q(x).
Podemos indicar nosso problema, resumidamente, da seguinte maneira:
x2 + 5x3 + 4x4 + 3x5 ≥ 10 2x1 + x2 + 3x4 + 2x5 ≥ 30 3x1 + x2 + 9x4 ≥ 18 x1 , x2 , x3 , x4 , x5 ≥ 0 4x1 + 2x2 + x3 + 10x4 + 5x5 = Q(x) → M IN !
Agora que temos um problema para trabalhar precisamos formatar nossa Planilha no Excel para que o mesmo possa, utilizando o Solver, resolver o problema, como nos mostra a Figura 4.
Figura 4: Planilha do Problema no Microsoft Excel
Onde,
• Os Coeficientes das Restric¸˜oes ´e a quantidade de que cada alimento possui de cada uma das vitaminas, de uma maneira geral, nesses campos geralmente estar˜ao os pesos de cada uma das vari´aveis nas desigualdades ou igualdades de restric¸˜oes. • As c´elulas Necessidade refere-se ao valor que cada uma das desigualdades ou
igualdades dever˜ao cumprir. E As c´elulas Quant ´e a quantidade que ser´a atendida ap´os a resoluc¸˜ao do problema.
• As c´elulas Vari´aveis nos tr´as o valor que cada uma das vari´aveis assumir´a, na ordem em que aparecem. Para in´ıcio de c´alculo podem assumir qualquer valor inteiro n˜ao-negativo.
• As c´elulas Valor Unit´ario ´e o custo de cada uma das vari´aveis, no nosso caso o custo de cada tipo de alimento.
• A c´elula Total representa o custo total da dieta no nosso exemplo, e de maneira geral esta ´e a c´elula a qual queremos maximizar ou minimizar.
As ´unicas c´elulas que ter˜ao f´ormulas ser˜ao as Quant e a Total. As c´elulas Quant nada mais ´e que a transformac¸˜ao das desigualdades do problema em igualdades com valores hipot´eticos, a princ´ıpio, dependendo do valor do “chute inicial” para as vari´aveis. Na c´elula Total ser´a digitada a func¸˜ao objetivo, que a princ´ıpio tamb´em ter´a um valor hipot´etico, que depende dos valores iniciais das vari´aveis.
As f´ormulas para cada uma das c´elulas Quant s˜ao as seguintes:
Figura 5: F ´ormulas das C ´elulas Quant (H6, H7 e H8, respectivamente)
As f´ormulas para a c´elula Total ´e a seguintes:
Figura 6: F ´ormula da C ´elula Total (J14)
Agora que temos a planilha digitada, basta a programac¸˜ao do Solver para que seja resolvido nosso problema. Para isso abrimos o Solver (Menu Ferramentas (Excel 2003 e Anteriores) ou Guia Dados (Excel 2007)). Ser´a ent˜ao exibido a caixa de di´alogo Parˆametros do Solver.
Na caixa Definir c´elula de destino dever´a conter a c´elula onde est´a a func¸˜ao ob-jetivo, no nosso exemplo a c´elula J14. Como nosso problema consiste na minimizac¸˜ao, devemos escolher a opc¸˜ao M´ın. Nas C´elulas vari´aveis dever˜ao estar as c´elulas Vari´aveis que nomeamos na planilha (B12, C12, D12, E12 e F12), como mostra a Figura 7.
Ainda precisamos clicar no bot˜ao Adicionar para que as restric¸˜oes do problema sejam inseridas no Solver. Ao clicar no bot˜ao Adicionar, temos a caixa de di´alogo Adi-cionar restric¸˜ao. As nossas restric¸˜oes dizem que a nossa Quant deve ser maior ou igual a nossa Necessidade, ou seja, os valores das c´elulas H6, H7 e H8 devem ser maior ou igual aos valores das c´elulas J6, J7 e J8, e podemos adicion´a-las de uma vez, desde que a quantidade de celulas sejam iguais, como nos mostra a Figura 8.
Ainda temos a restric¸˜ao de n˜ao negatividade das vari´aveis, que n˜ao precisamos adicionar, basta escolher a opc¸˜ao Presumir n˜ao-negativos na caixa de di´alogo Opc¸˜oes. E ainda precisamos dizer ao Excel que nosso problema ´e linear, marcando a opc¸˜ao Presumir modelo linear tamb´em da caixa de di´alogo Opc¸˜oes, as outras opc¸˜oes deixaremos como o padr˜ao do Excel como mostra a Figura 9.
Ap´os a configurac¸˜ao do Solver, basta clicar no bot˜ao Resolver, e com isso o Excel fornece a resposta ´otima ao problema, exibindo antes disso a caixa de di´alogo Resultados
Figura 7: Caixa de Di ´alogo Par ˆametros do Solver em nosso Exemplo
Figura 8: Restric¸ ˜ao 01
Figura 9: Opc¸ ˜oes do Solver
do Solver (Figura 10), onde podemos manter a soluc¸˜ao encontrada ou restaurar os valores iniciais.
Em nosso exemplo temos como resultado a seguinte planilha:
Onde temos o resultado do nosso problema. Que diz que a soluc¸˜ao ´otima ´e o consumo de 10 unidades do alimento s1 e 10 unidades do alimento s2, o que dar´a um custo de 60 e as necessidades das vitaminas foram todas satisfeitas, com o consumo de 10 unidades da vitamina A, 30 unidades da vitamina B e 40 unidades da vitamina C.
Figura 10: Caixa de Di ´alogo Resultados do Solver
Figura 11: Planilha com os Resultados do Solver
5. Conclus˜ao
Podemos ver que o Excel pode nos auxiliar em problemas de programac¸˜ao linear de pe-queno porte, com at´e 200 vari´aveis, o que corresponde a uma parte dos problemas en-contrados no dia-a-dia. O comando Solver do Excel torna o trabalho bastante r´apido e de f´acil manipulac¸˜ao.
A modelagem de problemas de programac¸˜ao de produc¸˜ao ou planejamento de investimentos por programac¸˜ao linear ´e uma pr´atica usual em empresas, que procuram com isso orientar a busca da melhor soluc¸˜ao. A modelagem e obtenc¸˜ao da soluc¸˜ao ´otima formalizam o processo de planejamento e facilitam a criac¸˜ao de alternativas vi´aveis.
Considerando ainda que o tempo ´e o grande diferencial em tomadas de decis˜ao, a utilizac¸˜ao de um recurso computacional simples ´e uma alternativa eficaz para aux´ılio na hora de decidir.