Product Profitability Analyzer
Calculates the true profitability of each product or service by allocating direct and indirect costs. Ranks products by contribution margin and net margin to identify which generate profit and which erode it.
Business - Product Profitability Analyzer.xlsx
Excel (.xlsx) — No macros — Works in Excel, Google Sheets, LibreOffice
What This Spreadsheet Solves
- Inability to see which products actually make money after all costs are allocated
- Revenue-focused decision-making that ignores margin differences across products
- No method to allocate shared overhead costs to individual products
- Difficulty identifying loss-making products hidden inside a profitable business
- Lack of data to support product discontinuation or pricing decisions
Who This Is For
- Product managers optimizing product portfolios
- CFOs analyzing margin by product line
- E-commerce operators managing large SKU catalogs
- Manufacturing managers evaluating production priorities
Inputs
- textProduct Name
- $Unit Price
- #Units Sold
- $Direct Cost per Unit
- $Allocated Overhead per Unit
Outputs
- Revenue per Product
- Contribution Margin per Unit
- Contribution Margin %
- Net Profit per Product
- Profit Ranking
- Cumulative Profit Contribution
How Calculations Work
Revenue is calculated as unit price times units sold. Contribution margin subtracts direct costs from unit price. Net profit further subtracts allocated overhead. Products are ranked by total net profit contribution and displayed with cumulative percentages to reveal which products drive most of the profit.
Example Use Case
Scenario: A company sells three products: Product A ($50 price, 1,000 units, $20 direct cost, $10 overhead), Product B ($120 price, 300 units, $60 direct cost, $25 overhead), and Product C ($25 price, 2,000 units, $18 direct cost, $8 overhead).
Result: Product A: $20K net profit (40% margin). Product B: $10.5K net profit (29% margin). Product C: -$2K net loss (-4% margin). Product C is unprofitable and should be repriced or discontinued.
What You Get — 5 Sheets
Technical Details
Frequently Asked Questions
How should I allocate overhead if I do not have activity-based costing data?
Use revenue-based allocation as a reasonable default. It distributes overhead in proportion to each product's share of total revenue. Select this in CONFIG.
What is the difference between contribution margin and net margin?
Contribution margin subtracts only direct/variable costs. Net margin subtracts both direct costs and allocated overhead/fixed costs. A product can have a positive contribution margin but negative net margin.
How many products can I analyze?
The template supports up to 50 products. Add rows in the INPUT sheet as needed.
Should I discontinue every product with a negative net margin?
Not necessarily. If a product has a positive contribution margin, it still helps cover fixed costs. Discontinue only if removing it does not shift overhead burden to remaining products in a way that makes them unprofitable.
Can I include services alongside physical products?
Yes. Enter services the same way. For services, direct cost per unit is typically labor cost per delivery. Overhead allocation works identically.
Download Product Profitability Analyzer
Ready to use immediately. Enter your data in the INPUT sheet, see results in OUTPUT.