# How to Do Multi Product Break Even Analysis in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

## 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. ### 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, 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.
`=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. ## Related Articles Musiha Mahfuza Mukta

I am Musiha, graduated from Naval Architecture and Marine Engineering Dept., BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  