Utilizar o Solver para determinar a combinação de produtos ideal

Este artigo aborda a utilização do Solver, um suplemento do Microsoft Excel que pode utilizar para análise de hipóteses, para determinar uma combinação de produtos ideal.

Como posso determinar a combinação mensal de produtos que maximiza a rentabilidade?

Muitas vezes, as empresas precisam de determinar a quantidade de cada produto a produzir mensalmente. Na sua forma mais simples, o problema da combinação de produtos envolve como determinar a quantidade de cada produto que deve ser produzido durante um mês para maximizar os lucros. Normalmente, a combinação de produtos tem de cumprir as seguintes restrições:

• A combinação de produtos não pode utilizar mais recursos do que os disponíveis.
• Existe uma procura limitada por cada produto. Não podemos produzir mais produtos durante um mês do que a procura dita, porque o excesso de produção é desperdiçado (por exemplo, um fármaco perecível).

Vamos agora resolver o seguinte exemplo do problema de combinação de produtos. Pode encontrar a solução para este problema no ficheiro Prodmix.xlsx, mostrado na Figura 27-1.

Digamos que trabalhamos para uma empresa farmacêutica que produz seis produtos diferentes na sua fábrica. A produção de cada produto requer mão-de-obra e matérias-primas. A linha 4 na Figura 27-1 mostra as horas de mão-de-obra necessárias para produzir um quilo de cada produto, e a linha 5 mostra os quilos de matéria-prima necessárias para produzir um quilo de cada produto. Por exemplo, produzir um quilo do Produto 1 requer seis horas de trabalho e 3,2 quilos de matéria-prima. Para cada fármaco, o preço por libra é dado na linha 6, o custo unitário por libra é dado na linha 7, e a contribuição para o lucro por libra é dada na linha 9. Por exemplo, o Produto 2 vende por $11,00 por libra, incorre num custo unitário de $5,70 por libra, e contribui com um lucro de $5,30 por libra. A procura mensal por cada fármaco é dada na linha 8. Por exemplo, a procura do Produto 3 é de 1041 libras. Este mês, estão disponíveis 4500 horas de trabalho e 1600 quilos de matéria-prima. Como pode esta empresa maximizar o seu lucro mensal?

Se não soubéssemos nada sobre o Solver do Excel, atacaríamos este problema ao construir uma folha de cálculo para controlar o lucro e a utilização de recursos associados à combinação de produtos. Em seguida, utilizaríamos a versão de avaliação e o erro para variar a combinação de produtos para otimizar o lucro sem utilizar mais mão-de-obra ou matéria-prima do que está disponível, e sem produzir qualquer fármaco acima da procura. Utilizamos o Solver neste processo apenas na fase de avaliação e erro. Essencialmente, o Solver é um motor de otimização que executa perfeitamente a pesquisa de tentativas e erros.

Uma chave para resolver o problema da combinação de produtos é calcular eficientemente a utilização de recursos e o lucro associados a qualquer combinação de produtos. Uma ferramenta importante que podemos utilizar para fazer esta computação é a função SOMARPRODUTO. A função SOMARPRODUTO multiplica os valores correspondentes nos intervalos de células e devolve a soma desses valores. Cada intervalo de células utilizado numa avaliação SUMPRODUCT tem de ter as mesmas dimensões, o que implica que pode utilizar SUMPRODUCT com duas linhas ou duas colunas, mas não com uma coluna e uma linha.

Como exemplo de como podemos utilizar a função SOMARPRODUTO no nosso exemplo de combinação de produtos, vamos tentar calcular a utilização de recursos. A nossa utilização de mão-de-obra é calculada por

(Trabalho utilizado por quilo de droga 1)*(Droga 1 libras produzidas)+
(Trabalho usado por quilo de droga 2)*(Droga 2 libras produzidas) + ...
(Trabalho utilizado por quilo de droga 6)*(Droga 6 libras produzidas)

