SUPERMARKET SALES ANALYTICS PROJECT
Introduction
For my first data analytics project using Microsoft Excel, I carried out the sales analysis of a supermarket company. The aim of this project is to derive reasonable insights from the given data, produce an interactive dashboard from these insights and give recommendations that would improve sales thereby increasing the gross income of the supermarket company.
Dataset Description
The dataset contains the sales record of a supermarket company with three branches A, B and C located in Yangon, Mandalay and Naypyidaw respectively for the first quarter of 2019. The dataset contains 1,001 rows and 17 columns. These columns include Invoice ID, Branch, City, Customer type, Gender, Product line, Unit price (in $), Quantity, Tax (5% tax fee for customer buying), Total (Total price), Date, Time, Payment method, COGS (cost of goods sold), Gross margin percentage, Gross income, Rating (on a scale of 1 to 10). The first row contains the name of each attribute (column), the remaining 1,000 rows corresponds to a customer’s transaction.
Download dataset: https://www.kaggle.com/datasets/aungpyaeap/supermarket-sales
Analysis and Insights
The data analysis was done using only Microsoft Excel. No data cleaning was done as the data was already clean. I generated pivot tables and charts from the data which helped in deriving insights and producing an interactive dashboard.
I used the dataset to answer the following questions.
• What was the total revenue generated by the Supermarket Company?
The supermarket company generated $322,966.75 in total revenue between January to march 2019.
• What was the total quantity of products sold by the supermarket company?
The supermarket company sold 5,510 products between January to march 2019.
• What was the Gross income generated by the supermarket company?
The supermarket company generated a gross income of $15,397.37.
• What was the average rating of the supermarket company?
The supermarket company had an average rating of 6.97 out of 10.
• How many customers did the supermarket company have between January to march 2019?
The supermarket company had 1,000 customers between that period.
• Which branch generated the largest gross income?
Branch C generated the largest gross income, it contributed 34.24% to the gross income of the supermarket company. Branches A and B contributed 32.88% respectively.
• Which product line had the most sales and which had the least?
A total of 971 quantities of electronic accessories were sold across the 3 branches making it the product line with the most sales. Health and beauty had a total sale of 854 quantities, making it the least sold product.
• How many percent did each product line contribute to the gross income?
Food and beverage had the highest contribution to the gross income of the supermarket company while Health and beauty had the least. They contributed 17.38% and 15.23% respectively.
• What payment method was most used by the customer?
345 customers paid using E-wallet. 344 and 311 customers paid using cash and credit card respective making E-wallet the most used payment method.
• Did the supermarket company have more female or male customers?
The supermarket company had a total of 501 female customers and 499 male customers. Contributing 50.1% and 49.9% respectively to the total number of customers.
• Was more income generated from members or normal customers?
Members generated more income with a percentage of 50.85. Normal customers contributed 49.15% to the gross income.
• How much gross income was generated per month?
There was a 16.4% decrease in the gross income of the supermarket company in February.
- What time of the day does the supermarket company make the highest gross income?
The supermarket company makes the highest gross income between 6pm — 7pm.
- What was the average rating of each product line?
Each product line sold in the supermarket company had a rating between 6.8 and 7.1.
All the Visuals can be seen at a glance on this dashboard. The filters in the dashboard can be used to check the analysis of each branch and the general analysis of the supermarket company.
Recommendations
• There was a 16.4% decline in the gross income of the supermarket company in February. A root cause analysis should be done and appropriate solutions should be implemented to prevent this from reoccurring.
• The supermarket company had an average rating of 6.97 out of 10 which is not so impressive. A customer survey should be taken and appropriate solutions should be implemented so as to increase customers satisfaction. The following questions may be asked in the survey;
1. Are customers preferred brands being sold?
2. How satisfying is the customer service?
3. How accessible are the products?
4. Others (mention other reasons).
