AP Subledger and General Ledger not balance due to exchange rate

(0) Share

Report

Posted on by Community Member

Hi All,

Can someone show me how to clear GL balance.

Scenario:

USD invoices were paid with BSD currency. Now, AP Subledger and GL does not balance for both currency. Reason I found is because of exchange rates.

.1 Invoices that were paid - Settled currency and Payment should have the same amount since exchange rate is at par

.2 Exchange Rate

.3 AP - GL Balance

.4 Step to take to correct - Journal entry

Do I need to go thru cross currency account to clear balance? or GJ entry is enough?

Thank you,

Jesa

*This post is locked for comments

I have the same question (0)

All responses (17)

Answers (1)

Ludwig Reinhard Microsoft Employee on at

Like (0)

Report

Hi Jesa,

Have you run the AP-GL reconciliation report available in GL?

If so, does it show the same variance that you calculated above?

Have you made the payments against a bridging account or directly against a bank account?

Have you run any foreign currency revaluation in GL and/or AP?

Best regards,

Ludwig

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Ludwig,

1 I have not run the AP-GL reconciliation report as this will give me huge number of transactions from 1/31/16 - 12/31/16

- is there another way to run this report without getting huge number of transactions

2 The offset account for the invoices that were paid is bank

3 We normally run the foreign currency revaluation in GL when GL and AP Subledgers balance. This time we have not.

Tried to do a GJ entry in test environment it seems this will not work. Hope you can help me with this problem.

Thanks,

Jesa

Was this reply helpful? Yes No
Ludwig Reinhard Microsoft Employee on at

Like (0)

Report

Hi Jesa,

The AP-GL reconciliation report has a parameter that says 'show differences only', which should considerably limit the number of transactions included in the report even if you run it for the whole year 2016. Would be great if you could test running this report and share the result.

You mentioned that the payments are made against a bank account. Do you run a foreign currency revaluation for this bank account in the GL?

Can you explain more on point no 3? Why you run a foreign currency revaluation in GL and for which accounts?

Best regards,

Ludwig

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Ludwig,

When you run AP-GL reconciliation report, this is in reporting currency right? And for us this is in CAD. But anyway I ran the report and the variance I got is different from what I had.

We run the reval on all of our main accounts every month end. We do this because we deal with different currencies. Any main account that has open transaction on it, it runs a reval.

Thanks,

Jesa

Was this reply helpful? Yes No
Ludwig Reinhard Microsoft Employee on at

Like (0)

Report

Hi Jesa,

It is a bit difficult trying to help you on this theoretical level without seeing the underlying data/issue in detail. Irrespective of that, can you let us know why you run the GL currency revaluation for all of your accounts? Do you also run it for the AP summary account that is used for recording the vendor balance? If that is the case you might have a double / duplicate exchange rate effect. Please try to verify this in your system by analyzing the data on your AP and probably also AR summary account because I believe that you might have a similar issue with the AR.

Also test with some sample transactions in your test environment whether this might be the underlying reason for your variance.

Hope this helps.

Best regards,

Ludwig

Was this reply helpful? Yes No

Community Member on at

Like (0)

Report

Hi Ludwig,

Just a thought, when we do settle open transaction in AP/AR module does this affect the GL or just the subledger? If yes I think this is the reason why my APSL and GL are not balance.

This is what the entire scenario was:

.1 On OCT below invoices were posted - in USD currency

Date	Voucher	Ledger account	Currency	Amount in transaction currency USD	Amount in reporting currency CAD	Exchange rate
10/2/2016	VCH1650505	20201-90-90-02-0000	USD	644.63	845.56	131.1698
10/2/2016	VCH1650504	20201-90-90-02-0000	USD	4,500.00	5,902.65	131.1700
10/2/2016	VCH1650503	20201-90-90-02-0000	USD	4,600.00	6,033.82	131.1700
10/3/2016	VCH1650699	20201-90-90-02-0000	USD	-13,800.00	-18,101.46	131.1700
10/3/2016	VCH1650700	20201-90-90-02-0000	USD	-4,500.00	-5,902.65	131.1700
10/3/2016	VCH1650701	20201-90-90-02-0000	USD	-1,818.75	-2,385.65	131.1698
10/3/2016	VCH1650702	20201-90-90-02-0000	USD	-5,950.00	-7,804.62	131.1701
			Total invoices	-16,324.12	-21,412.35

.2 On NOV PMT was generated - BSD currency

Date	Voucher	Ledger account	Currency	Amount in transaction currency	Amount in reporting currency	Exchange rate
11/30/2016	PMT1883356	20202-90-90-02-0000	BSD	16,232.74	21,458.06	132.19

.3 Bank transaction - PMT in USD is 15,969.38 but exchange rate of BSD to USD is at par

