Use the given data to do the followings. Show all the calculations in Excel spreadsheets. You may use the exercise template to help set it up. Put each part on a new sheet.
Part 1: Regression Model
1. Create a scatter plot, and the trendline .
2. Develop a regression model; find b0 and b1. Show a table with the calculations.
3. Find SST, SSR, SSE and r^2. Explain the meaning of the r^2 value for this model.
4. Find MAD.
5. Find MSR, MSE, and F_calculated
6. Use the significance level of 5% to determine whether or not the Y values depend on the X values.
Part 2: Averages
1. Develop a forecast using a 4-month moving average. Find MAD.
2. Develop a forecast using a weighted 4-month moving average in which the revenue in the most recent month is given a weight 2 and revenue in the other 3 months is each given a weight of 1. Find MAD.
3. Develop a forecast using an exponential smoothing with smoothing constant of 0.4. Assuming the forecast for January of 2013 is $445,000. Find MAD.
4. Develop a forecast using an exponential smoothing with trend. Use smoothing constant of 0.4 for forecast, and smoothing constant of 0.3 for trend. Assuming the forecast for January of 2013 is $445,000. Find MAD.
Part 3: Decomposition Method for data with Trend and Seasonal variations
Use the decomposition model to incorporate both trend and seasonal components into the forecast.
1. Find CMA, seasonal ratio, seasonal indices, deseasonalized revenue. Start CMA in the month of July (half way between one January to the next).
2. Find the equation of a regression line (trend line) using the deseasonalized data (with the seasonal elements taken away). Show graph.
3. Use the regression line to find the Y_reg values for revenue in 2013-2016.
4. Find the final forecast (with the seasonal elements added) for the revenue in 2013-2016.
5. Find MAD.
Part 4: The best forecast.
1. Compare the forecasting methods from Part 1 - Part 3. Which one is the best method to use for this problem? Why?
2. Use the best forecasting method to create the revenue forecast for the year 2016 (January-December).
Part 5: Do Problem #31 on page 145
After the best model is found, predict the number of victories using the following values, where they are applicable: ERA = 4.5, R = 750, AVG = 0.260, ORP = 0.320.