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

The ABC Banking Corporation issues a national credit card through its various bank branches in five southeastern states. The bank credit card business is highly competitive and interest rates do not vary substantially, so the company decided to attempt to retain its customers by improving customer service through a reduction in billing errors. The credit card division monitored its billing department process by taking daily samples of $300$ customer bills for $25$ days and checking their accuracy. The sample results are as follows:

Develop a p$-$chart for the billing process using $3-$sigma $($z $=3)$ control limits and indicate if the process is out of control.

$$

$$

$.$

$$

Control Chart Factors for x$-$bar and R Charts

Sample size, n

Mean Factor, A$2$

Upper Range, D$4$

Lower Range, D$3$

$2$

$1\mathrm{.}88$

$3\mathrm{.}268$

$0$

$3$

$1\mathrm{.}023$

$2\mathrm{.}574$

$0$

$4$

$0\mathrm{.}729$

$2\mathrm{.}282$

$0$

$5$

$0\mathrm{.}577$

$2\mathrm{.}115$

$0$

$6$

$0\mathrm{.}483$

$2\mathrm{.}004$

$0$

$7$

$0\mathrm{.}419$

$1\mathrm{.}924$

$0\mathrm{.}076$

$8$

$0\mathrm{.}373$

$1\mathrm{.}864$

$0\mathrm{.}136$

$9$

$0\mathrm{.}337$

$1\mathrm{.}816$

$0\mathrm{.}184$

$10$

$0\mathrm{.}308$

$1\mathrm{.}777$

$0\mathrm{.}223$

$11$

$0\mathrm{.}285$

$1\mathrm{.}744$

$0\mathrm{.}256$

$12$

$0\mathrm{.}266$

$1\mathrm{.}716$

$0\mathrm{.}284$

$13$

$0\mathrm{.}249$

$1\mathrm{.}692$

$0\mathrm{.}308$

$14$

$0\mathrm{.}235$

$1\mathrm{.}671$

$0\mathrm{.}329$

$15$

$0\mathrm{.}223$

$1\mathrm{.}652$

$0\mathrm{.}348$

$16$

$0\mathrm{.}212$

$1\mathrm{.}636$

$0\mathrm{.}364$

$17$

$0\mathrm{.}203$

$1\mathrm{.}621$

$0\mathrm{.}379$

$18$

$0\mathrm{.}194$

$1\mathrm{.}608$

$0\mathrm{.}392$

$19$

$0\mathrm{.}187$

$1\mathrm{.}596$

$0\mathrm{.}404$

$20$

$0\mathrm{.}180$

$1\mathrm{.}586$

$0\mathrm{.}414$

$21$

$0\mathrm{.}173$

$1\mathrm{.}575$

$0\mathrm{.}425$

$22$

$0\mathrm{.}167$

$1\mathrm{.}566$

$0\mathrm{.}434$

$23$

$0\mathrm{.}162$

$1\mathrm{.}557$

$0\mathrm{.}443$

$24$

$0\mathrm{.}157$

$1\mathrm{.}548$

$0\mathrm{.}452$

$25$

$0\mathrm{.}153$

$1\mathrm{.}541$

$0\mathrm{.}459$

Q$4.$ Capability Ratio & Capability Index

The Johnson$$s $75-$watt light bulbs are designed to have a life of $1050$ hours with tolerances of $+150$ hours. The process that makes light bulbs has a mean of $900$ hours, with a standard deviation of $40$ hours. Compute the process capability ratio and the process capability index, and comment on the overall capability of the process.

Please provide the answers to each question in a excel file