4. The table below gives the total number of hours (X ) in a day spent by a number of salesmen in marketing a certain product and the corresponding total sales (Y in thousands) due to the salesmen.

X 1.0 ,2.0 ,3.0 ,4.0 ,5.0 ,6.0 ,7.0

Y 11.5, 11.8,14.8, 13.2,14.4,16.5, 16.9

Using Excel

Compute the correlation coefficient between the number of hours spent by a number of salesmen in marketing and total sales. Explain your result

ii) Obtain a scatter diagram of number of hours and total sales

iii) Obtain the least-squares estimates for the regression line = 0 + 1 using the Excel SLOPE and INTERCEPT functions.

iv) Carry out the analysis using Excel's built-in regression tool

Fit a regression equation of the form Y = a + bX to this data. Interpret your results

vi) Use the equation in above to predict the total sales at x = 5.5, hours and x = 11.5 hours