Excel 2016 includes 5 new forecast functions.
FORECAST.LINEAR
This function predicts a y value (eg sales) for a given x value (eg a forecast date). You need to provide known y’s and known x’s which could be for example sales and sales date respectively.
=FORECAST.LINEAR(x, known_y’s, known_x’s)
The forecast is predicted on a linear basis and is therefore not particularly useful if your data follows seasonal variances.
FORECAST.ETS
This function predicts a future value based on historical values. It uses the Exponential Triple Smoothing (ETS) algorithm, which:
- estimates trends
- estimates seasonality
- gives most weight to recent data points (declining exponentially)
=FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation])
Target_date – the date for which you want to predict a value.
Values – the historical values – for example past sales
Timeline – the range of cells that show corresponding dates for your values. The dates in the timeline must have a consistent step between them eg month, day, year – you can’t mix the steps between the dates.
Seasonality – (Optional). If not specified Excel will automatically detect seasonality. 0 would indicate that there is no seasonality, meaning the prediction will be linear. Seasonality (the length of the season) can be specified with a positive whole number (number of date periods).
Data completion – (Optional). FORECAST.ETS will automatically adjust for up to 30% missing data. Enter 1 or leave omitted to calculate an average of neighbouring values. 0 to treat missing points as zeros.
Aggregation – (Optional). FORECAST.ETS will aggregate multiple points which have the same time stamp. Enter 0 or leave omitted to use AVERAGE, or specify one of the following: SUM, COUNT, COUNTA, MIN, MAX, MEDIAN.
FORECAST.ETS.SEASONALITY
Can be used to identify which automatic seasonality was detected and used in FORECAST.ETS.
=FORECAST.ETS.SEASONALITY(values, timeline, [data_completion], [aggregation])
FORECAST.ETS.CONFINT
Returns a confidence interval (how close to the predicted value the forecast is likely to be). The lower the confidence interval the more accurate the forecast. So a confidence interval of 5 would indicate the forecast value could be 5 points above or below the forecast value on a particular date. The confidence interval is linked to the confidence level which is automatically set at 95%. By stating a confidence of level of 95% we are saying that 95% of future date points will lie within upper and lower bounds of the confidence interval.
=FORECAST.ETS.CONFINT(target_date, values, timeline, [confidence_level], [seasonality], [data_completion], [aggregation])
Confidence_level – (Optional). The default is 95%. A numerical value between 0 and 1 (exclusive), can be used to indicate another confidence level.
FORECAST.ETS.STAT
Returns a statistical value relating to forecasting.
Statistic type indicates which statistic is requested by this function.
=FORECAST.ETS.STAT(values, timeline, statistic_type, [seasonality], [data_completion], [aggregation])
See Microsoft’s page on this function.
Check out our video on the new forecast functions. Download the featured file here.