EU Tax directives

A well-formed intra-community or export invoice in the European Union should have a certain remark for the issuer (exporter) to have a tax-free delivery, and for the recipient (importer) to apply the proper tax regime.

In the past we used to build complex hard-coded logic into our invoices in Dynamics AX: “if the destination delivery address is not our country but another EU member, and the goods are tangible, then print XXXX, otherwise YYYY” etc. It turns out we were reinventing the wheel. Pascal, a colleague of mine, discovered a function called Tax directive which emerged apparently in one of the AX2012 releases and remained in Dynamics 365 for Finance and Operations EE.

On the VAT codes screen there is a button called Tax directives. This button appears in legal entities with a primary address in one of the EU countries. The remark can be entered in the language of the prospective customer:

Now, if the parameter VAT specification in Sales ledger (en-us: Accounts receivable) > Setup > Forms > Form setup is set to either Registration currency or Registration and company currency, the text of the directive appears nicely in the middle section of the sales order invoice or the free text invoice:

The remarks are as flexible as the tax configuration itself, and the tax directives are additive, which allows for complex cases e.g. where services (=reverse charge) and goods (=IC delivery) are billed with one invoice.

There are limitations. The tax directives are not yet available on PSA (professional service automation) project invoices, in particular on the so-called “Project invoice without billing rules”. The remedy is the VAT exempt codes with their Translations. They are a bit more difficult to configure because not assigned to tax codes directly but to VAT group — Tax code combinations, but they fulfil the same purpose, here for the reverse charge remark in the German language:

You may vote for my product improvement suggestion to extend the VAT directives into the Project accounting realm sooner.

Blog series on VAT in the EU

On provisions, accruals and deferrals

It’s the holiday season, time to close the accounting year, accrue prepaid expenses in your books and build provisions for anticipated losses, reducing your corporate tax base and saving money.

If an AP invoice for goods or services has been received at or before the year end but relates to the next year or financial period, this prepaid expense is classified an asset in the balance and it should be removed from the current P&L:

Debit “7x Office rental” Credit “1x Cash”.
Debit “2x Prepaid expense” Credit “7x Office rental”.

If an invoice for anticipated expenditures or contractual obligations has not been received yet, but relates to the current accounting year, this provision is represented by a liability in the balance:

Debit “7x Audit” Credit “3x Provision”.

In “Dynamics 365 for Finance and Operations (EE)” there are in essence 3 methods to record these accruals, each with its own benefits and shortcomings: Reversing entry, Accrual schemes and Deferrals.

Reversing entry

The Reversing entry feature is good for provisions if they are not allocated over time / accounting months, but built on the 31th of December and dissolved immediately on the 1st of January.

In the basic General ledger > Journal entries > General journal, you just tick the Reversing entry checkbox and enter the Reversing date. On posting the journal, the system automatically adds a second journal line where it swaps the debit with the credit:
This simple feature fulfils the purpose of reducing the tax load in the financial year, but

it is not appropriate to allocate the expenditure over multiple periods, and
it books the second voucher in the future period. It is a good accounting practice to only keep one accounting period open, which is going to manifest itself in the error message “The financial period is closed for module Ledger on the date 01.01.2018”.

Accrual schemes

The Accrual schemes are better in the sense that they are capable of allocating prepaid expenses over time, but share many disadvantages with the Reversing entry:

accrual schemes are only available in the General Ledger journal and cannot be applied to an AP invoice;
they record transaction in future [closed] financial periods, and these transactions are final and cannot be changed;
an accrual scheme cannot reverse the provision entry at the end of the designated period.

The feature was made in an assumption that the expense to accrue is posted/debited in the same voucher, which in a large organization may defy the principle of segregation of duties as embodied by different forms to enter the invoice (AP Pending invoice form) and to accrue it (GL journal).
In the below example an General ledger > Journal setup > Accrual scheme

is triggered in the GL journal line by clicking Functions > Ledger accruals. On the screenshot below the accrual was applied to a contra-intuitive second line in the GL journal line. This was to trick the system into the desired Dr Expense Cr Prepaid posting (use the Transactions button in the Ledger accruals dialog box to preview the result):
The resulting GL voucher resembles a battlefield, but can be reduced to the following: 1/3 of the expense was recognized, 2/3 of the prepaid expense remained in the balance:

Deferrals

Since the early versions of Dynamics (namely, Axapta 3.0) there has been a sub-ledger called Deferrals in the Russian localized version: https://technet.microsoft.com/en-us/library/jj678354.aspx. It is free of the Accrual scheme handicaps, because it keeps the evidence of prepaid expenses and provisions in a separate ledger and does not impose GL transactions in future periods. It resembles the Fixed Assets module and supports 3 types of operations (transaction types):

