Product profitability analysis in Excel

Product profitability analysis in Excel starts with sales by SKU, the cost of those sales and a stated rule for shared expenses. Keep each cost layer visible so a missing join or overlapping expense cannot inflate the result. The workbook should explain the product ranking and prove the total.

Prepare the period and the SKU keys

Take a September 2026 sales-line export for a US industrial distributor. Include invoice-line ID, posting date, SKU, net units and net revenue after discounts, credits and allowances. Use the same business scope, currency and cutoff in the cost and support files.

Aggregate sales into an Excel table named Products, one row per SKU. Add other directly traced fulfillment expenses and recorded warehouse-support hours. In this example those expenses are excluded from COGS, and the hours allocate a separate support pool.

Preserve the invoice lines behind the summary. The units are comparable individual items here; do not add cases and individual units without conversion. Keep returns and their cost reversals aligned with the company's reporting policy.

Use the product taxonomy guide for family and line mappings. This walkthrough owns the SKU workbook implementation; the broader product profitability report explains the management report's cost boundaries.

Join the approved cost without hiding failures

The approved September table named Costs contains SKU and UnitCOGS in adjacent columns. These are the applicable costs of the units sold, including any costs already assigned through the company's costing method.

The illustration uses one approved unit cost per SKU for the period. Where actual costs vary by shipment, lot or date, match at that grain before aggregating. A current price list is not a substitute for recognized cost of sales.

Add CostMatches, UnitCOGS, COGS and BeforeSupport to Products. First check the cost key:

=COUNTIFS(Costs[SKU],[@SKU])

Every result must equal 1. Fix missing and duplicate costs before continuing. Then use:

=VLOOKUP([@SKU],Costs[[SKU]:[UnitCOGS]],2,FALSE)
=[@Units]*[@UnitCOGS]
=[@NetRevenue]-[@COGS]-[@OtherDirect]

Microsoft's VLOOKUP reference confirms that FALSE requests an exact match; duplicate keys return the first match, which is why the uniqueness check matters. Keep missing-match errors visible. Replacing them with zero would make an uncosted sale look profitable.

For a line-level sales workbook, summarize its joined costs by SKU with:

=SUMIFS(Sales[COGS],Sales[SKU],[@SKU])

Microsoft documents SUMIFS as adding values meeting multiple criteria. Use that summed-cost route instead of multiplying by a single unit cost when the costs vary. Covirage is not affiliated with Microsoft.

Allocate the full support pool

Finance approves a $3,000 warehouse-support expense pool for the same period. The pool excludes COGS and the $2,000 of directly traced expenses. The operating owner approves recorded handling hours as the allocation basis; all 60 hours relate to the products in this scope.

Put 3000 in P1. Calculate cost per hour in P2:

=$P$1/SUM(Products[Hours])

The rate is $3,000 / 60 = $50 per hour. A zero driver total needs an exception and a reviewed allocation basis; it cannot produce a meaningful rate. Add AllocatedSupport, Contribution and Margin:

=[@Hours]*$P$2
=[@BeforeSupport]-[@AllocatedSupport]
=IF([@NetRevenue]=0,NA(),[@Contribution]/[@NetRevenue])

Product C consumes 30 of the 60 support hours, so it receives $1,500 of the pool. Its contribution is $5,000 minus $2,500 COGS, $700 direct expenses and $1,500 support, leaving $300. Its margin is $300 / $5,000 = 6.0%.

Percentages are rounded to one decimal place; formulas retain precision. The total margin is $6,300 / $27,000 = 23.3%, rather than the average of SKU margins. For costs driven by customer service activity, use the separate customer cost-to-serve guide.

Prove the contribution and assign the review

Three checks must close. Revenue of $27,000 ties to the scoped sales export. COGS of $15,700 and other direct costs of $2,000 tie to their source accounts. Allocated support equals the entire $3,000 approved pool:

=SUM(Products[AllocatedSupport])-$P$1
=SUM(Products[NetRevenue])-SUM(Products[COGS])-SUM(Products[OtherDirect])-$P$1-SUM(Products[Contribution])

Both formulas return zero. The second proves $27,000 minus $15,700 minus $2,000 minus $3,000 equals $6,300. Corporate expenses outside this scope remain separate, so this subtotal is contribution after allocated support, not company net profit.

Finance owns the revenue and cost reconciliation. Operations owns the hours and pool boundary. The product manager reviews C's support demand and commercial role before changing price or assortment. A waterfall bridge can present subsequent changes without replacing these checks.

Where it goes wrong

• Joining the wrong cost period. Match the cost effective for the sale before summarizing.
• Accepting duplicate SKU keys. Exact lookup alone does not prove a unique match.
• Allocating costs twice. Remove expenses already captured in COGS or directly traced costs from the shared pool.
• Calling allocated costs avoidable. Discontinuation savings require a separate review of costs that actually disappear.

Compare your product files in Covirage

Bring your sales, approved SKU costs and support-driver files; Covirage maps the columns, and its tools compute and check product contribution and allocations. The external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.

See customer profitability analysis for the supporting business context.