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.
- Invoice (sales/purchase)
- Invoice payment
- Withdrawal/Deposit to financial account
- Reconciliation of financial account
At each stage we could potentially have a different currency or at least a different conversion rate. In practice:
- all payments will be in the same currency as the invoice. e.g. if invoice is in USD, you would pay/receive a USD amount.
- withdrawals & deposits are recorded in the currency of the financial account.
- if there is 1 payment which matches 2 diferent invoices with different accounting dates, then the system must take into account the exchange rates on each date and generate the corresponding gain/loss ledger entries while posting the payment.
The other dimension to multicurrency is the accounting schema currency:
- A company making business in foreing currency might require just one accounting schema, e.g. EUR.
- Foreing currency transaction (USD) must be converted into the account schema currency (EUR).
- A company making business in foreing currency might require posting in several accounting schema currencies. e.g. EUR accounting schema and USD accounting schema.
- Depending on the currency transaction, the corresponding currency conversion should take place.
Out of scope:
- 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.
- 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.
- 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):
- Accounting Schema is in EUR
- Bank account is in EUR
- Purchase Invoice for USD 1400
- Conversion rate varies day to day
- Invoice is received for USD 1400 on 1st Jan 2011
- Conversion rate is 1 USD = 0.9 EUR (A/R entry is 1260 EURO)
- Payment for the invoice is made on 8th Jan.
- Conversion rate is 1 USD = 0.8 EUR
- User adds payment to invoice, specifies 1400 USD as payment amount, the EUR bank account as the source of the payment
- System should show the expected EUR amount using the current conversion rate (i.e. 1120 EUR)
- User completes the payment and chooses to just process the payment
- System needs to record a currency gain of 140 EUR in journal entry while posting the payment.
- On 9th Jan user processes yesterdays payments and adds the transaction to the bank account
- Conversion rate is 1 USD = 0.85 EUR
- System records a withdrawal from the account of 1190 EURO and a currency loss of 70 EUR in the journal entry
- 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)
- 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
- Accounting Schema is in EUR
- Purchase Invoice for EUR 1000
- Bank account is in USD
- The EUR to USD conversion rate is fixed at 1 EUR = 1.4 USD
- Invoice is received for EUR 1000 on December 2010
- Payment for the invoice is made on January 2011
- User adds payment to invoice, specifies EUR 1000 as payment amount
- User selects the USD bank account as the source to make the payment
- 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)
- User could change the given exchange rate and therefore the system will have to properly adjust the expected USD amount
- User completes the payment and chooses to automatically withdraw the amount
- System generates a financial txn for 1400 USD to withdraw from the bank account
- 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.
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
- Accounting Schema is in EUR
- Bank account is in EUR
- Purchase Invoice for USD 1400
- Conversion rate varies day to day
- Invoice is received for USD 1400 on 1st Jan 2011
- Conversion rate is 1 USD = 0.9 EUR (A/R entry is 1260 EURO)
- Payment for the invoice is made on 8th Jan.
- Conversion rate is 1 USD = 0.8 EUR
- User adds payment to invoice, specifies 1000 USD as payment amount, the EUR bank account as the source of the payment
- System should show the expected EUR amount using the current conversion rate (i.e. 1120 EUR)
- User completes the payment and chooses to process and withdrawn the payment
- 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:
- to apply the USD/EUR exchage rate
This scenario implies in relation to the Invoice Payment in USD:
- to apply the USD/EUR exchage rate
Invoice
On the 1st January receive an invoice for $1100 USD (including $100 tax).
Exchange rates
- System (USD->EUR) = 0.9 (1 USD = 0.9 EUR)
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
- System (USD->EUR) = 0.8 (1 USD = 0.8 EUR)
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
- System (USD->EUR) = 0.75 (1 USD = 0.75 EUR)
which is manually changed by the end-user to (USD->EUR) = 0.7 (1 USD = 0.7 EUR)
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:
- to apply the USD/EUR exchange rate, to get the "Deposit Amount"
- to apply the EUR/USD exchange rate, to get the "Foreing Amount"
Besides, this scenario implies in relation to the Bank transaction posting:
- to apply the EUR/USD exchage rate
Invoice
On the 1st January receive an invoice for $1100 USD (including $100 tax).
Exchange rates
- System (USD->EUR) = 0.9 USD/EUR
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:
- the Deposit Amount will be in (EUR)
- the Foreign Amount will be in (USD)
Exchange rates
- System (USD->EUR) = 0.8 (0.8 USD/EUR)
- System (EUR->USD) = 1.25 (1.25 EUR/USD)
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
- System (USD->EUR) = 0.75 (0.75 USD/EUR) which implies
- System (EUR->USD) = 1.333333333333 (1.33333 EUR/USD)
End user update the exchange rate to:
- Transaction (USD->EUR) = 0.7 (0.7 USD/EUR) which implies
- Transaction (EUR->USD) = 1.428571 (1.428571 EUR/USD)
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
- Payment proposals are restricted to payments in the same currency.
- Exchange rates for payments will be stored in the document so they can be overridden and referred to later
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:
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- See "Out of Scope" section.