Por vezes o sistema informático da empresa – tipicamente um ERP – dispõe de um conjunto de relatórios padronizados que nos permite responder a diversas necessidades de análise. Outras vezes, porém, enquanto analistas, somos confrontados com questões sobre o desempenho da empresa que não conseguimos responder a partir desse conjunto de relatórios. Temos então que aceder a extensas listas de dados com informação detalhadas mas pouco estruturada. Neste artigo vamos ver como podemos partir de uma listagem de vendas e construir um relatório de análise sintético e que nos ajudará a responder a diversas perguntas comuns.

Suponha que o ponto de partida é o seguinte relatório de vendas para o 1º quadrimestre do ano:

Esta é uma lista extensa. Como pode verificar no ficheiro em anexo (que pode descarregar gratuitamente para o seu computador), a lista está no primeiro separador e contém 40 registos. Para o nosso exemplo serve perfeitamente para ilustrar as técnicas que veremos de seguida. A mesma lista poderia ter milhares (ou mesmo milhões) de registos, seria indiferente.

Somos confrontados com as seguintes questões:

• Qual o número de transações por mês?
• Quantas dessas transações têm uma quantidade superior a 50.000 unidades?
• Como tem evoluído o preço médio de venda e a margem média ao longo dos meses?
• Qual é a margem total a cada mês?

Uma boa forma de atacar estes problemas seria construir uma tabela que permitisse estruturar as respostas. É o que fazemos no segundo separador:

Na primeira coluna, o campo ‘Mês’ deve estar formatado como data. Muitas vezes se comete o erro de formatar este campo como texto, o que impede o bom funcionamento das fórmulas que utilizamos para resolver o problema.

Então, para calcular o número de transações teríamos que contar o número de células da nossa lista cujas datas se situam no mês de Janeiro de 2014. Para isso, uma solução possível seria fazer uso da fórmula COUNTIFS.

Esta fórmula permite contar o número de células num determinado intervalo que cumprem uma ou mais condições. Assim:

• O intervalo de células a contar está no intervalo A2 a A41 do 1º separador;
• A 1ª condição é que a data seja superior ou igual a 1 de Janeiro de 2014
• A 2ª condição é que a data seja inferior a 1 de Fevereiro de 2014

Seria mais fácil exprimir esta fórmula a partir da seguinte janela:

Repare que quando utilizamos um critério de comparação de valores – no nosso caso “maior ou igual” ou “menor” do que – temos de colocar o operador entre aspas seguido do símbolo & que representa a operação de concatenação. É uma pequena nuance que causa frequentemente muitas dúvidas.

Para os restantes meses, o cálculo será semelhante.

Para responder à pergunta seguinte, podemos usar a mesma fórmula. Desta vez, temos que acrescentar mais uma condição às anteriores: a quantidade tem de ser superior a 50.000 unidades.

Então, teríamos:

Podemos verificar rapidamente nesta janela que o número de transações do mês de Janeiro com quantidade superior a 50.000 foi igual a 4.

Para responder à pergunta: “Como tem evoluído o preço médio de venda e a margem média ao longo dos meses?”, podemos acrescentar duas colunas à nossa tabela original. Ao fazê-lo vamos simplificar os cálculos.

A primeira coluna adicional será a Faturação e para a calcular basta multiplicar a quantidade vendida pelo preço de venda.

De seguida, acrescentamos a coluna Margem Total multiplicando a quantidade vendida pela margem.

Chegamos então à tabela seguinte:

Vamos calcular o preço médio de venda e a margem média dividindo a faturação e a média, respetivamente, pela quantidade vendida a cada mês.

Este cálculo é um pouco mais complexo do que os anteriores por duas razões:

1. Implica a divisão de dois valores
2. Cada um desses valores é calculado pela fórmula SUMIFS que contém dois intervalos:

• Um intervalo de células a somar
• Um intervalo de células com datas

No primeiro caso, a fórmula do cálculo do numerador (a faturação total do mês de Janeiro) é dada pelo seguinte:

Em que o intervalo de soma, isto é, o primeiro argumento da fórmula é o que consta da coluna F e intervalo de datas da coluna A. Os critérios para seleção do mês são idênticos aos das fórmulas anteriores.

A fórmula do cálculo do denominador é muito parecida, apenas se substituindo o primeiro argumento que agora se refere à coluna B – quantidades.

O cálculo da Margem média segue exatamente o mesmo raciocínio, sendo suficiente trocar a coluna F pela coluna G na nossa fórmula do numerador.

Por fim, o cálculo da Margem total resume-se ao cálculo já efetuado no numerador da fórmula Margem Média. Seria a seguinte:

Como vê, tudo o que tem de fazer é compreender as fórmulas SUMIFS e COUNTIFS para construir o resumo da análise pretendida. O resultado final seria o seguinte: