A1 Business and Technical College Module Multiple Regression Excel Sheet download the files down below andfollow all the steps pleasethank you>>>>>>>>>>>>a

A1 Business and Technical College Module Multiple Regression Excel Sheet download the files down below andfollow all the steps pleasethank you>>>>>>>>>>>>a The CollegeGPA worksheet contains information about 224 students who entered a
university in a particular year and were planning to major in computer science. The data
were collected to see if information available before students enroll can predict success
in the early university years. The response variable (Y) is a student’s cumulative grade
point average (GPA) after three semesters. Among the predictors are average high
school grades in mathematics (HSM), science (HSS), and English (HSE). We expect
high school grades in these three subjects will help predict College GPA.
Download and go over the attached pdf file for information about multiple regression.
1.
Use Excel to obtain the regression estimates and write the regression equation.
2.
Interpret each regression coefficient.
3.
Make a prediction for a straight-C student’s cumulative grade point average (GPA) when HSM = 4, HSS = 4,
and HSE = 4.
4.
What is the coefficient of determination (R2). Based on the R2, how would you describe the fit of the model?
5.
What is the adjusted R2? Does the model contain useless predictors?
6.
What is the F statistic and its p-value? Based on the p-value, is the regression significant overall at the 0.05
significance level?
7.
Fill the first two columns of the table in the Results worksheet of the CollegeGPA with the t statistic and the pvalue for the t statistic.
8.
Based on the p-value for the t statistic, fill the last column of the table to indicate whether these regression
coefficients are significant or not at the 0.05 significance level.
Include all your answers within the workbook. Rename the assignment file as
YourLastName_Assignment9.xlsx
HSM
10
6
8
9
8
10
8
3
9
7
9
5
6
10
8
10
10
9
9
9
9
8
7
9
10
10
9
8
7
10
6
10
9
10
9
10
9
7
9
10
10
7
10
10
10
HSS
10
8
6
10
9
8
8
7
10
7
10
9
8
9
9
10
10
9
6
10
7
8
9
10
9
9
8
10
8
10
8
10
7
10
7
9
10
4
9
10
7
8
10
7
7
HSE
10
5
8
7
8
8
7
6
8
6
6
7
8
9
6
9
9
8
5
9
8
7
8
8
9
8
7
10
6
10
6
7
4
10
7
9
9
7
9
9
7
9
10
8
5
GPA
3.32
2.26
2.35
2.08
3.38
3.29
3.21
2.00
3.18
2.34
3.08
3.34
1.40
1.43
2.48
3.73
3.80
4.00
2.00
3.74
2.32
2.79
3.21
3.08
3.75
3.16
2.73
3.06
1.07
3.35
1.82
3.12
2.25
2.93
3.16
2.83
3.10
3.07
2.87
3.61
2.17
1.95
3.35
3.58
2.75
10
9
10
10
10
9
10
9
7
10
9
7
10
8
9
7
9
6
9
10
8
9
8
8
10
9
7
8
6
8
9
8
9
8
10
9
9
10
4
10
10
10
7
9
6
9
10
4
10
10
10
10
10
5
8
9
7
7
9
8
10
6
9
8
7
10
7
10
10
8
10
8
8
7
7
6
10
10
7
6
10
7
9
10
3
10
10
9
8
6
6
7
9
7
10
7
9
10
8
7
8
9
8
8
9
7
8
6
9
6
8
10
9
9
8
7
10
7
7
8
7
5
10
8
4
8
10
6
8
8
4
10
10
10
4
6
7
6
3.26
3.41
3.67
3.81
3.30
2.30
3.62
3.20
2.55
2.82
3.25
2.21
2.50
3.03
1.92
4.00
1.81
2.70
2.96
2.76
2.71
3.40
2.65
2.48
3.86
2.62
3.72
3.50
2.83
3.06
4.00
3.70
2.81
1.93
3.70
2.96
2.64
3.09
3.00
2.97
2.81
3.32
3.40
1.84
0.40
2.88
6
5
6
7
10
8
9
7
7
8
6
8
10
9
7
4
8
6
6
8
10
9
10
8
9
6
6
7
5
9
9
8
7
10
8
4
6
9
10
8
10
6
7
10
7
9
5
7
7
10
10
4
9
7
6
7
7
9
10
7
6
5
9
9
7
9
10
8
10
9
6
5
6
6
9
9
9
8
7
10
7
6
5
7
6
7
9
6
10
8
7
9
9
7
9
10
6
3
9
8
7
7
7
7
7
7
7
7
7
9
8
8
10
9
10
10
7
6
6
6
9
9
10
8
7
10
8
6
6
9
6
7
9
6
10
7
8
8
2.77
2.26
2.03
2.43
2.63
1.66
3.41
2.12
3.33
1.69
2.46
1.59
1.14
0.65
2.12
2.82
2.34
2.11
1.34
2.53
2.75
3.14
2.25
1.00
2.79
2.39
2.15
0.75
3.06
2.50
2.78
2.44
1.11
3.12
2.17
0.12
2.00
3.22
1.88
2.04
2.58
2.16
2.50
1.85
1.46
2.95
8
6
9
9
7
6
5
10
10
9
10
9
10
10
8
8
9
10
10
10
9
10
10
7
6
10
8
9
10
10
10
10
9
9
10
10
8
8
10
9
10
7
9
8
4
2
10
5
9
8
7
6
7
10
10
9
9
9
10
10
8
9
8
8
9
8
5
10
10
6
5
10
6
9
8
10
10
10
9
8
10
9
9
9
9
7
10
7
10
10
7
4
9
7
10
7
5
8
7
9
10
9
8
10
9
10
7
8
9
9
10
8
9
10
10
8
6
10
8
10
8
9
9
10
9
9
10
10
10
8
9
9
8
7
10
10
7
6
0.80
0.91
2.67
2.51
1.79
2.42
0.58
3.00
2.76
3.35
3.80
2.38
2.58
3.18
2.87
3.16
3.07
3.68
3.34
1.93
2.43
3.28
3.66
2.29
2.19
3.06
3.41
3.14
2.85
3.47
3.44
3.90
3.65
1.32
3.23
2.86
2.51
2.86
3.34
3.33
3.69
1.80
2.57
2.28
1.60
2.00
7
9
8
9
7
10
9
8
7
10
5
7
9
10
9
7
6
9
8
8
10
9
10
10
6
10
6
9
6
10
8
8
9
6
9
10
9
7
7
5
9
6
10
9
10
10
9
9
7
8
9
4
7
7
10
9
6
6
9
6
8
8
7
8
9
9
10
5
7
6
10
6
7
9
9
4
9
7
7
9
4
8
7
9
8
8
9
9
8
9
6
9
8
8
10
10
10
9
9
10
9
8
9
8
10
10
10
10
7
8
7
10
8
9
10
9
8
10
6
9
7
7
9
1.69
3.06
2.75
2.62
0.39
2.44
3.46
2.37
1.25
2.80
2.14
2.45
2.71
2.59
2.93
2.53
1.95
3.39
2.69
1.94
3.00
2.09
1.85
3.34
2.25
4.00
2.72
2.61
2.32
3.39
3.64
1.80
1.52
3.40
2.86
3.32
2.07
0.85
1.86
2.59
2.28
t statistic
Intercept
HSM
HSS
HSE
p-value
Significant or not?
Multiple Regression
Learning Objectives







