Cashflow Technical Notes

Overview

This report provides an 8-week forward-looking cash flow projection, broken down by week. It offers full visibility into actual and expected inflows and outflows, categorised by transaction type and includes a running bank balance, overdraft usage, and cash available tracking. This helps finance teams plan liquidity, manage payment priorities and anticipate cash constraints.

Report Requirements - Detailed Cashflow Report

• Generate a weekly cash flow table for a specified 8-week rolling window.

• Include both inflows and outflows by detailed category.

• Calculate the running anticipated Closing Bank Balance.

• Track Overdraft Available and remaining facility.

• Highlight Cash Available based on the available overdraft limit.

Technical Spec

• Create a custom table. Stored procedures to update the custom table.

• Data pulled from:

  • Accounts Receivable (outstanding invoices)

  • Accounts Payable (outstanding invoices)

  •  Active Sales and Purchase Orders minus invoiced.

  • Banking

  • Overdraft

  • Expenses

  • Other Income

  • VAT Recon Module

  • Loan Accounts

• Display format: Tabular with weeks on the X-axis and data/summaries on the Y-axis.

• Export options: Excel (.xlsx), PDF

• Outflows must be inserted as a minus.

• For values, select local currency fields. There is an option to use the current Rate of Exchange as per the Currency Setting in QE, or to use the Rate of Exchange on the transaction.

Filters

     ■ Current Date (fixed filter)

Fields - X Axis

• Inflow/Outflow: Specify whether the line item is inflow or outflow.

• Key ID: Summary fields are assigned a minus, and data fields are assigned a positive.

• Group: Type of inflow/outflow.

• Description: Transaction or account name.

• Company: For supplier and customer transactions, specify the company name.

• W1 to W8:  Values of the line items (Y axis) in a week period. Specify the actual dates from Monday to Sunday (e.g. W1 = 21 - 27 July).

Groups

• Opening Balance - W1 balance = Actual bank balance as per Ledger (as at the current date). W2 - W8 = Carries over from the previous week’s closing bank balance. If multiple bank accounts exist, sum the opening balance of all accounts. 

• Description - Bank Name & Account Number. If multiple bank accounts exist, add all bank names, separated by a comma.

• Debtors - List each outstanding amount on customer invoices. Use the Transaction Due Date to place the value in the relevant period (W1 - W8). 

  • Description - Transaction Code & Number + Transaction Reference (e.g. CI 2938 - Pamphlets).

• Active Orders - List each Active (group) Sales Order minus what has been invoiced (search for linked customer invoices). Use the Transaction Due Date + Customer Terms (e.g. 30 days) to place the value in the relevant period (W1 - W8). 

  • Description - Transaction Code & Number + Reference (e.g. SO 3938 - Pamphlets).

• Creditors - List each outstanding amount on the supplier invoice. Use the Transaction Due Date to place the value in the relevant period (W1 - W8). 

  • Description - Transaction Code & Number + Transaction Reference (e.g. CI 2938 - Pamphlets).

• Active Orders - List each Active (group) Purchase Order minus what has been invoiced (search for linked supplier invoices). Use the Transaction Due Date + Supplier Terms (e.g. 30 days) to place the value in the relevant period (W1 - W8). 

  • Description - Transaction Code & Number + Reference (e.g. PO 27938 - Pamphlets).

• Cashflow Transaction Module

  • Data to be pulled from Active Cashflow transaction/s to the report. There are 4x Item Categories, namely Ad Hoc, Weekly, Monthly & Overdraft. 

• Ad Hoc - Inflow or outflow items (depending on whether the value is a positive or negative) where an exact date (dd/mm/yyyy) is selected as the Forecast Date and pulled through to the relevant period on the Cashflow report.

• Weekly - Inflow or outflow items (depending on whether the value is a positive or negative) that occur weekly. No Forecast Date is selected and is pulled through to every week on the Cashflow report.

• Monthly - Inflow or outflow items (depending on whether the value is a positive or negative) where the day of the month (e.g. Due on the 1st of every month) is selected and pulled through to the relevant period on the Cashflow report on a monthly basis (i.e. it should pull through on the every month in the period where the 1st lies as an example). The month and year selected in the Forecast Date are irrelevant.

• Overdraft - The value of the overdraft/s is captured per bank on the transaction and is summed together on the report. No Forecast Date is selected and is pulled through to every week on the Cashflow report.

  • Item Groups on the Cashflow transaction are used to determine the type of inflow or outflow (e.g. Loan Accounts, Expenses, Other Income, Overdraft, etc). This field will be used on the Cashflow report to group by the field to get a summary of the report.

  • Pull the Item Description as is from the Cashflow Transaction.

