0093-Utilizando o Solver

Imagine que você se deparou com o seguinte problema: “Como direcionar a produção de diferentes centros para um variedade de centros de distribuição (CDs), minimizando os custos de transporte?”
Este tipo de problema é resolvido matematicamente por meio de uma abordagem chamada programação linear.
No Excel não há necessidade de criar programas com esta finalidade por existe um suplemento chamado Solver. O Solver foi desenvolvido pela Frontline especializada em métodos que envolvem modelagem e simulação, tendo sido integrado ao Excel como um suplemento há várias versões.
Antes de utilzarmos o Solver é necessário ativá-lo (Versão 2007: Botão início >> Opções do Excel >> Suplementos >> Ir >> Marcar o checkbox do Solver)
Vamos estudar as funcionalidades básicas do Solver, respondendo à questão formulada no início deste post.

Etapas da modelagem:
Para obter a resposta desejada para uma questão é necessário formulá-la corretamente. Quanto falamos de respostas a problemas matemáticos, precisamos fazer sua Modelagem.

O problema levantado envolve as seguintes variáveis:

• Distância entre os centros produtores e os CDs;
• Custos de transporte;
• Capacidade produtiva;
• Demanda dos CDs.

Para melhorarmos a visualização e organização das informações, cada um dos conjuntos acima foi colocado em Tabelas conforme as imagens a seguir:

Os custos de transporte foram expressos em $ por 1.000 unidades transportadas por km rodado.

Uma vez organizadas todas as informações necessárias para a modelagem do problema, falta informamos que de forma elas se relacionam com a Função Objetivo. A função objetivo é aquela cujo resultado desejamos obter sob as condições do problema. Como queremos minimizar o custo de transporte, o cálculo do custo de transporte será a nossa função objetivo.

Podemos definir que o Custo de transporte = Σ (Unidades transportadas × Custo/1.000 un/km × Distância percorrida).

Criaremos agora uma tabela para as unidades transportadas e definiremos a função objetivo.

A célula Custo Total possui fórmula:

=SOMARPRODUTO(Distâncias[[São Paulo]:[Curitiba]];

Custos_Transporte[[São Paulo]:[Curitiba]];

Produção[[São Paulo]:[Curitiba]])/1000000

Como utilizamos tabelas, as fórmulas que as referenciam já trazem informações de referência para identificar os intervalos utilizados. O resultado final foi dividido por 1.000, pois o custo está expresso a cada 1.000 unidades.

Outro fator crítico na resolução deste tipo de problema é a definição das Condições de Contorno ou Restrições. As restrições estabelecem quais são as regras que tem que ser obedecidas para que uma solução seja considerada válida. No nosso exemplo, temos as seguintes restrições:

1. A soma das produções calculadas por CD deve ser igual à demanda deste CD (esta é a garantia que todo o mercado será abastecido)
2. A soma das produções calculadas para cada produtora deve ser menor ou igual à sua capacidade produtiva (caso contrário será alocada para um centro produtor, uma necessidade de produção que não será capaz de atender)
3. As produções devem ser números maiores ou iguais a zero (embora possa parecer óbvio, há soluções matematicamente corretas nas quais as produções podem ser números negativos. O Solver não irá diferenciar a menos que informemos como uma restrição)

Parametrização do Solver.

Uma vez que o nosso problema está modelado, vamos informar para o Solver de que forma desejamos resolvê-lo.

Primeiramente devemos informar que queremos minimizar o valor do custo total de transporte (célula D35) e em seguida informar quais são as células variáveis ($D$28:$I$31).

Para inserirmos as restrições, vamos selecionar Adicionar.

Restrição nº 1: “A soma das produções calculadas por CD deve ser igual à demanda deste CD”

Restrição nº 2: “A soma das produções calculadas para cada produtora deve ser menor ou igual à sua capacidade produtiva”

Restrição nº3: “As produções devem ser números maiores ou iguais a zero”

Após inserir a terceira restrição e selecionando OK, temos a caixa de diálogo preenchida:

Com todas variáveis e restrições devidamente informados, podemos selecionar Resolver.

Foi encontrada uma solução que satisfez às condições do nosso problema e que resultou num custo total de $ 33.693,55.

Obtido o resultado, podemos Manter as soluções do Solver ou Restaurar os valores originais. Outras opções são:

• Salvar cenário: salvando os resultados como um cenário, podemos utilizar o modelo para comparar diferentes soluções ajustando as condições e restrições. Isto é comum quando a solução ótima não é inteiramente factível ou deseja-se investigar o impacto dos diferentes fatores.
• Relatórios: há 3 tipos possíveis: Resposta, Sensibilidade e Limites. Em cada um deles o Solver provê informações adicionais sobre os resultados e as condições de variáveis e restrições.

Faça o download da planilha com a solução e os relatórios: Exemplo de uso do Solver