Log in / create account
Openbravo.com
Issues
Forge
University
View source | View content page | Page history | Printable version   

Projects:AdvPaymentMngt/Functional Documentation Multicurrency

Contents

Objective

The objective of this project is to enable multicurrency functionality in Advanced Payables and Receivables Management (APRM).

Process Overview and Assumptions

There are four distinct stages or documents involved in the payment workflow.

  1. Invoice (sales/purchase)
  2. Invoice payment
  3. Withdrawal/Deposit to financial account
  4. Reconciliation of financial account

At each stage we could potentially have a different currency or at least a different conversion rate. In practice:

The other dimension to multicurrency is the accounting schema currency:

Out of scope:

  1. Implementation of an "Exchange/Conversion Rate" in the Financial Account.
    Current version works based on the "System" conversion rates wich can be find at the application path
    General Setup // Application // Conversion Rates.
    System conversion rate setup implies that it is not possible to have different conversion rates on the same day, we can only have one conversion rate for any given day.
  2. Implementation of a "Currency" field and a "Exchange/Conversion Rate" field in the Payment Proposal window.
    This scenario could allow us to create a payment proposal in other currency than the Financial Account currency.
  3. Implementation of an "Exchange/Conversion Rate" in the Financial Account- Add Transaction window.
    This scenario could allow us to take into account a diferent exchange rate than the one used when entering a payment, while entering withdrawals/deposits.

Example of Out of Scope (3):

  1. Invoice is received for USD 1400 on 1st Jan 2011
    1. Conversion rate is 1 USD = 0.9 EUR (A/R entry is 1260 EURO)
  2. Payment for the invoice is made on 8th Jan.
    1. Conversion rate is 1 USD = 0.8 EUR
    2. User adds payment to invoice, specifies 1400 USD as payment amount, the EUR bank account as the source of the payment
    3. System should show the expected EUR amount using the current conversion rate (i.e. 1120 EUR)
    4. User completes the payment and chooses to just process the payment
    5. System needs to record a currency gain of 140 EUR in journal entry while posting the payment.
  3. On 9th Jan user processes yesterdays payments and adds the transaction to the bank account
    1. Conversion rate is 1 USD = 0.85 EUR
    2. System records a withdrawal from the account of 1190 EURO and a currency loss of 70 EUR in the journal entry
  4. On 31st Jan the bank reconciliation is run & the final withdrawal amount is 1195 EUR (because bank conversion rate was slightly different to the system rate)
    1. We will obviously need to adjust the recorded transaction to match the actual transaction.

The key point from this scenario is that the user needs the ability to override the converted amount proposed by the system. In 2.5 this was not possible, so recording payments involved working out the needed conversion rate, entering it into Openbravo for the payment date, and then hoping it all came out right. Sometime this worked, but often there would be a small discrepancy due to rounding errors that needed to be accounted for somehow.

An alternative path in this scenario is for the user to choose to combine steps b & c. i.e. process & withdraw the payment when it is entered. In this case we need to allow the user to override the converted amount in the initial payment screen so that the correct amount is recorded in the financial txn.

User Stories

Simple scenario

  1. Invoice is received for EUR 1000 on December 2010
  2. Payment for the invoice is made on January 2011
    1. User adds payment to invoice, specifies EUR 1000 as payment amount
    2. User selects the USD bank account as the source to make the payment
    3. System should show the current exchange rate (1 EUR = 1.4 USD), and therefore the expected USD amount using that conversion rate (i.e. 1400 USD)
      1. User could change the given exchange rate and therefore the system will have to properly adjust the expected USD amount
    4. User completes the payment and chooses to automatically withdraw the amount
  3. System generates a financial txn for 1400 USD to withdraw from the bank account
  4. On 31st Jan the bank reconciliation is run & the 1400 USD withdrawal is matched to the bank statement and cleared

This is the simplest scenario as the currency conversion rate is fixed so there are no currency gains/losses to deal with. This also means that the user can just accept the conversions proposed by the system and has no need to think about the different currencies.

MultiCurrency-Simple 0000 New.png MultiCurrency-Simple 0001 Payment-EUR.png MultiCurrency-Simple 0002 Payment-USD.png MultiCurrency-Simple 0003 Change-Rate.png MultiCurrency-Simple 0004 Change-Amount.png

Financial Account

A financial account always uses one currency, e.g EUR, but it should also allow making/receiving payments in "Other currencies".

