Inventory value in foreign prepayment scenarios

  • Post category:Accounting
  • Post comments:1 Comment
  • Reading time:8 mins read

I have this client who operates in very specific conditions: majority of their vendors are foreign companies which invoice them in a foreign currency (USD) and almost invariably ask for at least 50% prepayment.

NAV can handle prepayments and foreign currencies like a charm—the issue lies elsewhere: the fluctuations of currency exchange rate can easily cause real and tangible losses.

Even though prepayment invoice is fully closed by a prepayment applied against it, the actual costs of goods is not calculated from prepayment invoice, but from the actual invoice. And if there was difference between currency exchange rate at prepayment and invoicing dates, the inventory value reflects the actual invoiced value (instead of the prepaid value), there is currency exchange gain/loss which is fictitious, but taxable.

Thankfully, there are ways to avoid this.

I wrote an article on MSDynamicsWorld.com about how currency exchange gains and losses reflect on business. In short, if you get invoiced 100 USD at a rate of 5, and you pay this at a rate of 4, then you have realized 100 in foreign exchange gains, which is treated as taxable revenue.

Why?

Invoice creates a liability, which has to be posted in your local currency. At the time of invoicing, you owed your vendor 500 LCY. If you pay only 400 LCY to settle this invoice, thanks to the double-entry accounting system, you have to put the difference of 100 LCY somewhere, and you put it into your exchange gains account.

Let’s dissect the hippo (if you don’t know what the hippo is, read Chapter 5 of Dave’s and my book).

Posting of an invoice will credit 500 LCY in vendor liability (the balance sheet part of your general ledger), and also debit 500 LCY in expenses (the P&L part of your general ledger).

Posting of a payment will credit (i.e. decrease) 400 LCY in your bank account, and will debit the vendor liability by 400 LCY (both are balance sheet). However, as far as your vendor is concerned, they are alright, because they got their 100 USD. However, you have an issue, because your books show that you actually owe them another 100 LCY. Which you actually don’t.

Here application steps in. When you post the application of the payment to the invoice, you debit your vendor liability account (the balance sheet one) by 100 LCY, and you balance it by crediting the exchange gains account (the P&L part of the G/L) by 100 LCY.

This leaves you with 100 LCY in your bank account, which you have, but shouldn’t have had. This is your balance sheet, your asset, so the tax authorities don’t care. But this also leaves you with 100 LCY in revenue, and those folks do care about that, big time.

That’s why exchange gains are taxable revenue.

And now let’s return to my client who mostly prepays for goods. For them, any currency difference is actually inventoriable.

There are two ways to book currency difference as inventory cost in NAV.

A. Use the Change Exchange Rate functionality

When you post the prepaid invoice, and pay for it, and then post the invoice, your (pre-)payment will effectively be applied to your invoice, causing currency exchange gain/loss. To avoid this, you could post your invoice at the same currency exchange rate you used when posting the prepayment. To do that, just go to the Foreign Trade tab, and click the assist button in the Currency Code field. Then change the currency, and there you go.

The issue with this approach is that if you invoice multiple receipts which were prepaid with multiple prepayments, you can only have one exchange rate per document, which means you’ll have to do a manual calculation to actually end up with zero exchange rate gains/losses.

This approach will result in zero turnover in exchange gains/losses accounts, but the document exchange rate will be an approximation. You actually have to calculate it as a weighted average of all exchange rates of the original prepayment invoices.

B. Use Item Charges

Or, alternatively, you can set up some item charges which you use to book the differences. You still have to do calculations, but you end up clearly knowing what part of item cost structure comes from currency exchange rate difference.

To achieve that, you just need a simple setup:

  • G/L Account for internal invoicing purposes: this account will be used as both the payables account and purchases account, and will always have 0 balance.
  • Gen. Bus. Posting Group for internal invoicing purposes (call it INTERNAL)
  • Gen. Prod. Posting Group for posting currency exchange gains into inventory (call it GAINS)
  • Gen. Prod. Posting Group for posting currency exchange losses into inventory (call it LOSSES)
  • Vendor Posting Group for internal invoicing purposes (call it INTERNAL)
  • Vendor for internal posting purposes.
  • Item Charge for posting gains into inventory (call it GAINS)
  • Item Charge for posting losses into inventory (call it LOSSES)

Make two General Posting Setup lines, one for INTERNAL/GAINS, and another one for INTERNAL/LOSSES. On both lines, set the Purch. Account and Purch. Credit Memo Account to the internal invoicing account. Then for INTERNAL/GAINS set the Direct Cost Applied Account to exchange gains account, and for INTERNAL/LOSSES set it to exchange losses account.

For Vendor Posting Group INTERNAL set the Payables Account to the internal invoicing account.

Configure the item charges GAINS and LOSSES to use the Gen. Prod. Posting Groups GAINS and LOSSES, respectively.

For internal invoicing vendor, set the Vendor Posting Group to INTERNAL, and Gen. Bus. Posting Group to INTERNAL. Set VAT Bus. Posting Group to a zero-VAT posting group, preferably one which is not included in your VAT statement setup. Also, needless to say—the currency code for this vendor is empty!

When you pay the original invoice, and apply the payment to that invoice, you’ll get some exchange rate difference which was posted automatically by NAV into your exchange gains/losses accounts, and balanced against payables account. You now need to move that from gains/losses accounts into inventory account.

To do that, first determine if you have gains or losses. If you have gains, create a new purchase credit memo; if you have losses, create a new purchase invoice. The vendor should be the internal invoicing vendor. Insert one line, type is Charge (Item), No. is GAINS or LOSSES. Set Quantity to 1, and Direct Unit Cost to the total amount of exchange gain or loss. Call the Item Charge Assignment function, and Get Receipt Lines. Select the receipt lines which you received from the original prepaid order. Suggest Item Charge Assignment by amount.

And just post.

This will debit and credit (at the same time) the internal invoicing account for the LCY amount of the exchange rate. Since it’s the balance sheet account, you are only interested in the total amount, which is going to be 0 after this transaction, meaning that you didn’t affect anything. At the same time, your inventory account will be credited and exchange gains account debited, or your inventory account will be debited and exchange losses account credited, depending on whether it was gains or losses that you were settling.

Next time you call the Adjust Cost-Item Entries batch job, your cost for the item will be adjusted and will reflect the actually prepaid value of the item, and not the invoiced value, and you have effectively introduced the exchange gains/losses as inventoriable cost component.

DISCLAIMER

I am not an accountant or an auditor, and I cannot guarantee for this solution. You use this solution entirely at your own risk. This solution may not be applicable to your country, and there may be legislation or accounting rules or practices which explicitly prohibit this. Before trying this at home, please check with your auditor first.

Vjeko

Vjeko has been writing code for living since 1995, and he has shared his knowledge and experience in presentations, articles, blogs, and elsewhere since 2002. Hopelessly curious, passionate about technology, avid language learner no matter human or computer.

This Post Has One Comment

Leave a Reply