Tag Archive for: data analysis

Static Data – unsexy, but imperative to workflows

| 23-04-2018 | treasuryXL |

We live in the world of Big Data – we are told that there is so much potential that can be unleashed by embracing Big Data. This can lead to business efficiency, increased revenue, reduced expenditure, earlier identification of fraud etc. But for all this to reach fruition, we need to rely on the most basic building block – Static Data. Many companies have grand ideas of how to maximise revenue with data streams, yet fail to grasp the essential need for good, sound, structured Static Data.

Definition

This is data that remains constant (mostly) during the lifetime of its use; once input and recorded it becomes static and is used as reference data. The most logical example would be the data on relationships – when a company starts trading with a new supplier, a new record needs to be added to the bookkeeping system.

Types of data include:

  • Legal name of counterparty
  • Short name
  • Legal address
  • Telephone number
  • Fax number
  • Email
  • Contact persons
  • IBAN
  • BIC Code
  • KvK number
  • BTW number

Once the Static Data has been input it should only be changed by authorized staff. Dynamic data – the lifeblood of Big Data – can later be input (trades, invoice numbers, delivery dates, amounts etc.), but it needs good Static Data to make the data consistent. The complete data set for a counterparty must always be unique – there can not be 2 entities with the same set of data.

The structure of the data is also important – it could quite easily be the case that a company has one large client with the same bank details, but relationships with 5 different divisions. It is therefore essential that the correct protocols are in place for consistent data – whilst the legal name will be the same the importance of the short name becomes evident.

When it goes wrong

Inter company communication does not always involve use of a bookkeeping system. If staff start referring to a counterparty by another name than is in the system or use a name that is in the system but not the name they mean, problems can occur. Incorrect bookings arise which can lead to incorrect exposure levels or limits being breached. It can also be that a legal entity in a different country is referenced as they have offices in more than 1 country and issues such as VAT (BTW) can suddenly appear.

The need for secure Static Data is very high – the consequences of errors should never be underestimated. Data entry should be undertaken by people who do not enter any other data into the systems – in other words it should not be undertaken by the same staff that work in debtor and creditor administration.

Furthermore, a clearly defined protocol needs to be implemented to determine when and how Static Data can be changed.

In a little more than 1 month from now, GDPR comes into effect. The urgency to understand Static Data and to appreciate its significant contribution to daily operations has never been greater.

If you have any questions, please feel free to contact us.

