Rousseau Associates were asked to develop an Excel-based dashboard to report on a sales and credit control function within a high-volume sales business. The client didn’t have a process in place so the build was based on a very brief requirement of needing a live dashboard for their sales and credit control staff to view on an overhead screen.

The build required Excel to query their database to get up to date data every 5 minutes and to show a number of measures.

The initial step to the build was to see the data that was being queried to ensure that it contained everything required to produce the required dashboard elements.

Once the missing data had been added to the query the next step was to understand data fields were needed for each element of the dashboard and to ensure that there was no duplication of the data analysis causing potential lags when updating the dashboard. Where elements of the dashboard could utilise already established data analysis, this was utilised which reduced the processing time to display the output.

When the outputs had been created, the VBA module was utilised remove the row labels, column headers, function ribbons, worksheet labels, scroll bars and formula bar which enabled the outputs to utilise the full screen. Further VBA code was implemented to give the user the functionality to update the data if needed outside of the five-minute automatic updates and to close the tool down.

The client required a number of Excel-based developments to maintain and report on a long-term project they were running for a UK based supermarket chain.

The initial requirement was to review an Excel-based tool to record activities in relation to store-based marketing. This included organising activities, samples, competitions, leaflet distribution on a store by store basis.

The tool in place was implemented on a Macintosh platform using an old version of Excel/Office which regularly experienced errors and was built using over-complicated Excel and VBA functionality. This required a full understanding of the flow of the Mac-based build and the VBA code to execute the functionality required to maintain a store plan and to then deploy onto a Windows platform.

Once the Excel functions and VBA code had been translated to a Windows platform and all the errors had been resolved, the tool was deployed to the team and as the requirement wasn’t to perform a complete rebuild, there was a requirement to be available for any further issues that were experienced due to the majority of the initial build being kept in place.

A number of new requirements were identified once the Store Plan tool was in place and required reporting of various aspects of each store plan.

These reports included

• Weekly Costs Report – This required all the live store plans to be queried to pull in the relevant costs for both internal reporting and reporting to their client.

 

• Store Dashboard Report – This allowed the user to select a store plan and the process retrieved a number of measures and values from the selected store plan and then linked the values to a PowerPoint template file which was populated with the linked data. The user then was required to select a number of images (photos were taken on-site) to embed in the PowerPoint output.
  
• Store Packing List Report – For each store being visited a packing list was required which included mandatory items (leaflets, stationery, etc) and items for the selected activities on-site such as food sample ingredients, roller banners, etc. This then created an output to be sent to the relevant department details items codes needed to pick the required items.
• Global Summary Report – The requirement was to query every live store plan and establish of any plans has been created and were missing required information (store ID, contact, etc) and created an exception report that was sent out to the team detailing the issues for each plan. The report also created a summary of the total quantities needed, firstly for each item type and then by individual item.

During the build for each of the reports, consideration had to be given to the fact that store plans created prior to the initial error fixes, were not redone due to the amount of time required to do this so the reporting tools needed to be able to deal with errors in these files and locate required data from different areas of the store plan.

Rousseau Associates were approached by the Commercial Director of a national building materials manufacturer to discuss a number of potential projects.

 

The initial project was to recreate a monthly reporting process that was taking 80 hours a month to complete. The process required the user to manually manipulate 10 individual datasets some of which contained over 10,000 rows of data in preparation for usage and then create approximately 350 individual outputs to .pdf to be packaged up into approximately 10 reports for each member of the sales team.

 

The reports being created needed to report based on a management structure where the heads of regions received some summary reports based on their teams where the individual team members only received a report on their own performance. Each individual also required a front page and report index/contents to be created which needed an image of the individual, a list of the reports in their pack and different colours used dependent on their position in the business.

 

Due to the volume of raw data being utilised in some of the reports, the Power Query and Power Pivot add-ins were utilised. This massively reduced the amount of time required for Excel to ‘prepare’ the raw data.

The final automated build enabled the user to

• Dynamically create a file for the period
• Import and prepare the required data
• Create each report for all individuals at the click of a button
• Re-run a report if the raw data needed to be refreshed
• Increase the number of individuals requiring reports from 40 to 80+

The final build now enables a user to create all the reports in approximately 1.5 hours compared to the original 80 hours.