As Carlos Otero and I mentioned in our talk at MDIS (link), forecasting is an important area of focus for businesses in general across a range of functions: for instance, you can have finance teams forecasting costs, sales teams forecasting revenues, or engineering teams forecasting developer-hours and bug burn downs, etc. In addition, business data often flows through Excel – arguably, Excel is the most widely used tool for business analytics and forecasting. Finally, with the increased importance of Data Science and Machine Learning and the increasing complexity of business data, Business Analysts have taken to more sophisticated methods to do forecasting. Thus, the importance of exploring how to incorporate more sophisticated forecasting models within Excel workflows. The goal of this post is to share a few ideas and tips on how to super-charge your skillsets – in Excel and Machine Learning - to increase your forecasting efficiency.
In this post, we’ll cover:
Note that the sample techniques are commonly used by teams at our company, Microsoft. For purposes of this blog post, we have not focused on stochastic forecasting techniques such as Monte Carlo simulations, although it is possible to extend the work we’re doing here to implement the method in ML if needed and analyze in Excel via an add-in.
Exponential Triple Smoothing (ETS) uses the weighted mean of past values. One reason ETS is popular is that it adjusts for seasonal variation in data. Some trace the origins of exponential smoothing to Poisson, as an extension of a numerical analysis technique from the 17th century, and the technique was later adopted by the telco community in the twentieth century. In Excel, we use a variation of the Holt Winters ETS algorithm.
Here is a demo on how a Bikes Accessories Analyst uses Excel ETS Forecasting.
Azure Machine Learning (or Azure ML) is a cloud predictive analytics service that makes it possible to quickly create and deploy predictive models as analytics solutions.
According to a recent survey by KD Nuggets, R and Python feature prominently among tools used by Data Scientists, as does Excel. In addition, based on our research and conversations with Data Scientists and Analysts, there is a need for better integrating workflows between these tools and Excel. While there are several ways of integrating ML workflows into Excel including the work of our partners such Anaconda, XLWings, Pyxll we’ll focus on AzureML in this post.
Azure ML Studio is the tool we use to author machine learning experiments in Azure ML. Studio is a GUI-based cloud IDE for ML, offers one click deployment of Web Services, and supports advanced analytics via R, Python, and packaged modules.
One sample experiment we built for forecasting leverages the R forecast package and the Auto-Arima function – in ML Studio. This experiment is inspired by work done by Lucas A. Meyer of Microsoft. ARIMA stands for AutoRegressive Integrated Moving Averages, and is popularly used for time-based data series predictions.
Now let’s take a quick look at a simple Auto-Arima forecasting experiment. To follow along:
Besides the example described in this post, several other forecasting methods exist in the Cortana Intelligence Gallery (screenshot below). In addition, other powerful models and techniques for different domains are available if we search in the Gallery. The Azure ML – Excel integration helps bridge the gap between Data Scientists and Analysts. As we’ve seen you can build a model in Azure ML for your dataset, easily deploy it as a web service, and bring the data into Excel via the Excel Azure ML Add in. For more details, you can see the demo recording on AzureML.
The full talk at MDIS has more details including some tricks to compare the accuracy of different forecasting techniques (i.e. Mean Absolute Percent Error or MAPE) and how to aggregate multiple forecasts using Excel’s new Get and Transform capabilities. Watch the full talk here. We hope this inspires a few experiments.
Let us know what you think and if you have any questions. Also, given the feedback on the talk this year, we will likely prepare a presentation at other conferences such as next year’s MDIS and would love to start collecting ideas and customer scenarios. We’d love to learn how you use Excel for your forecasting workflows.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.