Objective : Vrinda Store wants to create an annual sales report for 2022. So that, Vrinda can understand their customers and grow more sales in 2023 and next. Tools Used : MS EXCEL KPI's : *Comparing the sales and orders using single chart *Which month got the highest sales and orders. *who purchased more - men or women in 2022. *What are different order status in 2022. *List top 10 states contributing to the sales. *Relation between age and gender based on numbers. *Which channel is contributing to maximum sales. *Highest selling category... etc.
Operations performed on task are :
- Applying the filters and finding out the blanks values, excluding the double spelt data , correcting the data etc.
- Extracting the month from the data using TEXT fucntion.
- Created new column( Age Group) using age column by Nested IF's function. sorted by below criteria 0-24 = "Teenager", 25 - 49 = "Adult", >50 = "Senior"
- Created pivot tables by means wanted KPI's
- Created pivoted charts by the pivot table
- Inserting the slicers required
- creating the format in to millions to make it more readable.
- beautifying the pivot charts
- Charts used are = "Pie chart" , "Donut chart", "Combo- Clustered column line chart",
"Horizontal Bar chart", "Column Chart".
- Created Dynamic dashboard using all these pivot charts
- Created three slicers and referencing them with the required charts in dashbaord
- very interactive insights
Sample Insights :
-
women are more likely to buy compared to men (~65%)
-
Maharashtra, Karnataka and Uttar Pradesh are the top 3
-
Adult age Group(25-49) is max Contributing (~50%)
-
Amazon, Flipkart and Myntra channels are max contributed.
Final Conclusion to improve Vrinda store sales
Target Women customers of age group (25-49)yrs living in maharashtra, karnataka and uttar pradesh by showing ads/ offers/ coupons available on Amazon , Flipkart and myntra.
-