Forecasting%20Part%203

1
ForecastingPart 3

• By
• Anita Lee-Post

2
Selecting a forecasting model
3
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

4
Overall error measures

• Mean absolute deviation (MAD)
• Mean squared error (MSE)
• The forecast technique giving the lowest MAD/MSE
  is preferred
• MSE magnifies large errors through the squaring
  process

5
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

6
Tracking signal continued
Signal exceeded limit
Tracking signal
Upper control limit 4MAD

0
0 MAD
-
Lower control limit -4MAD
Time
7
Correlation coefficient

• Correlation coefficient, r, measures the
  direction and strength of the linear relationship
  between the independent (x) and dependent (y)
  variables

8
Correlation coefficient continued

• r 1 a perfect positive linear relationship
• r 0 no relationship
• r -1 a perfect negative linear relationship

9
Using Excel for forecasting

• Enter the following demand figures for CAs
  product in an Excel worksheet

Jan Feb Mar Apr May Jun
650 700 810 800 900 700
10
Using Excel for forecasting continued

• Invoke the data analysis tool
• Tools ? Data Analysis
• If Data Analysis is not found, then
• Tools ? Add-ins ? select Analysis ToolPak

11
Using Excel for forecasting continued

• 3. Select Moving Average from the list of data
  analysis options to compute a 3-month moving
  average

12
Using Excel for 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 for forecast
  values (need to offset the input range by one
  row)

13
Using Excel for forecasting continued

• Excel-generated moving average forecasts

14
Using Excel for 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 for forecast
  values (no offset is needed)

15
Using Excel for forecasting continued

• Excel-generated exponential smoothing
  forecasts

Copy the formula in cell C7 to cell C8 to compute
the forecast for July
16
Using Excel for 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

17
Excel-generated regression analysis report
Enter the formula D17D18A8 in cell B8 to
compute the forecast for July
18
Excel can be used to compute MAD and MSE
A B C D E
1 Month Demand 3-month Moving Average Absolute Deviation Squared Error

5 Apr 800 720 ABS(B5-C5) (B5-C5)2
6 May 900 770 ABS(B6-C6) (B6-C6)2
7 Jun 700 836.7 ABS(B7-C7) (B7-C7)2
8
9 MAD AVERAGE(D5D7)
10 MSE AVERAGE(E5E7)
19
Excel can be used to compute MAD and MSE
A B C D E
1 Month Demand Exp. Smooth. (a 0.1) Absolute Deviation Squared Error

5 Apr 800 670.5 ABS(B5-C5) (B5-C5)2
6 May 900 683.5 ABS(B6-C6) (B6-C6)2
7 Jun 700 705.1 ABS(B7-C7) (B7-C7)2
8
9 MAD AVERAGE(D5D7)
10 MSE AVERAGE(E5E7)
20
Excel can be used to compute Tracking Signals
A B C D E F
1 Month Demand 3-month Moving Average Error Cumulative Sum of Error Tracking Signal

5 Apr 800 720 B5-C5 D5 E5/D9
6 May 900 770 B6-C6 E5D6 E6/D9
7 Jun 700 836.7 B7-C7 E6D7 E7/D9
8
9 MAD 116