FORECAST Functions

Introduction

Someone asked me the other day how I use the forecast functions in Excel. As I started thinking about it, I realised just how many FORECAST functions there are in Office365 Excel: here they all are:
FORECAST
FORECST.LINEAR
FORECAST.ETS
FORECAST.ETS.CONFINT LOWER
FORECAST.ETS.CONFINT UPPER
FORECAST.ETS.SEASONALITY
FORECAST.ETS.STAT
Rather than going on and on about these functions, take a look at their syntax and then look at what I did.

FORECAST Syntax

  • =FORECAST(x, known_ys,known_xs)
  • =FORECAST.LINEAR(x, known_ys,known_xs)
  • =FORECAST.ETS(forecast_date,values,timeline,[seasonality],[data_completion],[aggregation])
  • =FORECAST.ETS.CONFINT(target_date,values,timeline,[confidence_level],[seasonality],[data_completion],[aggregation])
  • =FORECAST.ETS.SEASONALITY(values,timeline,[data_completion],[aggregation])
  • =FORECAST.ETS.STAT(values,timeline,statistic_type,[seasonality],[data_completion],[aggregation]) …
The Confidence Interval function can be used to find both the lower and upper limits: see the example below for ideas in action. The Statistic Types to choose from are:
  1. Alpha parameter of ETS algorithm    Returns the base value parameter: a higher value gives more weight to recent data points.
  2. Beta parameter of ETS algorithm    Returns the trend value parameter: a higher value gives more weight to the recent trend.
  3. Gamma parameter of ETS algorithm    Returns the seasonality value parameter: a higher value gives more weight to the recent seasonal period.
  4. MASE metric    Returns the mean absolute scaled error metric: a measure of the accuracy of forecasts.
  5. SMAPE metric    Returns the symmetric mean absolute percentage error metric: an accuracy measure based on percentage errors.
  6. MAE metric    Returns the symmetric mean absolute percentage error metric: an accuracy measure based on percentage errors.
  7. RMSE metric    Returns the root mean squared error metric: a measure of the differences between predicted and observed values.
  8. Step size detected    Returns the step size detected in the historical timeline.
Here is my example that shows all of these functions in action and you can download my working file from the link at the bottom of the page. Click on the images to enlarge them:
Same Data Used for All Functions … contrast the forecasts in Columns C, D and E 
Here are all of the formulas I used in the example you have just seen
All Functions and Formulas, as Programmed
For those of you who are using Office365 Excel, here is a screenshot of the results of using the Forecast Sheet: compare the results with the .ETS, .CONFINT, .ETS.STAT functions I used:
Results of the Forecast Sheet Utility

Conclusions

There are other functions and methods that we can use in forecasting in Excel but these are the six dedicated functions currently provided. If forecasting is important to you, open my file, work through it and then make sure you read all about the functions I have used. Download the file … 
forecasting_functions_oct_2018Download
Duncan Williamson 25th October, 2018