Workbook.CreateForecastSheet (Excel)

If you have historical time-based data, you can use CreateForecastSheet to create a forecast. When you create a forecast, a new worksheet is created that contains a table of the historical and predicted values and a chart showing this. A forecast can help you predict things like future sales, inventory requirements, or consumer trends.

When you use a formula to create a forecast, it returns a table with the historical and predicted data and a chart. The forecast predicts future values by using your existing time-based data and the AAA version of the Exponential Smoothing (ETS) algorithm. The table has the following columns, three of which are calculated columns:

CreateForecastSheet (Timeline, Values, ForecastStart, ForecastEnd, ConfInt, Seasonality, DataCompletion, Aggregation, ChartType, ShowStatsTable)

ActiveWorkbook.CreateForecastSheet Timeline:=, Values:=

Arguments

The following arguments are required:

Timeline (Range) - The independent array or range of numeric data. The dates in the timeline must have a consistent step between them and can't be zero. The timeline isn't required to be sorted because the forecast mechanism will sort it implicitly for calculations. If a constant step can't be identified in the provided timeline, an invalid procedure call or argument (Error 5) will be returned.

Values (Range) - The historical values for which you want to forecast the next points.

Optional arguments

The following arguments are optional

ForecastStart - The point from which the generated forecast will begin

ForecastEnd - The point in which the generated forecast will end

ConfInt - A numerical value between 0 and 1 (exclusive), indicating a confidence level for the calculated confidence interval. For example, for a 90% confidence interval, a 90% confidence level will be computed (90% of future points are to fall within this radius from prediction). The default value is 95%

Seasonality - A numerical value. The default value of 1 means Excel detects seasonality automatically for the forecast and uses positive, whole numbers for the length of the seasonal pattern. 0 indicates no seasonality, meaning the prediction will be linear. Positive whole numbers will indicate to the algorithm to use patterns of this length as the seasonality. For any other value, Error 5 will be returned. Maximum supported seasonality is 8,760 (number of hours in a year). Any seasonality above that number will result in the Error 5

DataCompletion (XlForecastDataCompletion) - Can be one of these XlForecastDataCompletion constants: xlDataCompletionZeros or xlDataCompletionInterpolate (default).

Possible return values are xlForecastDataCompletionInterpolate, xlForecastDataCompletionZeros.

Aggregation (XlForecastAggregation) - Can be one of these XlForecastAggregation constants: xlAggregationAverage (default), xlAggregationCount, xlAggregationCountA, xlAggregationMax, xlAggregationMedian, xlAggregationMin, or xlAggregationSum.

Possible values are

ChartType (XlForecastChartType) - Can be one of these XlForecastChartType constants: xlChartTypeLine (default) or xlChartTypeColumn.

Possible return values are xlForecastChartTypeColumn, xlForecastChartTypeLine.

ShowStatsTable (Boolean) - True or False. If True, an additional table is generated in the created sheet. This table contains statistical measures that indicate the accuracy of the created forecast.