Automation Streamlines Bank Statement Management

Congratulations to Microsoft, winner of the 2021 Silver Alexander Hamilton Award in Technology Excellence!

Microsoft’s bank account structure reflects the company’s size and global market position. Every day, Microsoft receives more than 1,600 bank statements from more than 80 banking partners. Managing these data flows and pulling transaction information into the corporate enterprise resource planning (ERP) system used to be a significant pain point for the cash operations team.

“The process for importing bank statements was very cumbersome,” says Sabin Jafri, senior treasury manager in the cash operations group. “We leveraged predefined ERP reporting to extract data from the statements. But our ERP system and bank account database did not talk to each other, so the ERP didn’t always know when a new account was added or an old account was deleted. Someone had to review line items on a daily basis to ensure the account structure was up to date and all our bank statements were coming in on time via SWIFT.”

The cash operations group used Excel to track receipt of electronic bank statements. They extracted data using macros and VLOOKUP formulas. “We were trying to be more efficient by creating macros to run behind the scenes, but there was the potential for errors if data came through in a different format,” Jafri says. “Invalid formats cause issues with VLOOKUP and require additional manual corrections.”

Moreover, the process entailed quite a bit of manual effort. If a statement failed to arrive, users would have to research the cause, then record those reasons for future reference. Once a month, they would produce a report that captured information about the problems they’d experienced with receiving bank statements.

All this manual monitoring and follow-up consumed so much staff time that it reduced the efficiency gains of Microsoft’s automated, SWIFT-based receipt of electronic bank statements. “Microsoft leadership asked what we would like to improve,” Jafri says. “I said I wanted a dashboard for monitoring electronic bank statements. I thought that by introducing automation to this process, we could improve efficiency for the cash operations staff, while also gaining faster access to data and ensuring the data’s accuracy.”

When the project got the green light from management, Jafri teamed up with Mark Kola, senior software engineer. “We worked together to identify the pain points, as well as the system requirements and user interface needs,” Jafri says. They designed a tool that would streamline management of the electronic bank statements.

“We started with the goals of making sure that all bank statements were available by 7:30 a.m. and building resiliency in the system to make sure that happens,” Kola explains. “The challenge was that some of our daily bank statements are quite large—on the scale of hundreds of thousands of line items. And in some cases, the data needs some formatting changes in order to fit properly into the ERP system.”

Together, engineering and treasury developed a tool that pulls bank statements arriving via SWIFT into a purpose-built database in the Microsoft Azure cloud. Within that database, transaction data is processed as needed for automated ingestion into the company’s ERP system. Then, it is transmitted to the ERP system via internal application programming interface (API). The database also serves as a clearinghouse for information on the status of the accounts themselves.

“Previously, a bank account might show up in one of our systems as being closed, while another internal system would indicate it was open,” Kola says. “Our electronic bank account management system is always up-to-date, providing a single version of the truth about our global account structure.”

See also:

And join Treasury & Risk for a webcast celebrating the winners of our 2021 Alexander Hamilton Awards in Treasury Transformation! The live virtual event will take place on April 7 at 2pmET. Register today.

The cross-functional team also built a front-end dashboard for viewing information about incoming statements. “This centralized interface enables us to view electronic bank statement data in one place, rather than chasing the data across multiple different applications,” Jafri says. “The dashboard is refreshed every five minutes, so we know the data is current. This enables the cash operations group to focus on the important issues that require our attention, rather than on finding updated and accurate information.”

Jafri and her team now have immediate visibility into when each bank statement arrives and which statements are missing for a given day. The tool’s built-in business logic attempts to automatically identify possible reasons for failures, such as a local bank holiday or a non-reporting account. When a missing statement requires manual investigation, the user who determines the cause can select descriptors through a drop-down menu, which keeps comments consistent. The dashboard also includes graphs that help the team visualize historical trends, and it enables users to move between high-level views of electronic bank statement health into detailed information about a specific account.

“The engineering group worked very closely with the business stakeholders, using an Agile methodology to develop additional features as they were needed,” Kola says. “This dashboard doesn’t provide transaction details because that information is housed in the ERP system. Instead, our dashboard shows any failures to receive statements and provides the information the cash operations group needs to make sure there’s nothing happening on our side that is prohibiting us from receiving the statements.”

Overall, the tool’s automation and ease of use have reduced the amount of time the cash operations team spends on managing and monitoring electronic bank statements by about 95 percent, Jafri says. “This tool has enabled us to use our time more productively,” she adds. “We’re spending a lot less time bringing the data in and a lot more time analyzing it.”

Recommended Stories