A Data Science Approach to Cashflow Modelling in Capital Intensive Companies

Written by Simon Hiel

Cashflow modelling in growing companies is crucial but hard to get right -here is why!

A growing company works much better when management has a clear view of the capital -the cash -they have at their disposal. In particular, companies employing a buy & build strategyhave large fluctuations in cash positions due to large cash-outflows at the time of acquisition. Even if they are able to financetheir acquisitions, after the recent 2008 & 2016 crisis the EU requires an increasing amount of self-funding in each deal. More recently, the covid pandemic and war in Ukraine have shaken-up modern supply chains, with shortages in the supply of a wide range of products and materials. From computer chips to wood to wine bottles. The wine industry is ‘bottlenecked’ by a shortage in supply of glass wine bottles. As response to these supply chain shocks,many companies have decided to rethink their supply chain strategies. As a short to mid term remedy, they have decided to increase inventory, a decision that mobilizes working capital leaving less room for M&A activity. Finally, prices of products have increased across the board and are eating away at profitability and increasing cash outflows. All these elements emphasize the importance of having a clear picture of your cash position, cash inflows and cash outflows to make sure operations are not placed in cash distress from M&A activity and other outside factors.

Two approaches to cashflow modelling

In general, we define four scenarios of uncertainty in cashflow modelling. Firstly, you can have events with no uncertainty both in terms of the occurrenceof an event and the cashflow linked to that event, a good example is interest payments. Secondly, you can have events without any uncertainty of the occurrence, where you know at which point in time they will occur, but you do not know the cashflow linkedto the event. End of year bonusses are a great example, you know when they have to be paid but it is hard to estimate the exact amount. Thirdly, events for which the occurrence is uncertain but the cashflow amount is known beforehand. A good example here are client receivables. Lastly, the hardest scenario, you have uncertain events for which you do not know when they will occur, nor the linked cashflow. This is typically a cashflow related to deals -where the deal closing price and date can change quickly. We handle each scenario differently. The first case can be covered by financial data. The second scenario can be covered by a rule based approach to cashflow modelling, because applying rules to estimate amounts is easier than applying rules to estimatethe occurrence of an event. Finally, scenarios three and four are covered by machine learning approaches.

Another element to cashflow modelling supporting the idea of different approaches is the size of the predictive window under consideration. In the short term, more information is available and these windows can be covered by business rules. The further into the future, the less information is available and the more heavily one has to rely on machine learning approaches. In the next section we shortly introduce both techniques and discuss their benefits and limitations.

Classical Rules Based Approach

A typical approach to cashflow modelling is applying business rules on events for which you know the timing. For example, all operational assets are financed at 90% of acquisition price, salaries are paid on the 2nd day of the next month and are estimated 40% of that month’s revenue, suppliers are paid on the due date of the invoices (use aging list), etc. These rules can be applied as follows:

  1. Start from modelling cash inflows based on business rules: For example: 95% of people pay within a week of recognized revenues. This allows us to use revenues as proxy for cash inflow and pretend that we know it’s occurrence.

  2. Subtract all cash outflows based on business rules: For example: recognized costs are paid in batches. One batch of payments is processed per week on Thursday. Each batch payment fetches all invoices with deadline for next week and are paid immediately. Banks take 2 business days to execute the transactions. Thus the resulting cash outflow takes place on every Tuesday.

  3. Determine net cash in-or outflow

  4. Add the net cash movement to the opening balance for that period

  5. Repeat process for future periods

  6. Replace the closing balance with the actual closing balance as time progresses

  7. Recalculate the future closing balances.

This approach works for short term modelling. One of the limitation we face here is the use of aging lists, which typically can only be used for determining next month’s cash position. This limitation occurs because suppliers only send their invoices a short amount of time before the payment date. Supplier payments shift from certain events with certain cashflows to uncertain events with unknown cashflows. Another limitation are uncertain events such as acquisitions and capex investments. These events are better suited for machine learning approaches.

Machine learning approaches and statistical modelling

For some elements in the modelling of cashflows, it is difficult to determine business rules. For example, how do you determine when accounts receivable will be received and how much will they be a bit further into the future? How do you take into account interim personnel holidays resulting in lower monthly cost? When do capex investmentstake place? When will we be able to close the M&A deal and acquire a new entity? -just to name a few. These are all elements for which either the event or the amount or both are uncertain. Machine learning techniques are much better at handling these scenarios than business rules.

Modelling events with unknown occurrence but known cashflow amount

In general, cash inflow amounts are known well before hand based on sold projects, scheduled work, invoices sent, revenues recognized, etc. However, timing whenthe cash will come in is often much more difficult. A good approach is to use past events and the meta-data to train AI models that predict, for each week in a predefined time window, the likelihood of the event taking place. The model needs to be parametrized to determine the number of predictions it should put out. Once a prediction for each week in the predefined window is determined, the week with the highest probability is chosen and the amount is mapped to the correct week.

Modelling events with known occurrence but unknown cashflow amount

This scenario lends itself to modelling expected values based on a number of event features such as type of event, number of occurrences, a feature detailing the impact, can it be financed, etc. All these features can be applied to training machine learning models. Once the model is trained, it can be used to determine the amount of cashflow related to an event. And since we know the occurrence date before hand, we simply map the amount to the correct date in the model.

Modelling events with unknown occurrence and unknown cashflow amount

Here we apply typical statistical time series approaches because nor the amount nor the date of occurrence is know. These methods lend themselves best to scenarios with a high level of uncertainty. Typically, these are external events and relate to macro economic or industry shocks.

The BrightWolves Way -A hybrid cashflow mode

At BrightWolves, we do not believe in one-solution-fits-all. We look at the client’s context and analyze their situation vigorously. For example, we quickly learned that applying a typical data science approach, as the theory defines it, would not work at our client, which was very active on the M&A front. And so, our hybrid approach was born, where the well integrated entities would be included in a stable model using a rule based approach for near-term cash flow predictions and a set of data science models for longer term predictions. Depending on the type of prediction required, i.e. predicting the occurrence of the cash event or predicting the amount of the cash event, different models would be used. The second part of the consolidated cash flow predictions, called the flexible model, was implemented as a parametrized lists of events, where the cash event data (date and amount) could be entered as range or as exact value, and the model would pick-up the events and map them to the consolidated cash planning based on the highest probability fit. This still allowed the end users to quickly add manually any new cash events, given that M&A activities can be quite unpredictable and change rapidly. The outcome of our implementation was a strong stable model for predicting cashflows both near-term and long-term for the “well integrated”entities but remained flexibleenough to plan for new M&A activity.

If you need help with setting up a predictive cashflow model or want to modernize and improve the accuracy of your existing cashflow? Do not hesitate to reach out to our partner and data expert, Luc Machiels, or the author Simon Hiel, at luc.machiels@brightwolves.eu or simon.hiel@brightwolves.eu