Contribution margin in Excel: per unit, ratio and total

The three formulas

• Contribution margin per unit = selling price − variable cost per unit
• Total contribution margin = units sold × CM per unit = sales − total variable costs
• Contribution margin ratio = contribution margin ÷ sales (per unit or in total, the result is the same for one product)

Variable costs are the costs that rise and fall with each unit sold: materials, packaging, piece-rate labour, sales commissions, payment fees, shipping. Fixed costs stay the same within a normal range of activity: rent, salaries, insurance, a leased machine. The split is the whole point of the measure, so it is worth checking each cost line once before building the sheet. Costs that are partly fixed and partly variable, such as electricity, can be split with the high-low method or a regression.

Step by step in Excel

1. One product per row: units in B, price in C, variable cost per unit in D.
2. CM per unit in E2: =C2-D2
3. CM ratio in F2: =E2/C2, formatted as a percentage
4. Total CM in G2: =B2*E2
5. Rank in H2: =RANK(G2,G$2:G$5), the dollar signs keep the range fixed when you fill down
6. Company totals: revenue =SUMPRODUCT(B2:B5,C2:C5), variable costs =SUMPRODUCT(B2:B5,D2:D5), and their difference.

SUMPRODUCT multiplies units by price row by row and adds the results, so you do not need a helper column for revenue. The weighted CM ratio is total contribution ÷ total revenue: 37.9% in the example. Do not average the four ratios in column F; that gives 37.3% and ignores how much of each product is sold.

Worked example: contribution margin of four coffee products

A fictional coffee roastery's annual units, prices and variable costs per unit (yellow). Variable costs are beans, packaging, card fees and shipping. The block below adds up the company-level contribution and compares it with fixed costs.

Worked example: contribution margin of four coffee products

	A	B	C	D	E	F	G	H
1	Product	Units sold	Price	Variable cost / unit	CM / unit	CM ratio	Total CM	Rank (total CM)
2	House blend 250 g	12,000	9.50	5.70	3.80	40.0%	45,600	1
3	Single origin 250 g	4,000	14.00	7.70	6.30	45.0%	25,200	2
4	Espresso pods (box)	9,000	6.00	4.20	1.80	30.0%	16,200	4
5	Cold brew can	15,000	3.50	2.31	1.19	34.0%	17,850	3
6	Total	40,000					104,850
7
8	Company level							Value
9	Sales revenue							276,500
10	Variable costs							171,650
11	Contribution margin							104,850
12	Weighted CM ratio							37.9%
13	Fixed costs (rent, salaries, roaster lease)							72,000
14	Operating profit							32,850
15	Break-even revenue at this product mix							189,871
16	Highest CM per unit							Single origin 250 g

Fictional figures for illustration only. This is a calculation guide, not accounting, tax or business advice.

Reading the example

Single origin coffee has the highest margin per unit ($6.30) and the highest ratio (45%), but it sells only 4,000 bags. The house blend, with a lower ratio of 40%, sells three times as many and contributes $45,600, the largest share of the $104,850 total. Espresso pods have the lowest ratio, 30%, and the smallest total.

Should the roastery drop the pods? Not on these numbers. They still contribute $16,200 a year towards the $72,000 of fixed costs. If the pods disappeared and the fixed costs stayed the same, operating profit would fall from $32,850 to $16,650. A product with a positive contribution margin improves profit unless dropping it also removes fixed costs, frees capacity for something more profitable, or the price is so low that it pulls down the price of other products.

Contribution margin vs gross margin

The two are often confused. Gross margin is sales minus cost of goods sold, and under the usual absorption costing COGS includes a share of fixed production costs such as factory rent and depreciation of the roaster. It leaves out selling costs such as card fees and shipping. Contribution margin subtracts every variable cost, from production and from selling, and no fixed cost at all. That makes it the better tool for short-term decisions: pricing a special order, choosing which product to push, or calculating break-even. For the difference between margin and markup on the price itself, see markup vs margin.

From contribution margin to break-even and capacity

Fixed costs ÷ CM ratio gives the break-even revenue: $72,000 ÷ 37.9% = $189,871 at the current product mix. If the mix shifts towards pods and cold brew, the weighted ratio falls and break-even rises, even though no price or cost changed. The break-even analysis guide goes further with margin of safety, target profit and operating leverage.

When a resource is scarce, rank products by contribution per unit of that resource instead. If the roaster can run only 2,000 hours a year, the useful number is contribution per roasting hour: divide CM per unit by the hours each unit needs. A product with a modest margin that roasts quickly can beat a premium product that blocks the machine.

Common mistakes

• Counting fixed costs as variable. Spreading rent or salaries over units makes a product look unprofitable and leads to wrong drop-or-keep decisions.
• Forgetting variable selling costs. Payment fees, marketplace commissions, packaging and free shipping are variable costs too and often decide whether a product really contributes.
• Averaging CM ratios. The company-level ratio is total contribution ÷ total revenue, weighted by sales, not the simple average of product ratios.
• Dropping every low-ratio product. A low ratio with a positive margin still covers fixed costs. Check what changes in fixed costs and capacity before cutting it.
• Using gross margin for short-term decisions. Gross margin includes allocated fixed production costs, so it is not the extra profit from one more unit.