Data Analytics Solution Drove Efficient Decision Making and Improved Operational Efficiencies Across Corporate Functions 

Established in 1997, the client is a world leader in developing and commercializing first-or-best-in-class therapies for rare genetic diseases. Headquartered in California, US, the client has operations spread across 20+ countries and has 2500+ employees worldwide. 

Background

The client was in a growth phase and was expanding its global presence. To support its growth ambition, it identified the need to invest in enterprise-scale systems. Some of the challenges across key corporate functions were:

– Substantial manual effort spent to get up-to-date supply chain planning related information for end-of-month reporting 

– Difficulty and delays in providing answers to common ad hoc questions to support management decisions

– Lack of accuracy in predicting demand for its products

– Inconsistencies in supply chain in terms of production of goods, their delivery and their inventory

– Lack of visibility into production quantity, percentage produced on time/late, percentage scrap, etc

– Difficulty in obtaining insight into planned cost vs. actual cost, cost variance and percentage variance, cost elements

– Difficulty in measuring planned vs. actual material and resource usage, material and resource usage variance and percentage variance

– Limited visibility into material type, resource and operation

– Lack of trust-worthy data for inventory balances, aging, inventory expiration days

– Lack of adherence to regulatory requirements

– Long time-to-market to launch products

– High risk of product recalls

– High overall cost-to-quality

– Lack of insights into turnover, headcount, terminations and new hires information

– Complex and inefficient process using complex SQL packages and Excel Macros for generating reports

– Limited reporting insights available from existing 3rd party business intelligence solutions

– Inability to gather insights on the effectiveness of current rebate programs

– Lack of understanding of the different costs being incurred for different programs and explain their variances from budgeted values

– Lack of visibility for transactional reports to CCMs 

– Lack of accountability in the Finance Team for the business activities related to the different programs

– Facing issues related to file transfer, file size, file location, frequent corruption of macros, etc. with the existing reporting tool

– Facing difficulties in integrating and analyzing clinical research data 

– Finding it difficult to figure out which raw material required restocking, when to place the orders, how much to order, and when the ordered items should be delivered

– Facing challenges in sharing vital information with internal stakeholders and CMOs (contract manufacturing organizations)

DiLytics’ Solution

DiLytics partnered with the client in several analytics initiatives. DiLytics implemented analytics solutions on Oracle Analytics across different corporate functions of the client. A brief description of the insights provided by DiLytics solution in different corporate functions is provided below: 

– Inventory Overview 

– Safety Stock Vs. Projected Inventory 

– Inventory by Org 

– Inventory Balances – Item Status 

– Months on Hand (MOH) – Item Status 

– Months on Hand (MOH) – Overview 

– Production Plan – Overview 

– Production Plan – Fill 

– Production Plan – Fill Schedule 

– Production Plan – Pack/Label 

– Production Plan – Pack Schedule 

– Order Forecast 

– Plan Vs. Actual Ship Quantity 

– Plan Recommendations 

– Forecast Accuracy  

– Forecast Changes 

– Shipment Forecast  

– Forecast Bias  

– Out-of-Trend Forecast 

– Consensus Forecast 

– Production Batch Transactions 

– Indented BOM (Bill of Material)  

– Material Usage Variance 

– Material Transactions 

– Where Used  

– Routing Details 

– Material Aging  

– Scrap  

– Inventory Expiration 

– Inventory Adjustments 

– Real-Time Inventory  

– Zero Stock  

– Stock Outs 

– Confirmed Out of Specification (OOS)/Out of Trend (OOT) 

– Product Complaint Rate 

– Deviation Rate Per Day 

– Product Complaint Closure Rate 

– Closure Rate for Deviations and Corrective Actions Preventive Actions (CAPAs) 

– Past Due Regulatory and Partner Commitments 

– Recruitment process: Employment brand, sourcing, assessment, offer, onboarding, turnover, diversity, internal mobility 

– Recruitment effectiveness: process effectiveness, quality of hire, recruiting performance, technology utilization, hiring mix and volume, recruiting staff 

– Budgeting and costs: cost of hire, recruitment expense, cost of early turnover, overhead cost, staffing efficiency ratio 

– Invoice Register Check 

– VAT Detail 

– VAT Summary 

– Account Reconciliation 

– GL Detail for Capitalization  

– Build & Developed ETL 

– Extended Enterprise Data Warehouse (EDW)  

– Provided reports on Channel Rebate Offer, Channel Rebate Accrual Report, Channel Rebate Claim Report 

– Build & Developed ETL 

– Extended Enterprise Data Warehouse (EDW)  

– Provided reports on Time Reporting, CCM Program Reporting (Summary View & Transaction View)  

