Regression analysis is a tool for building statistical models that characterize relationships among a dependent variable [sales, for example] and one or more independent variables [price, for example]. Find real world data, perhaps from a company's annual report or from government sources, then use Excel to develop a simple or multiple regression analysis with a dependent and an independent variable. Describe, explain, discuss, and comment on the regression's predictive, strategic value. Attach your spreadsheet. REAL WORLD EXAMPLE Take a look at the following spreadsheet which shows the number of hours a student studied and the grades achieved by the students. STUDENTS A B C D E F G H J K L M N O P Q R S T U V HOURS STUDIED 6 7 6.5 8 6.6 8.1 6.8 6.9 7.3 6.9 8.2 7.2 7.3 6.9 8.6 7.4 7.6 6.8 8 7.4 6.5 Regression Statistics Multiple R R Square Adjusted R Square Standard Error Observations GRADE 53 60 56 79 58 85 70 56 69 76 79 68 74 72 84 78 76 65 92 80 65 GRADE 100 90 80 70 60 50 40 30 20 10 0 5 7 9 0.8162 0.6661 0.6476 5.9769 20.0000 ANOVA df Regression Residual Total 1 18 19 Coefficients -26.161299435 13.4604519774 Intercept SS MS F Significance F 1282.7810734463 1282.781 35.908833113 1.1446851853E-005 643.0189265537 35.72327 1925.8 Standard Error t Stat P-value 16.452033061 -1.590156 0.1292092136 2.2462546888 5.992398 1.14469E-005 Lower 95% Upper 95% Lower 95.0% -60.7257382996 8.4031394296 -60.7257382996 8.7412459938 18.179657961 8.7412459938 Upper 95.0% 8.4031394296 18.179657961 RESIDUAL OUTPUT Observation 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Predicted -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 Residuals 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 Regression Equation is given by y = 13.689x - 27.889 Dependent Variable: Grades obtained by the students. Independent Variable: Hours studied R Square : R Square equals 0.6661, which is a good fit. 66% of the variation in Grades is explained by the independent variables Hours studied. The closer to 1, the better the regression line (read on) fits the data. Significance F and P values To check if your results are reliable (statistically significant), look at Significance F (0.001). If this value is less than 0.05, you're OK. If Significance F is greater than 0.05, it's probably better to stop using th independent variables. Delete a variable with a high P-value (greater than 0.05) and rerun the regression until Significance F drops below 0.05. Most or all P-values should be below below 0.05. In our example this is the case. (0.000, 0.129). Coefficients The regression line is: y = Grades = -26.16129 +13.46045 * Hours studied . For each unit increase in Hours of study, Grades increases with 13.46045 units. This is valuable information. Residuals The residuals show you how far away the actual data points are fom the predicted data points (using the equation). For example, the first data point equals 53. Using the equation, the predicted data point equals -26.16129 +13.46045 * 6 =54.60141, giving a residual of 54.60141 - 53 = 1.60141. his set of REAL WORLD EXAMPLE Take a look at the following spreadsheet which shows the number of hours a student studied and the grades achieved by the students. SUMMARY OUTPUT Regression Statistics Multiple R R Square Adjusted R Square Standard Error Observations 0.8162 0.6661 0.6476 5.9769 20.0000 ANOVA df Regression Residual Total 1 18 19 Coefficients -26.161299435 13.4604519774 Intercept SS MS F Significance F 1282.7810734463 1282.781 35.90883 1.1446851853E-005 643.0189265537 35.72327 1925.8 Standard Error t Stat P-value 16.452033061 -1.590156 0.129209 2.2462546888 5.992398 1.14E-005 Lower 95% Upper 95% Lower 95.0% -60.7257382996 8.4031394296 -60.7257382996 8.7412459938 18.179657961 8.7412459938 RESIDUAL OUTPUT Observation 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Predicted -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 Residuals 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 R Square : R Square equals 0.6661, which is a good fit. 66% of the variation in Grades is explained by the independent variables Hours studied. The closer to 1, the better the regression line (read on) fits the data. Upper 95.0% 8.4031394296 18.179657961 REAL WORLD EXAMPLE Take a look at the following spreadsheet which shows the number of hours a student studied and the grades achieved by the students. STUDENTS A B C D E F G H J K L M N O P Q R S T U V HOURS STUDIED 6 7 6.5 8 6.6 8.1 6.8 6.9 7.3 6.9 8.2 7.2 7.3 6.9 8.6 7.4 7.6 6.8 8 7.4 6.5 Regression Statistics Multiple R R Square Adjusted R Square Standard Error Observations GRADE 53 60 56 79 58 85 70 56 69 76 79 68 74 72 84 78 76 65 92 80 65 GRADE 100 90 80 70 60 50 40 30 20 10 0 5 7 9 0.8162 0.6661 0.6476 5.9769 20.0000 ANOVA df Regression Residual Total 1 18 19 Coefficients -26.161299435 13.4604519774 Intercept SS MS F Significance F 1282.7810734463 1282.781 35.908833113 1.1446851853E-005 643.0189265537 35.72327 1925.8 Standard Error t Stat P-value 16.452033061 -1.590156 0.1292092136 2.2462546888 5.992398 1.14469E-005 Lower 95% Upper 95% Lower 95.0% -60.7257382996 8.4031394296 -60.7257382996 8.7412459938 18.179657961 8.7412459938 Upper 95.0% 8.4031394296 18.179657961 RESIDUAL OUTPUT Observation 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Predicted -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 -26.161299435 Residuals 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 26.161299435 Regression Equation is given by y = 13.689x - 27.889 Dependent Variable: Grades obtained by the students. Independent Variable: Hours studied R Square : R Square equals 0.6661, which is a good fit. 66% of the variation in Grades is explained by the independent variables Hours studied. The closer to 1, the better the regression line (read on) fits the data. Significance F and P values To check if your results are reliable (statistically significant), look at Significance F (0.001). If this value is less than 0.05, you're OK. If Significance F is greater than 0.05, it's probably better to stop using th independent variables. Delete a variable with a high P-value (greater than 0.05) and rerun the regression until Significance F drops below 0.05. Most or all P-values should be below below 0.05. In our example this is the case. (0.000, 0.129). Coefficients The regression line is: y = Grades = -26.16129 +13.46045 * Hours studied . For each unit increase in Hours of study, Grades increases with 13.46045 units. This is valuable information. Residuals The residuals show you how far away the actual data points are fom the predicted data points (using the equation). For example, the first data point equals 53. Using the equation, the predicted data point equals -26.16129 +13.46045 * 6 =54.60141, giving a residual of 54.60141 - 53 = 1.60141. his set of