Question

CAN YOU PLEASE SOLVE THESE IN EXCEL.

a large city hospital conducted a study to investigate the relationship between the number of authorized days that employees are absent per year and the distance between home and work for the employees. A sample of 10 employees was selected and the following data were collected

Distance to work: Number of days absent:

1 8

3 5

4 8

6 7

8 6

10 3

12 5

14 2

14 4

18 2

a.) develop a scatter diagram for these data. Does a linear relationship appear reasonable? Explain.

b.) develop the lease squares estimated regression equation that relates the distant to work to the number of days absent.

c.) Predict the number of days absent for an employee who lives 5 miles from the hospital

d.) Write the regression equation

e.) Interpret the regression constant and regression coefficient

f.) Forecast a value for the dependent variable, test the significant of the regression coefficient at an alpha level of .05, test the overall significant of the regression model, and interpret the coefficient of determination.

Answer 1

Solution:

Select the data in excel

Go to insert>scatter chart

Then click on + to add axis titles and chart title

click on chart styles

You will get

Solution-b:

b.) develop the lease squares estimated regression equation that relates the distant to work to the number of days absent.

In excel install analysis tool pak and then go to

Data>Data analysis>Regression

select X as distance to work

Ya s number of days absent

You will get

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.843121
R Square	0.710854
Adjusted R Square	0.674711
Standard Error	1.289415
Observations	10
ANOVA
	df	SS	MS	F	Significance F
Regression	1	32.69928	32.69928	19.66767	0.002183
Residual	8	13.30072	1.662591
Total	9	46
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	8.097826	0.808822	10.01187	8.41E-06	6.232679	9.962973
Distance to work	-0.3442	0.077614	-4.43482	0.002183	-0.52318	-0.16523

ease squares estimated regression equation that relates the distant to work to the number of days absent. is

Number of days absent=8.097826-0.3442*distance to work

Solution-c

Number of days absent=8.097826-0.3442*distance to work

given distance to work as 5 mils

=8.097826-0.3442*5

=6.376826

  the number of days absent for an employee who lives 5 miles from the hospital is 6 days(rounding to nearest integer)

d.) Write the regression equation

Number of days absent=8.097826-0.3442*distance to work