Com as fórmulas determinadas anteriormente, podemos partir para a construção da planilha eletrônica. A Figura 19 mostra uma op- ção de como ela poderia ser elaborada para conter o Modelo matemá- tico para o Problema da Dieta.
Perceba que o aspecto geral da planilha é idêntico ao do proble- ma de Mix de Produção visto no Capítulo I. A linha 2 permanece como a escolhida para descrever a Função-Objetivo do problema. A linha 5 agora é reservada para a quantidade de porções de 100 g a ser incluída no cardápio e, a linha 8, o custo unitário da porção de cada um dos itens alimentícios. Em relação à planilha de Mix de Produção, acres- centamos mais sete linhas para as Restrições, porque agora elas são em número de 10. Assim, as linhas 12, 13, 14, 15, 16, 17, 18, 19 e 20 são reservadas para a entrada destas Restrições.
Figura 19: Planilha para resolução do Problema da dieta
Iniciamos a entrada de dados inserindo, na célula D2, a fórmula da Função-Objetivo, que neste problema, desejamos minimizar. Em outros termos, trata-se do custo total da refeição, representado pela soma dos custos com cada item alimentício (carne, arroz, feijão, cou- ve e banana). Estes custos por item, por sua vez, são calculados pela multiplicação dos seus custos unitários (contidos nas células D8 a H8) pelas respectivas quantidades de porções de 100 g (presentes nas cé- lulas D5 a H5). Assim, na célula D2, devemos digitar:
= (D5*D8)+(E5*E8)+(F5*F8)+(G5*G8)+(H5*H8)
O próximo passo é inserir os valores dos custos unitários de cada item alimentício nas células (D8 a H8). Neste caso, a célula D8 deve conter o valor 0,8 (referindo-se ao preço de R$ 0,8 pela porção de 100g de carne) e assim por diante:
Célula D8 → 0,8 Célula E8 → 0,2 Célula F8 → 0,2 Célula G8 → 0,1 Célula H8 → 0,4
Seguindo com a entrada de dados, vamos preencher os blocos de células reservados para os coeficientes das Restrições (lado esquer- do e direito das inequações). Na nossa planilha, os coeficientes são inseridos nas células que formam o bloco que se inicia na célula D12 e vai até a célula H21. Os valores para o lado direito das Restrições ficam nas células da coluna L, indo de (L12 a L21). Veja na Figura 20 o esquema do preenchimento utilizado, estando indicado pelas setas a entrada de dados da Restrição referente à quantidade de Vitamina A:
Ao acabar de digitar os coeficientes e os valores do lado direito das Restrições, o próximo passo é inserir as fórmulas do lado esquer- do das Restrições do problema, no bloco de células reservado para tal. Veja que na planilha são as células da coluna J, ou seja, de (J12 a J21). Assim: J12 → = (D12*D5)+(E12*E5)+(F12*F5)+(G12*G5)+(H12*H5) J13 → = (D13*D5)+(E13*E5)+(F13*F5)+(G13*G5)+(H13*H5) J14 → = (D14*D5)+(E14*E5)+(F14*F5)+(G14*G5)+(H14*H5) J15 → = (D15*D5)+(E15*E5)+(F15*F5)+(G15*G5)+(H15*H5) J16→ = (D16*D5)+(E16*E5)+(F16*F5)+(G16*G5)+(H16*H5) J17 → = (D17*D5)+(E17*E5)+(F17*F5)+(G17*G5)+(H17*H5) J18 → = (D18*D5)+(E18*E5)+(F18*F5)+(G18*G5)+(H18*H5) J19 → = (D19*D5)+(E19*E5)+(F19*F5)+(G19*G5)+(H19*H5) J20 → = (D20*D5)+(E20*E5)+(F20*F5)+(G20*G5)+(H20*H5) J21 → = (D21*D5)+(E21*E5)+(F21*F5)+(G21*G5)+(H21*H5) As células contêm a soma das multiplicações dos coeficientes da coluna D, pelas respectivas quantidades totais de cada item alimen- tício, contidas nas células (D5 a H5).
Tudo isso é necessário ser digitado na planilha. O passo seguin- te, se você se lembrar bem, é recorrer ao Solver do Excel para a reali- zação dos cálculos. Então, acesse o menu do Excel, escolha Ferra- mentas, e clique na opção Solver.
Figura 20: Esquema de entrada dos dados na Planilha da Dieta
Ao abrir a janela do Solver, vamos informar em que posições na planilha se encontram as células contendo a Função-Objetivo, as Vari- áveis de Decisão e os lados esquerdo e direito das Restrições. A Figu- ra 21 mostra como deve ficar a entrada desses dados para o Modelo do Problema da Dieta. Essa entrada deve ser feita em blocos, ou seja, as regiões da planilha que são as entradas dos Parâmetros do Solver são selecionadas arrastando o mouse da célula inicial até a célula final desejada.
Caso tenha dúvidas de como utilizar a seleção em blocos, peça ajuda ao seu tutor para maiores explicações.
Você precisa se certificar de escolher a opção Min para que o
Solver resolva o problema como uma minimização da Função-Objeti-
vo. Isto porque, como você deve se lembrar, queremos minimizar o custo da merenda. Veja na figura anterior onde deve ser escolhida esta opção.
Para executar a otimização, basta clicar no botão resolver e ins- tantaneamente o Excel calculará a solução.
Se você seguiu as instruções corretamente, observe que as célu- las sombreadas em cinza claro que indicam a quantidade total de ali- mento tiveram seus valores modificados da mesma forma que a célula D2, que contém o custo mínimo da merenda. Outras células que tive-
Figura 21: Regiões da planilha utilizadas como entradas no Solver
ram seus conteúdos alterados foram as células de (J12 a J21), conten- do os valores obtidos com a solução para cada uma das Restrições. Veja na Figura 22 o aspecto final da planilha, após a execução dos cálculos pelo Solver.
Figura 22: Aspecto final da planilha do Problema da Dieta
Fonte: elaborada pelos autores
Vamos agora à análise dos resultados dos cálculos. Qual seria a merenda ideal a ser adotada pelo município? A resposta está contida nas células (D5 a H5). Note que os valores ali presentes representam o número de porções de 100g de cada um dos alimentos disponíveis. Assim: Carne → 0,5 x 100g = 50g Arroz → 3,98 x 100g = 398g ≈ 400g Feijão → 0,8 x 100g = 80g Couve → 4,95 x 100g = 495g Banana → 0,5 x 100g = 50g
Discuta essa distorção com seu Professor / Tutor: quais seriam suas implicações em termos gerenciais?
O preço total desta refeição seria de R$ 2,05 (resultado da Fun- ção-Objetivo na célula D2), representando o menor valor possível para as Restrições utilizadas na Modelagem do problema.
Note que o total de alimentos (as soma das quantidades de car- ne, arroz, feijão, couve e banana) para a merenda seria de aproxima- damente 1,1 kg, o que, na prática, é uma quantidade irreal para crian- ças em idade escolar. É difícil acreditar, principalmente, que uma cri- ança comeria 495g de couve, um valor excessivamente grande a ser ingerido numa única refeição. Isto demonstra que o problema de otimização também serve para indicar distorções no problema original proposto. Neste caso, a nutricionista poderia sugerir um outro alimen- to complementar para substituir parte da couve e tornar a refeição mais balanceada. Então, uma nova Variável de Decisão e novas Restrições seriam acrescidas ao problema, havendo a necessidade de outros cál- culos para buscar a Solução Ótima.