Bridgewater State University $-$ Ricciardi College of Business

MGMT $426$ Spring $24$

Assignment $3$ Statistical Process Control

It is okay to help each other by asking and answering questions about the assignment. However, all spreadsheet work must be completed individually. You should never share your work electronically with other students or submit a spreadsheet file that has been completed or partially completed by others. This is a misrepresentation of work completed and will be considered plagiarism.

All arithmetic necessary to complete assignment problems must be implemented using MS$-$Excel equations and$/$or functions.

Problem $1$

A regional sales manager wants to create an X chart to control customer entertainment expenses in the region. Random samples of $12$ entertainment events were taken during $15$ different weeks. The expenses in thousands of dollars $($$K$)$ for those events are given in the table below. The process standard deviation is unknown. A sample spreadsheet layout is attached at the end of this document.

Create a MS Excel worksheet $$problem $1$ and paste the above data into the worksheet.

In a textbox, briefly explain why an X chart should be used.

In a new column, compute X$-$bar, the sample average, for each week.

In a new column, compute the range for each sample.

Enter the sample size, n$,$ in a clearly labeled cell.

Enter a formula, to compute X$-$bar$-$bar, the mean of the sampling distribution. Make sure the cell is clearly labeled.

Enter a formula, to compute R$-$bar the average range of the samples. Make sure the cell is clearly labeled.

Paste the table at the end of this assignment $($Table X and R Charts when Standard Deviation is unknown$)$ to the right side of your data.

Enter a vlookup function to lookup A$2$ given the sample size. Clearly label the cell A$2.$ The function prototype for vlookup is vlookup$($lookup value, table range, column number with return data$).$ So in this case, the lookup value is the sample size, the table is in the A$2$ worksheet $($select table excluding column and row labels$)$ and the column number is $2.$

Compute the lower $($LCLX $)$ and upper$($UCLX $)$ control limits$.$ Do not put values in formulas. Instead, include references to cells that contain the appropriate values. Clearly label cells.

Create a control chart showing X$-$bar values. Create a new column with the LCL and UCL value for each of the twelve weeks $($e$.$g$.$ values will be repeated since limits are a constant$).$ Select the week column, X$-$bar column. LCL column and UCL column. To select non$-$adjacent cells you need to hold down the control key while selecting data. Choose insert scatter chart. You will need to play with formatting options to get the chart to look nice. Add axes labels. Format X axis to increment week by $1$ and also remove decimal places.

In a textbox, indicate whether the computed controls limits should be used for ongoing control. Explain why or why not..