To add a forecast, choose the Add Forecast command from the Forecast menu. The dialog shown below is presented. The field at the top shows the location on the worksheet of the upper left corner of the forecast display. This option allows an arbitrary placement of the forecast display and several forecasts may be placed on the same worksheet.

The Name field determines named ranges on the worksheet. This name must be unique for different forecasts. It must start with a letter and include no spaces or punctuation.

The Data Columns field specifies the number of individual time series that will be placed on the display. It might be useful to have more than one if the user has a several series to be simultaneously tracked such as the stock prices in a portfolio or the sales volumes for different products. Each time series will have a separate forecast.

The Time Horizon is the number of periods to be included in the series. This number can be changed using the Change command. The History is the number of periods of data used to obtain the first forecast. All the methods require this warm-up period to obtain valid forecasts. This history is important because it limits several features of the forecast. For instance the number of observations in a moving average and the number of observations in a regression forecast are limited to the number of periods in the history. The forecast interval is also limited by the history.

A nonzero entry in the Extra Data Columns field will cause the add-in to create extra columns that are placed to the left of the data columns. These might be useful for holding associated information related to the data. For example on the Investments page we use a single extra data column to hold the dates associated with closing stock prices.

A nonzero entry in the Extra Results Columns field will cause the add-in to create extra columns in the display that appear to the right of the forecasts. These might be useful for additional processing of the forecasts. This is illustrated on the Investments page where we use several extra columns to make stock buy and sell decisions.

When the Simulate button is checked, data will be simulated using Monte Carlo simulation. The results are similar to the Simulation command on the forecast menu, but the data placed on the worksheet is fixed, rather than controllable through simulation parameters.

When the Freeze Panes button is checked, the worksheet window is divided into four sections and the panes are frozen. This is useful for large data sets where the active data cells may be far removed from the row and column titles.

The Seasonal button allows the analysis of time series with cyclical variations. The number of data points in a cycle is placed in the field below the button.

The Include Forecast button determines whether the display will include forecasts for future times. For example a moving average forecast places one column on the display holding the moving average. When this button is checked, two additional columns are also included, one holding forecasts for future times and one holding error measurements for the forecast. Usually one would check this button, but for some applications future forecasts are not required. Again we illustrate this on the Investments page. The other pages of this section all include forecasts.

The dialog requires the selection of one of the forecasting methods using the buttons on the right. In the remainder of this page, we illustrate the various forecasting methods.

Clicking the OK button causes the add-in to construct the form shown at the left. Cell C3 gives the name of the data. This cell should not be changed. Cell D4 contains the number of data points to be included in the moving average. This number can be changed to observe the effects of different lengths. It can be no greater than the history, 10 in this case.

The data is in column C. Cells C10 through C19 hold data for the warm-up period. For a moving average of 10, there must be at least 10 data points available for the first forecast. The data shown is a simulated random variable with a mean of 50 and a standard deviation of 5. Column D holds the estimate of the mean of the time series. The moving average estimates only the mean. Column E shows the forecast. The time interval, t, for the estimate is set in E4 by the user. For the example t is 2. The number can be changed but is limited from above by the history. The entries in column E are offset by t periods from the moving average value from which the forecast was derived. For example, the entry in E21 is the forecast for time 2 based on the moving average computed for time 0 in cell D19. The two values are equal because the moving average assumes a constant for the underlying mean value of the time series. The column is labeled Fore(2) to indicate that it holds an estimate of the time series based on the mean value estimated two periods earlier.

Column F computes the forecast errors. The error is simply the difference between the value observed for a period and the forecasted value. For example, the entry for period 2 which appears in cell F21 is the value of the data in period 2 (cell C21) less the value of the forecast (cell E21).

The means and standard deviations of the columns are computed in rows 6 and 7. The moving average column should have less variability than the data column because moving average eliminates some of the noise variability. The error column variability depends on both the data variability and the variability of the forecasts.

Row 8 (cell F8) shows the MAD or Mean absolute deviation of the error results. This is sometimes used as a measure of forecast quality.

