Two columns representing the confidence interval (calculated using ). Historical values column (your corresponding values data series)įorecasted values column (calculated using FORECAST.ETS) Historical time column (your time-based data series) The table can contain the following columns, three of which are calculated columns: The forecast predicts future values using your existing time-based data and the AAA version of the Exponential Smoothing (ETS) algorithm. When you use a formula to create a forecast, it returns a table with the historical and predicted data, and a chart. Doing this adds a table of statistics generated using the function and includes measures, such as the smoothing coefficients (Alpha, Beta, Gamma), and error metrics (MASE, SMAPE, MAE, RMSE). To use another calculation method, such as Median or Count, pick the calculation you want from the list.Ĭheck this box if you want additional statistical information on the forecast included in a new worksheet. When your data contains multiple values with the same timestamp, Excel will average the values. To treat the missing points as zeros instead, click Zeros in the list. To handle missing points, Excel uses interpolation, meaning that a missing point will be completed as the weighted average of its neighboring points as long as fewer than 30% of the points are missing. This range needs to be identical to the Timeline Range.
PREDICTIVE ANALYTICS EXCEL SERIES
This range needs to match the Values Range.Ĭhange the range used for your value series here. And when the seasonality is not significant enough for the algorithm to detect, the prediction will revert to a linear trend.Ĭhange the range used for your timeline here. With less than 2 cycles, Excel cannot identify the seasonal components. Note: When setting seasonality manually, avoid a value for less than 2 cycles of historical data. You can override the automatic detection by choosing Set Manually and then picking a number. For example, in a yearly sales cycle, with each point representing a month, the seasonality is 12. Seasonality is a number for the length (number of points) of the seasonal pattern and is automatically detected. The default level of 95% confidence can be changed using the up or down arrows. A smaller interval implies more confidence in the prediction for the specific point. Confidence interval can help you figure out the accuracy of the prediction. The confidence interval is the range surrounding each predicted value, in which 95% of future points are expected to fall, based on the forecast (with normal distribution). If your data is seasonal, then starting a forecast before the last historical point is recommended.Ĭheck or uncheck Confidence Interval to show or hide it. Using all of your historical data gives you a more accurate prediction. However, if you start the forecast too early, the forecast generated won't necessarily represent the forecast you'll get using all the historical data. Starting your forecast before the last historical point gives you a sense of the prediction accuracy as you can compare the forecasted series to the actual data. When you pick a date before the end of the historical data, only data prior to the start date are used in the prediction (this is sometimes referred to as "hindcasting"). You'll find information about each of the options in the following table. If you want to change any advanced settings for your forecast, click Options. You'll find the new worksheet just to the left ("in front of") the sheet where you entered the data series.
In the Forecast End box, pick an end date, and then click Create.Įxcel creates a new worksheet that contains both a table of the historical and predicted values and a chart that expresses this data. In the Create Forecast Worksheet box, pick either a line chart or a column chart for the visual representation of the forecast. On the Data tab, in the Forecast group, click Forecast Sheet. Tip: If you select a cell in one of your series, Excel automatically selects the rest of the data.
0 kommentar(er)
