Atingir Meta e Solver

Que o Excel é uma excelente ferramenta para controle e análise de dados todo mundo já sabe, mas o que nem todos sabem é que ele também te ajuda a resolver problemas matemáticos de forma automática com as ferramentas Atingir Meta e Solver.

---

Vídeo explicativo de Atingir Meta e Solver

Coloque o vídeo em tela cheia para assistir normalmente

---

Para que servem estas ferramentas?

Ambas tem o mesmo propósito, resolver equações, porém uma é muito mais robusta que a outra, vamos a dois exemplos simples onde elas podem ser utilizadas:

Exemplo 1 – Preço do Bolo

Você é o dono de uma bolaria (vende bolos) e como um bom administrador de seu negócio, você sabe bem quanto que consome de cada ingrediente para fazer um bolo de cenoura e chocolate de acordo com o peso final do bolo. Um cliente lhe encomendou um bolo de 1,2Kg e seu intuito é lucrar exatos R$50,00 com a venda desse bolo, por quanto o bolo deve ser vendido?

Exemplo 2 – Quanto produzir de cada tipo para obter a maior receita?

Você é o dono de um fábrica de paletes, que produz 4 tipos de paletes de madeira diferentes com preços de venda diferentes. Todos têm os mesmos insumos em sua produção, porém não nas mesmas proporções, e estes insumos são limitados. Qual é a combinação perfeita de quantidade a ser produzida de cada tipo de palete para se obter a maior receita?

Ambos os exemplos podem ser resolvidos utilizando o Solver, porém o Atingir Metas só conseguiria resolver o exemplo do bolo. Então vamos resolver estes dois casos utilizando para o bolo, o Atingir Metas e, para os paletes, o Solver.

---

Atingir Metas

A imagem abaixo mostra um esquema para o caso do bolo.

C4 = Preço de venda do bolo ao cliente.

C12 = Preço de venda subtraído do custo total, é o lucro com a venda do bolo

C5 = Peso final do bolo

F6:F10 = Custo unitário de cada ingrediente

G6:G10 = Quanto cada ingrediente rende ou é utilizado em um bolo de 1kg

C6:C10 = Custo de cada ingrediente no bolo. A fórmula contida nestas células é:
(Peso do Bolo) x (Rendimento do ingrediente) x (Preço do ingrediente)

C11 = Custo total dos ingredientes

O que precisamos fazer é encontrar o valor exato que inserido na célula C4 fará com que o valor da célula C12 seja igual a R$50,00. Note que existem fórmulas na planilha de modo que o resultado obtido em C12 depende do valor contido em C4, caso contrário não funcionaria.

Então vamos abrir o Atingir Metas e informar isso lá. Siga os passos da imagem abaixo.

A seguinte janela será exibida, preencha os campos de acordo com a imagem.

A janela do Atingir Metas possui 3 campos apenas, e são bem auto explicativos, mas vamos a eles.

Definir célula: Aqui selecionamos a célula cujo valor queremos definir. Para o exemplo esta será a célula C12.

Para valor: É o valor que queremos que a célula definida em Definir célula assuma. Para o exemplo, utilizaremos o número 50.

Alterando célula: É a célula que deverá ter seu valor alterado a fim de que a célula definida em Defini célula resulte no valor definido em Para valor. Neste exemplo será a célula C4.

Obs: Não é possível selecionar mais de uma célula nos campos Definir célula e Alterando célula.

Obs. 2: Nas imagem de exemplo existe um ‘?‘ na célula C4, este deve ser substituído por um número qualquer antes de executar o Atingir Metas, caso contrário resultará em erro.

Resumindo tudo, informamos ao Atingir Metas que encontre o valor que quando inserido na célula C4 faça com que o resultado da fórmula contida em C12 resulte em 50.

Assim que terminado, basta apertar OK e o resultado será automaticamente calculado, confira na imagem.

---

Solver

O solver é um complemento para Excel, mas não se preocupe você não precisa baixar nada por fora, apenas precisa habilitá-lo. Para isso siga os passos abaixo.

---

Como habilitar o Solver no Excel

1 – Abra o menu de opções do Excel.

2 – No menu da esquerda selecione a opção Suplementos.

3 – Na parte inferior selecione Suplementos do Excel e clique em Ir, conforme a imagem.

4 – Na janela que se abrir, marque o Solver e aperte OK, conforme a imagem.

