Question
1. Use Excel's Data Analysis ToolPak and Scatter Plot to analyze the attached Sales Assignment Set 5.xls data. Please provide answers to the following questions
1. Use Excel's Data Analysis ToolPak and Scatter Plot to analyze the attached "Sales Assignment Set 5.xls" data. Please provide answers to the following questions on the same sheet where you have the data. Be sure to answer the question in complete sentences and organize your content appropriately.
The objective is to analyze how a salesperson's annual sales volume is associated with the salesperson's years of experience.
a. Develop a scatter diagram for the given data with sales as the dependent variable and the years of experiences as the independent variable. Show the estimated equation and R-squared on the diagram.
b. Use the Data Analysis ToolPak to run the Simple Linear Regression and show the results on the same Excel Sheet.
c. How do you interpret the "Intercept" you obtained?
d. How do you interpret the coefficient on the variable "Years"?
e. How do you interpret the value of R-square?
f. Use the estimated regression equation to predict annual sales of salesperson with 10 years of experience. Please show your work.
2. Enter the following data on a new sheet within the same Excel file. Then, calculate 0 1 and write down a proper regression line (estimated sample regression function). How do you interpret the 0 1 coefficients?
Please show your work in with the Excel document. You are supposed to use the formula for 0 1 as derived in class, NOT use the Data Analysis ToolPak.
X | Y |
1 | 5 |
2 | 3 |
3 | 4 |
5 | 7 |
6 | 10 |
7 | 9 |
8 | 12 |
8 | 9 |
This is the "Sales Assignment Set 5.xls" that was spoken about prior.