• VAT Due - Values to be pulled from the VAT Reconciliation module (Local currency). Use the Transaction Date periods to determine when the VAT is payable. VAT is payable by the 25th in the following month (e.g. VAT Control 01/08/2025 - 31/08/2025. The value will appear in the week where the 25th of September lies. Once a VAT Payment is created in the same month (Sep), the value falls away on the report.

  • Description - ‘VAT Control’

• Net Cash Movement - Total inflows minus total outflows

• Closing Bank Balance - Opening Bank Balance + Net Cash Movement

• Overdraft Available - Pull from the Cashflow Transaction where Item Category = Overdraft. Sum all values from banks together in local currency. List all the bank names in the Description.

• Cash Available - Closing Bank Balance + Overdraft

Cashflow Functionality Setups

1. Follow the steps as outlined in the tutorial. 

2.  Log onto the server to upload the report. 

3. Run this script to create the custom table and the required stored procedures

   1.  First browse the script and note where various Transaction Type IDs and Category IDs are required. These IDs are in the database you are adding the report to.

   2. Create the report as a .exe

      1.  SQL: SELECT * FROM CTM_GEN_CF(0)

         1. The 0 indicates that the ROE is taken from the transaction and not the currency ROE setting. 

         2. Using the databases currency rate will be implemented in phase 2. We will also ensure that transactions linked to a CI or SI are taken into account. This is implicitly done with the 0 option already. 

      2. Name: Cashflow Report (8 Weeks)

      3. Report Description: 

This report provides a detailed and dynamic projection of your company's liquidity over the next eight weeks. It is generated in real-time using the latest available data. Its primary purpose is to help you anticipate future cash positions thereby enabling proactive financial planning and decision-making.

The forecast starts with the actual Opening Bank Balance, calculated by summing the balances of all active bank accounts in local currency at the start of the period. It then models all anticipated cash movements by consolidating data from across the system:

 Cash Inflows (money coming in) are projected from: 

- Expected payments from outstanding Customer Invoices (Debtors), based on their Payment Dates. 

- Projected income from Active Sales Orders that have not yet been invoiced. The expected payment date is precisely calculated by taking the order's Due Date and adding the specific customer's Payment Terms (e.g. 30 days from statement or invoice date).

 - Scheduled VAT refunds. The expected refund date is calculated as the 25th of the month following the VAT period's end date. The report automatically detects any refunds already received and will only show the outstanding amount due to you.

 - Any manually entered future income, such as from loans or other sources, captured in the Cashflow Transaction module. 

Cash Outflows (money going out) are projected from: 

- Scheduled payments for outstanding Supplier Invoices (Creditors), based on their Payment Dates.

 - Projected payments for Active Purchase Orders that have not yet been billed. The expected payment date is precisely calculated by taking the order's Due Date and adding the specific supplier's Payment Terms (e.g. 60 days from statement or invoice date). 

- Upcoming VAT payments. The due date is calculated as the 25th of the month following the VAT period's end date. The report automatically detects any VAT Payments already made against a VAT period and will only show the remaining outstanding amount due. 

- Any manually entered future expenses or loan repayments from the Cashflow Transaction module. 

After consolidating these detailed transactions, the report calculates a running total for each week. The final summary section provides a clear overview of your financial position, including the Net Cash Movement, the Bank's Closing Bank Balance, your total Overdraft Facility across all banks in local currency, and the final Cash Available (your projected closing balance plus your overdraft limit). Ultimately, this report serves as a critical tool for managing working capital, identifying potential cash shortfalls, and ensuring the financial health of the business.

3. 2. 4.  Drilldown: 

         1. Transaction: TRANS_ID_OUT 

         2. Customer: COMPANY_ID_OUT 

         3.  Supplier: COMPANY_ID_OUT 

      5. Caption (Fieldname): Select WEEK1_CAPTION_OUT for WEEK1 VALUE and so on. 

      6.  Colour (Fieldname):  Select COLOUR_OUT for WEEK 1 to 8 VALUES

      7.  Set to two decimal places for numbers

      8. Only select the relevant Visible columns (INFLOW_OUTFLOW, REPORT_GROUP, REPORT_DESCRIPTION, COMPANY_NAME_OUT, WEEK1_VALUE to WEEK8_VALUE) 

      9.  To add the summary report, simply copy the report created above and update the name to Cashflow Report (Summary).

         1.  SQL: SELECT * FROM CTM_GEN_CF_SUMMARY(0). 

      10. Set user access to the report.

      11.  Add the Cashflow Reports module to the Menu under Accounting named Cashflow Report (Detailed) and Cashflow Report (Summary).