Create Forecasting Models using Excel and Machine Learning.

Microsoft

Jul 31, 2017

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:

Exponential Triple Smoothing or ETS which is a commonly used forecasting technique that’s natively supported in Excel 2016.
Azure Machine Learning (Azure ML), R, and as an example a popular technique called Auto-Arima. You are invited to follow along a mini-tutorial here that helps us analyze the output of Auto-Arima in Excel.

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 Smoothing (ETS)

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.

In Excel 2016, we introduced native ETS functionality. This includes both a set of new functions such as FORECAST.ETS and other supporting functions for additional statistics. Your dataset does not need to be perfect, as the functions will accommodate up to 30% missing data.
Another way of using these capabilities in Excel is via the one click forecasting button in the Data tab, which gives us a forecasting chart at a button press. The visual below shows us the forecast (in orange) vs actual data (in blue) as well as the confidence intervals of the forecast.

Here is a demo on how a Bikes Accessories Analyst uses Excel ETS Forecasting.

Azure Machine Learning + R + Arima

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:

Start at https://studio.azureml.net/ (Azure ML Studio) and create your own subscription & workspace.
Open the sample Experiment here, and click the “Open in Studio” green button.

In the next dialog box, pick your Region and Workspace to successfully copy the Experiment into your account.
You should now see the different modules in the sample experiment (it should look like the image below).

When you click on the “Execute R Script” module, you’ll see a side pane containing the sample R Script. In the screenshot below, the box on the right shows the place where the Auto Arima forecast functions is invoked.
This experiment has already been set up to provide the output via a web service that can be integrated into Excel using the earlier-mentioned add-in.
Now click “Run”, then “Deploy Webservice” the taskbar below (see box at the bottom). Thus we create a webservice API that apps like Excel can now use to call into the experiment.

Now you’ll see the newly created webservice API details. Click on one of the links under the “Request/Response” and “Apps” area (or right click and select Save target as…). See blue box in the screenshot below.

This will download an Excel file already set up to consume the webservice.
Open the file, and click on “Enable Editing” in the Protected View yellow bar on top.
This will automatically load and open the Azure Machine Learning add-in. Save this file to your local share.
To work on some sample data open this file and copy over the data from A1:B74 to Sheet1 of your file. Adjust the column widths until you see all the data.
On C1 write Forecast. Use Format Painter to copy the format from B1 to C1. Select col C. On the Home tab of Excel, update the number format to Currency.
On the Azure Machine Learning Add-In, select your input range e.g. Sheet1!A2:B50. Uncheck my data has headers.
Under output select the 1^st cell of the range where we want output, e.g. Sheet1!C51. Uncheck Include Headers. The sample data now looks like this –

Hit the “predict” button to get forecast output into the table.

After selecting the data in all 3 columns (Month, Revenue, ForecasT), you can plot a chart of your choice, for e.g. Insert Chart -> Line Chart. The chart shows both blue (actual) and orange (forecast) trends.

Going back to ML Studio, I can share my experiment with others via the Publish to Gallery button which provides access to others via the Cortana Intelligence Gallery.

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.

In Conclusion…

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.

Updated Jul 31, 2017

Version 2.0