Signing Process Optimization

Task

Since our team had access to the data files, it was asked to develop macros to automate the process and deliver the data to the external vendor in the format they required to print the store signs.

Action

Having access to the historical pricing (Checkerboard) files, the process was re-designed to have internal users to run the code and provide the CSV files to the vendor. Additionally, it was noticed that templates were very different across divisions, thus a master macro was created in order to have consistency in the process in all divisions.

An Excel application file to produce Excel files for sign printing was created. It opened Excel files called PTS (Plan to Sell), copied and pasted the data into tables and formatted it as per the external vendor requirements. 

As an additional measure to avoid discrepancies, a master Excel template was designed. It would consolidate data not only from store signing files, but also from PTS, PLU (Price Lookup) and the Checkerboards.               

The Master template, called "The Hub", would flag any discrepancies between all data sources and generate reports to follow-up with the buyers in order to make the necessary corrections.  

The data sources being audited were the main databases containing the pricing information pertaining all sides of business:
1) PLU (Price Lookup; CSV files being uploaded to the AS400 and POS, Point of Sale, with price changes)
2) PTS (Plan to Sell, an Excel spreadsheet created by buyers to map their promotions within a timeframe)
3) Checkerboards
4) CopynotesCSV files given to an external vendor to print store signs

Results

The re-design and automation of the original process resulted in the successful delivery of the data required by the vendor in a timely manner minimal manual user interaction and 100% data accuracy.

Technical Data