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

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.

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

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

Practice Section

Now, you can practice the explained method by yourself.

Download Practice Workbook

You can download the practice workbook from here:

Break Even Analysis.xlsx

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

Get FREE Advanced Excel Exercises with Solutions!