{"id":93,"date":"2018-08-23T13:57:53","date_gmt":"2018-08-23T12:57:53","guid":{"rendered":"http:\/\/sagefinancialsknowhow.com\/?p=93"},"modified":"2018-08-23T15:44:35","modified_gmt":"2018-08-23T14:44:35","slug":"sage-financials-multi-currency-part-2","status":"publish","type":"post","link":"https:\/\/sagefinancialsknowhow.com\/sage-financials-multi-currency-part-2\/","title":{"rendered":"Sage Financials & multi-currency: Part 2"},"content":{"rendered":"

\"Sage<\/p>\n

In the last article I went over how Sage Financials\u00a0handles raising an invoice at one exchange rate, and receiving payment at another rate.(See;\u00a0Sage Financials & multi-currency<\/a>). In this article I will cover Currency Adjustments.<\/p>\n

Here is the problem:<\/em><\/strong><\/h4>\n

If I raise a sales invoice for $100,000 on 5th Jan when the exchange rate is 1.28 this will show as \u00a378,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 \u00a370,921. If you don’t do an adjustment you are overstating your profit by \u00a37,203!<\/p>\n

Multi-currency and Un-Realised Gains\/Losses<\/h2>\n

In the case above, you have an ‘unrealized loss’ of\u00a0\u00a37,203. This loss will only be\u00a0realized\u00a0<\/em>when you receive the money*, at which point the exchange rate may again be different.<\/p>\n

Lets look at the postings you need:<\/p>\n

Raising the invoice<\/h4>\n

This is exactly the same as the previous example<\/p>\n\n\n\n\n\n\n\n\n\n\n
Sales Invoice 5th<\/sup>\u00a0Jan (Exchange rate 1.28)<\/em><\/strong><\/td>\nDb<\/td>\nCr<\/td>\n<\/tr>\n
1100 Debtors Control Account (BS)<\/td>\n$100,000 = \u00a378,125<\/td>\n<\/td>\n<\/tr>\n
2200 Sales Tax Control Account (BS)<\/em><\/td>\n<\/td>\nZero<\/td>\n<\/tr>\n
4000 Equipment Sales (P&L)<\/em><\/td>\n<\/td>\n$100,000 = \u00a378,125<\/td>\n<\/tr>\n
Dimension tags:<\/em><\/span><\/td>\n<\/td>\n<\/td>\n<\/tr>\n
Sales Invoice Number<\/span><\/td>\n$100,000 = \u00a378,125<\/span><\/td>\n<\/td>\n<\/tr>\n
Customer<\/span><\/td>\n$100,000 = \u00a378,125<\/span><\/td>\n<\/td>\n<\/tr>\n
Analysis Dimensions (eg Product)<\/span><\/td>\n\u00a378,125<\/span><\/td>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

Currency Adjustment<\/h3>\n

For the Currency Adjustment we need to reduce the Debtor control account, but leave the Fx balance in the Dimension Tag unchanged.<\/p>\n\n\n\n\n\n\n\n\n
Adjustment to 31st<\/sup>\u00a0Jan(Exchange rate 1.41)<\/strong><\/em><\/td>\nDb<\/td>\nCr<\/td>\n<\/tr>\n
1100 Debtors Control Account (BS)<\/td>\n<\/td>\n\u00a37,203<\/td>\n<\/tr>\n
1108 Unrealized Currency Gains\/Losses (P&L)<\/td>\n\u00a37,203<\/td>\n<\/td>\n<\/tr>\n
Dimension tags:<\/em><\/span><\/td>\n<\/td>\n<\/td>\n<\/tr>\n
Sales Invoice Number<\/span><\/td>\n<\/td>\n\u00a37,203<\/span><\/td>\n<\/tr>\n
Customer<\/span><\/td>\n<\/td>\n\u00a37,204<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

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\u00a0at the date you run the currency adjustment<\/em>. So this does not work if you want to apply your adjustment to a period end.
\nThe good news is that Alpha Index have developed a\u00a0currency adjustment plugin<\/a>\u00a0that\u00a0does\u00a0<\/em><\/strong>apply the adjustments to a period end.<\/p>\n

The resulting trial balance is:<\/p>\n\n\n\n\n\n\n
\n

Trial Balance 31th<\/sup>\u00a0Jan<\/strong><\/em><\/h4>\n<\/td>\n

Db<\/td>\nCr<\/td>\n<\/tr>\n
Debtors (BS)<\/td>\n\u00a370,922<\/td>\n<\/td>\n<\/tr>\n
Sales (P&L)<\/td>\n<\/td>\n78,125<\/td>\n<\/tr>\n
Unrealized gain\/loss\u00a0(P&L)<\/td>\n\u00a37,203<\/td>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

Receiving payment<\/h3>\n

Suppose you receive payment on 7th<\/sup><\/em><\/strong>\u00a0March when the exchange rate is 1.35. The $100,000 you receive is now worth\u00a0\u00a374,074. Your\u00a0realized\u00a0<\/em>loss is the value when you raised the invoice (\u00a387,719) less the value when you received\u00a0the payment (\u00a378,125) which is \u00a34,051.<\/p>\n

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:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Sales Receipt\u00a0 7<\/strong><\/em>th<\/sup><\/em><\/strong>\u00a0March (Exchange rate 1.35)<\/strong><\/em><\/td>\nDb<\/td>\nCr<\/td>\n<\/tr>\n
1100 Debtors Control Account (BS(<\/td>\n<\/td>\n$100,000 = \u00a374,074<\/td>\n<\/tr>\n
1200 USD Bank Account (BS)<\/em><\/td>\n$100,000 = \u00a374,074<\/td>\n<\/td>\n<\/tr>\n
7906 Exchange Rate Variance (P&L)<\/em><\/td>\n\u00a34,051<\/td>\n<\/td>\n<\/tr>\n
1108 Unrealized Currency Gains\/Losses(P&L)<\/em><\/td>\n<\/td>\n\u00a34,051<\/td>\n<\/tr>\n
1100 Debtors Control Account (BS)<\/td>\n\u00a33,152<\/td>\n<\/td>\n<\/tr>\n
1108 Unrealized Currency Gains\/Losses (P&L)<\/em><\/td>\n<\/td>\n\u00a33,152<\/td>\n<\/tr>\n
Dimension tags:<\/em><\/span><\/td>\n<\/td>\n<\/td>\n<\/tr>\n
Sales Invoice Number<\/span><\/td>\n<\/td>\n$100,000 = \u00a374,074<\/span><\/td>\n<\/tr>\n
Customer<\/span><\/td>\n<\/td>\n$100,000 = \u00a374,074<\/span><\/td>\n<\/tr>\n
Sales Invoice Number<\/span><\/td>\n<\/td>\n\u00a0\u00a33,152<\/span><\/td>\n<\/tr>\n
Customer<\/span><\/td>\n<\/td>\n\u00a0\u00a0\u00a33,152<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

These postings are all handled using the standard Sage Financials ‘Customer Receipt’ journal. Posting rules compare the Payment Amount ($100,000) at the\u00a0Initial Exchange Rate<\/em>\u00a0(1.28) against the\u00a0Current Exchange Rate<\/em>\u00a0(1.35) to get the realized amount:
\n($100,000 \/ 1.28) – ($100,000 \/ 1.35) = \u00a34,051<\/p>\n

And using the of the Sales Invoice Number tag: compares the Base Balance (\u00a370,921) with the Foreign Balance at the current exchange rate ($100,000 \/ 1.35 =\u00a0\u00a374,074) to get the unrealised adjustment .
\n(\u00a370,921) – ($100,000 \/ 1.35) = \u00a33152<\/p>\n

And the resulting Trial Balance<\/p>\n\n\n\n\n\n\n\n\n
\n

Trial Balance 31th<\/sup>\u00a0Jan<\/strong><\/em><\/h4>\n<\/td>\n

Db<\/td>\nCr<\/td>\n<\/tr>\n
Debtors (BS)<\/td>\n0<\/td>\n<\/td>\n<\/tr>\n
Bank (BS)<\/td>\n\u00a374,074<\/td>\n<\/td>\n<\/tr>\n
Sales (P&L)<\/td>\n<\/td>\n\u00a378,125<\/td>\n<\/tr>\n
Unrealized gain\/loss\u00a0(P&L)<\/td>\n0<\/td>\n<\/td>\n<\/tr>\n
Exchange Rate Variance\u00a0(P&L)<\/td>\n\u00a34,051<\/td>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Words of warning<\/h3>\n