Question: I attached 5 excel spreadsheets. Please note: a. Time series data will need its own spread sheets completed. I attached 4 excel sheets 1. Seasonal

I attached 5 excel spreadsheets. Please note: a. Time series data will need its own spread sheets completed. I attached 4 excel sheets 1. Seasonal Index 2. Quadratic Trend 3. Exponential Smoothing 4. Moving average b. Time series data with interest will need its own 4 spread sheets completed. 1. Seasonal Index, 2. linear trend, 3. Exponential smoothing 4. Regression (a) When consider \"time series data\" excel spreadsheet only, please compare 4 excel spreadsheets (1. Seasonal Index 2. Quadratic Trend 3. Exponential Smoothing 4. Moving average) select the best time series forecast model with clear explanation of why it is better than others. For each method you compare, what are the quarterly sales to be expected in 2006 (b) When consider \"time series data with interest\" this excel spreadsheet is a sub header spreadsheet as time series data excel, use how would you adjust the forecasting model to improve the forecasting accuracy? When apply the new forecasting model, what are the quarterly sales to be expected in 2006? (1. Seasonal Index, 3.linear trend, 4. exponential smoothing) Please describe your method in details and explain how interest rate may affect the forecasting accuracy. Submit supplement Excel sheets to show all forecasting models and results Sales (in million) 147.6 251.8 273.1 249.1 139.3 221.2 260.2 259.5 140.5 245.5 298.8 287 168.8 322.6 393.5 404.3 259.7 401.1 464.6 479.7 264.4 402.6 411.3 385.9 232.7 309.2 310.7 293 205.1 234.4 285.4 258.7 193.2 263.7 292.5 315.2 178.3 274.5 295.4 286.4 190.8 263.5 318.8 305.3 242.6 318.8 329.6 338.2 232.1 285.6 291 281.4 600 Sales Year Qrtr 1993 1 2 3 4 1994 1 2 3 4 1995 1 2 3 4 1996 1 2 3 4 1997 1 2 3 4 1998 1 2 3 4 1999 1 2 3 4 2000 1 2 3 4 2001 1 2 3 4 2002 1 2 3 4 2003 1 2 3 4 2004 1 2 3 4 2005 1 2 3 4 500 400 300 200 100 0 0 10 20 0 10 20 Period 30 40 50 Interest rate Sales (in million) 6.5% 147.6 6.5% 251.8 6.5% 273.1 6.7% 249.1 6.9% 139.3 7.2% 221.2 6.9% 260.2 6.9% 259.5 6.7% 140.5 6.5% 245.5 6.3% 298.8 6.0% 287 5.7% 168.8 5.4% 322.6 5.1% 393.5 5.0% 404.3 4.7% 259.7 4.7% 401.1 4.7% 464.6 4.8% 479.7 5.1% 264.4 5.3% 402.6 5.7% 411.3 5.7% 385.9 6.0% 232.7 6.1% 309.2 6.2% 310.7 6.2% 293 6.3% 205.1 6.3% 234.4 6.3% 285.4 6.3% 258.7 6.2% 193.2 6.2% 263.7 6.2% 292.5 6.2% 315.2 6.2% 178.3 6.2% 274.5 6.2% 295.4 6.2% 286.4 6.1% 190.8 6.1% 263.5 6.1% 318.8 6.1% 305.3 5.9% 242.6 5.9% 318.8 5.9% 329.6 5.9% 338.2 6.2% 232.1 6.2% 285.6 6.2% 291 6.2% 281.4 600 500 Sales Year Qrtr 1993 1 2 3 4 1994 1 2 3 4 1995 1 2 3 4 1996 1 2 3 4 1997 1 2 3 4 1998 1 2 3 4 1999 1 2 3 4 2000 1 2 3 4 2001 1 2 3 4 2002 1 2 3 4 2003 1 2 3 4 2004 1 2 3 4 2005 1 2 3 4 400 300 200 100 0 0 10 600 Sales 500 400 300 200 100 0 0 10 20 Period 30 40 50 Moving Average Time Period 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 Actual Data 176 134 186 195 189 157 195 211 205 180 212 229 223 192 234 248 239 217 271 284 MSE 2-Month Moving Avg --155.00 160.00 190.50 192.00 173.00 176.00 203.00 208.00 192.50 196.00 220.50 226.00 207.50 213.00 241.00 243.50 228.00 244.00 277.50 284.00 #DIV/0! #DIV/0! 4-Month Moving Avg ----172.75 176.00 181.75 184.00 188.00 192.00 197.75 202.00 206.50 211.00 214.00 219.50 224.25 228.25 234.50 243.75 252.75 257.33 277.50 284.00 777.39 502.29 Page 1 400.00 350.00 300.00 250.00 200.00 150.00 100.00 50.00 0.00 1 6 11 6 Moving Average Actual Data 2-Month Moving Avg 4-Month Moving Avg 11 16 21 Page 2 Exp. Smoothing Actual Data 176 134 186 195 189 157 195 211 205 180 212 229 223 192 234 248 239 217 271 284 ----- Exp. Smoothing Prediction 176.00 176.00 156.82 170.15 181.50 184.92 172.17 182.60 195.57 199.88 190.80 200.48 213.51 217.84 206.04 218.81 232.14 235.28 226.93 247.06 263.93 143.38 77.89 42.31 22.98 22.98 22.98 22.98 alpha 0.457 MSE 696.00 40 Units Sold Time Period 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 38 36 34 32 30 1 3 5 7 9 11 13 15 Time P Page 1 Exp. Smoothing Ac tual Data Exp. Smoothing Predic tion 5 7 9 11 13 15 17 19 21 23 25 27 29 Time Period Page 2 Time^2 1 4 9 16 25 36 49 64 81 100 121 144 169 196 225 256 289 324 361 400 441 484 529 576 Actual Data 176 134 186 195 189 157 195 211 205 180 212 229 223 192 234 248 239 217 271 284 ----- Quadratic Trend 167.858 170.422 173.278 176.426 179.866 183.598 187.621 191.936 196.543 201.441 206.631 212.113 217.887 223.952 230.310 236.959 243.899 251.132 258.656 266.472 274.580 282.979 291.671 300.654 MSE 339.245 Sales (in $1,000s) . Time Year Qtr Period 2001 1 1 2 2 3 3 4 4 2002 1 5 2 6 3 7 4 8 2003 1 9 2 10 3 11 4 12 2004 1 13 2 14 3 15 4 16 2005 1 17 2 18 3 19 4 20 2006 1 21 2 22 3 23 4 24 350 300 250 200 150 100 50 0 1 3 5 7 9 11 T Actual Data Quadratic Trend 1 3 5 7 9 11 15 Time13Period 17 19 21 23 25 SUMMARY OUTPUT Regression Statistics Multiple R ### R Square ### Adjusted R ### Standard E ### Observatio 20 ANOVA df Regression Residual Total Intercept X Variable SS 1 ### 18 ### 19 25072.55 MS ### ### Coefficients Standard Error t Stat ### ### ### ### ### ### F Significance F ### ### P-value Lower 95%Upper 95%Lower 95.0% Upper 95.0% ### ### ### ### ### ### ### ### ### ### Upper 95.0% 2002 2003 2004 2005 2006 Qtr 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 Time Period 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 Actual Data 176 134 186 195 189 157 195 211 205 180 212 229 223 192 234 248 239 217 271 284 ----- Linear Trend 159.5429 164.7331 169.9233 175.1135 180.3038 185.4940 190.6842 195.8744 201.0647 206.2549 211.4451 216.6353 221.8256 227.0158 232.2060 237.3962 242.5865 247.7767 252.9669 258.1571 263.3474 268.5376 273.7278 278.9180 159.543 164.733 169.923 175.113 180.303 185.493 190.683 195.873 201.063 206.253 211.443 216.633 221.823 227.013 232.203 237.393 242.583 247.773 252.963 258.153 MSE ### Sales (in $1,000s) . Year 2001 300 250 200 150 100 50 0 1 3 5 7 9 11 Ti 0 0 0 0 0 Actual Data Linear Trend 0 0 1 3 5 7 9 11 13 15 Time Period 17 19 21 23 25

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer
Step: 1 Unlock blur-text-image
Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock
Step: 3 Unlock

Students Have Also Explored These Related Mathematics Questions!