Excel is Excel, right? Vlookups and Pivot Charts provide everything I need, so why would I upgrade to Excel 2016? If you are asking yourself this then boy, you are in for a treat! Your friendly neighborhood forecaster is going to share something very cool about Microsoft Excel forecasting.

Valuable Tools To Forecast In Excel

Like many of us (and I’ve seen the surveys), there’s a good chance you do your forecasting in Excel. If you’re one of these people, or you have a forecasting tool but also want to do your own supplemental modelling once in a while, then get ready for a shocker! There are new, surprisingly unknown forecasting features that were launched with Excel 2016, and they were included for free!

 

Now before I jump into the cool new stuff, let’s establish some baselines. If you’ve never built a forecast in Excel before, consider yourself blessed and thank your employer for wisely purchasing demand planning software with a stat. forecast engine. Then take some time to look around and acquaint yourself with the basics of Excel.

Forecast Excel

Forecasting in Excel is a great place to start if you know all the tools it offers

Recommended Books for Microsoft Excel Forecasting

The best resource is the IBF’s primary source literature, Fundamentals of Demand Planning & Forecasting, by Dr. Chaman L Jain. It is mandatory reading if in you’re in this profession, in my opinion. I’m just a blogger, but you came to read me, so why don’t you just trust me and buy it! Also useful (and well-worn on my bookshelf) are Forecasting Methods and Applications by Spyros G. Makridakis,‎ Steven C. Wheelwright and Rob J Hyndman, and Excel Sales Forecasting for Dummies, by Conrad Carlberg. The latter also has a useful cheat sheet available online.

Buy the books, read, go play with Excel, then be brave and make a few forecasts and see how they did. Make sure to use holdout periods.

The Little-Known Excel Forecasting Tools

If after experimenting with Excel’s Data Analysis add-in and the basic Forecast, Linest, Correl, and Trend functions, you find yourself feeling confident but still yearning for the fresh-stuff teased by this blogger, then come try your hand at this:

Present in the Ribbon in Excel 2016 under the Data tab, is a new section literally called “Forecast”. The Forecast Sheet is a dead-simple option that allows users to highlight a data set with two series – time and values – and generate both a forecast (you enter the desired end date for how far out to extrapolate) and a linear chart.  So simple your boss could do it! There is also an Options section where you can customize things like where in your time series data to start building the forecast from, the confidence interval, seasonality intensity, how to handle missing data points, generating forecast performance statistics, and other tweaks.

Image of Excel forecasting otpions

Click the Data tab then select Forecast Sheet to set Confidence Intervals and Timeline ranges

The new, behind-the-scenes individual functions of this beautifully simple feature include:

  • FORECAST.ETS,
  • FORECAST.ETS.SEASONALITY,
  • FORECAST.LINEAR,
  • FORECAST.ETS.CONFINT,
  • FORECAST.ETS.STAT.

Microsoft has a wonderful write-up on the new Forecast Ribbon features and these associated functions, as well as a sample workbook with which to try them out.

Well, that’s it. Not a long article this time, but I bet you that you’ll be thanking me once you try it. I can’t take the credit (thank you, Microsoft!), other than for passing this along. I’ll end with a line from my children’s favorite new Disney movie, Moana: “You’re welcome.” Happy 2018 from your friendly neighborhood forecaster!

Any questions about Excel forecasting, post in the comments section below and I’ll do my best to answer them.