When a previously reliable financial forecasting model failed to make an accurate prediction in a rapidly evolving market, finance executives at Microsoft called for change. The executives wanted changes to the forecasting model and to how forecasts are made. The finance team contacted Microsoft IT to help them use the advanced capabilities of the Azure cloud computing platform and the Azure AIThree teams worked on different aspects of this project:
- The financial analysts wanted to learn how machine learning and predictive analytics could improve their forecasts.
- The IT team had expertise in automation and advanced analytical tools.
- The Azure cloud computing platform team was interested in developing solutions that used the capabilities of the Azure AI, and in helping business groups at Microsoft take advantage of the increasing power of cloud computing.
For a year, the teams collaborated to build an automated predictive model that incorporated their financial acumen, current and historical sales data, and the latest technology to make better financial forecasts, faster.
Shortcomings of spreadsheet forecasting
Financial forecasting has always been demanding. In the 1980s, the practice was revolutionized by the computerized spreadsheet—the first “killer app” of the microcomputer revolution. Decades later, many enterprises still prepare financial forecasts using tools that haven’t changed much since those first computerized spreadsheets. Preparing forecasts using old-fashioned, manual methods can be:
- Expensive. Many hours are required to prepare a sophisticated forecast from a large dataset, and the time of skilled financial analysts is expensive.
- Error-prone. Even the best people make mistakes. Plus, people are not always objective about the data they are working with.
- Tedious and time-consuming. Updating prognostic algorithms—or even updating simple calculations buried in the cells of spreadsheets—can be a time-consuming process.
- Drab. Graphs and charts generated by spreadsheet programs aren’t as dynamic as those produced by more advanced reporting solutions.
- Inconsistent. Without centralized analysis and information storage, business groups produce reports independently using different data, making different assumptions, and sometimes ending up with dramatically different results.
- Old-fashioned. Traditional methods do not take advantage of powerful new capabilities such as machine learning and cannot generate predictions on-demand for different users.
A predictive analytics solution
As a small-scale, preliminary test, we decided to use predictive analytics to forecast the final sales total for North America in the current quarter, a prediction that is calculated every week and then published in a financial report that is emailed to sales managers and executives in the North America region.
We did not envision our predictive analytics solution as a replacement for existing methods; instead, we envisioned it as another method for predicting the future. The results from the new solution would be compared with the results from traditional methods to validate the new solution. This approach was ambitious enough to test the concept without overwhelming the capabilities of our small development team.
The goals for the solution were to:
- Use the new and increasing capabilities of machine learning and other capabilities in the Azure AI.
- Comply with the requirements for high business impact solutions at Microsoft, so we could scale up the solution for global use.
- Improve the methods for presenting predictions to users—specifically, make predictions available on demand and use the power of Microsoft Power BI to deliver better graphical views of our predictions.
- Improve forecasting of sales opportunities. (For more information about the team’s sales opportunity forecasting efforts, see Predictive analytics improves the accuracy of forecasted sales revenue.)
- Validate our solution by comparing results with traditionally prepared predictions.
Building an analytical engine
We started building the solution using a somewhat unconventional process. We didn’t have a formal software development cycle. Instead, we quickly assembled a small, agile team. The project was planned to last between three and four months, and it started with a narrow scope and few specific requirements. There were weekly reviews, and some useful capabilities started to become available within only a few weeks of starting the project.
Our predictive analytics solution starts with information about sales of Microsoft products in North America. This information was gathered from a variety of sources, and it included both historical sales data and information about deals in progress that were expected to close in the near future.
That sales data is processed using elements of the Azure AI, which is an integrated set of tools for managing and analyzing big data and then effectively presenting the results to users.
The elements of the Azure AI that we used include:
- Azure Data Factory provides data management and integration services for data-based solutions.
- Azure SQL Database provides relational data storage on the Azure cloud platform.
- Azure Machine Learning gives developers tools to mine and explore big data on Azure.
- Microsoft Power BI transforms results into dashboards and visualizations for users.
- Windows Apportal provides the navigational experience for the users of our solution. It is an app that can integrate an entire Line of Business stack into a single, touch-based experience, with the same nested folder structure used in a desktop operating systems.
Also used in the predictive analytics solution were:
- R packages, which are software packages that extend the capabilities of the R statistical computing programming language.
- Microsoft Data Management Gateway, which connects on-premises data sources to cloud services for consumption in a secure and managed way so you can use Microsoft cloud services with the on-premises data.
We started with analytical models that are available as part of Azure Machine Learning. The models do not require programming. Only basic knowledge of various standard machine learning models and databases is needed, so creating, testing, and training a model can be performed by anyone with data science knowledge.
Training the model
A machine learning solution must be trained by exposing it to the kind of data that it is expected to analyze. We trained our model every week using all of the current and historical sales data that we had. Then the sales performance data from the most recently completed week was added to the entire set, and the model was retrained using that data set.
We test the accuracy of the prediction produced by our predictive analytics solution by:
- Comparing the results that we get using current sales data with forecasts obtained by conventional methods.
- Comparing the results that we get using historical inputs with previous financial results.
The results that we get using both testing methods has improved steadily.
One of our highest priorities for this predictive analytics model is to improve its accuracy, with a goal of 99 percent accuracy or better.
When we started development, the predictions from the model were only 60 to 70 percent accurate. After months of refining the models and weekly training, accuracy rose to close to 95 percent.
Our goal for this project was to create a small-scale, automated revenue forecast for inclusion in a weekly email sent to finance managers and executives. Although the solution is still in development and has not been completely approved by the leadership team, it has emerged from testing and is in regular use.
By using our predictive analytics solution, we can now:
- Deliver instant insights about actual revenue realized and sell-through variance-to-budget across market segments.
- Perform in-depth analysis of budget variance, and side-by-side comparisons for month to date, quarter to date, and year to date.
- Enable users to examine the details of predictions, and to get a broader view by slicing across market segments, sub-segments, pricing levels, and strategic products.
- Predict budget attainment.
- Analyze billed-revenue by segment.
- Present predictions on demand using customized data dashboards and visualizations.
When using our solution, users can get results instantly and on demand. Microsoft Power BI enables them to create custom views of the data that they are interested in, and they can view the data in a variety of ways. They can also get predictions by typing natural language queries for the Cortana virtual assistant to answer.
The solution delivers results in two forms: analysis and predictions.
Three types of analysis are performed: sell-through performance, variance analysis, and summary.
Sell-through performance includes:
- Quarter-to-date variance-to-budget in dollars, by segment, and for strategic products, which reveals how salespeople are performing and which products are selling best.
- Information about which products are meeting budget attainment every quarter.
- Information about which pricing strategies are producing the most revenue.
Variance analysis includes:
- Quarter-to-date variance-to-budget in dollars and percent, by segment, and by strategic products.
- Percent variance to budget for the top 12 price levels.
Summary analysis includes:
- Snapshot views of actual revenue.
- Variance-to-budget in dollars and percent.
- Year-over-year percentage revenue.
- Trend analysis for the quarter.
- Detailed views of areas of interest.
Our predictive analytics solution provides two types of prediction: a quarterly outlook and quarterly close performance.
Quarterly outlook includes:
- Likely outlook for the current quarter based on historical results and what is in the sales pipeline.
- Four outlook charts (sometime called “banana charts”).
- Quarterly close performance includes:
- The actual final sales figure for the quarter compared with the machine learning prediction.
- Comparison of the machine learning model versus the historical prediction.
It’s still early days for automated data analytics in general; particularly, for this solution. Our solution still hasn’t been fully implemented across the whole company and the scope is still limited, but we are already seeing real benefits. So far, our solution:
- Saves work and increases productivity. Automated data analytics platforms are a “develop once, use many” solution. You create the algorithms and then, with machine learning capabilities, the solution learns and improves. After development work is completed, much less labor is required to produce financial forecasts, freeing people in finance to do more interesting and profitable work. Additionally, any tool or process that we develop can be used elsewhere in the system or can be scaled up or improved upon.
- Is more powerful. An automated system is essentially unlimited in the amount of data that it can use as a source and the amount of reporting that it can produce. Accuracy is increasing rapidly to rival traditional methods.
- Is more objective and consistent. A predictive analytics model is dispassionate, so it sidesteps some of the subjective factors of manual forecasting. Working from a centralized pool of data using agreed-upon analytical methods reduces disagreement.
- Uses machine learning. Modern predictive analytics solutions can learn and evolve.
- Delivers results better. Users can get customized predictions whenever they need them, and predictions are easier to understand using advanced reporting capabilities of tools such as Microsoft Power BI.
- Is more flexible. Algorithms and data sources can be managed from a central location.
Using the tools for forecasting
For now, we are using the framework that we have developed as a supplement to the forecasts derived by conventional means for Microsoft operations in North America. Our goal is to augment traditional forecasts, not replace them.
To improve their forecasts, the finance team is currently using both predictive models based on Azure Machine Learning and existing forecasting tools. Each method gives us a different way of looking at sales data and what the future may hold.
The first model uses Azure Machine Learning, which is a powerful predictive tool, and we are continuing to develop it and expand its capabilities and usage.
The second model—a simpler, traditional model—predicts budget attainment using linear regression.
Along with these two models, we used budget attainment charts. These charts help us to know how much of our budgeted revenue we should have attained each week as a quarter progresses.
Although there is still a lot of work to do to train this solution and expand its capabilities, we think it’s clear that we have only scratched the surface of what predictive analytics can do in finance. We are excited about what we have developed so far and about what the future holds.
We plan to expand the range of source data that the solution draws from, the range of the predictions that it can make, the number of reports and visualizations it generates, and the range of users who have access to the system. We are joining with other finance teams and corporate controllers with the goal of expanding the scope of the model and enabling business groups worldwide to use it.
Our hope is that just as the pocket calculator and computer spreadsheet freed us from the computational drudgery of the past, predictive analytics will free financial analysts from the routine chores of making financial predictions so they can be more creative, productive, and successful.
For more information
© 2019 Microsoft Corporation. All rights reserved. Microsoft and Windows are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. The names of actual companies and products mentioned herein may be the trademarks of their respective owners. This document is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.