The production manager of American Tool and Castings Company is conducting a study regarding the relationship between the number of alloy caps milled on a lathe versus the measure of distance from specification of outside cap diameters. The lathe uses a sharp steel cutting tool in a milling process to cut and shape raw alloy bars into caps. The lathe tool turns at a high speed while cutting into the alloy, in essence, cutting the alloy down to size and shaping it to resemble a round cap. A similar lathe tool cuts into the inside of the cap. The caps are later fit with interior gaskets and permanently sealed onto airtight canisters. After the steel cutting tool is used repeatedly, the tool begins to wear, hence cutting a larger outside cap diameter than desired. If the outside cap diameter is too large the cap cant be properly affixed and sealed to the canister. The production manager would like to build a model to estimate/predict how many caps a tool can mill until it wears down too much, hence milling caps that are too large in diameter and unusable. Each cap costs approximately $400 to mill, so defective caps are expensive. The main variable of interest (y) is distance from specification of outside cap diameter. To conduct the study, 62 lathe tools were randomly sampled. Each lathe operator keeps a record of the number of caps milled by particular tool. Each cap milled is measured to see how close to specification the outside diameter is. According to specification, each cap should be 6 inches in diameter. For example, the measure in record one is 0.36, meaning it was 0.36 inches larger than specification. When each tool was sampled, the number of caps milled by the tool was recorded, as well as a measure from specification of the diameter of the last cap milled by that particular tool. The data for each cutting tool sampled and the measure of distance from specification of the outside cap diameter of the last cap milled is in the spreadsheet labeled American. a. (4 points) Scatterplot Construct a scatterplot revealing the relationship between the number of caps milled by a tool and the distance from specification of the outside cap diameter. Make sure the variable is on the -axis and the variable is on the y-axis. Move the 1 chart so that it starts in cell E3. Do not resize the chart beyond the red shaded region. b. (6 points) Correlation (2 points) Using a built-in Excel function in cell F22, calculate the correlation (r) between the number of caps milled by a tool and the distance from specification of the outside cap diameter. (2 points) In cell F23, indicate the strength of the linear relationship as very strong, relatively strong, very weak, relatively weak, or no relationship. (2 points) In cell F24, indicate if the relationship is positive or negative. c. (4 points) Generate and place the Excel regression output in cell O3. Make sure you select an appropriate Residual Plot, and place the residual plot in the designated area near cell E30. d. (6 points) Output (2 points) In cells J23 and J24, enter the value of the intercept and slope (respectively) by referencing the appropriate cells in the regression output. (1 point) In cell K23, enter the value of the test statistic for testing the slope significance by referencing the appropriate cell from the regression output. (1 point) In cell L23, enter the p-value regarding the slope significance by referencing the appropriate cell from the regression output. (2 points) In cell M23, indicate with the word Yes or No if the slope coefficient is significant. Assume =0.01. e. (2 points) In cell G26, provide the predictive power (a.k.a. the coefficient of determination) of the model by referencing the appropriate cell from the regression output. f. (4 points) In cell G27, write the prediction equation relating NM to DS using the intercept and slope values. g. (6 points) (2 points) Cell E30 should contain the residual plot. Keep the plot within the red shaded area. (2 points) In cell F47, comment on the assumption of linearity as interpreted using this residual plot. (2 points) In cell F48, comment on the assumption of constant variance as interpreted using this residual plot. h. (6 points) Prediction and Residual (2 points) In cell F53, predict the distance from specification of a cap milled by a tool when the cap is the 20th cap to be milled. (4 points) In cells F54 and F55, calculate the lower limit (LL) and upper limit (UL) for the range of this data set. 2 i. (12 points) Use StatTools to do the regression and place StatTools output in cell A1 of the Regression Worksheet.

Please help me with the excel formula.