Simple Dashboard Development
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.
Reporting Suite - Bespoke Excel Based Planning Tool
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.
Custom Built Monthly Reporting Processes
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.
Case Studies
Our Microsoft Excel Case Studies show you how you can create tools and analysis, each truly bespoke with very few limitations.
Simple Dashboard Development
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.
Reporting Suite - Bespoke Excel Based Planning Tool
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.
Custom Built Monthly Reporting Processes
The client, a national building materials manufacturer, asked us to build a number of monthly reporting processes, cutting down the 80-hour processing time to just 1.5 hours.
Our Testimonials
Michael did a superb job for our company. A very complicated “smart” Excel-based order form for our clients to translate their input into 13 languages and output the information in various formats for us to use directly in the manufacturing process.
Michael understood what we needed and literally just got on creating this for us. The result is an Excel form that saves our clients days of work translating their data and we get organised data we can trust. Very efficient. Very quick turnaround.
Andrew Fairclough, Company Owner
Really pleased to recommend Michael as his work on our billing engine has been a revelation. If you need an expert, especially on Excel you will be in safe hands with Mr May.
Steven Barrett, Managing Director Portel AV Limited
He made my data useful. I had two spreadsheets with data and I needed them matching and Michael sorted this for me in around 10 minutes! This meant I could then use the data in my business to help Drive performance. It would have taken me hours trying to cross read the data sets. Great time saver.
Sarah Bradley, The HR Dept Leeds
Michael is an excellent value add Excel expert. He takes the time to define the problem and the scope of work very thoroughly and brings creative solutions to several business problems we’ve brought before him. Each time he’s saved us hours of manual work and it’s so worth it. Highly recommend his work.
Matthew Kee, Tundra Angels
I engaged Michael for a bespoke Excel automation requirement. Michael responded very quickly and professionally, and we engaged a few days later. Michael was also very flexible when the macro failed on a Mac, he kindly remote controlled to troubleshoot and came up with a solution. He’s a true professional, very reliable and I wouldn’t hesitate to recommend him in his field of expertise.
Nicholas Stapley, Citrix Virtualization Architect
At our initial meeting, Michael quickly grasped the concept of what we were after, even though it related to a product that he didn’t know existed!
He skilfully created a bespoke programme in Microsoft Excel that has enabled us to generate our Sales Quotations in a visually pleasing and professional format. Throughout the development process, he continually came up with new and inventive ways to implement our varied requests, no matter how complex.
Any updates to the templates that we require are carried out quickly & efficiently. We will be continuing, without hesitation, to use his services in the future as our UK sales business continues to grow.
Simon Laffoley, Stertil Koni
Contact
Discuss your requirements today
Start your project with us
+44 (0) 7957 266984