Customer - Trial Balance vs GL Trial Balance

Get our Free Newletter

Don't you have the time to be online here at DUG every day? So how do you follow what is going on in the Dynamics industry and community?
If you subscribe to the DUG Newsletter then you can relax! We will make sure that you don't miss the big news!

Check out our
Newsletter Subscription Center
page a complete list of our different newsletters.

Solution (Verified) This post has 1 verified solution | 4 Replies | 4 Followers

Male
26 Posts
239 Points
Joined: 2007-8-24
Last Online:
2013-7-31 11:02
Location: Kampala, Uganda
Robert Mutyaba posted on 2009-8-20 10:38 | Locked

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

Solution (Verified) Verified Solution

Male
2,155 Posts
45,977 Points
Joined: 2005-12-6
Last Online:
2014-11-27 1:32
Location: Rælingen (Akershus), Norway
Moderator
Solution (Verified) Modris Ivans replied on 2009-8-20 13:03 | Locked
Verified by Robert Mutyaba

rbmafrica:
they cannot be applied as the invoice and payments are in different currencies.

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...

Modris Ivans
MCP-Dynamics NAV Application

Please specify in your posts
1.product and DB version;
2.locale, or use International Forums

"Verify Solution" that helped you solve your problem

All Replies

Male
2,155 Posts
45,977 Points
Joined: 2005-12-6
Last Online:
2014-11-27 1:32
Location: Rælingen (Akershus), Norway
Moderator
Solution (Verified) Modris Ivans replied on 2009-8-20 13:03 | Locked
Verified by Robert Mutyaba

rbmafrica:
they cannot be applied as the invoice and payments are in different currencies.

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...

Modris Ivans
MCP-Dynamics NAV Application

Please specify in your posts
1.product and DB version;
2.locale, or use International Forums

"Verify Solution" that helped you solve your problem

Male
539 Posts
13,141 Points
Joined: 2001-9-10
Last Online:
2014-10-30 15:18
Location: Copenhagen, Denmark
Moderator
Alexander Pallesen replied on 2009-8-20 13:08 | Locked

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

 

Regards,

Alexander Pallesen 
Dynamics NAV Developer

Male
26 Posts
239 Points
Joined: 2007-8-24
Last Online:
2013-7-31 11:02
Location: Kampala, Uganda
Robert Mutyaba replied on 2009-8-20 14:28 | Locked

Normal 0 false false false EN-GB X-NONE X-NONE MicrosoftInternetExplorer4

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

8 Posts
59 Points
Joined: 2012-11-24
Last Online:
2012-11-27 21:17
Location: Adelaide, Australia
Raheena Beegum replied on 2012-11-24 18:49 | Locked

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.

 

 

 

 

Page 1 of 1 (5 items) | Get this RSS feed | Bookmark and Share