Question

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 data on 100 customers who visited the store; see the attached Excel file: ServiceQuality.

Using XLMiner Platform > Cluster, apply K-Means Clustering with the following Selected Variables: Wait Time (min), Purchase Amount ($), Customer Age, and Customer Satisfaction Rating as Selected Variables. In Step 2 of the XLMiner k-Means Clustering procedure, normalize input data, assume k = 5 clusters, 50 iterations, and fixed start with the default Centroid Initialization seed of 12345. (Note: If the allowable number of iterations is less than 50, it means that you do not use the educational version of XLMiner available to BUAD 2070 students; see the syllabus.)

1. A) What is the minimum normalized (standardized) Euclidean distance between created cluster centers (centroids)?

B) What is the maximum average normalized Euclidean distance between the cluster observations and the cluster centroid?

C) Based on your answers to Questions 1a and 1b, are the five created clusters justifiable? (Note. Recall that the distances between clusters should be greater than the distances within clusters.)

D) Using original data (coordinates), what are the maximum and the minimum average customer satisfaction ratings for the five created clusters?

E) Using original data (coordinates), what are the maximum and the minimum average wait times (in minutes) for the five created clusters?

F) Using original data (coordinates), what are the maximum and the minimum average purchase amounts ($) for the five created clusters?

G) Based on your answers to Questions 1d, 1e, and 1f, what reasons do you see for low customer satisfaction ratings?

2. Using XLMiner Platform > Cluster, apply Hierarchical Clustering with the following Selected Variables: Wait Time (min), Purchase Amount ($), Customer Age, and Customer Satisfaction Rating. In Steps 2 and 3 of the XLMiner Hierarchical Clustering procedure, normalize input data and apply Ward’s clustering method with k = 5 clusters.

A) What is thee obtained dendrogram?

B) For each of the five created clusters, find the number of observations and the averages for the four variables. Hint: Using the worksheet HC_Clusters, you may first sort the column Cluster ID, and next calculate these numbers (using e.g. Excel function COUNTIF) and these averages (using Excel function AVERAGE).

C) Based on your findings for Task 2b, what reasons do you see for low customer satisfaction ratings?

D) Provide some recommendations for improving customer satisfaction.

Write a managerial report in MS Word that presents your findings. Do not attach any separate XLMiner and/or Excel outputs. Instead paste into your report the XLMiner outputs with answers to Questions 1a, 1b, 1d, 1e, and 2a, and Excel results related to Task 2b. Showing these outputs/results is crucial because without them I will not be able to verify your findings.

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

Answer 1

As your questions specifically mentioned the educational version of XLMiner available to BUAD 2070 students.

And we don't have educational version answer are somehow not coming, it is not coming ;less than 50. the process is simple, We let you know the process follow in your system the same,

1. Open MS excel > open this table in excel

2. Go to > XLMiner Platform

3. Apply > Cluster, apply K-Means Clustering

4. Select the variables from the table - Wait Time (min), Purchase Amount ($), Customer Age, and Customer Satisfaction Rating as Selected Variables.

5. fill the following in normalize input data, assume k = 5 clusters, 50 iterations, and default Centroid Initialization seed of 12345.