Automating Margin Analysis and Cutting Data Processing from Hours to Minutes

60%

Faster data extraction using multi-threading

90%

Fewer script failures due to better data type enforcement.

80%

Reduction in data processing time (from 5+ hours to under 1 hour).

Background

A major packaging and printing company with 8,000+ employees and 40+ facilities across North and Latin America aimed to transform its financial analytics. The focus was on Pocket Margin Analysis (PMA), a granular profitability analysis across plants, products, and customers. The company needed a scalable, automated solution to replace fragmented, manual reporting and support strategic decisions.

Challenge

The project tackled several complex issues: • Data fragmentation: Data came from multiple ERP systems, flat files, and manual inputs across 40+ plants. • Limited visibility: Existing tools couldn’t provide real-time, company-wide profitability insights, especially for inter-company sales. • Manual processes: Static Excel-based reporting was slow, error-prone, and lacked scalability. • User empowerment: Needed to enable self-service analytics for financial analysts and plant controllers. • Cross-platform deployment: Required compatibility across Windows, Linux, and Docker environments

Approach

A robust, end-to-end pipeline was built using Python and modern data engineering practices: • Data ingestion: Multi-threaded extraction from SQL Server and flat files, stored as CSVs. • Processing engine: Python-based computation of invoice-level profitability, including UOM conversions and leaker allocations. • Leaker Allocations: Leakers (costs) gathered through Gathering tools surveys across sites and allocated using custom built Python logic. • True-up logic: Adjustments applied using custom Gathering tools surveys to ensure accuracy. • Inter-company sales tracking: Mapped purchase and sales orders across ERPs to calculate company-level margins. • Web interface: Flask-based site with FastAPI backend for triggering builds, managing access, and promoting versions from UAT to Prod. • Visualization: Results consumed via QlikSense with row-level security for leadership insights

Key results

25% improvement in allocation speed

Unified data source: Replaced fragmented site-level reporting with a centralized, accurate system.

Real-time insights: Empowered users to trigger builds and analyze data within minutes.

Benefits

This digital transformation project successfully established a unified, centralized data source to replace fragmented site-level reporting, granting stakeholders real-time insights and the power to analyze data within minutes. This new system delivered strategic visibility by enabling inter-company sales tracking, allowing leadership to accurately view profitability and operational performance across the entire organization. Crucially, this robust foundation is now future-ready, paving the way for advanced initiatives like dynamic pricing, cost optimization, and sophisticated customer segmentation.