options(scipen = 999, digits=3)

library("openxlsx")
dfExhibit <- read.xlsx("./GA2_raw_data.xlsx", sheet = "Exhibit 1")  # sample size of 5
dfTable <- read.xlsx("./GA2_raw_data.xlsx", sheet = "Table 1")

1. To estimate process mean and standard deviation for both the X-bar and R charts

xbar_mean <- mean(dfExhibit$Mean)
xbar_sd <- sd(dfExhibit$Mean)

rchart_mean <- mean(dfExhibit$Range)  # That column range is already the max - min
rchart_sd <- sd(dfExhibit$Range)

X-bar mean: 12.004
X-bar standard deviation: 0.029

R-chart mean: 0.135
R-chart standard deviation: 0.035

2. To estimate the UCL and LCL for the X-bar and R charts

# find sample size 5
sampleTableRow <- dfTable[dfTable$Sample.size==5,]

# X-bar UCL, LCL
SE3 <- sampleTableRow$A2 * rchart_mean
xbar.UCL <- xbar_mean + SE3
xbar.LCL <- xbar_mean - SE3
  
# R chart UCL, LCL
rchart.UCL <- rchart_mean * sampleTableRow$D4
rchart.LCL <- rchart_mean * sampleTableRow$D3

X-bar UCL: 12.082
X-bar LCL: 11.925

R-Chart UCL: 0.284
R-Chart LCL: 0

3. To plot both X-bar and R control charts and check if the process is in or out of control.

plot(
  dfExhibit$`Sample.#`,
  dfExhibit$Mean,
  ylim = c(11.8, 12.2),
  xlim = c(0, 30),
  main = "X-bar Control Chart",
  xlab = "Sample Number",
  ylab = "Measurement Value",
  pch = 15,
  col = "coral"
)
abline(h=xbar.UCL, col="blue", )
text(x=28, y=xbar.UCL+0.02, labels="UCL", col="blue")

abline(h=xbar_mean, col="black", )
text(x=28, y=xbar_mean+0.02, labels="Mean, μ")

abline(h=xbar.LCL, col="blue", )
text(x=28, y=xbar.LCL+0.02, labels="LCL", col="blue")

plot(
  dfExhibit$`Sample.#`,
  dfExhibit$Range,
  ylim = c(0, 0.4),
  xlim = c(0, 30),
  main = "R-Chart",
  xlab = "Sample Number",
  ylab = "Measurement Value",
  pch = 15,
  col = "coral"
)
abline(h=rchart.UCL, col="blue", )
text(x=28, y=rchart.UCL+0.02, labels="UCL", col="blue")

abline(h=rchart_mean, col="black", )
text(x=28, y=rchart_mean+0.02, labels="Mean, μ")

abline(h=rchart.LCL, col="blue", )
text(x=28, y=rchart.LCL+0.02, labels="LCL", col="blue")

Conclusion for Q3: Based on the 2 chart, the process is in control because all the sample mean and range, which measures variability, are within the Upper and Lower Control Limit.