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.
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.
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’.
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.
Creating a Custom Forecast
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!
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.
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.
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.