As a member of our consulting team I thoroughly enjoy analyzing POS Data, conducting warehouse inventory forecasts and helping implement store replenishment policies. To use a football term, data has always been the “12th man” in our office and we enjoy working with it no matter the form or function.
Sometimes our consulting team is presented with the opportunity to help improve business processes and better streamline data compilation efforts. This was the case during a recent project with a home textiles manufacturer who was looking to take a procedure that had (over the course of years of use) become a Frankenstein monster of broken formulas and bolted on features.
Our client had started from an internally developed Excel spreadsheet used for analyzing the profitability of incoming orders. The users in the company were faced with a number of limitations in this format:
- Static Formulas only supported 6 SKUs at a time for each item type
- Some users would manually key over Formulas, creating discrepancies further down the road.
- Data structures made an upload to an ERP system nearly impossible.
- Each user had their own method when adding new product categories or items.
As a part of our initial analysis of the project we saw that the original format was sound, but could be greatly optimizing by using Excel’s built-in scripting interface (VBA).
Utilizing VBA and our Excel skills our team was able to create an automated modular format that helped implement many new functions including the ability to:
- Dynamically create new slots for additional SKUs.
- Dynamically add sales royalties (without manually looking up each royalty fee).
- “Lock down” the formulas so non-power users could not key over formulas.
- Generate a flat file that would easily upload into an ERP system.
- Automatically generate a formal sales invoice to send to the customer.
- Back into a sale price based on a warehouse margin goal .
- Project the warehouse margin based on a proposed retail price and cost.
We were able to condense complex features and processes into a few buttons that sit on the first tab of their report, allowing users to stay in the Excel environment they are familiar with while also significantly upgrading the flexibility and accuracy of the tool.
Our client has been especially pleased because it means this tool is no longer bound to the current users, instead any of their employees can use this new and improved format: from those at corporate headquarters to local users at their manufacturing and warehouse facilities.
While our consulting team at Enhanced Retail Solutions continues to extend our expertise in POS and demand planning data analysis and reporting, we also continue to take a comprehensive approach in improving our client’s bottom line through specialized projects in other of areas where our clients have needs. Whether it is in analyzing MABD compliance, Sales Performance across multiple retailers or factory compliance reporting, we’re always happy to jump in, assess needs and inefficiencies and then turn around results through improved reporting and improved analysis tools.