Sage Financials & multi-currency: Part 2

Sage Financials & multi-currency: Part 2

In the last article I went over how Sage Financials handles raising an invoice at one exchange rate, and receiving payment at another rate.(See; Sage Financials & multi-currency). In this article I will cover Currency Adjustments.

Here is the problem:

If I raise a sales invoice for $100,000 on 5th Jan when the exchange rate is 1.28 this will show as £78,125 in the Debtor Control Account. But on the 31st Jan (the end of period) if the rate is now 1.41 the true value of your debtors is £70,921. If you don’t do an adjustment you are overstating your profit by £7,203!

Multi-currency and Un-Realised Gains/Losses

In the case above, you have an ‘unrealized loss’ of £7,203. This loss will only be realized when you receive the money*, at which point the exchange rate may again be different.

Lets look at the postings you need:

Raising the invoice

This is exactly the same as the previous example

Sales Invoice 5th Jan (Exchange rate 1.28) Db Cr
1100 Debtors Control Account (BS) $100,000 = £78,125
2200 Sales Tax Control Account (BS) Zero
4000 Equipment Sales (P&L) $100,000 = £78,125
Dimension tags:
Sales Invoice Number $100,000 = £78,125
Customer $100,000 = £78,125
Analysis Dimensions (eg Product) £78,125


Currency Adjustment

For the Currency Adjustment we need to reduce the Debtor control account, but leave the Fx balance in the Dimension Tag unchanged.

Adjustment to 31st Jan(Exchange rate 1.41) Db Cr
1100 Debtors Control Account (BS) £7,203
1108 Unrealized Currency Gains/Losses (P&L) £7,203
Dimension tags:
Sales Invoice Number £7,203
Customer £7,204

Entering these adjustments manually is clearly a complicated task, but luckily Sage provides a Currency Adjustment feature. This goes through all unpaid Sales & Purchase invoices and calculates the necessary adjustments. The only draw back is it only applies it at the date you run the currency adjustment. So this does not work if you want to apply your adjustment to a period end.
The good news is that Alpha Index have developed a currency adjustment plugin that does apply the adjustments to a period end.

The resulting trial balance is:

Trial Balance 31th Jan

Db Cr
Debtors (BS) £70,922
Sales (P&L) 78,125
Unrealized gain/loss (P&L) £7,203


Receiving payment

Suppose you receive payment on 7th March when the exchange rate is 1.35. The $100,000 you receive is now worth £74,074. Your realized loss is the value when you raised the invoice (£87,719) less the value when you received the payment (£78,125) which is £4,051.

As well as recording the realised loss in ‘Exchange Rate Variance’, we need to reverse out the Currency Adjustment, so the final posting looks like this:

Sales Receipt  7th March (Exchange rate 1.35) Db Cr
1100 Debtors Control Account (BS( $100,000 = £74,074
1200 USD Bank Account (BS) $100,000 = £74,074
7906 Exchange Rate Variance (P&L) £4,051
1108 Unrealized Currency Gains/Losses(P&L) £4,051
1100 Debtors Control Account (BS) £3,152
1108 Unrealized Currency Gains/Losses (P&L) £3,152
Dimension tags:
Sales Invoice Number $100,000 = £74,074
Customer $100,000 = £74,074
Sales Invoice Number  £3,152
Customer   £3,152

These postings are all handled using the standard Sage Financials ‘Customer Receipt’ journal. Posting rules compare the Payment Amount ($100,000) at the Initial Exchange Rate (1.28) against the Current Exchange Rate (1.35) to get the realized amount:
($100,000 / 1.28) – ($100,000 / 1.35) = £4,051

And using the of the Sales Invoice Number tag: compares the Base Balance (£70,921) with the Foreign Balance at the current exchange rate ($100,000 / 1.35 = £74,074) to get the unrealised adjustment .
(£70,921) – ($100,000 / 1.35) = £3152

And the resulting Trial Balance

Trial Balance 31th Jan

Db Cr
Debtors (BS) 0
Bank (BS) £74,074
Sales (P&L) £78,125
Unrealized gain/loss (P&L) 0
Exchange Rate Variance (P&L) £4,051

Words of warning

  • The posting rules for ‘Customer Receipt’ and ‘Supplier Payment’ which handle all these posting are complex. Be very careful if you make any modifications.
  • If you create a credit note from a Sales or Purchase invoice, the posting rules do not currently handle these fx postings. Best to pay the invoice and raise a separate credit note with a refund journal. The credit note and refund journal need the same date & same exchange rate.
  • You can see from the calculations that the Base Balance, Initial Exchange Rate and Foreign Balance in the invoice Dimension Tag are critical for correct postings. Running the ‘Rebuild Balances’ function before processing your Fx payments would be recommended.

* (NB:some people say an Fx transaction it is only realized when if is finally converted into GBP, but I believe the process described above is standard accounting practice)

I hope you found this useful, and please register to receive regular updates from Sage Financialsknowhow

Leave a Reply

Your email address will not be published. Required fields are marked *