Edit

Workbook.CreateForecastSheet method (Excel)

  • Article

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.

Syntax

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

expression A variable that represents a Workbook object.

Parameters

Parameter Required/Optional Data type Description
Timeline Required 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 Required Range The historical values for which you want to forecast the next points.
ForecastStart Optional Variant The point from which the generated forecast will begin.
ForecastEnd Optional Variant The point in which the generated forecast will end.
ConfInt Optional Variant 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 Optional Variant 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 Optional Variant Can be one of these XlForecastDataCompletion constants: xlDataCompletionZeros or xlDataCompletionInterpolate (default).
Aggregation Optional Variant Can be one of these XlForecastAggregation constants: xlAggregationAverage (default), xlAggregationCount, xlAggregationCountA, xlAggregationMax, xlAggregationMedian, xlAggregationMin, or xlAggregationSum.
ChartType Optional Variant Can be one of these XlForecastChartType constants: xlChartTypeLine (default) or xlChartTypeColumn.
ShowStatsTable Optional Variant 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.

Return value

None

Remarks

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:

  • Historical time column (your time-based data series)

  • Historical values column (your corresponding values data series)

  • Forecasted values column (calculated by using FORECAST_ETS)

  • Two columns representing the confidence interval (calculated by using FORECAST_ETS_CONFINT)

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.