“Shadow Accounting” in Dynamics AX using the example of silent factoring

28 Saturday May 2016

Posted by Ludwig Reinhard in Accounts Receivable

≈ Comments Off on “Shadow Accounting” in Dynamics AX using the example of silent factoring

Tags

Factoring, Sale of receivables, Shadow accounting

Within this post I want to demonstrate you the use of a shadow accounting system based on the example of silent factoring. What makes silent factoring special is the fact that a company sells its receivables, which consequently disappear from its books. At the same time the company still needs to manage and track the receivables and payments made by their customers in their (accounting) system.

How Dynamics AX can support you in implementing such a shadow accounting system will be exemplified next based on the following process flow:

Step 1: Post customer free text invoice for 100 EUR
Step 2: Transfer of the open customer position to the “shadow accounting book”
Step 2a: Process allocation request to balance original and shadow accounts
Step 3: Record the 90 EUR payment receipt of the factoring company for the sale of the receivables
Step 3a: Process allocation request to balance original and shadow accounts
Step 4: Customer transfers 95 EUR to our company’s bank account
Step 5: We transfer the customer payment received to the factoring company

The following illustration summarizes the different process steps and postings in T-account form.

Notes:

Balance sheet accounts are illustrated in blue color, profit & loss (P&L) accounts in red color and shadow accounts in black color.
The 4-digit main accounts used in this demonstration are shown in square brackets.
The numbers shown next to the amounts recorded on the different main accounts refer to the process flow steps shown above.
The process flow steps and the way how the shadow accounting books are illustrated in this blog do only represent one option how a shadow accounting system can be implemented in Dynamics AX.

Now let’s have a look at the different transactions and the setup required in Dynamics AX to implement such a shadow accounting system.

Step 1: Post customer free text invoice for 100 EUR
Initially, a free-text invoice for a total amount of 100 EUR is recorded in Dynamics AX. Recording this invoice generates postings on ledger accounts no. 1400 (Accounts Receivables) and ledger account no. 5000 (Sales revenue) within the original accounting books.

Step 2: Transfer of the open customer position to the “shadow accounting book”
The next process step involves the transfer of the open customer position to a dummy customer account. As the dummy customer account is setup with a summary account (no. 9499) that belongs to the shadow accounting system, the open customer position disappears from the company’s books.

The transfer of the open customer position to the shadow accounting books can be identified in the next screen-print by the green and grey highlighted lines.

A detailed investigation of the accounts illustrated in the above screen-print shows that the original and shadow accounting books do not match. That is, the total of all accounts in the original and shadow accounting books do not add up to zero.

Step 2a: Process allocation request to balance original and shadow accounts
What is missing in the previous screenshot is an expense transaction that offsets the reduction in the open customer position (receivable). The same holds for the transaction that is recorded in the shadow accounting book.
To get the missing transaction into Dynamics AX, the following allocation rule is setup:

The source of the allocation rule are the transactions recorded on main account no. 9499, which is setup as the summary account for the transactions on the dummy (shadow) customer account.
The same amount that is posted on account no. 9499 is also posted on main account no. 6250 (“destination”). The latter account is used for posting the missing expense transaction in the original accounting books.
The offset account for the transaction generated is main account no. 9999 from the shadow accounting system that ensures that also the shadow account book balance.

Processing this allocation rule results in the following voucher …

… and the following account statement.

The screen-print above shows that the original and shadow accounting books are now balanced.

Step 3: Record the 90 EUR payment receipt of the factoring company for the sale of the receivables
The third process step is related to the receipt of the amount that the factoring company pays us for the sale of the receivable. In the example used, a total of 90 EUR is paid by the factoring company and posted on our bank account. The offset account used is the dummy factoring account from the shadow accounting book. For details, please see the next screen-print.

After recording the money received from the factoring company, the original and shadow accounting books do – once again – not balance and show a total amount of +90 EUR respectively -90 EUR.

Step 3a: Process allocation request to balance original and shadow accounts
To align both accounting worlds – original & shadow accounting – another allocation rule is setup and processed. This second allocation rule uses the transactions recorded on the factoring bank account no. 1801 as the basis for generating a posting on the shadow clearing account no. 9999.

The offset account is the P&L account no. 6250 that was already used in the first allocation rule before. That is because the receipt of the money from the factoring company does not only result in an increase of your bank account balance but does also require a corresponding offset P&L account transaction. Otherwise the expense amount of 100 EUR recorded with the first allocation rule would not correctly represent the “loss” (10 EUR) that we make from selling a 100 EUR receivable for 90 EUR.

Based on the setup shown above, the second allocation rule results in the following voucher…

… that ensures that both accounting worlds do again balance.

(Note: If you post the transfer of the open customer invoice to the shadow accounting books (step 2) and the payment receipt from the factoring company (step 3) at the same time, the setup and processing of the first allocation rule is sufficient to get the original and shadow accounting books balanced. In the example used two allocation rules are required because of a timing difference between step 2 and step 3. Whether you will need to setup a single or several allocation rules thus depends on the specific organizational process flow in your organization).

Step 4: Customer transfers 95 EUR to our company’s bank account
After a while the customer finally makes a payment of 95 EUR to our ordinary bank account.

As we record this receipt on main accounts that do all belong to the original accounting book, both accounting worlds balance.

Step 5: We transfer the customer payment received to the factoring company
The last process step involves forwarding the money we received to the factoring company. As this final transaction involves once again only accounts from the original accounting system, everything balances. For details, please see the next screen-prints.

Summary
The shadow accounting example illustrated in this post can be used for several other accounting scenarios. The important thing to make this work is setting up and processing allocation rules. Whether one single allocation rule – that is processed e.g. at month-end – is sufficient or whether several allocation rules are required highly depends on your organizational process flows and time gaps that might exist between the different process steps.

Foreign currency revaluation

13 Friday May 2016

Posted by Ludwig Reinhard in Accounts Payable, Accounts Receivable

≈ 4 Comments

Tags

Accounts Payables, Accounts Receivables, Foreign Currency, Foreign currency revaluation

This post deals with the foreign currency revaluation feature available in the Accounts Payable (AP) module and illustrates the effect that different parameter settings have on the foreign currency revaluation calculation and ledger posting. Please note that the examples and explanations made in the following do also apply for the Accounts Receivable (AR) module as the foreign currency revaluation functionality in AR is basically identical to the one in AP.

The examples below are all recorded in the Contoso DEMF company that uses the EURO as accounting currency. All sample vendor invoices are recorded in USD in January and February 2022. For those months/days, the following currency exchange rates apply:

The post is separated into four parts. The first part starts with an analysis of the different currency revaluation methods. This part is followed by a subsection on the different financial dimension options available for foreign currency revaluation postings. Part 3 deals with the posting profile parameters and part 4 concludes this post by having a look at the different dates that can be selected when running the foreign currency revaluation process.

Part 1: Foreign currency revaluation method
Let’s start by having a look at the different currency revaluation methods (standard, invoice date and minimum) that can be selected in the foreign currency revaluation parameters form. For that purpose a vendor invoice with a total amount of 1000 USD has been recorded on 1 January when the exchange rate between the USD and EUR stood at 1,00.

In order to illustrate the different foreign currency revaluation method options, three currency revaluations will be run on 28 .January and 31. January respectively, where the following currency exchange rates between the USD and EUR apply:

The first currency revaluation is run on 28 January with the “Standard” method.

Because of the appreciation of the USD, an unrealized foreign currency loss of 250 EUR arises that is shown in the following revaluation report.

The unrealized loss is posted on the account for unrealized currency losses and increases the vendor liability expressed in EUR currency correspondingly.

Next, a foreign currency revaluation with the „Invoice date” method is run.

Please note that selecting this method does not allow you changing the date value shown in the “Date of rate” field. As the “Considered date” value can be changed, I entered 31 January to identify the effect that this change might have on the transactions generated.

Result:
The revaluation report and the corresponding voucher show that the invoice method reverses the previous foreign currency revaluation. If you have a look at the voucher date then you can identify that the date value entered in the “considered date” field is used as the posting date for the foreign currency revaluation voucher.

