# Forecast Tools

## 8.4.1.1. Exponential Smoothing Tool

Figure 8-34Exponential Smoothing Tool Dialog

The Exponential Smoothing tool performs the exponential smoothing for the given set or sets of values. It provides the choice of 5 different exponential smoothing methods:

• Simple exponential smoothing according to (Hunter, 1968).
• Simple exponential smoothing according to (Roberts, 1959).
• Holt's trend corrected exponential smoothing (occasionally also referred to as double exponential smoothing)
• Multiplicative Holt-Winters exponential smoothing (occasionally also referred to as triple exponential smoothing)

Since the kind of options available depend on the type of exponential smoothing desired, you can choose the type on the Input page.

### 8.4.1.1.1. Common Options of the Exponential Smoothing Tool

Specify the cells containing the datasets in the Input Range entry. The entered range or ranges are grouped into datasets either by rows or by columns.

If you have labels in the first cell of each data set, select the Labels option.

If you select the Include chart option, Gnumeric will also create a chart showing both the data and corresponding smoothed values.

### 8.4.1.1.2. Exponential Smoothing According to Hunter

Each value in the smoothed set is predicted based on the forecast for the prior period. The formula is given in Figure 8-35. α is the value given as Damping factor. yt is the tth value in the original data set and lt the corresponding smoothed value.

Figure 8-35Exponential Smoothing Formula According To Hunter

For example, a value for α between 0.2 and 0.3 represents 20 to 30 percent error adjustment in the prior forecast.

If you choose to have the tool enter formulæ rather than values into the output region, then you can modify the damping factor α even after you executed the tool.

To have the standard errors output as well, check the Standard error check box. The formula used is given in Figure 8-36. The denominator can be adjusted by selecting the appropriate radio button. Since there are t−1 terms in the sum of the denominator, selecting n−1 means that the denominator will be t−2.

Figure 8-36The Standard Error Formula For Exponential Smoothing According To Hunter

If you check the Include chart check box, a line graph showing the observations yt and the predicted values lt will also be created.

Example 8-7Using the Exponential Smoothing Tool

Figure 8-37 shows some example data, Figure 8-38 the selected options and Figure 8-39 the corresponding output.

Figure 8-37Some Example Data for the Exponential Smoothing Tool
Figure 8-38The Options for the Exponential Smoothing Tool
Figure 8-39Exponential Smoothing Tool Output (Hunter)

### 8.4.1.1.3. Exponential Smoothing According to Roberts

The simple exponential smoothing method according to Roberts is used for forecasting a time series without a trend or seasonal pattern, but for which the level is nevertheless slowly changing over time. The predicted values are calculated according to the formula given in Figure 8-40. α is the value given as Damping factor. yt is the tth value in the original data set and lt the predicted value. l0 is the predicted value at time 0 and must be estimated. This tool uses the average value of the first 5 observations as estimate.

If you choose to have the tool enter formulæ rather than values into the output region, then you can modify the damping factor α and the estimated value at time 0 after executing the tool.

Figure 8-40Exponential Smoothing Formula According To Roberts

To have the standard errors output as well, check the Standard error check box. The formula used is given in Figure 8-41. The denominator can be adjusted by selecting the appropriate radio button.

Figure 8-41The Standard Error Formula For Exponential Smoothing According To Roberts

If you check the Include chart check box, a line graph showing the observations yt and the predicted values lt will also be created.

Example 8-8Using the Exponential Smoothing Tool

Figure 8-42 shows example output for the exponential smoothing tool using the formula according to Roberts. Cell A4 contains the estimated level at time 0. If you requested to have formulæ rather than values entered into the sheet, then changing the estimate in A4 and/or the value for α in A2 will result in an immediate change to the predicted values.

Figure 8-42Exponential Smoothing Tool Output (Roberts)

### 8.4.1.1.4. Holt's Trend Corrected Exponential Smoothing

Holt's trend corrected exponential smoothing is appropriate when both the level and the growth rate of a time series are changing. (If the time series has a fixed growth rate and therefore exhibits a linear trend, a linear regression model is more appropriate.)

yt is the true value at time t, lt is the estimated level at time t and bt is the estimated growth rate at time t. We use the two smoothing equations given in Figure 8-43 to update our estimates. α is the value given as Damping factor and γ is the value given as Growth damping factor.

This tool obtains initial (time 0) estimates for the level and growth rate by performing a linear regression using the first 5 data values.