– Build & Developed ETL 

– Extended Enterprise Data Warehouse (EDW)  

– Provided reports on Invoice Register Check, Expected vs Effective Rate, Account Reconciliation, CSUT NSA Detail & CSUT Self Assessed Detail, Rec – VAT Detail, IE VAT Summary

– Build & Developed ETL 

– Extended Enterprise Data Warehouse (EDW)  

– Developed Data Integration layer to extract clinical research data from flat files  

– Developed data validation logic to validate the data in the flat files provided by the customer vendors 

– Collaborated with the customer in integrating data from EDW into Board 

– Created new tables and added extended columns in existing tables in EDW 

– Build & Developed ETL 

– Modeled the extended EDW tables in Oracle Analytics to create metrics and attributes 

– Provided reports on Items with BOH Below Safety stock, Need by to Promise date PO, OTD to Promise date PO, Past Due PO Confirmation, Past Due PO, Past Due RM QA Release, Planned Orders Compression Lead Time, PO Lines created from MRP, PO Reschedule In-Out, & Production Shortage Dashboard

– Projected Demand 

– Work Orders 

– Planned Orders 

– Lot Number of the ingredient reserved for the Work Order 

– Beginning On Hand inventory 

– Projected On Hand inventory  

– Safety Stock 

– Months on Hand 

– Targeted Months on Hand 

Business Benefits

Key business benefits to the client across various corporate functions included: 

– Ability to make efficient planning decisions based on visibility into the latest supply chain planning data

– Agility in responding to changes in supply chain situations by Executives and Planners, with enablement of alert triggering mechanisms

– Enhanced focus by Planners on optimization of supply chain process, due to elimination of manual effort required to produce supply chain planning reports 

– Superior ability to identify opportunities for business process improvement, with Executives and Planners obtaining visibility into a variety of business areas

– Accurate, data-driven demand forecasts 

– Increased supply chain efficiency 

– Reduction in inventory holding costs 

– Availability of insights to manufacturing supervisors and executives to spot business process improvements opportunities due to visibility into additional areas not captured currently or correctly

– Visibility into the latest production data on shared dashboards for all stakeholders in the manufacturing process

– Alignment of multiple organization functions along consistent definitions and calculations for metrics and data points 

– Enablement of manufacturing supervisors to redirect their efforts efficiently towards optimizing production operations

– Ability to respond to changes in manufacturing operations for executives and supervisors with availability of automatic alerts on conditions and criteria defined

– Savings in manual effort spent by manufacturing supervisors in deriving manufacturing reports

– Facilitation of compliance with industry best-practice standards and Food and Drug Administration Safety and Innovation Act (FDASIA) reporting expectations 

– Ability to easily view compliance levels across sites and products from a single source and on a continual basis

– Improved efficiency in recruitment function by enablement of recruitment team to easily slice-and-dice requisition data by various parameters such as by recruiter, cost center, location, department, division, status, agency, candidate education, etc. 

– Greater visibility into number of requisitions, total applications, number of offers, hires, etc. and ability to drill down to details 

– Improved compliance check for invoices by ensuring that all the invoices posted during the month are included in the reports

– Improved compliance check for reconciliation of monthly VAT with the report

– Superior insights into Finance with visibility into General Ledger postings and easy drill-down into sub-ledger

– Improved Visibility: To check the products’ performances and create marketing strategies accordingly

– Enhanced Decision Making: To figure out how to allocate resources to optimize channel rebate program

– Improved Relationships with Channel Partners: To understand how channel partners are performing and where they need support  

– Improved accountability by making cost information available at transaction and aggregate level

– Improved identification of cost reduction areas

– Improved regulatory compliance

– Improved accountability by making cost information available at transaction and aggregate level

– Improved identification of cost reduction areas

– Improved regulatory compliance

– Improved decision making via enablement of comprehensive data analysis

– Improved accuracy and completeness of clinical research data due to improved data quality

– Increased efficiency in the clinical research process via identification of bottlenecks and areas of inefficiency

– Superior competitive advantage due to improved collaboration among customer and clinical research data vendors

– Improved inventory control via better scheduling and ordering for raw material items  

– Increased production efficiency via maintenance of a healthy flow of raw materials required for production

– Improved planning and scheduling of the pack and fill process 

– Minimization of deviation from pack and fill plan 

– Prevention of shipment delays and quality issues 

Technical Stack

Source Systems: Oracle EBS, Oracle ASCP, Demantra, TrackWise, Taleo 

Data Integration Tool: Informatica 

Data Warehouse: Oracle Business Analytics Warehouse 

Reporting Tool: Oracle Analytics