CFR EPP Tool
Apr'9th 2018 - Aug'22nd 2018
VWR International LLC
Project Charter
PROBLEM STATEMENT
Generate 2 Customer Facing Environmentally Preferred Products reports by extracting the data from SAP BEx, input them into the Microsoft Access. Run queries and export the resulting tables to the desired template and format. This involves manual copying and pasting data from separate tabs which is time consuming and have quality issues.
OBJECTIVE
Primary Objective:
-
Reducing the manual process and increase the quality
-
To propose an alternate method that eliminates the usage of Microsoft Access by introducing power queries and power pivots
Secondary Objective
-
To save time taken to process from 120 Min to 5 Min by implementing automation.
-
To develop a procedure for extracting the data from SAP BEx and format various sheet tabs in the desired excel dashboard.
-
Quality check once the reports are generated.
EXPECTED DELIVERABLES
Implementing Automation, will lower the rework and increase quality, through
-
Poka-yoke
-
Without using Microsoft Access,
-
Proceeds only if Account Reconciliation is complete.
-
Without adding additional resources,
-
Decrease time,
-
Lower human fault,
If achieved, the CFR EPP reports could be produced with greater quality in very short time.
PROJECT SCOPE- IN SCOPE
In Scope:
Quality Management, Customer Service Satisfaction, Use of Power Queries and Power Pivots, Use of Automated tool, Time Management, Generates only if Account Reconciliation is completed, Associates who run EPP Reports, If tool is slightly adjusted it can run GPO EPP reports
Out Scope:
Can only be used for CFR EPP Reports
Work Break down Structure
The Tool has been created on Microsoft Excel VBA. It has been incorporated with SAP BEx query, excel VBA, Power Queries and Power Pivots. It needs to check first whether Account Reconciliation is complete. If is completed then it needs to run, extract, Update queries, Pivot tables, format and populate the data into desired template for the user specified time period. If Account Reconciliation is not completed then tool needs to terminate stating that "Account Recon not done". The QC mechanism is also incorporated successfully. If this is implemented successfully then without using Microsoft Access the Turn Around Time for the stakeholder can be achieved. There will be no issues on quality
The Tool has been created and it performs good. The user needs to open the template in SAP BEx explorer and enter the time period in the desired cell and click "Start". The tool first check whether the Account Reconciliation is complete and if yes, the tool runs, extracts the data from SAP, Updates the Power Queries and Refresh the Power Pivots. Then Copies them to the respective sheet tabs on the desired template and formats each tab based on requirements. If Account Reconciliation is not completed then tool terminates stating that "Account Recon not done". The QC status can be checked whether the tool has run the report correctly. If the reports are ran correctly then it marks as "QC Passed" else the tool marks "QC Failed"
-
The official meeting with team members and management was conducted on Apr'9th 2018
-
The Project has Gone live after getting approval with IT on Aug'22nd 2018
-
It avoids using Microsoft Access
-
Proceeds only if the Account Reconciliation is complete
-
Provides an alternative method to run all the CFR EPP reports
-
It saves time and increases the quality