How to find sst ssr and sst in excel

Instructions
ISOM 201 Chapters 4 &5: Project 1 Prof. Brooks
Due Date: On blackboard by October 25, 11:59PM
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
Data
MonthlySales for the Glass Slipper Restaurant
Year Month Monthly Revenue (in $1,000s)
2013 January 438
February 420
March 414
April 318
May 306
June 240
July 240
August 216
September 198
October 225
November 270
December 315
2014 January 444
February 425
March 423
April 331
May 318
June 245
July 255
August 223
September 210
October 233
November 278
December 322
2015 January 450
February 438
March 434
April 338
May 331
June 254
July 265
August 231
September 224
October 243
November 289
December 335
Regression Model
Year Month Monthly Revenue (in $1,000s) (y-y_mean)^2 (x-x_mean)^2 (x-x_mean)(y-y_mean) y_reg (y_reg - y_mean)^2
2013 January 438
February 420
March 414
April 318
May 306
June 240
July 240
August 216
September 198
October 225
November 270
December 315
2014 January 444
February 425
March 423
April 331
May 318
June 245
July 255
August 223
September 210
October 233
November 278
December 322
2015 January 450
February 438
March 434
April 338
May 331
June 254
July 265
August 231
September 224
October 243
November 289
December 335
SST SSR
x_mean =
y_mean =
b1 =
b0 =
SSE =
r^2 =
df1 =
df2 =
MSE =
MSR =
F_calculated =
Answers:
Averages
Year Month Monthly Revenue (in $1,000s) 4-month Average ERROR 4-month weighted Average ERROR Exponential Smoothing: a = 0.4 ERROR F T FIT ERROR
Decomposition
Month Monthly Revenue (in $1,000s) CMA Seasonal Ratio Seasonal Index Deseasonalized Revenue Y_reg regression Final forecast Error
The best forecast
Problem #31