Most small business owners know their overall profitability but have no idea which products are making money and which are quietly destroying it. A product profitability spreadsheet shows you exactly where your margin comes from.
Why Gross Margin Is Misleading Alone
You might know that you make a 40% gross margin overall. But that average hides wide variation:
- Product A: 65% margin
- Product B: 48% margin
- Product C: 12% margin
Product C is pulling your average down — and you might be spending as much marketing and operational energy on it as on Product A.
The True Profitability Calculation
Start with revenue and subtract costs in layers:
Layer 1: Direct material costs What do the materials, components, or wholesale goods cost for this product?
Layer 2: Direct labor costs How much labor time does this product require? Multiply hours × loaded labor rate (include employer taxes and benefits in the labor rate).
Layer 3: Variable overhead Costs that vary with production volume: packaging, shipping prep, payment processing fees specific to this product.
Gross Profit = Revenue − (Layer 1 + Layer 2 + Layer 3)
Layer 4: Allocated fixed overhead Costs that support the entire operation: rent, utilities, equipment depreciation, software subscriptions. Allocate these by a reasonable driver — machine hours, floor space, or simply revenue percentage.
Net Profit = Gross Profit − Allocated Fixed Overhead
Building the Spreadsheet
Header rows (inputs):
- Selling price per unit
- Units sold this period
- Material cost per unit
- Labor time per unit (hours)
- Labor rate ($/hour including burden)
- Variable overhead rate (%)
- Fixed overhead allocation ($ amount per period)
Calculated outputs:
- Gross margin per unit
- Gross margin %
- Net margin per unit
- Net margin %
- Total profit contribution
Put each product in a separate column (or row) so you can compare side by side.
The Insight Section
Below your per-product calculations, add summary analysis:
Contribution by product: Which product generates the most absolute gross profit?
Margin by product: Which product has the highest margin percentage?
Volume sensitivity: If you sold 20% more of Product A vs. Product C, how does total profit change? (Hint: Product A with higher margin contributes far more per unit sold.)
Breakeven by product: How many units does each product need to sell to cover its allocated overhead?
What to Do With the Analysis
Double down on high-margin products. If Product A drives 65% margin, it deserves more shelf space, more marketing spend, and more inventory investment.
Fix or cut low-margin products. For Product C at 12% margin, the question is: can you raise prices, reduce costs, or should you discontinue it? Every product should justify its presence in your lineup.
Price check. Products with very high margins might be underpriced — the market may accept a price increase. Products with compressed margins often have pricing power being left on the table.
Watch for margin trends. Run this analysis quarterly. If a product’s margin is declining, costs are rising faster than prices — a leading indicator of a problem before it shows up in overall P&L.
Common Mistakes
Using revenue share to allocate overhead. A low-margin product with high volume gets penalized; a high-margin product with low volume looks artificially good. Use a more neutral driver like units or machine hours if possible.
Forgetting indirect costs. Customer service time, returns processing, and billing complexity are real costs that vary by product. High-return products have hidden costs that don’t appear in the direct cost calculation.
Not updating for input cost changes. Material costs, shipping rates, and labor costs change. If you built this spreadsheet a year ago with old numbers, your margin analysis is wrong today.
Run your product profitability analysis this month. Identify your top three products by net margin and your bottom three. The decisions that follow from that analysis will likely make more difference to your business than any other financial lever you have.
5 Google Sheets Every Small Business Needs
Cash flow, P&L, mileage log, invoice tracker, and payroll — all free.
No spam. Unsubscribe any time.