The art of data science roger peng pdf

Making the Case for Quality

February 2017

by Sivaram Pandravada and Thimmiah Gurunatha

Using Exploratory Data Analysis to Improve the Fresh Foods Ordering Process in Retail Stores

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 ineffi- ciency 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.

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 lon- ger 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 extract- ing 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:

• 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.

At a Glance . . .

http://www.asq.org
ASQ www.asq.org Page 2 of 5

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 pro- cesses. 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 dis- count sales and prioritize them for improvement.

Exploratory data analysis

The team used data analytics and lean Six Sigma tools to iden- tify 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 high- est 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

56

50

31

10 6

1 1 0 0 0 0 1

40

21

8

80

30

20

10

0

40

50

60

160 240 320 400 480 560

Fr eq

ue nc

y

Quantity sold

36

51

7

2 0 1

11

0 0 3

0

21

0 02 3

5 00 01

0

30

20

10

0

40

50

75 150 225 300 375 450

Fr eq

ue nc

y

Quantity ordered

Figure 1: Histogram of quantity sold and frequency of occurrence

Figure 2: Histogram of quantity ordered and frequency of occurrence

http://www.asq.org
http://asq.org/learn-about-quality/cause-analysis-tools/overview/pareto.html
ASQ www.asq.org Page 3 of 5

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?

Table 1 focuses on a specific category/ SKU and iterates the gaps in quantity of goods sold and goods received/ordered.

Model building and interpretations

Model building is part of the improve phase of a typical Six Sigma define, measure, analyze, improve, control (DMAIC) project. Having contributed to defining and understanding the prob- lem and analyzing opportunities for improvement, data still have a role to

play in constructing and implementing solutions for sustained results.

For this project, a simple model might be a rule-of-thumb estimation or an empirical equation that can predict the ordering quantity and the quantity to be maintained as minimum inventory. It involves iterations of steps and assump- tions to arrive at an equation that best fits the demand of the store for the item.

One has to make assumptions for the model and conduct backtesting based on quantities demanded to ascertain that there are no instances of lost sales. Comparing the quantities ordered against actual lost sales incidents plus shrinkage incidents and discount sale incidents leads to a calculation of the benefits for the project. In the spirit of lean, a 10 percent reduction in shrink- age within three months was selected as the improvement target. The basis of the model is to minimize the gap between the quantity sold, represented by the blue line in Figure 3, and the quantity ordered, shown in red.

With the model and goals established, interpretation and communication then become project-specific and store- specific based on assortment demands within different geographies.

This approach is complementary to Six Sigma DMAIC, but instead of emphasiz- ing the conventional defects per million opportunities (DPMO), waste reduction is measured in terms of money written off as shrinkage, a metric to which store and department managers can more eas- ily relate. As a result, the control charts were designed around shrinkage.

Turning data into action

In order to reduce excess ordering, the warehouse used color coding to moni- tor and track inventory. The colors of tags on the shelves helped ensure that

department managers would see indica- tions of stock levels during daily physical inspections. Red tags indicated SKUs with high shrinkage. Blue tags signaled SKUs with high stock levels.

Ordering proceeded according to stock shelf life and quantity sold. The store manager would only have to look at the tags on the shelf to understand the need to investigate any SKUs that might lead to shrinkage events and require preven- tive action.

A rainbow SPC chart (see Tools Used in This Case Study on page 5) was

Table 1: Comparison of quantities sold versus quantities ordered by day of the week

Number days ordered

Quantity received

Average of quantity received

Number days sold

Quantity sold

Average of quantity sold

Sunday 32 3,186 100

Monday 22 5,612 255 32 5,951 186

Tuesday 28 7,099 254 32 5,941 186

Wednesday 16 4,776 298 32 5,731 179

Thursday 30 7,827 261 32 5,994 187

Friday 18 5,169 287 32 6,536 204

Saturday 29 8,650 298 33 5,903 179

Total 143 39,133 274 225 39,242 174

0

20

40

60

80

100

Pe rc

en t

0 100 200 300 400 500 600

39.6

34 9.

4

80 25

0 85.6

23 4.

2

Variable Quantity sold Quantity received

Data

Normal

StDev 71.06 90.01

Mean 174.4 273.7

N 225 143

Figure 3: Cumulative distribution of quantities sold and ordered

http://www.asq.org
ASQ www.asq.org Page 4 of 5

deployed for real-time identification of spikes in the stock-to-sales ratio, as represented in Figure 4. The escalation matrix that was established required root cause analysis for any rise or fall out of the green zone. Entry of the ratio into the red zone would need intervention of the store manager, while entry into the orange/pink zone would prompt the intervention of the floor manager. Entry of the ratio into the yellow zone would need the atten- tion of the department manager who

is responsible for placing the order. Acting on the immediate feedback provided in rainbow SPC monitoring helped reduce shrinkage by 20 percent over a period of one month.

Figure 4 presents the rainbow control chart plotting the stock-to-sales ratio based on quantity. At various points, the ratio enters the yellow, orange, and red zones, requiring escalation by one level up at each stage and root cause analysis by different levels of management.

The approach ensured real-time track- ing of variation and brought sustainable results within three months. The team monitored process data for the 10 top- selling articles for each department using a simple Excel template with a rainbow SPC macro and subsequently built a dynamic reporting and chart- ing tool using other data visualization software. Internal benchmarking then allowed the organization to continuously improve the ordering process across store outlets.

Pre-control inventory stock management

