Cashflow Forecasting

Tehe Cashflow Forecasting feature and How it works

Written By Niall Conlon (Draft Writer)

Updated at June 6th, 2018

1.    Introduction:

Careful management of your cashflow is essential to managing your business effectively. Depending on your type of business and its stage of development, good cashflow management is key to raising investment and/or obtaining working capital finance and overdraft facilities from your bank. But it is also a key component for established companies as cashflow problems are usually the core reason businesses get into difficulty and eventually fail.

It is generally recommended that all businesses maintain an ongoing, monthly cashflow forecast for the current year, and even an annual forecast for the next 3-5 years based on an overall plan or budget for the business. However, more accurate forecasts should be possible on a weekly basis for the next month or two as often most of the transactions that will result in cashflow are already known at that stage.

Having accurate information on what is due to be received by the company and when and what needs to be paid by the company and when, is vital so that you can accurately assess your cash position and requirements over the next 2-3 months or even assess when you might be able to make investments in inventory or an asset you require.

The Cashflow Management module is designed to give you access to that information from the best source available and provide a tool with which you can manage the expected payment or receipt date for outstanding amounts to more accurately predict your cashflow.

This has the following main benefits:

    You will be able to more accurately specify times when your business may need additional funding or overdraft facilities.
>     Gives a clearer picture of how your business is doing and how it is likely to perform in the future from a cash generation perspective.
>     Irregular payers can be closely monitored and managed and if necessary refused credit in future.
>     Credit from suppliers (vendors) etc. can be optimised.
>     Easier to get invoice discounting type credit.
    Inconsistencies in performance can be identified, predicted and remedied.
>    The cash implications of major planned investments can be accurately assessed.

The Cashflow Forecasting Module will enable you to forecast the movement of cash in your bank accounts based on existing transactions held in the system, plus future and expected cash transactions that you may wish to include.

2.    Preparing the Initial Cashflow Forecast:

The cashflow forecast can be run for a user defined period, usually concentrating on the next 1-3 months and is based on pulling all details in the system that are expected to impact on cashflow into a model where they can then be manipulated to accurately forecast the cashflow over that period.

This is based on the following elements:

a)    Balances in selected bank account(s) (including credit card accounts).  Each account should ideally be reconciled using eBanking auto-reconcile facility so that any transactions hitting the bank account directly have been accounted for before the forecast is carried out. Each bank account is projected separately as each accounts receivable and accounts payable account is linked to a default bank account.

b)    Less all expected and forecasted payments:

1)    To suppliers (vendors) for outstanding transactions in the system based on average credit days taken for each supplier (vendor).

2)    Saved Purchase Orders based on the date the order is due plus the average credit days taken for the relevant supplier (vendor).

3)    Purchases, Overheads and Capital Expenditure held in template journals specifically affecting Bank accounts (e.g. Wages / Payroll and Salaries journal templates). The forecasted due date would be based on a recurrence pattern held against the relevant journal templates.

4)    Budgeted Purchases, Overheads and Capital expenditure based on the budget values held against any General Ledger accounts flagged for inclusion in cashflow forecasts.

c)    Plus all expected and forecasted receipts:

5)    From Debtors for outstanding invoices in the system based on average collection days against the specific customer accounts.

6)    Sales orders and quotes based on the date the order is due plus the average collection days for the relevant customer.

7)    Customer Direct debits and standing orders stored in the journal templates (e.g. maintenance contract income) based on a recurrence pattern held against the relevant journal templates.

8)    Other receipts from General Ledger accounts flagged for inclusion in the cashflow (e.g. proceeds from the sale of assets of the business or cash sales) based on the budget value for the specified period.

All these elements are extracted and brought into a cashflow model to arrive at a net balance position for each account for the selected date range.  The cashflow for each account is shown on a graph for the period specified.

The user can now manage this forecast by manipulating the above details to remove/add items and amend the projected payment or receipt date for any of the items listed.

3.    Running a Cashflow Forecast:

Within the Cash and Bank menu choose 'Cashflow Forecasting' to open the function.  

The initial cashflow forecast will be presented as outlined above in the following screen:

The screen is divided into the following main sections:

i)    Forecast Summary: Provides an overview of the forecast based on the date range and bank accounts selected for inclusion. The values are in base currency.

ii)    Forecast Chart: This provides a line graph illustrating the cash movement for the selected bank accounts(s) over the projected period. There is a separate line shown for each Bank Account selected and the chart series will scale based on the date range chosen. The chart legend displays the General Ledger code of the bank account(s) selected.

