Question

1. Develop a scatter plot with HRS1 (how many hours per week one works) as the dependent variable and age as the independent variable. Include the estimated regression equation and the coefficient of determination on your scatter plot.
2. Does there appear to be a relationship between these variables (HRS1 and age)? Briefly explain and justify your answer.
3. Calculate the slope (b₁) and intercept (b₀) coefficients and use them to develop an estimated regression equation that can be used to predict HRS1 given age. Conduct your analysis using Alpha (α ) of 0.05. Submit your Excel output or workings to receive full points. Hint: Use formulas and Excel, or Excel regression Tool (run a regression with HRS1 as dependent or y variable and age as independent or x variable).
4. Interpret the slope coefficient b₁ (the coefficient for the independent variable, age).

Age	HRS1
58	32
24	46
32	40
29	40
34	86
49	40
60	40
78	25
39	5
67	15
22	40

Please show work in excel thank you

Answer 1

Develop a scatter plot with HRS1 (how many hours per week one works) as the dependent variable and age as the independent variable. Include the estimated regression equation and the coefficient of determination on your scatter plot.

1. Bring data in to excel sheet. select both independent and dependent variable and go to insert tab and choose scatter plot diagram.

2. after populating scatter chart in the excel sheet , select any data point and right click--> add trend line

3. choose "Linear as trend line and select "display an equation on chart" and select display R-squared"

А В с D E J K L O P Format Trendline 1 Age HRS1 2 32 58 Trendline Options 24 46 4 32 40 5 29 40 6 34 86 7 49 40 OExponential O + 8 60 40 Age vs HRS1 9 78 25 O Linear 10 39 5 100 OLogarithmic 11 67 15 90 12 22 40 80 -0.4469x + 57.171 y Polynomial Order 2 13 R2 0.1679 70 14 60 Power 15 50 O... 16 Moving Average 40 Period 2 17 30 18 Trendline Name 19 20 OAutomatic Linear (HRS1) 20 10 21 O Custom 0 22 0 10 20 30 40 50 60 70 80 Forecast 23 Forward periods 0.0 24 Backward periods 25 0.0 26 Set Intercept 0.0 27 Display Equation on chart 28 Display R-squared value on chart 29 30 Sheet1 Ф Ready Average: 40.95454545 Sum: 901 100% Count: 24 90 z I LL

Does there appear to be a relationship between these variables (HRS1 and age)? Briefly explain and justify your answer.

No, thr relationship between Age and HRS shown negative.

fix CORREL(A2:A12,B2:B12) Е5 X В C D E F 1 Age HRS1 2 58 32 24 46 4 32 40 Correlation r= -0.41 29 40 6 34 86 7 49 40 8 60 40 78 25 10 39 5 11 67 15 12 22 40 13 LL LO

Calculate the slope (b₁) and intercept (b₀) coefficients and use them to develop an estimated regression equation that can be used to predict HRS1 given age. Conduct your analysis using Alpha (α ) of 0.05. Submit your Excel output or workings to receive full points. Hint:

Book1- Excel AutoSave Off Prabhu Pandiarajan O Tell me what you want to do Review Share File Home Formulas View Developer Add-ins Help Power Pivot Analysis Analysis Design Insert Page Layout Data Group 明Ungroup Recent Sources L From Text/CSV Clear Reapply Data Analysis + Queries & Connections ШЩ Existing Connections From Web Properties Get From Table/Range Refresh Sort Filter Text to What-If Forecast Geography AJ Stocks BSubtotal SEdit Links 5Advanced Data Analysis Sheet All Columns Analysis Get & Transform Data Oueries & Connections Data Types Sort & Filter Data Tools Outline Forecast F7 A В C D E F Н J K L N O P Format Shape 1 Age HRS1 32 2 58 46 24 4 32 40 Fill 5 29 40 Data Analysis ? X 6 Line 34 86 Analysis Tools 7 49 40 ок No line Covariance 60 40 Descriptive Statistics Exponential Smoothing F-Test Two-Sample for Variances Fourier Analysis Histogram Moving Average Random Number Generation Cancel Solid line 78 25 Gradient line 10 39 Help 11 67 15 12 22 40 13 Rank and Percentile Regression 14 15 16 17 18 19 20

? X Regression Input OK $B$1:$B$12 Input Y Range: Cancel Input X Range: $A$1:SA$12 Help Constant is Zero Labels Confidence Level: 95 Output options SE$2 New Worksheet Ply: New Workbook Residuals Residuals Residual Plots Line Fit Plots Standardized Residuals Normal Probability Normal Probability Plots

fix Chart 2 K A В E F Н L М HRS1 1 Age Age Line Fit Plot 2 SUMMARY OUTPUT 58 32 24 46 100 Regression Statistics Multiple R R Square 32 40 0.409779 29 40 50 6 0.167919 34 86 HRS1 Adjusted R Square Standard Error 7 0.075466 49 40 Predicted HRS1 0 19.71397 60 40 0 20 40 60 80 100 Observations 11 78 25 Age 10 39 5 о 11 ANOVA 67 15 MS 12 df SS F Significance F 22 40 0.210701652 13 Regression 1 705.8711 705.8711 1.816257 14 Residual 9 3497.765 388.6406 Total 15 10 4203.636 t Stat P-value 17 Coefficientsandard Err Lower 95% Upper 95%ower 95.0pper 95.0% Intercept Slope 18 Intercept Age 57.1708 15.97878 3.577919 0.00595 21.02427781 93.31732 21.02428 93.31732 19 -0.44691 0.331612 -1.34769 0.210702 -1.197065536 0.3032491.19707 0.303249 20 21 22 HRS1

Interpret the slope coefficient b₁ (the coefficient for the independent variable, age).

y = -0.4469x + 57.171

Slope: -0.4469x

Intercept: 57.171