70.2K

Verified Solution

Link Copied!

As part of the quarterly reviews, the manager of a retail store analyzes the quality of customer service based on the

periodic customer satisfaction ratings (on a scale of 1 to 10 with 1 = Poor and 10 = Excellent). To understand the level of

service quality, which includes the waiting times of the customers in the checkout section, he collected the data shown

below on 100 customers who visited the store.

Use data in tab Prb47-50 for problems 47 through 50

47. Using the data given, apply k-means clustering with k = 5 using Wait Time (min), Purchase Amount ($), Customer

Age, and Customer Satisfaction Rating as variables. Be sure to Normalize input data, and specify 50 iterations and

10 random starts in Step 2 of the XLMiner k-Means Clustering procedure. Analyze the resultant clusters.

What is the smallest cluster? What is the least dense cluster (as measured by the average distance in the cluster)?

What reasons do you see for low customer satisfaction ratings?

Print program output

48. Using the data given, apply hierarchical clustering with 5 clusters using Wait Time (min), Purchase Amount ($),

Customer Age, and Customer Satisfaction Rating as variables. Be sure to Normalize input data in Step 2 of the

XLMiner Hierarchical Clustering procedure. Use Wards method as the clustering method.

a. Use a PivotTable on the data in the HC_Clusters1 worksheet to compute the cluster centers for the five clusters

in the hierarchical clustering.

b. Identify the cluster with the largest average waiting time. Using all the variables, how would you characterize

this cluster?

c. Identify the smallest cluster.

d. By examining the dendrogram on the HC_Dendrogram worksheet (as well as the sequence of clustering stages

in HC_Output1), what number of clusters seems to be the most natural fit based on the distance?

49. a. Using the data given, apply hierarchical clustering with 5 clusters using Wait Time (min) and Customer

Satisfaction Rating as variables. Be sure to Normalize input data in Step 2 of the XLMiner Hierarchical

Clustering procedure, and specify single linkage as the clustering method. Analyze the resulting clusters

by computing the cluster size. It may be helpful to use a PivotTable on the data in the HC_Clusters worksheet

generated by XLMiner to compute descriptive measures of the Wait Time and Customer Satisfaction Rating

variables in each cluster. You can also visualize the clusters by creating a scatter plot with Wait Time (min)

as the x-variable and Customer Satisfaction Rating as the y-variable.

b. Repeat part a using average linkage as the clustering method. Compare the clusters to the previous method.

LandValue ($)	BuildingValue ($)	Acres	Age	Price ($)
18100	92500	0.5	53.9	114885
23600	152700	0.22	19.7	180895
25900	134300	0.3	15.9	162038
22100	129600	0.23	41	154496
23900	168700	0.32	39.9	196973
22400	118300	0.25	41.8	145075
24100	123300	0.26	70.9	151480
26300	133800	0.26	37.8	164762
24900	139400	0.24	33	166528
13600	87200	0.17	34.7	105762
36100	210400	0.6	52.9	250170
19500	101300	0.16	67.8	125082
38800	224700	0.44	21.7	265066
23500	139000	0.22	10.8	166697
26300	164200	0.35	3.9	194881
21900	122400	0.17	15.7	146818
23400	149600	0.22	15.7	176048
15000	102200	0.12	97.8	119584
15000	102200	0.12	97.7	121759
9200	22000	0.17	120.9	34947
9200	22000	0.17	120.9	35214
5600	48000	0.12	103.9	57142
9000	58800	0.24	88	72192
21000	109600	0.21	36.7	133848
23500	165900	0.15	5.7	194079
36000	262500	0.22	2.9	300407
23700	114900	0.22	37.7	141700
22000	102700	0.2	48.9	128866
19900	95800	0.23	78.9	119189
22100	116300	0.18	30.8	141018
24600	165500	0.29	43	193661
21500	113400	0.17	44.9	137308
15000	81100	0.16	62.9	99817
15700	129200	0.23	46.7	148909
14200	81600	0.15	57.9	100701
10700	49700	0.15	99.8	65082
16600	72700	0.18	91.8	92614
25500	110700	0.21	48	137889
15100	74300	0.23	71.8	91180
7400	55500	0.15	96.8	64119
28500	129400	0.25	49.9	160139
25100	83900	0.2	45.8	113043
50100	164600	0.23	44	217684
83300	276000	0.61	47.9	360936
124500	552300	1.05	5.7	679795
47000	214400	0.22	92.9	264115
64600	185000	0.58	91	254075
33900	138800	0.22	97.9	173987
41100	156300	0.18	76	200251
29100	96400	0.28	57.8	130214
56400	256400	0.4	56.8	316874
45400	219200	0.21	79.8	267672
23800	92100	0.15	91.9	119769
52800	172800	0.27	74.8	229499
25100	99200	0.19	36.7	128456
27200	152600	0.18	16.7	181102
28100	102900	0.18	75.8	132977
28800	98800	0.19	53.9	131411
33400	103900	0.45	84.9	139697
20700	95600	0.14	89.8	120046
25600	101900	0.2	57.8	131026
25800	110700	0.18	51.9	141202
29300	147700	0.2	90.9	181575
26000	116000	0.18	44	144513
25900	73500	0.16	81.8	100953
32800	125000	0.35	68.7	160546
31100	166800	0.2	57.7	199970
25800	105300	0.17	58.8	134647
27200	94800	0.17	42.9	124311
25000	105900	0.16	82	133543
29200	117500	0.2	53.8	151392
30000	93300	0.26	55.7	124476
20400	112000	0.13	80.9	136599
23600	83400	0.16	57.7	110399
16200	85800	0.1	67	105027
29300	123900	0.22	44.8	157819
27000	97800	0.18	46.8	129675
25600	86300	0.16	61.7	115952
46200	220500	0.57	50.8	268552
22900	160000	0.15	20.7	187870
27100	105200	0.21	51.8	135549
30700	107100	0.3	70	142738
29100	102400	0.23	58	135284
34700	150400	0.28	68.9	189790
20000	80400	0.24	66.9	105302
35700	159400	0.28	1.7	196936
35100	161500	0.25	8.8	201349
33700	162500	0.21	8.8	198580
33700	162500	0.21	8.8	200228
36400	176100	0.29	8.9	215634
33200	122300	0.2	4.9	157208
39200	169200	0.36	5.9	212662
33100	180100	0.2	5.8	217543
16000	98400	0.19	49.9	118491
24900	63800	0.45	83.9	91539
22000	121300	0.27	34.9	147802
20000	107600	0.23	36.7	131948
33900	230800	0.27	10	268444
22100	153800	0.3	46.8	180464
22800	111100	0.23	52	137326
24700	117800	0.32	48.7	145115
38700	118700	0.81	47.8	159644
25800	108000	0.26	53.3	135049
31700	140500	0.34	40.6	174475
82200	171700	1.23	56.4	257467
19500	147600	0.53	28.2	169311
24400	132000	0.25	14.2	157570
22500	119800	0.18	15.5	143676
25900	117100	0.29	17.7	146960
22700	95000	0.25	55.6	121175
21200	56700	0.23	96.6	81869
34000	163800	0.26	15.2	199361
18900	118000	0.17	45.5	139981
33900	151600	0.26	25.3	186637
23800	133500	0.21	13.6	161123
23900	119000	0.21	14.3	146054
18500	110500	0.19	32.2	130575
36300	122500	0.61	56.2	162270
47300	298800	0.36	31.4	348138
36600	238700	0.28	25.5	278839

Answer & Explanation Solved by verified expert

See Answer