Otimização com Excel Solver

Solver é um programa add-in do Microsoft Excel que você pode usar para otimização em análises de hipóteses.

De acordo com O'Brien e Marakas, optimization analysisé uma extensão mais complexa da análise de busca de objetivos. Em vez de definir um valor de destino específico para uma variável, o objetivo é encontrar o valor ideal para uma ou mais variáveis ​​de destino, sob certas restrições. Então, uma ou mais outras variáveis ​​são alteradas repetidamente, sujeitas às restrições especificadas, até que você descubra os melhores valores para as variáveis ​​de destino.

No Excel, você pode usar Solver para encontrar um optimal value (máximo ou mínimo, ou um determinado valor) para uma fórmula em uma célula chamada célula objetivo, sujeito a certas restrições ou limites, nos valores de outras células de fórmula na planilha.

Isso significa que o Solver trabalha com um grupo de células chamadas variáveis ​​de decisão que são usadas no cálculo das fórmulas nas células de objetivo e restrição. O Solver ajusta os valores nas células da variável de decisão para satisfazer os limites das células de restrição e produzir o resultado desejado para a célula objetivo.

Você pode usar o Solver para encontrar soluções ideais para diversos problemas, como -

  • Determinar o mix mensal de produtos para uma unidade de fabricação de medicamentos que maximize a lucratividade.

  • Agendamento de força de trabalho em uma organização.

  • Resolvendo problemas de transporte.

  • Planejamento financeiro e orçamento.

Ativando Solver Add-in

Antes de prosseguir com a busca de solução para um problema com o Solver, certifique-se de que o Solver Add-in é ativado no Excel da seguinte forma -

  • Clique na guia DADOS na faixa de opções. oSolver O comando deve aparecer no grupo Análise, conforme mostrado abaixo.

Caso você não encontre o comando Solver, ative-o da seguinte maneira -

  • Clique na guia ARQUIVO.
  • Clique em Opções no painel esquerdo. A caixa de diálogo Opções do Excel é exibida.
  • Clique em Add-Ins no painel esquerdo.
  • Selecione Suplementos do Excel na caixa Gerenciar e clique em Ir.

A caixa de diálogo Add-Ins é exibida. VerificaSolver Add-ine clique em Ok. Agora, você deve conseguir encontrar o comando Solver na Faixa de Opções na guia DADOS.

Métodos de resolução usados ​​pelo Solver

Você pode escolher um dos três métodos de solução a seguir que o Excel Solver suporta, com base no tipo de problema -

LP Simplex

Usado para problemas lineares. UMASolver modelo é linear nas seguintes condições -

  • A célula-alvo é calculada somando os termos da forma (célula variável) * (constante).

  • Cada restrição atende ao requisito do modelo linear. Isso significa que cada restrição é avaliada somando os termos da forma (célula variável) * (constante) e comparando as somas com uma constante.

Gradiente Reduzido Generalizado (GRG) Não Linear

Usado para problemas não lineares suaves. Se sua célula-alvo, qualquer uma de suas restrições ou ambas contiverem referências a células variáveis ​​que não são da forma (célula variável) * (constante), você tem um modelo não linear.

Evolucionário

Usado para problemas não lineares suaves. Se sua célula-alvo, qualquer uma de suas restrições ou ambas contiverem referências a células variáveis ​​que não são da forma (célula variável) * (constante), você tem um modelo não linear.

Compreendendo a avaliação do Solver

O Solver requer os seguintes parâmetros -

  • Células Variáveis ​​de Decisão
  • Células de restrição
  • Células Objetivas
  • Método de Resolução

A avaliação do Solver é baseada no seguinte -

  • Os valores nas células da variável de decisão são restringidos pelos valores nas células de restrição.

  • O cálculo do valor na célula objetivo inclui os valores nas células da variável de decisão.

  • O Solver usa o Método de Resolução escolhido para resultar no valor ideal na célula objetivo.

Definindo um problema

Suponha que você esteja analisando os lucros obtidos por uma empresa que fabrica e vende um determinado produto. Você deve encontrar o valor que pode ser gasto em publicidade nos próximos dois trimestres, até um máximo de 20.000. O nível de publicidade em cada trimestre afeta o seguinte -

  • O número de unidades vendidas, determinando indiretamente o valor da receita de vendas.
  • As despesas associadas, e
  • O lucro.

Você pode prosseguir para definir o problema como -

  • Encontre o custo unitário.
  • Encontre o custo de publicidade por unidade.
  • Encontre o preço unitário.

Em seguida, defina as células para os cálculos necessários conforme fornecido abaixo.

Como você pode observar, os cálculos são feitos para o Trimestre 1 e o Trimestre 2 que estão em consideração são -

  • O número de unidades disponíveis para venda no primeiro trimestre é de 400 e no segundo trimestre é de 600 (células - C7 e D7).

  • Os valores iniciais para orçamento de publicidade são definidos como 10.000 por trimestre (células - C8 e D8).

  • O número de unidades vendidas depende do custo de publicidade por unidade e, portanto, é o orçamento para o trimestre / Adv. Custo por unidade. Observe que usamos a função Min para verificar se o não. de unidades vendidas em <= não. de unidades disponíveis. (Células - C9 e D9).

  • A receita é calculada como Preço Unitário * Nº de unidades vendidas (Células - C10 e D10).

  • As despesas são calculadas como Custo Unitário * Nº Unidades Disponíveis + Adv. Custo para esse trimestre (Células - C11 e D12).

  • Lucro é receita - despesas (células C12 e D12).

  • O lucro total é o lucro no primeiro trimestre + o lucro no segundo trimestre (célula - D3).

