Automation Streamlines Bank Statement Management


Microsoft’s bank account structure reflects the size of the company and its position in the global market. Every day, Microsoft receives more than 1,600 bank statements from more than 80 banking partners. Managing this data flow and pulling transaction information into the company’s enterprise resource planning (ERP) system was once a pain point for the treasury operations team.

“The process of importing bank statements was very cumbersome,” explains Sabin Jafri, senior treasury director in the treasury operations group. “We used predefined ERP reports to pull data from the statements. But our ERP system and bank account database didn’t talk to each other, so the ERP didn’t always know when a new account was added or an old account deleted. Someone had to review the line items daily to make sure the account structure was up to date and all of our bank statements were arriving on time via SWIFT.

The treasury operations group used Excel to track receipt of electronic bank statements. They extracted the data using macros and VLOOKUP formulas. “We were trying to be more efficient by creating macros to run in the background, but there was a risk of error if the data arrived in a different format,” says Jafri. “Invalid formats cause problems with VLOOKUP and require additional manual fixes.”

In addition, the process required quite a bit of manual effort. If a statement did not arrive, users should research the cause and then save those reasons for future reference. Once a month, they produced a report containing information on the problems they had encountered while receiving bank statements.

All of these manual checks and follow-ups have taken so long for staff that they have reduced the efficiency gains of Microsoft’s automated receipt of SWIFT-based electronic bank statements. “Microsoft management asked what we would like to improve,” says Jafri. “I said I wanted a dashboard for tracking electronic bank statements. I thought that by bringing automation into this process, we could improve the efficiency of treasury operations staff, while accessing data faster and ensuring data accuracy.

Quote: "This tool has allowed us to use our time more productively.  We spend a lot less time entering data and a lot more time analyzing it."

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

“We started with the goal of making sure all bank statements were available by 7:30 am and building the resilience of the system to ensure that happens,” says Kola. “The challenge was that some of our daily bank statements are quite large, scaling hundreds of thousands of items. And in some cases, the data requires formatting changes in order to fit properly into the ERP system.

Together, engineering and treasury have developed a tool that extracts bank statements arriving via SWIFT into a specially designed database in the Microsoft Azure cloud. In this 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 the 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 could show up in one of our systems as closed, while another internal system would show it was open,” says Kola. “Our electronic bank account management system is always up to date, providing a unique version of the truth about our global account structure. “

See also:

And join Cash flow and risk for a webcast celebrating the winners of our 2021 Alexander Hamilton Prize in Treasury Transformation! The virtual live event will take place on April 7 at 2 p.m. ET. Register today.

The cross-functional team also created a front-end dashboard to display information about incoming statements. “This centralized interface allows us to view electronic bank statement data in one place, rather than searching for the data in several different applications,” explains Jafri. “The dashboard is refreshed every five minutes, so we know the data is up to date. This allows the treasury operations group to focus on the important issues that require our attention, rather than finding up-to-date and accurate information.

Jafri and his team now have immediate visibility on the arrival date of each bank statement and the missing statements for a given day. The business logic built into the tool attempts to automatically identify possible reasons for failures, such as a local holiday or a non-reporting account. When a missing report requires manual investigation, the user who determines the cause can select descriptors via a drop-down menu, which maintains the consistency of comments. The dashboard also includes charts that help the team visualize historical trends, and it allows users to switch from a high-level view of the health of electronic bank statements to detailed information about a specific account.

“The engineering group worked closely with stakeholders across the business, using an Agile methodology to develop additional functionality as needed,” says Kola. “This dashboard does not provide the details of the transaction because this information is hosted in the ERP system. Instead, our dashboard shows any failure to receive statements and provides the information the treasury operations group needs to ensure that nothing is happening on our end that is preventing us from receiving statements. .

Overall, the automation and ease of use of the tool reduced the time the treasury operations team spends managing and monitoring electronic bank statements by approximately 95%, says Jafri. . “This tool has allowed us to use our time more productively,” she adds. “We spend a lot less time entering data and a lot more time analyzing it. “

Thank you to our judges and the 2021 Alexander Hamilton Award sponsors Kyriba, ICD, C2FO and GTreasury.

Comments are closed.