In practice, one would fill in a table like this one period at a time as data is observed. Data not yet available are indicated by ***, as shown for periods 16 through 20. Moving averages are computed for these periods because the moving average function looks back the specified number of periods (10) and uses as many numeric values as available. For example, the moving average for period 20 (in cell D39) is the average of the five data points for periods 11 through 15.

The selection of the number to be included in the moving average is the prerogative of the analyst. If the mean of the underlying time series is truly constant, the average should be as long as possible. In reality the mean value may be changing. If the forecast is to discover changes quickly, the number in the average should be low.

The same series is shown at the left with 5 periods in the moving average. The standard deviation of the forecast has increased because the noise has more effect because of the smaller number of periods in the average.

The display at the left shows the same data with forecasts provided by the exponential smoothing method. This method has a single parameter Alpha. A small value of Alpha tracks rapidly varying time series better than a large value, but a small value is more affected by noise. The value of Alpha for the example is in cell J4. The default value is

2/(history + 1)

Alpha can be changed by the user.

The forecast for time 0 (cell J19) is computed by a moving average with length equal to the history (10 in this case). Exponential smoothing requires such an estimate to get started.

The time interval of the estimate is in K4. The estimates for the mean of the series are in column J and the forecasts are in column K. Since exponential smoothing only estimates the mean, the forecast will be the same as the estimate t periods earlier.

The regression method is similar to the moving average method in that it uses a fixed interval to determine forecasts. The example uses 10 periods. The method takes the last 10 observations and constructs a linear regression estimate for the mean of the time series. There are two results of the regression, a constant term labeled Reg. a, and a linear term labeled Reg. b. Estimates are computed with the linear expression:

Est(t) = a + bt

where t is the interval between the time of the estimation and the estimated mean value. For example, the estimate for time 4 (cell R23) is based on the regression coefficients computed at time 3 (cells P21 and Q21). With all values rounded to two decimal places, this is

Est(2) = 49.95 + (0.22)(2) = 50.40

The numbers used by Excel have many significant digits of accuracy, but when rounded values are shown the results seem to have small errors. For the example we would expect 50.39 as the result, but actually the value 50.40 is more accurate.

The data observed for time 4 (cell O23) is: 38.03, so the error for time 4 (cell S23) is

38.30 - 50.40 = -12.11

The regression method is useful for time series that have a trend component. Again the choice of the length parameter is important. For rapidly changing series the length should be short. For slowly changing series that can include a trend the length should be long. The value of the length can be changed in cell P4, but it cannot exceed the history.

This method is also used when the series might have a trend. It is similar to the exponential method, but also estimates the value of the trend component. Like the regression method, both the constant term and the trend are estimated. It has two parameters, Alpha and Beta. The estimates for time 0 are obtained with a regression forecast.

The Add Forecast dialog allows for the analysis of more than one series . The dialog is shown below for two series.

The forecast is produced below using simulated data. Both forecasts use the same method but may have different parameters.

To illustrate the use of the Seasonal button, we consider a time series that observe the visits to a web page. Data showing the visits to the site for a 28 day period is shown below. It appears that there is a weekly variation in the data with the fewest visits on Saturday and Sunday. We have tabulated the first three weeks at the top right with the total and average number of visits per day. At the bottom right we divide the visits by the average for each week to compute the relative number of visits compared to the average. Finally we average these over the three weeks to compute an adjustment factor for the days.

To analyze the data we click the Seasonal button on the Add Forecast dialog and indicate the Season Cycle as 7. One week is specified as the history and we provide room for 28 days of data.

The forecast is produced below with the data placed in column O. Column P is for the adjustment factors. The first 7 cells in this column hold the factors computed above. The remaining cells in this column hold equations linking to the first cells. Thus changing the factors in the first 7 cells will change the entire column. The adjusted data is computed in column Q by dividing the data by the adjustment factors. Columns R and S perform the exponential smoothing with trend to forecast the adjusted data in column T. The forecasted visits in column V are computed by multiplying column T by the adjustment factors.