Multiple Currencies
Introduction
One of the most important points of a finance software is the proper handling of a different currencies. To enable openpetra to do the valid calculations the columns of the database table “a_currency” shall be enhanced. Therefore let us have a look onto the actual table referred in http://openpetraorg.sourceforge.net/dbdoc/ and the topic main.
This table manages the handling of the different money units in different countries.
- a_currency_code_c –This defines an international code which describes the currency like USD for the us-American dollar or EUR for the European currency
- a_currency_name_c – A free text string containing the name of the currency like “United States Dollar” or “European Money Unit”
- a_currency_symbol_c – This is the symbol used to show a currency, e.g. $US or EUR
- p_country_code_c – Name of the country the currency is used in like “United States” or “99” with a reference to the table p_country
- a_display_format_c – The currency format used to display and to accept input currency (decimal values)
- a_in_emu_l – Indicates whether currency is part of the european exchange rate mechanism/ European Monetary Union or not
Remarks to the actual database table
- a_currency_code_c – ok.
- a_currency_name_c – This name is a free text and there may be the requirement to translate this column respective to get access to different language version at the same time. So this column shall be swapped out into a multilanguage table.
- a_currency_symbol_c – The real symbols are $ and €. By changing from Courier formated reports to graphical reports we shall change this values too.
- p_country_code_c – As seen in europe a currency is not used in a specific country. First thougt is to skip this column. Second thought: The value 99 is used for Europe but 99 is also used as a "error-default". So the 99 in this table shall be changed to something else.
- a_display_format_c – Actually this are there are four different format strings which are developed for Progress. This string shall be changed to a .net compatible format string.
- a_in_emu_l – In this case only the problem of the Euro and the european union is solved. But there are some other curreny unions like the East Caribbean dollar.
Multiple Currency Basics
Different Accountancy Curreny Types
Openpetra knows three different currency types.
- B is the base currency which shall be defined once at installation time and shall be used for the Lifetime of the accountancy. An Exception may be the change from "Deutsche Mark" to Euro but such changes are not handled in this document.
- I is international currency and this actually is the US$. This currency can be changed at any time and the only result is a set of management reports producing different numbers and different currency names. No official reports like a tax report uses this currency.
- T is a transaction currency which is different from B. The reason to handle such accounts is foremost a money cash in different currencies. A guest from England has paid a book in Germany using pounds or something else. Sometimes there may be a gift which is done in a non-B-currency.
More than 99% of all Transactions in an OM-office are B-transactions. Each Month the offices send its money to the different fields in the foreign countries and here a normal bank transaction is used. The source-office sends their money from a B-ledger, the bank changes this into the currency of the target-office and at this place it will be a B-Transaction too. No T-ledgers is involved in this process. So the T-ledgers are something like a millstone round the neck of this offices.
Common Mathematic Rules
Requirement: For all this transactions only the data type decimal is allowed.
Each national office has to manage its own book keeping process based on its own base currency or primary currency. Furthermore they can handle some assets defined in other currencies. In the very first step this is petty cash and this is the only form of foreign currencies which shall be managed by openpetra.
Requirement: Only one of the following transactions are allowed
- primary currency to primary currency ledger (B2B-Transfers)
- primary currency ledger to any secondary currency ledger and vice versa (T2B and B2T-Transfers)
The same Requirement is: Direct secondary currency to secondary currency transactions are forbidden (T2T-Transfers are forbidden)
For example in the German office, the primary currency is euro and the secondary may be the English pound and the Swiss franc. A transaction from the English pound to the Swiss franc is not allowed. This are two transactions.
In the finance market two different exchange rates are defined. In our example his is the rate for the exchange of the € to $ (exch€$) and this is the vice versa exchange rate to € (exch$€).
Requirement: In order to avoid rounding differences in a book keeping system holding the primary currency Euro, it is not allowed to calculate and to use the value of exch$€. Only the value of exch€$ is allowed to use. The value exch$€ is reserved for systems holding $ as its primary currency.
To be used:
- amountIn$ = amountIn€ * exch€$ and
- amountIn€ = amountIn$ / exch€$
Not to be used:
- exch$€ = 1 / exch€$
- amountIn€ = amountIn$ * exch$€
Requirement: A currency exchange rate has to be defined for each currency say exchPrimCurToSecCur
In the next step there exist a smallest currency value. In case of the euro and the dollar it is 1 cent, in case of CHF it is 0.05 cent.
Requirement: Any of this calculation shall be rounded to a multiple number of the smallest currency value after a division.
A german example may be the prices for fuel and one Liter may cost 1.329 Euro. The smallest currency value is 0.01 Euro and if you by 15 Liters, you have to pay 19.935 Euro which may be rounded to 19.94 Euro. This rounding is done before any other calculation.
- If you want to refuel two cars in two refueling processes you have to round two times, only if you refuel the cars in one step, the refuelling is done one time
Requirement: Only rounded numbers are allowed to be added up.
Requirement: Ledgers which represents a cash value only can handle multiple whole number amount of the smallest currency value.
- Actually you get 2,167.195,020 Russian Rubels for one euro. In this case you can define a multiplier to reduce the size of the number. For example you can set this value to 1,000.00 and then you can say that you get 2.167,195,02 (*1,000 Rubel) for one Euro.
Requirement: Each Currency shall handle it's own currency multiplier.
There is no value like "Not a Number" for a .net decimal value. The best choice in errors which will produce results useless for accounting is.
Requirement: In case of a critical error the result shall be Decimal.MaxValue.
Exchange from primary currency to a secondary currency or vice versa
Let someone exchange some money of primary currency into money of secondary currency. Independently if he uses cash or a bank account, he gets a transaction report holding the data for
- the money he has spent in primary currency
- the relevant exchange rate
- the sum of the exchange fee
- the money he has got in secondary currency
The exchange fee shall be accounted against a ledger like administration costs or else.
But in normal cases there is a difference between the internal value of exchPrimCurToSecCur and the value used in the transaction report. So the result will be an exchange rate profit or an exchange rate loss.
Requirement: The accounting software shall have the option to define a ledger for the exchange rate profit and a ledger for the exchange rate loss.
Open Question: One ledger for each foreign currency or one ledger for all?
Requirement: The exchange rate profit or the exchange rate loss of this transaction shall be accounted simultaneously if an exchange transaction is accounted.
Accounting of expenses and incomes
If someone spent some foreign currency money he gets a record holding a value based in the foreign currency. The cash ledger is defined in the foreign currency and the offset ledger in primary currency.
In the first case the amount can be accounted directly.
In the second case the amount can shall be converted into primary currency by using the value of the actual exchange rate and rounding to a whole number multiple of the smallest currency value.
Requirement: Expenses and incomes shall be accounted by using the actual exchange rate rounded to a whole number multiple of the smallest currency value.
Currency update
The currency rate shall be updated each month manualy or automaticaly by an office staff member. Any update implicitly creates an exchange rate profit or an exchange rate loss.
Requirement: By updating the exchange rate the exchange rate profit or the exchange rate loss value shall be calculated and accounted automatically
Accounting Processes
There are following different types of accounting processes.
- Someone changes money from the base currency to some other currency or vice versa
- Someone spent something in a foreigen currency
- The exchange rate will be upgraded
In all this cases the following process is required.
- Check if the balance sheet is in balance
- Account the bills and receipts or change the exchange rate
- Reset the balance into a balances state by accounting the differences against the accounts exchange rate profit or exchange rate loss
Actually there is no process required in Step 1. We allways can assume, that the balance sheet is in balance before an accounting process is done.
Country Specific Information
Switzerland
In switzerland there exists three main languages
- English (Culture: en-ch)
- French (Culture: fr-ch)
- German (Culture: de-ch)
This means that there are different types to present a currency value.
Base Currency Unit is the Swiss Franc and in normal cases you are speaking about 1 CHF (Sometimes 1 SFr. or shortly 1 Fr. is used. Allthough CHF is requrired by the ISO 4217 windows culture only knows SFr, but in order of keeping up an international understandability we require following:
Requirement: For the presentation of the Swiss currency CHF shall be used in the complete program.
1 CHF is divided into 100 Swiss Rappen.
For all payments in Switzerland the amount is rounded in 0.05 CHF, because the smallest coin is 5 Rp (0.05 CHF).
Requirement: If Petra calculates the value of an invoice it may be necessary to round up to 0.05 CHF.
In case of the financial accounting the amount from the voucher is used.
Payments from other countries are debit/credit on the banc-account in 0.01 and at the end of the year/quarter/month the bank debit the costs in 0.0x, so that the solde is 0.05.
This process is driven completely by the bank account statements. There is no need for an additionaly rounding of some values.
TODO-List
Overview
| Column Name | Status | ToDo |
|---|---|---|
| a_currency_code_c | Ok | Use unchanged |
| a_currency_name_c | Not Ok | Shall be prepared for international language support |
| a_currency_symbol_c | Not Ok | Change to correct currency symbols |
| p_country_code_c | Not Ok | Decide to remove the complete column or to repair the 99-entry for Europe |
| a_display_format_c | Not Ok | Update the format strings (*) |
| a_in_emu_l | Not Ok | Change to the possibility to add or remove a currency to any other monetary union |
| currencyCultureCode | To be added | Defines a reference to the .net-CultureInfo Class |
| smallestCurrencyUnit | To be added | Defines the smallest unit of the currency. All values have to be rounded down to this precision. |
| currencyMultiplier | To be added | Defines a multiplier to reduce the size of inflationary currencies |
| actualExchangeRate | To be added | Defines the actual exchange rate relative to the base currency |
| ??? | To be added | May be that it is a good idea to place the ledger numbers and cost centre numbers for exchange rate profits or exchange rate losses here |
(*) By updating the format strings the regional settings of windows shall be refered.
Using internal Windows functionality
Open Question
It is important to define good test cases. So let us have a look to an example.
| Description | Currency Value | Remarks |
|---|---|---|
| Start value | 123,4700 € | May be inserted by Keyboard |
| Faktor | 1,23450 | |
| Converted | CHF 152,4237 | |
| Rounded | CHF 152,40 | Smallest Size CHF 0.05 |
| Reconverted | 123,4508 € | |
| Reconverted & Rounded | 123,4500 € | Values are different |
In any cases the cost center value remains unchanged.
- 152,40 CHF has to be accounted agains the debit ledger in CHF
- Reconversion shows a loss of 2 Cent (a value of 123,45 €).
- 123,45 € has to be accounted against the credit ledger in €
- 0,02 € has to be accounted against the exchange loss ledger in €
Questions:
- Is this a correct handling?
- I'm looking for useful ledger numbers and cost centre numbers