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
Muito bom seu trabalho.
Minha dúvida é como eu consigo fazer o contrário deste exemplo do Solver. Eu tenho a quantidade de insumos e quero definir a produção de paletes.