Treasury Reporting: An Effective Middle Path

There has been a lot of coverage in the trade press about the external drivers affecting corporate treasuries in terms of the banking crisis, regulatory compliance and accounting standards. These are obviously having a major impact on the reporting requirements of treasury departments. But as well as the macro pressures, there’s also the micro story – changes occurring within corporates from board level on down that are impacting the way reports are generated and delivered.

There is increasing demand on treasury for richness and speed in information delivery to the board level. There is more focus on reporting and optimising a company’s global liquidity position and counterparty risks, which is a challenge in most large corporate treasuries. And there is pressure to deliver more information faster, despite frozen or shrinking treasury budgets and staff resources.

Smaller treasuries with relatively standardised system environment might run on a single core treasury system with no other surrounding systems. In this case the choice is easy – use that system’s reporting module to meet information needs. Many treasury systems have good reporting modules that offer access to all information with a user-friendly presentation.

But the reality for larger treasuries is inherently more complex. Apart from one or more treasury systems, they normally have a range of other internal systems, e.g. multiple enterprise resource planning (ERP) systems, special systems for customer finance, leasing, sales, data warehouse, etc. They can use the report generators that come with each of these solutions, but then they’re faced with the need to produce composite reports from these multiple production systems. Often the only way is manual cut and paste in spreadsheets – a process that’s difficult to manage efficiently and prone to error.

Larger corporates sometimes try to get around this with a company-wide data warehouse project. Often these are led and driven by the central IT department in response to explicit or perceived business needs, and commonly develop into large mega-projects requiring huge amounts of financial and human capital investment. The OLAP cube, all-singing, all-dancing dream is raised and financed, but the reality can be very different.

Even when implementations go according to plan, there are issues. Treasury users just can’t be given direct access to OLAP cubes – they still need to be manipulated to produce intelligence that can be reported. Treasury is more about precise calculations, condensed composite information and speed of reporting, than playing around with massive amount of data in an ‘nth’ dimensional world.
And problems arise from the dumping and subsequent feeding of multiple system data into the data warehouse. The data often loses most of the business logic and context from the original treasury system, diluting its value and making it more difficult for it to be applied in meaningful reports.

But IT departments can’t and shouldn’t be asked to replicate the business logic of treasury systems within a data warehouse. In many cases they won’t actually have the skills and resources to do it, and probably couldn’t afford to replicate the hundreds of man-years of development the treasury vendor has spent.

A Middle Path is Available

And, perhaps surprisingly for those who are accustomed to sneering at ‘low-tech’ and ‘dangerous’ spreadsheets, many organisations are finding that Excel is actually the best tool for a composite reporting approach. But only if they move away from the way spreadsheets are traditionally used.

Spreadsheets have traditionally reduced reporting effectiveness because of errors that occur through manual data re-entry, as well as the fact that duplicated data, calculations and presentations are held in unique self-contained files. Without additional software it is incredibly difficult to manage multiple users and provide version control and tracking of spreadsheets for compliance purposes.

But when you consider Excel as just a familiar presentation layer, you are playing to the application’s strengths. Everyone in treasury is familiar with its interface, graphing, data sorting and visualisation capabilities. So if you can use Excel as part of a reporting solution (see Figure 1) and follow these rules, you get the best of both worlds:

  • Never store data in Excel. All data presented in Excel should be sourced directly from the relevant treasury and production systems. This applies to transaction data, static data such as counterparties, standard settlement instructions and instruments and all calculated risk and performance data.
  • Never store any calculations in Excel. Excel is generally not very good at handling data sets that change in size, for example more transactions, new portfolios, or fewer counterparts. Any change in size of the data set usually means that you have to manually copy/paste a lot of Excel formulas in order to cover the new data set. This creates a lot of extra manual work and introduces the risk of having errors in the formulas that are cumbersome to find and correct. Calculations should be stored in a separate report editing layer of software that sits before the Excel presentation layer.

In most organisations when a CEO requests a report outside the usual reporting cycle, a member of staff has to manually update and collate the report and email it to the manager, which could take several hours or days – by which time the data is unlikely to reflect the real-time status of the business.

But if the treasury team have set up their Excel spreadsheet report layouts that source data directly from the treasury system and other production systems, members of the management team who don’t have or need previous experience of the underlying systems can, in one click, gain immediate access to updated real-time data in a condensed and composite format.

Taking this approach, a growing number of corporate treasuries, asset management organisations and central banks are using Excel effectively, while increasing automation, reducing errors and delivering reports to the business in a cost efficient manner that provide real-time intelligence on risk, liquidity management and performance.

Figure1: Using Excel as Part of a Reporting Solution