Figure 8-43Formulae Of Holt's Trend Corrected Exponential Smoothing

If you choose to have the tool enter formulæ rather than values into the output region, then you can modify the damping factors α and γ as well as the estimated level and growth rate at time 0 after executing the tool.

To have the standard errors output as well, check the Standard error check box. The formula used is given in Figure 8-44. The denominator can be adjusted by selecting the appropriate radio button.

Figure 8-44The Standard Error Formula For Holt's Trend Corrected Exponential Smoothing

If you check the Include chart check box, a line graph showing the observations yt and the estimated level values lt will also be created.

Example 8-9Using the Exponential Smoothing Tool

Figure 8-45 shows example output for Holt's trend corrected exponential smoothing. Cell A4 contains the estimated level at time 0 and B4 the estimated growth rate at time 0. If you requested to have formulæ rather than values entered into the sheet, then changing the estimates in A4, B4, the values for α in A2 and/or for γ in B2 will result in an immediate change to the predicted values.

Figure 8-45Exponential Smoothing Tool Output (Holt's)

The additive Holt-Winters method of exponential smoothing is appropriate when a time series with a linear trend has an additive seasonal pattern for which the level, the growth rate and the seasonal pattern may be changing. An additive seasonal pattern is a pattern in which the seasonal variation can be explained by the addition of a seasonal constant (although we allow for this constant to change slowly.)

yt is the true value at time t, lt is the estimated level at time t, bt is the estimated growth rate at time t and st is the estimated seasonal adjustment for time t. We use the three smoothing equations given in Figure 8-46 to update our estimates. α is the value given as Damping factor, γ is the value given as Growth damping factor and δ is the value given as Seasonal damping factor. L is the value given as Seasonal period. If your data consist of monthly values, then L should be 12, if it consist of quarterly values then L should be 4.

This tool obtains initial (time 0) estimates for the level and growth rate by performing a linear regression using all data values. It obtains estimates for the seasonal adjustments by averaging the appropriate seasonal differences from values predicted by linear regression alone.

Figure 8-46Exponential Smoothing Formulae Of The Additive Holt-Winters Method

If you choose to have the tool enter formulæ rather than values into the output region, then you can modify the damping factors α, γ and δ as well as all estimates after executing the tool.

To have the standard errors output as well, check the Standard error check box. The formula used is given in Figure 8-47. The denominator can be adjusted by selecting the appropriate radio button.

Figure 8-47The Standard Error Formula Of The Additive Holt-Winters Method

If you check the Include chart check box, a line graph showing the observations yt and the estimated level values lt will also be created.

Example 8-10Using the Exponential Smoothing Tool

Figure 8-48 shows the options' tab of the exponential smoothing tool for the additive Holt-Winters method. The data is expected to have a seasonal period of 4 (this would for example happen if we have a data value for each quarter of a year). Figure 8-49 shows the corresponding example output for the additive Holt-Winters method. Cell C7 contains the estimated level at time 0, D7 the estimated growth rate at time 0, and E4 to E7 the initial seasonal adjustments for each of the 4 seasons preceding our data time period. If you requested to have formulæ rather than values entered into the sheet, then changing any of these estimates, the values for α in A2, for γ in B2 and/or for δ in C2 will result in an immediate change to the estimated values.

Figure 8-48Exponential Smoothing Tool Options (Additive Holt-Winters))
Figure 8-49Exponential Smoothing Tool Output (Additive Holt-Winters)

### 8.4.1.1.6. Multiplicative Holt-Winters Method

The multiplicative Holt-Winters method of exponential smoothing is appropriate when a time series with a linear trend has a multiplicative seasonal pattern for which the level, the growth rate and the seasonal pattern may be changing. A multiplicative seasonal pattern is a pattern in which the seasonal variation can be explained by the multiplication of a seasonal constant (although we allow for this constant to change slowly.)

yt is the true value at time t, lt is the estimated level at time t, bt is the estimated growth rate at time t and st is the estimated seasonal adjustment for time t. We use the three smoothing equations given in Figure 8-50 to update our estimates. α is the value given as Damping factor, γ is the value given as Growth damping factor and δ is the value given as Seasonal damping factor. L is the value given as Seasonal period. If your data consist of monthly values, then L should be 12, if it consist of quarterly values then L should be 4.

This tool obtains initial (time 0) estimates for the level and growth rate by performing a linear regression using the data values of the first 4 seasonal periods. It obtains estimates for the seasonal adjustments by averaging the appropriate seasonal differences from values predicted by linear regression alone during the first 4 seasonal periods.

