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

1. Make scatter plot of all variables against Sales (Y-axis).  Find the best fitted linear equation and R² for all 7 scatter plots and explain any significant relationship you may observe.
2. 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.
3. 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.
4. 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.)
5. Select the top 5 significant variables (lowest p-values) in part 4 and use them in parts 6,7,8, and 9 as indicated.
6. Find the best multiple linear regression (based on R²) 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

1. 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.
2. 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.
3. 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.
4. Use model in part 9 to rank sales rep. performance from highest to lowest. Indicate top 3 and bottom 3 performers.
5. 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

1. Provide brief conclusion of your analysis.