A Análise de Dados tem como finalidade permitir tomar as melhores decisões em função dos objetivos definidos pelas empresas. Estes objetivos incluem, normalmente, maximizar lucros, minimizar custos, reduzir riscos financeiros, etc…

Assim, os decisores procuram saber se a oportunidade de investir num novo negócio será lucrativa, procuram prever a procura e estimar os resultados financeiros para que possam planear devidamente a afetação de recursos financeiros, humanos e materiais ou, ainda, procuram lidar com o risco e a incerteza da melhor forma possível.

Encontrar a melhor solução para um determinado problema pode ser resolvido através do Solver, na medida em que se pretende otimizar (maximizar ou minimizar) um determinado objetivo.

Enquanto ferramenta de otimização, o Solver serve para resolver problemas de minimização, maximização ou atingir um determinado valor específico para um objetivo.

Um modelo de otimização tem 3 elementos fundamentais:

• Uma célula objetivo;
• Células variáveis e
• Restrições.

A célula objetivo representa, conforme o nome indica, o objetivo a alcançar. Se, por exemplo, o nosso objetivo for minimizar custos de produção essa célula terá de refletir essa fórmula de cálculo. As células variáveis são o conjunto de células que se alteram para otimizar a célula objetivo. As restrições, são condições colocadas pelo modelo que têm de ser cumpridas para que o objetivo seja atingido.

Quando estamos perante problemas de minimização ou de maximização de uma função objetivo, não sabemos antecipadamente qual vai ser o resultado. Apenas sabemos que temos de obter o valor mais baixo ou mais alto possível, respetivamente.

Os problemas mais comuns são a minimização de custos e a maximização do lucro. Vamos resolver um problema de otimização da produção de vários produtos, ou seja, maximização do lucro em função de determinadas condições de produção. Neste caso, teremos de determinar a quantidade ótima a produzir de cada produto, tendo por base as seguintes restrições:

• A produção não pode usar mais recursos do que os que estão disponíveis,
• A produção não pode ser maior do que a procura, para não termos excedentes.

Da análise dos nossos dados, na figura seguinte, podemos reter o seguinte:

• Cada produto gasta um determinado nº horas e 2 tipos de matérias-primas
• Temos de calcular os custos de produção para cada produto, bem como o preço de venda líquido.
• Temos de calcular o tempo gasto na produção de cada produto, bem como a quantidade de matérias primas correspondentes.
• A nossa função objetivo será, então, maximizar o lucro da produção dos 4 produtos.

Como começamos?

O primeiro passo será definir a fórmula da função objetivo. Uma vez que a função objetivo é maximizar o lucro, teremos de encontrar a fórmula do seu cálculo. O lucro será, então, calculado por Preço Venda Líquido * Produção.

Como temos valores correspondentes em dois intervalos de células, podemos usar a função SOMARPRODUTO, conforme a imagem seguinte:

Claro que a célula não vai apresentar nenhum valor agora, uma vez que ainda não calculamos o P.V. líquido. Esse é o segundo passo.

Para calcular o PVL temos de calcular os custos de produção para cada produto. Assim, calculamos o custo de produção através da multiplicação de cada fator de produção pelo seu custo e respetiva soma, ou seja:

=MOD utilizada * MOD preço/hora + MP utilizada * Custo MP

Na figura seguinte podemos ver a aplicação desta expressão:

De notar que as células de preço estão com referência absoluta, o que permite arrastar a fórmula para as outras células, poupando tempo.

Calculamos agora o PVL, fazendo a subtração do preço unitário pelos custos produção. Aqui chegados, falta calcular os valores gastos de MOD e MP1 e de MP2. Calcularemos cada um deles, multiplicando o consumo pela unidade produzidas (produção). Aqui também podemos utilizar a função SOMARPRODUTO.

Assim, temos:

Agora que temos todos cálculos efetuados, estamos em condições de utilizar o Solver para obtermos a nossa otimização.

Selecionamos a célula da função objetivo (destaca a amarelo) e clicamos no comando do Solver, no separador Dados do friso. Abre-se a caixa dos Parâmetros do Solver:

Conforme podemos ver, temos os 3 elementos fundamentais que teremos de preencher:

• Definir Objetivo: maximizar célula objetivo
• Células variáveis: intervalo de dados da produção
• Restrições:
  • As unidades a produzir têm de ser igual ou menores do que a procura
  • As unidades têm de ser um número inteiro
  • Os recursos disponíveis não podem ser ultrapassados

Definimos, também, o método de resolução GRG, por ser um problema não linear e ativamos a caixa de «Tornar Não Negativas Variáveis Não Constrangidas» para evitar que as variáveis de decisão sejam negativas. Depois de preenchidos, só temos de clicar no botão Resolver.

O Solver encontrou uma solução que satisfaz todas as restrições e condições de otimização. Este é um exemplo da aplicabilidade e utilidade do Solver, sendo igualmente interessante na resolução de exercícios mais complexos.