VIEWS: 5 PAGES: 40 POSTED ON: 10/14/2011
Forecasting Quantitative Approaches to Forecasting The Components of a Time Series Measures of Forecast Accuracy Using Smoothing Methods in Forecasting Using Trend Projection in Forecasting Using Regression Analysis in Forecasting Qualitative Approaches to Forecasting 1 Quantitative Approaches to Forecasting Quantitative methods are based on an analysis of historical data concerning one or more time series. A time series is a set of observations measured at successive points in time or over successive periods of time. If the historical data used are restricted to past values of the series that we are trying to forecast, the procedure is called a time series method. If the historical data used involve other time series that are believed to be related to the time series that we are trying to forecast, the procedure is called a causal method. 2 Time Series Methods Three time series methods are: smoothing trend projection 3 Components of a Time Series The trend component accounts for the gradual shifting of the time series over a long period of time. Any regular pattern of sequences of values above and below the trend line is attributable to the cyclical component of the series. 4 Components of a Time Series The seasonal component of the series accounts for regular patterns of variability within certain time periods, such as over a year. The irregular component of the series is caused by short-term, unanticipated and non-recurring factors that affect the values of the time series. One cannot attempt to predict its impact on the time series in advance. 5 Measures of Forecast Accuracy Mean Squared Error The average of the squared forecast errors for the historical data is calculated. The forecasting method or parameter(s) which minimize this mean squared error is then selected. Mean Absolute Deviation The mean of the absolute values of all forecast errors is calculated, and the forecasting method or parameter(s) which minimize this measure is selected. The mean absolute deviation measure is less sensitive to individual large forecast errors than the mean squared error measure. 6 Smoothing Methods In cases in which the time series is fairly stable and has no significant trend, seasonal, or cyclical effects, one can use smoothing methods to average out the irregular components of the time series. Four common smoothing methods are: Moving averages Centered moving averages Weighted moving averages Exponential smoothing 7 Smoothing Methods Moving Average Method The moving average method consists of computing an average of the most recent n data values for the series and using this average for forecasting the value of the time series for the next period. 8 Example: Rosco Drugs Sales of Comfort brand headache medicine for the past ten weeks at Rosco Drugs are shown on the next slide. If Rosco Drugs uses a 3-period moving average to forecast sales, what is the forecast for Week 11? 9 Example: Rosco Drugs Past Sales Week Sales Week Sales 1 110 6 120 2 115 7 130 3 125 8 115 4 120 9 110 5 125 10 130 10 Example: Rosco Drugs Excel Spreadsheet Showing Input Data A B C 1 Robert's Drugs 2 3 Week (t ) Salest Forect+1 4 1 110 5 2 115 6 3 125 7 4 120 8 5 125 9 6 120 10 7 130 11 8 115 12 9 110 11 13 10 130 Example: Rosco Drugs Steps to Moving Average Using Excel Step 1: Select the Tools pull-down menu. Step 2: Select the Data Analysis option. Step 3: When the Data Analysis Tools dialog appears, choose Moving Average. Step 4: When the Moving Average dialog box appears: Enter B4:B13 in the Input Range box. Enter 3 in the Interval box. Enter C4 in the Output Range box. Select OK. 12 Example: Rosco Drugs Spreadsheet Showing Results Using n = 3 A B C 1 Robert's Drugs 2 3 Week (t ) Salest Forect+1 4 1 110 #N/A 5 2 115 #N/A 6 3 125 116.7 7 4 120 120.0 8 5 125 123.3 9 6 120 121.7 10 7 130 125.0 11 8 115 121.7 12 9 110 118.3 13 13 10 130 118.3 Smoothing Methods Centered Moving Average Method The centered moving average method consists of computing an average of n periods' data and associating it with the midpoint of the periods. For example, the average for periods 5, 6, and 7 is associated with period 6. This methodology is useful in the process of computing season indexes. 14 Smoothing Methods Weighted Moving Average Method In the weighted moving average method for computing the average of the most recent n periods, the more recent observations are typically given more weight than older observations. For convenience, the weights usually sum to 1. 15 Smoothing Methods Exponential Smoothing Using exponential smoothing, the forecast for the next period is equal to the forecast for the current period plus a proportion () of the forecast error in the current period. Using exponential smoothing, the forecast is calculated by: [the actual value for the current period] + (1- )[the forecasted value for the current period], where the smoothing constant, , is a number between 0 and 1. 16 Trend Projection If a time series exhibits a linear trend, the method of least squares may be used to determine a trend line (projection) for future forecasts. Least squares, also used in regression analysis, determines the unique trend line forecast which minimizes the mean square error between the trend line forecasts and the actual observed values for the time series. The independent variable is the time period and the dependent variable is the actual observed value in the time series. 17 Trend Projection Using the method of least squares, the formula for the trend projection is: Tt = b0 + b1t. where: Tt = trend forecast for time period t b1 = slope of the trend line b0 = trend line projection for time 0 b1 = ntYt - t Yt b0 Y b1 t nt 2 - (t )2 where: Yt = observed value of the time series at time period t Y= average of the observed values for Yt t= average time period for the n observations 18 Example: Rosco Drugs (B) If Rosco Drugs uses exponential smoothing to forecast sales, which value for the smoothing constant , .1 or .8, gives better forecasts? Week Sales Week Sales 1 110 6 120 2 115 7 130 3 125 8 115 4 120 9 110 5 125 10 130 19 Example: Rosco Drugs (B) Exponential Smoothing To evaluate the two smoothing constants, determine how the forecasted values would compare with the actual historical values in each case. Let: Yt = actual sales in week t Ft = forecasted sales in week t F1 = Y1 = 110 For other weeks, Ft+1 = .1Yt + .9Ft 20 Example: Rosco Drugs (B) Exponential Smoothing ( = .1, 1 - = .9) F1 = 110 F2 = .1Y1 + .9F1 = .1(110) + .9(110) = 110 F3 = .1Y2 + .9F2 = .1(115) + .9(110) = 110.5 F4 = .1Y3 + .9F3 = .1(125) + .9(110.5) = 111.95 F5 = .1Y4 + .9F4 = .1(120) + .9(111.95) = 112.76 F6 = .1Y5 + .9F5 = .1(125) + .9(112.76) = 113.98 F7 = .1Y6 + .9F6 = .1(120) + .9(113.98) = 114.58 F8 = .1Y7 + .9F7 = .1(130) + .9(114.58) = 116.12 F9 = .1Y8 + .9F8 = .1(115) + .9(116.12) = 116.01 F10= .1Y9 + .9F9 = .1(110) + .9(116.01) = 115.41 21 Example: Rosco Drugs (B) Exponential Smoothing ( = .8, 1 - = .2) F1 = 110 F2 = .8(110) + .2(110) = 110 F3 = .8(115) + .2(110) = 114 F4 = .8(125) + .2(114) = 122.80 F5 = .8(120) + .2(122.80) = 120.56 F6 = .8(125) + .2(120.56) = 124.11 F7 = .8(120) + .2(124.11) = 120.82 F8 = .8(130) + .2(120.82) = 128.16 F9 = .8(115) + .2(128.16) = 117.63 F10= .8(110) + .2(117.63) = 111.53 22 Example: Rosco Drugs (B) Mean Squared Error In order to determine which smoothing constant gives the better performance, calculate, for each, the mean squared error for the nine weeks of forecasts, weeks 2 through 10 by: [(Y2-F2)2 + (Y3-F3)2 + (Y4-F4)2 + . . . + (Y10- F10)2]/9 23 Example: Rosco Drugs (B) α = .1 α = .8 Week Yt Ft (Yt - Ft)2 Ft (Yt - Ft)2 1 110 2 115 110.00 25.00 110.00 25.00 3 125 110.50 210.25 114.00 121.00 4 120 111.95 64.80 122.80 7.84 5 125 112.76 149.94 120.56 19.71 6 120 113.98 36.25 124.11 16.91 7 130 114.58 237.73 120.82 84.23 8 115 116.12 1.26 128.16 173.30 9 110 116.01 36.12 117.63 58.26 10 130 115.41 212.87 111.53 341.27 Sum 974.22 Sum 847.52 MSE Sum/9 108.25 Sum/9 94.17 24 Example: Rosco Drugs (B) Excel Spreadsheet Showing Input Data A B C 1 Robert's Drugs 2 3 Week Sales 4 1 110 5 2 115 6 3 125 7 4 120 8 5 125 9 6 120 10 7 130 11 8 115 12 9 110 25 13 10 130 Example: Rosco Drugs (B) Steps to Exponential Smoothing Using Excel Step 1: Select the Tools pull-down menu. Step 2: Select the Data Analysis option. Step 3: When the Data Analysis Tools dialog appears, choose Exponential Smoothing. Step 4: When the Exponential Smoothing dialog box appears: Enter B4:B13 in the Input Range box. Enter 0.9 (for = 0.1) in Damping Factor box. Enter C4 in the Output Range box. Select OK. 26 Example: Rosco Drugs (B) Spreadsheet Showing Results Using = 0.1 A B C 1 Robert's Drugs 2 = 0.1 3 Week (t ) Sales t Forec t +1 4 1 110 #N/A 5 2 115 110.0 6 3 125 110.5 7 4 120 112.0 8 5 125 112.8 9 6 120 114.0 10 7 130 114.6 11 8 115 116.1 12 9 110 116.0 27 13 10 130 115.4 Example: Rosco Drugs (B) Repeating the Process for = 0.8 Step 4: When the Exponential Smoothing dialog box appears: Enter B4:B13 in the Input Range box. Enter 0.2 (for = 0.8) in Damping Factor box. Enter D4 in the Output Range box. Select OK. 28 Example: Rosco Drugs (B) Spreadsheet Results for = 0.1 and = 0.8 A B C D 1 Robert's Drugs 2 = 0.1 = 0.8 3 Week (t ) Sales t Forec t +1 Forec t +1 4 1 110 #N/A #N/A 5 2 115 110.0 110.0 6 3 125 110.5 114.0 7 4 120 112.0 122.8 8 5 125 112.8 120.6 9 6 120 114.0 124.1 10 7 130 114.6 120.8 11 8 115 116.1 128.2 12 9 110 116.0 117.6 29 13 10 130 115.4 111.5 Example: Auger’s Plumbing Service The number of plumbing repair jobs performed by Auger's Plumbing Service in each of the last nine months is listed on the next slide. Forecast the number of repair jobs Auger's will perform in December using the least squares method. 30 Example: Auger’s Plumbing Service Month Jobs Month Jobs Month Jobs March 353 June 374 September 399 April 387 July 396 October 412 May 342 August 409 November 408 31 Example: Auger’s Plumbing Service Trend Projection (month) t Yt tYt t2 (Mar.) 1 353 353 1 (Apr.) 2 387 774 4 (May) 3 342 1026 9 (June) 4 374 1496 16 (July) 5 396 1980 25 (Aug.) 6 409 2454 36 (Sep.) 7 399 2793 49 (Oct.) 8 412 3296 64 (Nov.) 9 408 3672 81 Sum 45 3480 17844 285 32 Example: Auger’s Plumbing Service Trend Projection (continued) t = 45/9 = 5 Y = 3480/9 = 386.667 ntYt - t Yt (9)(17844) - (45)(3480) b1 = = = 7.4 nt 2 - (t)2 (9)(285) - (45)2 b0 Y b1 t = 386.667 - 7.4(5) = 349.667 T10 = 349.667 + (7.4)(10) = 423.667 33 Example: Auger’s Plumbing Service Excel Spreadsheet Showing Input Data A B C 1 Auger's Plumbing Service 2 3 Month Calls 4 1 353 5 2 387 6 3 342 7 4 374 8 5 396 9 6 409 10 7 399 11 8 412 12 9 408 13 34 Example: Auger’s Plumbing Service Steps to Trend Projection Using Excel Step 1: Select an empty cell (B13) in the worksheet. Step 2: Select the Insert pull-down menu. Step 3: Choose the Function option. Step 4: When the Paste Function dialog box appears: Choose Statistical in Function Category box. Choose Forecast in the Function Name box. Select OK. more . . . . . . . 35 Example: Auger’s Plumbing Service Steps to Trend Projecting Using Excel (continued) Step 5: When the Forecast dialog box appears: Enter 10 in the x box (for month 10). Enter B4:B12 in the Known y’s box. Enter A4:A12 in the Known x’s box. Select OK. 36 Example: Auger’s Plumbing Service Spreadsheet with Trend Projection for Month 10 A B C 1 Auger's Plumbing Service 2 3 Month Calls 4 1 353 5 2 387 6 3 342 7 4 374 8 5 396 9 6 409 10 7 399 11 8 412 12 9 408 13 10 423.667 Projected 37 Example: Auger’s Plumbing Service (B) Forecast for December (Month 10) using a three-period (n = 3) weighted moving average with weights of .6, .3, and .1. Then, compare this Month 10 weighted moving average forecast with the Month 10 trend projection forecast. 38 Example: Auger’s Plumbing Service (B) Three-Month Weighted Moving Average The forecast for December will be the weighted average of the preceding three months: September, October, and November. F10 = .1YSep. + .3YOct. + .6YNov. = .1(399) + .3(412) + .6(408) = 408.3 Trend Projection F10 = 423.7 (from earlier slide) 39 Example: Auger’s Plumbing Service (B) Conclusion Due to the positive trend component in the time series, the trend projection produced a forecast that is more in tune with the trend that exists. The weighted moving average, even with heavy (.6) placed on the current period, produced a forecast that is lagging behind the changing data. 40