Business Analytics – trendlines and regression

Description

Complete the following problems in an Excel format. Use separate Worksheet for each problem. Submit the whole assignment in one Excel Workbook

1. Using the data in Excel file Student Grades, construct a scatter chart for midterm versus final exam grades and add a linear trendline. What is the regression model? If a student scores 70 on the midterm, what would you predict her grade on the final exam to be?

2. The Excel file Concert Sales provides data on sales dollars and the number of radio, TV, and newspaper ads promoting the concert for a group of cities. Develop simple linear regression models for predicting sales as a function of the number of each type of ad. Compare these results to a multiple linear regression model using both independent variables. Examine the residuals of the best model for regression assumptions and possible outliers.

3. the Excel file Unemployment Rates provides data on monthly rates for 4 years. Compare 3- and 12-month moving average forecast using the MAD criterion. Explain why the 3-month yields better results.

4. The Excel file Closing Stock Price provides data for four stocks and Dow Jones Industrial Index over a 1-month period:

a. develop spreadsheet models for forecasting each of the of the stock prices using simple 2-period moving average and simple exponential smoothing with a smoothing constant of 0.3.

b. Compare your results to the outputs from Excel’s Data Analysis Tool. c. Using MAD, MSE, and MAPE as guidance, find the best number of moving average periods and best smoothing constant for exponential smoothing.