Multiple input currencies for a single entity

[#1]

My client must support multiple currencies for any given Entity. For instance, Spanish Company #1 must be able to record transactions (sales and purchases/costs) in both Euros and in British Pounds, since both currencies are used by this entity. Since, by the default configuration, an Entity can only support one currency (LC), I am searching for alternatives.

Option 1:

Use a user-defined dimension, such as a Geography dimension, as the dimension that is tied to Rate, and modify the FX scripts accordingly. The Spanish Company #1 will remain in the Entity Type dimension.

Option 2:

Assign a different dimension as the Entity Type, such as a Geography dimension. And make the former Entity Type dimension, where the Spanish Company #1 lives, into a user-defined dimension.

Are their other options to consider?

We are planning on supporting multiple reporting currencies (it is currently just USD), so we are reluctant to use Option 1, because the business rules may be hard-coded to the Entity dimension (are they?). We also plan on performing intercompany eliminations down the road, so we are reluctant to use Option 2, since at that time we will be relying on the entity type for those functions.

Thank you for your thoughts on this.

[Former Member · Accepted solution]

If it's possible to have two sub-entities within the Spain company, this may be easiest from a system-complexity standpoint (particularly the currency conversion). Consider how your data integration of actuals, work status submission, and reporting would work; it may not be feasible.

On a few occasions I've had an account-level over-ride (for USD or EUR bank accounts & loans, things like that) for the entity-level default currency. This requires you to use custom SQL-based currency conversion logic, since the SPRunConvert business rule only allows for entity-level control over defining LC. But if you can then have "GBP Sales" and "EUR Sales" accounts, that may work.

[Former Member]

We considered your first option earlier of multiple entities each with their own currency, but the number of currencies and number of entities in the implementation is too large as to be prohibitive.

Your second option sounds a bit more complex that I'd like to attempt, since it would be going too much outside of the more standard functionality.

This must be a common issue with a simpler solution .... somewhere....

[Former Member]

If you're interested, here's some FX translation logic with account-level override. You may be able to modify this to choose some other dimension as your override -- datasrc, or whatever makes most sense.

// Factors that impact currency conversion:
// Entity.Currency property (required)
// Account.Currency property (optional, overrides Entity.Currency if set)
// Account.RateType - AVG & END use corresponding rate types, blank value translates at 1:1, NOTRANS produces no record in reporting currency
// InputCurrency.Reporting = "Y" produces a translation; must have a matching member in RptBasis


// look up dimension names and parameters
*INCLUDE system_constants.lgl
*SELECT(%REPORTING_CURRENCIES%, "[ID]", "RptBasis", "[REPORTING] = 'Y'")
*SELECT(%FX_RATES%, "[ID]", "RATE", "[GROUP] = 'FX RATE'")

*CLEAR_DESTINATION
*DESTINATION RPTBASIS=%REPORTING_CURRENCIES%

// look up rates from Rate cube
*LOOKUP RATE
  *DIM RATEENTITY="RATECALC"
  *DIM RATE=ACCOUNTDIM.RATETYPE
  *DIM SOURCECURR1:INPUTCURRENCY=ENTITY.CURRENCY
  *DIM SOURCECURR2:INPUTCURRENCY=ACCOUNT.CURRENCY
  *FOR %CURR%=%REPORTING_CURRENCIES%
    *DIM %CURR%:INPUTCURRENCY="%CURR%"
  *NEXT
*ENDLOOKUP


// translation rule

*WHEN ACCOUNTDIM.RATETYPE
  *IS "NOTRANS"
  // skip; no ReportingCurrency records are generated for these accounts


  *IS %FX_RATES%
  // translate AVG & END accounts

      *WHEN ACCOUNTDIM.CURRENCY
        *IS <>""
        // use the currency of the account
          *FOR %CURR%=%REPORTING_CURRENCIES%
            *REC(FACTOR=LOOKUP(SOURCECURR2)/LOOKUP(%CURR%),RPTBASIS="%CURR%")
          *NEXT

       *ELSE
        // use the currency of the entity
         *FOR %CURR%=%REPORTING_CURRENCIES%
           *REC(FACTOR=LOOKUP(SOURCECURR1)/LOOKUP(%CURR%),RPTBASIS="%CURR%")
         *NEXT
      *ENDWHEN

   *ENDWHEN

  *ELSE
  // For account.ratetype that is blank, ReportingCurrency values are equal to LC values.
     *FOR %CURR%=%REPORTING_CURRENCIES%
        *REC(RPTBASIS="%CURR%")
     *NEXT
*ENDWHEN


*COMMIT