Cash forecasting data visualisations

cash forecasting data visualizations

Data visualisation is an important step in any form of data analysis. Presenting data in a graphical format often helps to highlight trends, identify anomalies, and uncover insights that will be missed when simply reading through raw data. The same is true when analysing the data produced as part of a cash forecasting process.

To illustrate how data visualisation can help with data analysis, this post will review three potential ways to visualise cash forecast data. These three options are;

  • Cash Walk Through Visualisation (shows how cash moves from an opening position to a closing position)
  • Forecast Versus Actual Visualisation (quickly understand forecast variances)
  • Time Series Visualisation (analyse accuracy across multiple versions of a forecast)

However, it is important to note that these examples are not exhaustive, and that many other formats of visualising cash forecasting data are available.

Cash Walk Through Graph

A cash walk through visualisation breaks the journey from opening cash balance to closing cash balance into a series of steps that identify the most significant contributors/detractors to the net cash balance.

In the example below, we can see the three headline cash inflow categories highlighted in green (Customer Receipts, Investing Inflow, Dividend Receipts). The four headline outflow categories are highlighted in red (Supplier Payments, Tax, Payroll, Debt Payments).

Cash walk through visualisation - system view

One of the key benefits of this method of data visualisation is that it highlights the extent to which each category of cash flow affects the cash balance in an easy to understand visual. This means 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 outgoings) cancel out the positive contributions from customer receipts, investing inflow, and dividend receipts combined.

Forecast vs Actual Variance Visualisation

The graph below visualises a comparison of forecast versus actual data for a variety of reporting entities. It breaks the measure into two categories, percentage variance and amount variance. (Here, the different reporting entities’ variances are transposed to a common currency, US dollars).

Forecast vs actual variance visualisation - system view

One of the key benefits of this method of data visualisation is that it allows material variances to be quickly identified and put into context with other entities’ percentage accuracy. This means that attention can be paid to the variations that have the greatest impact. For example, in the instance above, Brazil had a major deviation between forecast and actual (92%) but this only equated to a variance amount of $1.3million. Whereas in China, where forecast accuracy was better (though still poor as China had an 83% divergence from the actuals), the value of this discrepancy  was $4.0million. 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.

Time Series Visualisation

A Time Series Visualisation allows variance analysis to be carried out across multiple forecast versions and it is usually best displayed in a table such as the one below.

Actual vs forecast matrix visualisation graph

Here, the table shows how multiple forecast versions compare to the actual data (in bold):

  • y axis: shows versions of the forecast (when they were produced)
  • x axis: shows the month each forecast is projecting the closing balance of
  • Each data point on the chart corresponds to a forecast or actual (actuals are in bold) closing cash position
  • Taking the Feb-18 forecast version as an example, scanning along the row we can see that this submission captured the actual closing balance for JAN 2018, and forecast closing balances for FEB – JUNE 2018.

The advantage of capturing the data in this format is that it enables easy comparison of multiple forecast versions. This means that trends in the data can be easily identified and, where appropriate, addressed to improve accuracy.

For example, the table above shows that while forecast accuracy broadly improved as the forecast horizon reduced, there remained a tendency to underestimate the actuals. 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 it underestimated by 19,000), a forecast for May 2018 (underestimated by 100,000) and a forecast for June 2018 (underestimated by 134,000).

As there is enough data captured (in the table we can see 36 forecasts and six closing balances) it can by hypothesised that this is not a natural variance but rather a trend that is the result of a fundamental area of the forecast being miscalculated. This might be higher than expected sales volumes, lower overheads, perhaps a recent reduction in business rates was secured by the company but this wasn’t reflected in forecast calculations, for example. 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.

Data visualisation software and tools

As with all parts of a cash and liquidity forecasting process, data visualisation options can be greatly improved with the use of specialised software tools. We have helped many companies to automate their cash forecasting processes, to increase forecast accuracy, and set up a new cash forecasting process altogether. If you are looking to improve the impact of cash forecasting in your organisation, contact us to see a demo of our specialised software.