Currency Principles

This section describes how Datafile Software foreign currency procedures operate.

In general, financial management reporting needs to have a common unit of measure if a clear picture of a company’s position is to be seen. It is usual, therefore, for transactions in different currencies also to be translated back to a common currency for reporting purposes.

Base Currency

We call the reporting currency the base currency — others also call it the homecurrency. You can set up your company to use any currency as the base currency, but once you have defined the base currency you cannot change it; everything you then do is related to that base currency. Clearly UK pounds are normally the base currency for a UK company. But if you operate in the oil industry, or are (say) the subsidiary of an American company, you might prefer to use US dollars as the base currency.

Dual Recording

To achieve the dual purpose of working in the currency of the account, yet to report in the base currency, Datafile Software records both the original currency values and their equivalents in the base currency, on every transaction. For the purpose of dealing with customers and suppliers, it is clear that you must deal with them in their own currency, and the ledgers work essentially in the currency of the account. For reporting and control purposes, all aspects are also recorded in the base currency.

Currency of the Account

Within Datafile Software ledger applications (sales, purchase, nominal) any account must be assigned to a particular currency. Once so defined, you can only post transactions in the currency of that account, although values are always held in both the currency of the account and in the base currency. Accounts and transactions in the base currency itself, therefore, effectively hold two sets of identical values.

Exchange Rate

This is the rate at which the conversion from the base currency is determined. For example, if UK pounds is the base currency, then the exchange rate for Euros might be €1.3935 to the UK £ — exchange rates are usually quoted to four places of decimal. Since every transaction maintains values both in the currency of the account and in the base currency, each transaction also holds the exchange rate value at which the conversion was calculated.

Which Exchange Rate?

Exchange rates in currency markets are constantly shifting. To use a different rate of exchange on every transaction can make it very hard for management to assess whether foreign activities are working well or not. One month could see a large profit, followed in the next month by a large loss, and all because the exchange rates fluctuated. What is really going on?

There is no real need for your transactions to be converted at the current market rate. For management purposes financial results are easier to interpret if you use a fixed exchange rate for as long as is practicable.

Holding Rate

Such a fixed rate is known as a holding rate, and is set by management to use as the internal exchange rate for a currency. In the usual operation of foreign currency, all transactions take place at the holding rate, unless specifically altered, irrespective of what the exchange rate might have been in the currency markets at the time.

The holding rate takes no account of whether you are "buying” or "selling” — the same rate is used whatever the transaction. It is only when real money is transferred between currencies that real external market rates of exchange are used.

Managers change holding rates only when these rates have become sufficiently out-of-line with the real world that the company results have become distorted. Holding rates might need to be changed once or twice in a volatile year.

Spot Rate

There are times, however, when it is appropriate to use a more real-world exchange rate. We use the term spot rate to refer to a specific exchange rate, based probably on the rates found in a daily newspaper or at a bank foreign exchange counter.

Examples of the need to use spot rates include certain statutory reports — such as VAT reports and Companies House returns — which must be presented at exchange rates determined by the bodies concerned.

Sales & Purchase Ledgers

All customer and supplier accounts are assigned to a specific currency. Transactions are then entered in the currency of the account. You are normally offered (all this is described in more detail below) the current holding exchange rate for the purpose of determining the equivalent base currency values, but otherwise transaction entry now proceeds in the currency of the account.

You can change the exchange rate for the transaction if you wish, but the only real reasons to do so would be if you’d taken out a futures contract to cover the value, or if the transaction was covered in some other way at a fixed exchange rate. In such cases you could use the actual rate, and "fix” the exchange rate here (see below under revaluations for the purpose of "fixing” the rate). Actually, there is no real need to do so, since any exchange issues will be resolved when the contract matures.

Allocation Actions

Invoices and payments are allocated together in the currency of the account in the usual way. If all the allocated transactions were recorded at the same exchange rate, then the base currency amounts should match too.

However, a foreign payment may have been banked through a base currency bank account, in which case the actual rate of exchange for the payment would have been recorded. If this is different to the rates on the invoice it pays, then although the currency amounts balance out, the base rate values do not, which cannot be right.

For example, if you raised an invoice for $10,000, against which you’ve received a cheque for $10,000, then clearly the customer’s account is clear. However, if your holding rate was $1.60, but the payment cleared through your bank at $1.6529, then the base currency values don’t balance. Your invoice was recorded at £6250, but the payment has only raised £6050, which appears to show that you’re still owed £200!

The allocation process resolves this difference by writing an extra ledger transaction for £200 — known as an exchange difference transaction (type 6).

Exchange difference transactions only arise when a set of debit and credit transactions clear each other in currency values, but not in the base currency. If the transactions are not allocated, no exchange difference can be determined. In the above example, should the payment not have been allocated to the invoice, then the currency balance on the account would show as zero, but the base currency balance would show £200 still outstanding.

