Como fazer uma análise de ponto de equilíbrio no Excel?

Suponha que você vá vender um novo produto e conheça o custo variável por unidade e o custo fixo total. Agora, você vai prever os possíveis volumes de vendas e precificar o produto com base neles.

1. Crie uma tabela simples e preencha os itens com os dados fornecidos na tabela. Veja a captura de tela abaixo:

2. Insira as fórmulas adequadas para calcular a receita, o custo variável e o lucro. Veja a captura de tela acima:
Receita = Preço Unitário x Unidades Vendidas
Custos Variáveis = Custo por Unidade x Unidades Vendidas
Lucro = Receita – Custos Variáveis – Custos Fixos

3. Clique em Dados > Análise de Hipóteses > Atingir Meta.

4. Na caixa de diálogo Atingir Meta que se abre, faça o seguinte (veja a captura de tela acima):
(1) Especifique a Definir célula como a célula de Lucro; em nosso caso, é a Célula B7;
(2) Especifique o Valor para como 0;
(3) Especifique a Alterando célula como a célula de Preço Unitário; em nosso caso, é a Célula B1.
(4) Clique no botão OK botão.

5. Em seguida, aparecerá a caixa de diálogo Status do Atingir Meta. Clique no botão OK para aplicar.

Agora ele altera o Preço Unitário de 40 para 31,579, e o lucro líquido muda para 0. Portanto, se você prevê que o volume de vendas será de 50 unidades, o preço unitário não pode ser inferior a 31,579, caso contrário, ocorrerá prejuízo.

Baixar Modelo Complexo de Ponto de Equilíbrio

http://office.microsoft.com/en-us/templates/breakeven-analysis-TC001116512.aspx
http://office.microsoft.com/en-us/templates/break-even-analysis-TC001017515.aspx

Comparado ao recurso Atingir Meta, também podemos aplicar fórmulas para realizar facilmente a análise de ponto de equilíbrio no Excel.

1. Crie uma tabela simples e preencha os itens com os dados fornecidos na tabela. Neste método, supomos que o lucro seja 0, e já prevemos as unidades vendidas, o custo por unidade e os custos fixos. Veja a captura de tela abaixo:

2. Na tabela, digite a fórmula =B6/B2+B4 na Célula B1 para calcular o Preço Unitário, digite a fórmula =B1*B2 na Célula B3 para calcular a receita, e digite a fórmula =B2*B4 na Célula B5 para os custos variáveis. Veja a captura de tela abaixo:

E então, quando você alterar qualquer valor das unidades previstas vendidas, custo por unidade ou custos fixos, o valor do preço unitário mudará automaticamente. Veja a captura de tela acima:

Dica: Salve um intervalo como entrada de AutoTexto (mantendo formatos de células e fórmulas) para reutilização futura
Deve ser muito tedioso referenciar células e aplicar várias fórmulas para a análise de ponto de equilíbrio toda vez. O Kutools for Excel oferece uma solução prática com o utilitário AutoTexto que salva o intervalo como uma entrada de AutoTexto, mantendo os formatos de células e fórmulas no intervalo. Depois disso, você pode reutilizar esse intervalo com apenas um clique em qualquer pasta de trabalho.

Obtenha agora um teste gratuito completo de 30 dias!

Se você já registrou os dados de vendas, também pode criar a análise de ponto de equilíbrio com gráficos no Excel. Este método irá guiá-lo para criar um gráfico de ponto de equilíbrio facilmente.

1. Prepare uma tabela de vendas conforme mostrado na captura de tela abaixo. Em nosso caso, assumimos que as unidades vendidas, o custo por unidade e os custos fixos são fixos, e precisamos fazer a análise de ponto de equilíbrio pelo preço unitário.

2. Finalize a tabela conforme mostrado abaixo:
(1) Na Célula E2, digite a fórmula =D2*$B$1, e arraste sua alça de preenchimento automático até o Intervalo E2:E13;
(2) Na Célula F2, digite a fórmula =D2*$B$1+$B$3, e arraste sua alça de preenchimento automático até o Intervalo F2:F13;
(3) Na Célula G2, digite a fórmula =E2-F2, e arraste sua alça de preenchimento automático até o Intervalo G2:G13.

Até agora, finalizamos os dados de origem do gráfico de ponto de equilíbrio que criaremos mais tarde. Veja a captura de tela abaixo:

3. Na tabela, selecione simultaneamente a coluna Receita, a coluna Custos e a coluna Lucro, e depois clique em Inserir > Inserir Gráfico de Linha ou Área > Linha. Veja a captura de tela:

4. Agora, um gráfico de linhas foi criado. Clique com o botão direito no gráfico e selecione Selecionar Dados no menu de contexto. Veja a captura de tela abaixo:

5. Na caixa de diálogo Selecionar Fonte de Dados, faça o seguinte:
(1) Na seção Entradas de Legenda (Série) selecione uma das séries conforme necessário. No meu exemplo, selecionei a série Receita série;
(2) Clique no botão Editar na seção Rótulos do Eixo Horizontal (Categoria) ;
(3) Na caixa de diálogo Rótulos do Eixo que aparece, especifique a coluna Preço Unitário (exceto o nome da coluna) como intervalo de rótulo do eixo;
(4) Clique em OK > OK para salvar as alterações.

Agora, no gráfico de ponto de equilíbrio, você verá que o ponto de equilíbrio ocorre quando o preço é igual a 36, conforme mostrado na captura de tela abaixo:

Da mesma forma, você também pode criar um gráfico de ponto de equilíbrio para analisar o ponto de equilíbrio pelas unidades vendidas, conforme mostrado na captura de tela abaixo:

Exporte/salve facilmente a seleção como arquivos PDF/CSV/TEXT/HTML separados no Excel

Por exemplo, ao finalizar sua análise de ponto de equilíbrio no Excel, você pode querer apresentar essa análise. Nesse caso, você pode usar o utilitário Exportar Intervalo para Arquivo do Kutools for Excel para exportar rapidamente a seleção como um arquivo PDF separado, arquivo HTML, arquivo CSV, arquivo de texto, etc.