In my years as a consultant I have come across numerous questions and requirements in the playing field of Business Intelligence. All throughout the customer’s organization in any level and any discipline, from operational managers to sales representatives or back-office employees, I have received questions for smart reports, KPIs, alerts, exceptions and you name it. These vary as much in diversity as they do in complexity. The most innocent looking request could be a puzzle where as an apparently most impossible looking request could be solved within minutes. One particular however keeps resurfacing at many of my customers. It’s a bit like the crystal bowl or holy grail of Business Intelligence. The request: please give me reliable and detailed information in the cashflow or working capital of my organization and forecast this into the future. Thus giving an organization insight in when to expect cashflow-problems in advance, so they can react upon it.
So far at a number of customers this request was realized but mostly partial. When the source systems simply do not provide the data or the volatility and uncertainty are too big and diverse it’s not feasible to create a meaningful insight that adds value to the forecast of the financial health of a company. The complexity can increase further when the data has different sources and is most likely difficult to match or to make compatible. In this article I set out two actual realized parts and I close by lifting a veil of what might become achievable just yet.
One major component of the forecast of cashflow is the payment behavior of customers. The variables at play here are the following:
Based on these measures one can create a timeline into the future showing when cash inflow can be expected based on the expected date of invoicing and the expected date of payment. Based on the region or payment history of a customer one can add a certainty or reliability by which that cashflow is guaranteed. If aggregated on a higher level each customer’s certainty percentage can be weighed by the total amount of exposure, also know as headroom.
The more accessible variant is commonly known as a Credit Check or Head Room report showing how much customers still can spend without exceeding their credit limits. This can be a static report (credit limit – open amounts), but given the same payment terms and history one could advance this into a future prediction.
A similar insight can be created looking at the other side of the supply chain. This can involve:
What if we combine these two, add an outlook of our bank balances (which is of course a direct derivative of the cashflow realization) and put it all in one single dashboard?
What if we add some machine learning algorithms to predict the future on far more advanced calculations then the linear regressions used above?
What if we then store all these predictions in snapshots through time, so that we can compare our prognosis with the actual history of our cashflow and we start improving our predictive model?
Recently, we have developed a demo for cashflow predictions in Tableau. This demo is based on realistic data, generated in R. In this Tableau workbook, two types of dashboards were built; there are dashboards that provide basic reports on cash flow, such as inflow/outflow charts and some sales analyses, and there are dashboards that show what-if scenarios. The latter type shows how changes in sales, selling prices and wages can affect your companies cash flow in the future. This type of predictive analytics can be applied to many different variables in a similar way. For these variables, you can think of changes in VAT, focusing on promoting different products and switching to different suppliers. After applying these variables, the dashboards provide an outlook of the account balance to show what effect the changes would have. Additionally, it summarizes resulting profit or losses.
As a Senior BI Consultant at Cadran Analytics, Rick is highly specialized in Tableau.
Puck is a Junior Analytics Consultant at Cadran. She spends most of her time on performing data analyses in Tableau.
