Forecasting tool in Excel

From Excel 2016, there is a convenient Forecasting tool in Excel which allows you to show Excel some historical data (say 24 months worth of sales) and Excel will, taking growth and seasonality into account, forecast for up to 50 months into the future.

As mentioned this is only for Excel 2016 (Office 365 is on Excel 2016 so it is the same thing) and up. So below some historical data and we have plotted it on a chart and used a Trendline to get a simple regression. Although the growth is shown, it is clear that there is some sort of seasonality in the numbers which the simple regression forecast will miss.

Forecasting Tool in Excel

In MS Excel 2016 and up, you can use the Forecast Sheet tool. As shown below, you would highlight the information (A9 to B45), and click on the Forecast Sheet button that appears in the Data ribbon. The Create Forecast Worksheet popup box will appear with its forecast. Note that it already looks like a more accurate forecast.

Forecasting Tool in Excel

There are a number of options available to you, but for now you can click the Create button. Excel will conveniently create a new sheet which will list:

  • The historical data you gave it (B1 to B37 below)
  • It’s forecast going forward for 50 months (column C)
  • Some confidence interval numbers
  • The chart already linked and differentiated between actuals and forecasts
Forecasting Tool in Excel

This may not be your final forecast for your budget, but it is a nice start and check to see that your staff are thinking through their budgets.

Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course  or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.

Online Budgeting and Forecasting course