Utilizar o Solver para determinar a 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 em intervalos de células e devolve a soma desses valores. Cada intervalo de células utilizado numa avaliação de SOMARPRODUTO tem de ter as mesmas dimensões, o que implica que pode utilizar SOMARPRODUTO 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 SOMARPRODUTO($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 SOMARPRODUTO(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 mudança, 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 (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 de produção de 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 produzido é menor ou igual à procura do Medicamento 4)

• H2<=H8 (a quantidade produzida do Fármaco 5 feita é menor ou igual à procura do Medicamento 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 ao adicionar os termos do formulário (alterar célula)*(constante).

• Cada restrição satisfaz o "requisito de modelo linear". Isto significa que cada restrição é avaliada ao adicionar os termos do formulário (alterar célula)*(constante) e comparar as somas com uma constante.

Porque é que este problema do Solver é linear? A nossa célula de destino (lucro) é calculada como

(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)

Esta computação segue um padrão no qual o valor da célula de destino é derivado ao adicionar termos do formulário (alterar célula)*(constante).

A nossa restrição laboral é avaliada comparando o valor derivado de (Trabalho usado por quilo de Droga 1)*(Droga 1 libras produzidas) + (Trabalho usado por quilo de Droga 2)*(Droga 2 libras produzidas)+ (Labore-nosed por libra de Droga 6)*(Droga 6 libras produzidas) para o trabalho disponível.

Por conseguinte, a restrição laboral é avaliada ao adicionar os termos do formulário (alterar célula)*(constante) e comparar as somas com uma constante. Tanto a restrição de mão-de-obra como a restrição da matéria-prima satisfazem o requisito do modelo linear.

As nossas restrições de procura assumem o formulário

(Droga 1 produzido)<=(Procura de Fármaco 1)
(Droga 2 produzido)<=(Procura do Fármaco 2)
{
(Droga 6 produzido)<=(Procura de Fármaco 6)

Cada restrição de procura também satisfaz o requisito de modelo linear, uma vez que cada um é avaliado ao adicionar os termos do formulário (alterar célula)*(constante) e comparar as somas com uma constante.

Depois de mostrar que o nosso modelo de combinação de produtos é um modelo linear, por que devemos preocupar-nos?

• Se um modelo do Solver for linear e selecionarmos Assumir Modelo Linear, é garantido que o Solver encontrará a solução ideal para o modelo 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 selecionarmos Assumir Modelo Linear, o Solver utiliza 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 selecionarmos Assumir Modelo Linear, o Solver utiliza 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 Solver, regressamos à caixa de diálogo solver principal, apresentada anteriormente na Figura 27-7. Quando clicamos em Resolver, o Solver calcula uma solução ideal (se existir uma) para o nosso modelo de combinação de produtos. Como referi no Capítulo 26, uma solução ideal para o modelo de combinação de produtos seria um conjunto de alterações dos valores das células (libras produzidas por cada fármaco) que maximiza o lucro em comparação com o conjunto de todas as soluções viáveis. Uma vez mais, uma solução viável é um conjunto de valores de células que satisfazem todas as restrições. Os valores das células variáveis apresentados 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 procura e a utilização de recursos não excede os recursos disponíveis.

Os valores das células variáveis apresentados na Figura 27-10 na página seguinte representam uma solução inviável pelos seguintes motivos:

• Produzimos mais droga 5 do que a procura por ela.

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

• Utilizamos mais matéria-prima do que aquilo que está disponível.

Depois de clicar em Resolver, o Solver encontra rapidamente a solução ideal apresentada na Figura 27-11. Tem de selecionar Manter Solução solver para preservar os valores de solução ideais na folha de cálculo.

A nossa farmacêutica pode maximizar o 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 conseguimos obter o lucro máximo de $6.625.20 de outras formas. Tudo o que podemos ter a certeza é que, com os nossos recursos e procura limitados, não há forma de ganhar mais de $6.627,20 este mês.