How to Do a Multi-Product Break-Even Analysis in Excel Step-by-Step Guide

The following underlying assumptions will limit the precision and reliability of a given cost-volume-profit analysis. The company must produce and sell 800 units of Product A, 1,600 units of Product B, and 4,000 units of Product C in order to break-even. If we know we need $125,000 in sales to break even but the sales mix is different from what we budgeted, the numbers will appear quite different (as you should have noticed in the video). In a multi-product environment, calculating the break-even point requires careful consideration of the individual cost structures, selling prices, and sales volumes for each product.

How to calculate a break-even point with multiple products

Examples for fixed cost would be rent, insurance, managers’ salaries, and other costs that don’t change. Here, in this formula, I have multiplied variable expenses per unit by total target units. The business breaks even when it sells 1,842 units of product A, and 205 units of product B. The small net income of 7 is due to rounding up to the nearest unit during the calculations. However, fixed costs are normally incurred for all the products hence a need to compute for the composite or multi-product break-even point.

• In a multi-product environment, calculating the break-even point requires careful consideration of the individual cost structures, selling prices, and sales volumes for each product.
• We must also proceed under the assumption that the sales mix remains constant; if it does change, the CVP analysis must be revised to reflect the change in sales mix.
• A good break-even analysis should be able to highlight unsustainable variable costs such as labour and materials and identify poor selling or low profitability products.
• The small net income of 7 is due to rounding up to the nearest unit during the calculations.
• We provide tips, how to guide, provide online training, and also provide Excel solutions to your business problems.

Perform break-even sensitivity analysis for a multi-product environment

The only difference is that the denominator is the weighted average contribution per unit (for break-even point in units) or weighted average contribution to sales (C/S) ratio (for Break-even Revenue). The necessary contribution to earn the target profit is the target profit plus the fixed costs. The activity level required to achieve the necessary contribution may be found using contribution per unit, contribution per batch or the CS ratio. Although you are likely to usecost-volume-profit analysis for a single product, you will morefrequently use it in multi-product situations.

How to calculate your break-even point: A guide for ecommerce businesses

In the YouTube video below, our sales mix is 20% for product A, 30% for product B, and 50% for product C. Using the sales mix, you can calculate the weighted average price, weighted average variable cost, and weighted average cost per unit. If a company has $15,000 in fixed cost per month, and their product sells for $50, and the variable cost per unit is $20, that is $30 contribution margin per unit. Once you’ve calculated the BEP for multiple products, you can then use it to assess the current product line-up and profitability for the products you are selling. A good break-even analysis should be able to highlight unsustainable variable costs such as labour and materials and identify poor selling or low profitability products. 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.

We must also proceed under the assumption that the sales mix remains constant; if it does change, the CVP analysis must be revised to reflect the change in sales mix. For the sake of clarity, we will also assume that all costs are companywide costs, and each product contributes toward covering these companywide costs. Although you are likely how to report farm rents on a schedule e to use cost-volume-profit analysis for a single product, you will more frequently use it in multi-product situations. The easiest way to use cost-volume-profit analysis for a multi-product company is to use dollars of sales as the volume measure. For CVP purposes, a multi-product company must assume a given product mix or sales mix.

In this article, we would explain the procedure of calculating break-even point of a multi-product company. So we need to break up the 180 units into the 3 different products using our mix of 60%. Management might want to know what the volume of sales must be in order to achieve a target profit. Using a forecasted or estimated contribution margin income statement, we can verify that the quantities listed will place West Brothers at break-even. For Multi-product margin of safety, the break-even point can be compared to the budgeted activity level using batches, units or revenue. Suppose for example, our business has the same operating expenses of 4,500, but this time is has two product lines.

Calculating the the number of units which need to be sold for the business to reach break even is relatively straight forward when a business sells only one product. Costs that do not change with the level of production or sales, such as rent and salaries, essential for calculating the break-even point. The weighted average CM may also be computed by dividing the total CM by the total sales. In this formula, I have subtracted the fixed expense from the contribution margin. You will get the unit for all the products that should be sold to cover the costs. In this formula, I multiplied the unit variable expense by the expected sale for each product and used the SUM function to add them.

Notice that the composite contribution margin is based on the number of units of each item that is included in the composite item. Calculate the weighted average contribution margin by considering the proportion of each product’s sales to total sales. In the YouTube video below, I use Excel to find the break-even with multiple products. I demonstrate the array function, use conditional formatting, and work with absolute reference to find the weighted average contribution margin per unit.