Break-even analysis in Excel with multiple products

Posted on:  09/13/2017
Break-even analysis in Excel with multiple products

Break-even overview

Calculating the break-even on a company with only one product is relatively easy. Just take the total fixed expenses and divide by the contribution margin per unit. The contribution margin per unit is the unit sales price minus the unit variable cost.

Here is an example. 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. Divide $15,000 by $30, and you end up with 500 units. Five hundred units a month leaves you with $0 income. You did make any money. You also did not lose any money. You broke even.

Target Audience

  1. Accounting students
  2. Executive MBA students
  3. Business owners
  4. Startup business owners
  5. Managers

Multiple products break-even

It gets tricky when you have multiple products. In this case, you need to know the sales mix. 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.

Variable expenses are direct material and direct labor. Sales commission would also be a variable cost. Examples for fixed cost would be rent, insurance, managers’ salaries, and other costs that don’t change. Produce 500 units a month or 5,000 units a month, the fixed cost stay the same.

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.

Calculate the break-even on multiple products

YouTube video on creating a break-even chart

Chris Menard

Chris Menard is a certified Microsoft Trainer (MCT) and works as a full-time Trainer at BakerHostetler - one of the largest law firms in the US. He runs a YouTube channel with 900+ technology videos that cover various tools such as Excel, Word, Zoom, Teams, Gmail, Google Calendar, and Outlook. To date, the channel has helped over 20 million viewers. Menard also does 2 to 3 public speaking events every year, presenting at the Administrative Professional Conference (APC), the EA Ignite Conference, the Support Staff Conference, the University of Georgia, and CPA conferences. You can connect with him on LinkedIn at https://chrismenardtraining.com/linkedin or watch his videos on YouTube at https://chrismenardtraining.com/youtube.

Categories