Question

In: Statistics and Probability

As part of the quarterly reviews, the manager of a retail store analyzes the quality of...

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

Solutions

Expert Solution

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.


Related Solutions

As part of the quarterly reviews, the manager of a retail store analyzes the quality of...
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 Data Mining > Cluster,...
Business Analytics, Assignment on Clustering As part of the quarterly reviews, the manager of a retail...
Business Analytics, Assignment on Clustering 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....
Go to a retail store of your choice and introduce yourself to the store manager as...
Go to a retail store of your choice and introduce yourself to the store manager as a retailing student at the University of Mount Olive. Explain that your visit, observations, and questions are part of a class assignment focusing on retailers' loss prevention programs. Develop answers to the following questions and submit a 2-3 page answering them: Are there surveillance cameras? If so, where are they located? Are they monitored continuously or merely there to scare shoplifters? What is the...
Bill Thompson is the new manager of a retail sporting goods store in Vermont that is...
Bill Thompson is the new manager of a retail sporting goods store in Vermont that is part of a national chain. Bill, who is 25 years old, has been working for the company for four years. Before his promotion, he was the assistant manager for two years at a company store in Delaware. Last week his boss, the regional manager, briefly introduced him to the employees. The profit performance of this store is below average for its location, and Bill...
Part 1. Internal Controls Ramona's Clothing is a retail store specializing in women's clothing. The store...
Part 1. Internal Controls Ramona's Clothing is a retail store specializing in women's clothing. The store has established a liberal return policy for the holiday season in order to encourage gift purchases. Any item purchased during November and December may be returned through January 31, with a receipt, for cash or exchange. If the customer does not have a receipt, cash will still be refunded for any item under $75. If the item is more than $75, a check is...
You are the marketing manager for an online retail store, selling shoes. Currently, for every 1000...
You are the marketing manager for an online retail store, selling shoes. Currently, for every 1000 people that visit your website you make 283 sales. You have decided to trail a new website design on 100 visitors to the website to see if the sales rate improves. A) How should the 100 people be selected? Explain why this is important? B) Assuming that the new website design has had no effect, what distribution best describes the number of sales out...
For staffing purposes, a retail store manager would like to standardize the number of checkout lanes...
For staffing purposes, a retail store manager would like to standardize the number of checkout lanes to keep open on a particular shift. She believes that if the standard deviation of the hourly customer arrival rates is 8 customers or less, then a fixed number of checkout lanes can be staffed without excessive customer waiting time or excessive clerk idle time. However, before determining how many checkout lanes (and thus clerks) to use, she must verify that the standard deviation...
Discuss how a manager of a retail store can use both the binomial and Poisson distributions...
Discuss how a manager of a retail store can use both the binomial and Poisson distributions to make business decisions. Explain how the distributions differ. Provide examples of the type of data that could be used in calculating the probabilities.
Scenario You are a manager at a retail pharmacy outlet called One Pharmacy. Your store is...
Scenario You are a manager at a retail pharmacy outlet called One Pharmacy. Your store is in a very socially and culturally diverse suburb. Sometimes your staff members complain that the customers they serve are rude, unreasonable or difficult to understand. You realise your customer service systems may need to be reviewed and updated to best support your staff in serving the needs of your customers 1.1)   Customer service standards should ensure all customers are treated with respect, and staff members...
Scenario You are a manager at a retail pharmacy outlet called One Pharmacy. Your store is...
Scenario You are a manager at a retail pharmacy outlet called One Pharmacy. Your store is in a very socially and culturally diverse suburb. Sometimes your staff members complain that the customers they serve are rude, unreasonable or difficult to understand. You realise your customer service systems may need to be reviewed and updated to best support your staff in serving the needs of your customers 1.1)   Customer service standards should ensure all customers are treated with respect, and staff members...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT