How to calculate break-even analysis in Excel?

Break-even analysis is the study of the amount of sales or units sold required to break even after all fixed and variable costs of running the business have been taken into account. Break-even analysis is essential in business planning and finance because assumptions about costs and potential revenue determine whether a company (or project) is on the path to profitability.

Important points to remember

  • Break-even analysis is the study of the amount of sales or units sold required to break even, taking into account all fixed and variable costs.
  • Break-even analysis helps companies determine how many units need to be sold to cover all costs and make a profit.
  • Businesses use break-even analysis to determine the price they should charge to cover their variable and fixed costs.

Understand break-even analysis

Businesses use break-even analysis to determine the price they need to charge in order to generate enough revenue to cover their costs. Therefore, break-even analysis often includes revenue and sales analysis. However, it is important to distinguish between sales, revenue and profit. Revenue is the total amount earned by selling a product, while profit is the income remaining after all expenses and costs of running the business have been subtracted from the number.

types of costs

The two costs involved in the break-even analysis are fixed costs and variable costs. Variable costs vary with the number of units sold, while fixed costs remain relatively constant regardless of the number of units sold. Variable costs include inventory or raw materials needed for production. The fixed costs would include renting the production facility. Break-even analysis helps companies determine how many units need to be sold before they can cover their variable costs, but also how much of their fixed costs are related to producing that unit.

pricing policy

With break-even analysis, business owners can compare different pricing strategies and calculate how many units sold will result in profitability. For example, if they lower the price of their product during a marketing campaign to generate new sales, the lower price per unit means they need to sell more units to make up for the lower sales. If they lower the price drastically, they will be faced with a sharp increase in demand for their product to pay for their fixed costs, which are necessary to keep the business going.

  How to add or subtract hours in Microsoft Excel

If they lower the price too much and forecast revenue is inaccurate as demand increases, they can cover their variable costs but not their fixed costs. If they don’t reduce their price at all, or if the unit price is not competitive with the market, they risk falling demand for their product and not being able to cover all fixed costs. Break-even analysis helps determine when profit will be made by considering all costs and sales proceeds.

contribution margin

An important part of break-even analysis is understanding what the margin, or profit, is from the sale after the variable cost of production has been subtracted from the units. The selling price minus the variable cost is called the variable cost margin.

For example, if a product sells for $200 per unit and the total variable cost is $80 per unit, the contribution margin is $120 ($200 to $80). The $120 is the revenue earned after variable costs have been deducted and should be enough to cover the company’s fixed costs.

Formula for break-even analysis

The equilibrium point occurs when:

Total Fixed Costs + Total Variable Costs = Revenue

  • Total fixed costs are well known; They include items such as rent, salaries, ancillary costs, interest expenses and depreciation.
  • Total variable costs are more difficult to ascertain, but are estimable and include such things as direct materials, billable labor, commissions and fees.
  • receipts is the unit price * number of units sold

With this information, we can solve each piece of the puzzle algebraically. It’s important to note that each part of the equation – sum of fixed costs, sum of variable costs, and total revenue – can be expressed as “total” or as a unit measure, depending on the specific measure of the break-even point that we need. This point is examined in more detail in our Excel example.

  What is the formula to calculate internal rate of return (IRR) in Excel?

Special considerations

In the break-even analysis formula, there is disagreement on whether to use the standard definition of revenue as it does not include taxes. A company may determine that it must sell “X” quantities of a product to cover its costs, but taxes are a very real expense. In business planning, it is also important to calculate the after-tax operating result.

The metric that includes taxes is called Net Operating Profit After Tax (NOPAT). By using NOPAT, you include the cost of all actual operations, including the impact of taxes. However, the commonly understood definition uses recipes, so we use those in this article.

Types of Break-Even Analysis

There are several ways to analyze a company’s break-even point, which can include the total sales required, the number of units to sell, and the price per unit required to break even.

Total sales break-even point

Sometimes companies want to analyze the total sales and revenue required to cover all costs of running the business.

The following formula calculates total sales, but the measurement is in dollars ($) rather than units:

  • Break-Even Sales = total fixed costs / (contribution margin)
  • Contribution Margin = 1 – (Variable Costs / Revenue)

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

Break-Even Unit Sales

Determining how many units need to be sold to breakeven is one of the most common methods of breakeven analysis.

Depending on the data you have, you may need to convert total dollar values ​​to unit values:

  • Units of Profitability = Total Fixed Cost / (Price per Unit – Variable Cost per Unit)

To calculate the break-even analysis, we divide the sum of the fixed costs by the contribution margin per unit sold. Continuing with the previous example, let’s say the total fixed cost is $10,000.

We already know that the product sells for $200 per unit and the total variable cost is $80 per unit, giving a contribution margin of $120 ($200 to $80).

  Text wrapping and multi-line formulas in Excel

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

The sales breakeven is 83.33, or 84 units, which must be sold before the company covers its fixed costs. From this point, i.e. 85+ units, the company has paid its fixed costs and achieved a profit per unit.

break-even price

Here we resolve known fixed and variable costs and an estimated number of units sold for the given price. Note that in the first two formulas we know the selling price and essentially get the 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 it to the estimated selling price.

  • Percentage of variable costs per unit = sum of variable costs / (sum of variable costs + sum of fixed costs)
  • Total fixed cost per unit = total fixed cost / total number of units
  • break-even price = 1 / ((1 – Total variable cost percentage per unit)*(Total fixed cost per unit))

In essence, all of these formulas can be viewed as a form of payback analysis, except that the “time in years” is actually the time it takes to generate the number of sales required in the calculations above.

Break-Even Analysis in Excel

Now that we know what a break-even analysis is, we can start modeling it in Excel. There are several ways to achieve this. The two most useful are creating a break-even calculator or using Goal Seek, a built-in Excel tool.

We’re demonstrating the calculator because it’s more in line with financial modeling best practices, where formulas should be itemized and verifiable.

By creating a scenario analysis, we can instruct Excel to calculate based on unity. (Note: if the table looks small, right click on the image and open a new tab for a higher resolution).

Calculate Breakeven Analysis in Excel.

Or depending on the price:

Calculate Breakeven Analysis in Excel.

Finally, we can easily create a sensitivity matrix to study how these factors interact. Taking into account the different cost structures, we can observe a break-even price range of $28 to $133.

Sensitivity Analysis in Excel.