Predicting cashflow with BI

In my years as a consultant, I have encountered many questions and requests regarding business intelligence. Throughout the organization, from operational managers to sales representatives and back-office staff, I received requests for smart reports, key performance indicators (KPIs), alerts, and exceptions. These requests varied greatly in complexity. Sometimes a seemingly simple request turned out to be a huge challenge, while a complex request could be solved in just a few minutes. However, there was one question that kept coming back from my clients. It seemed like the crystal ball or the holy grail of business intelligence: reliable and detailed information about the cash flow and working capital of the organization, with a forecast for the future. This allows an organization to gain insights into potential future issues so they can act on them in time.

So far, this request has been realized at a few clients, but often only partially. Sometimes the systems simply do not provide the required data, or there is too much uncertainty and variation to generate meaningful insights that add value. The complexity increases when data comes from different sources. In this article, I will discuss two implemented solutions and give an overview of what is currently achievable. Finally, we will show how this can be visualized in Tableau using a demo dataset.

Forecasting liquidity

An important part of cash flow forecasting is predicting customer payment behavior. The following factors play a role in this:

• Customer Credit Limit
• Customer Insurance Amount
• Customer Payment Terms or Conditions
• Open Invoices
• Open Sales Orders (not yet invoiced)
• Open Sales Contracts (not yet ordered, but already scheduled in time)
• Service Subscription recurring revenue
• Payment behavior
• Payment certainty

Based on these factors, a timeline can be created that shows when income can be expected, based on expected invoicing and payment dates. Depending on a customer’s payment history, a reliability factor can also be added to the forecast. This insight is also known as liquidity space.

A common example is credit control, which shows how much a customer can still spend within the agreed credit limit. This can be a static overview (credit limit minus outstanding amounts) but can also include a forecast based on payment terms and history.

Commitment Forecast

A similar insight can be created looking at the other side of the supply chain. This can involve:

• Supplier Payment Terms or Conditions
• Open Invoices
• Open Purchase Receipts (not yet invoiced)
• Open Purchase Orders (not yet received)
• Open Purchase Contracts (not yet ordered, but already scheduled in time)
• Service Subscription recurring expendables
• Employee Salary payments
• Lease fees
• Expected indirect and overhead costs (such as fixed assets maintenance)
• Settlement of to pay received VAT on Revenue -/- to receive VAT on Purchases (quarterly)

Cashflow Prediction

What if we combine these two insights, add a forecast of the bank balance, and bring everything together in one dashboard? What if we use machine learning algorithms to refine the forecasts and provide more advanced insights than with linear regressions? And what if we store all these forecasts as snapshots, allowing us to compare our predictions with actual cash flow and continually improve our model?

Tableau Demo for Cashflow Prediction

Recently, we developed a demo model for cash flow forecasting in Tableau, based on real-life data generated in R. In this Tableau model, we built two types of dashboards: dashboards with basic reports on cash flow, such as diagrams of incoming and outgoing cash flows and sales analyses, and dashboards with “what-if” scenarios. The latter shows how changes in, for example, sales prices or salaries could impact your organization’s cash flow in the future. With these insights, you can also consider changes in VAT, promoting other products, or switching to different suppliers. The dashboard then provides a forecast of the bank balance and summarizes the expected profit or loss. of verlies samen.