How can I calculate break-even analysis in Excel? (2024)

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 analysisis 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.

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 thecontribution 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 theeffect 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 theeffect 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.

The formula below helps calculate the total sales, but the measurement is indollars ($), not units:

• Break-even Sales = Total Fixed Costs / (Contribution Margin)
• Contribution Margin = 1 - (Variable Costs / Revenues)

Please note that this can be either per unit or total or expressed as a percentage.

Break-Even Units Sold

Determining the number of units that need to be sold to achieve the break-even point is one of the most common methods of break-even analysis.

Depending on the data you have, you may need to translate total dollar values into per-unit values:

• Break-Even Units = Total Fixed Costs / (Price per Unit - Variable Cost per Unit)

To calculate the break-even analysis, we divide the total fixed costs by the contribution margin for each unit sold. Using the earlier example, let's say that the total fixed costs are $10,000.

We already know that the product sells for $200 each, and the total variable costs are $80 per unit, resulting in a contribution margin of $120 ($200 - $80).

Using the break-even point formula above we plug in the numbers ($10,000 in fixed costs / $120 in contribution margin).

The break-even point for sales is 83.33 or 84 units, which need to be sold before the company covers their fixed costs. From that point on, or 85 units and beyond, the company will have paid for their fixed costs and record a profit per unit.

Break-Even Price

Here we are solving for the price given a known fixed and variable cost, as well asan estimated number of units sold. Notice in the first two formulas, we know the sales price and are essentially deriving quantity sold to break even. But in this case, we need to estimate both the number of units sold (or total quantity sold) and relate that as a function of the sales price we solve for.

• Variable Costs Percent per Unit= Total Variable Costs / (Total Variable + Total Fixed Costs)
• Total Fixed Costs Per Unit = Total Fixed Costs / Total Number of Units
• Break-Even Price = 1 / ((1 - Total Variable Costs Percent per Unit)*(Total Fixed Costs per Unit))

Break-Even Analysis in Excel

Now that we know what break-even analysis consists of, we can begin modeling it in Excel.There are a number of ways to accomplish this. The two most useful are by creating a break-even calculatoror byusing Goal Seek, which is a built-in Excel tool.

We demonstrate the calculator because it better conforms to financial modeling best practicesstatingthat formulas should be broken out and auditable.

By creating a scenario analysis, we can tell Excel to calculate based on unit. (Note: Ifthe table seems small, right-click the image and open in new tab for higher resolution.)

Or based on price:

Finally, we can easily build a sensitivity matrix to explore how these factors interact. Given various cost structures, we can see a range of break-even prices from $28 to $133.