Poderíamos calcular a utilização do trabalho de uma forma mais entediante como D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4. Da mesma forma, a utilização de matérias-primas pode ser calculada como D2*D5+E2*E5+F2*F5+G2*G5+H2*H5+I2*I5. No entanto, introduzir estas fórmulas numa folha de cálculo para seis produtos é moroso. Imagine quanto tempo demoraria se estivesse a trabalhar com uma empresa que produzia, por exemplo, 50 produtos na fábrica. Uma forma muito mais fácil de calcular a utilização de mão-de-obra e matérias-primas é copiar da D14 para a D15 a fórmula SUMPRODUCT($D$2:$I$2,D4:I4). Esta fórmula calcula D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4 (que é a nossa utilização de mão-de-obra), mas é muito mais fácil de introduzir! Repare que utilizo o sinal $ com o intervalo D2:I2 para que, quando copiar a fórmula, ainda capture a combinação de produtos da linha 2. A fórmula na célula D15 calcula a utilização de matérias-primas.

De uma forma semelhante, o nosso lucro é determinado por

(Lucro do fármaco 1 por libra)*(Droga 1 libras produzidas) +
(Lucro do fármaco 2 por libra)*(Droga 2 libras produzidas) + ...
(Lucro do fármaco 6 por libra)*(Droga 6 libras produzidas)

O lucro é facilmente calculado na célula D12 com a fórmula SUMPRODUCT(D9:I9,$D$2:$I$2).

Agora, podemos identificar os três componentes do nosso modelo solver de combinação de produtos.

• Célula de destino. O nosso objetivo é maximizar o lucro (calculado na célula D12).

• Alterar células. O número de libras produzidas de cada produto (listado no intervalo de células D2:I2)

• Restrições. Temos as seguintes restrições:

  • Não utilize mais mão-de-obra ou matéria-prima do que está disponível. Ou seja, os valores nas células D14:D15 (os recursos utilizados) têm de ser menores ou iguais aos valores nas células F14:F15 (os recursos disponíveis).
  • Não produza mais um fármaco do que o que está a ser procurado. Ou seja, os valores nas células D2:I2 (libras produzidas de cada fármaco) devem ser menores ou iguais à procura de cada fármaco (listado nas células D8:I8).
  • Não podemos produzir uma quantidade negativa de qualquer droga.

Vou mostrar-lhe como introduzir a célula de destino, alterar células e restrições no Solver. Em seguida, tudo o que precisa de fazer é clicar no botão Resolver para encontrar uma combinação de produtos que maximize os lucros!

Para começar, clique no separador Dados e, no grupo Análise, clique em Solver.

A caixa de diálogo Parâmetros do Solver será apresentada, conforme mostrado na Figura 27-2.

Clique na caixa Definir Célula de Destino e, em seguida, selecione a nossa célula de lucro (célula D12). Clique na caixa Ao Alterar Células e, em seguida, aponte para o intervalo D2:I2, que contém os quilos produzidos de cada fármaco. A caixa de diálogo deverá agora ter o aspeto Figura 27-3.

Estamos agora prontos para adicionar restrições ao modelo. Clique no botão Adicionar. Verá a caixa de diálogo Adicionar Restrição, apresentada na Figura 27-4.

Para adicionar as restrições de utilização de recursos, clique na caixa Referência de Célula e, em seguida, selecione o intervalo D14:D15. Selecione <= na lista do meio. Clique na caixa Restrição e, em seguida, selecione o intervalo de células F14:F15. A caixa de diálogo Adicionar Restrição deverá agora ter o aspeto da Figura 27-5.

Garantimos agora que, quando o Solver tenta valores diferentes para as células em alteração, apenas serão consideradas as combinações que satisfaçam D14<=F14 (a mão-de-obra utilizada é menor ou igual à mão-de-obra disponível) e D15<=F15 (a matéria-prima utilizada é menor ou igual à matéria-prima disponível). Clique em Adicionar para introduzir as restrições de procura. Preencha a caixa de diálogo Adicionar Restrição, conforme mostrado na Figura 27-6.

