# How can I Calculate Break-Even Analysis in Excel?

Break-even analysis is the study of what amount of sales, or units sold, is required to break even after incorporating all fixed and variable costs of running the operations of the business. Break-even analysis is critical in business planning and corporate finance because assumptions about costs and potential sales determine if a company (or project) is on track to profitability.

### Key Takeaways

• Break-even analysis is the study of the amount of sales or units sold required to break even considering all fixed and variable costs.
• Break-even analysis helps companies determine how many units need to be sold to cover all of their costs and begin earning a profit.
• Companies use break-even analysis to determine the price they need to charge to cover both their variable and fixed costs.

## Understanding Break-Even Analysis

Companies use break-even analysis to determine what price they must charge to generate enough revenue to cover their costs. As a result, break-even analysis often involves analyzing revenue and sales. However, it's important to differentiate sales, revenue, and profit. Revenue is the total amount of money earned from sales of a product while profit is the revenue that's remaining after all expenses and costs of running the business are subtracted from revenue.

### Types of Costs

The two costs involved in break-even analysis are fixed and variable costs. Variable costs change with the number of units sold while fixed costs remain somewhat constant regardless of the number of units sold. A variable cost would include inventory or raw materials involved in production. A fixed cost would include the rent for the production plant. Break-even analysis helps companies determine how many units need to be sold before they can cover their variable costs but also the portion of their fixed costs that are involved in producing that unit.

### Pricing Strategies

With break-even analysis, company owners can compare different pricing strategies and calculate how many units sold will lead to profitability. For example, if they cut the price of their product during a marketing campaign to generate new sales, they'll need to sell more units to help make up for the lower amount of revenue earned, due to the lower price per unit. If they cut the price substantially, they'll need a large jump in demand for their product to pay for their fixed costs, which are needed to keep the business operating.

If they cut the price by too much and the sales forecasts for an increase in demand are inaccurate, they may cover their variable costs but not cover their fixed costs. If they don't cut their price at all or the price per unit isn't competitive with the market, they may see less demand for their product and not be able to cover their total fixed costs. Break-even analysis helps determine at what point profit kicks in by considering all costs and revenue from sales.

### Contribution Margin

A key component of performing break-even analysis is to understand how much margin or profit is being earned from sales after subtracting the variable costs to produce the units. The selling price minus the variable costs is called the contribution margin.

For example, if a product sells for $200 each, and the total variable costs are$80 per unit, the contribution margin is $120 ($200 - $80). The$120 is the income earned after deducting variable costs and needs to be enough to cover the company's fixed costs.

## Formula for Break-Even Analysis

The break-even point occurs when:

Total Fixed Costs + Total Variable Costs = Revenue

• Total Fixed Costs are usually known; they include things like rent, salaries, utilities, interest expense, depreciation, and amortization
• Total Variable Costs are tougher to know, but they are estimable and include things like direct material, billable labor, commissions, and fees.
• Revenue is Unit Price * Number of units sold

With this information, we can solve any piece of the puzzle algebraically. It's important to note that each part of the equation–total fixed costs, total variable costs, and total revenue–can be expressed as a "Total," or as a per-unit measurement, depending on what specific break-even measure we require. This is explored more thoroughly in our Excel example.

## Special Considerations

The metric that includes taxes is called Net Operating Profit After Tax (NOPAT). By using NOPAT, you incorporate the cost of all actual operations, including the effect of taxes. However, the widely understood definition uses revenue, so that is what we're using in this article.

When calculating break-even quantities, it is important to account for taxes, which are a real expense that a company incurs. Taxes do not vary directly with the revenues; instead they are usually calculated on taxable profits. This makes it hard to factor in taxes using our simple formula above.

A solution to this would be to use Net Operating Profit After Tax (NOPAT). By using NOPAT, you incorporate the cost of all actual operations, including the effect of taxes. For the rest of this section, we use the first formula to calculate the break-even point.

## Types of Break-Even Analysis

There are various ways to analyze the break-even point for a company, which can include the total amount of revenue needed, the number of units that need to be sold, and the price per unit needed to reach the break-even point.

### Break-Even Total Sales

Sometimes companies want to analyze the total revenue and sales needed to cover the total costs involved in running the company.