How to Do Multi Product Break Even Analysis in Excel

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

• After that, pressENTER, 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.

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.

Related Articles

• Mortgage Break-Even Analysis in Excel (with Easy Steps)