Introdução à otimização com a ferramenta Solver do Excel

Aplica-se a
Microsoft Office Excel 2003
Microsoft Excel 2000 e 2002

Capa do livro Este artigo foi adaptado do livro Microsoft Excel Data Analysis and Business Modeling, escrito por Wayne L. Winston.

Este livro no estilo de curso foi desenvolvido com base em uma série de apresentações de Wayne Winston, um renomado professor de estatística e negócios, especializado em aplicações criativas e práticas do Excel. Portanto, esteja preparado — talvez você precise colocar seu chapéu pensador.

Neste artigo

O que é otimização?

  • Como uma grande indústria farmacêutica pode determinar a combinação de produtos mensal que maximiza sua lucratividade na fábrica de Indianápolis?
  • Se a Microsoft produz consoles do Xbox em três locais, o que a empresa deve fazer para minimizar os custos de modo a atender à demanda de consoles do Xbox?
  • Qual preço deverá ser definido para os consoles e jogos do Xbox de modo a maximizar os lucros com suas vendas?
  • A Microsoft gostaria de empreender 20 iniciativas estratégicas que atrairão capital e programadores habilidosos nos próximos cinco anos. A empresa não tem recursos suficientes para empreender todos esses 20 projetos. Quais projetos deverá escolher?
  • Como os bookmakers identificam o melhor conjunto de "classificações" para as equipes da NFL de modo a definir limites de pontos precisos?
  • Como devo alocar minha carteira de aposentadoria entre ações de alta tecnologia, ações valorizadas, títulos, dinheiro e ouro?

Em todas essas situações, desejamos identificar a melhor maneira de realizar uma ação. Nós queremos localizar os valores de certas células em uma planilha que otimizam (maximizam ou minimizam) um determinado objetivo. A ferramenta Solver do Excel ajuda a resolver os problemas de otimização.

Definindo um modelo de otimização

Um modelo de otimização constitui-se de três partes: a célula de destino, as células variáveis e as restrições.

Célula de destino

Essa célula representa o objetivo ou a meta. Queremos minimizar ou maximizar a célula de destino. No exemplo de uma combinação de produtos de uma indústria farmacêutica, presume-se que o gerente da fábrica queira maximizar a lucratividade da mesma a cada mês. A célula que mede essa lucratividade é a célula de destino. As células de destino para cada situação descrita no início do artigo são listadas na tabela a seguir.

Modelo Maximizar ou minimizar Célula de destino
Combinação de produtos da indústria farmacêutica Maximizar Lucro mensal
Fornecimento de Xbox Minimizar Custos da distribuição
Apreçamento de Xbox Maximizar Lucro com os consoles e jogos do Xbox
Iniciativas de projetos da Microsoft Maximizar Valor líquido atual decorrente dos projetos selecionados
Classificações da NFL Minimizar Diferença entre as pontuações previstas pelas classificações e os pontos reais dos jogos
Carteira de aposentadoria Minimizar Risco da carteira

Tenha em mente que, em algumas situações, talvez existam várias células de destino. Por exemplo, a Microsoft pode ter a meta secundária de maximizar a fatia de mercado do Xbox.

Células variáveis

As células variáveis são células de planilha que podem ser alteradas ou ajustadas de modo a otimizar a célula de destino. No exemplo da indústria farmacêutica, o gerente da fábrica pode ajustar a quantidade produzida de cada item durante um mês. As células nas quais essas quantidades são registradas são as células variáveis neste modelo. A tabela a seguir lista as definições de células variáveis apropriadas para os modelos descritos no início do artigo.

Modelo Células variáveis
Combinação de produtos da indústria farmacêutica Quantidade de cada item produzida durante o mês
Fornecimento de Xbox Quantidade produzida em cada fábrica por mês e fornecida a cada cliente
Apreçamento de Xbox Preços do console e do jogo
Iniciativas de programa da Microsoft Quais projetos estão selecionados?
Classificações da NFL Classificações da equipe
Carteira de aposentadoria Parcela de dinheiro investido em cada classe de ativos

Restrições

