Exam 11: Time Series Forecasting

Exhibit 11.4 The following questions use the data below. Joe's Sporting Goods wants to forecast quarterly sales figures using the additive seasonal method. The store has collected 12 quarters of data and needs your help to analyze the data. -Refer to Exhibit 11.4. What formula should be entered in cell E3 to compute the base level when using the additive seasonal effects method?

Exhibit 11.6 The following questions use the data below. A store wants to predict quarterly sales. The owner has collected 3 years of sales data and wants your help in analyzing the data using the double moving average model with k = 4. -Refer to Exhibit 11.6. What formula should be entered in cell I16 to compute the forecast for time period 14?

Exhibit 11.6 The following questions use the data below. A store wants to predict quarterly sales. The owner has collected 3 years of sales data and wants your help in analyzing the data using the double moving average model with k = 4. -Refer to Exhibit 11.6. What formula should be entered in cell E6 (and copied to E7:E14) to compute the first average when using the double moving average method with k = 4?

Exhibit 11.24 The following questions use the data below. A store wants to predict quarterly sales. The owners have has collected 3 years of sales data and wants your help in analyzing the data. They want to use a quadratic trend model with seasonality to forecast sales. Regression Statistics Coefficients Intercept 56.29 X Variable 1 3.59 X Variable 2 0.08 -Refer to Exhibit 11.24. Based on the regression output, what formulas should go in cells D3:H21?

Exhibit 11.17 The following questions use the data below. A store wants to predict quarterly sales. The owner has collected 3 years of sales data and wants your help in analyzing the data using the additive seasonal method. A B C D E F G H I 1 Time Actual Seasonal 2 Year Qtr Period Sales Level Factor Forecast 3 1991 1 1 368 462.50 -94.500 alpha 0.764197 4 2 2 168 462.50 -294.500 beta 1 5 3 3 530 462.50 67.500 6 4 4 784 462.50 321.500 7 1992 1 5 770 769.71 0.293 368.00 8 2 6 354 677.08 -323.081 475.21 9 3 7 1012 881.44 130.558 744.58 10 4 8 1244 912.82 331.182 1202.94 11 1993 1 9 1326 1228.35 97.653 913.11 12 2 10 986 1290.04 -304.044 905.27 13 3 11 1624 1445.48 178.520 1420.60 14 4 12 2090 1684.93 405.068 1776.66 15 16 70755.110 -Refer to Exhibit 11.17. What formulas should go in cells E3:G16 of the spreadsheet if the additive seasonal method is used to forecast sales?

Exhibit 11.1 The following questions use the data below. Honest Al's Used Cars wants to predict how many cars are sold each month. He has collected data for 12 months. He needs your help in analyzing this data using moving averages. A B C 1 Number of 4-Month 2 Time Period Cars Sold Moving Avg. 3 1 70 4 2 80 5 3 66 6 4 74 7 5 64 8 6 76 71.00 9 7 72 70.00 10 8 82 71.50 11 9 82 73.50 12 10 76 78.00 13 11 84 78.00 14 12 80 81.00 15 13 16 14 17 18 MSE 40.59 -Refer to Exhibit 11.1. What Excel function will compute the root mean squared error in cell C18 of the spreadsheet?

Exhibit 11.1 The following questions use the data below. Honest Al's Used Cars wants to predict how many cars are sold each month. He has collected data for 12 months. He needs your help in analyzing this data using moving averages. A B C 1 Number of 4-Month 2 Time Period Cars Sold Moving Avg. 3 1 70 4 2 80 5 3 66 6 4 74 7 5 64 8 6 76 71.00 9 7 72 70.00 10 8 82 71.50 11 9 82 73.50 12 10 76 78.00 13 11 84 78.00 14 12 80 81.00 15 13 16 14 17 18 MSE 40.59 -Refer to Exhibit 11.1. What formula should be entered in cell C13 to compute the 4-month moving average forecast for month 11?