Em seguida, você pode definir os parâmetros do Solver conforme fornecido abaixo -

Como você pode observar, os parâmetros do Solver são -

  • A célula objetiva é D3 que contém Lucro Total, que você deseja maximizar.

  • As células da variável de decisão são C8 e D8 que contêm os orçamentos para os dois trimestres - Trimestre1 e Trimestre2.

  • Existem três células de restrição - C14, C15 e C16.

    • A célula C14 que contém o orçamento total deve definir a restrição de 20000 (célula D14).

    • A célula C15 que contém o no. de unidades vendidas no primeiro trimestre é definir a restrição de <= não. de unidades disponíveis no primeiro trimestre (célula D15).

    • A célula C16 que contém o no. de unidades vendidas no segundo trimestre é definir a restrição de <= não. de unidades disponíveis no trimestre 2 (célula D16).

Resolvendo o problema

A próxima etapa é usar o Solver para encontrar a solução da seguinte maneira -

Step 1- Vá para DATA> Análise> Solver na faixa de opções. A caixa de diálogo Solver Parameters é exibida.

Step 2 - Na caixa Definir objetivo, selecione a célula D3.

Step 3 - Selecione Max.

Step 4 - Selecione o intervalo C8: D8 no By Changing Variable Cells caixa.

Step 5 - Em seguida, clique no botão Adicionar para adicionar as três restrições que você identificou.

Step 6- A caixa de diálogo Adicionar restrição é exibida. Defina a restrição para o orçamento total conforme fornecido abaixo e clique em Adicionar.

Step 7- Defina a restrição para nº total. de unidades vendidas no primeiro trimestre conforme fornecido abaixo e clique em Adicionar.

Step 8- Defina a restrição para nº total. de unidades vendidas no trimestre 2 conforme fornecido abaixo e clique em OK.

A caixa de diálogo Solver Parameters aparece com as três restrições adicionadas na caixa –Subject to the Constraints.

Step 9 - no Select a Solving Method caixa, selecione Simplex LP.

Step 10- Clique no botão Solve. A caixa de diálogo Resultados do Solver é exibida. SelecioneKeep Solver Solution e clique em OK.

Os resultados aparecerão em sua planilha.

Como você pode observar, a solução ideal que produz o lucro total máximo, sujeito às restrições fornecidas, é considerada a seguinte -

  • Lucro total - 30000.
  • Adv. Orçamento para o primeiro trimestre - 8.000.
  • Adv. Orçamento para o segundo trimestre - 12.000.

Percorrendo as soluções de teste do Solver

Você pode percorrer as soluções de teste do Solver, observando os resultados da iteração.

Step 1 - Clique no botão Opções na caixa de diálogo Parâmetros do Solver.

o Options a caixa de diálogo aparece.

Step 2 - Selecione a caixa Mostrar Resultados da Iteração e clique em OK.

Step 3 - o Solver Parametersa caixa de diálogo aparece. CliqueSolve.

Step 4 - o Show Trial Solution caixa de diálogo aparece, exibindo a mensagem - Solver paused, current solution values displayed on worksheet.

Como você pode observar, os valores de iteração atuais são exibidos em suas células de trabalho. Você pode parar o Solver de aceitar os resultados atuais ou continuar com o Solver para encontrar a solução nas próximas etapas.

Step 5 - Clique em Continuar.

o Show Trial SolutionA caixa de diálogo aparece em cada etapa e, finalmente, após a solução ideal ser encontrada, a caixa de diálogo Resultados do Solver é exibida. Sua planilha é atualizada a cada etapa, mostrando finalmente os valores dos resultados.

Salvando seleções do Solver

Você tem as seguintes opções de salvamento para os problemas que resolver com o Solver -

  • Você pode salvar as últimas seleções na caixa de diálogo Parâmetros do Solver com uma planilha, salvando a pasta de trabalho.

  • Cada planilha em uma pasta de trabalho pode ter suas próprias seleções do Solver, e todas elas serão salvas quando você salvar a pasta de trabalho.

  • Você também pode definir mais de um problema em uma planilha, cada um com suas próprias seleções de Solver. Nesse caso, você pode carregar e salvar problemas individualmente com Carregar / Salvar na caixa de diálogo Parâmetros do Solver.

    • Clique no Load/Savebotão. A caixa de diálogo Carregar / Salvar é exibida.

    • Para salvar um modelo de problema, insira a referência para a primeira célula de um intervalo vertical de células vazias nas quais deseja colocar o modelo de problema. Clique em Salvar.

    • O modelo do problema (o conjunto de Parâmetros do Solver) aparece começando na célula que você forneceu como referência.

    • Para carregar um modelo de problema, insira a referência para todo o intervalo de células que contém o modelo de problema. Em seguida, clique no botão Carregar.