Limitations of bivariate regression
Understand multiple regression
Determine a regression using Microsoft Excel
Interpret a regression
Prediction using regression
Assess the fit of a regression
Determine if the regression parameters are
significant
Limitations of Bivariate Regression
In a bivariate regression, a low R2 does not
mean that X and Y are not related
◦ The correct independent variable(s) were not
included
◦ The model may be too simplistic
 The estimates are thus biased
 Bivariate regression is only used when
◦ There is a compelling need for a single model
◦ A single logical predictor ‘stands out’ as doing
a very good job all by itself

Multiple Regression
In an attempt to improve the fit and to specify a
correct model
◦ Add additional predictors (X1, X2, X3,….)
 Multiple or multivariate regression
◦ Extends bivariate regression to include several
independent variables

Data Format for a Multiple Regression
To obtain a fitted regression, we need
◦ n observed values of the response variable Y
and its proposed predictors X1, X2, …, Xk
 A multivariate data set
◦ Single column of Y-values and k columns of Xvalues
 In Excel
◦ You are required to have X data in contiguous
columns

Data Format for a Multiple Regression
Multiple Regression

In the Sales example we went over for the
tutorial
◦ A personnel researcher wanted to find out
how well sales volume of salespersons could
be predicted from a score derived from
demographic information (Biodata Score) and
a Sales Aptitude test score
 Dependent variable – sales
 Independent variables – Biodata scores and
