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 Microsoft Certified Trainer (MCT) and is employed full-time as a Trainer for BakerHostetler, one of the nation’s largest law firms. Menard has a YouTube channel with over 900 technology videos covering Excel, Word, Zoom, Teams, Outlook, Gmail, Google Calendar, and other resources that over 15 million viewers have appreciated. Menard also does public speaking at conferences for CPAs and Administrative Professionals. Connect with Chris on LinkedIn at chrismenardtraining.com/linked or on YouTube at chrismenardtraining.com/youtube

Categories