A adição destas restrições garante que, quando o Solver tenta combinações diferentes para os valores das células em alteração, apenas serão consideradas as combinações que satisfaçam os seguintes parâmetros:

• D2<=D8 (a quantidade produzida do Fármaco 1 é menor ou igual à procura do Fármaco 1)
• E2<=E8 (a quantidade produzida do Fármaco 2 é menor ou igual à procura de Droga 2)
• F2<=F8 (a quantidade produzida do Fármaco 3 feita é menor ou igual à procura do Fármaco 3)
• G2<=G8 (a quantidade produzida do Fármaco 4 feita é menor ou igual à procura de Fármaco 4)
• H2<=H8 (a quantidade produzida do Fármaco 5 feita é menor ou igual à procura de Fármaco 5)
• I2<=I8 (a quantidade produzida do Fármaco 6 feita é menor ou igual à procura de Droga 6)

Clique em OK na caixa de diálogo Adicionar Restrição. A janela Solver deve ter o aspeto da Figura 27-7.

Introduzimos a restrição de que a alteração de células tem de ser não negativa na caixa de diálogo Opções do Solver. Clique no botão Opções na caixa de diálogo Parâmetros do Solver. Selecione a caixa Assumir Modelo Linear e a caixa Assumir Não Negativo, conforme mostrado na Figura 27-8 na página seguinte. Clique em OK.

Selecionar a caixa Assumir Não Negativo garante que o Solver considera apenas combinações de células alteradas nas quais cada célula em alteração assume um valor não negativo. Verificámos a caixa Assumir Modelo Linear porque o problema da combinação de produtos é um tipo especial de problema do Solver chamado modelo linear. Essencialmente, um modelo solver é linear nas seguintes condições:

• A célula de destino é calculada adicionando os termos do formulário (alterando célula)*(constante).
• Cada restrição atende ao "requisito de modelo linear". Isso significa que cada restrição é avaliada adicionando os termos do formulário (alterando célula)*(constante) e comparando as somas com uma constante.

Por que esse problema do Solucionador é linear? Nossa célula de destino (lucro) é calculada como

(Lucro da droga 1 por libra)*(Droga 1 libras produzida) +
(Lucro da droga 2 por libra)*(Droga 2 libras produzida) + ...
(Lucro da droga 6 por libra)*(Droga 6 libras produzida)

Essa computação segue um padrão no qual o valor da célula de destino é derivado adicionando termos do formulário (alterando célula)*(constante).

Nossa restrição de trabalho é avaliada comparando o valor derivado de (trabalho usado por quilo de Droga 1)*(Droga 1 quilos produzido) + (Trabalho usado por quilo de Droga 2)*(Droga 2 quilos produzido)+ ... (Trabalho usado por quilo de Droga 6)*(Droga 6 libras produzida) para o trabalho disponível.

Portanto, a restrição de trabalho é avaliada adicionando os termos do formulário (alterando célula)*(constante) e comparando as somas a uma constante. Tanto a restrição de mão-de-obra quanto a restrição de matéria-prima atendem ao requisito de modelo linear.

Nossas restrições de demanda tomam o formulário

(Droga 1 produzida)<=(Droga 1 Demanda)
(Droga 2 produzida)<=(Drug 2 Demand)
§
(Droga 6 produzida)<=(Droga 6 Demanda)

Cada restrição de demanda também atende ao requisito de modelo linear, pois cada uma é avaliada adicionando os termos do formulário (alterando célula)*(constante) e comparando as somas a uma constante.

Tendo mostrado que nosso modelo de mix de produtos é um modelo linear, por que devemos nos importar?

