Break-even analysis is very important for a business or a company. In the pre-production period, it is necessary to calculate the break-even point of the product or service. And, from a break-even chart, we can easily get those values and draw conclusions. In this article, we’ll demonstrate 6 easy and quick steps of how to make a break-even chart in Excel. So, let’s go through the article entirely to understand the topic properly.
How to Make a Break-Even Chart in Excel: 6 Steps
For ease of understanding, we are going to use a report on the Cost of Goods of ABC Company. This data set includes the Sales Quantity in units, the Unit Selling Price, the components of Fixed Cost, and the components of Variable Cost.
Now, we will make a break-even chart for this business using this dataset in Excel. So, let’s explore the method step by step.
Here, we have used the Microsoft Excel 365 version, you may use any other version according to your convenience.
Step 01: Calculate Different Cost Components
At the very beginning, we’ll sum up all the cost components. It’s simple and easy. Just follow along.
📌 Steps:
- First of all, select cell C7 and start writing the following.
=sumInstantly, Excel will show suggestions of functions related to this word.
- Then, double-click on the SUM function to select it. Also, you could choose it by tapping the TAB key.
- After that, go to the sheet Dataset and select cells in the C8:C12 range. Actually, those are the components of the fixed cost.
Finally, the formula for cell C7 looks like the following.
=SUM(Dataset!C8:C12)- Lastly, press ENTER.
Similarly, the formula in cell C8 to compute the Total Variable Cost is the following.
=SUM(Dataset!C15:C18)
Moreover, we can find out the Average Variable Cost which is the mean of the variable cost for each unit of product. Simply, we have to divide the total variable cost by the total production unit. The formula is also as simple as the following.
=C8/C4
We are done calculating the costs. Thus, let’s get to the next part.
Read More: How to Calculate Break Even Analysis with Formula in Excel
Step 02: Compute Break-Even Point of Sales
In this step, we will compute the break-even point for sales. Generally speaking, this break-even point consists of two elements. One is the Break-Even Sales Quantity and the other is the Break-Even Sales Amount. So, let’s begin.
📌 Steps:
- At first, select cell C10 and enter the formula below.
=C7/(C5-C9)- Then, press ENTER.
- After that, go to cell C11 and insert the following formula.
=C7/((C5-C9)/C5)- As usual, tap the ENTER button.
So, after selling 6071 units, the company will reach its break-even point. And then the sales amount will be $30,357.
Read More: How to Calculate Break-Even Sales with Formula in Excel
Step 03: Construct a Table of Costs, Revenue, and Profit
At this time, we’ll construct a table consisting of Units, different types of Costs, and their corresponding Revenue and Profit also. This table is crucial to plot the chart. So, without further delay let’s dive in.
📌 Steps:
- At first, create the basic outline of the table in the B4:G14 range.
- Following this, give some units in ascending order in the Unit column.
- Then, go to cell C5 and enter the following formula.
='Break Even'!$C$7- As always, hit ENTER.
- Now, bring the cursor to the right-bottom corner of cell C5 and it’ll look like a plus (+) sign. Actually, it’s the Fill Handle tool.
- After that, double-click on it.
Instantly, all the remaining cells get the output automatically.
At this time, select cell D5 and write down the formula below.
=B5*'Break Even'!$C$9In fact, we multiplied the number of Units by the Average Variable Cost. Here, we used an absolute cell reference, as the cell reference of cell C9 doesn’t get changed while using the Fill Handle tool.
Afterward, add the variable cost to the fixed cost to get the Total Cost in cell E5.
=C5+D5
To get the Revenue, multiply the Units in cell B5 by the Unit Selling Price in cell C5 in the Break Even worksheet.
- So, go to cell F5 and paste the formula below.
=B5*'Break Even'!$C$5- Afterward, press ENTER.
We all know how to get a profit. Just make the revenue greater than the cost. So, in Excel,
- Navigate to cell G5 and put down the following formula.
=F5-E5- Later, press ENTER.
That’s how we got cost and revenue for various numbers of units of the product.
Step 04: Insert Break-Even Chart
Now, come to the main topic, how to make a break-even chart in Excel. It’s the most important part of this article. So, don’t miss it a bit. Let’s follow along.
📌 Steps:
- Firstly, select the whole table without the Profit column only.
- Then, proceed to the Insert tab and click on the Insert Scatter (X, Y) or Bubble Chart drop-down icon.
- After that, choose Scatter with Smooth Lines and Markers type plot.
Immediately, you can see a chart in the sheet that looks like the following.
As a result, the context menu will appear before us.
- Here, just click on the Format Data Series… option.
In the Format Data Series task pane,
- At first, click on the Fill & Line icon.
- Then, select the Marker tab.
- After that, expand the Marker options and select None.
- Later, expand the Fill section and choose No fill from the available options.
Do the same for other series as well. Our chart looks like the following now: neat and clean.
Currently, we’ll distinguish between the line of fixed cost and variable cost.
- Firstly, right-click on the line of fixed cost.
- From the context menu, select Format Data Series….
On the Format Data Series task pane,
- Again, click on the Fill & Line icon.
- In the Line section, set the Dash type as Long Dash Dot.
Now, look at the chart. It looks even more beautiful.
Step 05: Determine Break-Even Point in the Chart
In this step, our task is to anticipate the position of the break-even point in the chart. Let’s see the process in detail.
📌 Steps:
- First and foremost, right-click anywhere inside the plot area.
- From the context menu, Choose the Select Data… option.
- Then, click on the Add button in the Select Data Source dialog box.
Suddenly, the Edit Series input box appears.
- Firstly, write BEP in the Series name box.
- Secondly, go to the Break Even worksheet and give the references of cells C10 and C11 in the Series X values and Series Y values box respectively.
- Thirdly, click OK.
You can see the newly added series in the dialog box also. Our work is done here.
- So, click OK.
- Similarly, open the Format Data Series task pane for the newly created series.
- Then, go to the Marker options like before.
- Here, select the Built-in option and choose the circle marker in the Type list.
- Additionally, set the marker Size at 8.
Look, how nicely our break-even point accommodated itself in the chart.
Step 06: Introduce Break-Even Line
Now, we’ll relate the axis values to the BEP. to do this, follow the steps below.
📌 Steps:
- Initially, click on the plus-shaped Chart Element icon.
- Then, click on the arrowhead at the right of Error Bars.
- Hereafter, select More Options….
Suddenly, it opens the Format Error Bars task pane.
- Presently, advance to the Error Bar Options tab icon.
- Then, select Minus in the Direction section and No Cap in the End Style section.
- In the Error Amount region, select Percentage and set it as 100%.
- Thenceforth, click on the drop-down arrow beside Error Bar Options.
- From the list, select Series “BEP” Y Error Bars and do the same for the Vertical Error Bar.
Lastly, our chart gets a final look.
After a bit of furnishing and formatting, the break-even chart looks like the below one.
Calculating Selling Price at BEP (Break-Even Point) Using Goal Seek Tool in Excel
Previously, we have shown how we can create a break-even chart in Excel. Now, we’ll exhibit how we can find out the selling price of a product at the break-even point using the Goal Seek feature of Excel. Let’s see it in action.
📌 Steps:
- To calculate the Revenue, we inserted the following formula in cell C9.
=C4*C5
And, the formula to determine the Profit in cell C10 is given below also.
=C9-C8
What’s the concept of the break-even point? It’s the condition just before a company or business starts to gain profit. So, at the BEP, the profit should be zero. We’ll use this concept in the following part.
- Primarily, jump to the Data tab.
- Secondarily, click on the What-If-Analysis drop-down icon on the Forecast group of commands.
- Then, select Goal Seek from the list.
- In the Goal Seek dialog box, give the references like the following image and click OK.
Here, we are setting the value of cell C10 as 0 by changing the value of cell C5.
Within a second, it gives us the result of 3.90 as the Unit Selling Price.
Practice Section
For doing practice by yourself we have provided a Practice section like the one below in each sheet on the right side. Please do it by yourself.
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice yourself.
Conclusion
This article explains how to make a break-even chart in Excel in a simple and concise manner. Don’t forget to download the Practice file. Thank you for reading this article. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website, Exceldemy, a one-stop Excel solution provider, to explore more.