The co-called ‘Receipts‘, i.e. records ‘zeroing out’ the expense amount in the AP invoice period,
‘Writing off‘, i.e. recurring prorated transactions allocated over a number of accounting periods and posted on the 1st of every period,
The so-called ‘Retirement‘ or Disposal, i.e. dissolving a provision.

Since AX2012 this sub-module has only been available in a legal entity with the primary address in Russia. In the latest releases of “Dynamics 365 for Finance and Operations (EE)” it became possible to extend all RDeferrals* application elements, add country codes and ‘jailbreak’ the desired function.
To implement the Deferrals module in Switzerland, I infused it with a multi-currency capability to store the currency code and the exchange rate in the Deferrals table, and populate the fields Currency, Exchange rate and the Secondary exchange rate in the journal lines. To increase productivity of the accountant the AP Pending invoice was enhanced to create Deferrals records automatically for every line (or expense account in the distribution). Furthermore, the Main account in the deferrals model table was customized to uptake the individual expense account from the AP invoice line and simplify the setup compared to the Accrual schemes: there should be no need to create an own Scheme for all 50+ expense accounts in question.

The process begins with the registration of a prepaid expense in the General Ledger > Common > Deferrals list. The new Deferrals [model] record becomes the status Scheduled i.e. awaiting the ‘Receipt’. The Receipt operations are proposed by the system in the General ledger > Journal entries > Deferrals journal, they intend to credit and hence neutralize the expense in the current accounting period:
The monthly allocation is performed in the similar manner where the user creates a new Deferrals journal and uses the button Group operations > Writing off in the Lines. This generates outstanding transaction proposals en masse for all accrual records in the status ‘In process’ up to the Transaction date specified:
N.B: In standard Dynamics due to some wicked design decision, the horizon for the transactions is the end of the month preceding the Transaction date. I.e. a Transaction date = 31.12.2017 brings transactions up to the 1st of November.

By the end of December 1/3 of the expense is recognized, and 2/3 of the prepaid expense remains in the balance:

Revenue recognition and deferred cost blog series

Electronic Reporting (ER) Cookbook

The Electronic Reporting module in Dynamics 365 for Operations, previously known as General Electronic Reporting (GER) was one of the coolest application inventions in Dynamics AX7. Developed apparently in my alma mater – the Dynamics development office in Moscow – this “ER” is a surprisingly powerful tool with an own in-built programming language for transformations. A consultant may configure any vendor payment format or any moderately complex GL export (such as the German GDPdU or French FEC) within days with literally no customizations, and test it ad hoc.

Over the last year, I implemented the British BACS18, the Swiss DTA, the Spanish SEPA sub-format and the North American ACH outbound payment format. While the AX wiki was a good start, the best way to learn the tool is practice. Below are some transformation code snippets that may help you along the steep learning curve.

Line counters

In the payment files, there are often record numbers. The ER element “Counter” is intended to be used, but it is incremented on every occurrence in the format. I.e. the counter has a global context, and the same count may not be used more than once in a row.
A total of records in a footer section is a special case: you may enumerate the lines with a Counter, then use a function of the Group by type over the data source to get a total record count, see the screenshot.

With 2 concurrent counters you have no other choice but to use the [real numeric] journal line number, and convert it into an integer:

NUMBERFORMAT(VALUE(model.Payments.PaymentIdentifications.SourceBusinessEntityDescription), "00000000000")
Beware of the major handicap of this trick: deleting payment journal lines creates holes in the numeration.

Simple transformation formula examples

Convert an amount into an amount with fixed 2 decimal places after the comma
NUMBERFORMAT(model.Payments.InstructedAmount, "#.00")
Remove the decimal separator, produce an amount in pence / cents, implemented as a subroutine (“transformation”) with a numeric parameter
REPLACE(NUMBERFORMAT(parameter,"0.00"), ".", "", false)
Remove line breaks from the postal address, and truncate to 70 characters
LEFT(REPLACE(model.Payments.Debtor.PostalAddress.AddressLine,"\n"," ",true),70)
Remove all non-numeric characters with a regular expression
REPLACE(model.Payments.DebtorAccount.Identification.Number, "[^0-9]", "", true)

Calculate a weighted checksum

