Quantitative Methods of Business

Instructions: Format all numbers appropriately with 2 decimal places. Name ranges.

1. Students in a management science class have just received their grades on the first test. The instructor has provided information about the first test grade in some previous classes as well as the final average for the same students. Some of these grades have been sampled and are as follows:

Student            1      2      3      4      5      6      7      8      9
1st test grade   98   77    88    80    96    61    66    95    69
Final Average   93   78    84    73    84    64    64    95    76

2. In EXCEL, indicate which variable is dependent and which is independent. Also indicate which is variable is the X and which is Y.
a) Do a scatterplot of the above data to see whether a linear equation might describe the relationship between the 1st test grade and the Final Average.
b) Add a Linear Trend Line
c) Display the equation and R-squared value on the chart
d) Below the scatterplot calculate the correlation between the two variables.
e) Name the chart and the two axes appropriately.
f) Change the appearance of the data points to an X.
g) Below the scatterplot, in your own words describe the relationship between the 1st Test Grade and the Final Average.
h) Paste list of variable names on the scatterplot worksheet.
i) Name the scatterplot worksheet, “Scatterplot”

3. On the second worksheet, do all of the calculations in EXCEL that were done in class. Ymean, Xmean, (X-Xmean), (X-Xmean)^2, (Y-Ymean), (X-Xmean)(Y-Ymean), Y^, SST, SSE, SSR, R^2, R, and Standard Error of Estimate.
a. Calculate slope and intercept based on the above EXCEL calculations. Name the second worksheet “Calculations”.
b. Using EXCEL and the information that you just calculated, what is your Final Average estimate if the 1st Test Grade is 85?

4. On the third worksheet, using EXCEL’s built in formulas
a) Calculate the mean for each variable, correlation, slope, intercept, Standard Error of Estimate, and the Coefficient of Determination.
b) Using EXCEL and the information that you just calculated, what is your Final Average estimate if the 1st test grade is 67?
c) Using the Forecast formula what is your Final Average estimate if the 1st Test Grade is 43?
d) Name the third worksheet “Built In”.

5. On the fourth worksheet, run Regression using the Data Analysis addin.
a) Use the information to estimate the linear regression model?
b) Include the Residual Plots
c) Do the residuals violate any of the assumptions discussed in class?
d) Interpret each of the estimated regression coefficients of the regression model.
e) Using Appendix D, is this regression model a good predictor based on the Significant F?
f) Is your regression model (Ŷ) the same as in 2, 3 and 4 above?
g) Identify and interpret the coefficient of determination R2 for the model.
h) Identify and interpret the standard error of the estimate (se) for the model.
i) How might the instructor do a better job explaining the variation in the Final Average?
j) What is your Final Average estimate if the 1st Test Grade is 90?
k) Name the fourth worksheet “Regression”.   

6. Name your EXCEL workbook “Chapter 4 – Your Name”
7. Insert a header: Left (Your Name), Middle (Chapter 4), Right (Date)
8. Drop the Excel workbook (only one document should be dropped) in the appropriate Chapter’s dropbox folder for your class. Make sure you receive an email that it was dropped successfully before you close D2L.

This material may consist of step-by-step explanations on how to solve a problem or examples of proper writing, including the use of citations, references, bibliographies, and formatting. This material is made available for the sole purpose of studying and learning - misuse is strictly forbidden.