Overview
In this activity, you gain experience with regression analysis. Your work here will help prepare you for the Week 7 assignment.
Instructions
In this activity, you examine two scenarios and develop simple regression models that help interpret associated data. You will also make predictions, construct confidence intervals, determine slopes, and validate a null hypothesis.
Download and use the
Week 6 Activity Template [XLSX]
Download Week 6 Activity Template [XLSX]
to read and answer the activity questions.
MAT540 Week 6 Activity Template
Student Name:
Question 1
Table 1 shows data on the total sales generated by the seafood industry and the corresponding jobs support
by the seafood industry in the top 10 states by seafood sales. The data are published by the National Marine
Fisheries Service of the National Oceanic and Atmospheric Administration of the U.S. Department of
Commerce.
Instructions:
Note that you may need to add the linear regression capability to the Excel program on your PC. The
Regression Analysis in Excel blog post may help if you need it: https://www.ablebits.com/office
blog/2018/08/01/linear-regression-analysis-excel/
Use the Question 1 Workspace tab to help complete the following tasks as needed:
1. Develop a simple regression model using the appropriate Excel function to predict the number of jobs
supported by the seafood industry from the total sales generated by the seafood industry of a given state
You will develop an equation with the following structure:
y = a + b1 * X1
where:
y = the number of jobs supported by the seafood industry or the dependent variable
a = intercept
b1 = coefficient of the independent variable – X1
X1 = the total sales generated by the seafood industry or the independent variable
[Enter regression equation and predicted number of jobs here]
2. Imagine that the state of North Carolina (not listed in the table) has seafood sales of $3,000 (million).
Construct a confidence interval for the average number of jobs created by the seafood sales in North
Carolina.
[Enter confidence interval here]
3. Use the t statistic to test to determine whether the slope is significantly different from zero using α = .0
[Enter your answer here]
e corresponding jobs supported
ished by the National Marine
e U.S. Department of
Table 1 – Total sales generated by the seafood industry and
the corresponding jobs supported by the seafood industry
in the top 10 states by seafood sales.
State
gram on your PC. The Linear
s.com/office-addins-
predict the number of jobs
ood industry of a given state .
e dependent variable
dependent variable
d sales of $3,000 (million).
seafood sales in North
erent from zero using α = .05.
Total Sales
Generated by the
Seafood Industry
(in $ millions)
California
22,776
Florida
16,874
Massachusetts
Washington
New Jersey
New York
Alaska
Maine
Texas
Louisiana
7,663
7,464
6,226
4,412
3,895
2,582
2,091
2,022
by the seafood industry and
rted by the seafood industry
by seafood sales.
Jobs Supported by
the Seafood
Industry (1000s)
125
77
87
55
37
33
47
42
22
36
Use as a workspace for Question 1
Question 2
Instructions
Study the following Excel regression output for an analysis attempting to predict the number of union members
in the United States by the size of the labor force for selected years over a 30-year period from data published by
the U.S. Bureau of Labor Statistics. Analyze the computer output.
Use the Question 2 Workspace tab to help complete the following tasks as needed.
1. Write the equation for the best-fit regression line from the results of this regression.
[Enter the equation here]
2. State the null hypothesis for this regression and perform the necessary calculations to determine the
validity of the hypothesis.
[Enter the null hypothesis]
[Enter calculations and validity claim here]
3. Use the equation of the regression line to predict the number of union members when the labor force is
110,000.
[Enter your prediction here]
***Important Note: The model was developed with data already recorded in 1000 units. Use the data in the
model as is.***
umber of union members
od from data published by
s to determine the
hen the labor force is
its. Use the data in the
Table 2 – Regression Output
Multiple R
R Square
Adjusted R Square
Standard Error
Observations
0.798
0.636
0.612
258.632
17
ANOVA
Regression
Residual
Total
df
1
15
16
SS
1756035.529
1003354.471
2759390
Coefficients
Intercept
Labor Force
20704.3805
-0.039
MS
1756036
66890.3
Standard
Error
879.6067
0.0076
F
26.25
t Stat
23.54
-5.12
Significance F
0.00012
P-value
0
0.00012
Use as a workspace for Question 2
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
