Created a dynamic and interactive Excel Dashboard for the "Complete Corner" Store's Sales Annual Report 2023. The dashboard showcases the most important information and various KPIs, including: Orders vs. Sales by month, Sales by gender, Order status (delivered, refunded, canceled), Top 5 states by sales, Orders by age and gender, Orders by channel, Sales by product category (Kurta, Blouse, Bottom, etc.),
The Dashboard utilizes pivot tables and pivot charts to allow users to filter and interact with the data to gain insights into sales trends and performance.
The dataset comprises information from the entire corner store, with column headers including Index Order ID, Customer ID, Gender, Age, Age Group, Date, Month, Status, Channel, and SKU. Imported existing data into an Excel file.
- Order Vs sales: By analyzing order trends throughout the year, we can gain valuable insights into customer behavior. Creating a pivot table that summarizes sales and order count by month (January to December) allows us to easily compare activity across different periods. Visualizing this data in a chart helps identify patterns that might be missed in raw numbers. For instance, we might see fluctuating sales throughout the year, with some months experiencing peaks and others seeing dips. Additionally, comparing sales figures with order volume can help determine if there's a correlation between the number of orders placed and the total revenue generated.
- Sales by gender: Let's investigate how sales differ between genders. We can create a pivot table to categorize sales by "Men" and "Women" and calculate the total sales amount for each group. Visualizing this data in a pie chart will provide a clear picture of the sales distribution across genders.
- Order status (delivered, refunded, canceled): Let's delve into order fulfillment! We can create a pivot table to categorize orders by their status - canceled, delivered, refunded, or returned. By counting the number of orders in each category, we can gain insights into the order fulfillment process. Visualizing this data with a pie chart will help us see the distribution of order statuses at a glance.
- Top 5 states by sales: Let's uncover the sales leaders! We can create a pivot table to identify the top 5 states with the highest total sales. This table will display the state names as row labels and their corresponding sales amount. To visualize this data effectively, we can use a bar chart. This will allow us to compare sales figures between these top states and easily see which state reigns supreme.
- Orders by age and gender: Let's explore how age and gender influence buying habits! We can create a pivot table with age groups (Adults, Seniors, Teenagers) as row labels and separate columns for Men and Women. This will categorize orders based on both demographics. To gain insights into buying preferences by age and gender, we can calculate the percentage of sales within each category. Finally, by presenting this data in a bar chart, we can visually compare the sales contribution of each age group for Men and Women.
- Orders by channel: By analyzing sales channels, we can see where customers are most likely to purchase our products. The data reveals that Amazon is the dominant platform, capturing 35% of total orders. Flipkart, Meesho, and other platforms contribute the remaining sales, with 'Others' accounting for only 4%. This suggests that focusing marketing efforts on Amazon could be highly beneficial.
=> Slicers transform pivot tables and charts into interactive playgrounds for data exploration! These visual filters allow users to slice and dice the data on the fly. Simply clicking on a slicer option instantly refines the view, eliminating the need to manually adjust complex filters. This makes slicers a game-changer, especially for massive datasets where uncovering insights from different angles is crucial.