[button url=”https://www.treasuryxl.com/contact/” text=”Contact us” size=”small” type=”primary” icon=”” external=”1″]

[separator type=”” size=”” icon=””]

Data analysis – pros and cons

| 18-04-2018 | Lionel Pavey |

 

With the advent of computing and ever more powerful processing capabilities, we are living in a time where there is more and more data available within a company. Advocates of data mining speak of the advantages that can be obtained by analysing all the data and discovering trends within the data. But there is also the risk that we end up being swamped by the data overload – so much data, so little time. If we want to analyse all our data, what is it that we truly want to find? How can we interpret all the data and arrive at beneficial conclusions?


Treasurers and cash managers are long time users of data analysis – it is used to go from a macro level to a micro level for individual transactions. When designing a cash flow forecast it is essential to take the micro approach. There will always be peak days for outflows – wages are paid, normally, on 1 specific day of the month; on the last working day of the month there is large expenditure relating to taxes and social premiums. Additionally, if the company works with monthly subscriptions, there will be peak days for inflows as all the renewals take place. These “exceptional” items need to be input as hard data on the relevant working days to assist in presenting an accurate forecast.

Another application of data analysis is to interrogate the actual Days Sales and Days Purchasing Outstanding – DSOs and DPOs – that make up the cash conversion cycle. A lot of unnecessary working capital can be tied up in this process. Understanding the transactional characteristics of individual debtors and creditors can be very beneficial to freeing up working capital. Furthermore, it allows the company to review their relationships – is it worth maintaining certain contacts if they do not meet the agreed terms and conditions on their trade transactions.

It is also possible to conclude that certain clients could benefit from a more advantageous pricing policy. Rewarding those that comply leads to better relationships and the improvements in cash flow can help reduce external borrowing requirements.

When attempting to analyse data, it is imperative to first understand what you are looking for. Obvious metrics could be month on month sales or purchases, seasonal effects on turnover, new products, promotional offers etc. The act of analysing data, together with the awareness within the company that the data is being analysed, can lead to anomalies caused by people’s actions. Data input could be subject to a form of “window dressing” – entries are made before the end of the month and then corrected in the following month.

It is possible to conclude that there is a trend in the data – some people even look for these – that could lead to a false sense of conclusion. There is also the danger that 2 different streams of data are linked to each other because they show the same trends. When analysing data is it necessary to be open minded about the expected outcome. If people start analysing with a preconceived idea of what the outcome should be, human nature can intervene and the data is interpreted in a way that justifies the preconceived idea.

Data analysis is a technical discipline that can overlook the fundamentals. Before the CDO crisis of 2008, most banks agreed with the interpretation of the underlying data within the systems, without challenging the reality of the scenarios being presented. Even after the crisis started, the banks were unable to foresee the severe impact that it would have on the whole financial market. I have a curious leaning to analysing long term interest rates – I have collated data on Interest Rate Swaps since the inception of the Euro. Whilst I am able to spot long term trends, I have failed in ever calling the top or the bottom of the market.

When analysing data, it is imperative that the basic fundamentals of a company and its products is never forgotten, If sales are down, a more fundamental approach needs to be undertaken. Are our competitors cheaper, are their products better, is the economy in a downturn, are our products obsolete?

Analysis should always be undertaken, but the results must always be weighed up against the reality of the marketplace. It is too easy to draw conclusions – it gives the illusion that the analysis is good.

A lot of good things can come from data analysis, but it must not exclusively determine the actions that a company takes in its quest for growth and survival.

Lionel Pavey

Lionel Pavey

Cash Management and Treasury Specialist

 

Cash forecasting: A data story

| 17-01-2018 | Cashforce |

Have you ever heard the dogma that people only use 10% of their brain capacity? Fortunately, this statement is a myth, but a similar (and more truthful) argument can be made for data usage. Using the example of an oil rig, a 2015 McKinsey & Company report states that an organisation typically uses less than 1% of the collected data to make decisions. While intuitively not all data will be useful to include in the decision-making process, it’s fair to say that there is a huge untapped potential.

From advanced retargeting in the marketing world to tailored music suggestions on Spotify, data has been in an uplift, opening doors in almost every field. Corporate finance & treasury is sitting pretty as well: amongst other areas, integrating relevant data into your forecasting model can facilitate substantial improvements in the quality of your cash flow predictions.

In this exuberant amount of data, it’s important to distinguish internal from external data.

Internal corporate data

Put simply, the bulk of data involved in cash projections will be found internally. Standard forecasting models, mostly build in spreadsheets, often make use of a small part of these data. Both account balances grabbed from banking portals and user generated input contribute to fulfil the daily, weekly or monthly cash forecast. User generated data may contain sales budget & forecast, average incoming & outgoing cash flows, projected dividends, CAPEX investments, etc. This information is necessary however typically lacks accuracy.

When making smart use of additional internal business data, most of these estimates can be derived from other internal data that may lead to a higher degree of forecast accuracy and a maintainable forecasting model. Such internal data sources are numerous and contain information on sales & purchase orders, quotations from your CRM system, production planning & all kind of recurring activities that carry relevant information on your future cash flows. Additionally, treasury data can automatically be included as well, enabling your treasury department to be multiple steps ahead instead of running behind daily facts.

To maximize the potential of your internal (big) data, algorithms and calculations need to be added to the forecasting model. By incorporating customer payment behaviour, seasonality patterns, correlations between different types of cash flows… your predictions can easily benefit from fine-tuning of these basic parameters. Re-evaluating those assumptions can by looking at meaningful patterns that are present in the data, can help to make a smarter and more tailored forecast. As an example, by carefully looking at past payment periods, future payments for each customer can be estimated with a high degree of precision.

 External data

Finally, integrating external data in your forecasting model will typically not affect cash the forecast in the short-term. It can however be relevant for long-term cash projections and fine-tuning. Market sentiment and macro economical indices will be most useful here, as well as all ticker information on treasury & commodity futures.

After capturing all this data, it’s key to consolidate everything from several (usually incompatible) operational systems. Note that not only the amount of data and diversity of data sources are important, but the accuracy of input and up-to-date information as well.

Consequentially, through extensive modelling and analysis, an effective and accurate cash flow forecast can be created. For this you would need software that can handle advanced big data analytics in order to convey pattern recognition and forecasting. The lion’s share of prevailing software doesn’t have the necessary integration possibilities and processing power to efficiently effectuate these kind of complex consolidation and analyses. Fortunately, some are built with this data requirements in mind and do have these capabilities. These make room for generating a significantly better cash forecast.

The world of business is going through rapid advancement in this age of technology, and the financial discipline is not spared in this phenomenon. While this data story unfolds, the time has come to put your “corporate brain-capacity” to use.  Will you let this wealth of data create an unseen amount of value?

If you want to find out more about Cashforce and their services and products please refer to their company profile on treasuryXL.

 

Forecasting the future by looking at the past

| 25-7-2017 | Lionel Pavey |


A key role within the Treasury function is providing forecasts to the directors and management. The most obvious would be the cash flow forecast, but others would include foreign exchange prices, interest rates, commodities and energy.

A forecasts is a tool that helps with planning for the uncertainty in the future, by analyzing data from the past and present whilst attempting to ascertain the future.

Internal – cash flow forecast

We would like our forecasts to be as accurate as possible – that the values we predict are close to the actual values in the future. This requires designing a comprehensive matrix to determine the variables needed for the data input. Data has to be provided by all departments within a company to enable us to build a forecast. This data needs to be presented in the same way by all contributors so that there is consistency throughout.

We also have to see if the forecast data is within the parameters of the agreed budget. We also need to check for variances – why is there a difference and how can it be explained.

External – FX and Interest Rates

A more common approach is to read through the research provided by banks and data suppliers to try and see what the market thinks the future price will be. Also we need to include data from the past – we need to know where the price has been, where it is now and what the expectation is for the future.

Extrapolating forward prices is notoriously difficult – if it were simple, we would all be rich in the future! But, by including past data, we can see what the price range has been, both on a long term as well as a short term basis.

When attempting to find a future value there are 2 common methods used – fundamental and technical.

Fundamental Analysis

Use is made of economic and financial factors both macroeconomic (the economy, the industrial sector) and microeconomic (the financial health of the relevant company, the performance of the management). The financial statements of a company are analysed in an attempt to arrive at a fair value. This leads to an intrinsic value, which is not always the same as the current value.

The value is normally calculated by discounting future cash flow projections within the company.

Technical Analysis

Use is made of the supply and demand within the market as a whole and attempts to determine the future value by predicting what the trend in the price should be. This is done by using charts to identify trends and patterns within the data. This assumes that the market price now is always correct, that prices move in determinable trends and that history repeats itself. Technical analysis uses the trend – this is the direction that the market is heading towards.

Whilst these 2 approaches are independent of each other, they can be used together. You could take a fundamental approach to value a company or asset, and then use technical analysis to try and determine when you should enter and exit the market.

Fundamental analysis is more of a long term path and technical analysis is more short term. The most important thing to remember is that markets only really experience large movements based on changes to the fundamentals. Predicting the long term future only via technical analysis is likely to be incorrect. All the major movements over the last 50 years in the prices of shares, bonds, foreign exchange and interest rates have occurred because of a change in the fundamentals.

In the next article, I will look at various methods of calculating averages to determine the trend.

An economist is an expert who will know tomorrow why the things he predicted yesterday didn’t happen today.

Lionel Pavey

 

Lionel Pavey

Cash Management and Treasury Specialist

 

The treasurer and data

| 13-12-2016 | Lionel Pavey |

dataTreasurers are confronted with new data every day – just think of the daily download from the bank statements. As this is a constant process, treasurers need to able to perform real-time financial analysis.

This analysis has to be performed with various internal data management sources, together with external data such as foreign exchange and interest rates. Originally this was done with rather large static data like annual budgets, but nowadays there appears to be a change in sentiment towards more proactive rolling forecasts.

The treasurer has a multitude of tasks including cash flow forecasting, hedging of foreign exchange and interest rates, investing excess funds, acquiring funding, advising on liquidity and financial risks, maintaining relationships with financial institutions. To be able to do all this requires a good continuous flow of internal data, together with an understanding of data analysis.

Treasurers need to be more proactive and interact and understand the requirements of all departments and divisions within the company. They need to be able to zoom in and out between the macro and micro levels and gain a better understanding of the business fundamentals through the whole scope from procurement to sales.

 The 5 biggest challenges

  • Receiving timely and accurate data
  • Recognizing and acting on data – both structured and unstructured
  • Keeping abreast with the constant changes in data technology – blockchain
  • Becoming truly proficient at data analytics
  • Continuous feedback to data providers to show how their data has been incorporated and used

So, just a few extra activities on top of the normal roles of forecasting, negotiating, risk management, and people management.

It is clear that the duties of a treasurer are many and that the job is a very special one requiring both proactive and reactive skills. For all of this to work, companies have to get all relevant staff to think the same way and understand the importance of continuous, timely and accurate data. Good structured data analysis can transform a company’s understanding of its business and provide important insight into its workings. This can lead to better knowledge of customers’ requirements, working capital flows, comparing internal data to industry or sector trends, changes to strategic thinking etc.

There are 2 sorts of data scientists: First those who can extrapolate from incomplete data….

Lionel Pavey

 

Lionel Pavey

Cash Management and Treasury Specialist – Flex Treasurer