Cash & Working CapitalTechnology

Transforming Treasury: The Power of a Data Warehouse and BI Tool

By October 6, 2020No Comments

How one tech company is making use of better centralized data to improve forecasting.

Better data makes for better forecasts, reporting and data analysis. At a recent NeuGroup meeting, one member of a large technology company’s treasury team described the results of a two-year project to create a global treasury data warehouse to raise its data game.

  • The company’s vision: a centralized data repository with BI reporting that integrates and organizes bank statement, market, ERP and forecasting data.
  • The benefits of warehouses: data can be more readily viewed, analyzed with business intelligence (BI) tools and fed into dashboards for on-demand reporting.
    • Once the data is in one place, machines, algos and artificial intelligence (AI) can learn from it.

Challenges. Treasury at the company uses data from more than 25 internal and external systems with over 100 datasets.

  • Multiple applications need access to common datasets.
  • Individual teams have unique data reporting needs that may require more robust functionality than available with the TMS or other systems. Not everyone has access to all datasets, making permissions a problem.
  • Forecasting requires significant custom configuration.

Solution. An illustration of the system’s basic architecture showed that most data flows through the company’s TMS into the data warehouse, with the exception of Bloomberg market data. The member said it was “better to start with this than do everything at once.” The data from the TMS includes:

  • Incoming trade requests
  • Trade data
  • Bank statement data
  • Core treasury data

Use cases for the BI tool. Toturn the data into actionable information, treasury makes use of an internally-developed BI reporting tool that supplements TMS reporting and can embed reports in web apps. The member described three use cases:

  • Cash forecasting takes bank data from US bank partners loaded into the warehouse and makes use of machine learning (ML), a process the member said took considerable time to develop. 
    • The dashboard shows the forecast by cash flow type and by various models.  
    • The company’s future plans include using ML forecasts internationally.
    • A lesson learned: you need three years of historical data for ML.
  • Counterparty exposure uses data from the TMS and Bloomberg market data and involves numerous calculations in order to set maximum exposure levels for each bank. The tool was built with the company’s fintech team. 
    • The limits can change based on the credit perspective and the size of the company’s cash level as well as changes in the bank’s tier 1 capital.. 
    • Treasury’s cash management team gets involved if the limits are exceeded and senior leaders receive the information as well.
  • FX settlements helped address challenges faced by the company’s middle office, which can now see if the cash management team has held up settlements looking at a dashboard comparing the amount of FX trades settling to the amount of debit matching cashflows found.

Lessons and plans. In addition to the data requirements for ML, the member said treasury needs to limit data in the warehouse because a lot is “not clean” and needs to be properly categorized. “We need to be careful of the systems we integrate and make sure the data is actually useful,” he said. 

  • That said, the company plans to integrate new datasets and use new BI tools going forward. Other lessons and plans:
  • Technical program managers or data engineers are needed for data integrations. Another member at the company said the engineers are more efficient at getting data on the site. 
  • Work with the information security team to build a timeline for security reviews.
  • The company plans to add training on the systems to improve the user experience. Thus far, use by treasury has all been self-taught, the member said. 
Justin Jones

Author Justin Jones

More posts by Justin Jones