Microsoft’s Treasury Mines Big Data and FP&A

Data is a means to an end rather than an end in itself. Delivering business intelligence (BI) to feed into the financial planning and analysis (FP&A) process, based upon the data available at Microsoft, is what elevates the treasury team at my corporation to a status of a trusted adviser and a strategic partner. As we look back, there were three key milestones in the Microsoft treasury team’s journey from merely collecting data to delivering business intelligence from it and aiding the business in its FP&A procedures and cash management. 

  1. Electronic Visibility to Global Cash: With just over 1,000 bank accounts, 100-plus bank partnerships and operations in more than 250 countries, it was critical for Microsoft’s treasury team to have electronic visibility to our cash balance and transactions. We were the first corporate to implement SWIFTNet connectivity in 2006 using our own SWIFT environment. It was a critical first step that allowed Microsoft to collect daily snapshots of our cash balance in various accounts across the globe. In this phase, we were still using a bank specific virtual private network (VPN) connection, effectively a data pipe, but the message format was SWIFT MT messaging. We leveraged Microsoft’s BizTalk Server as the middleware to get the bank statement loaded and translated from MT to the IDOC format and directly uploaded into our enterprise resource planning (ERP) system. This daily electronic visibility to nearly all of our bank balances allowed the treasury team to move from faxed monthly statements, enabling a paperless, more efficient environment. 
  2. Visibility to Mobility: With the credit crisis that started in 2007 and came to fruition in the Autumn of 2008, there was a commensurate increase in counterparty risk. It was critical for Microsoft to therefore not only have electronic visibility into our global cash position, but to also be able to move money between banks more easily. The ability to move cash became non-negotiable with continuing global issues such as the Icelandic bank freeze, the Arab Spring and uncertainty around the future of the euro. With this backdrop Microsoft treasury worked towards a new capability for initiating the movement of funds. Ultimately, Microsoft became the first corporation to implement the XML ISO 20022 v2 messaging standard for bank statement reporting using PAIN messages and using direct SWIFT connectivity to our banks. We no longer relied on individual bank specific VPNs. The second phase of this project allowed us to migrate payments to the XML ISO 20022 v3 format. The benefit was more structured and detailed financial messages, which allowed us to also carry out auto-recon and auto-posting for our accounting entries. 
  3. Big Data to Business Intelligence: Having prior-day and current-day statements for 1,024 accounts didn’t automatically translate to a consolidated view of the daily cash position at Microsoft. The constant volatility that is the ‘new normal’ post-crash requires agility to answer the question about ‘where and how much cash’ one has within minutes, rather than in days. George Zinn, Microsoft’s corporate vice president and treasurer, put it best when he said: “It has always been a challenge for treasurers to get a quick snapshot of all of their balances added up. This is a complex challenge that requires a consistent understanding of the assumptions embedded in the numbers – for instance, ledger balances or cash balances. So when the chief financial officer (CFO) asks the question, we wanted to be able to provide a timely response that does not lead to follow-up questions.” The answer needed to include data on cash in bank accounts, total cash in investment portfolio and overall balance sheet cash adjusted for accruals, let alone what was domestic versus permanently invested overseas. This started us along the next part of Microsoft’s journey. 

We quickly set up a cross-group team at Microsoft with representation from tax, accounting, legal, risk management, portfolio management, corporate finance, cash operations, cash planning and IT. The team, allied to the treasury, set off to work on the following key questions:  

  • First, how do we define cash? When a senior executive asks us how much cash we have, what are the components we include or how do we reconcile accounting, tax, cash operations and investment portfolio perspectives? 
  • Second, how do we improve response time and accuracy? All relevant data resides in disparate systems, and it takes time to reconcile these views. 

Figure 1: Microsoft’s Cash Management Overview.

Microsoft_AnitaPrasad Fig1

Source: Microsoft.

The schematic above in Figure 1 illustrates the systems challenges facing Microsoft and had us searching for a business intelligence solution that would allow us to take big datasets from different systems that could be consolidated to provide actionable BI and an aid to the FP&A process as well. There was no such off-the-shelf solution in existence for our treasury team, even though the number of vendors claiming to offer a solution keeps growing each year. We found our nirvana last year when we met with one of the in-house SQL developers at Microsoft and learned about Power View (PV). Treasury has been using this tool for more than nine months via SharePoint services and just last month we went live with a new custom application that leverages PV, SQL Server database, SQL Server Reporting Services (SSRS) and SharePoint services. The application is called ‘Cash Flow Analysis and Reporting (CFAR). It provides critical business intelligence functionality in two key areas:  

  • A snapshot of our global cash and investments position with drill-down capability.
  • The ability to work more efficiently with our global subsidiaries to improve cash-flow forecast so we can help provide timely funding to them. 

Treasury has adopted PV via SharePoint services in most aspects of its daily operations. All of our quarterly review meetings with our treasurer, George Zinn, now use PV so we can respond to his questions in the moment. We can slice the data with a simple mouse click in the meeting and enable him to make decisions rather than reverting back later with different static scenarios. 

Figure 2: Cash Balance by Bank, using Microsoft’s Power View (PV).

Microsoft_AnitaPrasad Fig2

Source: Microsoft.

“After we implemented SWIFT XML ISO 20022 messaging and got electronic visibility to where all of our cash was at any given moment, we knew we needed BI to be able to make sense of all that data,” said Microsoft’s Zinn. “Our team quickly built the BI tool using SQL Server and Power View so we can drive strategic decisions. They did a brilliant job to make sure we can view everything in a clear dashboard and drill down as far as we need.” 

This BI functionality, Power View, now comes included in Excel in the newly released Office 2013. Thus, the deployment and user experience is something all treasury teams are already very familiar with. 




Related reading