Customer and Supplier Statements

Statements print, by default, in the base currency. When sending to your customer or supplier you will want to print in the currency of the account. On print a run-time parameter allows you to print in the currency of the account.

When printing in the base currency you would probably use these only for internal purposes. Exchange differences (and valuation differences, see below) are printed on these base currency statements, to give a truer picture of the amounts outstanding.

Payments in Different Currencies

It can happen that a payment against an account is made which is in neither the currency of the account nor the base currency. This can be recorded simply through the cashbook, but takes two steps if processed through the ledger. The technique is to use an "internal” bank account against which a payment is made in the currency of the account to clear the invoices paid, and against this is contra’d the payment in the third currency. The net effect must be nil, and any apparent balance in the "internal” bank account must be written off to the exchange differences account.

Debtor/Creditor Control Accounts

Since the sales and purchase ledgers have a mixture of accounts in different currencies, the related debtor and creditor control accounts in the nominal ledger are kept only in the base currency. (For those companies which wish to maintain controls for each currency, both the sales ledger and purchase ledgers allow individual control reports per currency, which process only those accounts marked with that currency.)

However, this raises the issue that the base currency values rest on the exchange rate used. Most outstanding transactions will have been based on current holding rates. If these are no longer acceptable — perhaps some rates are more than 10% away from current market rates — management may decide to change the holding rates.

Holding Rate Revaluations

To present a true picture of the company’s financial health in the base currency, the sales and purchase ledgers should then be revalued to reflect these changes. Certain nominal ledger accounts — foreign bank accounts and other asset and liability accounts held in foreign currencies — ought also to be revalued. We supply revaluation facilities in all the ledger applications.

Holding rate revaluations of the sales and purchase ledgers permanently change base currency values of unallocated transactions (allocated transactions no longer affect the balances) unless they were marked as "fixed” — a concept we introduced briefly above. A transaction might be "fixed” because some sort of currency deal is already in place for its settlement — perhaps through a reciprocal purchase, or because you have entered into a futures contract to hedge against currency variations. Since these transactions will be settled at an already agreed exchange rate, there is no need to revalue them to the new holding rates, and so they are ignored.

Revaluation recalculates the base value of any amount outstanding on all unallocated transactions; it does not alter the currency values. The transaction is rewritten with the new rate and base values, the base currency balance in the related account record is changed, the new exchange rate is stored, and an additional revaluation transaction (type 7) for this account is added to the transaction file. For reference, the original exchange rate which was used is held separately on the transaction — it is then clear if any revaluation has ever been done.

For example, suppose your US customers owed you $50,000. If your US holding rate were $1.60 to the pound, then the sterling equivalent is £31,250. If the dollar strengthens sufficiently over a period that you decide to change the $ holding rate to $1.50 to the pound, your US debtors of $50,000 now convert to £33,333. Suddenly you seem to have made £2,083 without lifting a finger! Yet your US customers still owe you exactly $50,000.

From the UK point of view, you have created a revaluation difference, purely a paper profit. You only see a real profit (or loss) when you receive dollars and physically change them into pounds sterling. It is as likely to be a loss if it is you who owe your US suppliers £50,000, or if the dollar were to weaken whilst your US customers still owe you money. This illustrates how your accounts can be distorted by fluctuations in exchange rates when viewed in one currency, and why the concept of a holding rate is so useful from a management accounting viewpoint.

 Note

Sometimes a financial controller needs to know the value of the ledgers under a different set of rates of exchange. For example, when preparing your accounts for filing at Companies House, there is a statutory requirement to use the exchange rates which were current at your financial year end. Regardless of your internal holding rates, therefore, you must revalue the appropriate assets and liabilities to these financial year-end rates for reporting in your accounts.

We allow you to maintain a set of spot rates, used to revalue temporarily the ledgers rather than at your current holding rates. However, there is a major difference in this type of revaluation exercise compared with a holding rate revaluation, in that it is not meant to be permanent. Indeed, outstanding transactions are not changed in any way. Special revaluation transactions are written (type 8) to show the revaluation which would result from the spot rates, and these get transferred to the nominal ledger as provisional transactions; that is to say, you expect to reverse them out in the nominal ledger — thus reverting back to holding rate values — after month-end.

Daily Spot Rate History

Certain specialised sectors need to work at exchange rates which are changed daily, if not more frequently. Typically these are organisations trading in commodities in futures markets, or even directly in foreign exchange. For them the profit on the deal may be solely the variation in the currency exchange rate, so the concept of holding rates does not satisfy their needs.

Datafile Software foreign currency has a daily spot rate feature which satisfies these particular needs. If you elect to use daily spot rates, then holding rates do not apply.

Sales Tax (VAT)

