The goal of this project is to create a dashboard of annual sales data for a fictitious store called Vrinda Store for the year 2022. This dashboard aims to provide a visually appealing and intuitive interface to easily interpret key metrics and get new insights about the business in order to make data-driven decisions for improving sales for 2023
Disclaimer : All datasets and reports do not represent any institution comapny or country and is just a dummy dataset to demonstrate Excel capabilities.
- Data Cleaning, Data Transformation, Data Analysis
- Filtering, Sorting and Conditional function.
- Pivot Tables and Pivot Chart
- Dashboard creation
This project wasn't just about crunching numbers; it was about unraveling insights that could drive growth. 📈 I delved into a plethora of business questions:
📈 Comparative Sales and Orders Analysis: Visualizing the fascinating connection between sales and orders in a single chart.
📆 Highest Sales Month: Uncovering the powerhouse month with the highest sales and orders – a pivotal discovery for strategic planning.
🚻 Gender-Based Analysis: Delving into purchasing patterns to reveal whether men or women were the bigger shoppers in 2022.
📊 Order Status Breakdown: An in-depth analysis showcasing the distribution of different order statuses throughout the year.
🏢 Top Sales Contributing States: Highlighting the top 10 states that played a significant role in driving sales growth.
👥 Age and Gender Relation: Unraveling the intriguing relationship between age, gender, and the number of orders – unlocking hidden trends.
🌐 Channel Contribution to Sales: Assessing the impact of diverse sales channels on the overall revenue – crucial for channel optimization.
🏆 Highest Selling Category: Identifying the winning category that emerged as the champion in terms of sales.
The dataset was provided by Rishabh Mishra . I am greatful for his guidance throughout this project.
This stage began with getting to know the dataset and checking for any data quality issues.
- Checking NULL values, blanks and Errors
- Removing duplicates, and irrelevant columns.
- Made sure data is consistent and clean with respect to data type, data format and values used.
- Creating 2 new attributes 'Age Group' and 'Month' were added for logical and easy interpretation of dataset.
Six pivot tables were created to summarise the data and help identify trends in the dataset focusing on relationship between sales and other factors such as gender, orders in diff months, top states. Trends in order status, orders on diff channels orders and age comparison were also performed.
Finally, the dashboard was created by inserting and customizing the pivot charts of corresponding pivot table. For user friendly and interactive experience 3 "Slicers" were added.
Below is a snippet of the final dashboard in Excel.
Key insights from the analysis: 📅 Peak Months: February and March witness the highest orders and sales activity.
🚺 Gender Trends: Women lead the charge, accounting for approximately 64% of total purchases.
🏞️ Top States: Maharashtra, Karnataka, and Uttar Pradesh shine as the top contributing states, making up around 35% of sales.
👩💼 Age Matters: The adult age group (31-50 yrs) takes the lead, contributing a significant ~50% to the sales figures.
🛍️ Dominant Channels: Amazon, Flipkart, and Myntra stand out as the major players, contributing to a substantial ~80% of the overall sales.
📦 Order Status Breakdown: An impressive 92% of orders boast a "delivered" status, reflecting effective fulfillment processes.
To boost sales in the future:
🎯 Targeted Approach: Based on our data analysis, there's a golden opportunity to engage women aged 30-49 in Maharashtra, Karnataka, and Uttar Pradesh. 🚺 With this segment driving a significant portion of sales, tailoring marketing efforts could unlock remarkable growth potential.
📱 Online Strategy: Harnessing the power of online platforms, particularly Amazon, Flipkart, and Myntra, will be a game-changer. 🛒💻 Utilizing these channels for targeted ads and promotions can maximize reach and customer engagement.
If you would like to explore the detailed analysis you can access the files. Thank you for your interest and time. Feel free to give your valuable suggestions and connect with me on https://www.linkedin.com/in/harshitt-gahlaut/