How to Make a Break-Even Chart in Excel – 6 Easy Steps

This dataset includes the Sales Quantity in units, the Unit Selling Price, the components of Fixed Cost, and the components of Variable Cost.

how to make a break even chart in excel

 

Step 1 – Calculate Different Cost Components

Steps:

  • Select C7 and enter the following.
=sum

Calculate Different Cost Components

  • Select  C8:C12 (the fixed cost).

using SUM function to calculate break even point

  • Use this formula in C7:
=SUM(Dataset!C8:C12)
  • Press ENTER.

Total Fixed Cost

  • Use this formula in C8 to find the Total Variable Cos.
=SUM(Dataset!C15:C18)

Total Variable Cost

You can find the Average Variable Cost (the mean of the variable cost for each unit of product) by dividing the total variable cost by the total production unit:

  • Use the formula.
=C8/C4

calculating average variable cost to make break even chart

Read More: How to Calculate Break Even Analysis with Formula in Excel


Step 2 – Compute the Break-Even Point of Sales

Steps:

  • Select C10 and enter the formula below.
=C7/(C5-C9)
  • Press ENTER.

Compute Break-Even Point of Sales

  • Go to C11 and enter the following formula.
=C7/((C5-C9)/C5)
  • Press ENTER.

Compute Break-Even Point of Sales to make chart

After selling 6071 units, the company will reach its break-even point. The sales amount will be $30,357.

Read More: How to Calculate Break-Even Sales with Formula in Excel


Step 3 – Create a Table of Costs, Revenue, and Profit

Steps:

  • Create the basic outline of the table in B4:G14.

Construct a Table of Costs, Revenue, and Profit

  • Enter units in ascending order in the Unit column.

  • Go to C5 and enter the following formula.
='Break Even'!$C$7
  • Press ENTER.

Entering Fixed Costs

  • Find the Fill Handle at the right-bottom corner of C5: a plus (+) sign.
  • Double-click it.

using fill handle tool to make break even chart in excel

The other cells are automatically filled.

  • Select D5 and use the formula below.
=B5*'Break Even'!$C$9

The number of Units was multiplied by the Average Variable Cost. An absolute cell reference was used.

Fetching Variable cost

  • Add the variable cost to the fixed cost to get the Total Cost in E5.
=C5+D5

Calculating Total Costs to make break even chart in Excel

  • To find the Revenue, multiply the Units in B5 by the Unit Selling Price in C5 in the Break Even worksheet.
  • Go to F5 and enter the formula below.
=B5*'Break Even'!$C$5
  • Press ENTER.

  • Go to G5 and use the following formula.
=F5-E5
  • Press ENTER.

Computing Profit

 

Step 4 – Insert Break-Even Chart

 Steps:

Insert Break-Even Chart

The chart will be displayed.

Break-Even Chart in Excel

  • Right-click the marker on the line of any series.

In the context menu:.

  • Click  Format Data Series… .

Marker of break even chart

In Format Data Series :

  • Click Fill & Line.
  • Select Marker.
  • Expand the Marker options and select None.
  • Expand the Fill section and choose No fill.

Format Data Series Task Pane

Follow the same procedure for the other series.

Minimal outlook of the break even chart

To distinguish the line of fixed and variable cost.

  • Right-click the line of fixed cost.
  • Select Format Data Series….

In Format Data Series:

  • Click Fill & Line.
  • In Line, set the Dash type as Long Dash Dot.

differentiate between two lines in the chart in excel

This is the output.

Inserting Break-Even Chart in Excel


Step 5 – Determine the Break-Even Point in the Chart

Steps:

  • Right-click inside the plot area.
  • Choose Select Data… .

Determine Break-Even Point in the Chart

Select Data Source dialog box

In Edit Series:

  • Enter BEP in Series name.
  • Go to the Break Even worksheet and give the references of C10 and C11 in the Series X values and Series Y values.
  • Click OK.

Working on Edit Series dialog box

  • Click OK.

  • Open Format Data Series for the new series.
  • Go to Marker options.
  • Select Built-in and choose the circle marker in Type.
  • Set the marker Size to 8.

Changing marker size

This is the output.

Determining Break-Even Point in the Chart


Step 6 –  Apply a Break-Even Line

Steps:

  • Click the plus-shaped Chart Element icon.
  • Click the arrowhead at the right of Error Bars.
  • Select More Options….

Introduce Break-Even Line

In the Format Error Bars task pane:

  • Select Error Bar Options.
  • Choose Minus in Direction and No Cap in End Style.
  • In Error Amount, select Percentage and set it to 100%.

  • Click  the drop-down arrow in Error Bar Options.
  • Select Series “BEP” Y Error Bars and follow the same procedure for the Vertical Error Bar.

Format Error Bar in Break Even Chart in Excel

This is the output.

 

Introducing Break-Even Line


Calculating the Selling Price at BEP (Break-Even Point) Using the Goal Seek Tool in Excel

TSteps:

  • To calculate the Revenue, enter the following formula in C9.
=C4*C5

Calculating Selling Price at Break Even Point Using Goal Seek Tool

  • Use this formula to determine the Profit in C10.
=C9-C8

The break-even point is the condition of a company or business before it starts to gain profit.

Using Goal seek form What if analysis

  • In the Goal Seek dialog box, enter the references as shown below, and click OK.

C10 is set as 0 by changing the value of C5.
The output is: 3.90 for Unit Selling Price.

Calculating Selling Price at BEP Using Goal Seek Tool in Excel


Practice Section

Practice here.


Download Practice Workbook

Download the following Excel workbook.


Related Articles


<< Go Back To Break Even Analysis ExcelExcel For Finance | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Shahriar Abrar Rafid
Shahriar Abrar Rafid

Shahriar Abrar Rafid, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, Bangladesh, has worked with the ExcelDemy project for more than 1 year. He has written over 100+ articles for ExcelDemy. He is a professional visual content developer adept at crafting scripts, meticulously editing Excel files, and delivering insightful video tutorials for YouTube channels. His work and learning interests vary from Microsoft Office Suites and Excel to Data Analysis, VBA, and Video recording and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo