Now Available in Community - MBAS 2019 Presentation Videos
Catch the most popular sessions on demand and learn how Dynamics 365, Power BI, PowerApps, Microsoft Flow, and Excel are powering major transformations around the globe. | View Gallery
2019 release wave 2 Discover the latest updates to Dynamics 365Release overview guides and videos Release Plan | Early Access Availability
Ace your Dynamics 365 deployment with packaged services delivered by expert consultants. | Explore service offerings
Connect with the ISV success team on the latest roadmap, developer tool for AppSource certification, and ISV community engagements | ISV self-service portal
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance and Operations TechTalks | Customer Engagement TechTalks | Talent TechTalks
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.
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:
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:
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:
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.
Business Applications communities