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.

50. Using the data given, apply k-means clustering using Wait time (min) as the variable with k = 3. Be sure to Normalize

input data, and specify 50 iterations and 10 random starts in Step 2 of the XLMiner k-Means Clustering procedure. Then

create one distinct data set for each of the three resulting clusters for waiting time.

a. For the observations composing the cluster which has the low waiting time, apply hierarchical clustering with Wards

method to form two clusters using 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. Using a PivotTable on the data

in HC_Clusters, report the characteristics of each cluster.

b. For the observations composing the cluster which has the medium waiting time, apply hierarchical clustering with

Wards method to form three clusters using 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. Using a

PivotTable on the data in HC_Clusters, report the characteristics of each cluster.

c. For the observations composing the cluster which has the high waiting time, apply hierarchical clustering with Wards

method to form two clusters using 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. Using a PivotTable on the data

Customer Number	Wait Time (min)	Purchase Amount ($)	Customer Age	Customer Satisfaction Rating
1	2.3	436	42	7
2	2.8	408	33	6
3	3.2	432	38	5
4	3.4	431	40	5
5	3.4	456	29	6
6	4.2	537	46	4
7	3.2	456	42	5
8	1.4	430	40	8
9	6.4	663	24	3
10	7.8	839	37	4
11	6.5	659	52	5
12	9.8	836	43	2
13	5	543	56	4
14	1.8	419	35	8
15	6.1	700	39	6
16	3.4	432	44	7
17	7.8	845	33	5
18	2.8	467	42	6
19	1.2	425	46	8
20	9.5	848	50	4
21	8.2	808	55	3
22	7.6	674	35	3
23	5.4	547	52	4
24	6.7	691	38	5
25	9.6	847	53	4
26	11.4	826	48	2
27	2.1	426	52	7
28	5.6	535	32	7
29	3.7	521	43	8
30	4.9	513	44	6
31	6.4	645	53	5
32	9.3	846	52	4
33	10.6	730	51	3
34	6.5	786	53	3
35	5.4	523	46	5
36	7.6	654	36	6
37	3.2	443	48	7
38	2.4	409	54	8
39	1	400	39	6
40	0.2	418	51	7
41	2.4	498	30	6
42	5.7	532	32	5
43	6.4	663	44	7
44	6	681	39	8
45	3.7	543	54	5
46	8.7	800	51	5
47	6.9	673	45	5
48	9.8	856	43	4
49	10	756	44	4
50	9.5	854	43	6
51	6.3	672	50	6
52	7.4	698	47	7
53	2.3	434	43	7
54	4.6	544	40	4
55	4.9	523	53	6
56	5.7	546	55	6
57	7.4	676	42	8
58	6.8	662	36	6
59	9.6	1000	40	5
60	6.4	678	46	5
61	7.2	655	32	4
62	5.6	535	36	5
63	9.7	833	35	3
64	2.3	498	30	7
65	4.3	508	41	6
66	5.7	542	49	6
67	2.4	435	39	8
68	6.7	665	41	5
69	2.4	387	54	9
70	9.8	845	34	7
71	4.5	532	40	6
72	6.7	687	30	5
73	7.2	643	33	4
74	3.5	424	49	7
75	8.9	836	47	5
76	9.7	876	31	4
77	3.5	456	47	7
78	4.7	523	49	6
79	8.5	818	35	5
80	9.7	845	54	4
81	2.7	401	55	7
82	5.7	554	43	6
83	7.6	648	51	7
84	4.4	540	31	6
85	7.8	839	45	5
86	9.4	845	48	4
87	4.9	534	36	5
88	7.1	693	44	4
89	5.4	512	39	3
90	6.7	665	49	5
91	8.6	825	36	5
92	4.5	548	30	7
93	6.1	704	31	5
94	5.3	509	31	6
95	6.7	672	35	5
96	8.1	824	36	4
97	6.3	632	30	4
98	7.4	689	35	2
99	8.8	839	50	4
100	9.6	847	35	2