Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Shari Bruno]

?Good Afternoon,

We have several GP companies whose functional currency is not USD, and currency exchange rates from their functional currency to USD is updated in their companies daily. All reporting in these companies is in functional currency so when they process a USD transaction, it converts to  the functional currency using the currency exchange rate for that day. 

At the end of the month we run consolidated reports in Management Reporter that pulls data from these other companies, and using the date of the report, converts all of the non-USD currencies to USD.

Because we have new exchange rates downloaded into those GP companies every day, unless a USD transaction is processed on the last day of the month, there will be a difference between what the functional currency was on the day of the transaction, and what it is on the last day of the month when the report is run.

Is there a way for Management Reporter to pull in the USD transactions with the exchange rate that was available on the day of entry, instead of the day of the report?

If not we are considering having the currency exchange rates updated just on the first of the month so that there is only one currency exchange rate for the entire month. Or maybe there is another solution that we’re missing?

Thanks!

——————————
Shari Bruno
Accounts Payable Specialist
Bible League International
Crete, IL
——————————

[Beat Bucher]

Hi Shari,
How many Exchange Table ID’s have you defined in your setup ? There are at least 3 to use for Average, Month-end and maybe Budget rates..
Depending on your setup, MR might pull up the wrong rate and use the daily rate, rather than an Average rate for the monthly report.. (or even a monthe-end rate)..
Here’s a KB document from Microsoft that explains the steps:
https://support.microsoft.com/en-us/help/2986068/currency-translation-for-microsoft-management-reporter-2012-with-micro

——————————
Beat Bucher
Business Analyst, Dynamics GP MVP
Ultra-Electronics Forensic Technology Inc.
Montreal QC/Canada
@GP_Beat http://dyngpbeat.wordpress.com/
Montreal QC GPUG Chapter Leader
GP2013R2 / MR2012 CU14
——————————
——————————————-

[David Mitzel]

?Financial Reports generated in Management Reporter require up to 3 different exchange rate tables, Current, Average and Historical rate tables.  A single rate table is assigned to each GL account and even though the rates added to each table are the same, the rate used for proper financial reporting will be different based on the exchange rate table.  The current, average and historical rate will likely never be the same.

——————————
David Mitzel
Senior Consultant
Socius
Brecksville OH
——————————
——————————————-

[Shari Bruno]

? and   Thanks for this information! I’ll let you know how it worked.

——————————
Shari Bruno
Accounts Payable Specialist
Bible League International
Crete, IL
——————————
——————————————-

[Shari Bruno]

?This is how we are set up:

We have two tables set up for each currency, the budget table and the daily table. The budget rate is fixed for the entire year so there is only 1 rate that budget columns are calculated from. All other reports use the daily rate table. If the report is run today it will recalculate the entire year at the 9/14/17 exchange rate in the tables.

Since we don’t have an Average table set up, could that be why our reports come up with these differences?

——————————
Shari Bruno
Accounting Operations Manager
Bible League International
Crete, IL
——————————
——————————————-

[Beat Bucher]

Absolutely.
If you read the KB article I posted the reference for, you’ll see that Microsoft talks about the Average and Month-end rates too..
As a suggestion, your screenshot shows the Z-US$ currency.. which is one the demo currencies that come with GP by default (because used by the Fabrikam Inc/TWO company), but you shoudl definitely setup your own currency and use the ISO official shortnames for them.. (all the ones starting with ‘Z-‘ are demo currencies).
Our company deals with 8 different currencies accross all our branches, and we have something like 45 Exchange tabled ID entries.. about 4-5 per currency (it varries by the pairs that we need to deal with).
For MR we would typically have something like :

EURO-CAD-AVE
EURO-CAD-BUD
EURO-CAD-ME
EURO-GBP AVERAG
EURO-GBP MEND
EURO-US AVERAGE
EURO-US-ME
USA-CAD-AVE
USA-CAD-BUD
USA-CAD-ME

Just to give a glimpse.. 🙂

——————————
Beat Bucher
Business Analyst, Dynamics GP MVP
Ultra-Electronics Forensic Technology Inc.
Montreal QC/Canada
@GP_Beat http://dyngpbeat.wordpress.com/
Montreal QC GPUG Chapter Leader
GP2013R2 / MR2012 CU14
——————————
——————————————-

[Shari Bruno]

– thank you so much! I’m new to this area of GP, I’ve spent the last 13 years in the purchasing module, so I’m learning all sorts of new things.

I was not involved with the currency setup but seem to remember something about starting them with a Z so that all currencies would group together. Is there an advantage to using ISO IDs? At this point, with almost 14 years of history in GP, would changing our currency IDs mean we would have some transactions with the Z- currencies, and some with the ISO coded currencies? Or can the PSTL combine currency codes like it combines customers and vendors with different IDs? Also, what impact would this have on the MR reports?

I haven’t had a chance to read the KB article, I passed it onto our financial analyst, but plan to dig into it on Monday and increase my understanding of this.

Thanks for all the great information!

——————————
Shari Bruno
Accounting Operations Manager
Bible League International
Crete, IL
——————————
——————————————-