Utilizando o “Solver” para resolver problemas de otimização em sua empresa (PARTE 2)

Atualizado em 29/11/19 - Escrito por Celso Monteiro na(s) categoria(s): Custos e Finanças / Engenharia de produto / Estratégia / Processos e Organização

Seis Sigma

No artigo anterior falamos sobre programação linear e como representar um problema matematicamente. Para refrescar a memória, utilizamos como exemplo a empresa fictícia Diversão S.A., que buscava, dentro do seu cenário e restrições e condições custos e preços praticados, otimizar os lucros a partir da quantidade semanal a ser produzida para cada um de seus brinquedos de madeira (Trem e Avião).

Identificamos que, matematicamente, o cenário da empresa é descrito pela seguinte representação:

– X1 = Qtd a produzir de trem / semana

– X2 = Qtd a produzir de avião / semana

OBJETIVO -> Max(lucro) = 2*X1 + 3*X2

RESTRIÇÕES -> 1*X1 + 1*X2 <= 80

                          1*X1 + 2*X2 <= 100

                          X2 <= 40

                          X1 >= 0

                          X2 >= 0

Agora, partimos para incluir esse cenário no Excel e analisar os resultados obtidos através  do Solver. Pois bem, o primeiro passo aqui é verificar se o seu Excel já está com esse suplemento  habilitado. Caso essa seja a primeira vez que você tenha ouvido falar de Solver, é muito provável que o seu Excel não está com ele ativo e você precisará habilitá-lo antes de iniciar a parametrização.

Solver Excel

Como eu utilizo o Excel 2007, os passos que darei no artigo são exclusivos para essa versão , porém, para aqueles  que possuem versões diferentes basta acessar o Youtube e buscar por “tutorial para habilitar o solver no Excel XXXX (Ano da sua versão)”.

O primeiro passo é clicar em no botão “Iniciar” e clicar sobre o botão “Opções do Excel”.

Agora, vá em Suplementos e clique no botão Ir… ao lado direito de Gerenciar.

Após o Excel carregar todos os suplementos da sua versão, clique na caixinha ao lado do suplemento Solver e clique em OK.

Pronto, agora o seu Excel irá instalar o suplemento e você poderá utilizá-lo para resolver seus problemas de programação linear.

Com o Solver instalado, vamos iniciar a inclusão dos parâmetros da nossa representação matemática no Excel. Assim como identificado no artigo anterior, sugiro seguirmos a mesma  ordem para a parametrização do problema no Excel: Identificar variáveis, função objetivo, restrições.

Variáveis do problema

Temos duas variáveis no nosso exemplo X1 (quantidade a produzir de trem/semana) e X2 (quantidade a produzir de avião/semana). Vamos informar essas variáveis da  seguinte maneira:

Criamos duas linhas, uma para o trem e outra para o avião, e ao lado de cada um deles informar o valor igual a zero.

Função objetivo

A função objetivo será uma representação do lucro de cada um dos produtos , caso a produção seja vendida. Logo, a mesma será representada da seguinte forma:

Na célula B9, referente a função objetivo, eu passei o seguinte valor:

=2*B3+3*B4

Onde B3 é a célula referente a quantidade de trem e B4 é a célula para a quantidade de aviões.

Restrições

Para não confundir uma restrição com a outra, sugiro criá-las em linhas distintas e sempre identificá-las com expressões que possam ser facilmente relacionadas a elas individualmente.

Para facilitar a compreensão, irei informar as fórmulas usadas em cada uma das restrições e configuradas na coluna B.

TEMPO CARPINTARIA(h) = 1*B3+1*B4

TEMPO PINTURA (h) = 1*B3+2*B4

PRODUÇÃO NÃO NEGATIVA TREM = B3

PRODUÇÃO NÃO NEGATIVA AVIÃO = B4

PRODUZIR ATÉ 40 AVIÕES = B4

Na coluna C eu indiquei a condição (se era maior ou menor) apenas para referência  e na coluna D eu informei os valores de cada restrição, conforme identificado anteriormente.

Parametrizando o Solver

Agora, com toda a esquematização pronta, você precisará acessar o menu superior do Excel, entrar na aba Dados e selecionar o ícone do Solver, que  ficará na parte direita do menu, ao final de todas as opções.

Após o Excel abrir o Solver, ele pedirá para que você informe todos os parâmetros para que ele calcule corretamente o resultado da sua equação.

Na linguagem do Solver, a célula de destino é a célula que você definiu a função objetivo, que, no meu caso, é a B9. Basta você clicar na imagem que contém uma pequena tabela, à direita do campo para a definição da célula de destino, e selecionar a célula que está representando a função objetivo na sua planilha. Como a minha é a B9 o Excel representou a mesma como $B$9.

Em igual a, selecionar qual a otimização você quer obter com a função objetivo. Como a Diversão S.A. busca maximizar seu lucro semanal, selecionei a opção Máx.

Na seção Células variáveis clicar no mesmo ícone ao lado direito e selecionar na tabela quais são as células que representarão as variáveis da equação. No meu caso, as variáveis são as células B3 e B4, onde o Solver as representou como $B$3:$B$4.

Na seção Submeter às restrições você irá clicar no botão Adicionar e incluirá todas as restrições, uma a uma.

Na imagem acima, estou incluindo a restrição do tempo semanal da carpintaria, o campo Referência de célula representa a função da restrição de carpintaria, que está  configurada na célula B14, selecionei a condição <= (menor ou igual) e no campo Restrição selecionei a célula D14, a qual expressa o total de horas da carpintaria na semana. Para incluir as demais  restrições, clique no botão Adicionar até que, após incluir a última restrição, você clique no botão OK e finalize a inserção das restrições.

Após informar todas as restrições e clicar no botão OK, confira se todas estão corretas e clique no botão Resolver.

Após o Solver ter realizado todos os cálculos, ele apresentará (de acordo com o nosso exemplo) os seguintes resultados:

De acordo  com os resultados apresentados, a quantidade a ser produzida/semana de trem será de 60 unidades e de aviões 20 unidades, com um lucro de R$ 180,00/semana. Ao verificarmos os resultados das restrições, vemos quais restringiram diretamente o resultado da equação.

Com essa produção, a quantidade de horas utilizadas da carpintaria (Célula B14) e da pintura (Célula B15) são exatamente iguais a capacidade de cada setor. Ou seja, as restrições que limitaram o crescimento do lucro da empresa é a “baixa” disponibilidade de seus recursos de produção, caso a empresa investisse em mais maquinário, poderia aumentar a sua produção bem como a oferta de seus produtos. As restrições de não negatividade das duas variáveis foram respeitadas (ambos produtos tiveram quantidade maior do que zero) e a restrição de produzir até 40 aviões por semana também foi respeitada, já que a quantidade sugerida pelo Solver foi de 20, menor do que as 40 unidades da restrição.

Outra função do Solver

O Solver ainda oferece uma ferramenta fantástica que é a análise de sensibilidade desses resultados, o qual consegue informar qual restrição precisa ser aumentada para potencializar ainda mais o resultado da função objetivo. No nosso caso, temos duas máquinas (carpintaria e pintura), a análise de sensibilidade informará qual dessas duas restrições trará mais lucro para o resultado da equação, caso a restrição seja aumentada (no caso da carpintaria, se ela tiver mais do que 80h/semana e da pintura ter mais do que 100h/semana). Mas, isso já é assunto para um outro artigo, que pretendo montar mais para a frente…

Caso ainda não tenha visto, confira o primeiro artigo sobre como utilizar o Solver para resolver problemas de otimização em sua empresa.

Nomus ERP Industrial

Compartilhe esta matéria:


Tags:


Engenheiro de Produção formado pelo CEFET e especialista em implantação de sistemas de gestão Industrial na Nomus. Celso já atuou em fábricas de diversos setores, como: metal-mecânica, materiais de escritório, artefatos de concreto, perfuração, cabos e cordas navais, têxtil (confecção e tinturaria), reciclagem de metal, dentre outros segmentos.


Participe! Deixe o seu comentário agora mesmo: