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