Turning a year of scattered supplier sales files into one automated summary

The challenge

The client received a steady stream of Excel files from each of its suppliers throughout the year, with every file detailing the sales that supplier had made to each golf club or member over the course of a quarter. Across all suppliers and all quarters, this added up to a large number of separate files landing throughout the year.

None of these files were in a consistent, ready-to-analyse format on their own. To understand how the business was actually performing, someone needed to bring every supplier's figures together and summarise them across the entire financial year, comparing sales by supplier, by club or member, and by month and quarter.

On top of the annual summary, The client also needed to compare results from one financial year to the next. That meant taking the same quarterly breakdowns, by supplier, by member, or both, and showing the movement between the same quarter in consecutive years, not just the raw totals.

Doing this by hand meant manually opening, checking and combining a large number of supplier files every year, then repeating a similar manual effort again just to compare two years' worth of already-summarised results.

Our approach

1. 01

   Automating the single-year summary

   We built an Excel workbook using Power Query to automatically bring in all of the raw supplier sales files for a financial year, rather than requiring each one to be opened and copied in by hand. As new files were added, the workbook picked them up on refresh.

2. 02

   Categorising and structuring the combined data

   Once the raw files were pulled in, we used Power Query to clean and categorise the combined data consistently, so every supplier's figures lined up the same way regardless of how the original file had been laid out.

3. 03

   Building the summary views with Power Pivot

   We used Power Pivot to build the summarised results the client needed, allowing sales to be compared and viewed by supplier, by club or member, and across every month and quarter of the financial year, all from the one workbook.

4. 04

   Building the year-on-year comparison workbook

   We built a second Excel workbook that automatically extracts the results from two consecutive financial year summary files, rather than requiring the figures to be re-entered or copied across manually.

5. 05

   Showing the quarter-on-quarter movement

   This second workbook reproduces the same summary breakdowns as the original files, by supplier, by member, or both, while also calculating and displaying the difference for each quarter compared to the same quarter the previous financial year.

The outcome

The clent can now bring in every supplier's raw sales files and get a fully categorised, summarised view of the financial year without manually combining a single file by hand.

Sales can be compared by supplier, by club or member, and across every month and quarter, giving a clear picture of performance that would previously have taken considerable manual effort to piece together.

Comparing one financial year against the next is now handled automatically as well, with quarter-on-quarter movement calculated directly from the two summary files rather than reworked from scratch each time a year-on-year comparison was needed.