How to analyse cash forecasting data: tips on data visualisations

The data analysis conducted at the end of a cash forecasting process is where the insights are uncovered, and therefore where the real value is found.

Data visualisation (a process where data is displayed graphically) often helps to identify anomalies, highlight trends, and uncover insights in data that might otherwise have been missed.

This article reviews three forms of cash forecasting data visualisations, to illustrate the advantages that data visualisation has to offer. Those forms are:

1. Forecast vs actual variance graph. This is a simple scatter graph, but can really help to identify where material variances are to be found.
2. Cash walk though graph. Similar in appearance to a bar chart, this type of visualisation maps out the journey from opening to closing cash positions. This means the significance of contributors and detractors from the overall balance can be quickly identified.
3. Time series chart. Presented in a tabular format, this type of data visualisation allows quick comparison of multiple forecast versions. The layout of the matrix also means that changes in forecast figures as they approach to actual data can be viewed to identify trends in the data.

Below, we review each of these types of forecast data in further detail.

Forecast vs actual variance graph

This type of data visualisation measures the accuracy of cash forecasts for a range of reporting entities.

The graph below gauges accuracy by two different measures, percentage variance and amount variance. (To simplify and enhance the visualisation, the different reporting entities’ variances are transposed to a common currency, USD).

The purpose of this type of data visualisation is to enable quick identification of where material differences can be found, and put these differences into context with other entities. This means that attention can be paid to the variations that have the greatest impact.

For example, in the instance above, we can see that Brazil had a major percentage variance from the forecast and actual figures (92%) but that, in monetary terms, this variance only equated $1.3m. Whereas in China, where forecast accuracy was marginally better (though still poor with an 82% divergence), the monetary value of this difference was $4m.

This means that an increase in the accuracy of forecasts produced by the Chinese entity (where there is considerable scope for improvement) would have a far greater impact on overall, company-wide forecast accuracy than focusing on the most inaccurate forecasting entity.

Cash walk through graph

This type of data visualisation shows the journey from opening cash balance to closing cash balance in a series of steps that identify the most significant contributors/detractors to the net cash balance.

The example below shows three headline cash inflow categories highlighted in green (Customer Receipts, Investing Inflow, Dividend Receipts) and four outflow categories highlighted in red (Supplier Payments, Tax, Payroll, Debt Payments).

A key benefit of this type of data visualisation is that it highlights the extent to which each category of cash flow affects the overall cash balance in an easy to understand visual, meaning that attention can be focused on the element(s) that will have the greatest impact.

For example, in the graph above we can quickly see that supplier payments (which total $10million in outflows) cancel out the all of the positive contributions from customer receipts, investing inflow, and dividend receipts combined.

Time series chart

Below is a screenshot from the CashAnalytics system showing an actual versus forecast closing balance matrix.

In this visualisation we can measure how multiple versions of the forecasted closing balance compared to the actual data.

The table above shows:

• y axis: looking down the y axis we can see multiple forecast versions
• x axis: looking across the x axis we can see what each forecast version is predicting the closing balance will be at the end of each month
• Each cell in the table shows a forecast or actual closing cash position (actuals are in bold and italics)
• To illustrate, looking at the Feb 18 forecast version, scanning along the row shows that this submission recorded the actual closing balance for Jan 2018, and forecasted closing balances for Feb – June 2018.

Capturing the data in this format enables easy comparison of multiple forecast versions. This helps to identify trends in the data that can then be addressed to improve accuracy.

To illustrate this point, the table above shows that while forecast accuracy broadly improved as the forecast horizon reduced, we can see a trend where the actual figures where consistently underestimated.

If we review the submission made in Apr 18, we can see that the actual figure captured for the March 2018 closing balance was 154,000. Included in this submission were forecasts for the April 2018 closing balance (which were underestimated by 19,000), a forecast for May 2018 (underestimated by 100,000) and a forecast for June 2018 (underestimated by 134,000).

The extent of the data captured in this visualisation (in the table we can see 36 forecasts and 6 closing balances) means that this trend can be identified. Once identified, it could by hypothesised that this is not a natural variance but rather the result of a fundamental area of the forecast being miscalculated. For example, this trend might have been caused by a recent reduction in business rates that was secured by the company but wasn’t reflected in forecast calculations.

In any case, once this trend is identified its underlying cause can be investigated, identified, then corrected. Thereby improving forecast accuracy.

Benefits of data visualisation

As mentioned at the beginning of this post, data visualisation is a necessary part of any data analysis. Presenting the information graphically enables an analyst to quickly spot trends, identify anomalies, and helps to uncover the underlying causes of any flaws in the process. For a treasury team, this means that these insights can then be presented back to the business in a clear and concise format for easy interpretation by senior management. This therefore positions treasury as a strategic department within the business.

About CashAnalytics

CashAnalytics has helped many companies across a broad range of industries to build and maintain best-in-class cash forecasting processes that produce the highest quality reporting and analytics outputs.

Our flagship cash flow forecasting software provides dashboards, status reports and forecasts, where data is presented with an intuitive interface. You can see real-time cash positions from across the entire business while a suite of analytics tools helps uncover insights in your data that improve decision making.

If you would like to see a demonstration of how software and data visualisation can improve your forecasting processes, please contact us directly.

• Read more under:
• BankingTech
• Digital Banking