Excel – Margem de contribuição do produto

O conceito de margem de contribuição tem o mesmo significado de ganho bruto sobre vendas para o empresário. Ele representa o quanto sobrou das vendas para pagar as despesas fixas e gerar lucro.

Ou seja, é o cálculo de (custo – preço de venda) * quantidade vendida, este valor será a chamada margem de contribuição.

Na nossa planilha realizaremos uma análise custo-volume-lucro no qual identificamos os produtos que mais contribuíram com o ganho bruto da empresa em um determinado período.

Para isso criaremos uma lista com os seguintes dados:

Produto: Código e/ou descrição do produto vendido;

Preço de venda: Preço unitário da venda de cada produto, caso se tenha a receita total pode ser melhor a análise, dado que podem haver descontos ou ágios sobre os preços conforme cada venda;

Custo variável: Custo para a fabricação de cada produto. Neste custo são considerados apenas os custos aplicados diretamente sobre o produto, por exemplo os produtos utilizados na sua fabricação e os tempos de máquina utilizados no mesmo;

Quantidade vendida: Quantidade de produtos vendidos de cada item. Conforme citado anteriormente, se tiver a base dos custos do período não será necessário este campo, substituindo assim o preço de venda por receita total e o custo variável pelo custo total do produto e retirando este campo.

Após a criação da lista inicial iremos criar as colunas calculadas:

Excel - Margem de contribuição do produto 2

Margem de contribuição: Nesta coluna é realizado o cálculo (Preço de venda * Quantidade vendida)-(Custo variável * Quantidade vendida). Este cálculo representa o quanto sobrou do valor total da vanda descontando os custos variáveis. As outras colunas retornaremos em seguida.

Agora será necessário criarmos um resumo da tabela, aonde teremos a categorização dos produtos:

Excel - Margem de contribuição do produto 3

Perceba que temos 3 categorias, é a chamada classificação ABC, que é um método de classificação de informações, para que se separem os itens de maior importância ou impacto, os quais são normalmente em menor número. (Carvalho, 2002, p. 226)

Neste método separamos os 20% itens com maior margem de contribuição, seguido dos 30% com maior contribuição que seguem estes itens e por último na classificação C temos os itens restantes.

Então nas colunas temos:

Categoria: Somente o nome das categorias, por padrão são A, B e C;

Percentual: Classificação dos produtos conforme sua margem de contribuição. No caso são normalmente classificadas desta forma 20% para A, 30% para B e 50% para C;

Qtde.: Quantidade de produtos que estão até aquela categoria, ou seja, na categoria A estão os itens, de maior para menor quanto á margem de contribuição, de 1 á 18. Na classificação B temos os itens de 19 á 45 e na classificação C temos os itens classificados entre 46 á 89.

Para a classificação A a fórmula utilizada foi: =CONT.NÚM($E$2:$E$90)*M2, aonde contam-se os números de uma coluna e multiplica-se pelo valor percentual da categoria.

Para as categorias B e C é utilizada a mesma função somada do valor da categoria anterior: =CONT.NÚM($E$2:$E$90)*(M3+M2).

Voltemos agora á tabela, vamos criar a coluna de classificação:

Excel - Margem de contribuição do produto 4

A coluna de classificação identifica a categoria de cada produto. Para isso utilizamos o seguinte cálculo: =SE(E2>=MAIOR($E$2:$E$90;$N$2);$L$2;SE(E2>=MAIOR($E$2:$E$90;$N$3);$L$3;$L$4)).

Na primeira parte da fórmula é realizada a seguinte verificação:

=SE(E2>=MAIOR($E$2:$E$90;$N$2);$L$2 Se o valor da linha para a coluna Margem de contribuição estiver entre os 18 itens definidos na categoria A então categorizar como A;

;SE(E2>=MAIOR($E$2:$E$90;$N$3);$L$3 Se a condição inicial para categorizar como A não for atendida, identicar se o valor está classificado entre os 45 primeiros itens, da categoria B;

;$L$4 Senão utilizar o valor da célula L4, categorizando como C.

Agora voltamos á tabela de resumo para calcular os campos Valor e Total;

Excel - Margem de contribuição do produto 5

Valor: Incluir o cálculo que soma a categoria =SOMASE(H:H;L2;E:E), neste caso são somados todos os valores da coluna E, que é referente á margem de contribuição, quando na coluna H estiver preenchido com a classificação da célula L2;

Total: Na coluna Total é realizado a soma do valor da categoria mais o valor da categoria anterior. =P2+O3.

A nossa tabela de resumo estará completa, iremos agora concluir a nossa tabela de margem de contribuição:

Excel - Margem de contribuição do produto 6

Análise vertical: Realizar o cálculo de quanto o valor da margem de contribuição representa do total, dividindo o valor pelo valor total das categorias: =E2/$P$4;

Análise vertical acumulada: Nesta coluna é realizado a soma da coluna análise vertical acumulada com o valor do itens anterior.

Após preencher toda a planilha de cálculo selecione a planilha e classifique pelo campo Margem de contribuição do maior para o menor.

Após a criação da planilha e do resumo, já é possível criar o gráfico de análise de curva ABC, este gráfico foi criado utilizando a coluna Margem de contribuição em comparação com a quantidade de itens que chegam á este valor.

Excel - Margem de contribuição do produto 7

Interpretando o gráfico temos que os 20% com maior margem de contribuição, ou 18 primeiros itens são equivalentes á 43,93% de todo o ganho bruto da empresa e que os os primeiros 50% dos itens com maior margem de contribuição no período equivalem á 78,68% de todo o ganho, e que os 50% restantes equivalem á somente 21,32% do ganho. [saiba_mais]

Desta forma temos os produtos aonde devemos investir para aumentar as vendas, produtos que podemos excluir do mix da empresa entre outras análises.