Mix de Produção no Excel
Novamente, como no item de interpretação gráfica de um Pro- blema de Programação Linear, antes de partir para a construção da planilha eletrônica, vamos relembrar o Modelo determinado para o problema de Mix de Produção, em sua Forma Canônica:
Encontrar: z, x 1 e x2 Maximizar: z = 50x1 + 40x2 Restrito a: 30x 1 + 20x2≤ 360 5x1 + 10 x2 ≤ 120 x 1≤ 20 x1 ≥ 0 e x2 ≥ 0
A Figura 10 mostra como uma planilha poderia ser construída para conter o Modelo matemático deste problema.
Figura 10: Planilha construída para resolução do problema
Veja que procuramos separar as regiões horizontalmente e explicitá-las de modo que facilmente se perceba onde se encontram cada parte do Modelo. A linha 2 foi escolhida para descrever a Fun- ção-Objetivo do problema. A linha 5 para o número de itens fabrica- dos. Na linha 8, colocamos o lucro unitário que a empresa obtém para cada um dos itens. Finalmente, as linhas 12, 13 e 14 são reservadas para a entrada das Restrições do Problema.
Para facilitar ainda mais a visualização do problema, alguns blo- cos foram sombreados em cinza claro. Serão estas células que terão seus conteúdos modificados pelo Solver do Excel. As outras células utilizadas, em fundo branco, contêm valores numéricos ou fórmulas que devem ser introduzidas diretamente pelo usuário.
Vamos começar inserindo, na célula D2, a fórmula da função que desejamos maximizar. No caso, o lucro total da produção, repre- sentado pela soma dos lucros conseguidos com cada item; estes calcu- lados pela multiplicação dos seus lucros unitários (células D8 e E8) pelas respectivas quantidades produzidas (células D5 e E5). Assim, na célula D2, digitamos:
= (D5*D8) + (E5*E8)
O próximo passo é inserir os valores dos lucros unitários de cada item produzido pela empresa nas células D8 e E8. No nosso exemplo, D8 deve conter o valor 50, e E8 o valor 30.
Em seguida, devemos preencher os blocos de células reservados para os coeficientes das Restrições (lado esquerdo e direito das inequações). Na planilha, os coeficientes são inseridos nas células D12, D13, D14, E12, E13 e E14. Os valores para o lado direito das Restri- ções ficam nas células I12, I13 e I14. Veja um exemplo na Figura 11:
Figura 11: Esquema de alocação das Restrições na planilha
(O Solver faz através de seu algoritmo o que foi visto na abordagem gráfica anteriormente.)
Assim que acabar de digitar os coeficientes, você pode prosse- guir inserindo as fórmulas do lado esquerdo das Restrições do Modelo no bloco de células reservado para tal. Em nossa planilha de exemplo, este bloco de células trata-se das células G12, G13 e G14. Assim, G12 deve conter:
= (D12*D5)+(E12*E5)
que representa a soma das multiplicações dos coeficientes em D12 e E12 pelas respectivas quantidades produzidas de cada item, contidas nas células D5 e E5. Da mesma forma, as outras inequações são inseridas em G13:
= (D13*D5)+(E13*E5) e, em seguida, na célula G14:
= D5
Assim, finalizamos a entrada de dados na planilha. Tudo o que resta agora é recorrer ao Solver do Excel para a realização dos cálcu- los. Para isso, basta ir até o menu do Excel, escolher ferramentas, e clicar na opção Solver. Veja a Figura 12:
Figura 12: Localização do Solver no menu do Excel
Uma janela para entrada de Parâmetros se abrirá. É preciso ago- ra informar ao Solver em que posições na planilha se encontram as células contendo a Função-Objetivo, as Variáveis de Decisão (que no
Solver do Excel são chamadas de “células variáveis”) e os lados es-
querdo e direito das Restrições. Veja na Figura 13 a posição de cada um dos blocos de células e o aspecto final da tela de entrada de dados do
Solver, para o Modelo do problema do Mix de Produção a ser calculado.
Caso você encontre dificuldades no uso do Solver, consul- te o seu tutor sobre os passos necessários para entrada dos dados e a execução dos cálculos.
Figura 13: Tela do Solver
Fonte: elaborada pelos autores
Na janela de Parâmetros do Solver existe também o botão op- ções que dá acesso à janela da figura a seguir. Para os exemplos que utilizaremos nesta apostila, devemos presumir que o Modelo é linear e que as soluções deverão ser sempre não negativas. Assim, selecione essas duas opções de Parâmetros, no local indicado pela seta na Figu- ra 14. Note que existem outras opções na tela do Solver que não se aplicam aos problemas que estamos tratando neste curso.
Depois de preencher as opções como indicado, pressione a tecla
ok. A janela se fechará e a tela anterior será mostrada. Agora para
executar o algoritmo de otimização, basta clicar no botão resolver e instantaneamente o Excel calculará a solução. Se você aceitar o cálcu- lo, o programa retornará automaticamente para a planilha, onde po- dem ser observados os resultados.
Note que as células sombreadas em cinza claro tiveram seus va- lores modificados, como esperado. De principal importância, são as células D2, que contém a Função-Objetivo a ser maximizada (lucro máximo que pode ser obtido pela empresa) e as células D5 e E5, mos- trando a quantidade de itens que devem ser fabricados para que se obtenha este lucro máximo. Além disso, as células (G12 a G14) mos- tram agora os valores obtidos com a solução para cada uma das Res- trições. Veja na Figura 15 que todos estes valores se encontram dentro dos limites desejados.
Como vimos anteriormente, o lucro máximo para este Problema de Mix de Produção é de R$ 660,00, obtido com a fabricação diária de 6 itens A e 9 itens B por cada funcionário da empresa.
Figura 14: Tela de opções do Solver
Figura 15: Planilha resolvida para o problema de Mix de Produção