Tudo o que você precisa saber sobre o Solver no Excel

Este artigo tem o objetivo de mostrar ao leitor um pouco do Solver. O artigo assume que o leitor tem um conhecimento no mínimo adequado de matemática.

por Robert Martim

Este artigo tem o objetivo de mostrar ao leitor um pouco do Solver. O artigo assume que o leitor tem um conhecimento no mínimo adequado de matemática.

O artigo é retirado do curso sobre o Solver o qual pode ser adquirido no seguinte endereço http://shop.linhadecodigo.com.br/treinamento.asp?id=352 (via download) Ao adquirir este curso o leitor ainda ganha 30 pontos no programa TopDev da Microsoft para compras via download e 60 pontos para compras via CD. Não perca esta oportunidade!

Não farei aqui uma introdução do que é o Solver, pois isso se encontra no curso. Aqui, pretendo apenas mostrar como podemos aplicar o Solver no nosso dia-a-dia. O exemplo que darei lida com programação linear. Programação linear é usada para maximizar/minimizar diversos tipos de problemas. Concentrarei em problemas econômicos, pois está é minha área de atuação profissional, mas o exemplo pode ser adaptado para outras áreas como biologia, sociologia, química, etc.

Vejamos, por exemplo, a produção de uma fábrica de leite. Ao receber o carregamento de leite, uma fábrica (se estiver equipada) pode utilizar o leite líquido para produzir leite em pó, manteiga, creme de leite, queijo, etc. Cada um destes produtos tem um preço final de mercado diferente, mas o que todos têm em comum é a matéria prima e a quantidade disponível de leite.

Além disso, temos também as restrições referentes ao tempo de produção de cada um dos produtos em relação ao número de horas que a fábrica funciona, sazonalidade, etc. Todas estas variáveis externas colocam pressão sobre o pessoal de produção e os economistas da empresa, pois eles precisam maximizar a produção de forma que o retorno seja o mais alto possível.

Programação linear nos ajuda a responder tais questões e o Solver transforma tais dores de cabeça em um passeio no parque. Observe o seguinte gráfico:

Figura 1: representação gráfica das restrições e produção possível (área colorida)

Cada uma das linhas representa uma restrição na produção, chamadas de restrições A, B e C. A área pintada representa a área onde a produção é possível tendo em vista as restrições impostas. Em outras palavras, a área em azul turquesa representa as mais variadas combinações de alocação de recursos entre os produtos tendo em vista as limitações impostas pelas restrições. Contudo, apenas uma destas combinações maximiza o retorno na produção, todas as outras são meras combinações. Esta combinação é o ponto onde a linha tangente toca o encontro das duas linhas tracejadas, isto é, este é o ponto máximo possível tendo em vista os recursos e restrições impostas sobre tais recursos.

Embora o problema seja linear, note que as diversas combinações provocam um ponto máximo, diferentemente de outros problemas lineares apresentados no curso onde o valor procurado é definido como sendo zero[1].

Para colocarmos o Solver para funcionar, vejamos o exemplo acima:

• Um fabricante de transformadores produz dois tipos de transformadores, tipos X e Y. O transformador X requer 8,75 horas na linha de produção ao passo que a produção do transformador Y requer 3,5 horas na linha de produção. O tempo para montagem de cada transformador é de 5,25 horas para X e 21 horas para o transformador Y. Completada a montagem, os produtos são embalados em containeres especiais e o transformador X leva 7 horas para embalar e o transformador Y leva o dobro do tempo.

Sabendo que a empresa dispõe de 70 horas quinzenais para produção, 105 horas quinzenais para montagem e 84 horas quinzenais para embalagem, qual a quantidade que a empresa deve produzir de cada transformador para maximizar o seu lucro se o transformador X é vendido no mercado por R$ 12.250 e o transformador Y é vendido por R$36.750?

O problema pode parecer complexo, mas não é. A parte mais difícil é filtrar as informações e construir as funções que serão utilizadas na solução do problema de maximização. Iniciarei pela parte mais óbvia que é a construção da função de lucro.

Sabemos que o lucro é igual à quantidade de X e Y vendida multiplicada pelo preço de cada um dos transformadores; portanto, podemos resumir a função como:

Agora, precisamos resolver a questão da produção, montagem e embalagem dos transformadores. Sabemos que a empresa dispõe de 70, 105 e 84 horas respectivamente. Portanto, temos as seguintes restrições:

Precisamos agora montar as inequações que representam as restrições impostas na produção, montagem e embalagem dos transformadores.

Sabemos que o tempo de produção do transformador X é de 8,5 horas e o do transformador Y é de 3,5 horas. Portanto, esta inequação será:

O tempo de montagem requerido para X e Y é 5,25 horas e 21 horas respectivamente. Assim sendo, definimos a inequação como:

Finalmente, o tempo necessário para embalar os transformadores é de 7 horas e 14 horas (o dobro) respectivamente, portanto:

Você pode, agora, resolver para as três equações acima e grafá-las para gerar o gráfico de restrições conforme mostra a figura 1 acima. O gráfico é importante para lhe dar um posicionamento dentro do problema. Mesmo sem a área pintada, pelas restrições é óbvio que X não pode ser maior do que 9 (nove) mesmo estando abaixo de duas curvas de restrição, pois ele está fora de uma terceira curva que preclui tal valor.

Passadas as explicações, montaremos agora nossa planilha para resolver o problema. A figura abaixo mostra o problema com as fórmulas em cada célula. Os valores não calculados são mostrados como valores:

Figura 2 – Definição do problema: fórmulas utilizadas

Esta figura mostra a mesma planilha com os resultados calculados:

Figura 3 – Definição do problema: valores calculados

Uma questão importante para se ter sempre em mente diz respeito às restrições. Quando elas são muitas é fácil nos perdermos no meio de tantos números. O que procuro fazer é elaborar uma fórmula que calcule a restrição e retorne VERDADEIRO ou FALSO quando as restrições são mantidas e FALSO quando os valores extrapolam a restrição. Desta forma, fica relativamente fácil identificar problemas.

Além disso, procure alimentar o Solver com valores vindos de células. Se você inserir um valor constante em uma restrição no Solver ele ficará lá. Se você precisar de uma modificação qualquer e não lembrar de corrigir os valores, você estará em sérios apuros.

Abra o Solver e insira os detalhes do modelo conforme solução apresentada acima:

Figura 4: Especificando o modelo no Solver

Duas restrições que não coloco na planilha, mas defino no Solver diz respeito aos valores possíveis de X e Y. Como não existe uma produção negativa, isto é, você não tem como produzir -1 transformador, os valores ficam restritos àqueles maiores ou iguais a zero, pois a única coisa possível é simplesmente não produzir nada.

Finalmente, clique em Resolver para encontrar a quantidade que deve ser produzida de X e Y para maximizar o lucro da empresa.

CONCLUSÃO

Neste breve artigo mostro como utilizar o Solver para resolver problemas de programação linear no Excel. Se você deseja aprender mais sobre o Solver clique no link http://shop.linhadecodigo.com.br/treinamento.asp?id=352 (via download) para adquirir já o seu módulo. Além do exemplo acima, o leitor aprenderá a maximizar/minimizar programas de cálculo diferencial com múltiplas variáveis, problemas de equilíbrio no mercado, e muito muito mais. Além, é claro, de acumular pontos no programa TopDev da Microsoft.