New in Excel 2016 is the Forecast Sheet capability which you will find on the Data tab in the Forecast group. This looks at past data and the trends within it, to allow you to forecast what is likely to happen in the future.
Open a workbook containing historic data. Here I am looking at International Visitors to London.
This is quarterly data starting in 2002 and finishing at Q3 2017 (the most up-to-date I could find on the Internet).
Click anywhere within the data set, then on the Data tab, in the Forecast group, click Forecast Sheet. A line chart will be displayed showing your past data and future forecast.
At the top right, you have the option to choose a line chart or a column chart. Towards the bottom, you can either accept their Forecast End date or select your own. Clicking Options brings up a greater list of options.
From here, you can accept their Forecast Start date or choose your own. You can also specify a different Confidence Interval if required. You can allow Excel to automatically detect the seasonality of your data or set it manually and you can also choose whether or not to Include forecast statistics. If necessary change the Timeline Range and the Values Range. In this forecast I am looking at predicted spend, but I could want to predict nights spent or number of visits.
When you are happy with your chosen options, click Create. As well as the chart, you will then have predicted figures.
Here using a 95% confidence interval, it shows the expected forecast spend, the minimum spend based on my data and the maximum spend based on my data.
Find out more about our Microsoft Excel training London, UK.
Find out more about our Microsoft Excel training Wiltshire, Hampshire, Dorset.