Exhibit 11.8 The following questions use the data below. Joe's Sporting Goods wants to forecast quarterly sales figures using Winter's method. The store has collected 12 quarters of data and needs your help to analyze the data. 1 Time Actual Base Seasonal Predicted 2 Year Qtr Period Sales Level Trend Factor Sales 3 1 1 1 284 4 2 2 184 5 3 3 365 6 4 4 492 7 2 1 5 485 8 2 6 277 413.5 20.5 0.567 217.9 9 3 7 606 457.2 27.4 1.124 478.2 10 4 8 722 484.9 27.5 1.486 719.7 11 3 1 9 763 579.5 47.6 0.942 461.1 12 2 10 593 710.9 72.8 0.594 355.5 13 3 11 912 789.2 74.4 1.127 881.0 14 4 12 1145 845.0 68.8 1.473 1283.0 15 16 MSE 28,474.73 17 alpha 0.2 18 beta 0.3 19 gamma 0.1 20 -Refer to Exhibit 11.8. What formula should be entered in cell H7 to compute the Predicted Sales value for year 2 Quarter 1?

Exhibit 11.13 The following questions use the data below. The owner of Tim's Toys wants to predict monthly sales. He has collected data for 12 months. He needs your help in analyzing this data using moving averages. A B C 1 Number of 4-Month 2 Time Period Toys Sold Moving Avg 3 1 174 4 2 189 5 3 168 6 4 180 7 5 165 177.75 8 6 183 175.50 9 7 177 174.00 10 8 192 176.25 11 9 192 179.25 12 10 183 186.00 13 11 195 186.00 14 12 189 190.50 15 16 91.34 -Refer to Exhibit 11.13. What is the forecast for time periods 13-15?

Exhibit 11.6 The following questions use the data below. A store wants to predict quarterly sales. The owner has collected 3 years of sales data and wants your help in analyzing the data using the double moving average model with k = 4. -Refer to Exhibit 11.6. What formula should be entered in cell F9 to compute the second average when using the double moving average method with k = 4?

Exhibit 11.2 The following questions use the data below. Honest Al's Used Cars wants to predict how many cars are sold each month. He has collected data for 12 months. He needs your help in analyzing this data using weighted moving averages. A B C D E F 1 Number of 2-Month Weighted 2 Time Period Cars Sold Moving Avg. Weights 3 1 70 W1 0.244 4 2 80 W2 0.756 5 3 66 sum 1.000 6 4 74 76.59 7 5 64 67.95 8 6 76 71.56 9 7 72 66.93 10 8 82 75.02 11 9 82 74.44 12 10 76 82.00 13 11 84 80.54 14 12 80 77.95 15 16 MSE 26.72 -Refer to Exhibit 11.2. What formula should be entered in cell C6 to compute the 2-month weighted moving average forecast for month 3?

Exhibit 11.19 The following questions use the data below. A store wants to predict quarterly sales. The owner has collected 3 years of sales data and wants your help in analyzing the data using the double moving average method. -Refer to Exhibit 11.19. What formulas should go in cells E6:G14 of the spreadsheet if the double moving average method is used to forecast sales?

Exhibit 11.5 The following questions use the data below. A store wants to predict quarterly sales. The owner has collected 3 years of sales data and wants your help in analyzing the data using the multiplicative seasonal effects model. A 1 Time Actual Seasonal 2 Year Qtr Period Sales Level Factor Forecast 3 1 1 1 368 462.50 0.796 alpha 0.332167 4 2 2 168 462.50 0.363 beta 1 5 3 3 530 462.50 1.146 6 4 4 784 462.50 1.695 7 2 1 5 770 630.32 1.222 368.00 8 2 6 354 744.66 0.475 228.96 9 3 7 1012 790.65 1.280 853.34 10 4 8 1244 771.79 1.612 1340.26 11 3 1 9 1326 875.98 1.514 942.82 12 2 10 986 1273.96 0.774 416.43 13 3 11 1624 1272.25 1.276 1630.62 14 4 12 2090 1280.35 1.632 2050.66 15 16 85564.040 -Refer to Exhibit 11.5. What formula should be entered in cell G13 to compute the forecast for time period 11?

A technique that analyzes past behavior of a time-series variable to predict the future is referred to as

