Forecasting MC: Moving average

The following questions use the data in the table. A mobile clinic wants to predict how many patients it treats each month. The Director has collected data for 12 months. He needs your help in analyzing these data using moving averages.

1. Which Excel formula will correctly compute the MSE in cell C18 of the spreadsheet?
2. =SUMXMY2(B3:B16,C3:C16)
3. =SUMX2MY2(B7:B14,C7:C14)/COUNT(C7:C14)
4. =SUMXMY2(B7:B16,C7:C16)/COUNT(C7:C16)
5. =SUMX2MY2(B3:B14,C3:C14)/COUNT(C3:C14)
6. None of these

2. What is true of the calculation for the 4-month moving average forecast in month 14?

1. It contains only actual (vs. forecasted) data values for number of patients
2. One would first need to compute the 4-month moving average forecast for month 13
3. It will contain 3 actual data values and 1 forecasted data value for number of patients
4. Both B and C
5. None of the above

3. What is the 4-month moving average forecast value missing in cell C7 (X) to 1 decimal place?

4. As the number of periods in the forecast, k, decreases

1. the moving average decreases in value
2. getting a good forecast is impossible
3. the forecast responds more quickly to changes in the data
4. the moving average prediction becomes smoother
5. None of these

5. Nonstationary time series data show no significant upward or downward trend over time.

1. True
2. False

4-Month Moving Avg. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Month 1 2 3 4 5 6 7 8 9 10 11 12 13 14 B Number of Patients 70 80 66 74 64 76 72 82 82 || 71.0 70.0 71.5 73.5 78.0 78.0 81.0 76 84 80 P 16 17 18 MSE