On currency in credit card expense transactions – Part 2

In the previous blog On currency in credit card expense transactions – Part 1 we have explored a case where the credit card currency and the local accounting currency in D365FO did match. But what if the card is issued in a currency different from the accounting currency of the legal entity?

Case 2: credit card currency <> accounting currency

For example, Hungarian employees (the official currency of Hungary is the Forint, abbreviated as HUF) may be given credit cards billed in Euro, since they are surrounded by the Euro zone. If we follow the same logic as before, if such a credit card statement is expressed in EUR, then it must be posted in EUR into the AP subledger:

01.06.2019 Paid a hotel in Bratislava 500 EUR (the credit card provider applied the cross rate of 1.00)
04.06.2019 Posted in AP subledger as 500 EUR (the daily rate was 322 HUF/EUR)
30.06.2019 The credit card balance of June was withdrawn by the bank from the employee’s daily account in HUF @323,39 = 161 695 HUF. This is not recorded in D365FO for personal credit cards.
30.06.2019 An AP revaluation is performed in D365FO. The liability is now worth 161 450 HUF at the current exchange rate of 322,9 HUF/EUR
01.07.2019 The employee is reimbursed in the local currency by the employer with 161 450 HUF @322,9 HUF/EUR.

As a result, the employee will suffer a loss of 245 HUF but have to arrange himself/herself, as the benefits of travelling with an Euro card should overweigh any potential losses from the imperfect execution in D365FO by far.

Case 3: credit card currency <> accounting currency <> local currency

What if the trip was not to Slovakia (EUR) but to the Czech Republic who refused to adopt the Euro and retained their own Czech koruna (CZK)? Now all 3 currencies in the transaction are different:

The legal entity Accounting currency = HUF
The transaction Local currency = CZK
The Credit card Currency = EUR

The equation is still the same, the credit card balance is posted as a Forex liability in EUR. In the General Ledger voucher / accounting distribution the cross rate CZK -> HUF is kind of triangulated through the Euro: CZK -> EUR -> HUF where CZK -> EUR is the rate applied by the credit card institution. With that in mind, let us summarize the different business cases with the 3rd being the most generic and sound:

	Case 1	Case 2	Case 3
Credit card Currency	CHF	EUR	EUR
CC “Local” currency	THB	EUR	CZK
Accounting currency	CHF	HUF	HUF
Expense line	THB	EUR	CZK
Line itemizations	THB	EUR	CZK
Acc. distribution	THB	EUR	CZK*
GL voucher	Dr THB – Cr THB**	Dr EUR – Cr EUR	Dr CZK – Cr CZK**
AP subledger	CHF	EUR	EUR

(*) The accounting currency HUF equivalent i.e. ‘amount MST’ is evaluated through the rate EUR -> HUF (**) Note the currency mismatch between the AP and GL ledgers. This spoils the AP ledger account with transactions in foreign currencies and may potentially kick the subledgers off balance.

Expense management blog series

On currency in credit card expense transactions – Part 1

Introduction

The Expense management module in D365FO comes with an interface to credit card providers. I addressed this topic in an old blog of mine: visa-vcf-plain-text-credit-card-statement-into-dynamics-ax-with-xslt. Imported credit card transactions end up in the [Unattached] Credit card transactions list, ready to be taken over by the employee into an expense report.

There used to be a lot of misunderstanding with regards to the different currency rates and amounts and their meaning. Moreover, for a decade the Dynamics system had been posting the liability in the merchant transaction currency, which led to the expense report being split by currency code on posting into the AP ledger. It was wrong in many ways until we fixed it in a tremendous collaboration effort with Microsoft Support in 2018.

Case 1: credit card currency = accounting currency

In the expense report line there is an obligatory Payment method associated with the credit card type. It mandates how the employee is reimbursed for this part of the expense report. Typically, the Payment method (Expense management > Setup > General > Payment methods) is configured with the Offset account type = Worker which creates a liability against the AP account (vendor) associated with the employee.

If this liability is expressed in a currency different from the accounting currency of the legal entity, it starts accumulating exchange rate difference. If the payment takes place in a different accounting period, the employee may be reimbursed too much or too little. Indeed, the credit card it typically bound to a bank account of the employee, and the exchange rate charged by the bank to the cardholder is very unlikely to be the same as the current corporate exchange rate maintained in D365FO. Ergo, the AP transaction should be expressed in the accounting currency.

Take a look at the below example, the employee visited Thailand:

The Local currency code is not what you would expect but the currency the merchant charged the transaction in, here the Thai baht. Internally it is called EXCHCODE_LOCALCURRENCY.
Amount in local currency is the transaction amount in baht
The Currency code is the currency the credit card issued in (a.k.a. EXCHCODE_CREDITCARDCURRENCY), typically the currency of the state where the cardholder lives (here: the Swiss Franc)
Transaction amount is the amount the credit card issuer will settle against the bank account backing the credit card, i.e. an amount in CHF to be withheld from the cardholder at the end of the month. Internally it is called AMOUNT_CREDITCARDCURRENCY.

The exchange rate between the “Local currency” and the “Currency” must not follow the standard exchange rate setting in D365FO, but it should be what the credit card institute provides on the account statement. Take the next example (Expense management > Periodic tasks > Credit card transactions):

The exchange rate was developing over the same time span as shown below:
01.01.2018 2,995 CHF for 100 THB
01.02.2018 2,975 CHF for 100 THB
01.03.2018 3,001 CHF for 100 THB

The exchange rate applied by the bank in February was 15,11 CHF/ 500 THB = 0,03022 CHF/THB = 3,022 CHF for 100 THB. Clearly, it was higher than the average to the disadvantage of the employee, right as expected of a greedy bank.

What will you see in Dynamics 365 for Finance and Operations (version 8.x+, all bugs fixed) upon attaching the imported credit card transactions to an expense report and posting the same?

Expense report line currency: THB
Expense report line Transaction amount and currency: 77,67 CHF (!)
Expense report line Local amount: 2550
Accounting distribution currency and amount: 2550 THB
Expense report line Exchange rate: 3,046 CHF/100 THB, not editable (*)
GL Voucher transactions: Debit expense THB – Credit liability THB (**)
AP Subledger transaction: Credit liability CHF (!!)

(*) This is the unique exchange rate as charged by the credit card provider, and not the default legal entity exchange rate on the transaction date.

(**) The GL transaction currency does not match the subledger currency, as you can see. In theory, it is possible to have voucher transactions with the debit in one currency (THB) and a credit on another currency (CHF), as long as the equivalent in the local currency (CHF) matches and the voucher is in balance. However, it was not feasible for Microsoft to implement it in the subledger / accounting distribution programming model.

To be continued: Part 2

Expense management blog series

Configuring Austrian and Norwegian per diems in Dynamics 365

Building upon my insight into travel expenses and per diem calculation in Germany, let us consider Austrian and Norwegian per diems side by side. In both countries scaled rates per hour apply, and in both counties rules for the domestic travel and for traveling abroad differ. Both countries can be implemented in Dynamics 365 for Finance and Operations with literally no customizations at all!

In Austria, the first 3 hours of domestic travel are not reimbursed, but once the duration of the trip reaches 3 hours, the employee becomes 4/12 of the full rate of 26,40 euros. These 8,80 euros apply to a trip with a length between 3 and 4 hours, from the 4th full hour the rate increases to 5/12 and so on. From the 11th hour the full rate 26,40 applies. A multi-day trip from the 01.05.2018 10:00 to 03.05.2018 13:10 is divided into 24-hour periods + the number of remaining begun hours: 2 days + 4 hours = 2 + 4/12 of the daily rate = 2,3333 * 26,40 = 61,60 euros.
In Norway, a domestic trip starts monetizing after the first 6 hours, the employee becomes 289 NOK. Contrary to Austria, in Norway there are just 2 steps: one for 6-12 hours of 289 NOK and the full 537 NOK for anything between 12-24 hours. 24-hour periods are counted, but the last day of a multi-day trip counts as a full one as soon as the remainder exceeds 6 hours. I.e. a trip from 01.05.2018 10:00 to 03.05.2018 18:10 gives 2 days + ~8 hours = 3 daily rates = 3 * 537 = 1611 kroner. A Norwegian speciality is a higher overnight stay rate of 733 NOK, while no overnight is 537 NOK.

Should the employer or customer provide free meals, the daily rates begin to decrease. In Austria, a domestic lunch leads to 50% reduction and the dinner reduces the daily rate to zero: 50% – 50%. The percentages refer to the full rate of 26,40 EUR, and a single lunch reduces a short 3 hours trip to zero: 4/12 – 1/2 = 1/3 – 1/2; the per diem amount may not become negative, though.
In Norway, a breakfast gives 20% of reduction, a lunch counts 30% and a dinner is 50%: 20% – 30% – 50%. I assume the percentages apply to the respective rate of 289, 537 or 733. Let us put the numbers on a diagram (a non-overnight Norwegian stay is represented):

