This is a multipart series:
The success of Microsoft finance’s first machine learning (ML) forecast spread like wildfire throughout finance. The ML forecast was shared with all finance leaders. So naturally knowledge of ML’s potential trickled down to more people across the organization. Eventually the news came to a team in central finance (CFT). Think of this team as Microsoft’s core FP&A team across the entire company. After seeing the accuracy at a worldwide level, this team knew it could help in the biggest forecast process at Microsoft. Something called the commercial field forecast. This forecast is created by finance members who sit in the “field”. The field is just a cool way to say regional offices all around the world. These field finance teams support the sales teams who also sit in the field. How could they take a worldwide forecast by product and break it down into specific countries all across the world? Well buckle up gang, it’s time to find out! This is how a tool called “Commercial Predict” was born in 2017.
How Things Used to Work
Before we dive into all the ML goodness, we have to understand how the old way used to work. I know, it’s kind of like eating your vegetables. But we just have to do it real fast then we can get to the fun parts.
In the past, each finance team in the field was responsible for their own forecast each quarter. These forecasts would happen in “CFO forecast cycles”. With cycles happening in October, January, and April. Microsoft’s fiscal year runs from July - June so these forecast cycles happen at the start of Q2, Q3, and Q4. The forecast at the start of Q1 is budget (that’s a story for a different day). Each cycle, a forecast would be created for the remainder of the fiscal year.
Microsoft sells products in over 100 countries. Most of those countries have a sales team that tries to sell products to companies in that geographical region. If there’s a sales team, then there is a finance team who supports them. This means there are dozens of sales finance teams creating quarterly forecasts for the rest of the fiscal year each CFO forecast cycle. Each team had their own secret recipe of how the forecasting was done. Often a custom excel model that would create the forecast. This model needed to be handled with care. Since each quarter it would have to be rolled over and prepared for the next forecast cycle. Anyone who has ever created and owned a financial model in excel knows the anxiety faced with trying to build and maintain one. These models were complex, and you said a little prayer every time you opened the file. Hoping it wouldn’t crash your machine because it was so large.
Once each team in the field had their forecast for their geography, it would get sent up the food chain. Forecasts from each country would be combined to form higher level aggregations in Microsoft’s sales territories. Each aggregation added more countries and continents together. This continued until you got the total worldwide number for the entire commercial business. Each time the forecasts got combined together at a higher level, senior finance leaders had the opportunity to make adjustments to that forecast. Based on their domain knowledge of the business. Eventually the final forecast the CFO, Amy Hood, saw was something completely different than what was initially created by each sales finance team for their specific geography.
Layers upon layers of bias were added to the forecast. Some was good bias that could improve forecast accuracy, but often it was too many cooks in the forecast kitchen. Too many people touching a forecast that didn’t need to be touched. Resulting in worse accuracy and more confusion once the books were closed at quarter end. This process would take upwards of a month every quarter. From the initial forecast created by field team all the way up the food chain to the CFO. In the spirit of every good infomercial, “there just has to be a better way!”.
Now you know why finance had to do something different. Drastically different.
Excel Prototype Built in a Redmond Garage
All good things start from humble beginnings. The team in CFT wanted to centralize the field forecast process for the commercial business. Create a single way that everyone in the field would follow to create a forecast. To make this a reality, they started with the swiss army knife of every finance professional. That’s right. You guessed it. They started with excel. Like any innovative project, it quickly became their baby. And all babies need a name. The named it Commercial Predict.
The team created an excel prototype of a single model that every field finance team could use. It was a combination of the old and the new. First was old but reliable PxQ forecasts. Where you take what’s in the sales pipeline for a quarter and multiply it by how many deals on average have closed in similar historical quarters. Second was classic CAGR and year over year percentage growths, which actually still work quite well. These traditional methods were combined with more statistical rigor. Something more along the lines of machine learning. They built by hand, formula by formula, exponential smoothing statistical models. Which is a common model in time series forecasting. It’s more stats than machine learning, but still performed really well. Today exponential smoothing is a simple function call in excel, but this team built it from scratch. I tip my cap to them, because that was hard to do.
Now there were multiple forecasting methods in this mega excel model. The beauty of the idea is that someone could come into the model and choose what methods they wanted to use to forecast a specific geography, customer segments, and products. Users could even combine multiple methods together to get a more accurate forecast. This was powerful because someone could use the PxQ sales pipeline method for products that depended on big customer deals landing, and use the other methods for things that had more stable trends and seasonality.
It was genius prototype. The team was able to take this to their leadership team and show how one single approach to do forecasting in the field could save thousands of days of combined human effort across the field every year. One mega model to rule them all. It had the promise to cut forecasting down by 50%. Would it work though? To test it out, the team ran this excel model alongside the traditional bottoms up forecast process from each field team. They could then compare the results and even track accuracy across the old and new ways. The results were good. The new prototype was the same or even better than the existing process, but was 50% faster.
The new approach was fast, and it was accurate. The final roadblock before adopting the new approach was to get everyone in the field to agree on what level they should forecast at. This historically was a difficult subject to discuss, with everyone having differing opinions. Thankfully this was solved by getting the buy in from the top senior finance leaders in central finance and the field. Once that happened everyone was able to get on board.
Ok, so the team had a cool prototype that knew worked well. But if they just used that excel model, then they are still maintaining a model that is messy and requires constant upkeep. It would be hard to scale. They needed something more robust. A real tool that was built by engineers. Thankfully there was a team who could do just that.
Building the Tool
The vendor team who was created to take over the initial ML forecasts was the up to the task. They had the data science knowledge, but the data engineering and software engineering needed to build a software tool to scale out the excel prototype was missing. So the team got other vendors to fill in those gaps. Now there was a team of engineers all capable of making the tool a reality.
The first version of the production level Commercial Predict tool had to be built fast, before an upcoming CFO forecast cycle. V1 was built into excel within six months as an add-in field users could download and connect to. It needed to combine new machine learning methods with traditional PxQ and CAGR/YoY run rate methods.
Here’s how it worked.
- Data engineers would pull historical revenue and sales pipeline data. All the forecasts methods were precomputed and saved in a database that was turned into a cube. This would be the starting point for all field members. Instead of calculating these forecasts by hand in their old excel models, it would be precomputed for them. At scale.
- The forecasts in the cube were then served to users in a custom excel file. Each field team could come into the tool and select the geography, products, and segments they were responsible for forecasting. After making these selections, all the forecast methods would populate in the excel.
- Users could then see each forecast method and see which ones pass their smell test of what they expect to happen in the business based on their domain knowledge. They could choose a specific forecast method to use, or combine multiple methods together to get a more robust forecast. Finally there might be things these forecast methods don’t know about. Like upcoming tax changes or product strategy changes. Field users could ultimately make manually adjustments to get a final forecast.
- Once the final forecast was created, they could save it back to the cube. This allowed finance leaders to see the forecast creation in real time. Also it would prevent the classic “excel crash without saving” headache we’ve all been through in the past.
- Once the forecast was complete for each field team, a static output file was created at the touch of the button. Teams could take this output and load it into the final planning system.
Before official launch, training sessions were held to make sure everyone knew how to use it. It was also a good opportunity to fix any bugs in the tool. This resulted in some late nights and even weekend shifts, but the job got done. The tool was launched on time and the rest is history.
We were able to go from a forecast process of 21 business days each quarter, down to just 10. It was a revolution. This saved Microsoft millions of dollars each year of human capital. Finance teams in the field could now forecast faster, with less headaches, and prevent the layering of bias that was a staple of the previous way.
Evolutions
After this officially launched in 2018, the Commercial Predict tool has gone through a lot of iterations. What started in excel then moved into a web based tool. Then back to excel. With each iteration, we got better at the machine learning methods. Better at adding more features to give users more control over the final forecast.
Eventually Commercial Predict evolved into a much broader solution called “Fusion” in 2022. Think of it as a tool that could still do the commercial field forecast process but now also take on other forecasts within Microsoft finance. A true one stop shop for all things planning. Fusion is an excel add-in with a built in UI on the side of excel. Kind of like how excel copilot opens on the side of your excel tab, Fusion does the same. A user could select what forecast they want to do, select the parts they’re responsible for forecasting, and Fusion would populate the blank excel file with all the information they need to finalize their forecast. Methods like PxQ and machine learning are still ran ahead of time. The UI was truly dynamic. You could take any excel file and open the Fusion app inside to get going on the forecast. Fusion allowed finance to scale the learnings of Commercial Predict to so many other forecast processes. Improving the impact ML and centralization can have on forecasting.
Planning tools like Fusion will most definitely change in the future. As the business evolves, so should our way of forecasting it. What doesn’t change is how ML has become a central part of the forecast process.
Lessons Learned
Iterate Iterate Iterate
Rome wasn’t built in a day. Instead of building this complex centralized forecasting tool from the start, we started small. Built a prototype. Got senior leadership buy in. And continued to make it better every 6-12 months. Even as I write this we are in the process of improving the ML accuracy of the commercial field forecast. If you’re coasting, you’re going downhill. You need to continue to iterate.
Combine the Old with the New
ML didn’t outright replace every part of the commercial field forecast. Instead we combined ML techniques with older methods like PxQ sales pipeline methods. This allowed us to use the strengths of each approach based on what product was being forecasted. Some products are sensitive to large customer deals closing, so PxQ works best. Others have stable trends and seasonality, that’s where ML shines. Using both gives us the best of both worlds.
Senior Leadership Buy In
A forecast process is like a ship. The bigger the ship, the harder it is to change course. So the bigger the forecast process, the higher the buy in needed from a senior leader. Getting a GM or CVP level support allowed us to supercharge the change management. It’s easy to get bogged down in arguing with senior finance managers about how a forecast process should be done. Once a CVP (someone who reports to the CFO) comes in and says this is how we’re going to do it. Then everyone gets on board and starts turning the wheel of the ship together to change direction. The commercial field forecast had to get support from GM and CVP level leaders or else it would have taken years to change it instead of months.
Final Thoughts
The hardest part of any ML project comes down to people. Training models is easy, convincing people to use them is hard. It takes time. It takes senior leader buy in. It takes an open mind to rethink how your job can be done. It might be hard, but in the end it’s worth it.
Often we get asked by finance teams outside the company if they can take our “Commercial Predict” or “Fusion” tool off the shelf and start using it at their own company for forecasting. Sadly you cannot. We build a lot of these custom tools because we don’t have a choice. Microsoft’s business is complex. Often we need custom solutions that are hard to standardize in external products. Thankfully the machine learning methods we use are available for free as an open-source R package. Check it out if you’d like to learn more.