Excel has many functions. Other than the normal tabulations, Excel can help you in various business calculations, among other things being BreakEven analysis.
Table of Contents
 How to calculate Breakeven analysis in Excel
 What is breakeven analysis
 BreakEven Analysis Formula
 Contribution Margin
 BreakEven Point Formula in Excel
 Calculate breakeven analysis with GoalSeek
 Calculate BreakEven analysis in Excel with formula
 Calculate BreakEven analysis with chart
How to calculate Breakeven analysis in Excel
Breakeven analysis is the study of what amount of sales or units sold, a business requires to meet all its expenses without considering the profits or losses. It occurs after incorporating all fixed and variable costs of running the operations of the business.
In this post, we focus on how to use Excel to calculate BreakEven analysis.
What is breakeven analysis
The breakeven point of a business is where the volume of production and volume of sales of goods (or services) sales are equal. At this point, the business can cover all its costs. In the economic sense, the breakeven point is the point of an indicator of a critical situation when profits and losses are zero. Usually, this indicator is expressed in quantitative or monetary units.
The lower the breakeven point, the higher the financial stability and solvency of the firm.
Breakeven 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.
Breakeven analysis helps organizations/companies determine how many units they need to sell before they can cover their variable costs and the portion of their fixed costs involved in producing that unit.
BreakEven Analysis Formula
To find the breakeven, you need to know:
 Fixed costs
 Variable costs
 Selling price per unit
 Revenue
The breakeven point occurs when:
Total Fixed Costs (TFC) + Total Variable Costs (TVC) = Revenue
 Total Fixed Costs are known items such as rent, salaries, utilities, interest expense, amortization, and depreciation.
 Total Variable Costs include things like direct material, commissions, billable labour, and fees, etc.
 Revenue is Unit Price * Number of units sold.
Contribution Margin
A key component of calculating the breakeven analysis is understanding how much margin or profit is generated from sales after subtracting the variable costs to produce the units. This is called a contribution margin. Thus:
Contribution Marin = Selling Price  Variable Costs
BreakEven Point Formula in Excel
You can calculate the breakeven point with regard to two things:
 Monetary equivalent: (revenue*fixed costs) / (revenue  variable costs).
 Natural units: fixed cost / (price  average variable costs).
Bearing this in mind, there are a number of ways to calculate the breakeven point in Excel:
 Calculate breakeven analysis with GoalSeek Feature (a builtin Excel tool)
 Calculate breakeven analysis with a formula
 Calculate breakeven analysis with a chart
Calculate breakeven analysis with GoalSeek
Case: Supposing you want to sale a new product. You already know the per unit variable cost and the total fixed cost. You want to forecast the possible sales volumes, and use this to price the product. Here is what you need to do.

Make an easy table, and fill in items/data.

In Excel, enter proper formulas to calculate the revenue, the variable cost, and profit.
 Revenue = Unit Price x Unit Sold
 Variable Costs = Cost per Unit x Unit Sold
 Profit = Revenue – Variable Cost – Fixed Cost

Use these formulas for your calculation.

On your Excel document, Click the Data > WhatIf Analysis > select Goal Seek.

When you open the Goal Seek dialogue box, do as follows:
 Specify the Set Cell as the Profit cell; in this case, it is Cell B7;
 Specify the To value as 0;
 Specify the By changing cell as the Unit Price cell, in this case; it is Cell B1.
 Click the OK button

The Goal Seek Status dialogue box will pop up. Please click the OK to apply it.
Goal Seek will change the Unit Price from 40 to 31.579, and the net profit changes to 0. Remember, at breakeven point profit is 0. Therefore, if you forecast the sales volume at 50, the Unit price cannot be less than 31.579. Otherwise, you will incur a loss.
Calculate BreakEven analysis in Excel with formula
You can also calculate the breakeven on point on Excel using the formula. Here’s how:

Make an easy table, and fill items/data. In this scenario, we assume we know the units sold, cost per unit, fixed cost, and profit.
 Use the formula for calculating the missing items/data.
 Type the formula = B6/B2+B4 into Cell B1 to calculating the Unit Price,
 Type the formula = B1*B2 into Cell B3 to calculate the revenue,
 Type the formula = B2*B4 into Cell B5 to calculate variable costs.
Note: if you change any value, for instance, the value of the forecasted unit sold or cost per unit or fixed costs, the value of the unit price will change automatically.
Calculate BreakEven analysis with chart
If you have already recorded your sales data, you can calculate the breakeven point with a chart in Excel. Here’s how:

Prepare a sales table. In this case, we assume we already know the sold units, cost per unit, and fixed costs, and we assume they’re fixed. We need to make the breakeven analysis by unit price.

Finish the calculations of the table using the formula
 In the Cell E2, type the formula =D2*$B$1 then drag its AutoFill Handle down to Range E2: E13;
 In the Cell F2, type the formula =D2*$B$1+$B$3, then drag its AutoFill Handle down to Range F2: F13;
 In the Cell G2, type the formula =E2F2, then drag its AutoFill Handle down to the Range G2: G13.

This calculation should give you the source data of the breakeven chart.

On the Excel table, select the Revenue column, Costs column, and Profit column simultaneously, and then click Insert > Insert Line or Area Chart > Line. This will create a line chart.

Next, rightclick the chart. From the context menu, click Select Data.
 In the Select Data Source dialogue box, do the following:
 In the Legend Entries (Series) section, select one of the series as you need. In this case, we select the Revenue series;
 Click the Edit button in the Horizontal (Category) Axis Labels section;
 A dialogue box will pop out with the name Axis Labels. In the box specify the Unit Price column (except the column name) as axis label range;

Click OK > OK to save the changes.

A chat will be created, called the breakeven chart. You will notice the breakeven point, which occurs when the price equals to 36.

Similarly, you can create a breakeven chart to analyze the breakeven point by sold units:
You’re done. Its that simple.
Wrapping up
You can tweak the appearance of your data through the data section and design tools. Excel allows you to do many other things with the data.
If you’re looking for more guides or want to read more techrelated articles, consider subscribing to our newsletter. We regularly publish tutorials, news articles, and guides to help you.
Recommended Reads