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 strategy have large fluctuations in cash positions due to significant cash outflows at the time of acquisition. Even if they are able to finance their acquisitions, after the recent 2008 & 2016 crises, 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 the supply of glass wine bottles. Many companies have decided to rethink their supply chain strategies in response to these supply chain shocks. As a short to mid-term remedy, they have agreed to increase inventory, which 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 outflows to ensure 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 cash flow modelling. Firstly, you can have events with no uncertainty both in terms of the occurrence of an event and the cash flow 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 they will occur, but you do not know the cash flow linked to the event. End-of-year bonuses 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 cash flow amount is known beforehand. A good example here is client receivables. Lastly, in the most challenging scenario, you have uncertain events for which you do not know when they will occur nor the linked cash flow. This is typically a cash flow related to deals -where the deal closing price and date can change quickly. We handle each scenario differently. Financial data can cover the first case. The second scenario can be covered by a rule-based approach to cash flow modelling because applying rules to estimate amounts is more accessible than applying rules to assess the occurrence of an event. Finally, scenarios three and four are covered by machine learning approaches.
Another element of cash flow 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 business rules can cover these windows. 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 cash flow modelling is applying business rules on events for which you know the timing. For example, all operational assets are financed at 90% of the acquisition price, salaries are paid on the 2nd day of the following month and are estimated to be 40% of that month’s revenue, suppliers are paid on the due date of the invoices (use ageing list), etc. These rules can be applied as follows:
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 a proxy for cash inflow and pretend that we know its occurrence.
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 the deadline for next week and is paid immediately. Banks take two business days to execute the transactions. Thus the resulting cash outflow takes place every Tuesday.
Determine net cash in or outflow
Add the net cash movement to the opening balance for that period
Repeat the process for future periods
Replace the closing balance with the actual closing balance as time progresses
Recalculate the future closing balances.
This approach works for short-term modelling. One limitation we face here is ageing 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 confident cashflows to uncertain events with unknown cashflows. Another limitation is uncertain events such as acquisitions and CAPEX investments. These events are better suited for machine learning approaches.
Machine learning approaches and statistical modelling
It is challenging to determine business rules for some elements in modelling cashflows. For example, how do you decide when accounts receivable will be received and how much they will be a bit further into the future? How do you consider interim personnel holidays resulting in lower monthly costs? When do CAPEX investments take place? When will we be able to close the M&A deal and acquire a new entity? These are all elements for which either the event, 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
Cash inflow amounts are generally known well beforehand based on sold projects, scheduled work, invoices sent, revenues recognized, etc. However, timing when the cash will come in is often much more difficult. A good approach is to use past events and meta-data to train AI models that predict the likelihood of the event occurring each week in a predefined time window. The model needs to be parametrized to determine the number of predictions it should put out. Once a forecast for each week in the predefined window is selected, the week with the highest probability is chosen, and the amount is mapped to the correct week.
Modelling events with known occurrences but the unknown cash flow amount
This scenario lends itself to modelling expected values based on several event features such as type of event, number of occurrences, a feature detailing the impact, whether it can 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 cash flow related to an event. And since we know the occurrence date beforehand, we map the amount to the correct date in the model.
Modelling events with the unknown occurrence and unknown cash flow amount
Here we apply typical statistical time series approaches because nor the amount nor the date of occurrence is known. These methods lend themselves best to scenarios with a high level of uncertainty. Typically, these are external events related to macroeconomic 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 for 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. Different models would be used depending on the type of prediction required, i.e. predicting the occurrence of the cash event or predicting the amount of the cash event. The second part of the consolidated cash flow predictions, called the flexible model, was implemented as a parametrized list of events, where the cash event data (date and amount) could be entered as a range or as an exact value. 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 any new cash events manually, given that M&A activities can be pretty unpredictable and change rapidly. The outcome of our implementation was a robust and stable model for predicting near-term and long-term cashflows for the “well-integrated” entities, but we remained flexible enough to plan for new M&A activity.
Do you need help setting up a predictive cash flow model or want to modernize and improve the accuracy of your existing cash flow? Do not hesitate to reach out to our partner and data expert, Luc Machiels, or the author Simon Hiel, at email@example.com or firstname.lastname@example.org.