5 – Aguarde até que o processo de instalação seja concluído.

---

Como utilizar o Solver no Excel

Agora que tudo já está pronto, confira abaixo o esquema para o exemplo da fábrica de paletes.

C5:F5 = Quantidade a ser produzida de cada tipo de palete.

C6:F6 = Preço de venda, ou receita, pela venda de cada unidade de cada tipo de palete.

G6 = Receita total com a venda dos paletes. A fórmula contida nesta célula é:
=SOMARPRODUTO(C6:F6;C5:F5)

Ou seja, é o soma da multiplicação da quantidade vendida de cada palete pela receita obtida por cada palete.

C10:F13 = Quantidade utilizada de cada insumo para cada tipo de palete.

G10:G13 = Quantidade total utilizada de cada recurso na produção dos paletes. A fórmula contida nestas células é: =SOMARPRODUTO($C$5:$F$5;C10:F10)

A fórmula acima está inserida na célula G10 que quando arrastada para baixo sofre alterações até ficar igual a =SOMARPRODUTO($C$5:$F$5;C13:F13)

Esta fórmula é a soma da multiplicação da quantidade de cada palete produzida pelo quantidade de recurso necessário.

H10:H13 = Quantidade limite de utilização de cada insumo. Isto é, o resultado das fórmulas contidas nas células G10:G13 não poderão ser superiores aos valores contidos em H10:H13.

Neste caso, o que precisamos fazer é descobrir a combinação de valores que quando inseridos nas células C5:F5 façam com que o resultado da fórmula contida na célula G6 seja o maior possível, porém levando em consideração que o resultado da fórmula contida nas células G10:G13 seja menor ou igual aos valores contidos nas células H10:H13.

Então, vamos abrir o Solver e informar tudo isso para ele. Confira a imagem abaixo para abrir o Solver.

A seguinte janela será exibida, confira a imagem para como preencher os campos.

Definir Objetivo: Aqui selecionamos a célula cujo valor queremos definir, neste exemplo é a célula G6 que contém a receita total com a venda dos paletes.

Para: Aqui existem 3 opções, (1) fazer com que o valor da célula escolhida em Definir Objetivo seja o maior possível; (2) fazer com que o valor da célula escolhida em Definir Objetivo seja o menor possível; (3) fazer com que o valor da célula escolhida em Definir Objetivo seja igual a um valor qualquer a ser informado. Para o exemplo utilizaremos a primeira opção Máx..

Alterando células variáveis: Aqui escolhemos uma ou mais células que devem ser alteradas a fim que o valor da célula definida em Definir Objetivo satisfaça a condição escolhida em Para. Neste exemplo estas células são C5:F5.

Sujeito à restrição: Aqui definiremos algumas regras devem ser cumpridas enquanto o Solver estiver simulando os valores de C5:F5. Para inserir uma nova restrição, basta clicar no botão Adicionar à esquerda, a seguinte tela será exibida:

Nele informamos uma condição que deve ser respeitada por uma ou mais células. Neste exemplo iremos informar que o resultado das fórmulas contidas em G10:G13 deve ser menor ou igual aos valores contidos em H10:H13. Após clicar em OK esta restrição aparecerá na lista de restrições exibida na imagem anterior do Solver.

Tornar variáveis irrestritas não negativas: Este campo já vem marcado por padrão no Solver e, quando marcado, significa que os valores a serem inseridos nas células definidas em Alterando células variáveis, ou seja C5:F5 não podem ser negativos. Para este exemplo deixaremos este campo marcado, já que não existe produção negativa.

Selecionar um método de solução: Em praticamente todos os casos que utilizamos o Solver o método de solução padrão, GRG Não Linear, já resolve o problema. Caso isso não ocorra, você pode testar os outros dois métodos. Para este exemplo ficaremos com o padrão mesmo.

Pronto, agora basta pressionar o botão Resolver e deixar o Solver calcular o problema para nós. Assim que o cálculo é feito, outra janela é exibida informando se houve erro no cálculo e se o Solver conseguiu ou não um resultado que satisfaça todas as condições estabelecidas.

Confira o resultado encontrado pelo Solver.

Note que os valores em G10:G13 são todos inferiores ou iguais aos de H10:H13 e que o palete do tipo Aspen não é indicado para produção.

---

Acompanhe o Função Excel