A particular currency issue surrounds UK sales tax (VAT) for companies who are using other than pounds sterling as their base currency — very common in the oil industry where the usual working currency is dollars — and raise VAT-able invoices in other than UK pounds. In these circumstances VAT Invoices must include the total values (goods, VAT, total) in UK pounds – the invoice documents generated through Datafile allow this option.

Nominal Ledger and Cash Book

As with the sales and purchase ledgers, each nominal and cash book account can be assigned to a specific currency. A transaction to any account must be in the currency of the account, although the recorded transaction has both currency and base currency values. However, journals can mix transactions to different currency accounts, just so long as the final journal balances in the base currency.

Since you can post to any period of the current year — and to any in the previous year if open — a currency transaction prompts with the holding rate which applied at the time that period was current. To achieve this, currency rates are kept for two years or more, just so that the right rates can be used.

Revaluations

It is normally only necessary to revalue a few specific balance sheet accounts such as stock and bank accounts, and this is achieved with an extra option in the nominal ledger other journal routines. Sales and purchase ledger control accounts must never be revalued in the nominal ledger, because the result of any revaluations to the sales and purchase ledgers are automatically passed through to the nominal ledger control accounts as part of the monthly ledger updates.

You would not revalue income and expense accounts, because their values are based on the historic rates you used, and reflect what actually happened then. It makes no sense to revalue them, since effectively that rewrites history.

Stock Control

The stock control system is designed to operate essentially in the base currency, although there is provision for some aspects of foreign currency within it. If a stock item is purchased from abroad through the purchase order processing system, then you may keep in the stock record a currency cost field, representing the price in currency at which you buy it, plus the currency code.

Stock transactions can also hold the currency cost, currency code, and the currency rate used in translating to the base currency cost value.

A foreign currency feature allows you to specify that the price quoted for a stock item is normally to be the base currency price times the currency rate. This, combined with judicious use of the discount matrix, can allow you comprehensive product pricing facilities when selling in other countries.

Order Processing

Both sales and purchase order processing cater for ordering in foreign currencies. An order is assigned a currency by virtue of the account against which it is raised, and all values are then assumed to be currency values. Acknowledgements, delivery notes and invoices are printed in the currency of the order. As with other systems, both currency and base currency values are maintained.

There is a currency issue which concerns partial deliveries, however. Should you permit this, then it is possible that invoices raised from the same order could be based on different rates of exchange. This is not necessarily a problem, other than that base currency values on order header and detail files may not relate back exactly to the currency values and the exchange rates shown.

Further, if you are using batch and serial number tracking, the cost value of identical items — being shown only at base currency values — purchased on the same order may show different costs. See also Document Design Manager below.

Stock Costs

You may need to give some thought to how you value stock items purchased from overseas. If you do not use purchase order processing, then you will receive stock direct into the stock system, in which case all you are asked for is the base currency cost to use. How you arrive at this is outside the computer system, therefore.

If you use the purchase order processing system, however, almost certainly you will book goods into stock through that system. The cost price of the item derives from the cost price on the supplier’s invoice, translated at whatever exchange rate you use. The first issue, then, is whether the holding rate is an acceptable rate to use, particularly if you are working to tight margins. You have the option to enter a specific exchange rate for each order instead, which may resolve any difficulty here.

However, goods which are imported often suffer other charges too, such as freight, insurance, and perhaps a Customs and Excise import duty. These costs are likely to be recorded in entirely separate parts of the system, not related to stock at all.

There are no hard and fast rules which we can offer you to handle this issue, although we describe the following approach as an example of how to resolve it. It is, of course, Datafile Software’s unique flexibility which allows you to design an approach which best meets your own particular circumstances.

Standard Freight and Duty Costs

A common accounting method to manage costs is to use standard costs. With this approach, a management-determined cost is associated with whatever the item is — in this case a stock item — and used throughout the system. Separately the actual costs which are incurred are accumulated. Once a year, or a quarter, or whenever is deemed appropriate, these two sets of values are compared and analysed.

If the answers are pretty close, then there may be no need to adjust the standard costs. Often, however, standard costs need some adjustment in the light of the experience which the exercise has provided.

You can include an extra data item in the stock master file to represent the extra costs of freight, insurance and duty. This might be a percentage of the cost, or a fixed value (the former is more common). For base-currency items this data item will be zero, but for foreign-sourced items an appropriate percentage or value can be entered.

As well as the normal cost value of the stock item, you can define a new calculated data item to hold valuation cost, or "true cost”, being the cost price plus the extra charge. This item is then used as the valuation item in stock valuation reports, and is also used (via the Copy Item facilities of the Database Manager and in documents which update stock and the ledgers) to determine the cost within any transaction.

If you want also to analyse these extra costs against the true costs which you have incurred, then you need either to involve the nominal ledger, or create a sub-system (using the ProFiler application generator) to collect and report the necessary data. The key to this approach is to isolate the freight, insurance and duty cost as a separate item, also copied into the relevant transactions, and match these to the actual costs.