As restrições são limites que você impõe às células variáveis. Em nosso exemplo da combinação de produtos, essa combinação não pode ter uma quantidade maior de nenhum tipo de recurso disponível (por exemplo, matéria-prima e mão-de-obra) do que a quantidade disponível do recurso. Além disso, não devemos produzir uma quantidade maior de um produto do que as pessoas estejam dispostas a comprar. Na maioria dos modelos do Solver, há uma restrição implícita de que todas as células variáveis devem ser não negativas. Abordarei as restrições não negativas com mais detalhes nos próximos capítulos. Lembre-se de que não é necessário impor uma restrição a um modelo do Solver. A tabela a seguir lista as restrições para os problemas apresentados no início do capítulo.

Modelo Restrições
Combinação de produtos da indústria farmacêutica

A combinação de produtos não usa mais recursos do que a quantidade disponível

Não produzir uma quantidade maior de um produto do que seja possível vender

Fornecimento de Xbox

Não fornecer mais unidades a cada mês do que a capacidade de produção da fábrica

Verificar se os clientes recebem a quantidade de Xboxes de que necessitam

Apreçamento de Xbox Os preços não podem ser muito diferentes daqueles praticados pela concorrência
Iniciativas de projeto da Microsoft Os projetos selecionados não podem empregar mais capital ou programadores habilidosos do que as quantidades disponíveis
Classificações da NFL Nenhuma
Carteira de investimentos

Investir todo o capital em um item (dinheiro é uma possibilidade)

Obter um retorno inesperado de pelo menos 10% em nossos investimentos

Instalando e executando o recurso Solver

Para instalar o recurso Solver, clique em Suplementos no menu Ferramentas e marque a caixa de seleção Solver. Clique em OK e o Excel instalará o recurso Solver. Após a instalação do suplemento, você poderá executá-lo clicando em Solver no menu Ferramentas.

A figura a seguir mostra a caixa de diálogo Parâmetros do Solver, na qual você especifica a célula de destino, as células variáveis e as restrições aplicáveis ao seu modelo de otimização.

 Observação   Você aprenderá a fazer isso com mais detalhes nos artigos sobre o recurso Solver listados na seção Consulte também deste artigo.

A caixa de diálogo Parâmetros do Solver.

Após a definição da célula de destino, das células variáveis e das restrições, o que o Solver faz? Para responder a essa pergunta, você precisa de alguns conhecimentos terminológicos sobre esse recurso. Qualquer especificação das células variáveis que satisfaça as restrições do modelo será conhecida como uma solução viável. Por exemplo, em nossa combinação de produtos, qualquer combinação de itens que atenda às três condições a seguir será uma solução viável:

  • A combinação não emprega mais matéria-prima e mão-de-obra do que a quantidade disponível.
  • A combinação não produz uma quantidade de itens superior à demanda.
  • A quantidade produzida de cada item é não negativa.

Basicamente, o recurso Solver pesquisa todas as soluções viáveis e encontra a opção com o "melhor" valor de célula de destino (o maior valor para o máximo de otimização, o menor para o mínimo). Essa solução é chamada solução ideal. Alguns modelos do Solver não possuem solução ideal e outros têm uma única solução. Há também modelos que possuem várias (na verdade, um número infinito de) soluções ideais.

A melhor maneira de entender como usar o recurso Solver é examinando os exemplos detalhados. Na seção Consulte também deste artigo, você poderá encontrar links para artigos adicionais que descrevem como usar o recurso Solver para resolver vários problemas de negócios (e de outros itens) importantes.

Auto-avaliação

Para cada situação descrita abaixo, identifique a célula de destino, as células variáveis e as restrições.

  • Estou tomando um empréstimo de US$ 100.000 para uma hipoteca de 15 anos. A taxa anual de juros é de 8%. Eu farei pagamentos mensais. Como posso determinar o pagamento mensal da minha hipoteca?
  • Como uma empresa automotiva aloca seu orçamento de publicidade entre os diferentes formatos de propaganda?
  • Onde uma cidade deverá construir um único hospital?
  • Como uma indústria farmacêutica deverá alocar o trabalho da força de vendas para seus produtos?
  • Uma indústria farmacêutica dispõe de US$ 2 bilhões para alocar para a compra de empresas de biotecnologia. Que empresas deverá comprar?
  • A taxa de impostos cobrada para uma indústria farmacêutica depende do país no qual um item é produzido. Como uma indústria farmacêutica pode determinar onde cada medicamento deverá ser produzido?
 
 
Aplica-se a:
Excel 2003