Case Study: Real-Time Reporting Solution for Costing Department in the Automobile Industry

Project Timeline: December 2021 – February 2022
Client: Automobile Manufacturer
Technology Stack: AS400, SQL Server 2016 (Data Warehouse), Power BI (Dashboard)

Overview

A leading automobile manufacturer aimed to enhance its Costing Department’s reporting capabilities through a migration from GENTANI-SAP Business Objects (BO) to Power BI. The primary objective was to enable real-time reporting and analytics for better decision-making, ultimately driving cost savings and operational efficiency.

Challenge

The Costing Department faced several challenges with the existing reporting system:

• Delayed Reporting: The legacy GENTANI-SAP BO system provided limited insights and delayed access to critical costing data, hindering timely decision-making.
• Inefficient Scrap Management: High scrap rates and inefficient tracking of scrap-related metrics were leading to significant financial losses.
• Lack of Supplier Performance Insights: The inability to effectively monitor supplier performance impacted procurement decisions and overall operational efficiency.

To address these challenges, the client required a modern BI solution that could provide real-time insights into costing, scrap management, and supplier performance.

Solution

SQL Orbit implemented a comprehensive migration project that transitioned the Costing Department from GENTANI-SAP BO to Power BI. This solution included:

• Real-Time Data Integration: Leveraging AS400 and SQL Server 2016, the data warehouse was configured to provide real-time access to costing and scrap data, allowing for immediate reporting and analysis.
• Dynamic Power BI Dashboards: Custom Power BI dashboards were developed to visualize key metrics, enabling the Costing Department to analyze components and scrap rates with interactive reports.
• Scrap Rate Analysis: The dashboards provided detailed analysis of scrap rates by various factors, such as reason codes, number of scrapped parts, and shifts, facilitating improved scrap management.
• Performance Metrics for Suppliers: The introduction of a Performance Monitoring Metric (PPMeq) allowed for tracking rejection rates at the plant, supplier, and process levels, empowering the procurement department to make informed decisions regarding supplier performance.

Outcomes

The implementation of the Power BI reporting solution resulted in substantial business benefits, including:

1. Significant Reduction in Scrap Rates
   • The scrap rate was reduced by 80.5%, leading to an annual cost savings of $1.3 million for the organization.
2. Improved Scrap Management Efficiency
   • By analyzing scrap data across various factors, the Costing Department achieved a 25% improvement in scrap management efficiency, enabling more effective tracking and resolution of scrap issues.
3. Enhanced Decision-Making for Procurement
   • The PPMeq system enabled better tracking of rejection rates, resulting in a 30% reduction in rejection rates at the plant, supplier, and process levels, allowing for more informed procurement decisions.
4. Increased Supplier Performance
   • The identification of weak suppliers through PPMeq led to targeted actions for improvement, resulting in a 20% increase in supplier performance for critical processes such as machining and casting.

Benefits

• Real-Time Insights: The migration to Power BI provided the Costing Department with real-time reporting capabilities, facilitating quicker decision-making and enhanced operational responsiveness.
• Cost Savings: The significant reduction in scrap rates and improved supplier performance translated into substantial cost savings for the client.
• Enhanced Efficiency: The solution streamlined scrap management processes and improved overall efficiency within the Costing Department.