XYZ supermarket has three stores setup at Bengaluru, Chennai and Hyderabad. They are currently struggling with their business as there is a huge gap between there total sales and income and want to figure how to handle it because of which they need a report that is analyzing the data of all the three stores.
Download the Excel Workbook with all the details and the dashboard.
• Generate a single page dashboard using excel without any slicers that modifies the dashboard.
• Highlight the total sales of all the three stores combined, Quantity of items sold of all the three stores, total income and average unit price of all the three stores combined as primary values at the top of the dashboard.
• Create graphical representation of hourly trends of total orders and daily trend of total orders beside each other.
• Create graphical representation of Bengaluru store data, Chennai store data and Hyderabad store data beside each other.
• Create graphical representation of gender diversity, mode of payment and total sales and income of all the three stores combined.
• Create an insight space for each row of the graphical representation that has been asked and mention the insights derived out of it.
• Expected insights are busiest hour, day, and top selling store for each month. Other expected insights are gender diversity, preferred mode of payment and top selling store for all the three months combined.
• Cleaning the dataset and adding new calculated columns according to the requirements given.
• Prepared a background template on which the dashboard can be presented.
• Mapped KPI’s like Total Sales, Quantity Sold, Total Income and Average Unit Price on the dashboard.
• Set up charts for Hourly and Daily Trends for Total Orders, Store data of all the three stores, Gender Diversity, Modes of Payment and Total Sales and Income using Pivot Table and Pivot Chart.
• Picked Insights such as Busiest Day and Busiest Time, Top Sales Store for each of the three months, Gender and mode of payment preferred and the store with the highest sales overall.
• Insights on the Total Sales, Quantity Sold, Total Income and Average Unit Price out of all the three stores are fetched.
• Hourly Trend and Daily Trends for Total Orders out of all the three stores were fetched which indicated that Saturday is the busiest day and 7 – 8 pm is the busiest time in general among all the three stores combined. The hourly order value and daily order value was also fetched as data labels on the charts.
• The Total Income and Quantity sold of all three stores for the three months individually were plotted to find that the top sales in January, February and March were from Bengaluru, Chennai, Hyderabad Store respectively indicating an almost equally distributed performance.
• Further, Insights on Gender Diversity, Preference on modes of payment and comparison on Total Sales and Income for the three stores fetched individually resulted with diverse customer range, E – Wallet as the most preferred mode of payment and Hyderabad store with the highest total sales for all the three months combined.
• Significant attention has to be put on Sales and income margin as it is significantly low and increasing it will help the business flourish.
I was working on a freelance project for a well-known supermarket which had five branches across south India. The task there was completely different and it was more on analyzing each sales persons performance overall with sales associated with him and his / her performance according to the target. That experience gave me this idea about doing something related to supermarket but different from what I did so I chose to analyze the sales data in various ways grabbing as many insights as possible.