Salesperson Years of Experience Annual Sales ($1000s) 1 1 75 2 3 96 3 4 94 4 4 99 5 7 102 6 8 109 7 9 115 8 10 123 9 11 117 10 14 136 Due to a recent change by Microsoft you will need to open the XLMiner Analysis ToolPak add-in manually from the home ribbon. Screenshot of ToolPak A sales manager collected data on annual sales for new customer accounts and the number of years of experience for a sample of 10 salespersons. In the Microsoft Excel Online file below you will find a sample of data on years of experience of the salesperson and annual sales. Conduct a regression analysis to explore the relationship between these two variables and then answer the following questions. Open spreadsheet Compute b 1 and b 0 (to 1 decimal). b 1 = fill in the blank 2 b 0 = fill in the blank 3 Complete the estimated regression equation (to 1 decimal). = fill in the blank 4 + fill in the blank 5 x According to this model, what is the change in annual sales ($1000s) for every year of experience (to 1 decimal)? fill in the blank 6 Compute the coefficient of determination (to 3 decimals). Note: report r 2 between 0 and 1. r 2 = fill in the blank 7 What percentage of the variation in annual sales ($1000s) can be explained by the years of experience of the salesperson (to 1 decimal)? A new salesperson joins the team with 8 years of experience. What is the estimated annual sales ($1000s) for the new salesperson (to the nearest whole number)?
| Salesperson | Years of Experience | Annual Sales ($1000s) |
| 1 | 1 | 75 |
| 2 | 3 | 96 |
| 3 | 4 | 94 |
| 4 | 4 | 99 |
| 5 | 7 | 102 |
| 6 | 8 | 109 |
| 7 | 9 | 115 |
| 8 | 10 | 123 |
| 9 | 11 | 117 |
| 10 | 14 | 136 |
Due to a recent change by Microsoft you will need to open the XLMiner Analysis ToolPak add-in manually from the home ribbon. Screenshot of ToolPak
A sales manager collected data on annual sales for new customer accounts and the number of years of experience for a sample of 10 salespersons. In the Microsoft Excel Online file below you will find a sample of data on years of experience of the salesperson and annual sales. Conduct a
-
Compute b 1 and b 0 (to 1 decimal).
b 1 = fill in the blank 2
b 0 = fill in the blank 3
Complete the estimated regression equation (to 1 decimal).
= fill in the blank 4 + fill in the blank 5 x
-
According to this model, what is the change in annual sales ($1000s) for every year of experience (to 1 decimal)?
fill in the blank 6
-
Compute the coefficient of determination (to 3 decimals). Note: report r 2 between 0 and 1.
r 2 = fill in the blank 7
What percentage of the variation in annual sales ($1000s) can be explained by the years of experience of the salesperson (to 1 decimal)?
A new salesperson joins the team with 8 years of experience. What is the estimated annual sales ($1000s) for the new salesperson (to the nearest whole number)?
Trending now
This is a popular solution!
Step by step
Solved in 4 steps with 2 images
