1.1. Simple Regression in Excel (Excel 2010).
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
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 (Excel 2003).
Open the Excel Worksheet GPAvsGMAT.xls and select Tools > Data Analysis > Regression. Then fill out the popup window the same way as shown for Excel 2010. In case Data Analysis is not found under Tools, add it under Tools > Add-Ins.
1.2. Simple Regression in IBM SPSS.
1.2.1. Start IBM SPSS Statistics 20, 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.
1.2.2. 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) -1.700 .727 -2.338 .031
GMAT .008 .001 .809 5.831 .000
a. Dependent Variable: GPA
1.3. Simple Regression in MINITAB.
1.3.1. Start MINITAB 16 for Windows, available from the Statistics menu of the standard COB PC configuration. Select File > Open Worksheet. Select to see Files of type: Excel. Open GPAvsGMAT.xls.
1.3.2. Select Stat > Regression > Regression.
Select OK. This will produce the output:
Regression Analysis: GPA versus GMAT
The regression equation is GPA = - 1.70 + 0.00840 GMAT
Predictor Coef SE Coef T P Constant -1.6996 0.7268 -2.34 0.031 GMAT 0.008399 0.001440 5.83 0.000 S = 0.435014 R-Sq = 65.4% R-Sq(adj) = 63.5%
1.4. Simple Regression in SAS 9.3
Open SAS 9.3. IN the SAS environment, you will need to create a library called BUSI6220 and convert the data file GPAvsGMAT.xls from Excel format to SAS format.
1.4.1. Double-click the yellow libraries icon. Right-click > New. Type BUSI6220 as the name of the new library, and an appropriate folder location in the Path box. Click OK. Your new library should now appear as a new yellow icon.
1.4.2. Import the Excel data file by selecting File > Import Data > MS Excel > Next. Find your file and select it. Select BUSI6220 as the destination library and GPAVSGMAT as the Member name.
1.4.3. Select Solutions > Analysis >Interactive Data Analysis. Select SAS data file BUSI6220.GPAVSGMAT. Click Open.
1.4.4. Select Analyze > Fit. Specify GPA as the Y variable and GMAT as the X variable. Click OK.
GPA = GMAT Response Di st r i but i on: Nor mal Li nk Funct i on: I dent i t y
Model Equat i on
GPA = - 1. 6996 + 0. 0084 GMAT
400 500 600
GMAT 1.5
2.0 2.5 3.0 3.5
G P A
Summar y of Fi t Mean of Response 2. 5000 Root MSE 0. 4350
R- Squar e 0. 6538 Adj R- Sq 0. 6346
Anal ysi s of Var i ance Sour ce
Model Er r or C Tot al
DF 1 18 19
Sum of Squar es 6. 4337 3. 4063 9. 8400
Mean Squar e 6. 4337 0. 1892
F St at 34. 00
Pr > F <. 0001
Type I I I Test s Sour ce
GMAT
DF 1
Sum of Squar es 6. 4337
Mean Squar e 6. 4337
F St at 34. 00
Pr > F <. 0001 Par amet r i c Regr essi on Fi t
Cur ve
Degr ee( Pol ynomi al ) 1
Model DF 1
Mean Squar e 6. 4337
Er r or DF
18
Mean Squar e 0. 1892
R- Squar e
0. 6538
F St at 34. 00
Pr > F <. 0001
1.5b. Find the LSE solution for b
0, b
1, using Excel Solver (Excel 2010).
1.5.1. Start by setting up an Excel worksheet where the squared residuals are calculated. Use arbitrary values for b0 and b1 (the arbitrary solution b0=-1.00 and b1=0.10 is shown below).
1.5.2. Fill in the rest of the worksheet, and place the sum of squared residuals in one of the cells:
To add solver in your Excel, follow similar steps as those involved in adding Data Analysis capabilities. Solver will then be available under Excel’s Data tab.
1.5b.3. Set up a linear program using Solver (Data > Solver). Click Solve and keep the solution.
The results will appear in cells B23 (for b0) and B24 (for b1). Solver will replace the arbitrary values for b0 and b1 with the LSE solution values.
