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

Imagine que está prestes a lançar um novo produto e já conhece o custo variável por unidade e o custo fixo total. Agora, quer prever os volumes de vendas possíveis e definir o preço do produto com base nesses valores.

1. Crie uma tabela simples e preencha-a com os dados indicados. Veja a captura de ecrã abaixo:

2. Introduza as fórmulas adequadas para calcular a receita, os custos variáveis e o lucro. Consulte a captura de ecrã acima:
Receita = Preço Unitário × Unidades Vendidas
Custos Variáveis = Custo por Unidade × 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, proceda da seguinte forma (veja a captura de ecrã acima):
(1) Defina a Célula de Definição como a célula do Lucro; no nosso caso, é a célula B7;
(2) Defina o Valor pretendido como 0;
(3) Defina a Célula a alterar como a célula do Preço Unitário; no nosso caso, é a célula B1.
(4) Clique no botão OK.

5. Em seguida, aparece a caixa de diálogo Estado da Atingir Meta. Clique no botão OK para aplicar as alterações.

Agora, o preço unitário passa de 40 para 31,579, e o lucro líquido torna-se zero. Assim, se o volume de vendas previsto for de 50 unidades, o preço unitário não poderá ser inferior a 31,579, sob pena de gerar prejuízo.

Descarregue um 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

Em comparação com a funcionalidade Atingir Meta, também podemos aplicar uma fórmula para realizar de forma simples a análise do ponto de equilíbrio no Excel.

1. Crie uma tabela simples e preencha os itens com os dados fornecidos. Neste método, partimos do pressuposto de que o lucro é zero e já previmos as unidades vendidas, o custo por unidade e os custos fixos. Veja a captura de ecrã abaixo:

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

Depois, sempre que alterar qualquer um dos valores previstos — unidades vendidas, custo por unidade ou custos fixos —, o preço unitário será atualizado automaticamente. Veja a captura de ecrã acima:

Dica : Guarde um intervalo como entrada de TextoAutomático (mantendo os formatos de célula e fórmulas) para reutilização futura
Referenciar células e aplicar múltiplas fórmulas para analisar o ponto de equilíbrio repetidamente pode ser extremamente cansativo. O Kutools para Excel oferece uma solução inteligente com a utilidade TextoAutomático, que lhe permite guardar o intervalo como uma entrada de TextoAutomático, preservando os formatos de célula e as fórmulas nele contidas. Depois, poderá reutilizar esse intervalo com apenas um clique em qualquer livro!

Obtenha agora uma avaliação gratuita de 30 dias com todas as funcionalidades!

Se já registou os dados de vendas, também pode realizar a análise do ponto de equilíbrio com um gráfico no Excel. Este método orientá-lo-á na criação fácil de um gráfico de ponto de equilíbrio.

1. Prepare uma tabela de vendas conforme mostrado na captura de ecrã abaixo. No nosso caso, consideramos que as unidades vendidas, o custo por unidade e os custos fixos são constantes, e precisamos de realizar a análise do ponto de equilíbrio com base no preço unitário.

2. Preencha a tabela conforme indicado abaixo:
(1) Na célula E2, introduza a fórmula =D2*$B$1 e arraste a alça de autorreenchimento até ao intervalo E2:E13;
(2) Na célula F2, introduza a fórmula =D2*$B$1+$B$3 e arraste a alça de autorreenchimento até ao intervalo F2:F13;
(3) Na célula G2, introduza a fórmula =E2-F2 e arraste a alça de autorreenchimento até ao intervalo G2:G13.

Com isto, concluímos os Dados de Origem do gráfico de ponto de equilíbrio que criaremos de seguida. Veja a captura de ecrã abaixo:

3. Na tabela, selecione simultaneamente as colunas Receita, Custos e Lucro e, em seguida, clique em Inserir > Inserir Gráfico de Linhas ou de Áreas > Linhas. Veja a captura de ecrã:

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

5. Na caixa de diálogo Selecionar Origem dos Dados, proceda da seguinte forma:
(1) Na secção Intervalo de nomes da série, selecione uma das séries conforme necessário. No meu exemplo, seleciono a série Receita;
(2) Clique no botão Editar na secção Rótulos do Eixo Horizontal (Categoria);
(3) Na caixa de diálogo Intervalo de Rótulos do Eixo que surge, especifique a coluna de Preço Unitário (exceto o nome da coluna) como Intervalo de Rótulos do Eixo;
(4) Clique em OK OK para guardar as alterações.

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

De forma semelhante, também pode criar um gráfico de ponto de equilíbrio para analisar o ponto de equilíbrio com base nas unidades vendidas, conforme mostrado na captura de ecrã abaixo:

Exporte/guarde facilmente a seleção como PDF/CSV/TXT/Arquivo HTML separado no Excel

Por exemplo, depois de concluir a sua análise do ponto de equilíbrio no Excel, poderá querer incluí-la numa apresentação. Nesta situação, pode utilizar a utilidade Exportar Intervalo para Arquivo do Kutools para Excel para exportar rapidamente a seleção como um ficheiro separado nos formatos PDF, HTML, CSV, ficheiro de texto, entre outros.