Account Reconciliation
Nov'12th 2018 - Dec'17th 2018
VWR International LLC
Project Charter
PROBLEM STATEMENT
At the end of every month, Analyst needs to reconcile every GKA’s for which they are doing Scheduled/ Ad-hoc reporting. Each step involves manual intervention from the user and user needs to download data from QlikView.
OBJECTIVE
Primary Objective:
-
Eliminate complete manual steps involved which is time consuming process
-
Eliminate manual download from QlikView
Secondary Objective
-
Need to Backup DB for restoring and need to maintain an updated Sold To-Master DB.
-
To save time taken to process from 4320 Min to 60 Min by implementing automation
-
Follow all the steps involved without manual intervention.
-
Needs to leave the Decision-making step to the Analyst.
-
To propose an alternate method that reduces the rework rate.
EXPECTED DELIVERABLES
Implementing Automation, will reduce the manual steps invloved in Account Reconciliation, through
-
Poka-yoke,
-
Without using Microsoft Access,
-
Set up Sold To Master Database.
-
Create an Exception list,
-
Extracts data automatically from QlikView,
-
Backup Master database,
-
Run all Account Reconciliation Steps from 1 to 4 without manual intervention,
-
Without adding additional resources,
-
Decrease time,
-
Lower human fault,
If achieved, the the Account Reconciliation process could be produced with greater quality in very short time.
PROJECT SCOPE- IN SCOPE
In Scope:
QlikView data extraction, Quality Management, Customer Service Satisfaction, Use of Automated tool, Time Management, Associates who run Monthly Account Reconciliation process, New and updated Sold To Master DB, Backing up database, Exception list
Out Scope:
Emailing the final accounts list to recipient, Can only be used for Monthly Account Reconciliation, Decision making step
Work Break down Structure
The Tool has been created on Microsoft Excel VBA and incorporates the codes that interacts with QlikView application to extract data. It needs to help the user to find any new Accounts tagged under the Existing GKA without notice, any new relative Accounts that needs to be tagged under GKA-999. This also needs to help in finding CCC Text – SO & SH GKA mismatch alignment. Needs to create files for validating and updating Customer Business Area fields (1 – 5) and then create a Backup DB every month. Leaves the Decision-making step to the Analyst. He needs to review each output files based on the status provided by the tool and send the emails to respective Business Owners or the Customer Master Data team to make the respective account changes.
The Tool has been created and it performs good. The Tool is designed to run all the Account Reconciliation steps 1, 2, 3 & 4 for the scheduled GKA’s. The user needs to open the template and click run. It extracts data from Qlikview and It splits the files as per the customer name and saves them in respective step folder. It also creates a Backup db for every month. It now avoids complete manual steps involved. The user now can make the decision by reaching out to the business owners and confirming whether to make changes to accounts or not.
-
The official meeting with team members and management was conducted on Nov'12th 2018
-
The Project has Gone live after getting approval with IT on Dec'17th 2018
-
It can extract data from QlikView without manual intervention
-
Backs up the raw data for future reference
-
Runs all the Account Reconciliation process steps 1 to 4 on a single for the entire accounts
-
Leaves the decision making step to the Analyst
-
It saves time and increases the quality