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

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

Geralmente, as empresas precisam determinar a quantidade de cada produto a ser produzida mensalmente. Em sua forma mais simples, o problema da combinação de produtos envolve a determinação do valor de cada produto que deve ser produzido durante um mês para maximizar o lucro. A combinação de produtos geralmente deve obedecer às seguintes restrições:

• A combinação de produtos não pode usar mais recursos do que o disponível.

• Há uma demanda limitada para cada produto. Não podemos produzir mais de um produto durante um mês do que a demanda exige, porque a produção em excesso é desperdiçada (por exemplo, um medicamento perishable).

Agora, vamos solucionar o seguinte exemplo do problema de combinação de produtos. Você pode encontrar a solução para esse problema no arquivo Prodmix.xlsx, mostrada na Figura 27-1.

Vamos supor que trabalhamos para uma empresa farmacêutica que produz seis produtos diferentes em sua fábrica. A produção de cada produto exige mão-de-obra e material bruto. A linha 4 na Figura 27-1 mostra as horas de trabalho necessárias para produzir uma libra de cada produto, e a linha 5 mostra as libras da matéria-prima necessária para produzir uma libra de cada produto. Por exemplo, produzir uma libra do produto 1 requer seis horas de mão-de-obra e 3,2 kg de matéria-prima. Para cada medicamento, o preço por libra é fornecido na linha 6, o custo unitário por libra é fornecido na linha 7, e a contribuição de lucro por libra é fornecida na linha 9. Por exemplo, o produto 2 vende para $11 por libra, incorre um custo unitário de $5.70 por libra e contribui para o lucro de $5.30 por libra. A demanda do mês para cada medicamento é fornecida na linha 8. Por exemplo, a demanda do produto 3 é de 1041 kg. Este mês, 4500 horas de mão-de-obra e 1600 libras de material bruto estão disponíveis. Como essa empresa pode maximizar seu lucro mensal?

Se não soubermos nada sobre o Solver do Excel, informaremos esse problema ao construir uma planilha para acompanhar o uso dos recursos e do lucro associado à combinação de produtos. Em seguida, usamos tentativa e erro para variar a combinação de produtos para otimizar o lucro sem usar mais mão-de-obra ou matéria-prima do que a disponível e sem produzir qualquer medicamento em excesso de demanda. Usamos o Solver nesse processo somente no estágio de avaliação e erro. Basicamente, o Solver é um mecanismo de otimização que executa sem falhas a pesquisa de avaliação e erros.

Uma chave para resolver o problema da combinação de produtos é calcular eficientemente o uso dos recursos e o lucro associados a qualquer combinação de produtos específica. Uma ferramenta importante que pode ser usada para fazer esse cálculo é a função SOMARPRODUTO. A função SOMARPRODUTO multiplica os valores correspondentes em intervalos de célula e retorna a soma desses valores. Cada intervalo de células usado em uma avaliação SOMARPRODUTO deve ter as mesmas dimensões, o que significa que você pode usar SOMARPRODUTO com duas linhas ou duas colunas, mas não com uma coluna e uma linha.

Como exemplo de como podemos usar a função SOMARPRODUTO em nosso exemplo de combinação de produtos, vamos tentar calcular o uso do recurso. O uso do trabalho é calculado pela

(Mão-de-obra usada por libra do medicamento 1) * (medicamento 1 libras produzidas) +
(Mão-de-obra usada por libra do medicamento 2) * (medicamento 2 libras produzidas) +...
(Mão-de-obra usada por libra do medicamento 6) * (medicamento 6 libras produzidas)

Poderíamos calcular o uso de mão-de-obra de maneira mais entediante como D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + i2 * i4. Da mesma forma, o uso de material bruto pode ser calculado como d2 * d5 + E2 *E5 + F2 * F5 + G2 * G5 + H2 * H5 + i2 * i5. No entanto, inserir essas fórmulas em uma planilha para seis produtos é demorado. Imagine quanto tempo levaria se você estivesse trabalhando com uma empresa que produziu, por exemplo, 50 produtos em sua fábrica. Uma maneira muito mais fácil de calcular mão-de-obra e uso bruto do material é copiar do D14 para D15 a fórmula SOMARPRODUTO ($D $2: $I $2, D4: i4). Esta fórmula calcula D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + i2 * i4 (que é o nosso uso de mão-de-obra), mas é muito mais fácil de entrar! Observe que eu uso o sinal $ com o intervalo D2: i2 para que, ao copiar a fórmula, eu ainda Capture a combinação de produtos da linha 2. A fórmula na célula D15 calcula o uso do material bruto.

