The cash flow in a business is a good indicator of its financial solvency and can reveal whether the business can meet its financial obligations. Microsoft Dynamics 365 Business Central provides tools that businesses can use to analyze their cash flow by analyzing historic data to make predictions for future periods. In this article we’ll look at the Cash Flow Forecast feature, which is an advanced reporting tool that can use Azure Machine Learning to model various scenarios and give you insights into what to expect.

Calculating a basic forecast

A basic cash flow forecast is based on known facts. First, it determines the current state of your liquid funds. Typically, this data comes from balances in bank and cash accounts, but it can also include other accounts.

Then it adds or removes cash amounts based on payments that you know you will make or receive in the future. This data comes from open entries in the customer ledger and vendor ledger, such as posted sales and purchase invoices, which also contain the due dates for the payments.

Though ledger entries are reliable, they’re also a restriction in terms of forecasting because they have due dates based on payment terms, which most businesses use. Due dates limit the horizon, or how far into the future you can forecast. For example, if the payment terms for sales invoices is 1W (one week), the forecast doesn’t have any data for eight days from the current date.

One way to extend the horizon for the forecast is to manually enter budgeted numbers based on known agreements with vendors and customers. For example, by including adjustments for sales, purchase, and service orders, and revenue from jobs. Although there’s a risk that these get canceled or their due date is moved, they’re still good input.

You can manually enter budgeted amounts, for example, on the following pages:

  • G/L Budgets, where you can specify various dates.
  • Cash Flow Manual Expenses for things like rent, internet, and cell phones.
  • Cash Flow Manual Revenue for Interest or return of loan

Using Azure Machine Learning to Extend the Horizon for Cash Flow Forecasts

The budgeting tools are great for registering expenses and revenue. However, they aren’t terribly practical for extending the horizon of a cash flow forecast because of the amount of manual work they require.

Instead, you can use Azure Machine Learning (ML) from Cortana Intelligence to extend the horizon for your cash flow forecasts. Business Central can use models that deal with time series data to calculate forecasts:

  • ARIMA (AutoRegressive Integrated Moving Average)
  • ETS (Exponential Smoothing State Space)
  • STL (Seasonal Decomposition of Time Series by Loess)
  • TBATS (Exponential Smoothing State Space with Box-Cox transformation, ARMA errors, Trend and Seasonal components)

You can also use some of these models in combination, such as ETS + ARIMA and ETS + STL, which in some cases can improve the accuracy of forecast values. For more information, see the Azure AI Gallery.

Like basic cash flow forecasts, these models rely on historical data, including:

  • Customer ledger entries for receivables
  • Vendor ledger entries for payables
  • Tax/VAT entries for taxes

What they do differently is apply advanced statistical methods to the historical data to generate data in the future and include that data when calculating the forecast. When we predict receivables or payables, we use the due date as a date field and not the document date or posting date, so we don’t need to define and apply a payment term to the predicted results.

You can specify the model to use in the Time Series Model field on the Cash Flow Setup page. If you’re unsure about which model to choose, you can choose the All option and Business Central will use each model to calculate a forecast, compare the results, and return the best result. For more information about these models, here’s a link to an online publication on the subject.

Note: Azure ML is free in Business Central, you just need to turn it on. There is, however, a limit to the amount of compute time you can use every month. Choosing All will use more of that time than a single model would. When you hit the limit, you will need to wait until the next month or switch to your own subscription.

Notice that the forecasted value is a range that the value is expected to be within, with some probability, and not a single datapoint. The default probability is 80%, it’s not possible to change this setting via UI.

If you have data for at least two years you can forecast values up to four months ahead with reasonable quality. If you have less data than that you should use a shorter horizon.

Otherwise the range of predicted values can be too wide and therefore useless. There is a very limited help wit prediction that can be wrong 100%. That’s why we introduced threshold, that you can adjust in the Cash Flow Forecast setup. The field is called “Variance %” and allows you to specify range of deviation, plus or minus, that you’ll accept in forecast. Lover percentage represents more accurate forecasts, and are typically between 20 and 40. Forecasts outside the range are considered inaccurate, and are ignored.

Because we predict starting from the current date, we need to align predicted values with values already registered in the system, such as open customer ledger entries or sales orders. Here’s a simplified case that illustrates the logic.

For the last two years you have sold 10 pencils each month. Now, in the middle of the month, someone asks you, “How many pencils will you sell next month?” Based on the history, you’re answer would probably be 10.

Now, a customer arrives at your shop and wants to order six pencils next month. Does this order change what you expect to sell next month? Probably not. You still expect to sell 10 pencils in total, but you now know that six of them will go to that customer.

The same is true for revenue and expenses. If you have reliable information about revenue in an upcoming period, for example, an order is placed, then that amount becomes actual and should be removed from the predicted revenue.

However, if someone comes to your shop and orders 15 pencils for next month, the amount is larger than the 10 you expected so your forecast is adjusted from 10 to 15.

I hope this article has provided some clarity about how cash flow forecasts in Business Central work, and how using time series can improve your confidence in the future, at least about the receivables, payables, and tax components of your cash flow. You can learn more about cash flow forecasts in the online help: https://docs.microsoft.com/en-us/dynamics365/business-central/finance-analyze-cash-flow

If you have ideas about how we can improve cash flow forecasts, or Business Central in general, we encourage you to share them with us on https://aka.ms/bcideas.