The question that takes three weeks to answer

‍

Finance gets asked the same question every planning cycle: which customers are actually profitable? Which SKUs should we discontinue? Which product lines are we subsidizing?

The answers exist in the company's data. The revenue is in the ERP. The freight costs are in the TMS or carrier invoices. The warehousing costs are in the WMS or the 3PL billing statements. The trade spend is in a spreadsheet maintained by the sales team. The returns data is split between the ERP and the customer service system.

The analysis itself is not conceptually difficult. Allocate costs to the customer or SKU level, subtract from revenue, calculate the margin. Finance teams know how to do this. The problem is the data assembly. Each cost pool lives in a different system, uses different identifiers, covers different time periods, and requires different allocation logic. By the time the analyst has pulled, normalized, linked, and allocated costs from five systems, weeks have passed and the planning cycle has moved on.

The result is that customer and SKU profitability analysis is done infrequently, on a subset of the data, or with rough allocations that are directionally correct but not precise enough to make specific decisions. The top-line answer, "our largest customers are the most profitable," is intuitive but often wrong once below-the-line costs are fully allocated.

‍

Why profitability analysis stalls at data assembly

‍

The analytical framework for customer and SKU profitability is well understood. Activity-based costing, contribution margin analysis, and cost-to-serve models have been standard tools for decades. What prevents most companies from running them routinely is the practical difficulty of assembling the cost data at the right level of granularity.

‍

Each cost pool has a different grain

‍

Revenue is recorded at the invoice line level: customer, SKU, quantity, price, date. Freight costs may be recorded at the shipment level, the BOL level, or the carrier invoice level, none of which map directly to the customer-SKU combination. Warehousing costs are recorded at the facility level or the pallet position level. Trade spend is recorded at the customer-program level (promotional allowances, volume rebates, slotting fees). Returns are recorded at the RMA level.

Allocating each cost pool to the customer-SKU level requires a different allocation key. Freight costs might be allocated by weight, by cube, or by number of deliveries. Warehousing costs might be allocated by pallet positions occupied, by pick activity, or by storage duration. Trade spend is typically already at the customer level but not the SKU level.

Each allocation requires a join between the cost data and the allocation key, which lives in yet another data source. Freight allocation by weight requires knowing the shipped weight per customer-SKU combination, which is in the shipment data, not the cost data.

‍

Identifiers do not match across systems

‍

The ERP identifies customers by customer number. The TMS identifies them by ship-to location. The WMS identifies them by warehouse account code. The trade spend spreadsheet uses customer names, sometimes abbreviated. The returns system uses RMA numbers linked to order numbers linked to customer numbers.

Before any cost can be allocated, the analyst must build a crosswalk between identifiers. Customer 4200 in the ERP is ship-to WHRSE-0042 in the TMS, account WH-4200 in the WMS, and "Johnson Foods" in the trade spend tracker. This mapping is maintained manually or not maintained at all, rebuilt from scratch for each analysis.

SKU-level crosswalks are similarly fragmented. The ERP uses an internal SKU. The warehouse uses a different item number. The trade spend tracker references product groups, not individual SKUs. Linking costs to the right product at the right granularity requires mapping every identifier system to a common reference.

Time periods rarely align

‍

The analysis covers a fiscal quarter. Revenue is recorded at invoice date. Freight costs are billed weekly in arrears, with the final week's billing arriving two weeks after quarter-end. Warehousing costs are billed monthly, often mid-month, so a quarterly analysis requires prorating. Trade spend programs run on their own calendars: a promotional period that crosses quarter boundaries needs to be allocated proportionally. Returns may be recorded when the RMA is issued, when the product is received back, or when the credit memo is posted, each of which may fall in a different quarter.

Aligning all cost pools to the same time period is a non-trivial reconciliation that the analyst performs before the actual profitability calculation begins.

Allocation methods are judgment calls

