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.
Here’s how I approached each step of the project in Excel
1. Data Cleaning I started by cleaning the raw data to ensure it was accurate and consistent.
Removed duplicate entries to avoid skewed results. Fixed missing values by either filling them with averages or removing irrelevant rows. Standardized text data (like state names and gender) to maintain consistency. Reformatted dates to a uniform DD-MM-YYYY format for better analysis. Deleted unnecessary columns that were not relevant to the analysis.
2. Data Processing After cleaning, I processed the data to make it analysis-ready:
Combined data from multiple sheets into a single table for easy access. Used formulas like CONCATENATE, TRIM, and UPPER to organize and format text fields. Created calculated columns for metrics like total sales, profit margin, and order frequency. Used filters and sorting to organize data based on regions, age groups, and sales channels.
3. Data Analysis To extract insights, I analyzed the processed data:
Created pivot tables to summarize sales by region, gender, and age group. Used formulas like SUMIF and COUNTIF to calculate key metrics, such as total sales in specific states. Applied conditional formatting to highlight the top-performing channels and demographics. Analyzed trends to identify the highest contributing age group and states for sales.
4. Data Visualization Once the analysis was done, I visualized the results to make them easier to understand:
Designed bar charts and pie charts to showcase the contribution of different demographics. Created a line graph to visualize sales trends over time. Built an interactive dashboard with slicers to filter data by region, age group, and sales channel. Used color-coded charts to emphasize key insights, such as top-performing states and channels.
5. Reporting Finally, I compiled everything into a professional report:
Summarized all insights and highlighted actionable points in a dedicated sheet. Included an introduction explaining the project’s objectives and methods. Added annotations to graphs and charts to provide context for the visualizations. Ensured the report was well-formatted with consistent fonts, colors, and alignment.
The sample insights are:
Women are more likely to buy compared to men (~65%). Maharashtra, Karnataka, and Uttar Pradesh are the top 3 states (~35%). Adult age group (30–49 years) is the maximum contributing (~50%). Amazon, Flipkart, and Myntra channels are the maximum contributing (~80%).
Final Conclusion to improve Vrinda store sales:
Target women customers of age group (30–49 years) living in Maharashtra, Karnataka, and Uttar Pradesh by showing ads/offers/coupons available on Amazon, Flipkart, and Myntra.