A1 Business and Technical College Two Variable Regression Excel Worksheet download the files down below andfollow all the steps pleasethank you>>>>>>>>>>>>>>>>>>>>a Career airline pilots face the risk of progressive hearing loss, due to the noisy cockpits
of most jet aircraft. Much of the noise comes not from engines but from air roar, which
increases at high speeds. To assess this workplace hazard, a pilot measured cockpit
noise at randomly selected points during the flight by using a handheld meter. Noise
level (in decibels) was measured in seven different aircraft at the first officer’s left ear
position using a handheld meter. For reference, 60dB is a normal conversation, 75 is a
typical vacuum cleaner, 85 is city traffic, 90 is a typical hair dryer, and 110 is a chain
saw. The workbook ‘Module 8 Assignment’ shows 61 observations on cockpit noise
(decibels) and airspeed (knots indicated air speed, KIAS) for a Boeing 727, an older
type of aircraft lacking design improvements in newer planes. The goal of this analysis
is to determine whether airspeed predicts cockpit noise.
Download and go over the attached pdf file for information about bivariate or twovariable regression.
1.
Identify the independent and dependent variables.
2.
Use Excel to obtain the regression estimates.
3.
Write the regression equation.
4.
Interpret the intercept and the slope.
5.
Make a prediction of Cockpit Noise when Airspeed = 200 and Airspeed = 300.
6.
What is the R2? Based on the R2, what can you say about the fit of the regression model? Interpret the R2.
7.
What is the F-statistic for the regression? What is the associated p-value? Is the regression significant?
8.
What is the t-statistic for the intercept? What is the associated p-value? Is the intercept significant?
9.
What is the t-statistic for the slope? What is the associated p-value? Is the slope significant?
Include all your answers within the workbook. Rename the assignment file as
YourLastName_Assignment8.xlsx
Airspeed
250
340
320
330
346
260
280
395
380
400
335
380
380
390
400
400
405
320
310
250
280
320
340
340
380
385
420
230
340
250
320
340
320
330
360
370
380
395
365
320
250
250
320
305
350
Noise Level
83.0
89.0
88.0
89.0
92.0
85.0
84.0
92.0
92.0
93.0
91.0
93.0
91.0
94.0
95.0
96.0
97.0
89.0
88.5
82.0
87.0
89.0
90.0
91.0
96.0
96.0
97.0
82.0
91.0
86.0
89.0
90.0
90.0
91.0
94.0
94.5
95.0
96.0
91.0
88.0
85.0
82.0
88.0
88.0
90.0
380
310
295
280
320
330
320
340
350
270
272
310
350
370
405
250
92.0
88.0
87.0
86.0
88.0
90.0
88.0
89.0
90.0
84.0
84.5
88.0
90.0
91.0
93.0
82.0
Two-Variable Regression
Learning Objectives
Understand bivariate regression
Determine a regression using Microsoft Excel
Interpret a regression
Prediction using regression
Assess the fit of a regression
Determine if the intercept and slope are
significant
Introduction
Understanding how different business variables
are related to each other helps us predict, and
hopefully, improve our business performance
Examples of quantitative variables that might be
related to each other
◦ Spending on advertising and sales revenue
◦ Produce delivery time and percentage of
spoiled produce
Introduction
With these pairs of variables
◦ There is one variable that we want to predict
Examples
◦ Do advertising expenditures predict quarterly
sales revenue?
◦ Does the number of dependents predict
employee prescription drug expenses?
◦ Does the apartment size predict monthly
rent?
Bivariate Regression
Bivariate regression model
◦ Specifies one dependent variable (called the
response) and one independent variable
(called the predictor)
Example
◦ Do advertising expenditures predict quarterly
sales revenue?
Dependent variable – quarterly sales
revenue (what we are trying to predict)
Independent variable – advertising
expenditures
Bivariate Regression
In the GPA example we went over for the
tutorial
◦ A university is interested in finding out how
well ACT scores predict first-semester GPA
of its freshmen
Dependent variable – first-semester GPA
Independent variable – ACT scores
Determining a Regression
Two parameters are of importance:
◦ The intercept
◦ The slope
The assumed model for a linear relationship
y = Intercept + Slope*x
Determining a Regression
We will use the Excel output to determine the
intercept and the slope
Refer the results for the GPA data on next slide
The intercept is the value in the cell B17
◦ Intercept = 0.72
The slope is the value in the cell B18
◦ Slope = 0.11
We write the regression equation
◦ y = 0.72 + 0.11x or
◦ GPA = 0.72 + 0.11ACT
Determining a Regression
Intercept
Slope
Interpreting a Regression
The intercept and slope of a fitted regression
can provide useful information
For example
Sales = 268 + 7.37Ads
◦ Each extra $1 million of advertising will
generate $7.37 million of sales on average
◦ The firm will average $268 million of sales
with zero advertising
Interpreting a Regression
Looking at the GPA and ACT regression line
GPA = 0.72 + 0.11ACT
◦ Each unit increase in ACT will lead to an
increase of 0.11 in GPA on average
◦ A score of 0 on the ACT will average a GPA
of 0.72
Predicting Using Regression
One of the main uses of regression is to make
predictions
Once we have a fitted regression equation
◦ We can plug in any value of X to obtain the
prediction for Y
For example
Sales = 268 + 7.37Ads
◦ If the firm spends $10 million on advertising
Sales = 268 + 7.37(10) = $341.7 million
Predicting Using Regression
Looking at the GPA and ACT regression line
GPA = 0.72 + 0.11ACT
If a student has an ACT score of 28
GPA = 0.72 + 0.11(28) = 3.8
Assessing the Fit of a Regression
We assess the fit of a regression in two ways
◦ The coefficient of determination
◦ 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 GPA and ACT regression line
◦ The value for R2 is in cell B5
R2 = 0.7807 or 78.07%
Coefficient of Determination
Coefficient of Determination
Interpretation
◦ X (ACT) explains 78.07% of the variation in Y
(GPA)
◦ On the other hand, 21.93% of the variation in
GPA is not explained by ACT
The unexplained variation reflects factors
that have not been included in the model,
such as reading skills, study hours, and so on
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 GPA and ACT regression line
◦ The F-statistic is in cell E12
F-statistic = 46.28
◦ The p-value for the F-statistic is in cell F12
p-value = 0.00001
◦ Since the p-value is less than 0.05
We conclude that the regression is
significant at the 0.05 significance level
F-Statistic for Overall Fit
Are the Parameters Significant?
We can also determine whether the intercept
and the slope are significant
The intercept and the slope 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 GPA and ACT regression line
◦ The t-statistic for the intercept is in cell D17
t-statistic = 2.06
◦ The p-value for the F-statistic is in cell E17
p-value = 0.06
◦ Since the p-value is greater than 0.05
We conclude that the intercept is not
significant at the 0.05 significance level
Are the Parameters Significant?
Are the Parameters Significant?
Looking at the GPA and ACT regression line
◦ The t-statistic for the slope is in cell D18
t-statistic = 6.80
◦ The p-value for the F-statistic is in cell E18
p-value = 0.00001
◦ Since the p-value is less than 0.05
We conclude that the slope is significant at
the 0.05 significance level
Notice that the p-value for F-statistic is the
same as for the slope’s t statistic
Purchase answer to see full
attachment