How to create a break-even chart in Excel

Excel 2016
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 the 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, and 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, B10:E20).

   3.   On the Insert tab, in the Charts group, click on the Insert Line or Area Chart dropdown list:

The Insert Line or Area Chart button in Excel 2016

From the Insert Line or Area Chart dropdown list, select Line:

The Line Chart in Excel 2016

Excel creates the new chart for the data with automatic x-axis labels:

The new line chart in Excel 2016

   4.   Modify the horizontal axis:

   4.1.   Do one of the following:

  • 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

   4.2.   In the Select Data Source dialog box, under Horizontal (Category) Axis Labels, click the Edit button:

Select Data Source dialog box in Excel 2016

   4.3.   In the Axis Labels dialog box, choose the range for the horizontal axis (in this example, Units):

Axis Labels dialog box in Excel 2016

   4.4.   Click OK twice.

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.   Open the Select Data Source dialog box (see 4.1.).

   6.2.   In the Select Data Source dialog box, under Legend Entries (Series), 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.   Change the chart type:

   7.1.   Right-click on the chart and select Change Chart Type... in the popup menu:

Change Chart Type in popup menu Excel 2016

   7.2.   In the Change Chart Type dialog box:

  • Select the Combo tab,
  • Select the Custom Combination chart type,
  • Ensure that for the all data series except the last one, the Line chart type is selected,
  • Choose the Scatter type for the newly added data series:
The Change Chart Type dialog box in Excel 2016

   8.   Modify the data for this data series:

   8.1.   Open the Select Data Source dialog box (see 4.1.).

   8.2.   In the Select Data Source dialog box, under Legend Entries (Series), select the newly added data series and click the Edit button.

   8.3.   In the Edit Series dialog box, modify the values for the Series X values and Series Y values fields:

The Edit Series dialog box in Excel 2016

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.   On the Design tab, in the Chart Layouts group, click the Add Chart Element button:

Add Chart Element button in Excel 2016

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

If you have any questions or suggestions, please feel free to ask OfficeToolTips team.