Multinational manufacturer and marketer of chemical products for consumers and industrial customers acquired subsidiaries across various countries over the years. A lack of consolidation compromised real-time decision making and many opportunities for cross-selling and up-selling were lost. See how manual processes were eliminated to give a consolidated view on the sales and inventory (both actual and forecast amounts) and our team simplified access to business and management users access Tableau reports that enable market-, sector-, regional-level views of the data.
The Client:
As a US-based multinational manufacturer and marketer of chemical products for consumers and industrial customers, the client operates more than 70 manufacturing, research and development, and operations facilities in over 30 countries and employs approximately 9,000 associates across four business divisions.
The Challenge
- Although the client had acquired subsidiaries across various countries over the years, there was a lack of consolidation of databases across the various subsidiaries. This resulted in many inefficiencies, among them the cost of maintaining varied software solutions for data management.
- In addition, a lot of manual processing was required for the parent company to get a consolidated view of the sales and inventory (both actual and forecast amounts). Since this could only be consolidated and published once a month, real-time decision making was at stake and many opportunities for cross-selling and up-selling were lost because as a result.
Our Solution
- The first stage of the solution involved building a data warehouse that ingested data (ETL and transformation) from varied source systems (often legacy systems), residing in subsidiary companies that included Cobol, SQL DB, AS400, Firebird, etc.
- Next, a reporting layer was built, which published actuals on sales and inventory daily before the start of business hours and published forecast data once a month. The team handled challenges of multi-geo, multi-entity implementation—such as integrating with systems that had stringent protocol governing new code/software, dealing with inconsistent data availability, handling varying data quality issues across entities, etc.
- The solution was then set up to be able to accommodate the implementation of advanced analytics or AI/ML use cases in the future.
- The tech stack included Databricks, Azure Data Factory, SQL DW, and Tableau.
The Results
By consolidating various databases across subsidiaries, we delivered the following results:
- Consolidated view of sales and inventory across 12 entities in 10 countries with a daily refresh to optimize cost, inventory, and sales across subsidiaries.
- Faster decision making by leveraging resources across subsidiaries in a region to maximize sales.
- Potential savings of up to $21M through the consolidation of systems, as per a consulting exercise carried out by the client.
- No disturbance was caused to the existing user base as the original manual process got stopped only after validation and data reconciliation between two outputs and business sign off.
- Simplified access to business and management users access Tableau reports that enable market-, sector-, region-level views of the data.