Product line/type of service Fresh chicken Country Xyz

Quality characteristics Stock + ordering to sales ratio Department Fresh food

Date(s) 1/10/2015 to 30/04/2016 Ordering team/source# Fresh food

Purpose of data collection Monitoring ordering process to improve performance Station number/department EBITDA Solutions/Country divisional manager

Target 3 stock/sales Upper specification limit 9.5 stock/sales Lower specification limit -3.5 stock/sales

2 O

ct -1

5 5

O ct

-1 5

8 O

ct -1

5 11

O ct

-1 5

14 O

ct -1

5 17

O ct

-1 5

20 O

ct -1

5 23

O ct

-1 5

26 O

ct -1

5 29

O ct

-1 5

1 N

ov -1

5 4

N ov

-1 5

7 N

ov -1

5 10

N ov

-1 5

13 N

ov -1

5 16

N ov

-1 5

19 N

ov -1

5 22

N ov

-1 5

25 N

ov -1

5 28

N ov

-1 5

1 D

ec -1

5 4

D ec

-1 5

7 D

ec -1

5 10

D ec

-1 5

13 D

ec -1

5 16

D ec

-1 5

19 D

ec -1

5 22

D ec

-1 5

25 D

ec -1

5 28

D ec

-1 5

31 D

ec -1

5 5

Ja n-

16 8

Ja n-

16 11

J an

-1 6

14 J

an -1

6 17

J an

-1 6

20 J

an -1

6 23

J an

-1 6

26 J

an -1

6 29

J an

-1 6

1 Fe

b- 16

4 Fe

b- 16

7 Fe

b- 16

10 F

eb -1

6 13

F eb

-1 6

16 F

eb -1

6 19

F eb

-1 6

22 F

eb -1

6 25

F eb

-1 6

28 F

eb -1

6 2

M ar

-1 6

5 M

ar -1

6 8

M ar

-1 6

11 M

ar -1

6 14

M ar

-1 6

17 M

ar -1

6 20

M ar

-1 6

23 M

ar -1

6 26

M ar

-1 6

29 M

ar -1

6 1

A pr

-1 6

4 A

pr -1

6 7

A pr

-1 6

10 A

pr -1

6 13

A pr

-1 6

16 A

pr -1

6 19

A pr

-1 6

22 A

pr -1

6 25

A pr

-1 6

28 A

pr -1

6 2

M ay

-1 6

5 M

ay -1

6 8

M ay

-1 6

11 M

ay -1

6 14

M ay

-1 6

-130

10.9530 10.4559

9.9588 9.4618 8.9647 8.4677 7.9706 7.4736 6.9765 6.4794 5.9824 5.4853 4.9883 4.4912 3.9941 3.4971 3.0000 2.5030 2.0058 1.5088 1.0113 0.5147 0.0177

-0.4794 -0.9765 -1.4735 -1.9706 -2.4676 -2.9647 -3.4618 -3.9588 -4.4559 -4.9529 -5.4500

Get CEO involved when red Get �rst and second level involved at yellow and orange

Get �rst and second level involved at yellow and orange Get CEO involved when red

-120

-110

-100

-90

-80

-70

-60

-50

-40

-30

-20

-10

0

10

20

30

40

50

60

70

80

90

-100

-110

-120

130

Pe rc

en t o

f t ol

er an

ce

(Stock + GR)/Sales

STOP

CAUTION

GO

CAUTION

GO

STOP

Figure 4: Rainbow chart for sales to inventory management

http://www.asq.org
ASQ www.asq.org Page 5 of 5

The reduced waste in shrinkage and stock levels has enabled the retailer to generate higher margins and con- centrate more on availability and customer interaction.

References

1. Roger D. Peng and Elizabeth Matsui, The Art of Data Science, 2016-05-18, https://leanpub.com/ artofdatascience.

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 con- sulting 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).

This improvement opportunity presented a classic case of the need for control charts, combined with root cause analysis and an escalation matrix (a simple, visible, reliable, and responsive process for abridging Six Sigma define, measure, analyze, improve, and control process steps) to help monitor day-to-day progress. Histograms and statistical process control (SPC) charts assisted with high-level visualization of sales and ordering management to identify opportunities to reduce waste.

Histograms are graphs used to show frequency distributions. The bars on the graph provide a visual representation of how often each value occurs. Learn more about histograms.

Statistical process control charts show how processes change over time. Plotting data on current performance against the process average and upper and lower control limits based on historical data provides a look at whether the process continues to perform predictably. Learn more about control charts.

In this project, rainbow SPC charts, a variation on traditional control charts, helped analyze data and determine the anomalies to address. Invented by ASQ Fellow Thimmiah Gurunatha, the rainbow SPC approach seeks to simplify control charts and provide immediate feedback whenever a significant variation in data occurs. While monitoring continuous and attribute data for optimization, seven colors are used to indicate escalation actions. Teams can monitor data in real time to capture evidence of parameters causing variation rather than trying to find these parameters after the fact.

The rainbow approach uses attribute data and continuous data, as well as life test data for Rainbow Reliability SPC.

TOOLS USED IN THIS CASE STUDY

Learn more about the rainbow SPC process in Systems Engineering Standards – The State of the Art, Integrating DFR, DFSS, and DFX in Systems Engineering Environment, by Thimmiah Gurunatha.

http://www.asq.org
https://leanpub.com/artofdatascience
https://leanpub.com/artofdatascience
http://www.ebitda.co.in/
http://asq.org/quality-resources/case-studies