My First Project in data analytics - Sales dashboard using MS excel

Hi Hashnoders,
I'd like to share my experience on my first project in data analytics here.
The dashboard was created using Microsoft excel and the dataset (which is one of the historical sales of a supermarket company that was recorded in 3 different branches for 3 months data) was provided by my tutor Jacob Ajala

Below is what the raw data looks like :

First, let me walk you through the data cleansing based on the business requirements

Link to Dataset : https://drive.google.com/file/d/13sSDlvU-HW_KXAA8SFft_dWa0Cd8QiRR/view?usp=sharing, https://docs.google.com/spreadsheets/d/1F0zzvTOEtdlS4G-h0qo1Zt3lpamu_RaU/edit?usp=sharing&ouid=111388547857713195603&rtpof=true&sd=true, https://drive.google.com/file/d/1X0wtDdRZydRIYG1ktMV0ojAwaStVUDhx/view?usp=sharing

1. I expanded the columns to fit the data, converted the data into a table and froze the top row for an easy view

2. From the business requirements above I had to merge information (Customer type, Gender, Product type, City and branch of store) from the other sheets into the main sheet using the "vlookup" function highlighted below

   

   Also, the fields for (day, month and Time of the day) are required so these were also created.

   DAY and MONTH was extracted from one of the date's cell using the "text" function and the TIME OF THE DAY was derived from the time column using the "if and" function.

   

   

3. And the data is ready for visualization.

   

   1. Visualization was done using Pivot tables and Charts. Below is the sheet for my pivot table and finally the visualization sheet which has the charts and slicers for quick filtering.

      

      

      Here are insights from the above analysis.

      1. The supermarket has an overall customer shopping experience rating of 7/10.

      2. They made a total income of $ 322,966.75 and a Total gross income of $ 15,379.37 with a profit margin of 4.76%

      3. The total number of orders received for the period is 1,000 and 5,510 products were sold.

      4. The number of orders ranged from 6-20 daily, Peak hours were 10 am, 1 pm, 3 pm and 7 pm. 7 p.m. was also the time they got the most orders generating an income of $ 138,370.92.

      5. 52% of Female and 48% of male customers patronize the store and 51% of Customers are members while 49% are non members

      6. Branch C - Naypyitaw branch generated the highest gross income of $5,265.18 followed by Branch A -Yangow with a gross income of $5,057.16 and Branch B - Mandalay made the least gross income of $5,057.03

      7. The Food and beverages Product type made the highest gross income of $ 2,673.56.

      8. Of the 3 months analyzed January recorded the highest sales with a gross income of $ 5,537.71.

In conclusion, these are my recommendations.

The Supermarket has an above average rating of 7 out of 10 however, the below points should be taken into consideration to increase sales, beat the competition and exceed their customers' expectation

1. Given that the supermarket has nearly equal numbers of male and female customers, it's advisable to observe celebration days and tailor each occasion to celebrate and appreciate both genders accordingly.

2. The supermarket should let people know about the benefits of being a member so that those who aren't members will want to sign up. They can also set up a program where members get rewards for bringing in their friends who aren't members yet.

3. Given the high number of customers in the evenings and on Saturdays, the supermarket should consider having all hands on deck i.e more staff to help and guide customers, making it easier for them to find the products they want, maintain orderliness in the branches, and ensure an ample supply of products.

4. Ideas should be exchanged among branches, particularly with Naypyitaw, to learn from each other's successful strategies for increasing patronage.

5. All branches should prioritize having an adequate supply of products for January and ensure that product categories such as food and beverages and sports and travel items are consistently in stock.

Thank you.