Exhibit 11.15 The following questions use the data below. The owner of Tim's Toys wants to predict monthly sales. He has collected data for 12 months. He needs your help in analyzing this data using weighted moving averages. A B C D E F 1 Number of 2-Month Weighted 2 Time Period Toys Sold Moving Avg. Weights 3 1 174 W1 0.350 4 2 189 w2 0.650 5 3 168 179.25 sum 1.000 6 4 180 181.65 7 5 165 172.20 8 6 183 174.75 9 7 177 171.30 10 8 192 180.90 11 9 192 182.25 12 10 183 192.00 13 11 195 188.85 14 12 189 187.20 15 16 MSE 62.20 -Refer to Exhibit 11.15. What is the forecast for time period 13-15?

Exhibit 11.12 The following questions use the data below. Joe's Sporting Goods wants to forecast quarterly sales figures using a seasonal regression model. The store has collected 12 quarters of data and needs your help to analyze the data. The relevant regression output is provided in the following table. Regression Statistics Coefficients Intercept 388.88 X Variable 1 10.052 X Variable 2 4.248 X Variable 3 -79.917 X Variable 4 -296.008 X Variable 5 -84.924 -Refer to Exhibit 11.12. What is the Input X Range in the Regression command settings dialog box?

A model or technique that uses past behavior of a time-series variable to predict the future is referred to as

Exhibit 11.11 The following questions use the data below. Joe's Sporting Goods wants to forecast quarterly sales figures using seasonal indices. The store has collected 12 quarters of data and needs your help to analyze the data. The relevant regression output is in the following table. Ragression Statistics Coefficients Intercept 263.4545 X Variable 1 5.985514 X Variable 2 4.922577 -Refer to Exhibit 11.11. What formula should be entered in cell C18 to compute the Seasonal Index value for quarter 1?

Project 11.1  Developing and Assessing Forecasts at Dempsi Industries Management at Dempsi Industries is unsure about how to calculate demand for the next four weeks. They have collected demand data for the last year and have presented you with the data. They have asked you for a reasonable level of demand for the next four weeks. You have agreed to provide management a report describing your analysis. Conduct an analysis of the data provided. Based on the sample data and your initial analyses, evaluate the applicability of the forecasting techniques covered in the chapter. For each technique, provide a brief description of the technique and an assessment of the appropriateness of that technique to the sample data. Provide a table summarizing the forecast error evaluations for each technique examined. Recommend a technique to use to make the requisite predictions and provide a prediction of demand for the next four weeks based on this recommended model. $\quad$$\quad$$\quad$$\quad$$\quad$$\quad$$\quad$$\quad$$\quad$$\text { Demand for Demps1 Industries Product } A$ Week Demand Week Demand Week Demand Week Demand 1 410 15 463 29 332 43 434 2 229 16 393 30 412 44 297 3 344 17 427 31 332 45 523 4 278 18 381 32 261 46 381 5 293 19 359 33 255 47 487 6 395 20 516 34 381 48 409 7 449 21 191 35 382 49 260 8 436 22 446 36 217 50 392 9 448 23 555 37 431 51 474 10 347 24 628 38 292 52 468 11 523 25 277 39 286 12 245 26 549 40 278 13 266 27 354 41 393

Exhibit 11.18 The following questions use the data below. A store wants to predict quarterly sales. The owner has collected 3 years of sales data and wants your help in analyzing the data using the multiplicative seasonal method. 1 Time Actual Seasonal 2 Year Qtr Period Sales Level Factor Forecast 3 1 1 1 368 462.50 0.796 alpha 0.332167 4 2 2 168 462.50 0.363 beta 1 5 3 3 530 462.50 1.146 6 4 4 784 462.50 1.695 7 2 1 5 770 630.32 1.222 368.00 8 2 6 354 744.66 0.475 228.96 9 3 7 1012 790.65 1.280 853.34 10 4 8 1244 771.79 1.612 1340.26 11 3 1 9 1326 875.98 1.514 942.82 12 2 10 986 1273.96 0.774 416.43 13 3 11 1624 1272.25 1.276 1630.62 14 4 12 2090 1280.35 1.632 2050.66 15 16 85564.040 -Refer to Exhibit 11.18. What formulas should go in cells E3:G16 of the spreadsheet if the multiplicative seasonal method is used to forecast sales?