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 **Microsoft 365 version**.

**Table of Contents**hide

## Download Practice Workbook

You can download the practice workbook from here:

## 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,Â **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 **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 (with Easy Steps)**

### 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 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 (with Easy Steps)**

### Step-7: Compute Contribution Margin

Here, I will calculate the **contribution margin** with the help of sales 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.

## Conclusion

I hope you found this article helpful. Here, I have explained **8 **suitable steps to do **multi-product Break Even Analysis **in Excel. You can visit our website **Exceldemy** to learn more Excel-related content. Please drop comments, suggestions, or queries if you have any in the comment section below.