Data analysis and reporting
Case study
Data capture and reporting
Retail distribution industry
Situation analysis
A large distribution company had to produce a presentation on sales and store survey data on a weekly basis. This data reported on 60+ products across 600+ stores. This presented some problems:
- The client’s MS Excel workbook was cumbersome due to the amount of data
- Time loss due to slow opening, calculating and PC freezes
- Data had to be added daily – adding too much at once caused crashes
- Manual consolidation was required due to data being received in various formats and layouts
- The data processing required was increasing at an unsustainable rate, as a result of new data being added periodically
Solution
Our solution was to build an MS Excel tool that uses Power Query to handle:
- Data manipulation;
- Data cleaning;
- Data consolidation;
- Outputting the clean data to the tool for analysis and reporting
This allows the user to simply save the required data in a specific folder structure and click a button to update the data pulled into the tool.
7
hours saved per report
95%
decrease in time taken to open and calculate dashboard tool
5x
increase in result accuracy
Outcomes
Our solution has the following outcomes:
- Time spent importing data significantly reduced due to the heavy lifting of the data preparation being done by Power Query
- Reporting Tool much smaller in size as the data is kept externally
- Potential for human error vastly reduced
- Reliable results every time
- Tool updates in a matter of minutes
- Reporting inaccuracies reduced due to automated data cleaning and dirty data identification
We engaged Saul from GoalSeek to help us design and automate a complex manufacturing costing sheet and customer price list. After a few short meetings with Saul, he had an excellent understanding of the task at hand and provided us with a concise and clever solution. At each stage of the project, he made sure that his design was in line with our requirements. He is very hands on and the regular follow up meetings we had, ensured that I was happy with the end result . His enthusiasm and quick mind resulted in a superb costing and automated customer pricelist. I found him to be very professional and a pleasure to work with.