Project 2 - Supply and Demand Gap Analysis

Aim

Supply and Demand gap analyses are widely used by companies as strategic management decision tools. This project aims to identify the gap between demand and supply so that organizations can take action to minimize those gaps.

Introduction

One of the leading retail stores in the US, is Walmart, an American multinational retail corporation that operates a chain of hypermarkets in the United States, headquartered in Bentonville, Arkansas. The company was founded by brothers Sam and James "Bud" Walton in nearby Rogers, Arkansas in 1962.

Problem Statement

The business is facing a challenge due to unforeseen demands and runs out of stock sometimes. There are sales data available for 45 stores of Walmart. Some certain events and holidays impact sales on each day. It would like to predict the sales and demand.

Dataset Description

This is the historical data that covers sales from 2010-02-05 to 2012-11-01. Within this data we will find the following fields:

• Store - the store number
• Date - the week of sales
• Weekly_Sales - sales for the given store
• Holiday_Flag - whether the week is a special holiday week 1 – Holiday week 0 – Non-holiday week
• Temperature - Temperature on the day of sale
• Fuel_Price - Cost of fuel in the region
• CPI – Prevailing consumer price index
• Unemployment - Prevailing unemployment rate

Methodology

• The first step is cleaning the dataset, which is done with the help of the Transform option in Power BI.
• The data type of each column is changed to respective text, numbers, and date formats.
• To get the week number we have created a new table called “Table calendar” with the formula “Table Calender = CALENDAR(MIN(Walmart_Store_sales[Date]), MAX(Walmart_Store_sales[Date]))”
• Create another column name - “Weeknum = WEEKNUM('Table Calender'[Date],16)”, 16 – represents which day your week should start, in this case, Saturday.
• Then with the help of the Power BI desktop, we can start working on the visualization with different charts and graphs.

Analysis

• Top 10 weekly sales for most of the stores happened in December month two weeks before Christmas week (non-holiday week).
• In terms of holiday week, November last week has seen high sales (Thanksgiving and Black Friday week).
• Store 33 had the least weekly sales throughout the year, followed by store number 44,5,36.
• Store 20 had the highest weekly sales, followed by Store number 4, 14,13 - 19% of total sales came through these stores.
• The average temperature is below 60 degrees in the stores that had high weekly sales.
• The average temperature is above 72 degrees in the stores that had low weekly sales.
• Unemployment rate, CPI, and fuel price had no impact on sales.
• January month had the least sales recorded – 5% of total sales.
• The hottest seasons June, July, and August had the highest sales recorded accounting for 28% of total sales.

Insights

• In November month most of the sales came through Black Friday sales.
• January and the last week of December, the Christmas and New Year’s Eve weeks had low sales, people generally prefer to buy Christmas gifts 1-2 weeks before Christmas, so it does not change sales in the Christmas week.
• The top 5 sales averages by weekly belong to 1-2 weeks before Christmas, Thanksgiving, Black Friday, and June when the schools are closed.
• The year 2010 has higher sales than 2011 and 2012. But November and December sales are not in the data for 2012. Even without the highest sales months, 2012 is not significantly less than 2010, so after adding the last two months, it can be first.

Recommendations

• Location of the stores data needed, to identify whether the influence of fuel price, unemployment, and CPI has on weekly sales.
• Store size data needed to predict the higher sales stores was due to the size of the stores.
• Department-wise sales data also needed to break down which department performs better in different seasons.
• Time of purchase could further help to predict which hour of the day people prefer to buy products both on holiday and non-holiday week.

Conclusions

• Maintaining more inventories during November and December can further boost sales.
• After two high sales months, people prefer to pay less in January.
• End of School months – June, and July have significantly high sales after November and December.
• Stores 20, 4, 13, 14, 2 showed better sales in all quarters of the year.