There is no single correct way to allocate shared costs. Freight costs for a multi-stop delivery can be allocated by weight delivered at each stop, by revenue at each stop, or equally across stops. Warehousing costs for a SKU stored in multiple zones can be allocated by pallet positions, by throughput, or by value.

The allocation method affects the result. A heavy, low-value SKU looks unprofitable under weight-based freight allocation but acceptable under revenue-based allocation. These methodological choices need to be documented, consistently applied, and defensible to the business leaders who will make decisions based on the results.

‍

What the profitability analysis needs to produce

‍

A useful customer or SKU profitability analysis provides three layers: a high-level ranking that answers the strategic question, a detailed cost breakdown that explains the ranking, and enough methodological transparency that the results can be challenged and defended.

1. Revenue attribution

Assign gross revenue to each customer-SKU combination from invoice data. Apply any invoice-level adjustments: early payment discounts, volume pricing, returns credits. The result is net revenue per customer-SKU for the analysis period.

Revenue attribution is typically the most straightforward step because the data lives in one system and is already at the right granularity. The complications arise from credit memos, rebate accruals, and returns that need to offset the correct customer and SKU.

2. Direct cost allocation

Allocate COGS at the SKU level: raw material costs, conversion costs, packaging. For manufactured products, this comes from the standard cost or actual cost in the ERP. For purchased finished goods, this is the landed cost. Direct costs are the first layer of profitability and typically the only layer that companies calculate routinely.

3. Freight and delivery cost allocation

Allocate outbound freight costs to the customer-SKU level. This requires matching shipment data (which customer received which SKUs in which quantities on which shipment) against carrier cost data (what the company paid for each shipment). The allocation key, weight, cube, or number of deliveries, should reflect the cost driver.

Freight allocation is where the analysis gets granular. A customer that orders frequently in small quantities generates more deliveries and higher per-unit freight cost than a customer that orders monthly in full truckloads. This difference is invisible in the revenue data but significant in the profitability analysis.

4. Warehousing and handling cost allocation

Allocate storage and handling costs to the customer-SKU level. Storage costs can be allocated by the space the SKU occupies (pallet positions multiplied by time). Handling costs can be allocated by pick activity (number of order lines, number of units picked). Both require data from the WMS or 3PL billing.

Warehousing cost allocation often reveals that a small number of slow-moving SKUs occupy disproportionate storage space. A SKU that turns twice a year occupies a pallet position for six months per unit sold, while a SKU that turns twelve times occupies the same position for one month. The storage cost per unit sold is six times higher for the slow mover.

5. Trade spend and promotional cost allocation

Allocate trade spend to the customer level and, where possible, to the SKU or category level. Promotional allowances, slotting fees, volume rebates, co-op advertising, and markdown allowances are all costs that reduce the effective revenue from a customer.

Trade spend allocation often changes the profitability ranking. A customer that generates $5 million in revenue with $800,000 in trade spend has a different contribution than a customer that generates $3 million with $150,000 in trade spend. The second customer may be more profitable despite lower revenue.

6. Returns and allowance allocation

Allocate return costs and allowances to the customer-SKU level. This includes the cost of processing the return, the write-down on returned product, and any restocking or disposal costs. High-return customers and high-return SKUs absorb costs that do not appear in the standard margin calculation.

‍

From assembling the data to reviewing the results

‍

The time-consuming portion of customer and SKU profitability analysis is the data assembly: pulling cost data from five systems, building identifier crosswalks, aligning time periods, and applying allocation logic. The valuable portion is the business discussion that follows: which customers to reprice, which SKUs to rationalize, which cost-to-serve problems to address.

The Agent handles the data assembly. Upload the data exports from each system: ERP revenue and COGS data, freight carrier invoices or TMS cost reports, WMS storage and handling reports or 3PL billing statements, the trade spend tracker, and returns data. Describe what the analysis should produce:

