Simon Hurst has a quick look at Excel’s existing forecasting capabilities before looking in detail at what the new Forecast Sheet feature, and new forecast functions, add.
This is the third part of our series looking at the changes and new features in Excel 2016. In the first part we looked at an overview of the changes and covered those that related to the Office interface in particular. Then in Part 2 we used the tools in the new Get & Transform group of the Data ribbon to construct a list, based on merging two other lists, that would automatically reflect changes when refreshed.
Predicting the futureGeneral forecasting options
Excel has always had the ability to create forecasts. Formulae can be used to apply appropriate algorithms or you can use the built in Forecast functions. Even going back to Excel 2003, typing Forecast into the Insert Function search box will display four forecast functions:
As well as these, and other forecasting functions, other forecasting tools are included in the Analysis ToolPak add-in which, once installed, adds a Data Analysis option to the Data ribbon. In addition, the Excel chart engine includes the ability to add a trendline to a chart and to extend that trendline into the past and future using a variety of different calculation methods:
(Click to expand)
To add a trendline to an appropriate type of Excel chart you just right-click one the series and choose Add Trendline… You can then choose the type of calculation to use to calculate the trend and also extend the trendline a given number of periods Forward or Backward. In this example we have also chosen to display the equation used for the calculation on the face of the chart.
Excel 2016 Forecast Sheet
Perhaps the most significant of the 2016 enhancements to Excel’s forecasting capability is the inclusion of the Forecast Sheet option in the Forecast group of the Data ribbon tab. Microsoft refer to this as ‘One click forecasting’...
Register for free with AccountingWEB and log in to see the full, illustrated version of this article, which explains how to use all the Excel 2016 Forecast Sheet features.