De maneira semelhante, nosso lucro é determinado por

(Lucro do medicamento 1 por libra) * (produzido pelo medicamento 1-libras produzidas) +
(Lucro do medicamento 2 por libra) * (medicamento 2 libras produzidas) +...
(Lucro do medicamento 6 por libra) * (medicamento 6 libras produzidas)

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

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

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

• Células variáveis. O número de libras produzidas de cada produto (listadas no intervalo de células D2: i2)

• Centro. Temos as seguintes restrições:

  • Não use mais mão-de-obra ou material bruto do que está disponível. Ou seja, os valores nas células D14: D15 (os recursos usados) devem ser menores ou iguais aos valores nas células F14: F15 (os recursos disponíveis).

  • Não produza mais de um medicamento do que por demanda. Ou seja, os valores nas células D2: i2 (libras produzidas de cada medicamento) devem ser menores ou iguais à demanda de cada medicamento (listados nas células D8: i8).

  • Não podemos produzir um valor negativo de qualquer medicamento.

Mostrarei como inserir a célula de destino, as células variáveis e as restrições no Solver. Então, tudo o que você precisa fazer é clicar no botão de solução para encontrar uma combinação de produtos que maximize o lucro!

Para começar, clique na guia dados e, no grupo análise, clique em Solver.

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

Clique na caixa definir célula de destino e, em seguida, selecione nossa célula de lucro (célula D12). Clique na caixa alterando células e, em seguida, aponte para o intervalo D2: i2, que contém as libras produzidas de cada medicamento. A caixa de diálogo agora deve ter a Figura 27-3.

Agora estamos prontos para adicionar restrições ao modelo. Clique no botão Adicionar. Você verá a caixa de diálogo Adicionar restrição, mostrada na Figura 27-4.

Para adicionar as restrições de uso de recursos, clique na caixa referência de célula e selecione o intervalo D14: D15. Selecione <= na lista do meio. Clique na caixa de restrição e selecione o intervalo de células F14: F15. A caixa de diálogo Adicionar restrição agora deve se parecer com a Figura 27-5.

Garantimos que, quando o Solver tentar valores diferentes para as células variáveis, somente as combinações que satisfaçam o D14<= F14 (mão-de-obra usada é menor que ou igual a mão-de-obra) e D15<= F15 (o material bruto usado é inferior ou igual a material bruto disponível) será considerado. Clique em Adicionar para inserir as restrições de demanda. Preencha a caixa de diálogo Adicionar restrição, conforme mostrado na Figura 27-6.

Adicionar essas restrições garante que quando o Solver tentar combinações diferentes para os valores de células variáveis, somente as combinações que satisfaçam aos seguintes parâmetros serão consideradas:

• D2<= D8 (o valor produzido do medicamento 1 é menor ou igual à demanda do medicamento 1)

• E2<= E8 (a quantidade de produção do medicamento 2 é menor ou igual à demanda do medicamento 2)

• F2<= F8 (o valor produzido do medicamento 3 feito é menor ou igual à demanda do medicamento 3)

• G2<= G8 (o valor produzido do medicamento 4 feito é menor ou igual à demanda do medicamento 4)

• H2<= H8 (o valor produzido do medicamento 5 feito é menor ou igual à demanda do medicamento 5)

• I2<= i8 (o valor produzido do medicamento 6 feito é menor ou igual à demanda do medicamento 6)

Clique em OK na caixa de diálogo Adicionar restrição. A janela do Solver deve ter a aparência da Figura 27-7.

Inserimos a restrição de que as células variáveis devem ser não negativas na caixa de diálogo opções do Solver. Clique no botão Opções na caixa de diálogo parâmetros do Solver. Marque a caixa presumir o modelo linear e assumir a caixa não negativa, conforme mostrado na Figura 27-8 na próxima página. Clique em OK.

Marcar a caixa pressupor não negativo garante que o Solver considere apenas as combinações de células variáveis em que cada célula em alteração considera um valor não negativo. Marcamos a caixa presumir o modelo linear porque o problema da combinação de produtos é um tipo especial de problema do Solver chamado de um modelo linear. Basicamente, um modelo do Solver é linear nas seguintes condições:

