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:
- Alpha parameter of ETS algorithm Returns the base value parameter: a higher value gives more weight to recent data points.
- Beta parameter of ETS algorithm Returns the trend value parameter: a higher value gives more weight to the recent trend.
- Gamma parameter of ETS algorithm Returns the seasonality value parameter: a higher value gives more weight to the recent seasonal period.
- MASE metric Returns the mean absolute scaled error metric: a measure of the accuracy of forecasts.
- SMAPE metric Returns the symmetric mean absolute percentage error metric: an accuracy measure based on percentage errors.
- MAE metric Returns the symmetric mean absolute percentage error metric: an accuracy measure based on percentage errors.
- RMSE metric Returns the root mean squared error metric: a measure of the differences between predicted and observed values.
- 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:
Here are all of the formulas I used in the example you have just seen
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:
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 …
25th October, 2018