Vrinda Store Analysis
Using Microsoft Excel
In this project, we analyze the Vrinda store data to create an annual sales report for 2022. So that, Vrinda can understand their customers and grow more sales in 2023.
ABOUT DATA
The Product Category column contains 8 different categories of items sold by Vrinda Store, including Kurta, Set, Western Dress, Top, Saree, Blouse, Ethnic Dress, and Bottom. The Sales Channel column contains 7 different channels used by Vrinda Store to sell their products, including Myntra, Ajio, Amazon, Flipkart, Meesho, Nalli, and Others.
The data also includes shipping details such as City and State, with 1860 different cities and 36 different states listed. This information could be used to analyze Vrinda Store's sales patterns and customer demographics, as well as identify which products and sales channels are the most popular. It could also help in identifying areas where the store could potentially expand its operations or improve its shipping logistics.
DATA CLEANING
Find null and blank cell in each column
Check data consistency in column gender and quntity
Step by step data cleaning process - Watch following Video.
DATA PROCESSING
Create a new column as Age_Group by using the Age column and nested If formula
Similarly, create new column months by using the date column and TEXT formula
Important Tips :
If we have a lot of data and we create new columns by using different formulas, then excel will work slowly so we can copy, and use paste special function and paste as only value in original column.
DATA ANALYSIS
Order vs Sales :
Create pivot table
Add Months, Amount, Order_Id in the pivot table
Go to the insert tab and click on the pivot chart option
Next, select the clustered column-line chart and use the secondary axis option
Orders - Channels :
Create pivot table
Add Channel, Order_Id in the pivot table
Go to the insert tab and click on the pivot chart option
Next, select the column chart and edit it.
Purchase- men vs Women :
Create pivot table
Add Gender, Amount in the pivot table
Go to the insert tab and click on the pivot chart option
Next, select Pie chart and edit it.
Order Status :
Create pivot table
Add status, Order_Id in the pivot table
Go to the insert tab and click on the pivot chart option
Next, select the Pie chart and edit it.
Top 5 Sales State :
Create pivot table
Add Ship_State, Amount in the pivot table
Go to the insert tab and click on the pivot chart option
Next, select the Horizontal Bar chart and edit it.
Highest Selling Category :
Create pivot table
Add Category, Amount, Order_Id in the pivot table
Go to the insert tab and click on the pivot chart option
Next, select the clustered column-line chart and use the secondary axis option
Orders- Age vs Gender :
Create pivot table
Add Age_Group, Amount in the pivot table
Go to the insert tab and click on the pivot chart option
Next, select the clustered column chart and go to the format chart area and add percentages.
Vrinda Store Data Analysis Video
REPORT
Create a new sheet for the report section.
Copy and paste all pivot charts from other sheets to the report sheet.
Add slicers Months, Channel, Category, and Connect to all pivot charts.
Arrange all pivot charts in a suitable format.
Vrinda Store Workbook Here Click Please to view.
INSIGHTS
Top sales in march month.
The women's purchase rate is high.
The delivered Status is 92%.
The top 3 states are Maharashtra, Karnataka, and Uttar Pradesh.
The top purchased category is "Set".
Most sales in women, in each category.
Maximum sales by amazon are 35%.
CONCLUSION
Target women customers of age group (30-49 yrs) living in Maharashtra, Karnataka and Uttar Pradesh by showing ads/offers/coupons available on Amazon, Flipkart and Myntra.
Vrinda Store Analysis PPT
In this PPT, we analyze the Vrinda store data annual sales report for 2022.
