TREASURY UPDATE

Forecasting Series #3: Achieving Visibility part 2: How can I get at the data INTERNALLY?

“Are we there yet?” call the kids from the back seat for the umpteenth time. “You can see it from here, it’s not far…just to the base of the mountain range” comes the increasingly more agitated response. Each adult silently wondering how come it seems like the end point magically stays at the same distance. And, will we have enough gas to get there without stopping to fill up…again?

Getting at data internally is really easy if you have never attempted it before. Really easy. Then, when you attempt to acquire it there is always ‘something you didn’t expect’ about it that makes getting good data elusive. Just like the hotel at the foot of the mountain range…elusive.

This is a blog entry and not a book on overcoming forecasting challenges. Accordingly, I’ll lay out some of the thorny issues about getting at data INTERNALLY, and mention some options. But, this isn’t going to be the exhaustive decision tree about solving all of your internal data needs. Hopefully this will get you thinking that it requires a bit more work than the simple diagram you drew on the whiteboard. You know, the one with the boxes of information sources that had the line running over to your treasury data repository, reporting tool/cube or treasury workstation? That simple line is nice for drawing, but it is really only the “Fallacy of the Line”. What does the line mean? Is every line we draw equivalent? A line can be the same size and length but mean different things:

The fallacy of the line

Here is a starter list of issues to get your heart racing and the stomach creating an overabundance of acid:

• All of the data is in SAP/Oracle (some wonderful ERP systems that was supposed to solve all of our data issues)?
  • Not everything is in the system.
  • There are multiple instances of the ERP.
  • We have multiple entities that are not on the same type of platform
  • The data is different because the data we need isn’t crucial for the other area to process/analyze things correctly.
  • Data sits is so many disparate systems
  • The data is in different formats.  Japanese Yen is listed as YEN, JPY, JPN, Y… The normalization of this data is either a huge project or is addressed via a formatting process or via associative tables.
  • Stuff isn’t in the system

“We would have been able to deliver this on time if we didn’t have such data problems. No one could have anticipated xxx, yyy and zzz.”

Here are some primary ways of getting at data and some strengths and weaknesses of these models:

• Manual Entry. Major items that primarily sit in someone’s head can be captured on a web form and included in a cashflow forecast. This requires relentless feedback and retraining when people move.
• Feed of Data. This method works great for the ‘static’ aspect of reporting, forecasting or executing transactions. The constancy of the process can work well. However, as soon as you need to do some analysis (what was the cause…and root cause of this variance) you can only dig into the data that you have captured.
• Connection. A live connection can interrogate the data either when needed or real time. Some methods can degrade performance. Other methods are non-intrusive. Connecting allows treasury to perform additional analysis and ask better questions more quickly.

Your treasury information design is crucial. And, your plan and approach to getting at data that resides within an organization is far harder to achieve than most people can imagine due to disparate systems, processes and business requirements of the data. Underestimating the effort means a lot of extra work and extra explaining.  It is possible to get at internal data and manage through the non-normalized information effectively. Run from those who have never done it, but draw a line between two boxes and say it is easy. They will be the first ones to offer up the excuse of ‘we didn’t expect the data to be this bad’.

/caj

Where is the Data?

It always helps to know where you are going before you start a trip. Unless you are just going for a ‘walk-about’ or wandering for the sake of wandering. For those seeking to forecast their cash flows, one starts with the outcome they seek (perhaps a detailed 90 day daily cash forecast of all cash flows, with variance analysis capabilities, etc.). As soon as the desired outcome is determined for a forecast, a number of questions about data usually emerge that must be answered. In many instances, treasury professionals have these four questions:

• What data do we need? This is best answered on a custom basis for each individual firm.
• Where is the data?
• How can I get at the data that exists inside our company?
• How can I get at the data that resides beyond our company servers or databases?

This short blog entry is focused on the 2^nd question. Where is the data?

“In theory, reality is just like theory.

In reality, theory is nothing like reality.” source unknown

Determining what data you need and where it is most appropriately found is, in theory, quite a simple thing. However, serious analysis and discovery must be made when determining the location of the data that is needed. Questions such as the following can help you create your information inventory list.

• How frequently will I need this data? If there are options, what is the best source for that data given this frequency?
• What level of data will I need? Summary, detailed? Do I need to be able to drill down into it for research purposes?
• Given various options, which data is more accurate most of the time?
• Does some of this data sits ‘off the grid’ that I need to access or receive?
• Location of data? What sits inside Treasury, what is in the organization in another business area, what is on a spreadsheet? Is it is housed in a SaaS system or stored in locations outside our data center? Finding the location of the data includes the path to the server, the database and the specific table(s) that are necessary.
• Data needed that doesn’t exist. Some data that is needed for forecasting needs to be created. This can range from creating relational tables (on drives or in memory) from multiple sources for the forecast or analysis to creating databases for hierarchies or associative tables for a variety of reasons.

Once the location of the data is filled out on your information inventory list, you are now able to begin the process of getting the data or securing access to that data and determining what the format, shape and quality it is in – and what needs to be done to it.

/caj