How to create a break-even chart in Excel

Excel 365
A revenue the company generates from selling the products or providing the services should cover the fixed costs, variable costs, and leave a contribution margin. The point where the total operating margin (the difference between the price of the product or service and the variable costs per item or customer) covers the fixed costs is called a break-even point.

It is calculated by dividing the total fixed costs of the business by the price of the product or service less than variable costs per item or customer. Break-even analysis through break-even chart in Excel allows you to see the break-even point both in production units and in sales dollars and estimate the required growth rate of sales:

A break-even chart in Excel 2016

The break-even point (or breakeven point, BEP) is the volume of production and sales of products at which fixed costs will be offset by income. With the production and sale of each subsequent unit of production, the company begins to make a marginal profit:

The break-even formula in Excel 2016

where the Contribution margin for a product (or service) is the price minus the variable costs.

For example, the first company has the following estimates in its business plan:

The company indicators in Excel 2016

Using the above formulas, calculate the critical factors for the first business plan:

The critical factors for the business plan in Excel 2016

So, to cover all fixed costs, the first company managers should sell more than 6,667 units of the product or attract 6,667 customers to the service.

To create a graph for BEP in Excel, do the following:

  • Create a chart of revenue and fixed, variable, and total costs
  • Add the Break-even point
  • Add the Break-even point lines

Create a chart of revenue and fixed, variable, and total costs

   1.   Prepare the data for the chart:

For this example, create a new data table:

The data for the BEP chart in Excel 2016

where:

  • the Fixed costs values are constant and equal to C3 (= $C$3),
  • the Variable costs values = Average variable costs * Units (= $C$4 * A11),
  • the Total costs values = Fixed costs + Variable costs (= B11 + C11),
  • the Revenue values = Unit sale price * Units (= $C$5 * A11).

   2.   Select the costs and revenue data range (for this example, A10:E20).

   3.   On the Insert tab, in the Charts group, click on the Insert Scatter (X, Y) or Bubble Chart dropdown list:

The Insert Scatter (X, Y) or Bubble Chart button in Excel 2016

From the Insert Scatter (X, Y) or Bubble Chart dropdown list, select the Scatter chart you prefer, for example, Scatter with Straight Line.

Excel creates the new chart for the data:

The new line chart in Excel 2016

   4.   Make any other adjustments you desire:

The formatted line chart in Excel 2016

Add the Break-even point

   5.   Add the new data:

To add the Break-even point to the chart, it is necessary to have both BEP in sales volume and BEP in sales dollars values:

  • The Break-even point in sales volume was calculated above (= C8),
  • The Break-even point in sales dollars can be calculated using the following formula:
The break-even formula 2 in Excel 2016

For this example:

The additional data for the BEP chart in Excel 2016

   6.   Add the new data series to the chart:

   6.1.   Do one of the following:

  • Under Chart Tools, on the Design tab, in the Data group, choose Select Data:
    Select Data in Excel 2016
  • Right-click in the chart area and choose Select Data... in the popup menu:
    Select Data in popup menu Excel 2016

   6.2.   In the Select Data Source dialog box, click the Add button.

   6.3.   In the Edit Series dialog box, choose the appropriate values for the Series name and Series values fields:

Edit Series dialog box in Excel 2016

   7.   Customize the Break-even point:

   7.1.   Right-click on any data series and select Format Data Series... in the popup menu:

Format Data Series in popup menu Excel 2016

   7.2.   In the Format Data Series pane:

  • If you can't select the data series needed, click the arrow next to Series Options and select the appropriate data series from the drop-down list.
  • On the Fill & Line tab, under Marker, in the Marker Options section, select the Built-in option, the appropriate Type and Size:
    Built-in Marker Options in Excel 2016

    Note: You can select any Fill and Border type as you wish.

   8.   Make any other adjustments you want:

The combined chart in Excel 2016

Add the Break-even point lines

   9.   Select the data series for the Break-even point.

   10.   Under Chart Tools, on the Design tab, in the Chart Layouts group. From the Add Chart Element dropdown list, select Error Bars and then More Error Bars Options...:

More Error Bars Options in Excel 2016

Ensure that both Horizontal and Vertical Error Bars were added for the data series.

   11.   On the Format Error Bars pane for each error bar:

  • On the Error Bars Options tab, in the Vertical Error Bar (or the Horizontal Error Bar) section:
    • In the Direction group, select Minus,
    • In the End Style group, select No Cap option,
    • In the Error Amount group, select the Percentage option and then type 100%:
    Format Error Bars pane in Excel 2016
  • On the Fill & Line tab, select the Solid line option, choose the Color and Width, which you prefer:
The BEP chart in Excel 2016

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.