On provisions, accruals and deferrals

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

  1. it is not appropriate to allocate the expenditure over multiple periods, and
  2. 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:

  1. accrual schemes are only available in the General Ledger journal and cannot be applied to an AP invoice;
  2. they record transaction in future [closed] financial periods, and these transactions are final and cannot be changed;
  3. 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:

Windows 10 Creators Update + Dynamics AX 2012 = High DPI

Windows 10 Creators Update + Dynamics AX 2012 = High DPI

Windows 10 Creators update comes with a high DPI support. It has a positive impact on Dynamics AX 2012 presentation. Right-click the Dynamics AX link, choose Compatibility settings, set …DPI settings = System (a new option in the Creators Update). For the first time in 5 years the menus are rendered without overlapping on high DPI monitors, here on my 4K display :

AX 2012 on Creators Update
The DPI is low but up-scaled, and you can use Dynamics AX 2012 along with modern applications which natively support high resolution displays.

P.S. For those of us who run the DAX server on a laptop consumer OS: do not forget to save the registry branch
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Dynamics Server]
The Windows updater deletes it, it always does, and the ax32 service does not start after the reboot.

Electronic Reporting (ER) Cookbook

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.

NACHA file format
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,

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

our bank details,

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

and their (beneficiary) bank details:

AttributeModel 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 bankmodel.Payments.CreditorAgent.Name
Their bank account numbermodel.Payments.CreditorAccount.Identification.Number
Their IBANmodel.Payments.CreditorAccount.Identification.IBAN
Routing number of their bankmodel.Payments.CreditorAgent.RoutingNumber
BIC of their bankmodel.Payments.CreditorAgent.BICFI