The below expression adds the 9th checksum digit to a US ABA routing number, should the user only have entered the 8 significant digits. The RIGHT(NUMBERFORMAT(10 – VALUE(), “#”), 1) is a crude implementation of modulo 10.
CASE(LEN(parameter), 9, parameter, 8, parameter&RIGHT(NUMBERFORMAT(10-VALUE(RIGHT(NUMBERFORMAT( (VALUE(MID(parameter,1,1))+VALUE(MID(parameter,4,1)))+VALUE(MID(parameter,7,1)))*3+ (VALUE(MID(parameter,2,1))+VALUE(MID(parameter,5,1)))+VALUE(MID(parameter,8,1)))*7+ VALUE(MID(parameter,3,1))+VALUE(MID(parameter,6,1))), "#"), 1)), "#"), 1), RIGHT(parameter, 9))

Day number in a year (1-366)

The British BACS18 format contained a date format that really made me suffer: for the 1st of February 2016 it expected “ 16032” where 32 was the day count from the 1st of January. The below code took me a while; it used the Gregorian calendar formula and predated the fact that if you start counting months from March, the length of the months oscillates 31-30-31…
The repetitive IF(…) is an implementation of (month + 9) mod 12.
(VALUE(DATEFORMAT(parameter, "yyyy"))- ROUNDDOWN(IF(VALUE(DATEFORMAT(parameter, "MM")) >2, VALUE(DATEFORMAT(parameter, "MM")) -3, VALUE(DATEFORMAT(parameter, "MM")) +9)/10, 0) )*365 +ROUNDDOWN( (VALUE(DATEFORMAT(parameter, "yyyy"))- ROUNDDOWN(IF(VALUE(DATEFORMAT(parameter, "MM")) >2, VALUE(DATEFORMAT(parameter, "MM")) -3, VALUE(DATEFORMAT(parameter, "MM")) +9)/10, 0) )/4, 0) +ROUNDDOWN( (IF(VALUE(DATEFORMAT(parameter, "MM")) >2, VALUE(DATEFORMAT(parameter, "MM")) -3, VALUE(DATEFORMAT(parameter, "MM")) +9) * 306 + 5)/10, 0) +VALUE(DATEFORMAT(parameter, "dd")) - 1 -( (VALUE(DATEFORMAT(parameter, "yyyy")) - 1) * 365 + ROUNDDOWN( (VALUE(DATEFORMAT(parameter, "yyyy")) -1)/4, 0) + 305)
This function should work correctly for the next 83 years. We IT people think in shorter terms than the Roman Catholic Church.

Update 02.05.2017: the November 2016 update to Dynamics 365 for Operations features an undocumented function DAYOFYEAR, and the above code reduces to just
DAYOFYEAR(parameter)

Data model

To master the file format, you must first learn the data model, the ER’s configurable abstraction layer over the Dynamics database. Outbound vendor payments use the Payment model.

It provides generic payment attributes,

Attribute	Model node
Payment date (user parm or journal date)	model.Payments.RequestedExecutionDate
Amount	model.Payments.InstructedAmount
ISO payment currency (journal line currency)	model.Payments.Currency
Journal line number (see Line counters)	model.Payments.PaymentIdentifications.SourceBusinessEntityDescription
Payment / Note	model.Payments.PaymentsNotes
Voucher+RecId (a unique line ID)	model.Payments.PaymentIdentifications.EndToEndIdentification
Date of today	model.CreationDateTime

our bank details,

Attribute	Model node
Our name (company name)	model.Payments.Debtor.Name
Our full address (company address)	model.Payments.Debtor.PostalAddress.AddressLine
Name of our bank	model.Payments.DebtorAgent.Name
Our bank account number	model.Payments.DebtorAccount.Identification.Number
Our IBAN	model.Payments.DebtorAccount.Identification.IBAN
Routing number of our bank	model.Payments.DebtorAgent.RoutingNumber
BIC of our bank	model.Payments.DebtorAgent.BICFI

and their (beneficiary) bank details:

Attribute	Model node
Their name (vendor’s name)	model.Payments.Creditor.Name
Their full address (vendor’s payment address)	model.Payments.Creditor.PostalAddress.AddressLine
Name of their bank	model.Payments.CreditorAgent.Name
Their bank account number	model.Payments.CreditorAccount.Identification.Number
Their IBAN	model.Payments.CreditorAccount.Identification.IBAN
Routing number of their bank	model.Payments.CreditorAgent.RoutingNumber
BIC of their bank	model.Payments.CreditorAgent.BICFI

Dynamics 365 for Operations

EU Tax directives

EU Tax directives

Blog series on VAT in the EU

On provisions, accruals and deferrals

On provisions, accruals and deferrals

Reversing entry

Accrual schemes

Deferrals

Revenue recognition and deferred cost blog series

Electronic Reporting (ER) Cookbook

Electronic Reporting (ER) Cookbook

Line counters

Simple transformation formula examples

Calculate a weighted checksum

Day number in a year (1-366)

Data model

Electronic reporting blog series