Forecasting Part 3 By Anita LeePost 2003 Anita
Forecasting Part 3 By Anita Lee-Post © 2003 Anita Lee-Post
Selecting a forecasting model © 2003 Anita Lee-Post
Forecast accuracy • A good forecast is accurate but not perfect, i. e. , actual value forecast value • Overall accuracy measures: 1. Mean absolute deviation 2. Mean squared error • Forecast accuracy has to be monitored by using a “tracking signal” © 2003 Anita Lee-Post
Overall error measures 1. Mean absolute deviation (MAD): 2. Mean squared error (MSE): • The forecast technique giving the lowest MAD/MSE is preferred • MSE magnifies large errors through the squaring process © 2003 Anita Lee-Post
Tracking signal A way to monitor forecast accuracy is by comparing a measure called: against predetermined control limits (usually +/-4 MAD) in a control chart © 2003 Anita Lee-Post
Tracking signal continued Signal exceeded limit + Upper control limit = +4 MAD 0 - Tracking signal Lower control limit = 4 MAD Time © 2003 Anita Lee-Post
Correlation coefficient • Correlation coefficient, r, measures the direction and strength of the linear relationship between the independent (x) and dependent (y) variables © 2003 Anita Lee-Post
Correlation coefficient continued r = +1: a perfect positive linear relationship § r = 0: no relationship § r = -1: a perfect negative linear relationship § r=1 Y Y r = -1 Yi = a + b Xi X Y r =. 89 Yi = a + b Xi © 2003 Anita Lee-Post X Y X r=0 Yi = a + b Xi X
Using Excel forecasting 1. Enter the following demand figures for C&A’s product in an Excel worksheet Jan 650 © 2003 Anita Lee-Post Feb 700 Mar 810 Apr 800 May 900 Jun 700
Using Excel forecasting continued 2. Invoke the data analysis tool: Tools Data Analysis If “Data Analysis” is not found, then § Tools Add-ins select “Analysis Tool. Pak” § © 2003 Anita Lee-Post
Using Excel forecasting continued 3. Select “Moving Average” from the list of data analysis options” to compute a 3 -month moving average: © 2003 Anita Lee-Post
Using Excel forecasting continued 4. Fill in the Moving Average Parameters: • Input Range: cell range of the time series • Labels in First Row: leave it unchecked if your cell range above contains data points only • Interval: parameter n (number of data points used in moving average computation) • Output Range: starting cell address forecast values (need to offset the input range by one row) © 2003 Anita Lee-Post
Using Excel forecasting continued • Excel-generated moving average forecasts: © 2003 Anita Lee-Post
Using Excel forecasting continued 4. Fill in the Exponential Smoothing Parameters: • Input Range: cell range of the time series • Damping factor: 1 -a, the smoothing constant • Labels: leave it unchecked if your cell range above contains data points only • Output range: starting cell address forecast values (no offset is needed) © 2003 Anita Lee-Post
Using Excel forecasting continued • Excel-generated exponential smoothing forecasts: Copy the formula in cell C 7 to cell C 8 to compute the forecast for July © 2003 Anita Lee-Post
Using Excel forecasting continued 4. Fill in the Regression Parameters: • Input Y Range: cell range of the dependent variable • Input X Range: cell range of the independent variable • Labels: have it checked as column headings are included in our input ranges • Output range: starting cell address for regression analysis output © 2003 Anita Lee-Post
Excel-generated regression analysis report: Enter the formula =D 17+D 18*A 8 in cell B 8 to compute the forecast for July © 2003 Anita Lee-Post
Excel can be used to compute MAD and MSE: A B C D E Month Demand 3 -month Moving Average Absolute Deviation Squared Error … … … 5 Apr 800 720 =ABS(B 5 -C 5) =(B 5 -C 5)^2 6 May 900 770 =ABS(B 6 -C 6) =(B 6 -C 6)^2 7 Jun 700 836. 7 =ABS(B 7 -C 7) =(B 7 -C 7)^2 1 8 9 MAD 10 MSE © 2003 Anita Lee-Post =AVERAGE(D 5: D 7) =AVERAGE(E 5: E 7)
Excel can be used to compute MAD and MSE: A B C Month Demand Exp. Smooth. (a = 0. 1) Absolute Deviation Squared Error … … … 5 Apr 800 670. 5 =ABS(B 5 -C 5) =(B 5 -C 5)^2 6 May 900 683. 5 =ABS(B 6 -C 6) =(B 6 -C 6)^2 7 Jun 700 705. 1 =ABS(B 7 -C 7) =(B 7 -C 7)^2 1 D E 8 9 MAD 10 MSE © 2003 Anita Lee-Post =AVERAGE(D 5: D 7) =AVERAGE(E 5: E 7)
Excel can be used to compute Tracking Signals: A B C Month Demand 3 -month Moving Average Error Cumulative Sum of Error Tracking Signal … … … 5 Apr 800 720 =B 5 -C 5 =D 5 =E 5/$D$9 6 May 900 770 =B 6 -C 6 =E 5+D 6 =E 6/$D$9 7 Jun 700 836. 7 =B 7 -C 7 =E 6+D 7 =E 7/$D$9 1 D 8 9 MAD © 2003 Anita Lee-Post 116 E F
- Slides: 20