The third foreign currency revaluation is run with the “Minimum” method. This time, the considered date and date of rate values are set to 31 January.

Result:
This time, Dynamics AX does not record any foreign currency revaluation adjustment and does not create a voucher. That is because the “Minimum” method does not record a foreign currency gain as it would result in an unrealized exchange rate profit that some countries – such as Germany – do not allow to report in corporate financial statements.

Part 2: Foreign currency revaluation dimensions
After having a look at the different method options available for foreign currency revaluations, the financial dimensions parameter is investigated next. To do that, let’s first have a look again at the invoice voucher that has been recorded on 1 January.

The invoice voucher illustrated in the previous screen-print shows that the vendor invoice is recorded with the financial dimension “Business Unit 002”.

Against the background of the invoice voucher generated, let’s have a look at the foreign currency revaluation voucher that is generated when “None” is selected in the dimensions parameter field.

If the foreign currency revaluation process is started with this parameter setup, Dynamics AX shows the following message:

If you confirm this message with Yes and run the foreign currency revaluation, a voucher similar to the one shown in the next screen-print results:

As one might expect, the foreign currency revaluation adjustment is recorded without any financial dimension value.

To identify what difference the selection of any other dimension parameter makes, the previous foreign currency revaluation is first reversed by using the invoice date method shown above and then run again by selecting “Posting” in the dimension parameter field. Please see the following screen-print for details.

Result:
This time, the foreign currency revaluation voucher is generated with the financial dimension that has been used for posting the vendor invoice.

To see what difference the last dimension parameter (“Table”) makes, the previous foreign currency revaluation is cancelled again by using the invoice date method and run once again with the following settings:

Result:
This time, the foreign currency adjustment voucher is generated with the financial dimension “Business Unit 001”.

Please note that the financial dimension value “Business Unit 001” is taken from the vendor master record as exemplified in the next screen-print.

Part 3: Foreign currency revaluation posting profile
In this this part I want to focus on the difference that the selection of a specific posting profile makes to the foreign currency adjustment voucher. To do that, the previous foreign currency revaluation was first reversed by using the Invoice date method shown previously and then re-run with the following parameter settings:

Please note that the “FX” posting profile is setup with a different summary account.

Result:
Different from what one would expect from the parameter settings shown above, the foreign currency adjustment voucher still uses the same vendor summary account (No. 200100) for recording the unrealized foreign currency gain. Please see the next screen-print.

The underlying reason for this behavior is that the dimension setting “Posting” overwrites the posting profile parameter setting.

In order to verify this system behavior, the previous foreign currency revaluation is reversed (by using the Invoice date method) and re-run with “Table” selected in the financial dimensions field.

Result:
This time, the unrealized foreign currency revaluation gain is recorded on the summary account that has been specified in the “FX” posting profile.

Note:
Using a separate posting profile for recording unrealized foreign currency profits can help companies in two ways.
First, through an easy identification of a company’s foreign currency exposure and the related risks. That is, while the unrealized foreign currency gain/loss accounts do not differentiate between group internal and external currency risks, the use of a separate posting profile with vendor/customer group specific summary accounts for foreign currency adjustments, can help companies assessing whether or not the use of foreign currency hedging instruments might be beneficial. The consideration behind this statement is that only risks from external currency fluctuations should be hedged while group internal currency risks do not require costly hedging instruments.
Second, the separation of currency revaluation effects on separate ledger accounts can alleviate the financial consolidation process.

Part 4: Foreign currency revaluation dates
After reversing the previous foreign currency revaluation once again, a second vendor invoice for 2000 USD is recorded on 1^st February. On this date, the USD/EUR exchange rate stood at 1,00. For details, please see the next screen-prints.

With the two vendor invoices posted in Dynamics AX, a first foreign currency revaluation was run with the following setting:

Result:
Dynamics AX generates a foreign currency adjustment based on the exchange rate valid on 28 February (0,6 EUR/USD or 1,6667 USD/EUR). Because the considered date (31 January) is before the date the second vendor invoice was posted (1 February), only the unrealized foreign exchange rate gain for the first invoice is taken into consideration.

