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.
It's frustrating when Excel acts as if the active area of a worksheet is significantly larger than the actual area where you have data. Suddenly your scroll bars move you into uncharted areas, such as column TX or row 5,000. In programming parlance, this is known as the 'used range' of a worksheet.
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. 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, 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 by Spyros G. Makridakis, Steven C. Wheelwright and Rob J Hyndman, and, by Conrad Carlberg. The latter also has a useful available online. Buy the books, read, go play with Excel, then be brave and make a few forecasts and see how they did.
![Sheet Sheet](/uploads/1/2/5/4/125452098/435778141.jpg)
Make sure to use 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. 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 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. Andrew is an award-winning Sales & Operations Planning (S&OP) and Supply Chain Management professional. He is currently employed with Medtronic, the world's largest medical device company. He is also on the Board of Advisors for the Institute of Business Forecasting & Planning (IBF), and a regular international speaker at industry events, including the IBF.
Andrew is a hands-on data scientist; but also an inspirational industry leader with a passion for innovating & advancing the fields of demand planning, S&OP/IBP, and enterprise risk management.