Question

Use Excel to solve problem three. If you are submitting a hard copy of your exam, print out your Excel spreadsheet and return that along with your exam. If you are submitting your exam through email or Blackboard Messages, save your Excel spreadsheet as a file and attach that file to your submission. Do not take pictures of your computer screen showing your Excel worksheet and then submit those, as those images are very difficult to read and will not be graded. A researcher is hired to investigate the relationship between the number of unauthorized days that employees are absent per year and the distance in miles between home and work for the employees. A sample of 10 employees was chosen and the results are listed in the chart below. Use the proper Data Analysis function in Excel to compute the regression equation needed to predict the number of missed days that could be expected for employees who live certain distances away from the workplace. Compute the predicted number of days absent for an employee who lives 7 miles away and for an employee who lives 13 miles away. Distance to Work (miles) Number of Days Absent

Answer 1

Solution:

We have given information,
Distance to work(X)	Number of days absent(Y)
1	8
3	5
4	8
6	7
8	6
10	3
12	5
14	2
14	4
18	2
Let Y be the dependent variable (number of days absent) and X be the independent variable(distance to work).
The sample regression equation is given as,
Y = a + b*X + e
Here, a is the intercept coefficient and b is the slope coefficient.
and e is an error term
The OLS regression line is given as,
Y = a + b*X
The OLS regression line is obtained by the following Excel steps:
1. click the data tab in the ribbon and then click on data analysis tab.
2. select regression and select independent and dependent variable data
3. put the confidence level and output range then we get the following values
SUMMARY OUTPUT
Regression Statistics
Multiple R	0.843121469
R Square	0.710853812
Adjusted R Square	0.674710539
Standard Error	1.289414821
Observations	10
ANOVA
	df	SS	MS	F	Significance F
Regression	1	32.69927536	32.69927536	19.66766549	0.002182936
Residual	8	13.30072464	1.66259058
Total	9	46
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	8.097826087	0.808822137	10.01187494	8.4133E-06	6.232678894	9.96297328	6.232678894	9.96297328
Distance to work(X)	-0.344202899	0.077613652	-4.434824178	0.002182936	-0.5231803	-0.165225497	-0.5231803	-0.165225497
The estimated regression line is given as,
Y = 8.0978 - 0.3442* X
The predicted number of days when the value of X = 7 and X = 13 is given as,
Y (predicted number of absent days)	X (distance in miles)
5.6884	7
3.6232	13
Therefore, the required predicted value in the above table.