• Se um modelo solver for linear e selecionarMos Assumir Modelo Linear, o Solver será garantido para encontrar a solução ideal para o modelo Solver. Se um modelo solver não for linear, o Solver poderá ou não encontrar a solução ideal.
• Se um modelo solver for linear e selecionarMos Assumir Modelo Linear, o Solver usará um algoritmo muito eficiente (o método simplex) para encontrar a solução ideal do modelo. Se um modelo solver for linear e não selecionarMos Assumir Modelo Linear, o Solver usará um algoritmo muito ineficiente (o método GRG2) e poderá ter dificuldade em encontrar a solução ideal do modelo.

Depois de clicar em OK na caixa de diálogo Opções do Solucionador, retornamos à caixa de diálogo solucionador principal, mostrada anteriormente na Figura 27-7. Quando clicamos em Resolver, o Solver calcula uma solução ideal (se existir) para nosso modelo de mix de produtos. Como afirmou no Capítulo 26, uma solução ideal para o modelo de mixagem de produtos seria um conjunto de valores de células (libras produzidas de cada droga) que maximiza o lucro sobre o conjunto de todas as soluções viáveis. Novamente, uma solução viável é um conjunto de alteração de valores de célula que satisfazem todas as restrições. Os valores de célula de alteração mostrados na Figura 27-9 são uma solução viável porque todos os níveis de produção não são negativos, os níveis de produção não excedem a demanda e o uso de recursos não excede os recursos disponíveis.

Os valores de célula de alteração mostrados na Figura 27-10 na próxima página representam uma solução inviável pelos seguintes motivos:

• Produzimos mais da Droga 5 do que a demanda por ela.
• Usamos mais trabalho do que o que está disponível.
• Usamos mais matéria-prima do que o que está disponível.

Depois de clicar em Resolver, o Solver encontra rapidamente a solução ideal mostrada na Figura 27-11. Você precisa selecionar Manter solução solver para preservar os valores ideais da solução na planilha.

Nossa companhia farmacêutica pode maximizar seu lucro mensal a um nível de $6.625,20 produzindo 596,67 libras de Droga 4, 1084 libras de Droga 5, e nenhuma das outras drogas! Não podemos determinar se podemos obter o lucro máximo de US$ 6.625,20 de outras maneiras. Tudo o que podemos ter certeza é que, com nossos recursos limitados e demanda, não há como fazer mais de US $ 6.627,20 este mês.

Um modelo solver sempre tem uma solução?

Suponha que a demanda por cada produto deve ser atendida. (Consulte a planilha Sem solução viável no arquivo Prodmix.xlsx.) Em seguida, temos que alterar nossas restrições de demanda de D2:I2<=D8:I8 para D2:I2>=D8:I8. Para fazer isso, abra Solver, selecione a restrição D2:I2<=D8:I8 e clique em Alterar. A caixa de diálogo Restrição de Alteração, mostrada na Figura 27-12, é exibida.

Selecione >=e clique em OK. Agora garantimos que o Solver considerará alterar apenas os valores de célula que atendem a todas as demandas. Ao clicar em Resolver, você verá a mensagem "O solucionador não conseguiu encontrar uma solução viável". Esta mensagem não significa que cometemos um erro em nosso modelo, mas sim que, com nossos recursos limitados, não podemos atender à demanda por todos os produtos. O Solver está simplesmente nos dizendo que, se quisermos atender à demanda por cada produto, precisamos adicionar mais mão-de-obra, mais matérias-primas ou mais de ambos.

O que significa se um modelo Solver produz os valores de conjunto de resultados Não Convergir?

