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.

**Table of Contents**hide

## 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.

`=sum`

Instantly, 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
column.*Unit*

- 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$9`

In fact, we multiplied the number of ** Units** by the

**. Here, we used an**

*Average Variable Cost***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
column only.*Profit* - 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
worksheet and give the references of cells*Break Even***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
, we inserted the following formula in cell*Revenue***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.