Using Excel 2016 Forecast Feature


Excel 2016 forecast is a new feature with the ability to automatically create a forecast of your time based data. You can use this to forecast projected sales or costs for a variety of uses. It doesn’t even need to be financial data – you can use it to forecast anything that occurs over a time period.

When you use this new feature, Excel will automatically add a sheet with a forecast chart and a table showing the original data along with the forecast amount.

You will need two or more pieces of time/date data.

Below is a sample of nine months of data. Notice that there is no title for each column in the sample data. The Excel 2016 forecast will automatically add a title if there isn’t one, or will use the titles that are already there for each column.

excel 2016 forecast

Sample Forecast Data

Next, select the data series. You can click in only one cell of the data, as Excel will know to use all the data in the series. Then, under the forecast section of the data tab, click forecast sheet.

excel 2016 forecast

Excel 2016 Forecast Sheet Button

This will open up a form allowing you to change criteria before the forecast is automatically created.

You can preview the forecast chart, choose between a line chart and a column chart, change when the forecast will end, as well as several other options if you click the drop-down arrow beside ‘Options’.

excel 2016 forecast

Forecast Options

For the example data of nine months, the default forecast end was for another three months, bringing the total to one year.

If you leave all the options as default, and then clicking ‘Create’, Excel will add a new sheet to your document, placing a table and a chart as shown below to that newly added sheet.

excel 2016 forecast

Automatically Created Forecast Table

excel 2016 forecast

Automatically Created Forecast Chart

Creating a Custom Forecast

Forecast Start

You can change the month the forecast starts, by selecting months before the end of your actual data. Doing this lets you see how accurate the Excel forecast is compared what actually happened.

In the image below, the forecast month was moved from September to July. Notice that the forecast created for August and September was actually lower than what really happened. Ah, the perils of forecasting!

excel 2016 forecast

Effect of Moving the Forecast Start Date Back in Time

Confidence Interval

The confidence interval shows the possible range that the forecast could swing between, either higher than the forecast or lower.

This shows the effects of how confident you are that the forecast will be as predicted. This helps you decide how accurate the forecast could be.

A smaller percentage assumes more confidence in the forecast, and a larger percentage assumes less confidence in the forecast.

Shown below is the default 95% confidence interval, with a 25% confidence interval below it.

excel 2016 forecast

95% Confidence Interval

excel 2016 forecast

25% Confidence Interval

Seasonality

Represents the length of the seasonal pattern. The default setting is to automatically detect the pattern.

Timeline Range & Value Range

This is where you can change the range used. Both values need to match.

Fill Missing Points Using

The default is interpolation, which means that missing points will be the weighted average of its nearest points as long there is less than 30% missing.

Select zero from the drop-down list to replace them with zeros.

Aggregate Duplicates Using

If your data has more than one duplicate date value, Excel defaults to average the values. You can change it to do other functions instead of averaging here.

Include Forecast Statistics

Checking this box will add a table to the right of the data table created. This second table will show the measures used.

Conclusion

Excel’s new forecast feature really simplifies and visualizes the projecting and forecasting of values.

Try out this new Excel 2016 forecast tool, and create some amazing projections.

Let me know in the comments if you have any questions or ways that you have used this exciting new feature.

 

Leave a Reply