If you are heading to a new business, then you should know the calculation of the multi-product break-even analysis. So, if you are looking for how to do multi-product break-even analysis in Excel, then you have come to the right place. Today, in this article, I’m going to explain how to do multi-product break-even analysis in Excel.

Furthermore, for conducting the session, I will use the Microsoft 365 version.

## What Is Break-Even Analysis?

**Break-even analysis** is a calculation to find at which number of sales the production cost of the product would be recovered. Basically, the **break-even point **denotes the selling price along with the amount of product that should be sold to get back all the costs. Actually, this analysis is important for a newcomer to the business field. Moreover, the** break-even analysis** is based on the commensuration of the total revenue and cost.

## Step-by-Step Procedures to Do Multi-Product Break-Even Analysis in Excel

Here, I will demonstrate step-by-step procedures on how to do multi-product break-even analysis in Excel. For your better understanding, I am going to use the following dataset, which contains some information like per unit sale, per unit variable expenses, and the expected sale ratio of 3 products along with fixed expenses per month of the company. The dataset is given below.

### Step-1: Compute Weighted Average Selling Price

Here, I will do the **1st** step of the **break-even analysis **of these products. Before that, you may create the following format for your calculation.

- Firstly, you must select cell
**C12**where you want to keep the**weighted average selling price**. - Secondly, you should use the formula given below in the
**C12**cell.

`=SUM(C5*C7,D5*D7,E5*E7)`

In this formula, I have multiplied the unit sale by the expected sale for each product. Then, I used **the SUM Function** to add them.

- After that, press
**ENTER**, and you will see the weighted average selling price.

**Read More: **How to Do NPV Break-Even Analysis in Excel

### Step-2: Obtain Weighted Average Variable Expenses

Now, I will find out the weighted average variable expenses to get the **break-even point **in Excel for multi products.

- Firstly, you have to select cell
**C13**where you want to keep the weighted average variable expenses. - Secondly, you should use the formula given below in the
**C13**cell.

`=SUM(C6*C7,D6*D7,E6*E7)`

Here, in this formula, I have multiplied the unit variable expense by the expected sale for each product. Then, I used the** SUM **function to add them.

- Thirdly, press
**ENTER**to get the result.

### Step-3: Calculate Break Even Point

At this time, I will calculate the break-even point with the help of the weighted average selling price and weighted average variable expenses for multi products in Excel. Actually, this is the basic part of the break-even analysis**.**

- So, select cell
**C15**where you want to keep the**break-even point**. - Then, write down the following formula in the
**C15**cell.

`=C14/(C12-C13)`

Here, in this formula, I have divided the fixed expenses (per month) by the difference between the weighted average selling price and the weighted average variable expenses.

- Subsequently, press
**ENTER**, and you will find the**break-even point**.

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

### Step 4: Determine the Product Amount That Should Be Sold

After that, I will compute the total amount of products that should be sold to cover the cost of each item. Furthermore, I’m going to use the **break-even point** for this.

- Now, select cell
**C8**where you want to keep the**target unit**. - Then, write down the following formula in the
**C8**cell.

`=$C$15*C7`

In this formula, I have multiplied the break-even point by the expected sale ratio.

- Lastly, press
**ENTER**, and you will find the**total unit**of**handbags**that should be sold for balancing the cost.

- Then, you can drag the Fill Handle icon horizontally to AutoFill the corresponding data in the rest of the cells
**D8**&**E8**.

- As a result, you will get the unit for all the products that should be sold to cover the costs.

### Step-5: Obtain Total Sales at Break Even Point

Consequently, I will **calculate the** total sales of each product at** break-even point** for covering the cost. Here, I will use the total target units and the sale of unit products for this.

- So, select the cell
**C9**and write down the corresponding formula in the**C9**cell.

`=C8*C5`

Here, in this formula, I have multiplied the sale per unit by the total target units.

- Subsequently, press
**ENTER**, and you will find the**total sales**of**handbags.**

- Then, you can drag the
**Fill Handle**icon horizontally to**AutoFill**the corresponding data in the rest of the cells**D9**&**E9**.

- At this time, you will get certain sales for all the products to cover the costs.

- After that, for
**sales**at the**break-even point**, use the following formula in the**C18**cell.

`=SUM(C9:E9)`

In this formula, I have added all the target sales.

- Lastly, press
**ENTER**to get the sales (**BEP**).

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

### Step-6: Calculate Total Variable Expense

Now, I will find out the total variable expense for verification purposes.

- Firstly, you have to select cell
**C10**where you want to keep the**variable expenses**for the**handbag**. - Secondly, you should use the formula given below in the
**C10**cell.

`=C8*C6`

Here, in this formula, I have multiplied variable expenses per unit by total target units.

- Thirdly, press
**ENTER**to get the result.

- Consequently, drag the
**Fill Handle**icon horizontally to**AutoFill**the corresponding data in the rest of the cells**D10**&**E10**.

- As a result, you will get certain variable expenses for all the products.

- Then, for total variable expense, use the following formula in the
**C19**cell.

`=SUM(C10:E10)`

In this formula, I have added all the variable expenses of each product.

- Lastly, press
**ENTER**to get the**total variable expense**.

**Read More: **How to Make a Break-Even Chart in Excel

### Step-7: Compute Contribution Margin

Here, I will calculate the **contribution margin** with sales help at the **break-even point **and total variable expense for multi products in Excel. Actually, this is a part of verification. Basically, I will check through this whether the revenue amount becomes equal to the cost or not.

- Now, select cell
**C20**where you want to keep the**contribution margin**. - Then, write down the following formula in the
**C20**cell.

`=C18-C19`

Here, in this formula, I have subtracted the total variable expense from the sales at the **break-even point**.

- After that, press
**ENTER**, and you will find the**contribution margin**.

### Step-8: Evaluate Net Income for Verification

Lastly, I will find out the **net operating income** to prove whether the **break-even analysis** for multi-product was correct or not. Additionally, the **net operating income** should be **zero**.

- So, select cell
**C21**where you want to keep the**net operating income**. - Then, write down the following formula in the
**C21**cell.

`=C20-C14`

Here, in this formula, I have subtracted the fixed expense from the contribution margin.

- Lastly, press
**ENTER**, and you will find the**net operating income**.

As you can see the **net operating income** is** zero**, so you can say that the **break-even analysis **is right.

**Read More: **How to Do Break Even Analysis with Goal Seek in Excel

## Practice Section

Now, you can practice the explained method by yourself.

**Download Practice Workbook**

You can download the practice workbook from here:

## Conclusion

I hope you found this article helpful. Here, I have explained 8 suitable steps to do multi-product Break Even Analysis in Excel. Please drop comments, suggestions, or queries if you have any in the comment section below.

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