Automobile racing, high-performance driving schools, and driver education programs run by automobile clubs continue to grow in popularity. All these activities require the participant to wear a helmet that is certified by the Snell Memorial Foundation, a not-for-profit organization dedicated to research, education, testing, and development of helmet safety standards. Snell “SA” (Sports Application) rated professional helmets are designed for auto racing and provide extreme impact resistance and high fire protection. One of the key factors in selecting a helmet is weight, since lower weight helmets tend to place less stress on the neck. The following data show the weight and price for 18 SA helmets (Solo Racer website, April 20, 2008).
Use Excel.
Helmet Weight Price
1 64 248
2 64 278
3 64 200
4 64 200
5 58 300
6 47 700
7 49 900
8 59 340
9 66 199
10 58 299
11 58 299
12 52 479
13 52 479
14 63 369
15 62 369
16 54 559
17 63 250
18 63 280
Instruction:
1. write the regression equation,
2. interpret the regression constant and regression coefficient,
3. forecast a value for the dependent variable,
4. test the significant of the regression coefficient with alpha equaling .05,
5. test the overall significant of the regression equation,
6. interpret the coefficient of determination,
7. are there any violations of the general linear model? explain.
To answer these questions using Excel, you can follow these steps:
1. Open Excel and create a new spreadsheet.
2. Enter the given data in two columns, with "Weight" in column A and "Price" in column B.
3. Select a blank cell and click on the "Formulas" tab in the Excel ribbon.
4. Under the "More Functions" drop-down menu, choose "Statistical" and then "LINEST" to calculate the regression equation.
5. In the "Known_y's" field, select the range of the dependent variable (the column of prices, B2:B19).
6. In the "Known_x's" field, select the range of the independent variable (the column of weights, A2:A19).
7. Press Ctrl + Shift + Enter to input the formula as an array formula. This will display the regression equation in the desired cell.
8. The regression equation will then be displayed as y = mx + b, where "y" is the dependent variable (Price), "m" is the regression coefficient (slope), "x" is the independent variable (Weight), and "b" is the regression constant (intercept).
To interpret the regression constant and coefficient:
- The regression constant (b) is the estimated value of the dependent variable (Price) when the independent variable (Weight) is zero. In this case, it represents the estimated price of a helmet with zero weight, which is not practical.
- The regression coefficient (m) indicates the change in the dependent variable (Price) for a one-unit increase in the independent variable (Weight). In this case, it represents how much the price of the helmet changes for each additional unit of weight.
To forecast a value for the dependent variable, you can use the regression equation. Simply plug in a specific value of the independent variable (Weight) into the equation and calculate the corresponding value of the dependent variable (Price).
To test the significance of the regression coefficient and the overall significance of the regression equation:
- Excel does not provide direct tests for significance in the LINEST function output. However, you can calculate the p-values for the regression coefficient using the t-distribution and the degrees of freedom (N-2).
- To test the overall significance of the regression equation, you can use the F-test. This compares the regression sum of squares (SSR) to the residual sum of squares (SSE) in order to determine if the model is statistically significant.
To calculate the coefficient of determination (R-squared), you can use the R^2 formula =SSR/SST, where SSR is the sum of squares due to regression and SST is the total sum of squares. This value ranges from 0 to 1 and represents the proportion of the dependent variable's variability that can be explained by the independent variable.
Regarding violations of the general linear model, check for assumptions such as linearity, independence, constant variance, and normality of residuals. These assumptions can be assessed by examining residual plots and statistical tests, such as the Jarque-Bera test or the Shapiro-Wilk test. If any assumptions are violated, it may affect the validity of the results and interpretations.