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.