Customer and Product Profitability for a Manufacturer with Multiple Sites
Bringing shipment, pricing, cost, and financial records into one refreshable view of company, plant, customer, and product profitability.
Context
We developed a consistent profitability reporting framework for a multi-site manufacturer, spanning company, plant, customer, and SKU performance. The assignment connected monthly and annual financial views with operational data so management could examine revenue, cost, and margin drivers at different levels of the business.
Approach
We built refreshable Power Query workflows integrating shipment records, historical customer price lists, product material costs, and facility trial balances. Data preparation included unpivoting records, standardizing plant and SKU identifiers, aligning units of measure, handling returns, and incorporating customer rebates.
We applied management’s expense-allocation assumptions and documented the treatment of missing product-specific costs, keeping the basis of gross and operating margin comparisons visible.
Results
The workbook unified profitability reporting at four levels (company, plant, customer, and SKU), with monthly and annual views, pivot dashboards, and a management presentation. Refresh instructions supported reuse in operating reviews. Cost-allocation assumptions remained visible, replacing disconnected extracts with a consistent analytical structure.