Sales Aptitude test score
Determining a Regression
We will use the Excel output to determine the
intercept and the slope
 Refer the results for the Sales data on next slide
 We write the regression equation

or
Determining a Regression
Interpreting a Regression

Each regression coefficient bj shows
◦ The change in the expected value of Y for a
unit change in Xj, while holding everything else
constant
Interpreting a Regression

For example
◦ Holding everything else constant
 Each additional square foot adds about $171
to the average selling price
 Each additional square feet of lot size adds
$6,780 to the average selling price
 Each additional baths adds $15,530 to the
average selling price
Interpreting a Regression

Looking at the Sales regression line
◦ Holding everything else constant
 Each unit increase in Biodata score adds
$6490 to the average sales
 Each unit increase in Sales Aptitude adds
$2640 to the average sales
Predicting Using Regression
The fitted model is used to make predictions
for various assumed predictor values
 What would be the expected selling price of a
2,800 square foot home with 2-1/2 baths on a
lot with 18,500 square feet?
SqFt = 2800
LotSize = 18.5
Baths = 2.5

Predicting Using Regression

Looking at the Sales regression line

Let’s find out the sales volume of a salesperson
with biodata score of 20 and a sales aptitude
score of 20
Assessing the Fit of a Regression

We assess the fit of a regression in two ways
◦ The coefficient of determination (R2)
◦ Adjusted R2
◦ F-statistic
Coefficient of Determination
Denoted by R2
 Lies in the range 0 ≤ R2 ≤ 1
◦ The closer R2 is to 1, the better the fit of the
regression model
 Often expressed as a percent of variation
explained
 Looking at the Sales regression line
◦ The value for R2 is in cell B5
 R2 = 0.5996 or 59.96%

Coefficient of Determination
Coefficient of Determination

Interpretation
◦ This means that 59.96% of the variation in
sales is explained by the two factors
◦ While this indicates a somewhat good fit
 There is some unexplained variation
◦ Adding more predictors
 Can never decrease the R2
 Will generally raise the R2
◦ However, when R2 is high
 There is not a lot of room for improvement
Adjusted R2
In multiple regression, it is possible to increase
R2 by including additional predictors
◦ This tempts us to believe that we should
include many predictors to get a ‘better’ fit
 To discourage this tactic, called overfitting the
model
◦ An adjustment can be made to the R2 statistic
to penalize the inclusion of useless predictors
 Adjusted coefficient of determination

Adjusted R2
R2adj is always less than or equal to R2
 As predictors are added
◦ R2 cannot decline and will rise
◦ R2adj may rise, remain the same, or fall
 Depending on whether the added
predictors increase R2 enough to offset the
penalty
 If R2adj is substantially smaller than R2
◦ Suggests that the model contains useless
predictors

Adjusted R2
The adjusted R2 can be found in cell B6 of the
Sales data
R2adj = 52.68%
 The R2adj is close to R2
◦ Suggests that the model does not contain
useless predictors

Adjusted R2
F-Statistic for Overall Fit
The F-statistic is a measure of overall fit
◦ A larger F statistic indicates a better fit
 The p-value is used to determine whether the
regression is significant
◦ The regression is significant if the p-value ≤
0.05

F-Statistic for Overall Fit

Looking at the Sales regression line
◦ The F-statistic is in cell E12
 F-statistic = 8.24
◦ The p-value for the F-statistic is in cell F12
 p-value = 0.0065
◦ Since the p-value is less than 0.05
 We conclude that the regression is
significant
F-Statistic for Overall Fit
Are the Parameters Significant?
We can also determine whether the regression
parameters are significant
 The regression parameters are significant if the
associated p-value ≤ 0.05
 We can obtain the t-statistic and p-values for
the slope and intercept from the regression
output from Excel

Are the Parameters Significant?

Looking at the sales regression line
◦ The t-statistic and associated p-values for the
regression parameters can be found in the
range D17:E19
Are the Parameters Significant?
Are the Parameters Significant?
Parameter
Intercept
Biodata Score
Sales Aptitude
t-test
p-value
Significant?
– 0.131197677 0.897987708
No
1.565066696 0.145862558
No
0.901971106 0.386396122
No

Purchase answer to see full
attachment