Utilizando o Solver no Excel: Problemas simples


O Solver é suplemento presente no Microsoft Excel que permite realizar cálculos e testes de hipótese para se atingir um determinado resultado desejado, de acordo com o algoritmo informado, sendo normalmente utilizado para resolver problemas complexos que são dificilmente solucionados através das fórmulas regulares do Excel. No âmbito empresarial, podemos considerar que precisemos estipular nosso orçamento ideal a ser gasto com publicidade, de forma a atingir o máximo de lucro – uma vez que sabemos que gastos maiores com publicidade trazem mais vendas. O Solver pode ser utilizado para encontrar este valor ideal, máximo ou mínimo considerando os valores de restrição (que não podem extrapolar) e a fórmula de cálculo.

Habilitando Solver

O Solver é um suplemento do Excel e não é habilitado por padrão. Para poder utilizar este recurso, siga os passos abaixo:

  • Clique em na guia “Arquivo” (versão 2010) ou na bola com o logo da Microsoft (versão 2007).
  • Clique em “Opções” > “Suplementos”.
  • Clique na última opção da caixa de diálogo, “Gerenciar:”, para selecionar a opção “Suplementos do Excel” e clique em “Ir…” (figura 1).
solver1

Figura 1: Gerenciar Suplementos do Excel.

  • Na caixa “Suplementos”, marque a opção “Solver” e clique em “OK”.

Pronto! Você já poderá para utilizar seus recursos. A funcionalidade do Solver poderá ser acessada através das guias superiores “Dados”, no último grupo “Análise”.

Resolvendo problemas matemáticos

O Solver possui uma programação para resolver vários tipos de problemas, equações e algoritmos. Vamos começar com um problema simples de matemática, mas que poderia causar um pouco de dor de cabeça para resolver com as fórmulas simples que conhecemos.

Gilberto é empresário e solicitou um empréstimo para investir em novos produtos para revender em seu empório de produtos artesanais. O gerente do banco aprovou uma quantia de R$ 10.000,00 mas estabeleceu algumas regras para utilização no seu investimento:

  • Obter o maior lucro possível.
  • Adquirir ao menos 10 unidades de cada item disponível no mercado, a saber, vasos, tigelas, esculturas pequenas, esculturas médias e garrafas.

Além disso, Gilberto tem seus próprios objetivos e deseja que o volume por produto não ultrapasse 50 unidades, por que ele não quer manter muito estoque. Os preços para compra destes produtos e a margem de lucro seguem abaixo:

  • Vasos: R$ 50,60 (10%);
  • Tigelas: R$ 44,30 (15%);
  • Esculturas Pequenas: R$ 140,00 (25%);
  • Esculturas Médias: R$ 250,00 (20%);
  • Garrafas: R$ 35,00 (30%);

Como resolver? Primeiramente, vamos colocar todas essas informações em um racional numérico em uma planilha do Excel (faça download no final do artigo), conforme a figura 2.

Dados e fórmulas na planilha para utilização do Solver.

Figura 2: Dados e fórmulas na planilha para utilização do Solver.

Algumas das informações na planilha são obtidas em valores estáticos e outras em fórmulas:

  • G3 até G7: é uma fórmula para obter o valor total de compras em R$, calculado como segue: =F3*C3
  • I3 até I7: calcula o valor estimado de vendas, com base na margem de lucro de cada produto: =(F3*C3)+(F3*C3)*H3
  • G8: o total de compras em R$: =SOMA(G3:G7)
  • G10: o saldo do empréstimo utilizado para realizar as compras: =G9-G8
  • G11: o total de vendas em R$: =SOMA(I3:I7)
  • G12: o lucro das vendas totais em relação ao total de compras: =G11-G8

Agora chega o momento de utilizarmos o Solver, clique sobre o ícone do Solver, localizado na guia “Dados” > “Análise” e você visualizará a caixa como demonstrado na figura 3.

Figura 3: Parâmetros para utilização do Solver.

Figura 3: Parâmetros para utilização do Solver.

Neste post vamos tratar do modelo de solução simples, como já selecionado por padrão em “Selecionar um Método de Solução” como “GRG Não Linear”. Em próximos artigos vamos nos aprofundar em métodos mais complexos e muito interessantes, também, para solucionar equações no Microsoft Excel.

A ferramenta possui basicamente três grandes grupos de dados que você deve endereça para solucionar a fórmula: A célula que contém o seu valor de meta, as células que o Solver pode alterar (variáveis) e as células que contém as regras de restrição.

Começando pelo seu objetivo (a caixa de texto “Definir Objetivo”), inserimos a célula G12, que contém a fórmula de cálculo do lucro. Neste caso, como o banco demanda o lucro máximo possível, marcamos a opção “Máx.” que, em outros casos, pode ser selecionada como “Mín.” ou um valor específico, dependendo da sua meta no exercício. As células variáveis são representadas pelo intervalo F3:F7, que é justamente o local onde o Gilberto poderia inserir a quantidade de compra de cada produto. Mas queremos que o Solver preencha-a para nós.

Finalmente, estabelecemos as regras. Estas podem ser adicionadas, excluídas ou alteradas através dos botões ao lado da listagem: “Adicionar”, “Alterar”, “Excluir”. Clique em “Adicionar”.

Conforme a figura 4, devemos informar uma “Referência de Célula” que contém os valores a condicionar, uma condição (maior ou igual que, menor ou igual que, igual a) e uma “Restrição”, ou seja, o intervalo de células que contém o valor que restringe a referência de célula.

Figura 4: Adicionando restrições e regras.

Figura 4: Adicionando restrições e regras.

Em nosso caso e, conforme demonstrado na figura 3, temos três restrições:

  • F3:F7 <= E3:E7 que significa que a quantidade de compras deve ser menor ou igual à quantidade máxima (o Gilberto não deseja mais 50 unidades de cada item em seu estoque).
  • F3:F7 >= D3:D7 que significa que a quantidade de compras deve ser maior ou igual à quantidade mínima determinada pelo banco ao emprestar a quantia.
  • G10 >= 0 que, por fim, determina que o saldo do empréstimo não deve ser negativo, ou seja, menor que zero.

Clique em “Resolver” para iniciar a resolução do problema. Ao finalizar o processamento, você pode escolher por manter a solução do Solver ou restaurar os valores originais da planilha.

Figura 5: Resultados do Solver.

Figura 5: Resultados do Solver.

Assim, Gilberto poderá ter em mãos o plano perfeito para atender as expectativas do banco e as suas próprias, com as quantidades de produto que devem ser adquiridas de forma a trazer a maior rentabilidade possível, sem extrapolar as regras já estabelecidas!

down_icon Download | Modelo 1 – Solver

Se gostou compartilhe clicando nos botões abaixo e participe deixando um comentário com críticas, sugestões, complementação etc!

2,222 total views, 1 views today

Deixe um comentário

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *