Business Research | Homework Help Websites
Assignment 9: Excel & SPSS Linear Regression Analysis
Overview
1. In the first part an example shown for this analysis using the GPAvsGMAT.xls data.
2. In the second part you have to repeat the same steps to for the IQvsGPA.xls
3. You have to provide the same screenshots and results as in the examples that I provided.
1.1. Simple Regression in Excel 2010, 2013, & 2016
To get the Data Analysis tool, first click on File > Options > Add-Ins > Go > Select Data Analysis Toolpack & Toolpack VBA. Data Analysis is now available under Excel’s Data tab. Open the Excel Worksheet GPAvsGMAT.xls and select Data Analysis > Regression. Then fill out the popup window as shown below, specifying GPA as the Y variable and GMAT as the X variable:
This will produce the output:
SUMMARY OUTPUT
Regression Statistics
Multiple R 0.8086001
R Square 0.6538342 Adjusted R Square 0.6346027 Standard Error 0.4350142
Observations 20
ANOVA
df SS MS F Significance F
Regression 1 6.43372807 6.43373 33.9982 1.59668E-05
Residual 18 3.40627193 0.18924
Total 19 9.84
Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
Intercept -1.699561 0.72677682 -2.3385 0.0311 -3.22646284 -0.17266 -3.2264628 -0.17265997
GMAT 0.0083991 0.001440476 5.8308 1.6E-05 0.005372795 0.011425 0.0053728 0.01142545
Note: If you are using Excel 2003 or 2007 version, you get the Data Analysis by following these steps:
1.1b. Simple Regression in Excel 2007
To get the Data Analysis tool in Excel 2007, click on the Office button (top left corner), and select Excell options > Add-Ins > Go > Select Data Analysis Toolpack & Toolpack VBA. Data Analysis is now available under Excel’s Data tab.
1.1c. Simple Regression in Excel 2003
To get the Data Analysis tool in Excel 2003, select Tools > Add-Ins > Select Data Analysis Toolpack & Toolpack VBA. Regression analysis is then available under Tools > Data Analysis
> Regression.
1.2 Repeat these steps for the data of IQvsGPA.xls.
1.2.1 Provide your output as shown above. (12.5 points)
In the second part you have to repeat the same steps to for the IQvsGPA.xls
You have to provide the same screenshots and results as in the examples that I provided.
1.2.2
2. Simple Regression in IBM SPSS.
2.1 Start IBM SPSS Statistics 21, available from the Statistics menu of the standard COB PC configuration. Select File > Open > Data. Select to see Files of type: Excel. Open GPAvsGMAT.xls. Confirm that variable names should be read from the first row of data.
2.1.1 Select Analyze > Regression > Linear. Specify Dependent=GPA, Independent=GMAT.
Select OK.
This will produce the following output:
Regression
Variables Entered/Removed b
| Model | Variables Entered | Variables Removed | Method |
| 1 | GMAT a | . | Enter |
a. All requested variables entered.
b. Dependent Variable: GPA
Model Summary
| Model | R | R Square | Adjusted R Square | Std. Error of the Estimate |
| 1 | .809 a | .654 | .635 | .4350 |
a. Predictors: (Constant), GMAT
ANOVAb
| Model | Sum of Squares | df | Mean Square | F | Sig. | |
| 1 | Regression | 6.434 | 1 | 6.434 | 33.998 | .000(a) |
| Residual | 3.406 | 18 | .189 | |||
| Total | 9.840 | 19 |
a. Predictors: (Constant), GMAT
b. Dependent Variable: GPA
Coefficientsa
| Model | Unstandardized Coefficients | Standardized Coefficients | t | Sig. | |
| B | Std. Error | Beta | |||
| 1 (Constant) GMAT | -1.700
.008 |
.727
.001 |
.809 | -2.338
5.831 |
.031
.000 |
a. Dependent Variable: GPA
2.2. Repeat these steps for the data of IQvsGPA.xls. Provide your output as shown above. (12.5 points)
In the second part you have to repeat the same steps to for the IQvsGPA.xls
You have to provide the same screenshots and results as in the examples that I provided.