Foreign travel is peculiar: for Austria one meal abroad leads to no reduction, while 2 meals (lunch and dinner) reduce the foreign daily rate to 1/3 i.e. the reduction is never more than 66,67% per day. For Norway, even a hearthy breakfast abroad weighs less than a hyggelig breakfast at home: 10% – 40% – 50% 😉

Both countries maintain tables of detailed per diem rates for numerous destinations abroad, and account for some expensive capital cities.
In Austria the same X/12 rule applies to every destination abroad.
In Norway, the first 6-12 hours give 2/3 of the total foreign rate, and more than 12 hours give the full rate. This time, no exception is made for an overnight stay.
These rules can be visualized as follows:

Austrian configuration

The Minimum hours for per diem parameter is 3 hours (Expense management > Setup > General > Expense management parameters), Meal percent = 100, and Base per diem calculation on = 24 hour period.
The deduction for the breakfast used to be a grey zone in Austria: in the past there was an own percentage of reduction for breakfasts. This regulation was eased, and now only lunches and dinners count. In case of a travel abroad, one lunch or one dinner do nothing; only 2 meals lead to a reduction of 66,67%. I suggest using the mode Calculate meal reduction by = Number of meals per day, while preventing the breakfast entry by the Expense management > Setup > General > Expense report fields setup. The user should not be entering more than 2 meals per day.

There can be as many Per diem locations as there are distinct rates per country and city. The location “AUT” for the domestic travel features the following rates and tiers (Expense management > Setup > Calculations and codes > Per diems):

The Meal percent per number of hours refers to the portion of the full Meals rate of 26,40. The Per diem rate tiers apply to Both, i.e. to both the first day between 3 and 24 hours, and the last remainder of 3-24 hours. For any day (24 hour period, to be precise) in the middle, the General settings apply. The Percentage reduction for 1 meal and Percentage reduction for 2 meals should be tailored carefully, because the reduction amount may not exceed the given meal allowance amount and Dynamics gives an error message on the expense report screen: “Total meal reduction cannot exceed total per diem.”

Here is the result. A domestic business travel from 01.05.2018 10:00 to 03.05.2018 18:10 of 72,60 euros = 26,40 + 26,40 + 8,80 is reduced by 50% on the 1st day, by 100% on the 2nd day and by 8,80 euros on the 3rd day because there is nothing more to reduce there:

This amounts to 13,20 EUR.

The same trip to Sweden brings significantly more: 42,90 + 42,90 + 14,30 is reduced by 0% on the 1st day (one lunch alone does not count), 66,67% on the 2nd day and 0% on the 3rd day = 71,50 EUR:

Norwegian configuration

For Norway, the Minimum hours for per diem parameter would be 6 hours, while Calculate meal reduction by = Meal type per day, because the breakfast, lunch, dinner all lead to a reduction by a specific percentage (20% or 10%, 30% or 40% and 50%, respectively). Breakfasts should be enabled for entry. The Norwegian krone is a relatively light currency, the 0,01% precision of Dynamics’ configuration starts making a difference, and Per diem rounding needs to be set to Always round up, thanks to all the rates aligned to whole kroner.

In the Per diems configuration for the domestic location “NO“ I assume an overnight stay for every period longer than 23 hours, while the day of the departure is not overnight, i.e. the number of overnight stays is the number of whole 24 periods on site, and any last day longer than 6 hours gives 537 NOK:

With this setup a domestic business trip 01.05.2018 10:00 to 03.05.2018 18:10 which involves 2 overnight stays at the hotel and 2 breakfasts, 2 sponsored lunches and 1 sponsored dinner gives 733 + 733 + 537 reduced by 30%*733 on the 1st day, by 100%*733 on the 2nd day and by 50%*537 on the 3rd day = 781 NOK:

The above assumption with regards to the overnight stay may not always be correct, and this may require an additional virtual per diem location for Norway. When travelling abroad, there is no ambivalence, and the configuration reduces to the following (note the different reduction percentages 10-40-50):

On a domestic business trip from Norway to Sweden from 01.05.2018 10:00 to 03.05.2018 18:10 this evaluates to 750 + 750 + 750 reduced by 40% on the 1st day, by 100% on the 2nd day and by 50% on the 3rd day = 825 NOK: