Unveiling Insights from Global Superstore Data Using Excel
Introduction
In the realm of data analysis, extracting meaningful insights from raw datasets is a crucial task. Myself and my Project partner Oluwatobiloba Adewusi we both delve into the process of exploring and analyzing the Global Superstore dataset using the Excel tool, with the ultimate goal of uncovering valuable information that can guide business decisions.
Data Journey with Global Superstore
Loading and Initial Cleanup : The process initiated with the loading of the dataset using the powerful Power Query in Excel. This set the stage for subsequent transformations.
Refinement and Granulation: Redundancies were eliminated by removing the source name column. Addressing data integrity, null values in the postal code column were replaced with zeros. The customer name column was duplicated, allowing us to extract first and last names through a split delimiter process.
Enhanced Customer Information: Resulting from the initial step, two new columns — ‘first name’ and ‘last name’ — were created and strategically positioned beside the original customer name, enhancing the dataset’s granularity.
Custom Column Creation for Enhanced Metrics: The analytical power of Excel came to the forefront with the creation of custom columns. ‘Delivery days’ were calculated by subtracting the order date from the ship date. Additional columns for ‘actual cost’ and ‘price per quantity’ were added, providing deeper financial insights.
Integration and Loading: The transformed dataset was seamlessly integrated into Excel, ensuring that the modifications made in Power Query were incorporated for further analysis.
Pivot Table Insertion: The Excel journey concluded with the insertion of a pivot table, facilitating organized and insightful data visualization.
Key Insights Unveiled:
- Product Categories and Sales Distribution: The Global SuperStore dataset highlights three primary product categories — Furniture, Office Supplies, and Technology. Notably, the Technology category emerges as the sales leader, contributing 4.7 million, while Furniture and Office Supplies follow closely with 4.1 million and 3.8 million, respectively.
2. Sub-Product Categories Analysis: Delving deeper into sub-product categories, Phones and Labels stand out with the highest and lowest sales, respectively. Profitability nuances surface, with Copiers leading in profit generation and the ‘Tables’ category incurring a loss.
3. Delivery Mode Analysis: A detailed analysis of delivery modes reveals distinct timelines for different modes. ‘Same day’ and first-class delivery modes exhibit swift timelines of 24 hours and 1–3 days, respectively, while standard class takes a maximum of 7 days for delivery.
4. Market and Region Analysis: The ‘APAC’ market emerges as the leader in both sales and profit, encompassing Central Asia, North Asia, Oceania, and Southeast Asia. Further insights into profit margins indicate that Canada has the highest profit margin among markets and regions, suggesting potential for increased investment.
Recommendations:
- Discontinuation of ‘Tables’ Sub-Product Category: Given its consistent losses and low profit margin, discontinuing the ‘Tables’ sub-product category is recommended for overall financial health.
2. Strategic Investment in ‘Paper and Labels’: Capitalizing on the high profit margins recorded by the ‘Paper’ and ‘Labels’ sub-categories (24% and 20% respectively) could significantly boost overall profitability.
3. Focus on High-Margin Products: To enhance profitability, the company should prioritize products with profit margins exceeding the global average (12%), as indicated by the forecast projecting a 4% increase in total margin.
Conclusion: The synergy between Kaggle.com’s Global Superstore dataset and Excel’s analytical prowess has unearthed invaluable insights. By acting on these findings and implementing strategic recommendations, businesses can optimize product offerings, streamline logistics, and strategically invest in lucrative markets, ultimately enhancing overall performance in a competitive landscape.