Case Study: How to improve the cash collection process with Power BI

Cash flow is a weak spot in many businesses, often because of a lack of visibility of the company’s cash position rather than a cash flow problem per se.

This client combines technology and big data expertise to help its clients to make the most of their data assets.

Last year we wrote about our work with this client and how the ireport team helped them with their requirement to reduce working capital.

The Requirement

Delighted with the project’s results, they wanted to improve and model the cash collection process to forecast cash receipts using both system data and human knowledge, including:

When the invoice is due
When the credit control team believes the invoice will be paid
An estimate of when the client will pay based on historical payment patterns
A view of what could be done to accelerate cash collection

I am very pleased with the results of dashboard reporting for cash forecasting and collections. This reporting enabled several enhancements including but not limited to real-time data analysis, data insights into customer payment behaviors and expectations, informative actuals vs. forecasted visualisation, and dashboard views and trends that allow for decision support and accelerated actioning. James at Isosceles was extraordinary and instrumental in helping me realise my vision and key objectives of this reporting. This project resulted in immediate benefits across the organisation such as greater executive level and cross-functional transparency, improved cash forecasting and collections management, and overall process improvements.

Why Isosceles

Isosceles’ track record spoke for itself. They had already successfully implemented two critical cash flow projects:

Optimising staff utilisation provides visibility of the time spent on client work and the associated revenue, which enables management to maximise the most profitable use of employee time.

Analysing the revenue side of their cash cycle to identify bottlenecks in the process. Our analysis breaks down the overall cycle into the average time it takes to complete work, file and approve timesheets, raise an invoice, and receive payment. While this analysis was mainly backwards-looking, they now wanted to build a forward-looking model that could predict cash collection.

The Solution

Our solution delivered a comprehensive cash receipt forecasting model for the client.

Enhancing the cash cycle project with additional system connections

Building on the previous cash cycle project, which successfully allowed the client to reduce the working capital cycle, the team connected it with additional data points from the client’s Microsoft Dynamics GP accounting system and Kimble time recording system.

This data was then blended with human knowledge from the credit control team. For example, when the credit-control collector anticipated the client would likely pay.

The team constantly reviewed, refined and developed the data model, calculations, data points, and reports. At many stages, we started with data extracted manually from internal systems, used these to build and refine the analysis, and then worked with the IT department to move to a SQL server connection for a more robust data source which updates automatically on set schedules and is not dependent on ad-hoc downloads.

Cash Collection Modelling

The modelling work consisted of developing different scenarios. Our solution has given the client visibility of the cash collection process, including:

Cash received so far in the month
When outstanding cash is contractually due
When the credit control team estimate outstanding invoices would be paid based on the client’s payment history over the past six months using weighted averages

Flexible and ad-hoc reporting

They can now create flexible ad hoc reports and individual manipulations specific to each of the business’ collections teams, so each team member uses the same data set.

The Results

Isosceles’ solution has delivered:

Greater visibility of actual cash inflows and expected cash inflows with greater granularity and confidence. This has allowed the new director to target a robust and accurate collection effort.
Visibility of amounts available for early payment if required to meet cash collection forecasts and costs associated with early payer discounts.
Building on the previous project that forecasted cash flow based on invoices, the team added the capability to include work that has been undertaken but has yet to be invoiced. This provides a complete picture and allows the client to forecast further.
The management team can create flexible ad hoc reports specific to each of the business’ collections teams, with the whole team using the same data set.