AscentERP with SalesForce Multi-Currency Explanation

--Written by James Gaines, Director of Research & Development, AscentERP

 

There's been a lot of internal and external discussion about how Multi-Currency works with AscentERP.  Please use this document as your reference when you have Multi-Currency questions going forward.

 

First Some Education from SalesForce:

SalesForce.com provides a native solution for Multi-Currency exchange rates.  You can read more about it here:

https://help.salesforce.com/HTViewHelpDoc?id=admin_currency.htm&language=en_US

 

Some other Highlights:

- Multi-Currency is "off" in SFDC by default.  You must "enable" multi-currency - see attached "Multi-Currency...docx"  -- Once turned on, it cannot be turned off.

- You must set one and only one "Corporate" currency. For purposes of the next few statements, let's assume our corporate currency is GBP.  But that we also support other currencies like USD and EUR.

- When you turn on Multi-Currency in salesforce it means every single record in the system will now have a new field called "Currency". When I say ALL I mean ALL.  Everything. So Even records that have nothing to do with money like Locations or Movement Lines or Contacts.

- Some records may be stored in a different currency than the corporate currency, and that is ok, it is by design.

- AscentERP is a native application on the Salesforce platform giving us the ability to utilize salesforce's multi-currency exchange rates functionality in our application across many different objects and functionalities.    

 

(Go get some coffee...)

 

There are two types of Multi-Currency and they must be explicitly enabled. You can enable one, both or neither.  

1.) Parenthetical Multi-Currency Exchange Rates (The sexy, dangerous one)

2.) Advanced Dated Multi-Currency Exchange Rates (The ugly duckling that does all the work)

 

The problem with #1 and #2 are not that they are sexy or ugly, but that they do not play together. The Parenthetical Exchange Rates are stored in a different table than Advanced Exchange Rates.  It is your job as the salesforce admin to keep these tables in sync. Otherwise, Ascent users will be extremely confused and blame Ascent for something we cannot control.

 

Example:

Parenthetical Exchange Rates do exactly as they sound.  Let's say you have a record stored in EUR and there is a currency field on this record like "Price".  The number you see in the Price field is 663.98. This means this record is 663.98 EUR. When you turn on Parenthetical Exchange Rates you will see something like this

 

663.98 EUR (587.60 GBP)

 

This is because the exchange rate stored in the parenthetical exchange rate table is resulting in 663.98 EUR to be worth 587.60 GBP.  The key point to note here is that parenthetical exchange rates can only convert in one direction - From the record's currency EUR to the corporate currency GBP.  For instance, it can only tell you the conversion from EUR > GBP or from USD > GBP. It cannot tell you EUR > USD or USD > EUR.

 

While it's nice to see the converted parenthetical currency exchange to the corporate exchange rate on all these pages, is very dangerous because it is like a formula field in Salesforce.  This means it is dynamic. What do you mean? Say, I am looking at this record right now and the exchange rate yields this (587.60 GBP). Later my admin changes the Parenthetical Exchange Rate Table values.  This is VERY COMMON. Then if I come back to this record in a report years later it might appear something like this:

 

663.98 EUR (322.33 GBP)

 

You might say to me - "Wow, James, how is this possible?  How can the value of a Received Purchase Order Line change over time just because I changed the exchange rate years later?  I would think that it would stay the same because AscentERP would record the exchange rate when that record was created. Changing over time seems so confusing!!!  And scary for accounting."

 

Enter the ugly duckling, Advanced Multi-Currency Exchange Rates...

 

Advanced Multi-Currency Exchange Rates is another table that not only stores the exchange rate but also stores the date at which that exchange rate was valid to and from.  AscentERP likes this table very much. :-) So much so that we have built an entire conversion factor system to not only convert records to the corporate currency, but also to other Currencies.  With the power of AscentERP and Advanced Multi-Currency Exchange Rates, we can tell you the exchange rate of EUR>GBP and USD>GBP but also USD>EUR and EUR>USD! And we store them historically, so they don't change over time.  Records in the AscentERP now keep a static history of what the exchange rates at that time so historical reporting with multi-currency is now a reality...

 

Please see screenshots STEP 1 - STEP 4 to see how easy it is to get extremely confused by using Parenthetical and Advanced together.

 

STEP 1 - DIFFERENT SFDC CURRENCY EXCHANGE RATES EXPLAINED

STEP 2 - Advanced Dated Exchange Rates (THE NECESSARY ONE)

STEP 3 - Parenthetical Currency (THE COOL BUT CONFUSING ONE _ TO BE USED WITH EXTREME CAUTION)

STEP 4  - ILLUSTRATION OF CONFUSION

Created From using Both Advanced and Parenthetical at the Same time and Not keeping them in SYNC

Before I go into examples I want to answer some burning questions:

Why would anyone use Parenthetical?  

The answer is simple... because it is pretty, provides immediate gratification, and salespeople like it.  Let's be honest, it really does help them do their job better because it shows you right there how much the current value of an opportunity or sales order is in the corporate currency.  

 

Can we use Parenthetical and Advanced together successfully?

Yes, you certainly can.  You just have to make sure you keep the two tables in STEP 2 screenshot and STEP 3 screenshot attached in sync at all times.  Don't change one without changing the other!!

 

Why do you hate Parenthetical Exchange Rates?

I don't.  With great power comes great responsibility.  See above answer.

 

Explanation on How Multi-currencies work with Ascent Objects and Working Examples.

 

There's no custom setting.  It just works. (Apple™)

 

The official word is that AscentERP builds upon the Advanced Multi-Currency but will support Parenthetical.  It is important that everyone really understands the differences, which is the sole purpose of this dissertation.

 

Side notes: It is extremely important that FF customers understand that this is completely different than the FF multi-currency management.  It is truly unfortunate, but Salesforce has a separate system for currency management than Financial Force. This means the customer has to manage their exchange rates in THREE different places if you have been following along, and keep them in sync or things will go bonkers.

 

Sales Orders - Ascent stores the exchange rate at the time of the sales order on the sales order header and sales order line so you can always go back and find out why the value is what it is.  You can use this and the "ItemSalesPrice" field on the sales order line to make sure everything is working properly. ItemSalesPrice represents the sales price of the item before conversion. So you can go backward and forwards using this dated exchange rate stored on the sales order line with formulas or reports, etc.

 

The same is true for Purchase Order, PWO, RMA, etc.  We store the dated exchange rate everywhere, basically.

 

You can store currency at the item level in any currency you want.  Just make sure you understand that it will be converted to whatever cost/price based on the type of transaction you are doing.  Let's see how this works.

 

All child records created from other records will inherit the currency from their parent.

 

Customer Example:

Account (EUR) >> Sales Order (EUR) >> Sales Order Line (EUR) >> Packed Line (EUR) >> Movement Line (EUR) >> Sales Invoice (EUR) >> Sales Invoice Line (EUR)

 

Vendor Example:

Account (EUR) >> Purchase Order (EUR) >> Purchase Order Line (EUR) >> Packed Line (EUR) >> Movement Line (EUR) >> Vendor Invoice (EUR) >> Vendor Invoice Line (EUR)

 

The sales order inherits its currency from the Customer Account on the Sales Order.  The currency can be changed at the sales order level after it is inherited by it is strongly discouraged.  It makes everything after that record occurs in the new sales order currency.

 

If the item master is in a different currency than sales order line, AscentERP automatically converts from the SalesPrice on the Item Master to the currency of the sales order using exchange rates from salesforce advanced multi-currency exchange rates.

 

Please get to the examples...

 

The default org currency is USD.

Advanced Multi-currency exchange rates are like so:

EUR >> USD = 1.1

GBP >> USD = 1.5

 

Acme is a customer that is stored in USD

Item-E is stored in EUR - SalesPrice = 100

Item-U is stored in USD - SalesPrice = 100

Item-G is stored in GBP - SalesPrice = 100

 

I create a sales order for Acme.  Automatically the sales order is changed to USD.

I create 3 sales order lines, one for each of my items.  They will look like this:

 

SOLine1--Item-E---Price=$110 USD---Exchange Rate = 1.1

SOLine2--Item-U---Price=$100 USD---Exchange Rate = 1.0

SOLine3--Item-G---Price=$150 USD---Exchange Rate = 1.5

 

Following the rule, all records created get the currency of their originating transaction, the packed sales order lines will be generated in USD in this case because SO Line is USD because SO Header is USD, because Customer Account is USD.  Using this same rule, the movement lines generated from packed so lines will be... you guessed it USD.

 

You might sell the same items to a GBP Customer, and then all records created will be in GBP, storing the conversion rates along the way all the way to movement lines.  

 

Same thing with a Purchase Order.  Vendor's currency determines the currency of the Purchase Order and of all forthcoming records.

 

As far as inventory created, the Location and Lot and inventory all have a currency.  The Inventory/Lot/Location will always have the same currency where the Location is the parent in this case.  

 

It does not matter what currency the item is stored in, the currency of the inventory will always be in the Location for the inventory.

 

Example:

ITEM-X, Currency = USD, Cost = 12.00

 

LOT-A, Currency = AUD, Location = LOC-A

LOC-A, Currency = AUD

Inventory Detail Name = 1111, Currency = AUD, Cost = 15.00, Quantity = 10

 

LOT-B, Currency = GBP, Location = LOC-B

LOC-B, Currency = GBP

Inventory Detail Name = 2222, Currency = GBP, Cost = 10.00, Quantity = 10

 

Multi-Currencies with Average Cost:

This has HUGE implications on average costing in the system.  If you plan to use average cost and multi-currency, AscentERP is awesome and we allow you to have inventory valued differently based on the currency of the Location it is in and we do all the hard work for you and store the final converted average cost value on in the "Cost" field on the item master.  But if this gives you heartburn the moral of the Location story is this:

 

If you plan to only value your inventory at the Item Master currency and don't want exchange rates to impact the average cost calculation, then you need to make all of your locations where this item's inventory is stored has the same currency at the item master.  

 

Multi-Currencies with Trade Agreements:

Surprise! Trade Agreements have currencies too.  It is very common to have multiple trade agreements stored in different currencies against the item master.  It's kind of like price book. Only trade agreements that have the currency of the sales order or purchase order you are operating on will qualify.  Trade agreements override all "automatic" conversions of item sale price mentioned above for sales orders and purchase orders.

 

So let's say you have an ITEM-X stored in USD. SalesPrice = 500.00

Trade Agreements:

TA-USD, Price = 400

TA-EUR, Price = 550

 

Sales Order for USD customer will choose TA-USD and result in 400.00

Sales Order for EUR customer will choose TA-EUR and result in 550.00

Sales Order for AUD customer will see there is no TA's that qualify and go to the Advanced Multi-Currency Exchange Rate table and convert 500 > AUD value automatically.  

 

This information should make something painfully obvious. If you use trade agreements for pricing while multi-currency is turned on, then you are ignoring the dated exchange rate tables for converting records automatically from the item master currency to the currency of that sales order or purchase order.  You are purposefully overriding the converted value the exchange rate table would have yielded with a TA Price for marketing or operational purposes. There is nothing wrong with this approach. Many customers prefer this level of granular control over pricing.

 

To be continued…

There are several edge cases like Entities, Financial Force, Sage, AFP, and Inventory crossing country lines with different currencies.  All of these require more thoughtful considerations, which are not covered in this document. For now, this should handle 90% of daily use cases for multi-currencies.