This is the third part of a 3-part series on forecasting



Forecasting- Part Three

Slide One:

This is the third part of a 3-part series on forecasting. We will discuss the criteria for choosing a forecasting model and how to use Excel’s Data Analysis Tool for forecasting.

Slide Two:

The flow chart depicts the logic involved in forecasting model selection. A forecaster starts by choosing a qualitative or quantitative model depending on the type of data available. Next, either a time series or a causal model is picked according to the basis of forecast. If a time series model is chosen, then the appropriate type of forecasting technique should be used depending on whether the level or trend or seasonality component of the data pattern is present in the series. If a casual model is chosen, then linear regression is used if the underlying relationship is linear. We have discussed at length the time series models and linear regression in part 2. We now turn our attention to the task of evaluating the models chosen. Time series models are evaluated by their forecast accuracy using measures called mean absolute deviation (MAD) or mean squared error (MSE), and tracking signal. Linear regression is evaluated by its model strength as indicated by a measure called correlation coefficient.

Slide Three:

We will discuss how to evaluate a time series model first. Earlier on in part 1, we talked about a good forecast should be accurate but not perfect. In other words, an accurate forecast is one that matches closely but not exactly with the actual value. The difference between the forecast and actual values is referred to as the forecast error. Thus, one requirement for a good forecast is that its forecast error should be close to zero. However, a forecast that is accurate once awhile is of little interest. Instead we need to be assured of its accuracy over time by deriving an overall measure of forecast errors. Two most commonly used measures called the mean absolute deviation (MAD) and the mean squared error (MSE) will be introduced for such purpose. Finally, just knowing how to measure forecast errors is not enough unless we have a way to ascertain whether a forecast error of certain magnitude is reasonable or not. You will learn a way to monitor forecast accuracy using tracking signal.

Slide Four:

Two of the most commonly used forecast error measures are mean absolute deviation (MAD) and mean squared error (MSE). MAD is the average of the absolute errors. MSE is the average of the squared errors. Errors of opposite signs will not cancel each other out in either measures. However, by squaring the errors, MSE is more sensitive to large errors. Either MAD or MSE can be used to compare the performance of different forecasting techniques. The best technique is the one that yields the lowest MAD/MSE.

Slide Five:

As explained earlier, forecast errors need to be monitored to ascertain whether or not an error of certain magnitude signifies problems with the forecasting process. By following the control chart principle that you learned in quality control, a way of monitoring the forecast accuracy is accomplished by comparing a measure called tracking signal against predetermined control limits.

Slide Six:

Assuming the forecast errors are normally distributed with a mean of 0, a three-standard deviation spread from the mean will be equivalent to having a control chart with the upper and lower control limits set at 4 and -4 respectively from a center line of 0. If the tracking signal falls outside the control limits, as shown in this slide, we have to review the forecasting process.

Slide Seven:

In causal relationship forecasting, forecast errors as discussed earlier are already minimized when a least squares line is derived. Therefore, the strength of the relationship between the independent and dependent variables rather than forecast errors is the basis for causal model assessment. In linear regression, the direction and strength of the linear relationship between the two variables is captured by a measure called correlation coefficient (or r).

Slide Eight:

The value of r ranges from -1 to +1. An r value of +1/-1 is indicative of a perfect positive/negative linear relationship. An r value of 0 means there is no relationship between the two variables. Thus, the closer the value of r to 1 irrespective of the sign, the stronger is the linear relationship between the two variables.

Slide Nine:

We will use the time series data in part 2 to illustrate how to use Excel for forecasting. The first step is to enter the six months’ demand figures in a worksheet.

Slide Ten:

The second step is to invoke the analysis tools. To do so, choose “Data Analysis” from the “Tools” menu in Excel. If “Data Analysis” is not found, you will need to add it in by selecting the “Analysis ToolPak” from the “Add-ins” menu.

Slide Eleven:

The data analysis tools can be used to generate three of the forecasts that we learned in this series: simple moving average, exponential smoothing, and linear regression. The third step is to select the appropriate forecast technique from the Data Analysis window. If a moving average forecast is needed, we will select “Moving Average” from the list of data analysis options before clicking the OK button.

Slide Twelve:

There are 4 parameters in the Moving Average window that needs to be filled out. The “Input Range” refers to the cell range of the time series. In our example, it should be B2:B7. The “Labels in First Row” checkbox should be left unchecked if your cell range includes data points only as in this case. The “Interval” refers to the value of the parameter n which is the number of data points used in moving average computation. A 3-month moving average is of interest here, so a value of 3 is entered for the “Interval” parameter. Finally the “Output Range” refers to the starting cell address for forecast values. Since the forecast is intended for the next time period, we will provide the starting cell address one row down from that of the input range. In our example, the input range starts in row 2. Thus, the output range should start in row 3 and cell address C3 is entered for the “Output Range” parameter.

Slide Thirteen:

After the Moving Average parameters are filled out and the OK button is clicked, Excel will produce in the output range a list of moving averages for months that are applicable. In our case, a 3-month moving average for July is shown to be 800.

Slide Fourteen:

The previous discussed steps are repeated if an exponential smoothing average is sought. Here we will show you the parameters that needed to be filled out in the exponential smoothing window after “Exponential Smoothing” is selected from the “Data Analysis” window. Notice that instead of specifying the smoothing coefficient alpha here, Excel asks for a “Damping factor” which is equivalent to 1-alpha. Also, unlike the moving average computation, no offset is needed between the input and output ranges in exponential smoothing.

Slide Fifteen:

After the Moving Average parameters are filled in and the OK button is clicked, Excel will produce in the output range a list of exponential smoothing averages for months that are applicable. Notice that Excel starts the smoothing process in February by using the actual demand of January as its forecast value. To produce a forecast for July, the formula in cell C7 is copied into cell C8 yielding a value of 704.6.

Slide Sixteen:

If a linear regression forecast is sought, we need to first change the values in the “Month” column from text into numbers. After that, “Regression” should be selected from the “Data Analysis” window. Here we will show you the parameters needed to be filled out in the Regression window. Unlike the previous techniques, we will include the column headings in our input Y and X ranges, which implies the “Labels” box should be checked. In addition, as opposed to generating the forecast values in the “Output Range”, a regression analysis report called SUMMARY OUTPUT will be produced instead.

Slide Seventeen:

After the Regression parameters are filled in and the OK button is clicked, Excel will produce a regression analysis report in the output range. The parameters a and b for the regression line can be located from the last section of the SUMMARY OUTPUT report under the column labeled “Coefficients”. The Intercept coefficient in cell D17 gives a value of 676 for parameter a. The Month coefficient in cell D18 gives a value of 24 for parameter b. Thus, the forecast of July can be computed by entering a formula =D17+D18*A8 in cell B8 to yield a value of 844. Lastly, the strength of the relationship between month and demand can be found from the Regression Statistics section of the SUMMARY OUTPUT report in cell D4. The correlation coefficient or “Multiple R” in cell D4 is 0.48 which implies a fairly weak relationship.

Slide Eighteen:

In addition to producing forecast values, Excel is a useful tool to generate forecast accuracy measures, as shown in this illustration. To use Excel to compute MAD and MSE for the 3-month moving average forecasts, enter the formula =ABS(B5-C5) in cell D5 for the absolute deviation of April. Enter the formula =(B5-C5)^2 in cell E5 for the squared error of April. Copy these formulas to cells D6 to E7 to compute the absolute deviations and squared errors of May and June. Enter a formula =AVERAGE(D5:D7) and =AVERAGE(E5:E7) to calculate MAD and MSE respectively. The MAD and MSE for the 3-month moving average forecasts are found to be 116 and 13,993 accordingly.

Slide Nineteen:

Repeat the same procedure to compute MAD and MSE for the exponential smoothing forecasts with alpha=0.1. To be consistent with the moving average case, the forecast accuracy of exponential smoothing is calculated based on the last three months of data as before. MAD and MSE are found to be 117 and 21,230 respectively. Since the moving average technique yields a lower MAD and MSE, it is superior to exponential smoothing.

Slide Twenty:

Finally, we will demonstrate how to use Excel to compute tracking signals for the 3-month moving average forecasts. Recall that tracking signal is computed by dividing the cumulative sum of error by MAD. First we will enter a formula =B5-C5 in cell D5 for the forecast error of April. Copy this formula from cell D5 to cells D6 and D7 for the error of May and June. Next enter the formula =D5 in cell E5 for April’s cumulative sum of error. Enter the formula =E5+D6 for May’s cumulative sum of error. Copy this formula to cells E7. Finally, enter a formula =E5/$D$9 in cell F5 to compute April’s tracking signal. Copy this to cells F6 and F7. The tracking signals for April, May, and June are found to be 0.69, 1.82, and 0.63. All of which are within +/- 4. Thus, it can be concluded that 3-month moving average is a proper forecasting technique for C&A.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download