The Sunny Dale Bank monitors the time required to serve customers at the drive-through window because this service time is considered as one indication of whether their tellers have been effectively processing the customer's request. The manager has randomly chosen 16 customers during the peak hours each day and recorded the service times (in minutes) spent in each of them. The following table shows the data collected after 4 weeks of sampling (i.e., 20 business days). Answer the following questions.
Q1: The manager would like to study whether the process of meeting customers' needs is in statistical control. With the sample data below, the manager considers using average service time and the rate (can be a fraction or equivalent) of unacceptable service time as sample value, respectively, to prepare two different control charts (with 99.73% control limits). Help the manager make the charts. Note that service time at the drive-through window is considered as "unacceptable" if it is longer than 7 minutes.

 

	Customers
Day	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16
1	5.5	4.3	7.9	6.6	4.4	5.2	4.0	3.0	5.7	6.0	4.0	5.6	2.8	3.4	4.6	5.6
2	6.6	4.1	2.0	4.0	6.5	5.3	4.4	5.5	5.6	2.8	6.4	1.9	3.2	3.7	6.1	8.8
3	4.2	3.4	6.3	4.0	3.1	5.5	4.3	8.5	4.1	4.6	4.5	5.1	3.3	5.9	7.8	5.0
4	6.3	3.1	4.9	5.5	6.4	3.8	4.2	3.2	4.8	3.9	5.5	7.1	4.5	4.8	5.8	3.8
5	4.9	5.2	7.1	6.9	3.9	5.3	3.8	2.6	4.7	4.2	3.7	5.7	4.1	4.5	4.4	6.7
6	6.5	6.4	5.4	6.4	2.7	6.3	4.8	6.8	4.8	5.8	4.3	4.4	3.5	4.8	4.4	4.8
7	5.9	8.2	4.6	6.7	4.2	3.2	4.1	4.3	7.3	3.2	4.2	4.1	3.9	2.3	6.2	4.0
8	3.3	3.3	3.5	3.8	2.4	4.4	2.1	4.9	6.0	3.9	3.0	6.4	3.9	4.1	2.9	4.2
9	4.8	3.5	4.1	3.7	4.0	7.0	4.9	6.4	2.5	7.6	3.9	3.9	3.4	4.8	5.9	5.2
10	4.9	6.2	4.6	6.2	4.2	5.6	4.2	5.7	5.3	5.2	7.2	5.8	2.2	3.4	5.6	3.5
11	6.3	5.6	3.4	4.0	3.7	6.6	6.0	6.5	3.9	5.6	5.5	5.2	4.3	2.8	5.4	4.7
12	6.8	6.4	5.9	3.4	6.9	4.2	6.9	7.5	3.2	4.6	5.3	2.8	6.2	5.3	6.0	9.0
13	7.6	7.3	7.2	4.1	6.4	4.1	6.0	2.3	5.9	5.8	6.6	6.1	9.0	3.5	5.2	3.6
14	3.9	4.9	4.5	5.6	6.7	5.3	6.2	4.0	5.8	4.2	6.5	6.7	4.8	4.0	7.6	6.1
15	6.7	5.7	5.7	3.8	5.8	3.3	4.2	6.9	6.9	4.2	5.2	2.6	7.5	5.1	6.1	3.5
16	6.6	4.9	7.2	3.8	3.5	2.9	6.2	4.2	4.7	2.4	6.1	6.3	6.8	3.4	7.2	6.5
17	5.1	3.5	5.1	2.2	2.8	4.5	6.7	6.1	6.2	5.2	5.7	6.8	4.7	2.2	5.7	6.8
18	7.0	3.2	4.6	2.5	8.2	4.2	5.4	7.5	5.1	4.7	7.3	6.0	2.8	4.9	5.3	5.2
19	4.1	3.5	1.8	4.3	3.0	4.6	3.1	3.8	3.1	2.2	4.2	5.4	7.5	4.3	4.4	2.5
20	7.6	5.3	3.5	3.3	5.9	4.7	5.7	3.9	4.3	5.2	7.1	4.8	4.3	6.8	6.0	5.3

Q2: The manager replaced a couple of new tellers by more experienced ones with the attempt to improve the service time at the drive-through window. He again did random sampling of customers during the day and recorded the service times for each customer. He has collected the data each day for a few days. Below is the data showing 200 service times. Make a proper chart that can show the distribution of the service times. Also, calculate the mean and standard deviation for this sample data of service times.

 

Service time (minutes)

4.2

3.5

4.7

5.1

4.3

3.0

4.2

4.1

5.8

4.8

5.5

2.3

5.5

3.5

3.9

3.0

4.5

3.4

4.5

5.2

4.0

6.4

3.7

3.7

4.3

3.8

2.4

4.3

3.1

4.1

3.0

3.9

2.9

3.8

6.0

4.6

2.8

3.6

5.5

3.2

4.8

6.4

2.8

4.2

3.6

3.8

4.1

5.3

4.3

2.9

4.2

2.9

3.2

5.6

4.5

5.2

2.4

5.1

3.1

3.8

3.3

2.8

3.7

2.8

3.7

4.2

4.8

3.0

3.7

4.2

3.8

4.0

4.9

4.6

3.4

4.7

4.4

5.7

3.1

4.3

4.8

4.6

4.2

5.2

4.5

2.4

4.3

6.9

6.5

3.5

5.3

5.6

3.6

4.8

5.3

3.8

4.6

4.5

3.8

6.7

4.7

4.8

3.7

3.7

4.9

3.9

4.2

3.5

5.0

3.1

4.1

3.7

4.2

5.7

5.1

3.6

4.1

3.7

4.5

6.4

5.2

4.4

4.1

4.9

3.0

5.0

5.3

6.0

4.1

4.2

4.1

3.4

5.5

2.6

3.8

4.6

4.2

1.8

4.7

3.2

5.2

4.6

1.5

5.1

4.3

3.9

4.1

4.2

5.8

5.8

5.3

3.5

4.6

5.3

4.7

2.9

3.7

4.3

3.7

2.5

2.7

5.2

3.8

4.8

4.3

3.1

5.4

2.9

3.8

2.7

4.1

5.8

5.3

4.9

2.0

4.1

3.1

6.3

5.9

3.6

5.9

5.9

2.5

4.9

4.5

3.9

3.9

2.9

5.3

5.2

2.1

4.7

3.5

4.3

3.5

5.2

3.8

5.5

3.3

3.8

Q3: Continued from Q2, after his improvement on the service time at the drive-through window, the manager confidently claimed that 98% of the customers would spend no more 6 minutes at the window. The manager also observed that the distribution of service time appears like a normal distribution. With this observation, show your analysis in support of, or against the manager's claim.

Please show excel formulas and solutions.