This is an analysis of Historical sales data for 45 Walmart stores located in different regions. I got the dataset from Kaggle. The analysis and visualtion was done in Excel becuase of the pivot table feature and mathematical advantage it has. Click Here to downlaod the complete Excel wookbork for the analysis.
Historical sales data for 45 Walmart stores located in different regions are available. There are certain events and holidays which impact sales on each day. The business is facing a challenge due to unforeseen demands and runs out of stock some times, due to inappropriate machine learning algorithm. Walmart would like to predict the sales and demand accurately. The objective is analyze the store performance and the impact of holidays on the sales.
Holiday Events
Super Bowl: 12-Feb-10, 11-Feb-11, 10-Feb-12, 8-Feb-13
Labour Day: 10-Sep-10, 9-Sep-11, 7-Sep-12, 6-Sep-13
Thanksgiving: 26-Nov-10, 25-Nov-11, 23-Nov-12, 29-Nov-13
Christmas: 31-Dec-10, 30-Dec-11, 28-Dec-12, 27-Dec-13
Basic Statistics tasks
- Which store has maximum sales
The store with the Maximum number of sales is store 20, with a total of $301,397,792.5 Revenue generated while the store with the Minimum nuber of sales is store 33 with a total of $37,160,222 Revenue generated
- Which store has maximum standard deviation i.e., the sales vary a lot. Also, find out the coefficient of mean to standard deviation
The store with the Maximum standard deviation i.e sales vary a lot is store 14 with Sales standard deviation of $317,569.9 and a coefficient of variation of 15.71%
- Which store/s has good quarterly growth rate in Q3’2012
The only store that had an upward trajectory in terms of revenue in Q3'2012 is store 37 with a revenue growth of 1.63%
- Some holidays have a negative impact on sales. Find out holidays which have higher sales than the mean sales in non-holiday season for all stores together
The holidays that have positive impact on sales more than the non holiday season are Thanksgiving holiday and SuperBowl holiday with average revenue of $1,471,273.43 and $1,079,127.99 respectively. While the No holiday season has an average revenue of $1,041,256.38
- Provide a yearly, monthly and quarterly view of sales in units and give insights
The year with the highest sales is 2011 with a total of $2,448,200,007.35
Overall, sales is at it's peak in Qtr3 (Jul - Sep) with total revenue of $1,841,852,365.33 for all the years combined and lowest in Qtr1 (Jan - Mar) with total revenue of $1,494,112,230.22 for all the years combined
The month that has the highest sales overall is Jul with a total revenue of $650,000,977.25 for all the years combined and the Month with the lowest revenue is $332,598,438.49 this could be as a result of no data available for the month of January in 2010. Sales performance has been unstable MoM across the years as there has been no consistent growth in revenue MoM.
Sales is high during Thanksgiving and SuperBowl holiday, this means demand is very high on those weeks, it's advised that the business stocks up more during this period in order to accomodate the high demands. Demand is also high in Qtr3 (Jul - Sep) it's advised that the business stocks up more during these Months in order to accomodate the high demands and maximize sales.