{"id":178553,"date":"2015-10-06T17:53:45","date_gmt":"2015-10-07T00:53:45","guid":{"rendered":"http:\/\/www.microsoft.com\/?p=178553"},"modified":"2024-08-30T13:42:29","modified_gmt":"2024-08-30T20:42:29","slug":"one-click-forecasting-in-excel-2016","status":"publish","type":"post","link":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2015\/10\/06\/one-click-forecasting-in-excel-2016\/","title":{"rendered":"One-click forecasting in Excel 2016"},"content":{"rendered":"

\u201cIt is difficult to make predictions, especially about the future,\u201d said Danish physicist, Niels Bohr. Well, it just got a lot easier with Excel\u2019s one-click forecasting.<\/p>\n

Often we use Excel to analyze time-based series data\u2014like sales, server utilization or inventory data\u2014to find recurring seasonality patterns and trends. In Excel 2016<\/a>, new forecasting sheet functions and one-click forecasting helps you to explain the data and understand future trends.<\/p>\n

\"One<\/p>\n

Let\u2019s take a closer look at some of the capabilities that come with these new features, including how to detect seasonality, understand the level of confidence in the prediction, and create the forecast in one-click.<\/p>\n

Using Exponential Smoothing for forecasting<\/h3>\n

There are many ways to generate a forecast for your historical data. Before Excel 2016, many used the FORECAST()<\/strong> sheet function, which performs a linear forecast or extended trendlines in chart properties to extrapolate forward.<\/p>\n

The new functionality in Excel 2016 utilizes another algorithm, called Exponential Smoothing or ETS. Exponential Smoothing methods are a popular way to forecast and are among the leading methods that have become industry standards.<\/p>\n

The main advantages of using the ETS method are the ability to detect seasonality patterns and confidence intervals.<\/p>\n

Seasonality patterns<\/h3>\n

In many business scenarios there is a seasonality pattern that we would like to take into account in the forecast. An example of this could be ice cream sales presented in monthly data. We would expect to have a yearly cycle in this case, which would repeat itself every 12 points (months).\u00a0Another example is hourly traffic data, where a seasonality of 24 points (hours) makes sense.<\/p>\n

In the example below you can see how a yearly seasonality was detected and applied in the forecast. Since the data is monthly and repeats every 12 points, the detected seasonality was 12.<\/p>\n

In some cases we know what the seasonality length is, but in other cases we do not. This forecasting method looks for seasonality patterns in the historical data and tries to determine the pattern that best matches the data. For this to work properly, the more repeating cycles the historical data contains the better. It is recommended to have at least 2\u20133 full seasonal cycles in the historical data. The automatically detected value in the Seasonality section can be found in the Create Forecast<\/strong> dialog under Options<\/strong>. In case the seasonal data was not significant enough to be detected, or you know which seasonality you expect, you can manually override the automatically detected value by selecting Set<\/strong> Manually<\/strong>.<\/p>\n

\"One<\/p>\n

Confidence intervals<\/h3>\n

Apart from predicting future values for the input time series, the ETS forecast can also return a confidence interval.<\/p>\n

The confidence interval is the range surrounding each predicted value in which 95 percent of future points are expected to fall based on the forecast (with normal distribution). The confidence interval helps you figure out the accuracy of the prediction. A smaller interval implies more confidence in the prediction for the specific point. The default level of 95 percent confidence can be changed using the up or down arrows and can be used in two ways:<\/p>\n