Microsoft Excel 2016 – One-Click Forecasting

The Forecast Sheet button in Excel 2016 lets you use historical time-based data to create a forecast visualization that predicts things like future sales, inventory requirements, or consumer trends.

Here’s what you do:

In a worksheet, enter two data series that correspond to each other:

·      A series with date or time entries for the timeline,

·      A series with corresponding values,

These values will be predicted for future dates.

Note: The timeline requires consistent intervals between its data points. For example, monthly intervals with values on the 1st of every month, yearly intervals, or numerical intervals. It’s okay if your timeline series is missing up to 30% of the data points, or has several numbers with the same time stamp. The forecast will still be accurate. However, summarizing data before you create the forecast will produce more accurate forecast results.

Select both data series.

Tip: If you select a cell in one of your series, Excel automatically selects the rest of the data.

On the Data tab, in the Forecast group, click Forecast Sheet.

 

In the Create Forecast Worksheet box:

Pick either a line chart or a column chart for the visual representation of the forecast,

In the Forecast End box, pick an end date, and then click Create.

Excel creates a new worksheet that contains both a table of the historical and predicted values and a chart that expresses this data.

You'll find the new worksheet tab just to the left ("in front of") the sheet where you entered the data series.

Customize your forecast:

If you want to change any advanced settings for your forecast, click Options (at the bottom of the Create Forecast Worksheet box).