Question
A warehouse supervisor is interested in the relationship between the number of units picked by his employees and their working experience as pickers of stock. Stock picking is the process of picking items off shelves to fill orders for clients. Information on the number of units picked (recorded over the past week) and the length of working experience as a stock picker (in months) is given in the Data spreadsheet of the file MCD2080_A2_T212.xlsx.
All tables, graphs and comments for this question should be placed in the appropriately labelled spaces in the worksheet Results.
(a) Which is the independent (or predictor) variable and which is the dependent (or response) variable in this case? give reasoning for your choice of variables.
(b) Produce a scatter plot of the data. The chart must have an appropriate title and labels. Also you should rescale the graph axes to most clearly display the data. Describe the type of relationship between the variables as shown in the scatterplot. Would a linear regression model be appropriate to model this data?
(c) Using the regression function in Excel, generate regression output for the data and place it where indicated in the Results worksheet.
i. Write down the line of best fit for the given data from the regression output obtained. Clearly indicate the variables used in the equation.
ii. State the value of the intercept of the equation correct to three decimal places and interpret this value. Discuss the reliabillity of the intercept value.
iii. State the value of the slope of the equation correct to three decimal places and give an economic interpretation of this value.
(d) Investigate whether the data provides statistical evidence of a positive linear relationship between the stock picking experience of the employee and the number of units picked per week.
- Give appropriate null and alternative hypotheses. (You are not required to type Greek letters with subscripts. You may use words or symbols instead of Greek letters. For example, H0 can be written as H_0, and β1 as beta_1, etc.)
- State the appropriate p-value for this test from the regression output. If the significance level were 2%, would the null hypothesis be rejected? At what levels of significance will the null hypothesis be rejected?
- Briefly explain what a rejection of the null hypothesis means in this case.
(e) Predict the number of units picked per week (as a whole number) when the amount of stock picking experience is:
- 30 months
- 50 months
Comment on the reliability of each prediction.
(f) State the values of the coefficient of correlation and coefficient of determination correct to three decimal places and explain what information these values give about the relationship and its strength between the variables.