Vamos ver o que acontece se permitirmos a demanda ilimitada por cada produto e permitirmos que quantidades negativas sejam produzidas de cada droga. (Você pode ver esse problema do Solucionador na planilha Definir Valores Não Convergir no arquivo Prodmix.xlsx.) Para encontrar a solução ideal para essa situação, abra Solver, clique no botão Opções e desmarque a caixa Assumir Não Negativo. Na caixa de diálogo Parâmetros do Solucionador, selecione a restrição de demanda D2:I2<=D8:I8 e clique em Excluir para remover a restrição. Quando você clica em Resolver, o Solver retorna a mensagem "Definir valores de célula não convergem". Essa mensagem significa que, se a célula de destino deve ser maximizada (como em nosso exemplo), há soluções viáveis com valores de célula de destino arbitrariamente grandes. (Se a célula de destino deve ser minimizada, a mensagem "Definir valores de célula não convergem" significa que há soluções viáveis com valores de célula de destino arbitrariamente pequenos.) Em nossa situação, ao permitir a produção negativa de uma droga, na verdade "criamos" recursos que podem ser usados para produzir quantidades arbitrariamente grandes de outras drogas. Dada a nossa demanda ilimitada, isso nos permite obter lucros ilimitados. Em uma situação real, não podemos fazer uma quantidade infinita de dinheiro. Em suma, se você vir "Definir valores não convergir", seu modelo terá um erro.

Problemas

1. Suponha que nossa empresa farmacêutica possa comprar até 500 horas de trabalho a $1 a mais por hora do que os custos atuais de mão-de-obra. Como podemos maximizar o lucro?

2. Em uma fábrica de chips, quatro técnicos (A, B, C e D) produzem três produtos (Produtos 1, 2 e 3). Este mês, o fabricante de chips pode vender 80 unidades do Produto 1, 50 unidades do Produto 2 e no máximo 50 unidades do Produto 3. O Técnico A só pode fazer Produtos 1 e 3. O técnico B só pode criar Produtos 1 e 2. O técnico C só pode fazer o Produto 3. O Técnico D só pode fazer o Produto 2. Para cada unidade produzida, os produtos contribuem com o seguinte lucro: Produto 1, $6; Produto 2, $7; e Produto 3, $10. O tempo (em horas) que cada técnico precisa para fabricar um produto é o seguinte:

   Produto	Técnico A	Técnico B	Técnico C	Técnico D
   1	2	2,5	Não é possível fazer	Não é possível fazer
   2	Não é possível fazer	3	Não é possível fazer	3,5
   3	3	Não é possível fazer	4	Não é possível fazer

3. Cada técnico pode trabalhar até 120 horas por mês. Como o fabricante de chips pode maximizar seu lucro mensal? Suponha que um número fracionário de unidades possa ser produzido.

4. Uma fábrica de computadores produz joysticks de mouses, teclados e videogames. O lucro por unidade, o uso de mão-de-obra por unidade, a demanda mensal e o uso por unidade de tempo de máquina são dados na tabela a seguir:

   	Ratos	Teclados	Joysticks
   Lucro/unidade	$8	US$ 11	9 dólares
   Uso de mão-de-obra/unidade	.2 horas	.3 horas	.24 horas
   Hora/unidade do computador	.04 hora	.055 hora	.04 hora
   Procura mensal	15.000	27,000	11,000

5. Todos os meses, estão disponíveis um total de 13 000 horas de trabalho e 3000 horas de tempo da máquina. Como pode o fabricante maximizar a sua contribuição mensal para o lucro da fábrica?

6. Resolva o nosso exemplo de droga assumindo que deve ser satisfeita uma procura mínima de 200 unidades para cada fármaco.

7. Jason faz pulseiras de diamantes, colares e brincos. Quer trabalhar no máximo 160 horas por mês. Tem 800 onças de diamantes. Os lucros, o tempo de trabalho e as onças de diamantes necessários para produzir cada produto são dados abaixo. Se a procura por cada produto é ilimitada, como é que o Jason pode maximizar o seu lucro?

   Produto	Lucro unitário	Horas de trabalho por unidade	Onças de diamantes por unidade
   Pulseira	$300	.35	1,2
   Colar	$200	.15	0,75
   Brincos	$100	0,05	.5