Excel guide · 5 min read
How to calculate contribution margin by product in Excel
=B2-C2), the CM ratio is that divided by the price (=E2/B2), and total contribution is CM per unit times units sold (=E2*D2). Add the products up with =SUMPRODUCT(units, CM per unit). For the whole mix, divide total contribution by total revenue to get the weighted CM ratio, not the average of the product ratios.Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H | I | J |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Product | Price | Variable cost | Units sold | CM per unit | CM ratio | Total CM | Fixed costs (month) | ||
| 2 | House Blend 1kg | 28 | 14.5 | 640 | 13.5 | 0.48214285714 | 8640 | 12000 | ||
| 3 | Single Origin 250g | 16 | 6.2 | 410 | 9.8 | 0.6125 | 4018 | |||
| 4 | Decaf 1kg | 30 | 17.8 | 120 | 12.2 | 0.40666666667 | 1464 | |||
| 5 | Cold Brew 6-pack | 24 | 15.6 | 520 | 8.4 | 0.35 | 4368 | |||
| 6 | Gift Box | 55 | 31 | 90 | 24 | 0.43636363636 | 2160 | |||
| 7 | Drip Bags 10-pack | 12 | 4.1 | 300 | 7.9 | 0.65833333333 | 2370 |
Download this sheet as an Excel practice file (.xlsx)
CM per unit, CM ratio and total contribution
Contribution margin is what each sale leaves over, after the costs that rise with every unit, to pay for fixed costs and then profit. The sample is a small coffee roaster's month: six products with price, variable cost per unit (beans, packaging, card fees, shipping materials: anything you spend again for each extra unit) and units sold. Fixed costs (rent, salaries, the roaster lease) are in J2 and stay out of the product rows.
Three formulas, filled down: E2 =B2-C2 (CM per unit), F2 =E2/B2 (CM ratio, formatted as a percentage), G2 =E2*D2 (total contribution).
Result: 0.6125 shows as 61.3% with Percent format
0.6125 for Single Origin 250g: a 9.80 margin on a 16.00 price. The same bag contributes 4,018 in total from 410 units.
What counts as a variable cost?
A cost that goes up when you sell one more unit: materials, packaging, per-order shipping, payment fees, sales commission. Rent and salaries don't change with one more sale, so they're fixed. Costs that are a percentage of price (card fees, marketplace fees) belong in the variable cost too; work them out from the price in their own column so they update when the price does.
Total contribution with SUMPRODUCT
SUMPRODUCT multiplies units by CM per unit row by row and adds the results, so you get the total without a helper column. The same pattern on price gives total revenue. Subtract fixed costs from total contribution for the month's operating profit.
Result: 23020
23,020 of contribution from 49,110 of revenue (=SUMPRODUCT(B2:B7,D2:D7)). After 12,000 of fixed costs, that leaves 11,020: =SUM(G2:G7)-J2.
Rank products by contribution, not revenue
Revenue rankings flatter products with high prices and thin margins. SORTBY lists the products by total contribution; sort by price x units for comparison. Cold Brew 6-pack is the second-biggest seller by revenue (12,480, about 25% of sales) but brings in only 19% of the contribution, because its CM ratio is the lowest at 35%. Drip Bags tie with Decaf for the lowest revenue (3,600 each), yet contribute 2,370 against Decaf's 1,464.
Result: spills 6 rows × 1 column
| House Blend 1kg |
| Cold Brew 6-pack |
| Single Origin 250g |
| Drip Bags 10-pack |
| Gift Box |
| Decaf 1kg |
House Blend 1kg, Cold Brew 6-pack, Single Origin 250g, Drip Bags 10-pack, Gift Box, Decaf 1kg. By revenue (=SORTBY(A2:A7,B2:B7*D2:D7,-1)) Drip Bags drop to last, behind Gift Box and Decaf.
Ranking by CM ratio instead
=SORTBY(A2:A7,F2:F7,-1) puts Drip Bags first (66%) and Cold Brew last (35%). The ratio tells you which sales are most profitable per dollar; total contribution tells you which products actually pay the bills. Look at both: a high-ratio product with tiny volume won't cover much rent.
Result: spills 6 rows × 1 column
| Drip Bags 10-pack |
| Single Origin 250g |
| House Blend 1kg |
| Gift Box |
| Decaf 1kg |
| Cold Brew 6-pack |
Drip Bags 10-pack, Single Origin 250g, House Blend 1kg, Gift Box, Decaf 1kg, Cold Brew 6-pack.
Excel 2019 or earlier
SORTBY needs Excel 2021 or later. In older versions, select the table and use Data > Sort on the Total CM column, largest to smallest, or rank each row with =RANK.EQ(G2,$G$2:$G$7).
The weighted average CM ratio of your mix
To plan with the whole business (how much revenue covers fixed costs, what a sales target is worth), you need one CM ratio for the mix. That's total contribution divided by total revenue, which weights each product by how much of it you sell.
Don't average the six ratios. =AVERAGE(F2:F7) gives 49.1%, because it counts the small Drip Bags line (66%) as much as House Blend. The weighted ratio is 46.9%. Applied to 49,110 of revenue, the two differ by about 1,090 of contribution a month.
Result: 0.46874363673 shows as 46.9% with Percent format
0.4687: each dollar of sales, at this mix, contributes about 47 cents to fixed costs and profit.
Break-even revenue for the mix
Divide fixed costs by the weighted CM ratio. It holds only while the mix stays the same: sell more Cold Brew and fewer Drip Bags and the ratio drops, so break-even rises. For one product, see the break-even point guide.
Result: 25600.347524
About 25,600 of revenue a month (25,600.35) covers the 12,000 of fixed costs at this mix.
Keep fixed costs out of the product rows
It's tempting to share rent and salaries across products ("each product carries 2,000 of overhead") and show a profit per product. That makes a product with a positive contribution look like a loss, and dropping it would remove its contribution while the rent stays the same. Here Decaf contributes 1,464 a month; charge it a sixth of the fixed costs and it shows a 536 loss, yet the business would be 1,464 worse off without it (unless the space or time went to something more profitable).
Keep per-product numbers at contribution, and show fixed costs once, at the bottom. If a fixed cost truly belongs to one product (a machine only Cold Brew uses), subtract it from that product's total contribution as a separate line and call the result product margin.
Common mistakes
Averaging the CM ratios of each product
Use total contribution / total revenue (two SUMPRODUCTs) so big sellers count more.
Leaving percentage fees out of variable cost
Card and marketplace fees rise with each sale. Put them in variable cost, calculated from the price.
Spreading fixed costs over products
It turns products that contribute into apparent losses. Keep fixed costs in one cell below the products.
Dividing by cost instead of price for the ratio
CM ratio is CM / price. CM / variable cost is a markup-style number and will look much bigger.
FAQ
What is the formula for contribution margin in Excel?
Per unit: =price-variable cost. Ratio: =(price-variable cost)/price. Total: =(price-variable cost)*units, or =SUMPRODUCT(units, CM per unit) across all products.
How is contribution margin different from gross margin?
Gross margin subtracts cost of goods sold, which can include some fixed production costs. Contribution margin subtracts only variable costs, including variable selling costs like fees and commission. They're close for a reseller and further apart for a manufacturer.
How do I calculate a weighted average contribution margin?
Divide total contribution by total revenue: =SUMPRODUCT(units,CM per unit)/SUMPRODUCT(units,price). The weights come from how much of each product you sell.
Can contribution margin be negative?
Yes, if the variable cost per unit is higher than the price, often after a discount or a cost increase. Each extra unit then loses money before fixed costs. Flag it with conditional formatting on the CM per unit column (Cell Value less than 0).
Functions used
SUMPRODUCTMultiply then addSORTBYSorts by other columns, spillsSUMAdds numbersAVERAGEMean of numbersRANK.EQRank in a list, ties share the top place
Related guides
- How to do a price volume mix analysis in Excel
- How to calculate the break-even point in Excel
- How to calculate profit margin in Excel
- How to calculate markup in Excel
- Make or buy analysis in Excel
- How to calculate backorder rate in Excel
Updated