"Build a customer-SKU profitability matrix for the period. Allocate freight costs by shipped weight. Allocate warehousing costs by pallet positions and pick activity. Allocate trade spend at the customer level. Allocate returns at the customer-SKU level. Produce a contribution margin by customer and by SKU, ranked by profitability. Flag customers and SKUs that are margin-negative after full cost allocation. Show the cost breakdown for each so the team can identify the specific cost driver."

The output is a profitability matrix: each customer ranked by contribution margin after all cost allocations, each SKU ranked similarly, and a detailed cost waterfall for each showing where the margin erodes. The bottom-decile customers and SKUs are flagged with the specific cost pool that drives them below the margin threshold. The allocation methodology is documented for each cost pool.

The finance team reviews the rankings, validates the allocations against their operational knowledge, and presents the results to the business. When the VP of Sales asks why Customer X is flagged as unprofitable despite $4 million in revenue, the cost breakdown shows $620,000 in freight costs from weekly small-quantity deliveries and $340,000 in trade spend, with the supporting data traceable to the carrier invoices and promotion agreements.

The Agent works with the files the team already has: ERP exports, carrier invoices, WMS reports, trade spend spreadsheets, returns data. No BI platform or data warehouse required.

‍

What the numbers look like

‍

A mid-market CPG company with 120 customers, 800 SKUs, and $85 million in annual revenue.

Before: Profitability analysis is run annually, typically during the budget cycle. The FP&A analyst spends three weeks assembling data from the ERP, the 3PL billing portal, the TMS, and the trade spend tracker. Freight and warehousing costs are allocated using revenue-based proration because the data to allocate by weight or activity is too time-consuming to assemble. The result is a customer profitability ranking that confirms the obvious (large customers are profitable, small customers are marginal) but cannot explain why two customers with similar revenue have different margins.

After: Full cost allocation across all five cost pools at the customer-SKU level. Freight allocated by shipped weight, warehousing by pallet positions and pick activity, trade spend by customer program, returns by customer-SKU. Results: 14 of 120 customers are margin-negative after full cost allocation, including 3 customers in the top-20 by revenue. The primary cost drivers for the top-20 exceptions are freight (frequent small-drop deliveries generating 3.2x the per-unit freight cost of full-truckload customers) and trade spend (promotional allowances exceeding 18% of net revenue). At the SKU level, 83 of 800 SKUs are margin-negative after warehousing allocation, with 61 of those carrying below-average inventory turns that drive disproportionate storage cost. The total margin drag from the bottom-decile customers and SKUs is $3.2 million annually.

The specifics shift by industry:

• In food and beverage, cold chain logistics add a cost layer that varies significantly by customer. A customer requiring temperature-controlled delivery to 12 retail locations generates fundamentally different freight and handling costs than a customer accepting ambient delivery to a single distribution center. The cost-to-serve difference is often larger than the gross margin difference.
• In manufacturing, customer profitability is affected by order complexity. A customer that orders configured products with frequent engineering changes generates costs in engineering, production planning, and quality that do not appear in the standard BOM. Allocating these costs requires linking engineering hours and change orders to the customer, which is in the project management system, not the ERP.
• In distribution, margin lives and dies on freight. A customer 200 miles from the distribution center with weekly deliveries of mixed pallets has a fundamentally different cost-to-serve than a customer 20 miles away receiving full truckloads monthly. Revenue-based freight allocation masks this difference entirely. Activity-based allocation reveals it.

Every cost allocation is documented with the source data, the allocation key, and the method. When the business challenges a profitability number, the supporting detail is already assembled.

‍

The answer is in the data, not the analysis

‍

Companies do not lack the analytical capability to calculate customer and SKU profitability. They lack the bandwidth to assemble the data. The analysis requires cost information from five or more systems, each with different identifiers, different granularity, and different time periods. Building the unified dataset is the bottleneck, not the math that follows.

When the data assembly runs systematically against every cost pool, every customer, every SKU, profitability analysis becomes a quarterly deliverable instead of an annual project. The questions, which customers to reprice, which SKUs to rationalize, which delivery patterns to restructure, get answered while there is still time to act on them.

‍