One of the most common calls we receive from clients sounds something like this: “I have all these spreadsheets and I don’t know what to do with them.” It’s a common phone call because it’s a common problem. In this case study, our client had 39 spreadsheets that represented important details about his sales performance over the last 3 years. Each spreadsheet had 15 individual sheets of data. He receives this data each month from the company he represents in the Texas market.
As you can imagine, trying to analyze sales and commissions data in 39 spreadsheets with 15 sheets each is a formidable task. Our client did what most busy business owners would have done with this data, not a whole lot. He gave us a call to help him brainstorm some options. His goal was to organize this data properly so he could gain key insights about his business and share these insights with his team.
After analyzing the data with our client, we recommended a process where we would push this data each month into a database and then use Power BI to develop visualizations of the data that would clearly indicate the key trends and metrics that were important to his business.
Here is how we worked through this process:
Step #1: Database Design & Build
The database portion of this project was very important because it would allow us to organize and store all the spreadsheet data in a single location. As we normally do, we chose to use a SQL Server Database located on Microsoft Azure. Azure provides a very easy and inexpensive way to host a powerful database in the cloud.
The database was organized and designed to contain all relevant data coming from the spreadsheets each month. Each month was captured separately to allow us to trend the data. The data design was approved by the client and from there we built the tables in preparation for Step #2.
Step #2: Excel Data Conversion & Load
To push the data from Excel to the database, we used a macro enabled workbook. This workbook was set up to allow the client to do the monthly import himself in a very automated fashion. A browse button is clicked to select the monthly Excel file. A dropdown is used to select the month and year of the import. An import button is clicked to convert and import the data to the database.
can be used for many purposes. In this case, the VBA macros open the Excel data file, extract, and reorganize the data into sheets in the macro workbook, and make the necessary SQL calls to write the data to the database. The macros were run for each of the 39 months to complete the initial load and are also run each month to load the current month’s data.
Step #3: Power BI Design & Build
The final step in this project was to design the visualizations that would make the data “speak” and provide the desired insights. Power BI offers many ways to present data. Here are some of the visualizations used in this set of dashboards:
- Sales trends by year
- Sales trends by month vs. same month last year
- Sales and commissions by customer.
- Slicers by product group, territory, sales rep.
- Top sales increases vs. last year by customer.
- Top sales decreases vs. last year by customer.
- Sales by product group, territory, item.
- Rate of Change (ROC) moving averages.
- Fees and adjustments by year.
- Fees and adjustments by month vs. same month last year.
Each of these visualizations provides immediate insights into the business that were previously hidden inside many workbooks and worksheets. These types of insights provide the opportunity to make informed decisions and take decisive action to improve the business.
Summary
In this case study, our client had critical business data buried in many . We worked with the client to turn this difficult to use data into helpful business insights that make a bottom-line impact.
About the author: Brian Busscher is CEO and Founder of Thesis Technologies which pursues a mission to “help you get your systems right.” If you would like to discuss ways to move your business systems forward, please contact the Thesis Team at (888) 705-7253.
Find other posts here.