Reference no: EM133165292
Sales manager of a company wishes to evaluate the performance of the company's sales representatives. Each sales rep. is solely responsible for one sales territory, and the manager decides that it is reasonable to measure performance, Y, of a sales rep. by using the yearly sales of the company product in the representative's sales territory. The manager feels that sales performance substantially depends on eight independent variables:
Time: Number of months the rep. has been employed by the company
MktPoten: Sales of the company's company and competing products in the sales territory, a measure of sales potential
AdvExp: Advertising expenditure in the territory
MktShare: weighted average of the company's market share in the territory for the previous four years
Change: Change in the company's market share in the territory over the previous four years
Accts: Number of accounts handled by representative
WkLoad: Average workload per account based on sizes of order and other workload related criteria
Sales: The dependent variable indicating number of units sold
- Make scatter plot of all variables against Sales (Y-axis). Find the best fitted linear equation and R2 for all 7 scatter plots and explain any significant relationship you may observe.
- Make a correlation coefficient matrix of all 7 variables. Rank variables based on their level of correlation (absolute value) with Sales. Discuss findings based on the correlation matrix.
- Find simple linear regression models of 3 of the highest correlated variables with price (3 models). Observe Multiple R, R-squared, Significant F, P-values and interpret.
- Construct a full 7 variable regression model and formulate model equation to predict sales. Observe Multiple R, R-squared, Significant F, P-values and interpret. Rank the P-values of all 7 independent variables based on the degree of contribution to the model (highest to lowest contribution.)
- Select the top 5 significant variables (lowest p-values) in part 4 and use them in parts 6,7,8, and 9 as indicated.
- Find the best multiple linear regression (based on R2) using two independent variables. Observe Multiple R, R-squared, Significant F, P-values and interpret. Observe the residuals and standard residuals of the model. Use the following guidelines to assign performance rating on scale of 1-7. If the standard residuals is:
-3.00 and lower Rating of 1
-3.00 to -2.00 Rating of 2
-2.00 to -1.00 Rating of 3
-1.0 to 1.00 Rating of 4
+1.00 to 2.00 Rating of 5
2.00 to 3.00 Rating of 6
3.00 or higher Rating of 7
- Find the best multiple linear regression using three independent variables. Observe Multiple R, R-squared, Significant F, P-values and interpret. Observe the residuals and standard residuals of the model. Assign ratings based on table above.
- Find the best multiple linear regression using four independent variables. Observe Multiple R, R-squared, Significant F, P-values and interpret. Observe the residuals and standard residuals of the model. Assign ratings based on table above.
- Find the best multiple linear regression using five independent variables. Observe Multiple R, R-squared, Significant F, P-values and interpret. Observe the residuals and standard residuals of the model. Assign ratings based on table above.
- Use model in part 9 to rank sales rep. performance from highest to lowest. Indicate top 3 and bottom 3 performers.
- Use model in part 9 to find sales forecast for representative with following data:
Time: 36 month MktPoten: 40,000 AdvExp: 30,000 MktShare: 7.5 Change: +3.50
Accts: 100 WkLoad: 18
- Provide brief conclusion of your analysis.