Our client, an electronics distributor, was processing files received by their vendors almost daily through primarily a manual process. There were a couple of macros built to help put each vendor file in the correct column order, but otherwise there was a lot of copying and pasting to get the end result they needed. In addition, they had to match this data to their existing internal data. This process took approximately 15-20 minutes each day for someone to run. This process also did not take into account any overlap between the internal and vendor files for part numbers, etc. Therefore inventories were appearing on ecommerce websites with duplicate entries. Finally, a missing piece that the owner wanted to incorporate was adding more pricing to the ecommerce sites from the vendor and internal information they had. This was more complex and was not part of their existing daily process.
DB Pros was brought in to help automate the process of compiling the vendor and internal data, as well as expand the scope to include combining the inventory figures per part, and add pricing. After a detailed needs assessment to understand the true scope of the work, and analyze the files we would be working with, we set up an automation process in Excel using VBA scripting in a macro. This macro took into account all of the key steps being done prior, as well as summing the inventories where part numbers matched, and has been set up to accept the pricing information in the near future.
This process took the daily steps from 15-20 minutes down to 3-5 minutes (including the processing time. Human time is approximately 30-60 seconds). This equates to a 5-hour time savings in a typical month! In addition to the time savings we’ve provided them, there is also an accuracy factor that was achieved – there is no longer the possibility of human error by copying and pasting the wrong information (or pasting it in twice or missing it for a given file). And we were able to add functionality that would have otherwise been either too complex to achieve manually or would have caused more areas for human error to occur.
Through our understanding of data structures and the needs of our client, DB Pros was able to take a manual process and successfully automate it through Excel programming. We were able to take their data processing to the next level, which the business owner was trying to achieve, and reduce the amount of time it took to process the daily files with a higher rate of accuracy (by eliminating the possibility of human error). This makes the client more productive and provides their clients with a higher level of information to make purchase decisions, which will hopefully in turn bring more business to them.