review for exam 2_Forecasting_Smith Insurance Agency

Period Usage 1 17 2 24 3 21 4 20 5 17 6 21 7 21 8 19 9 17 10 18 11 20 12 21 Monthly water usage at Smith Insurance Agency for the past year has been provided. a) graph this time series. Does a stationary time series seem appr b) Using regression, show that a stationary time series is reasonab c) Using a four-month moving average, forecast water usage for b January and the entire upcoming year. d) What is the MAE of using four-month moving average forecasti e) Determine the forecast for the upcoming January if exponentia smoothing constant of 0.1. f) Repeat part e with smoothing constant of 0.6. g) Which of the methods of part d, e, and f perform better based

r, as measured in 100s of cubic feet, ropriate from the graph? ble. both the upcoming month of ting method? al smoothing is used with a on MAE?

a. Yes, looking at the time series, there is not much "predictable" variation in data, so a station stationary model seems appropriate b. Run regression of usage with respect to month # (period). From regression output, p-value of month # is p = .689. A stationary model seems appropriat signifcant, so we cannot reject H0: _1 = 0 which means that we can assume that _1=0 (slop ? ? c. forecast for January= (17+18+20+21)/4=19. since the model is stationary and we don't hav forecast for each month in the next year is 19 as well. So total water usage forecast for next ye the upcoming months would be the same value of 19 given that this is a stationary time series d. We need to calculate MAE. We use the performance measure template to get MAE value. For 4-period moving average, start from month 5 (this is the frst month we have 4 months of of 4 data points before month 5 as a forecast for month 5. Then drag the formula down to calc months. The MAE will appear in the template . MAD is 1.72. See next sheet. e,f. Since we don’t have any initial forecast, we have to come up with an initial forecast: _2 ? = smoothing mehtod formula to arrive at the forecast for period 3. _3 _2+(1 ) _2=0.1 2 ? =?? −? ? ∗ If you do this in Excel, you can just drag down the formula to calculate forecast for other perio reference to the cell you put your alpha value in, so that Excel treats it as a fxed cell). Please For part f, replace the cell for smoothing constant to 0.6. Py attention that the formulas in calc cell for smoothing constant. With smoothing constant of 0.1, the forecast for each month of n constant of 0.6, the forecast for each month of next year is 20.28. g. Incorporate the forecasts into a new forecasting accuracy template. With smoothing constant of 0.1, MAE = 2.31 With smoothing constant of 0.6, MAE = 2.13 Comparing the methods, it seems 4-month moving average is best in terms of MAE. e graph? ng sed with 12

SUMMARY OUTPUT Regression Statistics Multiple R 0.12923 R Square 0.0167 Adjusted R -0.08163 Standard E 2.232051 Observatio 12 ANOVA df SS MS F Regression 1 0.846154 0.846154 0.16984 Residual 10 49.82051 4.982051 Total 11 50.66667 Coefficients tandard Erro t Stat P-value Intercept 20.16667 1.373732 14.6802 4.3E-08 Period -0.076923 0.186653 -0.412117 0.688949 smoothing constant Period Usage forecasts 0.6 1 17 2 24 17 3 21 21.2 4 20 21.08 5 17 20.432 6 21 18.3728 7 21 19.94912 8 19 20.57965 9 17 19.63186 10 18 18.05274 11 20 18.0211 12 21 19.20844 13 20.28338 nary model seems appropriate Yes, a te since the linear term, Period, is not pe of the regression line is 0). ve data beyond the current year, the ear is 19*12=228 as forecast for each of s. data before it). Calculate the average culate the forecast for all other _1=17. Then apply the exponential =? 24+(1−0.1) 17=17.7. ∗ ods. (remember: press F4 on the see the solution spreadsheet. culation of forecasts are referring to the next year is 18.86. With smoothing

INPUTS Number of Periods of Data Collected = 12 Performance measures for forecasted values below MSE = 3.87 MAE = 1.72 MAPE = 9.26% Absolute Error Absolute Period Usage Forecasted values Error Error Squared % Error 1 17 2 24 3 21 4 20 5 17 20.5 -3.5 3.5 12.25 0.205882 6 21 20.5 0.5 0.5 0.25 0.02381 7 21 19.75 1.25 1.25 1.5625 0.059524 8 19 19.75 -0.75 0.75 0.5625 0.039474 9 17 19.5 -2.5 2.5 6.25 0.147059 10 18 19.5 -1.5 1.5 2.25 0.083333 11 20 18.75 1.25 1.25 1.5625 0.0625 12 21 18.5 2.5 2.5 6.25 0.119048

INPUTS Number of Periods of Data Collected = 12 Performance measures for forecasted values below MSE = 7.87 MAE = 2.13 MAPE = 10.52% smoothing constant 0.6 Absolute Error Absolute Period Usage Forecasted values Error Error Squared % Error 1 17 2 24 17 7 7 49 0.291667 3 21 21.2 -0.2 0.2 0.04 0.009524 4 20 21.08 -1.08 1.08 1.1664 0.054 5 17 20.432 -3.432 3.432 11.77862 0.201882 6 21 18.3728 2.6272 2.6272 6.90218 0.125105 7 21 19.94912 1.05088 1.05088 1.104349 0.050042 8 19 20.579648 -1.579648 1.579648 2.495288 0.083139 9 17 19.6318592 -2.631859 2.631859 6.926683 0.154815 10 18 18.05274368 -0.052744 0.052744 0.002782 0.00293 11 20 18.021097472 1.978903 1.978903 3.916055 0.098945 12 21 19.2084389888 1.791561 1.791561 3.209691 0.085312