Hi there,
This project is to identify the trend of sales between January 2019 to March 2019, the first quarter of the year, using Microsoft Excel.
DATA SOURCE
The dataset was gathered and collected from one of the SuperMarkets in Myanmar, in three Cities, which comprises of the Sales Data, Customer data, Location data and the Product data.
DATA CLEANING
*I used Microsoft Excel to clean the dataset, using the Excel functions.
*For me to have one worksheet with all the tables included in it, I used the VLOOKUP function (You can also merge the tables using Data Model, by forming a relationship between the Customer, Location, Product and Sales Data, using their primary keys, INVOICE ID).
\=VLOOKUP([@[Invoice ID]],'Customer-details'!$A$1:$C$1001,2,FALSE)
*Months and Days were extracted from the Date column using the text function.
\=TEXT(cell, "ddd"
*I also extracted the TimeFrame from the Time column with the IF/AND functions;
Morning: 9AM to 12PM,
Afternoon: 12PM to 4PM,
Evening: 4PM to 9PM.
\=IF(AND([@Time]>(9/24),[@Time]<(12/24)),"Morning", IF(AND([@Time]>(12/24),[@Time]<(16/24)),"Afternoon","Evening"))
VIsualization was done using Pivot Table and Pivot Charts
INSIGHTS FROM THE ANALYSIS
--From my Analysis, the total income made was $322,966, with a gross income of $15,379.
--The total number of orders recorded was 1,000 from the customers using their InvoiceID, and a total of 5,510 items amongst the six products were purchased, with Food and Beverages having the highest sales
--The overall rating amongst the customers ranging from 1 to 10, using their average rating, was 6.97/10
--The number of orders gotten each day varies, averagely 14.85, and the highest order we had was 164.
--Most orders were placed in the Evening, 7PM, and we made about $138,370.
IN CONCLUSION;
The peak hour is in the Evening, around 7PM, this shows that most of our customers prefer to shop in the Evening (probably because they are coming from work). If we want to generate more sales in the Morning and Afternoon(an equivalent sales amongst the timeframe), my suggestion would be for us to have an online store, where customers can shop as long as they want, at the comfort of their homes and "places of work" to drive more sales, which makes it a "win win" solution for both parties.
In the analysis gotten, it is evident that Naypyitaw, Branch C had the highest sales amongst the three location. I would suggest that we should get in touch with the Manager of the branch to understand what they are doing differently and incorporate those things in the other two branches (Yangon and Mandalay). Another recommendation I would give is, the supermarket should make use of their Social Media Platforms to advertise their goods to drive more sales.
CIAO! Bye for now.