This dataset includes the Sales Quantity in units, the Unit Selling Price, the components of Fixed Cost, and the components of Variable Cost.
Step 1 – Calculate Different Cost Components
Steps:
- Select C7 and enter the following.
- Double-click the SUM function to select it or press TAB.
- Select C8:C12 (the fixed cost).
- Use this formula in C7:
- Press ENTER.
- Use this formula in C8 to find the Total Variable Cos.
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.
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.
- Press ENTER.
- Go to C11 and enter the following formula.
- Press ENTER.
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.
- Enter units in ascending order in the Unit column.
- Go to C5 and enter the following formula.
- Press ENTER.
- Find the Fill Handle at the right-bottom corner of C5: a plus (+) sign.
- Double-click it.
The other cells are automatically filled.
- Select D5 and use the formula below.
The number of Units was multiplied by the Average Variable Cost. An absolute cell reference was used.
- Add the variable cost to the fixed cost to get the Total Cost in E5.
- 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.
- Press ENTER.
- Go to G5 and use the following formula.
- Press ENTER.
Step 4 – Insert Break-Even Chart
Steps:
- Select the table without the Profit column.
- In Insert, click Insert Scatter (X, Y) or Bubble Chart.
- Choose Scatter with Smooth Lines and Markers.
The chart will be displayed.
- Right-click the marker on the line of any series.
In the context menu:.
- Click Format Data Series… .
In Format Data Series :
- Click Fill & Line.
- Select Marker.
- Expand the Marker options and select None.
- Expand the Fill section and choose No fill.
Follow the same procedure for the other series.
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.
This is the output.
Step 5 – Determine the Break-Even Point in the Chart
Steps:
- Right-click inside the plot area.
- Choose Select Data… .
- Click Add in the 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.
- 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.
This is the output.
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….
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.
This is the output.
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.
- Use this formula to determine the Profit in C10.
The break-even point is the condition of a company or business before it starts to gain profit.
- Go to the Data tab.
- Click What-If-Analysis in Forecast.
- Select Goal Seek.
- 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.
Practice Section
Practice here.
Download Practice Workbook
Download the following Excel workbook.
Related Articles
- How to Calculate Break-Even Points in Excel
- How to Do Multi-Product Break-Even Analysis in Excel
- How to Do Break-Even Analysis with Goal Seek in Excel
<< Go Back To Break Even Analysis Excel | Excel For Finance | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!