Date	Voucher number	Bank transaction type	Payment reference	Method of payment	Deposit slip	Check number	Currency	Amount in transaction currency	Amount	Reconciled
11/30/2016	PMT1883356	06	8954-101716	WIRE			USD	-15,969.38	-21,458.06	No

.4 Exchage rate

.4 Settlement - BSD currency

Date	Voucher	Account	Offset voucher	Amount	Currency
11/30/2016	PMT1883356	31424	VCH1650503	-4,675.86	BSD
11/30/2016	PMT1883356	31424	VCH1650504	-4,574.21	BSD
11/30/2016	PMT1883356	31424	VCH1650505	-655.26	BSD
11/30/2016	PMT1883356	31424	VCH1650701	1,848.74	BSD
11/30/2016	PMT1883356	31424	VCH1650700	4,574.21	BSD
11/30/2016	PMT1883356	31424	VCH1650702	6,048.12	BSD
11/30/2016	PMT1883356	31424	VCH1650699	13,667.00	BSD
				16,232.74

.5 AP Subledger/GL

Currency	GL	AP Subledger	Variance
CAD TOTAL	(2,110,507.27)	(2,110,507.27)	-
USD TOTAL	4,495,694.51	4,511,663.90	(15,969.39)
BSD TOTAL	16,193.19	(39.55)	16,232.74
GBP TOTAL	(9,479.63)	(9,479.63)	-
	2,391,900.80	2,391,637.45	263.35

How am I going to correct this?

Thanks for the help, hope this time I have explained what was the problem.

Regards,

Jesa

Was this reply helpful? Yes No

Ludwig Reinhard Microsoft Employee on at

Like (0)

Report

Hi Jesa,

Thank you for sharing this data.

Now I can see the issue.

Is it possible that you post a screen print of voucher PMT1883356?

I would be interested especially in the transaction origin illustration of that voucher that shows the different modules and amounts posted.

If you have the voucher open can you identify the 'related voucher' button? If so, does it show you an additional voucher?

Have you setup (activated) the automatic settlement parameter or did you select the transactions to be paid and settled manually?

Finally, what AX version do you run exactly (R2/R3 Cu7/8/9...)?

Best regards,

Ludwig

Was this reply helpful? Yes No

Community Member on at

Like (0)

Report

Hi Ludwig,

.1 Transaction Origin

Module	Voucher	Date	Number	Text	Currency	Amount in transaction currency	Amount
Ledger	PMT1883356	11/30/2016	10301-90-90-02-0000	31424 - WIRE BSD	BSD	-16,232.74	-21,458.06
Ledger	PMT1883356	11/30/2016	20202-90-90-02-0000	31424 - WIRE BSD	BSD	16,232.74	21,458.06
Vendor	PMT1883356	11/30/2016	31424	31424 - WIRE BSD	BSD	16,232.74	21,458.06
Bank	PMT1883356	11/30/2016	BNS-USD	8954-101716	USD	-15,969.38	-21,458.06

.2 Related voucher - I wanted to add All Related Vouchers but it has 59 lines.

Related voucher	Date	Amount
PMT1913043	11/30/2016	335.80
PMT1913044	11/30/2016	328.50
PMT1913045	11/30/2016	47.06
PMT1913046	11/30/2016	132.77
PMT1913047	11/30/2016	328.50
PMT1913048	11/30/2016	434.35
PMT1913049	11/30/2016	981.50

.3 We select the invoices to be paid manually

.4 We are using AX 2012 R3

Thanks,

Jesa

Was this reply helpful? Yes No

Ludwig Reinhard Microsoft Employee on at

Like (0)

Report

Hi Jesa,

Further above you showed that the exchange rate USD/BSD is 1:1.

This does not seem to be the case for the voucher above, otherwise the USD amount posted on the bank account should be identical to the BSD amount.

Do you know whether the journal was somehow created automatically through an Import or something alike?

Can you analyze the related voucher transactions even if you do not post them? They should explain where the difference comes from. It might be a payment fee that is posted automatically or something alike.

Can you also open the journal through which the voucher was posted in order to double check that the exchange rate was not manually overwritten?

Best regards,

Ludwig

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Ludwig,

That's one of my question too. Why did the posted PMT did not convert at par?

The PMT was manually generated/created.

All the related vouchers are Foreign currency revaluation. The variance I have does not equal to any of the transactions.

The exchange rate from BSD to CAD is not the same value on the day the PMT was posted. I am not sure if this has to do with the original problem since the GL for CAD balance.

Is there anyway I can clear the amount in the GL that makes the variance? Coz, if you are to look .5 AP Subledger/GL above. It seems that the PMT was posted GL BSD, but did not clear the corresponding AP SL USD. Hope I was clear on my statement.

Thanks,

Jesa

Was this reply helpful? Yes No