It should be possible to setup "Making/Receiving payments in other currencies" in the Payment Methods linked to a Financial Account.

In the case of a transaction (e.g. a payment) done in a financial account non-default currency, this amount should be shown in the "Foreign Amount" column while the financial account default currency amount should be shown in the "Deposit Amount".

Variable conversion rate scenario

  1. Invoice is received for USD 1400 on 1st Jan 2011
    1. Conversion rate is 1 USD = 0.9 EUR (A/R entry is 1260 EURO)
  2. Payment for the invoice is made on 8th Jan.
    1. Conversion rate is 1 USD = 0.8 EUR
    2. User adds payment to invoice, specifies 1000 USD as payment amount, the EUR bank account as the source of the payment
    3. System should show the expected EUR amount using the current conversion rate (i.e. 1120 EUR)
    4. User completes the payment and chooses to process and withdrawn the payment
    5. System needs to record a currency loss of 140 EUR in the journal entry when posting the payment.

This scenario mostly involves adding appropriate currency gain/loss entries to the journal postings. The user can still accept the conversion rates proposed by the system or nor.


Accounting Scenarios

Payment : Schema(EUR), Bank (EUR) , Invoice (USD)

This scenario implies in relation to the Invoice in USD:

This scenario implies in relation to the Invoice Payment in USD:

Invoice

On the 1st January receive an invoice for $1100 USD (including $100 tax).

Exchange rates

Account Debit Credit Calculation
Accounts Payable 990 EUR 1100 USD * 0.9 USD/EUR
Purchase Expense 900 EUR 1000 USD * 0.9 USD/EUR
Tax 90 EUR 100 USD * 0.9 USD/EUR

First Payment

On the 16th January make a payment of $550 USD, using the system exchange rate

Exchange rates

Payment posting:

Account Debit Credit Calculation
Accounts Payable 495 EUR 550 USD * 0.9 USD/EUR (exchange rate at invoice date)
Bank In Transit 440 EUR 550 USD * 0.8 USD/EUR
Currency Gain 55 EUR 495 EUR - 440 EUR

Note: The A/P entry must be caculated using the same exchange rate as the a/p credit in the original invoice journal. Otherwise the a/p account will not balance properly.

On the 16th January post the transaction in the bank account.

Financial Account transaction posting:

Account Debit Credit Calculation
Bank In Transit 440 EUR
Bank 440 EUR

Second Payment

On the 23th January make a payment of $550 USD, using a custom exchange rate

Exchange rates

Payment posting:

Account Debit Credit Calculation
Accounts Payable 495 EUR 550 USD * 0.9 USD/EUR (exchange rate at invoice date)
Bank In Transit 385 EUR 550 USD * 0.7 USD/EUR (exchange rate entered manually by end user)
Currency Gain 110 EUR 495 EUR - 385 EUR

Note: The A/P entry must be caculated using the same exchange rate as the a/p credit in the original invoice journal. Otherwise the a/p account will not balance properly.

On the 25th January record the transaction in the bank account.

Financial Account transaction posting:

Account Debit Credit Calculation
Bank In Transit 385 EUR
Bank 385 EUR

Payment : Schema(USD), Invoice (USD), Bank (EUR)

This scenario implies in relation to the Bank (Financial Account) in EUR:

Besides, this scenario implies in relation to the Bank transaction posting:

Invoice

On the 1st January receive an invoice for $1100 USD (including $100 tax).

Exchange rates

Account Debit Credit Calculation
Accounts Payable 1100 USD
Purchase Expense 1000 USD
Tax 100 USD

First Payment

On the 16th January make a payment of $550 USD.

The Financial Account in EUR is used as the source of the payment.
In this scenario:

Exchange rates

Payment posting:

Account Debit Credit Calculation
Accounts Payable 550 USD
Bank In Transit 550 USD 550 USD 0.8 USD/EUR = 440 EUR (Deposit Amount)
440 EUR * 1.25 EUR/USD = 550 USD (Foreign Amount)

On the 16th January record the transaction in the bank account.

Financial Account transaction posting:

Account Debit Credit Calculation
Bank In Transit 550 USD
Bank 550 USD 440 EUR * 1.25 EUR/USD

Second Payment

On the 23th January make a payment of $550 USD, using a custom exchange rate

Exchange rates

End user update the exchange rate to:

while adding the payment, either from the Purchase Invoice or from the Financial Account.

Payment posting:

Account Debit Credit Calculation
Accounts Payable 550 USD
Bank In Transit 550 USD 550 USD * 0.7 EUR/USD = 385 EUR (Deposit Amount)
385 EUR * 1.428571 EUR/USD = 550 USD (Foreign Amount)

On the 25th January record the transaction in the bank account.

Financial Account transaction posting:

Account Debit Credit Calculation
Bank In Transit 550 USD
Bank 550 385 EUR * 1.25 EUR/USD

Design Considerations

UI Considerations

TBA

Issues

Please initiate a new discussion thread in the Forum for this project.

Remember that you will need to be logged in to the Openbravo Forge to do this.

All new discussion threads will be added to the Open Items list below by the Project Owner.

When it is agree in the Discussion Forum that it is closed (by the Functional Specification being updated, or the issue being deferred or rejected) then the link will be moved to Closed Items.

Open Items:

Closed Items:

  1. Should payment proposals be restricted to payments in the same currency?
    This means that you will not be able to have a payment for a USD invoice and a EUR invoice in the same proposal.
    I'm neutral on this, I don't see it as a big restriction, but happy to hear from others.
    RMO: Yes. Payment proposals are restricted to payments in the same currency. There is already a currency filter on the payment proposal header. Requirements updated.
  2. Should cross currency transactions be enabled on all financial accounts and payment methods? Several options here
    a) Enabled for all accounts and payment methods (no way to disable)
    b) Option to enable on accounts - if checked then any payment method on that account can be used for cross currency transactions
    c) Option to enable on payment method - could be one option, or separate for in/out. Specified in payment method but overridable when method added to account (exactly the same as the other settings)
    d) Option to enable for specified currencies on account or method. So you could say this EUR account can make USD payments but not GBP payments.
    I think (c) is the best, closely followed by (b). (a) seems too simple and (d) seems too complicated.
    RMO: Agreed. Option c) is the most desirable.
  3. How do we deal with fees associated with multicurrency transactions (e.g. conversion fee, international transaction fee, wire transfer fee)?
    Some banks will record fees as separate line items - this can be easily dealt with by adding separate transactions. Other banks bundle the fee into the one line item. We can ignore the fee component and just convert between the total amount and the foreign currency account. However some companies may want to record the fees separately so that they can monitor them.
    It would be useful to be able to add a fee amount to a payment, similar to the writeoff amount that currently exists.
    RMO: Agreed that it would be useful. We can keep this in mind as a future enhancement. Added below.
  4. What happens to the asset value of foreign currency accounts in general ledger as the conversion rate fluctuates?
    At the moment I don't think the only way to revalue/adjust the asset value of a foreign currency account is to do a manual general journal entry. This works, but relies on the end user to remember to do it. It would be nice if there was a way to automate this process (assuming the company wants it automated).
    I am not an accountant so I'm not really sure how this type of adjustment should be handled (or even if it is required)
    RMO: In the short term we will provide a report of the value if the currency account at an exchange rate specified by the user. That will enable the user to do a manual adjustment at the month end. It is acknowledged that automating this would be a desirable as a potential future enhancement. Added below.

Future Enhancements

Please describe here any enhancements to this process that could be considered for future development.

  1. How do we deal with fees associated with multicurrency transactions (e.g. conversion fee, international transaction fee, wire transfer fee)?
    Some banks will record fees as separate line items - this can be easily dealt with by adding separate transactions. Other banks bundle the fee into the one line item. We can ignore the fee component and just convert between the total amount and the foreign currency account. However some companies may want to record the fees separately so that they can monitor them.
    It would be useful to be able to add a fee amount to a payment, similar to the writeoff amount that currently exists.
  2. Automating the management of asset value of foreign currency accounts in general ledger as the conversion rate fluctuates or in case of end-year closing activities.
    At the moment the only way to revalue/adjust the asset value of a foreign currency account is to do a manual general journal entry. This works, but relies on the end user to remember to do it. It would be nice if there was a way to automate this process (assuming the company wants it automated).
    In the short term we will provide a report of the value if the currency account at an exchange rate specified by the user. That will enable the user to do a manual adjustment at the month end. It is acknowledged that automating this would be a desirable as a potential future enhancement.
  3. See "Out of Scope" section.

Retrieved from "http://wiki.openbravo.com/wiki/Projects:AdvPaymentMngt/Functional_Documentation_Multicurrency"

This page has been accessed 4,626 times. This page was last modified on 8 June 2012, at 05:26. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.