Customer - Trial Balance vs GL Trial Balance

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

Robert, your problems begin exactly here - as THIS is not correct, you CAN freely apply Invoices/Payments in different currencies (check in Sales and Purchases setup - there is setting Apll.between currencies - set it to All).

If you haven’t applied them, they stay with Open=True, that is a signal to Adj.Exch.Rates batch job these lines should be included in adjusment, thus causing unneeded fluctuation on actually long ago paid debts.

If you apply them now, the differences caused by excess adjustments going on & on [added] in rates will be eliminated, too.

And please don’t cross post…

also have a look at whether or not NAV allows direct posting to the G/L accounts.

My apology for cross posting.

I was in deep heat and was not very certain where my solution would come fastest. I will definitely not do this in future, and I will also visit the posting guidelines again.

Your reply is very helpful. I am working out on all open entries and should be able to get my position right soon. I had already checked that there were no direct postings in GL.

Thanks again,

Robert

Dear Modris,

I have a similar or rather exactly the same problem. Can I get some help here. My Customer Trial Balance and Detailed Cust. TB for does not tally with the receivables account ( the GL trial balance for the receivables account ). Your help is highly appreciated.