A análise do ponto de equilíbrio é uma ferramenta para avaliar as oportunidades de lucro de um modelo de negócios e várias estratégias de preços. Você pode encontrar custos fixos, custos variáveis e opções de preços no Excel para determinar o ponto de equilíbrio de um produto. O ponto de equilíbrio é o número de unidades que precisam ser vendidas a um preço definido para o equilíbrio (cobrir os custos de produção).
Etapa
Parte 1 de 5: Criando uma Tabela de Custo Variável
Etapa 1. Abra o Excel e crie uma pasta de trabalho
Você precisará de algumas planilhas na pasta de trabalho para controlar todos os seus custos.
Etapa 2. Clique no botão "+" próximo a "Folha1" na parte inferior da tela
Isso abrirá um novo documento de trabalho.
Etapa 3. Nomeie a nova planilha como "Custo variável"
Esta planilha conterá todas as tabelas que monitoram todos os custos variáveis, como custos de envio, comissões e assim por diante.
Etapa 4. Crie um rótulo de título para a nova planilha
Para criar uma tabela de custo variável padrão, insira “Descrição” na célula A1 e “Valor” na célula B1.
Etapa 5. Insira o nome do seu custo variável de negócios na coluna A
No título “Descrição”, insira o tipo de custos variáveis do produto a serem tratados.
Etapa 6. Deixe a coluna B ("Quantidade") em branco por enquanto
Esta coluna será posteriormente preenchida com os custos que realmente ocorreram durante o processo.
Etapa 7. Crie uma tabela a partir dos dados inseridos
Transforme os dados em tabelas para que você possa inseri-los facilmente em fórmulas:
- Selecione todos os dados, incluindo a linha de título e células em branco, clicando e arrastando o cursor do mouse por todas as células.
- Clique no botão "Formatar como tabela". Você o encontrará sob o rótulo Home. Se você estiver usando o Excel para Mac, clique no rótulo Tabelas, clique no botão "Novo" e selecione "Inserir Tabela com Cabeçalhos".
- Marque a caixa "Minha mesa tem cabeçalhos". Portanto, o rótulo da primeira linha será definido como o rótulo do título.
- Clique na caixa "Nome da Tabela" no canto superior direito e altere o nome para "Custo Variável"
Parte 2 de 5: Criando uma Tabela de Custo Fixo
Etapa 1. Clique no botão "+" ao lado de "Custo variável" na parte inferior da tela
Isso abrirá um novo documento de trabalho.
Etapa 2. Renomeie a nova planilha para "Custos Fixos"
Esta planilha conterá todos os custos fixos do produto, como aluguel, seguro e outros custos que não mudam.
Etapa 3. Crie um rótulo de título
Tal como acontece com a planilha de custo variável, crie um rótulo "Descrição" na célula A1 e um rótulo "Valor" na célula B1.
Etapa 4. Insira os nomes dos custos fixos de sua empresa na coluna A
Preencha a primeira coluna com uma descrição dos custos fixos, por exemplo “Aluguel”.
Etapa 5. Deixe a coluna B (“Quantidade”) em branco por enquanto
Você preencherá esses custos após preencher toda a papelada.
Etapa 6. Crie uma tabela a partir dos dados inseridos
Selecione tudo o que você criou nesta planilha, incluindo o título:
- Clique no botão "Formatar como tabela" no rótulo inicial.
- Marque "Minha tabela tem cabeçalhos" para alterar a linha 1 para o título da tabela.
- Clique na caixa "Nome da tabela" e digite o nome da tabela "Custos fixos"
Parte 3 de 5: Criando uma planilha de equilíbrio
Etapa 1. Renomeie a Planilha1 para "BEP" e selecione-a
Esta planilha conterá seu gráfico BEP (Ponto de equilíbrio) principal. Você não precisa alterar o nome para “BEP”, mas tornará seu trabalho mais fácil se o fizer.
Etapa 2. Defina o layout da planilha de equilíbrio
Para fins deste exemplo, crie uma planilha usando o seguinte layout:
- A1: Vendas - este é o rótulo da seção Vendas da planilha.
- B2: Preço por unidade - Este é o preço cobrado para cada item vendido.
- B3: Unidades vendidas - Este é o número de unidades vendidas a um preço definido em um determinado intervalo de tempo.
- A4: Custos - Este é o rótulo da seção Custos da planilha.
- B5: Custos variáveis - são os custos do produto que você pode controlar (frete, taxa de comissão, etc.)
- B6: Custos fixos - Estes são os custos do produto sobre os quais você não tem controle (aluguel de instalações, seguro, etc.)
- A7: Receita - É a quantidade de dinheiro gerada com a venda do produto antes da dedução dos custos.
- B8: Margem da Unidade - Esta é a quantidade de dinheiro feita por unidade após a dedução dos custos.
- B9: Margem de lucro bruto - Esta é a quantia total de dinheiro gerada com a venda de unidades do produto após a dedução dos custos.
- A10: BEP - este é o rótulo para a seção do ponto de equilíbrio da planilha.
- B11: Unidades - Este é o número de unidades que precisam ser vendidas para cobrir os custos de produção.
Etapa 3. Altere o formato numérico das células de saída e entrada
Você precisa alterar o formato numérico de células específicas para que os dados apareçam corretamente:
- Destaque C2, C5, C6, C8 e C9. Clique no menu suspenso na seção "Número" do rótulo Home e selecione "Moeda".
- Destaque C3 e C11. Clique no menu suspenso e selecione "Mais formatos de número". Selecione "Número" e defina "Casas decimais" para "0".
Etapa 4. Crie um intervalo para usar nas fórmulas
Selecione e crie os seguintes intervalos para que a fórmula funcione. Dessa forma, as variáveis podem ser criadas para serem inseridas em fórmulas para que você possa rastrear e atualizar facilmente seus valores.
- Selecione B2: C3 e clique no rótulo "Fórmulas". Clique em “Criar a partir da seleção” e continue com “OK”.
- Selecione B5: C6 e clique no rótulo "Fórmulas". Clique em “Criar a partir da seleção” e continue com “OK”.
- Selecione B8: C9 e clique no rótulo "Fórmulas". Clique em “Criar a partir da seleção” e continue com “OK”.
- Selecione B11: C11 e clique no rótulo "Fórmulas". Clique em "Criar a partir da seleção" e continue com "OK".
Parte 4 de 5: Inserindo Fórmulas
Etapa 1. Insira a fórmula de custo variável
Esta fórmula calculará o custo variável total das mercadorias vendidas. Clique em C5 e insira a seguinte fórmula:
= SUM (Custo Variável) * Units_Sold
Etapa 2. Insira os custos fixos na fórmula
O Excel calculará o custo fixo total do seu produto. Clique em C6 e insira a seguinte fórmula:
= SUM (custo fixo)
Etapa 3. Insira a fórmula da margem da unidade
Esta fórmula irá calcular a margem resultante após incluir os custos variáveis. Clique em C8 e insira a seguinte fórmula:
= Price_Per_Unit-SUM (variável de custo)
Etapa 4. Insira a fórmula da margem de lucro bruto
Esta fórmula determina o valor total do lucro para todas as unidades após a dedução dos custos variáveis. Clique em C9 e insira a seguinte fórmula:
= Margin_Unit * Unit_Sold
Etapa 5. Insira a fórmula BEP
Essa fórmula pega os custos fixos e os compara à margem para que você saiba quantas unidades você precisa vender para atingir o ponto de equilíbrio. Clique em C11 e insira a seguinte fórmula:
= IFERROR (Custo_Custo / Unidade_Margem, 0)
Parte 5 de 5: Determinando o ponto de equilíbrio
Etapa 1. Insira os custos variáveis da sua empresa
Volte para a tabela Custos variáveis e preencha todos os custos associados ao produto. Quanto mais precisos os custos registrados, mais precisos serão os cálculos.
Cada custo na tabela de custo variável deve ser por unidade vendida
Etapa 2. Insira os custos fixos de sua empresa
Insira esses custos na tabela de custos fixos. Estes são os custos de funcionamento de uma empresa, todos definidos em intervalos iguais (por exemplo, taxas mensais).
Etapa 3. Insira o preço por unidade
Na planilha do BEP, insira o preço estimado por unidade. Você poderá ajustá-lo à medida que o cálculo avança.
Etapa 4. Insira o número de unidades que deseja vender
Este é o número desejado de vendas no mesmo período de Custos Fixos. Por exemplo, se seus custos fixos incluem aluguel mensal e seguro, Unidades vendidas é o número de unidades vendidas no mesmo período.
Etapa 5. Leia a saída da "Unidade"
A célula de saída Unidades (C11) exibirá o número de unidades que precisarão ser vendidas para atingir o ponto de equilíbrio. Este valor mudará dependendo do preço por unidade, bem como suas tabelas de custo variável e custo fixo.
Etapa 6. Faça ajustes nos preços e custos
Mudar o preço por unidade também mudará o número de unidades necessárias para atingir o ponto de equilíbrio. Experimente alterar o preço de venda da unidade e veja o impacto no valor do BEP.
