Excel 2016: Create instant forecasts with the new Forecast Sheet feature

The Knowledge Base
Share this content

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.


Please Login or Register to read the full article

The full article is available to registered AccountingWEB.co.uk members only. To read the rest of this article you’ll need to login or register. Registration is FREE and allows you to view all content, ask questions, comment and much more.


Please login or register to join the discussion.

By redboam
14th Feb 2016 04:03

Figurewizard Forecasting

If you use Figurewizard, simply entering headline figures for sales margins, overheads and investments will produce just about every forecast anyone could possibly need including profits, balance sheets, cash flows taxes and analysis, all of which can be instantly amended with a single click, then exported to excel if need be. We find it an excellent (and profitable) advisory tool and are surprised that it rarely gets a mention here.

Thanks (1)
03rd Jun 2016 13:34

can you include various lines of sales items with mixes of VAT [ ie consulting uk 20% vat consulting int 0%] or use a GP % on a sales line #1 #2 etc to calculate GP [ with stock being adjusted]
-- or is all this an excel 'summary' with linked or input values only ?

will sign up and have a play - but interested as you seem to be a power user required for advisory fee work

Thanks (0)
By shurst
06th Jun 2016 08:31

The Forecast Sheet feature just requires a list of dates and corresponding values. So you could use whatever calculations and functions you wanted to calculate the values and then use Forecast Sheet as detailed in the article.

Thanks (0)
By shurst
15th Feb 2016 10:47

Which algorithm does Figurewizard use

Hi, thanks once again for your contribution. In terms of forecasting based on an existing set of values, how much flexibility does Figurewizard give you in terms of the type of algorithm used?

Thanks (0)
09th Mar 2016 17:22

Excel for Forecasting - Pros and Cons

As Simon has noted, Excel 2016 introduces some very useful features for forecasting and reporting.  But there are some important, inherent limitations.

Here's a summary of the pros and cons, with a generic comparison to specialist forecasting systems: http://bit.ly/excel_pros_cons


Thanks (0)