Greetings!
I have a critical scenario with one of our Client. Critical because he has auditors and failure to provide a proper report has a disastrous effect, even throwing out the system.
The Problem:
The Customer - Trial Balance (CTB) report totals do not agree (reconcile) with the figure on the receivables account in the General Ledger (Trial balance). Specifically, the figures on TB under the Debit Amount, Credit Amount and Balance are less than their corresponding figures on the CTB.
There are several receivables categories but all others agree, except one, let’s refer to it as X. X has a big number of foreign currency transactions as compared to others. In a few cases, they cannot be applied as the invoice and payments are in different currencies. The batch job for adjusting foreign currency rates has been run as necessary. I can obviously see many Realised and Unrealised Gains/Lossess posted in there.
Someone help me salvage the situation as soon as possible, actually I have less than twelve hours.
Investigation:
The way NAV works with customer ledger entries and detailed customer ledger entries seems to be the cause. I can see that the Customer - Det. Trial Bal. report also has a similar scenario where it adds a line for Adj. balance when you take the report for certain periods. This too is not clearly explain and the finance team has chosen to print a full report since that puts away the “wiered” adjustment.
I can see that this adjustment works with the “Net Change LCY” data and attempts to eliminate Customer Ledger Entries (CLE) of type “Application”, and Detailed Customer Ledger Entries (DCLE) of Type “Correction of Remaining Amount” and “Appln. amount Rounding”. Lets denote the respective amounts as CLEAmt, DCLEAmt. A start balance is calculated as the Net Change amount on the start date, let’s denot it as startBalance. The Period balance is calculated from Net Change using the entire period range specified; let denote this as periodBalance. So the Customer Balance is the derived as
CustomerBalance = startBalance + (periodBalance-CLEAmt-DCLEAmt).
So at the end of the day, it is expected that the individual customer balances should add up to the same outstding balance in the G/L but this is nto the case.
Waiting for the gurus…
Thanks, Robert