Figure 8-50Exponential Smoothing Formulae Of The Multiplicative Holt-Winters Method

If you choose to have the tool enter formulæ rather than values into the output region, then you can modify the damping factors α, γ and δ as well as all estimates after executing the tool.

To have the standard errors output as well, check the Standard error check box. The formula used is given in Figure 8-51. The denominator can be adjusted by selecting the appropriate radio button.

Figure 8-51The Standard Error Formula Of The Multiplicative Holt-Winters Method

If you check the Include chart check box, a line graph showing the observations yt and the estimated level values lt will also be created.

Example 8-11Using the Exponential Smoothing Tool

Figure 8-52 shows the example output for the multiplicative Holt-Winters method, assuming 4 seasons. Cell C7 contains the estimated level at time 0, D7 the estimated growth rate at time 0, and E4 to E7 the initial seasonal adjustments for each of the 4 seasons preceding our data time period. If you requested to have formulæ rather than values entered into the sheet, then changing any of these estimates, the values for α in A2, for γ in B2 and/or for δ in C2 will result in an immediate change to the estimated values.

Figure 8-52Exponential Smoothing Tool Output (Multiplicative Holt-Winters)

## 8.4.1.2. Moving Average Tool

Figure 8-53Moving Average Tool Dialog

Use the moving average tool to calculate moving averages of one or more data sets. A moving average provides useful trend information of the data that is lost in a simple average. In addition, moving averages can be used to eliminate random variance. For example, use this tool to create a smoother curve of a stock prize.

Specify the cells containing the datasets in the Input Range entry. The entered range or ranges are grouped into datasets either by rows or by columns.

If you have labels in the first cell of each data set, select the Labels option.

Choose the type of moving average you would like to calculate. The tool can determine 4 types of moving averages:

1. Simple moving average
2. Cumulative moving average
3. Weighted moving average
4. Spencer's 15 point moving average
Figure 8-54 Moving Average Tool Dialog: The Options Tab

Specify the Interval for the moving average. The interval i is the number of consecutive values to be included in each moving average. This options is only available for the simple and weighted moving averages.

Check the Standard errors checkbox if you would also like the standard error to be calculated. Since there is no general agreement on the denominator for the standard error you can choose the appropriate radio button.

In the case of the simple moving average, you can also choose between a prior moving average and a central moving average, or you may even specify any other desired offset.

1. Prior moving average: Each average takes into account the current observation and the most recent prior observations for a total of i observations.
2. Central moving average with i being odd: Each average takes into account the current observation and the same number of most recent prior observations and closest future observations for a total of i observations.
3. Central moving average with i being even: This is calculated according to the formula given in Figure 8-55. at is the moving average at time t and yt is the observation at time t.
4. Other offset: If the offset is 0, this is just the prior moving average. Otherwise the offset indicates the number of closest future observations to include in the average. Correspondingly, the number of most recent past observations is decreased.
Figure 8-55Formula For The Central Moving Average With Even Interval

The results are given in one column for each dataset (with a second column added if you have chosen standard errors to be calculated). Each row represents the moving average of the corresponding row or column in the input range. Depending on the type of average and the offset, the moving average cannot be calculated for the first rows in the input range.

### 8.4.1.2.1. Simple Moving Average

A simple moving average is the unweighted average of a collection of observations. Exactly which observations are included depends on whether a prior or central moving average is calculated.

### 8.4.1.2.2. Cumulative Moving Average

A cumulative moving average is a prior moving average in which the current and all prior observations are included.

### 8.4.1.2.3. Weighted Moving Average

A weighted moving average with an interval i is a prior moving average calculated according to formula Figure 8-55. at is the moving average at time t and yt is the observation at time t.

Figure 8-56Formula For The Weighted Moving Average With Interval i

### 8.4.1.2.4. Spencer's 15 Point Moving Average

Spencer's 15 point moving average is a central moving average calculated according to formula Figure 8-57. at is the moving average at time t and yt is the observation at time t.

Figure 8-57Formula For Spencer's 15 Point Moving Average

### 8.4.1.2.5. A Moving Average Example

Figure 8-58Some Example Data for the Moving Average Tool
Example 8-12Using the Moving Average Tool

Figure 8-58 shows some example data, Figure 8-59 shows the option settings, and Figure 8-60 the corresponding output.

Figure 8-59Moving Averages Tool Options
Figure 8-60Moving Averages Tool Output