Como posso calcular a análise de ponto de equilíbrio no Excel?

A análise de ponto de equilíbrio é o estudo de qual quantidade de vendas, ou unidades vendidas, é necessária para quebrar mesmo após a incorporação de todos os custos fixos e variáveis ​​da execução das operações do negócio. A análise do ponto de equilíbrio é crítica no planejamento de negócios e nas finanças corporativas, porque as premissas sobre custos e possíveis vendas determinam se uma empresa (ou projeto) está no caminho da lucratividade.

Como um exemplo simples, vamos tomar uma banca de limonada. Não importa quanta limonada você vende, você ainda precisa pagar a mesma quantia de aluguel por mês. Portanto, o aluguel é um custo fixo. Compare isso com as despesas com limões: você só comprará a quantidade de frutas necessária para satisfazer a demanda de limonada prevista para o seu negócio. No verão, isso pode ser mais limões; no inverno, menos limões. Isso faz com que as compras de limão tenham um custo variável. Os custos variáveis ​​aumentam proporcionalmente aos seus negócios.

Fórmula para Análise de Equilíbrio

Com o exemplo acima em mente, o ponto de equilíbrio ocorre quando:

Custos fixos totais + Custos variáveis ​​totais = receita

• Os custos fixos totais são geralmente conhecidos; eles incluem itens como aluguel, salários, serviços públicos, despesa de juros, depreciação e amortização.
• Os custos variáveis ​​totais são mais difíceis de saber, mas são estimados e incluem itens como material direto, mão de obra faturável, comissões e taxas.
• A receita é preço unitário * Número de unidades vendidas

Com esta informação, podemos resolver qualquer peça do quebra-cabeça algebricamente. Antes de ilustrar isso, há alguns pontos que vale a pena mencionar.

A primeira é que, dentro da fórmula, há divergências quanto ao melhor equalizador. A definição padrão é receita, mas o problema com o uso da receita é que dizer "precisamos vender um valor X para cobrir custos" exclui impostos, que são uma despesa muito real. No planejamento de negócios, você deve sempre considerar como é a receita operacional após impostos e essa medida é chamada de Lucro operacional líquido após impostos (NOPAT). Ao usar o NOPAT, você incorpora o custo de todas as operações reais, incluindo o efeito dos impostos. No entanto, a definição amplamente entendida usa receita, e é isso que usaremos.

O segundo ponto crítico é que o núcleo desta fórmula é a margem de contribuição, que desempenha um papel crítico na alavancagem operacional.

A terceira coisa que vale a pena notar é que cada parte da equação - custos fixos totais, custos variáveis ​​totais e receita total - pode ser expressa como "Total" ou como uma medida por unidade, dependendo da medida de equilíbrio específica que exigimos. Isso é explorado mais detalhadamente em nosso exemplo do Excel.

Vendas de ponto de equilíbrio

Ao analisar o total de vendas, a medida é em dólares ($), não em unidades:

Vendas de ponto de equilíbrio = custos fixos totais / (margem de contribuição)

Margem de contribuição = 1 - (custos / receitas variáveis)

Observe que isso pode ser por unidade ou total, pois é expresso como uma porcentagem.

Unidades de equilíbrio

Ao analisar o total de vendas, a medida é em unidades, não em dólares. Dependendo dos dados que você possui, pode ser necessário converter os valores totais em dólares em valores por unidade:

Unidades de equilíbrio = custos fixos totais / (preço por unidade - custo variável por unidade)

Preço de Equilíbrio

Aqui estamos resolvendo o preço, considerando um custo fixo e variável conhecido, bem como um número estimado de unidades vendidas. Observe que nas duas primeiras fórmulas sabemos o preço de venda e, essencialmente, derivamos a quantidade vendida ao ponto de equilíbrio. Mas, neste caso, precisamos estimar o número de unidades vendidas (ou a quantidade total vendida) e relacionar isso em função do preço de venda que resolvemos.

Porcentagem de custos variáveis ​​por unidade = custos variáveis ​​totais / (variável total + custos fixos totais)

Custos fixos totais por unidade = Custos fixos totais / Número total de unidades

Preço de equilíbrio = 1 / ((1 - Total de custos variáveis ​​por unidade) * (Total de custos fixos por unidade))

Basicamente, todas essas fórmulas podem ser consideradas como uma forma de análise do período de retorno do investimento, exceto que o "tempo em anos" é efetivamente quanto tempo leva para gerar o número necessário de vendas nos cálculos acima.

Análise de ponto de equilíbrio no Excel

Agora que sabemos em que consiste a análise de ponto de equilíbrio, podemos começar a modelá-la no Excel. Existem várias maneiras de conseguir isso. Os dois mais úteis são a criação de uma calculadora de equilíbrio ou o uso do Goal Seek, que é uma ferramenta interna do Excel.

Demonstramos a calculadora, porque ela se adapta melhor às práticas recomendadas de modelagem financeira, afirmando que as fórmulas devem ser divididas e auditáveis.

Ao criar uma análise de cenário, podemos dizer ao Excel para calcular com base na unidade. ( Nota: se a tabela parecer pequena, clique com o botão direito do mouse na imagem e abra em uma nova guia para obter uma resolução mais alta.)

Ou com base no preço:

Finalmente, podemos construir facilmente uma matriz de sensibilidade para explorar como esses fatores interagem. Dadas várias estruturas de custos, podemos ver uma gama de preços de ponto de equilíbrio entre US $ 28 e US $ 133.