brief excel homework
Your company manufactures plastic wrap for food storage. The tear resistance of the wrap, denoted by X, must be controlled so that the wrap can be torn off the roll without too much effort but it does not tear too easily when in use. In a series oftest runs, 15 rolls ofwrap are made under carefully controlled conditions and the tear
resistance of each roll is measured. The results are used as the basis of a quality assurance specification (see Problem 2.23). If X for a subsequently produced roll falls more than two standard deviations away from the test period average, the process is declared out of specification and production is suspended for routine maintenance. The test series data are as follows:
Roll 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 X
134 131 129 133 135 131 134 130 131 136 129 130 133 130 133
(a) Write a spreadsheet to take as input the test series data and calculate the sample mean (X) and sample standard deviation (sX), preferably using built-in functions for the calculations.
(b) The following tear resistance values are obtained for rolls produced in 14 consecutive production runs subsequent to the test series: 128, 131, 133, 130, 133, 129, 133, 135, 137, 133, 137, 136, 137, 139. On the spreadsheet (preferably using the spreadsheet plotting capability), plot a control chart of X versus run number, showing horizontal lines for the values corresponding to X, X 2sX, and X ‡ 2sX from the test period, and show the points corresponding to the 14 production runs. (See Figure 2.5-2.) Which measurements led to suspension of production?
(c) Following the last of the production runs, the chief plant engineer returns from vacation, examines the plant logs, and says that routine maintenance was clearly not sufficient and a process shutdown and full system overhaul should have been ordered at one point during the two weeks he was away. When would it have been reasonable to take this step, and why?