This process allowed the business unit to identify and process product to be returned to the Vendor, shipped to special locations, shipped to the corporate office or destroyed locally.
The data was used to update accounts payable to balance the various accounts for the business units.
Excel was used to create the updated form for the business units to enter the necessary data that would allow the Distribution Centers to process the request.
Teradata was used to connect to the Excel spreadsheet that allowed the user to select the specific division to process the return. The data would be isolated to the specific division so that the spreadsheet would not expand to an unusable size.
Front page data was paste linked into hidden tabs so that is was transformed into importable formats.
A request was sent via email to a community mail box. Macros in Outlook were used to extract the Excel attachment and move it to a folder on a local drive.
An Access database would then loop through the Excel files in the folder and import the formatted tab into the master table.
Data was analyzed to create reporting by Distribution Center, by Vendor and by Business Unit Analyst.
Reports were created by Distribution Center. These reports were created in a PDF format, exported to folders for each Distribution Center. The database would create an email for each Distribution Center, search the folder and attach a cover page and the PDF files which informed the Distribution Center which products to process.
Records were updated to an Excel tracking spreadsheet populated by the Distribution Center. The data was imported daily into the database. The information updated by the Distribution Center included the actual amount of product shipped, shipment method, tracking information, who processed the return and the date it was completed, date sent to Transportation to schedule the pickup, date shipped from the Distribution Center.
This data was compiled to create reporting which was sent to Accounts Payable monthly so that the charges could be reported on the Business Units ledgers.
Accounts Payable utilized weekly reporting to identify errors in the Distribution Center reporting. This allowed corrections to be made prior to monthly reports being crated.
Year-end reporting by product division, product category and by Distribution Center was published at the close of the fiscal year.
I created a search application which pulled all the data from the eight different Distribution Centers tracking spreadsheets into one database with multiple search options.