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

Observação: Desejamos fornecer o conteúdo da Ajuda mais recente no seu idioma da forma mais rápida possível. Esta página foi traduzida de forma automatizada e pode conter imprecisões ou erros gramaticais. Nosso objetivo é que este conteúdo seja útil para você. No final da página, deixe sua opinião se estas informações foram úteis para você. Aqui está o artigo em inglês para facilitar a referência.

Este artigo discute o uso do Solver, um programa de suplemento do Microsoft Excel que você pode usar para o analisador de hipóteses, para determinar uma combinação ideal de produtos.

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, mostrado na Figura 27-1.

Imagem de livro

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

Observação:  Conforme explicado no capítulo 26, "introdução à otimização com o Excel Solver", o Solver está instalado clicando no botão do Microsoft Office, em opções do Excel, seguidas por suplementos. Na lista gerenciar, clique em suplementos do Excel, marque a caixa de suplemento solver e clique em OK.

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

Imagem de livro

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.

Imagem de livro

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.

Imagem de livro

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.

Imagem de livro

Garantimos que, quando o Solver tentar valores diferentes para as células variáveis, somente as combinações que satisfaçam D14< = F14 (mão-de-obra usada é menor que ou igual a mão-de-obra) e D15< = F15 (o material bruto usado é menor ou igual a material bruto disponível) será considerada. 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.

Imagem de livro

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 (o valor 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 é 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.

Imagem de livro

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.

Imagem de livro

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

Imagem de livro

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.

Imagem de livro

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.

Imagem de livro

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.

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

Imagem de livro

Selecione > = e, em seguida, clique em OK. Garantimos que o Solver considere alterar apenas os valores das células que atendem a todas as demandas. Ao clicar em resolver, você verá a mensagem "o Solver não encontrou uma solução viável". Essa mensagem não significa que cometemos um equívoco em nosso modelo, mas que, em vez disso, com nossos recursos limitados, não podemos atender à demanda de todos os produtos. O Solver é simplesmente informar que, se quisermos atender à demanda de cada produto, precisamos adicionar mais mão-de-obra, mais materiais brutos ou mais de ambos.

Vamos ver o que acontece se permitisse a demanda ilimitada para cada produto e permitimos que quantidades negativas sejam produzidas de cada medicamento. (Você pode ver o problema do Solver na planilha definir valores não Convera planilha no arquivo prodmix. xlsx.) Para encontrar a solução ideal para essa situação, abra o Solver, clique no botão Opções e desmarque a caixa preSumir não negativo. Na caixa de diálogo parâmetros do Solver, selecione a restrição de demanda D2: I2< = D8: i8 e, em seguida, clique em Excluir para remover a restrição. Quando você clica em resolver, o Solver retorna a mensagem "definir valores da célula não convergem". Essa mensagem significa que, se a célula de destino for 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 conver" 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 um medicamento, nos empenhamos nos "criar" recursos que podem ser usados para produzir quantidades arbitrariamente grandes de outras drogas. Devido à nossa demanda ilimitada, isso nos permite fazer lucros ilimitados. Em uma situação real, não podemos fazer um valor infinito de dinheiro. Em resumo, se você vir "definir valores não convergir," seu modelo tem um erro.

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

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

    Produto

    Técnico A

    Técnico B

    Técnico C

    Técnico D

    1

    2

    2,5

    Não pode fazer

    Não pode fazer

    2

    Não pode fazer

    3

    Não pode fazer

    3,5

    3

    3

    Não pode fazer

    4

    Não pode fazer

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

  4. Uma fábrica de fabricação de computador produz um mouse, teclados e joysticks de videogame. O lucro por unidade, o uso de trabalho por unidade, a demanda mensal e o uso de tempo de máquina por unidade são fornecidos na tabela a seguir:

    Mouses

    Teclados

    Joysticks

    Lucro/unidade

    $8

    $11

    $9

    Uso/unidade de trabalho

    .2 horas

    .3 horas

    .24 horas

    Hora/unidade do computador

    .04 hora

    .055 hora

    .04 hora

    Demanda mensal

    15.000

    27.000

    11.000

  5. A cada mês, um total de 13.000 horas de trabalho e 3000 horas de tempo de máquina está disponível. Como o fabricante pode maximizar a contribuição de lucro mensal da fábrica?

  6. Resolva nosso exemplo de medicamento pressupondo que uma demanda mínima de 200 unidades para cada medicamento deve ser atendida.

  7. Jason faz o Diamond pulseiras, o Necklaces e o brincos. Ele quer trabalhar no máximo 160 horas por mês. Ele tem 800 onças de ouros. O lucro, o tempo de trabalho e as onças de ouros necessários para produzir cada produto são fornecidos abaixo. Se a demanda de cada produto for ilimitada, como Jason pode maximizar seu lucro?

    Produto

    Lucro unitário

    Horas de trabalho por unidade

    Onças de losangos por unidade

    Pulseira

    $300

    .35

    1,2

    Necklace

    $200

    .15

    .75

    Brincos

    $100

    0,05

    5

Expanda suas habilidades no Office
Explore o treinamento
Obtenha novos recursos primeiro
Ingressar no Office Insider

Essas informações foram úteis?

Obrigado por seus comentários!

Agradecemos pelos seus comentários! Parece que pode ser útil conectar você a um de nossos agentes de suporte do Office.

×