iii)    Bank A/Cs Tab: This tab lists all bank or credit card accounts setup in the General Ledger. The opening balance for each account (base currency) is shown, together with the forecasted movements based on the selected date range for the accounts which were selected for forecasting. You can select a bank for inclusion in the forecast by clicking the 'Include' flag.

iv)    Date Range: You can choose over what period you wish to run the forecast for (i.e. Weekly, Bi-weekly, Monthly, Bi-monthly, Quarterly, Annually, Semi-Annually). Choosing a set date range from the list will update the start date and end date fields appropriately. You can alter this date range by changing the start and end dates as you wish.  Note that you cannot select a date less than today's date - the forecast must always be for a period greater than or equal to today's date. Hence it is important to reconcile the accounts first so you are starting with the actual position.

v)    GL Setup: This option allows any General Ledger accounts to be selected for inclusion in the cashflow forecast, allowing cashflow items that do not originate in the Debtors, Creditors or Sales or Purchase orders to be added to the forecast.  The following screen is displayed:

The budget (P&L) or balance (B/S) for any selected General Ledger account (eg: payroll, pension, bank charges/interest, cash sales) can be included in the forecast for later manipulation.  As these items will not have an identifiable bank account associated with them the default bank account they should be forecast against should be selected.

These details will be used each time a Cashflow Forecast is run to extract details from the General Ledger.

How these items are treated in the Cashflow Forecast is dependent on what type of account it is as follows:

Balance Sheet Accounts: Balance Sheet Accounts (Assets, Liabilities and Equity), do not typically have a direct effect on your cashflow position. Therefore, journals recorded against these accounts are not included in the cashflow forecast, except to the extent that there is a Bank Account element to any template journals. However, some accounts, such as Tax liability account, can have a future effect on your cash position.  In addition, if you are considering liquidating some assets or paying off some loans you may wish to include these in the forecast to see what the effect would be.

By flagging a Balance Sheet account for inclusion, you are specifying that the balance of that account (to date) should be included in the cashflow forecast. Such balances are denoted by the internal reference of 'BAL-' followed by the Account Code in the forecast.

Profit & Loss Accounts: Profit & Loss Accounts (Revenues and Expenses) usually have a direct effect on your cashflow position (except for depreciation type accounts). Future dated journals against a Profit & Loss Account will be automatically included in the cashflow forecast should it fall within the date range.

Profit & Loss accounts have a different effect than Balance Sheet accounts. Instead of the account balance, budgets recorded against the account that fall within the date range will be included as a forecasted transaction in the cashflow. For example, a budget of 4,000 in December would appear as a transaction in the forecast that includes 31st December in its projection window. Such items are represented in the Cashflow Forecast by the internal reference of 'BUD-' followed by a numerical ID.

Generate Cashflow: This option creates or updates the Cashflow forecast. Choose 'Preserve Edits' to retain any updates you have made to transactions in the cashflow e.g changing the forecasted payment date (see below). Choose 'Reset Edits' to re-generate the cashflow from scratch losing any of the edits made. All the data is stored in the system based on your last updates to the forecast. You can choose to re-run the forecast to refresh it at any point.

Export: Having reviewed the transactions that make up the forecast, you can export the resulting data to Excel or PDF to allow further analysis or reporting if required. As the Cashflow Forecast is a dynamic tool you may wish to export at specific times to retain a historical copy of the cashflow forecast over time, if required.

4.    Reviewing and Manipulating the Forecast:

Once the cashflow forecast has been generated, the system displays the extracted transactions and elements in 4 separate tabs.  The following types of transactions are used in generating the cashflow forecast:

Sales Ledger: Sales Orders (Processed, part and fully delivered), Unallocated Sales Invoices (Processed and posted), Recurring Sales Invoices (forecasted out to the end of the selected date range). Unallocated Sales Receipts are also shown as reductions in amount due to specific Debtor.  

Purchases Ledger: Purchase Orders (Processed, part and fully delivered), Unallocated Purchase Invoices (Processed and posted) and Unallocated Purchase Payments shown as a reduction in amounts due to the specific Creditor.

Journal Templates: General or Bank containing a line that debits or credits a Bank account will be included and forecasted out to the end of the projection range should the recurrence field be set .

