Cellwise Start free

Excel guide · 5 min read

How to calculate contribution margin by product in Excel

Quick answerFor each product, contribution margin per unit is price minus variable cost per unit (=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.

RowABCDEFGHIJ
1ProductPriceVariable costUnits soldCM per unitCM ratioTotal CMFixed costs (month)
2House Blend 1kg2814.564013.50.48214285714864012000
3Single Origin 250g166.24109.80.61254018
4Decaf 1kg3017.812012.20.406666666671464
5Cold Brew 6-pack2415.65208.40.354368
6Gift Box553190240.436363636362160
7Drip Bags 10-pack124.13007.90.658333333332370

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).

=F3

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.

=SUMPRODUCT(D2:D7,E2:E7)

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.

=SORTBY(A2:A7,G2:G7,-1)

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.

=SORTBY(A2:A7,F2:F7,-1)

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.

=SUMPRODUCT(D2:D7,E2:E7)/SUMPRODUCT(B2:B7,D2:D7)

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.

=J2/(SUMPRODUCT(D2:D7,E2:E7)/SUMPRODUCT(B2:B7,D2:D7))

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

Related guides

Updated