Goals:
Identify top suppliers and brands from various categories for this fictitious company in 2023.
Calculate year-over-year growth to see how sales have changed over time
Calculate market shares
Calculate year-to-date and moving annual total profit figures
Outcome: Quick Pivot Tables for an ad-hoc report.
The raw data set is a simple, fake datasetⁱ for a gym equipment sales company.
ⁱDataset was provided by Mo Chen.
Starting with the raw data, I ran a quick data profile using PowerQuery: Table.Profile(#"Table1"). A review of the profile shows there are 9 different brands, 3 different categories, and 3 different suppliers. There is no anomalies in the number of months in a year and there is data for years 2018 - 2024.
Then I created a pivot table to show the 3 equipment categories: airbike, treadmill, rowing machine. Then listed the suppliers one level below and the brands for the last level. The data is showing the sum of the profit. The pivot table is filtered to only show values for 2023 and sorted from largest to smallest profit by supplier.
We can easily see that the profit for air bikes is 100k+ above profits for treadmills and rowing machines. Park Performance Gear supplier accounts for over 50% of the equipment and all their brands are generating more profit than the other brands. Is this worth looking into in depth? Maybe, but it's currently out of the project scope.
To calculate year-over-year (YoY) growth, I created a new pivot table. Grouping by Supplier and Brand, we're calculating the % difference from the previous year. Since the data begins in 2018, we have the YoY% difference from 2019 to 2024. I added in some conditional formatting to help visualize the variances.
The data for 2024 was removed from this final pivot table because we only had 5 months of data for 2024 and it was showing inaccurate comparisons.
Looking into the market share of each brand by category, each supplier's brand is close--typically within 1.5% of each other.
Moving Annual Total (MAT) is the rolling total for the past 12 months. This pivot table shows the MAT as of May 2024, and can be changed using the filters.