Excel Solver: o que é e quando você deve usá-lo

As ferramentas de planilha do Excel, como o Goal Seek, que alteram uma única variável, são úteis, mas infelizmente a maioria dos problemas nos negócios não é tão fácil. Normalmente, você enfrentará fórmulas com pelo menos duas e às vezes dezenas de variáveis. Freqüentemente, um problema terá mais de uma solução e seu desafio será usar o Excel para encontrar a solução ideal (ou seja, aquela que maximiza o lucro, ou minimiza custos, ou corresponde a outros critérios). Para esses desafios maiores, você precisa de uma ferramenta mais muscular. O Excel tem apenas a resposta: Solver. O Excel Solver é um programa de otimização sofisticado que permite encontrar as soluções para problemas complexos que, de outra forma, exigiriam uma análise matemática de alto nível.

Compreendendo o Excel Solver

O Solver, assim como o Goal Seek , usa um método iterativo para realizar seus cálculos. Usar iteração significa que o Excel Solver tenta uma solução, analisa os resultados, tenta outra solução e assim por diante. No entanto, essa iteração cíclica não é apenas um trabalho de adivinhação da parte do Solver. Isso seria bobagem. Não, o Excel Solver examina como os resultados mudam a cada nova iteração e, por meio de alguns processos matemáticos sofisticados (que, felizmente, acontecem em segundo plano e podem ser ignorados), geralmente podem dizer em que direção ele deve seguir para a solução.

As vantagens do Excel Solver

Sim, Goal Seek e Solver são iterativos, mas isso não os torna iguais. Na verdade, o Excel Solver traz uma série de vantagens para a mesa:

• O Excel Solver permite que você especifique várias células ajustáveis. Você pode usar até 200 células ajustáveis ​​ao todo.
• O Excel Solver permite que você defina restrições nas células ajustáveis. Por exemplo, você pode dizer ao Solver para encontrar uma solução que não apenas maximize o lucro, mas também satisfaça certas condições, como alcançar uma margem bruta entre 20 e 30 por cento ou manter as despesas abaixo de $ 100.000. Essas condições são consideradas restrições à solução.
• O Excel Solver busca não apenas um resultado desejado (a “meta” em Goal Seek), mas também o ótimo. Por exemplo, procurar um resultado ideal pode significar que você pode encontrar uma solução que seja a máxima ou mínima possível.
• Para problemas complexos, o Solver pode gerar várias soluções . Você pode então salvar essas soluções diferentes em cenários diferentes.

Quando você deve usar o Excel Solver?

Ok, vamos atirar direto aqui: Excel Solver é uma ferramenta poderosa que a maioria dos usuários do Excel não precisa. Seria um exagero, por exemplo, usar o Solver para calcular o lucro líquido com base na receita fixa e nos valores de custo. Muitos problemas, no entanto, exigem nada menos do que a abordagem do Solver. Esses problemas abrangem muitos campos e situações diferentes, mas todos eles têm as seguintes características em comum:

• Eles têm uma única célula objetiva (também chamada de célula-alvo ) que contém uma fórmula que você deseja maximizar, minimizar ou definir para um valor específico. Essa fórmula pode ser um cálculo, como despesas totais de transporte ou lucro líquido.
• A fórmula da célula objetiva contém referências a uma ou mais células variáveis (também chamadas de incógnitas ou células variáveis ). O Solver ajusta essas células para encontrar a solução ideal para a fórmula da célula objetivo. Essas células variáveis ​​podem incluir itens como unidades vendidas, custos de envio ou despesas de publicidade.
• Opcionalmente, há uma ou mais células de restrição que devem satisfazer certos critérios. Por exemplo, você pode exigir que a publicidade seja inferior a 10 por cento das despesas totais ou que o desconto para os clientes seja um valor entre 40 e 60 por cento.

Por exemplo, a imagem abaixo mostra um modelo de dados de planilha configurado para o Excel Solver. O modelo mostra a receita (preço vezes as unidades vendidas) e os custos de dois produtos, o lucro produzido por cada produto e o lucro total. A pergunta a ser respondida aqui é a seguinte: Quantas unidades de cada produto devem ser vendidas para obter um lucro total de $ 0? Isso é conhecido nos negócios como uma análise de ponto de equilíbrio.

O objetivo deste modelo de dados é encontrar o ponto de equilíbrio (onde o lucro total é $ 0).

Isso soa como uma tarefa direta de busca de metas, mas este modelo tem um aspecto complicado: os custos variáveis. Normalmente, os custos variáveis ​​de um produto são seu custo unitário vezes o número de unidades vendidas. Se a produção do produto A custa $ 10 e você vende 10.000 unidades, os custos variáveis ​​desse produto são $ 100.000. No entanto, no mundo real, esses custos costumam ser misturados entre vários produtos. Por exemplo, se você executar uma campanha publicitária conjunta para dois produtos, esses custos serão arcados por ambos os produtos. Portanto, esse modelo assume que os custos de um produto estão relacionados às unidades vendidas do outro. Aqui, por exemplo, está a fórmula usada para calcular os custos do alvo inflável (célula B8):

= B7 * B4 - C4

Em outras palavras, os custos variáveis ​​para o alvo inflável são reduzidos em um dólar para cada unidade vendida do Polidor de cães. Os custos variáveis ​​deste último usam uma fórmula semelhante (na célula C8):

= C7 * C4 - B4

Ter os custos variáveis ​​relacionados a vários produtos coloca esse modelo de dados fora do que o Goal Seek pode fazer, mas o Solver está à altura do desafio. Aqui estão as células especiais no modelo que o Solver usará:

• A célula objetiva é C14; o lucro total e a solução de destino para esta fórmula é 0 (ou seja, o ponto de equilíbrio).
• As células mutáveis ​​são B4 e C4, que contêm o número de unidades vendidas para cada produto.
• Para restrições, você pode querer adicionar que ambas as células de lucro do produto (B12 e C12) também devem ser 0.

Carregando o suplemento Excel Solver

Um suplemento é um software que adiciona um ou mais recursos ao Excel. A instalação de suplementos fornece recursos adicionais do Excel que não estão disponíveis na Faixa de Opções por padrão. O software complementar incluído está incluído no Excel, mas não é instalado automaticamente quando você instala o Excel . Vários suplementos são fornecidos como padrão com o Excel, incluindo o Solver, que permite solucionar problemas de otimização.

Você instala os suplementos agrupados usando a caixa de diálogo Opções do Excel; você pode encontrá-los na seção Add-Ins. Depois de instalados, os suplementos estão disponíveis imediatamente. Eles geralmente aparecem em uma guia relacionada à sua função. Por exemplo, o Solver aparece na guia Dados.

Aqui estão as etapas a seguir para carregar o suplemento Excel Solver:

Escolha Arquivo → Opções.
A caixa de diálogo Opções do Excel é exibida.

Escolha Add-Ins.

Na lista Gerenciar, selecione Suplementos do Excel e selecione Ir.
O Excel exibe a caixa de diálogo Add-Ins.

Marque a caixa de seleção Solver Add-In

Clique OK.
O Excel adiciona um botão Solver ao grupo Analisar da guia Dados.

Dê uma olhada para descobrir como usar o Excel Solver .