After reversing the previous foreign currency revaluation, the revaluation process is re-run with a considered date value that is after the date on which the second vendor invoice was recorded.

Result:
This time, both invoices are included in the foreign currency revaluation process for recording the unrealized foreign exchange rate gain.

[Invoice 1: 1000 USD, Invoice 2: 2000 USD => Total: 3000 USD * Exchange rate as of 28 February (1,6667 USD/EUR) = 5000 EUR => 2000 EUR unrealized foreign currency exchange rate loss]

Hope you found some useful information in this post. Till next time.

A note on vendor cash discount posting options

03 Tuesday May 2016

Posted by Ludwig Reinhard in Accounts Payable

≈ Comments Off on A note on vendor cash discount posting options

Tags

Accounts on the invoice lines, Cash discounts, Offset account

This post deals with the question how Dynamics AX decides which cash discount account to use when posting vendor cash discounts that get deducted when paying vendor invoices. Answering this question might seem straightforward especially for those of you who already worked with Dynamics AX for some time. Yet, recently – with AX2012 R3 – a new feature was added to the Dynamics AX cash discount form that offers new possibilities for recording cash discounts.

The feature I am referring to is a Dynamics AX public sector functionality that can generally be made available also for companies not operating in the public sector by activating the respective configuration key in the system administration module. If this configuration key is enabled you will find the following new parameter in the cash discount form that allows you specifying whether the cash discount account shall be taken from the cash discount form (as before) or from those accounts that are used when recording the invoice.

A. Overview
With this additional possibility in place I created the following graph that illustrates how Dynamics AX determines the ledger account taken for posting cash discounts.

What this graph tells you is that Dynamics AX uses the main accounts that were used when posting the original vendor invoice for posting the cash discount if the new parameter is set to “accounts on the invoice lines”.

If the new parameter is set to “main account for vendor discounts”, AX uses the ledger account from the cash discount form for posting the cash discount given that the original vendor invoice has been posted without sales tax.

If the original vendor invoice is posted with sales tax and if a vendor cash discount account is setup in the sales tax ledger posting form, Dynamics AX uses this account instead of the one from the cash discount form. In other words, the cash discount account setup in the sales tax ledger posting form overwrites the one setup in the cash discount form if the original invoice includes sales tax.

B. Setup
In order to show you the difference in activating the new parameter, two cash discount codes have been setup; one with the standard AX functionality that has already been available in prior Dynamcis AX versions (“cash discount A”) and one with the newly available parameter set to invoice lines (“cash discount B”). Please see the next screen-print for details.

As cash discount accounts can also be taken from the automatic posting accounts setup in GL, a separate vendor cash discount account (no. 520208) has been setup in this form to clearly identify situations where the vendor cash discount account is taken from the automatic accounts setup in GL.

What has been said for the automatic posting accounts in GL also holds for the vendor cash discount account setup in the sales tax ledger posting form. That is, in order to identify situations where the cash discount account is taken from this form, a separate ledger account (no. 520209) has been setup as illustrated in the next screen-print.

C. Demo transactions
Now let’s have a look at some sample transactions in Dynamics AX. With the setup illustrated in the previous chapter in place, the following four vendor invoices have been recorded in an ordinary vendor invoice journal in Accounts Payable.

The major differences in the four invoices recorded can be identified by the descriptive text that is added to each invoice line. That is,

the first invoice is posted with the first cash discount code setup (“cash disc A”) but without sales tax,
the second invoice is posted with the first cash discount code setup (“cash disc A”) and with sales tax,
the third invoice is posted with the second cash discount code setup (“cash disc B”) but without sales tax and
the last invoice is posted with the second cash discount code setup (“cash disc B”) without sales tax.

When paying those four invoices, the following vouchers are generated:

For the first invoice, the cash discount account is taken from the cash discount form.
For the second invoice, the cash discount account is taken from the sales tax ledger posting form as the second invoice has been posted with sales tax. (Please note that Dynamics AX splits up the total sales discount in a net and tax correction amount).
For the third invoice, the cash discount account is determined by the expense account that has been used when recording the vendor invoice
For the fourth invoice, the cash discount account is once again determined by the expense account that has been used when recording the vendor invoice.

