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:

Tabela de distâncias entre Produtoras e CDs

Tabela de Custos de Transporte

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

Capacidades produtivas

Demandas de cada CD

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.

Produção x CDs

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.

Caixa de diálogo do Solver

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

Anúncios

In Excel We Trust

Marcado com: , , , ,
Publicado em Nível: Avançado
4 comentários em “0093-Utilizando o Solver
  1. Wellington disse:

    Muito bom o exemplo. Uso muito o solver no dia-a-dia da minha empresa. Achei o blog bem interessante. Vou começar a acompanhá-lo agora. Abraço

  2. kio disse:

    Muito legal essas dicas. O Excel é muito abrangente nesse ponto. Obrigado por compartilhar o conhecimento.
    Da uma olhada nesse site tem varias planilhas e modelos pronto pra usar:
    http://www.lojaexcel.com.br

  3. Muito bom o material, mas gostaria de entender porque o exemplo foi dividido por 1.000.000 e não por 1.000?

    =SOMARPRODUTO(Distâncias[[São Paulo]:[Curitiba]];Custos_Transporte[[São Paulo]:[Curitiba]];Produção[[São Paulo]:[Curitiba]])/1000000

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

Visitas dos usuários
  • 2,069,536

Clique para assinar este blog e receber notificações de novos artigos por email.

Junte-se a 1.534 outros seguidores

Aumente sua interação

Siga-nos do twitter (@usuariosdoexcel).

Acesse o nosso fórum e compartilhe as suas dúvidas.

Curta nossa página no Facebook.

Junte-se ao grupo do LinkedIn.

Visite-nos utilizando o seu smartphone ou tablet.

Recomende este Blog
Visualizar notícias
Site monitorado:
Real Time Web Analytics

Clicky

%d blogueiros gostam disto: