Cash Flow Forecasting - The Definitive Guide

What is Cash Flow?

Cash flow represents the movement of funds in and out of the Company and is an indicator of the Company’s financial solvency, meaning that it can reveal whether or not the Company can meet its financial obligations.

Cash Flow Forecasting

In Microsoft Dynamics 365 Business Central, cash flow forecasting generates predictions of a company's cash flow.

Reporting and Analyzing Cash Flow

• Statistical - Point in time analysis

  • Search for "Bank accounts" will show the balance in accounts at a given date.

  • Numbers are drillable, so users can see the activity.

  • Users are able to link an online bank account, which will pull in daily balances.

• Statement of Cashflow - Shows a historical view of cash flow.

• Account Schedules & Charts – Out of the box, users can generate a few different cash forecasting reports in Account Schedules.

  • I_CACYCLE – Contains data for the cash cycle report.

  • I_CASHFLOW – Contains data for the CF Chart.

    • Shows same info as the Statement of Cash Flows report, but users are able to drill into the numbers.

  • Custom Account Schedules – Users can create custom Account Schedules.

Cash Flow Forecasting Sources

• Receivables – Based on Due Date & Amount fields of the Customer Ledger Entries.

• Payables – Based on Due Date & Amount fields of the Vendor Ledger Entries.

• Liquid funds - Balance in bank Accounts

• Manual Expenses / Revenues – Create for Rent, Payroll, Debt Payments, etc.

• Sales / Purchase Orders

• Fixed Asset Budgets / Disposals

• Service orders

• G/L budgets

• Jobs

• Taxes – Based on Document Date and Amount fields on the VAT (Tax) Ledger Entries.

• Cortana Intelligence

  • Cash flow forecasts can use AI to include documents with a due date in the future. The result is a more comprehensive forecast. The connection is already set up and simply needs to be enabled.

  • Note – you can create and use your own predictive web service based online, in the Azure AI Gallery.

---

It's a data driven world.

Make sense of the numbers with Power BI.

---

Summary of Steps to Set Up the Cash Flow Forecast

• STEP 1 – Create Cash Flow Payment Terms, if desired.

• STEP 2 - USE ASSISTED SETUP:

  • STEP 2.1 – Set up Chart of Cash Flow Accounts.

  • STEP 2.2 – Cash Flow Setup (use assisted setup and then adjust manually, as needed).

    • Links Cash Flow entities to your Cash Flow Chart of Accounts.

    • Defines your period (weeks, months, days, etc.).

    • It is here that users can enable Cortana AI.

  • STEP 2.3 – Create a Cash Flow Forecast

    • This will set up a DEFAULT Cash Flow Forecast.

    • Users can create as many Cash Flow Forecasts as desired.

    • Users can define which Cash Flow Forecasts to show in the role center chart.

• STEP 3 – Create Manual Revenue and Expense

• STEP 4 – Populating the Cash Flow Forecast

  • From the Cash Flow Forecast

    • Actions – Cash Flow Worksheet

    • Process – Suggest Worksheet Lines

      • Here, users can revise lines, delete lines, etc.

    • Click Register – this gives you a new Cash Flow Forecast by calculating Cash Flow Entries.

      • This refreshes the stored cash flow entries so that users can run reports.

  • Users can run this as often as desired.

    • Best practice - make it a part of the month-end process and then refresh the Cash Flow Forecast for the next 30 days.

• STEP 5 – Analyzing the Cash Flow Forecast

  • Cash Flow Chart - Review this in the user's role center.

  • Users can drill into the bars on the chart to view details.

  • Ex. If there is a spike in Payables where cash will fall to the negative, users can take action such as reaching out to the vendor for an extension, borrowing from a LOC, etc.

• Cash Flow Forecast– Statistics – Users can drill on any of the numbers

• Cash Flow Forecast– CF Availability by period.

  • Provides a comprehensive flow of cash flow impacts.

  • All the way to the right of the screen, the Total cash position expected is displayed.

Step 1: Set Up Cash Flow Payment Terms

In addition to the standard payment terms of customers and vendors, users can specify terms which use a different formula for the cash flow forecast. The Cash Flow Payment Terms Code field is available on both the customer and vendor cards. For example, if a customer has terms of 10 days, yet typically pays in 20, users may enter the 20D term in this field.

To set up cash flow payment terms for a customer:

1. Search customers and open the relevant customer card.

2. In the Payments FastTab, fill in the Cash Flow Payment Terms Code.

Step 2: Using Assisted Setup for Cash Flow Forecasting

• Search for “Assisted Setup” -> Click “Set Up Cash Flow Forecast”.

• Accounts - System suggests the cash accounts to base the forecast on, but allows users to select a range.

• Frequency - How often would you like us to update your cash flow forecast?

  • Specifies if and how often the Cash Flow Forecast in the role center chart is updated

    • Never, Daily, Weekly

• Enable Cortana Intelligence

  • Machine Learning from Azure analyzes expense patterns from the past.

  • Includes documents with a due date in the future.

  • Enable this feature for a more comprehensive prediction.

  • Note - if the system has 4 or fewer months of data, users may not be able to utilize this feature until more data is available, but can still enable at this time.

• Taxes

  • Identify how often the company pays taxes (monthly, quarterly, accounting period, yearly).

  • Identify the payment window for paying your taxes.

    • For example, if your payment window is 20 days -> 20D.

  • Identify which type of account taxes are paid from (Tax GL Account, Vendor, etc.).

Chart of Cash Flow Accounts

As a result of the Assisted Setup, a Cash Flow Chart of Accounts (COA) is created. This can also be created manually.

• This is where forecast entries are registered and stored.

• These accounts can be set up following your high-level financial COA.

• Ensure there is at least one account for each source (Receivables, Payables, etc.)

• When users drill into a Cash Flow COA account, they can ensure the source type is set up correctly.

• Ex. Below for Payables – the source type tells the system to look at the Payables subledger.

Standard Cash Flow Chart of Accounts created via Assisted Setup are below:

• CASH FLOW MANUAL EXP

  • Source Type – Cash Flow Manual Expenses

• CASH FLOW MANUAL REV

  • Source Type – Cash Flow Manual Revenues

• FIXED ASSETS BUDGET

  • Source Type – Fixed Assets Budget

• FIXED ASSET DIPOSAL

  • Source Type – Fixed Assets Disposal

• JOB

  • Source Type – Job

• LIQUID FUNDS

  • Source Type – Liquid Funds

  • G/L Integration – Balance

  • G/L Account Filter – 10100..10300

• PAYABLES

  • Source Type - Payables

• PURCHASE ORDERS

  • Source Type – Purchase Orders

• RECEIVABLES

  • Source Type – Receivables

• SALES ORDERS

  • Source Type – Sales Orders

• SERVICE ORDERS

  • Source Type – Service Orders

• TAX

  • Source Type – Tax

These accounts can be modified or a new account can be added:

• Search for "Chart of Cash Flow Accounts"

• In the Account Type field, select one of the following options:

  • Entry -> posting accounts

  • Heading

  • Total

  • Begin-Total

  • End-Total

• In the Source Type field, select a source.

  • Note - The G/L integration is only relevant when a cash flow account is linked to the general ledger. Options for G/L integration are below:

    • Balance - The current balance of the GL accounts used in the cash flow forecast calculation. This option is most often used for liquid funds.

    • Budget - The budgeted entries of the GL used in the cash flow forecast calculation.

    • Both - Includes both the balance and budget options.

• The G/L Account Filter field should be specified if the source is being linked to a G/L account. Enter the general ledger accounts from where the balance and/or the budget entries should be derived.

Configure the Cash Flow Setup page

After Assisted Setup is completed, basic cash flow configurations are set. On the Cash Flow Setup page, you can adjust the following information:

• Automatic Update Frequency - If and when the Cash Flow is to be refreshed.

• Accounts - Users can specify which accounts must be used for the entries in the sales, purchases, fixed assets, jobs, and tax areas.

• Cash Flow Forecast No. Series - Specifies the number series that is used in cash flow forecasts.

• Taxable Period

• Tax Payment Window

• Tax Bal. Account Type - Vendor or G/L Account.

• Tax Bal. Account No. - Specifies the balancing account that your taxes are paid to. This field is only available when the Tax Bal. Account Type field is set to G/L Account.

• Azure AI - On the Cortana Intelligence FastTab, users can configure the connection to Cortana Intelligence.

  • The module uses AI to look at historical trends with respect to Cash Flow and makes predictions of ending cash flow.

  • If the company has data for at least two years, you can forecast values up to four months ahead. If less data exists than two years, you should use a shorter horizon.

• Historical Periods – specifies the number of prior periods to include in the forecast.

• Horizon – specifies how many future periods the forecast should cover.

• Time Series Models

  • ARIMA (Autoregressive Integrated Moving Average) - is an acronym that stands for AutoRegressive Integrated Moving Average. It is a generalization of the simpler AutoRegressive Moving Average and adds the notion of integration.

  • ETS (Exponential Smoothing State Space) - The ETS model is a time series univariate forecasting method; its use focuses on trend and seasonal components. The data used are air temperature, dew point, sea level pressure, station pressure, visibility, wind speed, and sea surface temperature from January 2006 to December 2016.

  • STL (Seasonal Decomposition of Time Series by Loss) - is a versatile and robust method for decomposing time series. STL is an acronym for “Seasonal and Trend decomposition using Loess,” while Loess is a method for estimating nonlinear relationships.

  • ETS+ARIMA (returns average as result)

  • ETS+STL (returns average as result)

  • ALL - If you choose ALL the model compares the results and returns the one that has the lowest mean absolute percentage error (MAPE).

    • While Azure Machine Learning is free in Business Central, you will have a limit to the amount of compute time you can use every month. Selecting the All option will use more of that time than a single model would. When you have reached the limit, you'll need to wait until the next month or switch to your own subscription.

  • TBATS (Exponential Smoothing State Space with Box-Cox transformation, ARMA errors, Trend and Seasonal components) - Time-series forecasting refers to the use of a model to predict future values based on previously observed values. TBATS is a forecasting method to model time series data and forecast time series with complex seasonal patterns using exponential smoothing.

• Variance % - Specifies the range of deviation, plus or minus, that you'll accept in the forecast. Lower percentages represent more accurate forecasts and are typically between 20 and 40. Forecasts outside the range are considered inaccurate, and do not display.

Create a Cash Flow Forecast

• The Assisted Setup will create a "DEFAULT" Cash Flow Forecast, but users can edit and create as many Cash Flow Forecasts as desired (ex. one forecast includes discounts and another without).

• To calculate the forecasted cash flow, at least one Cash Flow Forecast card is required.

• Search “Cash Flow Forecasts”.

• No – Ex. CF100001 Cash Flow January.

• Description 1 & 2 – Users can add further definition here to identify the forecast.

• Consider Discounts - Specifies if you want to include the cash discounts from transaction headers that are assigned in entries and documents in cash flow forecast.

• Consider Pmt. Disc. Tol. Date – Only enable this if Consider Discounts is turned on.

  • If the check box is cleared, the due date or payment discount date from the customer and vendor ledger entries and the sales order or purchase order are used.

  • Ex. If missed deadline to pay, but only missed it by 2 days, system can still give the discount.

• Consider Pmt. Tol. Amount

  • If the check box is cleared, the amount without any payment tolerance amount from the customer and vendor ledger entries are used.

  • Ex. If discount was $50 but $55 was taken and the tolerance was $5, will write-off the $5.

• Consider CF Payment Terms

  • Specifies if you want to use cash flow payment terms for cash flow forecast.

  • Cash flow payment terms overrule standard payment terms defined for customers, vendors, and orders (including manually entered payment terms).

• Show Chart in Role Center– only one forecast can have this checked.

• Search Name – Keep this the same as the Description field.

• G/L Budget From – Specifies the start date from which to use budget values.

• G/L Budget To – Specifies the last date to which to use the budget values.

• Manual Payments from - Specifies a starting date from which manual payments (revenue and expense) should be included in cash flow forecast.

  • Note - if a wide range of dates is used (i.e. multiple years), the Cash Flow Chart will be difficult to analyze. We recommend 1 year or less.

• Manual Payments to - Specifies a starting date to which manual payments should be included in cash flow forecast (same note as above on range).

• Move Overdue Cash Flow Dates to Work Date - Specifies if change overdue dates should be moved to the current work date for the cash flow forecast.

  • Should be checked if users desire overdue balances to be included.

• Default G/L Budget Name - Specifies the general ledger budget to be used.

Step 3: Set Up Cash Flow Manual Expenses & Revenues

In addition to the amounts which calculate directly from the GL, Purchase Orders, Sales Orders, Assets, and Service areas, users can establish additional factors for manual revenue and expenses and plan these for a set time period so that they are used in the Cash Flow calculation. Examples below:

• Rental Income

• Interest from Financial Assets

• New Private Capital

• Salaries

• Interest on Loans

• Planned Investments

To ensure the Cash Flow Forecast includes Manual Revenue & Expenses, ensure that a date range is selected in the “Manual Payments From (and To)” fields in the Cash Flow Forecast Card.

• Careful not to go too far out – the resulting Cash Flow Forecast Chart will be too hard to read if too many periods are included.

To setup up, search for “Cash Flow Manual Expenses”

• Select New

• Description - Enter a description, i.e. “Salaries”

• Date – enter a date here which will be used for Cash Flow entries resulting from the manual expense.

  • Ex. If Payroll is bi-weekly, set two different manual expenses – one for the 15th, one for the 30th, etc.

• Amount - Enter the dollar value expected.

• Recurrence – Enter a date formula – ex. 1M for monthly expense. This can also be blank for one-time items

• End by – enter the last date for this manual expense (optional).

Manual revenues are set up in the same manner.

Once a manual revenue or expense item comes to fruition, users can remove it in the Cash Flow Worksheet or in the Manual Expense (or Revenue) pages.

Step 4: Populating the Cash Flow Forecast

• Navigate to the Cash Flow Forecast card to be run.

• Click “Process” then “Cash Flow Worksheet” (this pulls in open AP and AR, etc. into the model).

• Click “Process” then “Suggest Worksheet Lines”

• Select the forecast to run it for.

• Select which sources you would like to include

  • Users also have the ability to use AI -> Azure AI Forecast

• Click “OK”

• Users are presented with all of the cash inflows and outflows with amounts and dates.

  • From here, users can modify line dates, delete lines, and add lines.

  • Users can view overdue balances here, if selected to move these balances.

• Once finalized, click “Process - Register”

  • Provides a new Cash Flow Forecast by calculating Cash Flow Entries.

  • Data is saved in the Cash Flow Chart of Accounts.

  • Users can now run reports based on the updated forecast.

  • Note - The Cash Flow Worksheet will now appear blank.

Step 5: Analyzing the Cash Flow Forecast

• Account Schedules - Users can view standard reports, but can also create customized account schedules.

• Cash Flow Forecast Chart - This is now available in the User Role.

• CF Availability by Periods - To view the availability of each cash flow by period in an overview with periods in the rows and categories in the columns, search “Cash Flow Forecast”

  • Select More Options – Related – Cash Flow Forecast – CF Availability by Periods

• Cash Flow Date List Report

  • From the Cash Flow Forecast screen, click Reports – Cash Flow Date List

  • Enter options:

• Ex. 12 intervals and 1M length will show 12 months of forecast, as below:

• Statistics – can drill on any of the numbers

  • From Cash Flow Forecast screen, click Process – Statistics

Create Cash Flow Forecast Account Schedules

In order to view a Cash Flow Account Schedule, the system must have registered Cash Flow Entries.

Example Setup below:

Configure the Cash Flow Forecast Chart

• Only one forecast at a time can be displayed in the Chart on the role center.

• The chart is only available for some role centers, such as the Accountant role.

  • The chart is also available from the Intelligent Cloud Insights.

• Search for “Cash Flow Forecast” – open the card to display as a chart.

• In the General FastTab, enable the “Show in Chart on Role Center” field.

• Users can also toggle between daily, monthly, quarterly, annual, etc.

---

Learn More about Cash Flow Forecasting in Business Central

• Training Class: Forecast your cash flow in Dynamics 365 Business Central

• A Shot of Business Central and A Beer podcast segment: Cash Flow Forecasting with Cortana Intelligence in Business Central.