Assignment $2$: Statistical Process Control

Requirements:

Please finish the following questions using Excel with appropriate formulas, and answer the questions $($if any$)$ based on your calculation.

Show your work by using formulas.

All work should be completed in one single Excel file. Please refer to the last page for how to insert separate worksheets in Excel.

Please be noted: there are four questions overall. Please make sure you have answered all of them.

Submission must be uploaded through Blackboard. When submit, please name your file with the format: A$2\_$First name$\_$Last name.

Q$1.$ Construct a p$-$chart

ABC company ships thousands of orders each day from seven national distribution centers. The company wants to establish a p$-$chart to monitor the quality of its distribution process, specifically the number of $$problem$$ orders. A problem order is one that is delivered to a customer late, incorrect or incomplete. The company sampled $500$ orders every other day for $25$ samples and tracked them to final customer delivery, the results of which are as follows.

Based on the information, construct a p$-$chart for this process using $3\backslash$sigma limits and indicate if the process was out of control.

Sample $($k$)$ Number of Problems Orders

$114$

$222$

$336$

$417$

$525$

$641$

$718$

$819$

$927$

$1028$

$1138$

$1224$

$1315$

$1412$

$1510$

$1616$

$179$

$1820$

$1918$

$2012$

$2116$

$2211$

$2330$

$246$

$2518$

Q$2.$ Constructing a c$-$chart

The XYZ company daily delivers multiple orders by truck from its regional distribution center to stores in the ABC Supermarket chain. One measure of its supply chain performance is the number of late deliveries. The company$$s goal is to make all deliveries within one day, so a delivery is late if it exceeds one day. The late deliveries for each of the past $20$ days are as follows:

Construct a c$-$chart for late deliveries with $3$s control limits and indicate if the delivery process was out of control at any time.

Day Late Deliveries

$16$

$215$

$316$

$48$

$519$

$612$

$710$

$814$

$910$

$109$

$116$

$1212$

$1315$

$1411$

$1515$

$1618$

$1715$

$1812$

$1919$

$2023$

Q$3.$ Constructing an x $-$chart AND an R$-$chart

At ABC Super Store, the customer service area processes customer returns, answers customer questions and provides information, addresses customer complaints, and sells gift certificates. The manager believes that if customers must wait longer than $8$ minutes to see a customer service representative they get very irritated. The customer service process has been designed to achieve a customer wait time of between $6$ and $12$ minutes. The store manager has conducted $12$ samples of $6$ observations each of customer waiting over a two$-$week periods as follows:

Construct an x $-$chart in conjunction with an R$-$chart to monitor customer service wait time, and comment on the capability of the service area to meet its designated goal.

Month Response Times $($mins$)$

$18\mathrm{.}64\mathrm{.}56\mathrm{.}25\mathrm{.}710\mathrm{.}111\mathrm{.}5$

$2108\mathrm{.}97\mathrm{.}38\mathrm{.}16\mathrm{.}48\mathrm{.}6$

$35\mathrm{.}39\mathrm{.}410\mathrm{.}210\mathrm{.}25\mathrm{.}87\mathrm{.}8$

$46\mathrm{.}111\mathrm{.}59\mathrm{.}39\mathrm{.}49\mathrm{.}68\mathrm{.}5$

$512\mathrm{.}48\mathrm{.}312\mathrm{.}410\mathrm{.}18\mathrm{.}46\mathrm{.}4$

$69\mathrm{.}710\mathrm{.}47\mathrm{.}58\mathrm{.}5127\mathrm{.}6$

$76\mathrm{.}55\mathrm{.}95\mathrm{.}67\mathrm{.}79\mathrm{.}18\mathrm{.}8$

$815\mathrm{.}66\mathrm{.}610\mathrm{.}611\mathrm{.}57\mathrm{.}510\mathrm{.}6$

$912\mathrm{.}53\mathrm{.}68\mathrm{.}56\mathrm{.}49\mathrm{.}69\mathrm{.}1$

$109\mathrm{.}37\mathrm{.}49\mathrm{.}910\mathrm{.}414\mathrm{.}311\mathrm{.}9$

$1114\mathrm{.}58\mathrm{.}69\mathrm{.}35\mathrm{.}310\mathrm{.}611\mathrm{.}8$

$1210\mathrm{.}39\mathrm{.}611\mathrm{.}27\mathrm{.}56\mathrm{.}98\mathrm{.}1$

Control Chart Factors for Xbar and R Charts

Sample size, n Mean Factor, A$2$ Upper Range, D$4$ Lower Range, D$3$

$21\mathrm{.}883\mathrm{.}2680$

$31\mathrm{.}0232\mathrm{.}5740$

$40\mathrm{.}7292\mathrm{.}2820$

$50\mathrm{.}5772\mathrm{.}1150$

$60\mathrm{.}4832\mathrm{.}0040$

$70\mathrm{.}4191\mathrm{.}9240\mathrm{.}076$

$80\mathrm{.}3731\mathrm{.}8640\mathrm{.}136$

$90\mathrm{.}3371\mathrm{.}8160\mathrm{.}184$

$100\mathrm{.}3081\mathrm{.}7770\mathrm{.}223$

$110\mathrm{.}2851\mathrm{.}7440\mathrm{.}256$

$120\mathrm{.}2661\mathrm{.}7160\mathrm{.}284$

$130\mathrm{.}2491\mathrm{.}6920\mathrm{.}308$

$140\mathrm{.}2351\mathrm{.}6710\mathrm{.}329$

$150\mathrm{.}2231\mathrm{.}6520\mathrm{.}348$

$160\mathrm{.}2121\mathrm{.}6360\mathrm{.}364$

$170\mathrm{.}2031\mathrm{.}6210\mathrm{.}379$

$180\mathrm{.}1941\mathrm{.}6080\mathrm{.}392$

$190\mathrm{.}1871\mathrm{.}5960\mathrm{.}404$

$200\mathrm{.}1801\mathrm{.}5860\mathrm{.}414$

$210\mathrm{.}1731\mathrm{.}5750\mathrm{.}425$

$220\mathrm{.}1671\mathrm{.}5660\mathrm{.}434$

$230\mathrm{.}1621\mathrm{.}5570\mathrm{.}443$

$240\mathrm{.}1571\mathrm{.}5480\mathrm{.}452$

$250\mathrm{.}1531\mathrm{.}5410\mathrm{.}459$

Q$4.$ Capability Ratio & Capability Index

XYZ Company produces a specific part that has a design target of $2\mathrm{.}3$ inches with tolerances of $+0\mathrm{.}6$ inch. The production process that manufactures these parts has a mean of $1\mathrm{.}96$ inches and a standard deviation of $0\mathrm{.}095$ inch. $(1)$ Compute the process capability ratio and process capability index; $(2)$ Determine whether the overall capability of the process meets the design specifications.

$$

How to insert separate worksheets in Excel