You may also be interested in:

Articles

Cash Flow Forecasting In a Complex Business Environment

  • By Vasil Lyaskov
  • Published: 7/23/2015

As one of the largest companies in Bulgaria, among the 150 largest transport companies in Europe, my company has enjoyed solid performance in recent years. Revenues grew at the average rate of 40 percent per year which resulted in serious difficulties regarding cash collection management. Such dizzying growth, often is done in combination with major working capital assumptions, e.g., when the company “knows” (or hopes) that it can go without collecting any cash for the next 60 or 90 days.

Imagine, however, what might happen if the client with 60 days of deferred payment decided to wait until day 70, or day 80 or 90? What might happen if many clients did this? There are alternatives, including revolving credit lines, factoring agreements, letters of credit, etc. Even so, these instruments come with a price and in order to pay that price the company needs -- you guessed it -- cash. This is why cash management as part of working capital management is among the most important priorities of the FP&A professional. And being able to accurately forecast cash inflows to determine the external funds that may be needed is a skill of high value.

Forecasting methodology

The solution we came up with may be considered fairly straightforward, but we had to rack our brains for some time in order to “discover” it. We start by defining periods of 30 days based on the fact that what we receive as cash inflow this month will be the result of sales invoiced this month (for example, from customers with stricter trade policies), last month (e.g., in the last 30 days), in the last 60 days, and so on until the 120th day. Beyond that, we consider payments unlikely. Once these periods are defined, we sum up the amounts in each of them. We do this historically for as long as we have accounting data. We then collect data on cash inflows each month. In this way, we have a set of variables, both endogenous (cash flows) and exogenous (the set of periods).

It is worth noting that we implement the above in respect to the particular business line. Our company’s major sales generator is transport and forwarding. However, we have a variety of other businesses as well. This is why we define receivables from each and every business line. The same, of course, goes for cash collected which should also correspond to the relevant activity. A further note to make is that in order to forecast cash inflows we need the amount of sales, invoiced 30 days ago, 60 days ago, and so on, which we readily have in actual numbers. However, we also need next month’s figure since we want to know how much cash will be collected from sales made in that month.

In other words, we need to make sales projections. This, of course, adds a further level of complexity to the equation. Last, but not least, some dummy variables can be added to represent seasonal variations as well as some other to account for changes in customers’ payment behavior. For example, we calculate the elasticity of cash inflows to sales and in case there is significant deviation from the historical mean we can add a dummy that has the value of 1 for this period (this, we found, adds greater certainty to the equation).

Applying statistical principles and calculating various measures on the data allows us to conduct ordinary least squares (OLS) regressions with each individual variable (period). This is important as it will allow us to rule out every insignificant period. For example, in our company we use only the periods up to 120 days. T-tests conducted via OLS with all remaining periods show that they hardly explain any change within the variance of cash flows (poor R-squared and probability results).

Once all the significant variables are determined, we conduct multivariate regression where we regress cash inflows against all variables. The result is a set of beta coefficients which give us a formula that allows for cash inflow projections to be made. In our experience this method has resulted in forecast accuracy of an average of 90 percent to 95 percent. We consider this an especially successful outcome given the extreme dynamics of the business and the fact that this further includes forecasted sales.

Of course, once estimated, the model should be tested for robustness via standard auxiliary regressions (tests for autocorrelation, heteroscedasticity, normality and overall stability; for the latter we propose the Ramsey Reset test which works very well in OLS environment). These can further be supplemented with proper manipulation of the data beforehand such as logging of the data in order to smooth it and alleviate calculations and conducting stationarity tests with each individual variable so as to exclude biased results.

Vasil Lyaskov is Financial and Business Analysis Expert at PIMK Holding Group in Plovdiv, Bulgaria.

Read the full version of this article in the September issue of Exchange.
Developing an Effective Predictive Analytics Capability
On June 26 - 27, discover how the application of predictive business analytics is relevant to all financial functions. Explore practical case examples and identify ideas and actions from leading businesses that have successfully implemented these analytical processes.
Learn More

Copyright © 2018 Association for Financial Professionals, Inc.
All rights reserved.