Cash flow forecast Opening Balance calculation in SAP B1

[kingastrong]

Hello

We are trying to understand Cash flow forecast in SAP B1

Can you please explain me how is calculated the opening balance in Cash flow report of A/R Invoice , journal entries , outgoing payment ?

We see big amounts , also incomaming balance is in negative , outgoing in positives ..?

[former_member756849]

Do you get any clue for how opening balance amount calculated on object incoming payment and manual journal entries?

[DavidAndrusko]

Dear Samba and Andita,

The Cash Flow calculation is based on the due date defined in the transactions to be considered. If the due date of the transaction does not fall in the Opening Balance period, then the amount will not be considered for the Opening Balance.

As for Incoming Payments, I have the query for CF dashboard only. I believe it should be similar for the report as well. It should calculate it as follows:

SELECT SUM(IFNULL(T0."BalDueDeb",0)-IFNULL(T0."BalDueCred",0)) AS "OpenBalance",
T0."TransType" FROM "JDT1" T0 INNER JOIN "OCRD" T1 ON T0."ShortName" = T1."CardCode"
WHERE T0."DueDate"<'YYYYMMDD' ---- Due Date
AND T0."Account" <> T0."ShortName" AND T1."CardType" IN ('C','S') AND
T0."TransType"  = 24 GROUP BY T0."TransType";

for manual journal entries, the condition should be similar, like:

... AND T0."TransType" = 30 AND (T0."BalDueDeb" - T0."BalDueCred" <> 0) GROUP BY T0."TransType" , T0."TransId";

You may as well consult 2043036 - Sales and Purchase Orders Not Included in Calculation of Opening Balance in Cash Flow Repo... . As for Sales Orders and cancellation date (defined in online help as: Specify the last date on which the customer accepts the goods. The default value is 30 days after the Delivery Date.), note that the system does not consider the current month, and it will consider only the documents if the cancelation date is in the current month.

Hope you find this useful.

Kind regards,

David Andrusko
SAP Business One Support