The cash discount postings for the third and fourth invoice nicely illustrate the overwriting effect of the newly available cash discount parameter.

D. Split postings
Now let’s have a look at some split postings and how the new cash discount parameter deals with those transactions. To identify what AX is doing in case of split postings, the following two invoices have been recorded:

The fifth invoice is posted with the first cash discount code setup (“cash disc A”) but without sales tax and
the sixth invoice is posted with the second cash discount code setup (“cash disc B”) but without sales tax.

If those invoices are paid with the cash discount deducted, the following vouchers result:

For the fifth invoice, the cash discount account is – as usual – taken from the cash discount form and posted as a single amount.
For the sixth invoice that uses the new parameter, three cash discount postings are generated based on the expense accounts that have been recorded with the original vendor invoice. For details, please have a look at the next screen-print.

E. Other account transaction types
In this final sub-chapter I want to take a look at those invoice transactions respectively payments that do not use a ledger account as offset account.

E.1. Fixed asset transactions
The first transactions I want to take a closer look at are fixed asset transactions. Fixed asset transactions are “special” when it comes to cash discount postings as a separate fixed asset parameter exists that defines whether cash discounts shall be deducted from the fixed asset value or not. (Please see the next screenshot).

In order to identify the effect that the new cash discount parameter has on fixed asset cash discount postings, the subsequent transactions are first recorded with the fixed asset parameter activated and thereafter without having it activated.

E.1.1. Fixed asset transactions – fixed asset parameter “deduct cash discount” activated
To identify what difference the new cash discount parameter makes to fixed asset postings, two fixed asset invoices have been recorded; the first one with the ordinary cash discount code “A” and the second one with the newly available cash discount feature “B”. (Sales taxes are ignored for reasons of simplicity).

After paying the respective vendor invoices, the following vouchers result:

Payment voucher for the first fixed asset invoice
Payment voucher for the second fixed asset invoice

If you compare both payment vouchers, you will notice that the newly available parameter does not make any difference as the vouchers are identical (except for the amounts).

E.1.2. Fixed asset transactions – fixed asset parameter “deduct cash discount” not activated
Let’s now have a look at some similar fixed asset transactions with the fixed asset parameter “deduct cash discount” deactivated. To realize that, some similar invoices for two fixed assets have been recorded and been paid thereafter.

Outcome:

For the first vendor invoice that uses the “traditional” cash discount feature, the cash discount is now posted on the cash discount account that has been setup in the cash discount form. That is, the fixed asset value is – as one would expect – not influenced by the cash discount taken.
On the other hand, the cash discount for the second vendor invoice that has the new cash discount parameter activated is posted on the fixed asset ledger account. Yet, the transaction origin form indicates that there is no corresponding posting in the fixed asset module. As a result, a difference between the GL and fixed asset balances results!

E.2. Purchase order invoices
The last transactions I want to take a look at are cash discounts related to purchase order invoices. To compare what difference the new parameter makes to the cash discount postings for those kind of transactions I setup two vendors with the old (“cash disc A”) and the new (“cash disc B”) cash discount functionality. Example:

Thereafter, two identical purchase orders have been setup, invoiced and paid in order to compare the vouchers generated.

Outcome:

For the first purchase order invoice, Dynamics AX used – once again as expected – the ordinary cash discount account that is setup in the cash discount form.
The next screen-print shows the voucher generated for the payment of the second invoice. As you can identify from this screen-print, the cash discount taken reduces the inventory balance of the purchased items on the inventory account (no. 140200). Unfortunately, this transaction does not have any effect on the inventory module as the transaction origin window indicates. For that reason, a difference in the inventory balances in GL and the inventory module results.

Summary
To sum up, using the newly available cash discount functionality that allows recording vendor cash discounts on the ledger accounts that have been used when recording the original vendor invoice can be interesting for expense transactions that use a ledger account as offset account. That is because the new functionality allows you identifying the net effect of those transactions without having to add up amounts posted in different ledger accounts.

Yet, as soon as it comes to fixed asset or inventory related transactions, things can go terribly wrong; meaning that the new cash discount parameter can result in differences between GL and sub-ledger balances.