INTRODUCTION
The analysis was conducted in a supermarket in Myanmar, Asia for the first quarter of 2019. The branches considered for the analysis are located in Naypyitaw, Mandalay and Yangon. The analysis aims to provide insights into the supermarket stores and how profit can be optimized and increased.
DATA SOURCE
The data comprises Supermarket sales, Product details, Customer details and City details worksheets. Click here to access data source.
DATA CLEANING
The data was cleaned using Microsoft Excel.
I imported the relevant details from the 'Product details', 'Customer details' and 'City details' worksheets into the 'Supermarket sales' worksheet to have one worksheet by using the VLOOKUP function with the 'Invoice ID' being the lookup value. I also used full absolute referencing for the Table Array because the lookup values were not arranged the same way for all the different worksheets.
\=VLOOKUP(A2,'Customer-details'!$A$1:$C$1001,2,0)
I extracted the day and month from the 'Date' column using the TEXT function.
\=TEXT(F2,"ddd")
\=TEXT(F2,"mmmm")
I added a new column to indicate the period of day i.e. Morning, Afternoon and Evening using an IF/AND statement.
\=IF(AND(I2>(9/24),I2<(12/24)),"Morning",IF(AND(I2>=(12/24),I2<(16/24)),"Afternoon",IF(AND(I2>=(16/24),I2<(21/24)),"Evening")))
My dashboard was done using pivot tables and charts. Slicers were introduced and connected to the pivot tables to make the dashboard interactive.
INSIGHTS
The gross income for the supermarket in the first quarter was $15,379.37 out of a revenue of $322,966.75.
A total of 1000 orders were made.
The overall rating of the store was 6.97 out of 10.
Most sales were made on Saturday (164) which is most likely due to people not going to work.
Most sales were made in the evening by 7 PM which is most likely the time people are coming back from work.
Food and beverages made the most sales in the first quarter ($2673.56) but this changed when analyzed by the different cities.
January has the highest gross income in the first quarter most likely due to people shopping for the new year and quarter in bulk.
The difference between the gross income generated by the members and non-members (normal) is not a lot ($260.56).
RECOMMENDATIONS
More manpower should be employed in the evenings and on Saturdays to account for the influx of customers and to ensure efficient customer service.
I recommend that a survey be taken to get the root cause of the rating being 6.97/10 and find ways of increasing the rating.
Non-members should be told the benefits of becoming a member e.g. discounts, promo codes, goods on sale etc.
Least selling products (health and beauty) can be advertised to boost sales.
Best-selling products should not run out of stock to prevent loss of sales.
An online platform can be introduced to boost sales in the mornings and evenings so customers do not have to physically come to the store.
My comment section is open for questions, recommendations and constructive criticism.
Bye for now. Till we meet next time.