Using Exploratory Data Analysis to Improve the Fresh Foods Ordering Process in Retail Stores
by Sivaram Pandravada and Thimmiah Gurunatha
With the abundance of data now available in the information era, data science offers significant opportunities to complement quality approaches to problem solving and continuous improvement. Unfortunately, as Roger D. Peng and Elizabeth Matsui point out in their book, The Art of Data Science, “Data analysis is hard, and part of the problem is that few people can explain how to do it.”1 Quality professionals may find that applying the principles of data science is not always as straightforward and formulaic as they would like.
In illustrating how explorative data analysis and basic statistics helped a grocery chain reduce inefficiencies in its retail inventory and ordering process, this case study presents a real-world example of how the thought processes of data scientists can contribute to quality practice. Once the chain saw the difference that the data-based approach made in targeting waste and inefficiency in one store, it successfully replicated its process improvements to increase profitability throughout the organization. The project approach and lessons learned can also be applied in other retail settings.
Case Study At a Glance
- The short shelf lives of fresh foods along with fluctuating consumer demand meant that a European retail chain’s stores often had to hold clearance sales with zero or negative margins or write off inventory.
- Explorative data analysis and basic statistics helped the chain identify and reduce inefficiencies in its inventory and ordering process, minimizing the gap between quantities sold and quantities ordered.
- Rainbow statistical process control (SPC) charts, a variation on traditional SPC, helped ensure ongoing monitoring of the stock-to-sales ratio and triggered corrective action in real time, bringing sustainable results within three months.
Download the entire case study (PDF) or continue reading below for project highlights.
Find More Case Studies
Search Examples of Quality Success in Retail
The ASQ case study collection contains more than 1,800 examples of quality solutions to real-world problems. Find other case studies involving retail processes to learn about how organizations are using quality tools and approaches to improve.
Retail Ordering Process Case Study
A data science approach to assessing the problem
The short shelf lives of fresh foods along with fluctuating consumer demand had been causing a European retail chain’s stores to hold clearance sales with zero or negative margin or to write off some inventory as “shrinkage.” Annually, the problem of shrinkage accounted for revenue losses of up to 20 percent.
Although the ordering process was automated, the algorithm was better suited to articles with longer shelf life (>90 days); hence, department managers would often overwrite the automatic system and place orders manually. There were thousands of stock keeping units (SKUs) and no defined tolerance limits to manage stock and shrinkage across various stores. The organization needed an approach for monitoring and controlling this waste.
Granular data is key to accurate and productive problem assessment. When it is not available, significant time goes into defining metrics and capturing measurements before analysis can begin. With the prevalence of sophisticated database management systems, the trick becomes extracting information based on the questions that need to be answered to gain a better understanding of any problem.
For the problem of shrinkage, using data science and lean Six Sigma started with two questions:
Question 1: What are we trying to optimize in this project?
Answer: The team seeks to decrease shrinkage in fresh food divisions and also decrease the use of discount sales that lead to reduced margins.
Question 2: What factors have the most effect on shrinkage and discount sales?
Answer: Shrinkage and discount sales occur as a result of excess inventory in stores because of improper ordering processes. Key factors to study include the following:
- Identify the biggest contributing categories and departments leading to high shrinkage and discount sales
- Quantify the demand or conduct a sales study of these articles by number of SKUs
- Quantify the supply or purchase of these articles by number of SKUs
- Review gaps in the current ordering process leading to supply versus demand mismatch
The Japanese concept of muda, which translates to “waste” or “any activity that consumes resources but creates no value for the customer,” provided direction for the team’s next task of identifying and reducing waste to improve process efficiency. Pareto analysis helped the team understand which categories and SKUs were contributing to 80 percent of shrinkage and discount sales and prioritize them for improvement.
Exploratory data analysis
The team used data analytics and lean Six Sigma tools to identify and implement corrective action that would reduce waste and improve the ordering process. Targeting the SKUs that were contributing significantly to high shrinkage and discount sales, the team started with analysis of the daily unit sales of one fresh food SKU with a shelf life of five days and the highest shrinkage component within the fresh food category.
The histogram data in Figure 1 show daily sales in one store where there was maximum shrinkage due to this fresh food SKU. One observation that immediately stands out is that the sales data appear to be normally distributed if three data points for times when demand was an outlier are omitted from consideration. The distribution is good news from a modeling perspective and in terms of predictability.
In Figure 1, it is also easy to see that out of 225 days, the store sold its highest quantities on only a small percentage of days:
- ≥ 280 sold on only 19 days
- ≥ 320 sold on only nine days
- ≥ 400 sold on only two days
Figure 2 presents a histogram for quantities ordered for this same SKU. Reviewing alongside the data on quantities sold leads to a few notable takeaways:
- The ordering quantity did not follow a normal distribution, even though the process is within the control of the stores
- Out of 142 orders placed for the same time period of 225 days, approximately 80 were for quantities ≥ 300
- Department managers were particularly surprised by the fact that approximately 56 percent of the time, an order was placed for a quantity that sold only 4 percent of the time
A simple comparison of the sales and order quantities summarized by weekday should give any process owner many subsequent questions to ask the department manager responsible for ordering. In particular, why is there so much variation between the ordering process and the quantity sold when the article can be ordered six days in a week and has only five days of shelf life?
Continue Reading …
To continue reading this case study, download the complete PDF article.
For More Information
- EBITDA Solutions assisted the grocery retail chain with this improvement project. Visit www.ebitda.co.in/.
- Find more case studies on the use of quality tools and approaches at asq.org/quality-resources/case-studies.
About the Authors
Sivaram Pandravada is managing director, India - EBITDA Solutions.
Thimmiah Gurunatha is senior consulting partner at EBITDA Solutions, Florida, USA. Previously, he held senior engineering roles for Xerox for 32 years. An ASQ Fellow, Gurunatha is an ASQ Certified Reliability Engineer (CRE) and Six Sigma Black Belt (CSSBB).