General Ledger: As outlined above, for selected GL accounts, budgets recorded against Profit & Loss GL Accounts and the balance to date of Balance Sheet accounts that are flagged to be included in the cashflow forecast.

The 4 different tabs show the details of the forecast as follows:

a)    All Transactions:

This displays all outstanding transactions and GL elements that can effect your cashflow in the following screen:

The following functionality can be used in this screen:

i)    By default, each line is flagged for inclusion in the forecast, you can choose to include or exclude transactions as you wish by ticking the Include on/off button. You can move transactions in and out of the forecast range by editing the date on the associated line. To do this, just click Edit and change the Due Date accordingly. To find specific transactions you can sort the transactions by column or filter by header column by entering the value you want to filter by under the relevant column heading.

ii)    You can choose to group by header column (eg: by account) which produces sub-totals for each group.  Click on the relevant column heading and drag it to the section above the column heading to sort by that column.

iii)    For individual transactions listed you can drill down to the underlying transaction details and associated account using the hyperlinks provided per line under Account and Int Ref columns. Notes can be maintained once linked to the customer/supplier account. 

iv)    The chart and summary will automatically update to reflect the new cashflow position after your edits.

v)    It is also possible to edit some details against the transaction within the cashflow. Click on Delete to remove a specific item from the forecast. Click on the 'Edit' button and this will open up the line in edit mode at the bottom of the screen. You can change the value of the forecasted transaction, the bank account associated with it and the forecasted date.

NB: Changing the value of the transaction only changes it in the forecast - it does not change the actual value of the transaction as originally recorded in the system.

By editing the transaction in this way, you can move it into different periods in the forecast or alter its value (eg: part payment) to see the overall affect on the cashflow.  When you are finished editing, click 'Update' to save these changes or click 'Cancel' to ignore them.

b) Expected/Forecasted Payments:

The Expected/Forecasted Payments tab is similar to All Transactions but displays only payments that are forecast to be made in the date range specified below. To make changes to these payments just click on 'Edit'.

Similar to the 'All Transactions' tab above you can filter, sort and group transactions within the grid as you wish. There are also editing options provided as described above. Selecting/deleting/editing transactions will automatically update the summary forecast and chart tool.

c)  Expected/Forecasted Receipts:

The Expected/Forecasted Receipts tab displays only receipts that are forecast to be received in the date range specified. To make changes to these receipts just click on 'Edit'.

Similar to the 'All Transactions' tab above you can filter, sort and group transactions within the grid as you wish. There are also editing options provided as described above. Selecting/deleting/editing transactions will automatically update the summary forecast and chart tool.

Source Data for Forecasting:

A key element of the Cashflow forecast is the method by which the system calculates the expected receipt date for each outstanding debtor (monies in) or payment date for each outstanding creditor transaction (monies out). 

This is calculated based on the average (historical) collection period taken for payment and is automatically maintained by the system for each customer and supplier (vendor) account record (first tab) and used when projecting the cashflow.

The Average Pay Days is updated after each payment (supplier payment or customer receipt) and is a Weighted Average value based on the transactions the payment is allocated against. It is calculated as follows:  Sum of (Payment Date – Invoice Date) * Amount Allocated for Each Invoice in this Payment/ Total Historical Payments over the Last X Months.  X can be maintained at company level and represents the period over which the weighted average collection period is calculated.

5.    Maintaining the GL for Non-Creditors or Non-Debtors Cashflows:

There are a number of tasks that you should carry out to get the best out of the function so the Cashflow Forecast will pick up regular transactions that do not originate in the Debtors/Creditors Ledger:

i)    Set up General Ledger Journals for recurring journals such as payroll and standing orders/direct debits. You can indicate how often these re-occur (i.e. weekly, monthly etc) and the day in the month (i.e. day 28 of the month) when they are typically processed. 

ii)    Create monthly Budgets for Profit and Loss accounts that can be used in the forecast to cover budgeted overheads, future sales income etc.

iii)    Create Budgets for any Balance Sheet accounts to include future capital expenditure, loan repayments etc so these can be included in the forecast.

iv)    Flag the particular GL accounts that you wish to include in the cashflow forecast.

6.    Completing the Forecast:

Having reviewed the transactions that make up the forecast, you can export the data to Excel or PDF to complete your cashflow forecast.

It is recommended that you do this to retain a historical copy of the cashflow forecast over time.

Note that all the data is stored in the system based on your last updates to the forecast. You can choose to re-run the forecast to refresh it at any point.