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.

`=sum`

- Double-click the
**SUM function**to select it or press**TAB**.

- Select
**C8:C12**(the fixed cost).

- Use this formula in
**C7**:

`=SUM(Dataset!C8:C12)`

- Press
**ENTER.**

- Use this formula in
**C8**to find the Total Variable Cos.

`=SUM(Dataset!C15:C18)`

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`

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

- Go to
**C11**and enter the following formula.

`=C7/((C5-C9)/C5)`

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

- Go to
**C5**and enter the following formula.

`='Break Even'!$C$7`

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

`=B5*'Break Even'!$C$9`

The number of ** Units** was multiplied by the

**. An**

*Average Variable Cost***absolute cell reference**was used.

- Add the variable cost to the fixed cost to get the
in*Total Cost***E5**.

`=C5+D5`

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

### Step 4 – Insert Break-Even Chart

** Steps:**

- Select the table without the
column.*Profit* - 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
worksheet and give the references of*Break Even***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**

T**Steps:**

- To calculate the
, enter the following formula in*Revenue***C9**.

`=C4*C5`

- Use this formula to determine the
in*Profit***C10**.

`=C9-C8`

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**