• A célula de destino é calculada adicionando-se os termos do formulário (célula variável) * (constante).

• Cada restrição satisfaz o "requisito de modelo linear". Isso significa que cada restrição é avaliada somando os termos do formulário (célula variável) * (constante) e comparando as somas com uma constante.

Por que o problema do Solver é linear? A célula de destino (lucro) é calculada como

(Lucro do medicamento 1 por libra) * (produzido pelo medicamento 1-libras produzidas) +
(Lucro do medicamento 2 por libra) * (medicamento 2 libras produzidas) +...
(Lucro do medicamento 6 por libra) * (medicamento 6 libras produzidas)

Este cálculo segue um padrão no qual o valor da célula de destino é derivado adicionando-se os termos do formulário (célula variável) * (constante).

Nossa restrição de mão-de-obra é avaliada pela comparação do valor derivado de (mão-de- obra usada por libra do medicamento 1) * (medicamento 1 libras produzidas) + (mão-de-obra usada por libra do medicamento 2) * (medicamento 2 libras produzidas) +... (Mão-de-obraEd por libra do medicamento 6) * (medicamento 6 libras produzidas) para o trabalho disponível.

Portanto, a restrição de mão-de-obra é avaliada somando os termos do formulário (célula variável) * (constante) e comparando as somas a uma constante. Ambas as restrições de mão-de-obra e matéria-prima atendem ao requisito de modelo linear.

Nossas restrições de demanda assumem a forma

(Medicamento 1 produzido) <= (demanda do medicamento 1)
(Medicamento 2 produzido) <= (demanda do medicamento 2)
§
(Medicamento 6 produzido) <= (demanda do medicamento 6)

Cada restrição de demanda também satisfaz o requisito de modelo linear, pois cada um é avaliado somando os termos do formulário (célula variável) * (constante) e comparando as somas a uma constante.

Tendo mostrado que o nosso modelo de combinação de produtos é um modelo linear, por que devemos nos preocupar?

• Se um modelo do Solver for linear e selecionar supor o modelo linear, é garantido que o Solver encontre a solução ideal para o modelo do Solver. Se um modelo do Solver não for linear, o Solver poderá ou não encontrar a solução ideal.

• Se um modelo do Solver for linear e selecionar supor o modelo linear, o Solver usará um algoritmo muito eficiente (o método simplex) para encontrar a solução ideal do modelo. Se um modelo do Solver for linear e não selecionar assumir modelo linear, o Solver usará um algoritmo muito ineficiente (o método GRG2) e poderá ter dificuldade para encontrar a solução ideal do modelo.

Depois de clicar em OK na caixa de diálogo opções do Solver, retornamos para a caixa de diálogo principal do Solver, mostrada anteriormente na Figura 27-7. Quando clicamos em resolver, o Solver calcula uma solução ideal (se houver) para nosso modelo de combinação de produtos. Como fiz no capítulo 26, uma solução ideal para o modelo de combinação de produtos seria um conjunto de valores de células variáveis (libras produzidas de cada medicamento) que maximiza o lucro sobre o conjunto de todas as soluções viáveis. Novamente, uma solução viável é um conjunto de valores de células variáveis que atendem a todas as restrições. Os valores de células variáveis mostrados na Figura 27-9 são uma solução viável porque todos os níveis de produção são não negativos, os níveis de produção não ultrapassam a demanda, e o uso dos recursos não excede os recursos disponíveis.

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

• Produzimos mais do medicamento 5 do que a demanda para ele.

• Usamos mais mão-de-obra do que o que está disponível.

• Usamos material bruto mais do que a disponível.

Após clicar em resolver, o Solver encontrará rapidamente a solução ideal mostrada na Figura 27-11. Você precisa selecionar manter solução do Solver para preservar os valores de solução ideais na planilha.

Nossa empresa farmacêutica pode maximizar seu lucro mensal em um nível de $6625.20 produzindo 596,67 libras do medicamento 4, 1084 libras do medicamento 5 e nenhuma das outras drogas! Não podemos determinar se conseguimos alcançar o lucro máximo de $6625.20 de outras maneiras. Basta ter certeza de que, com nossos recursos limitados e demanda, não há nenhuma maneira de fazer mais do que $6627.20 este mês.