× Assignment 4 Tell me BD Home Insert Draw Page Layout Formulas Data Review View Automate Share Comments Calibri (Body) ✓ 11 ✓ Aˆ A Wrap Text Number Insert ✓ Σ ✓ ✓ ✓ Delete ▼ Paste B I U ✓ ✓ ✓ Merge & Center $ ✓ % .00 →0 Conditional Format Formatting as Table Cell Styles Format ✓ ✓ Sort & Filter Find & Select Analyze Data Sensitivity W29 fx A B C D E F G H J K L M N ° P Q R T U V W X Z AA AB AC AD AE AF AG AH Al AJ AK AL AM AN AO AP AQ AR AS AT AU AV AW AX AY AutoSave OFF り 23456789222222222222222 The Sunny Dale Bank is monitoring the time required to serve customers at the drive-through window, as this service time is an important indicator of how efficiently tellers process customer requests. The bank manager has randomly selected 16 customers during peak hours each day and recorded the time spent serving each one. The table below shows the data collected after four weeks of sampling (i.e., 20 business days). Q1: The manager wants to assess whether the service process is statistically controlled. Using the sample data provided, the manager considers utilizing two control charts (with 99.73% control limits)-one for the variability of service times as sample value and another for the rate of unacceptable service times. A service time is considered "unacceptable" if it exceeds 7 minutes. Help the manager construct these control charts. Your chart should be made by linking to the data on the worksheet, not a picture copied from another source. 10 11 Customers 12 Day 1 2 3456 13 1 14 2 15 3 16 4 17 5 18 6 19 7 20 8 21 9 10 7 8 9 10 11 12 13 14 15 16 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 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 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 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 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.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 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 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 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 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 15 28 16 29 17 30 18 31 19 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 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 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 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 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 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 32 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 33 34 35 36 37 38 39 Q2: To improve service efficiency, the manager replaced a few tellers with more experienced staff. After the change, the manager collected data on service times from 200 customers sampled randomly over several days. Create a distribution chart for this new sample and calculate the mean and standard deviation of the service times. 40 41 42 Service time 43 (minutes) Mean Standard deviation 44 4.2 45 3.5 46 47 4.7 5.1 48 4.3 49 3.0 50 4.2 51 4.1 52 5.8 53 4.8 54 5.5 55 2.3 56 5.5 57 3.5 58 3.9 59 3.0 60 4.5 Q3: Based on the manager's experience, they believe the distribution of service times approximately follows a normal curve. Referring to the data from Q2, the manager claims that 98% of customers will now spend no more than 6 minutes at the drive-through window. Conduct an analysis to either support or refute this claim, considering the overall drive-through service process, not just the sample data. 61 3.4 62 4.5 63 5.2 64 4.0 65 6.4 66 3.7 67 3.7 Problem 1 Problem 2 Problem 3 + Ready Accessibility: Investigate A + 65%