Dynamics 365 Business Central – Why didn’t the Reverse Transaction function in the General Ledger Entries page reverse all my General Journal?

Introduction

A client reported that when reversing a large journal via the “Reverse” option in the “General Ledger Entries” page only part of their original General Journal was reversed?

This stumped me and forced me to dig deeper into how the journal was posted. On investigation it seems the journal lines on the journal had been grouped, and each group has been given a different “Transaction No.” in the G/L Entry table. Then, when the reversal option had been selected, only those lines with the same “Transaction No” had reversed.

Its the first time I’ve come across this behaviour in one distinct journal, so in this post I’ll demonstrate how this happened and the correct way this should have been reversed. (i.e. I’d expect this for different document numbers but not the same document number)

The Journal

The clients journal contained many lines however I can recreate the scenario with a much simpler journal.

Below is a journal with five lines. All the lines have the same Posting Date and Document number and the journal balances to nil. This is essentially one journal which we want to post as one unit.

I’ll now post the journal and go and view this in the General Ledger Entries page. I filter on the document number and I can see the full journal. This is great and just what I expect 🙂

The Reversal

I now realise I’ve made a mistake and want to reverse the whole journal. Therefore, while still in the General Ledger Entries page, I select the top line and choose “Process > Reverse Transaction” from the menu however it only gives me the option to reverse a portion of the journal? (the first three lines of the original journal)

So what has happened here? Why is the system not offering to Reverse all the entries on this Document Number?

The Transaction No. column

As shown on the screen shot above it seems the “Reverse Transaction” option pulls back the G/L Entries based on a “Transaction No.”?

I’ve never seen this column before, and when I try and add this via personalisation to the “General Ledger Entries” page, its not an available column?

I’ll therefore dig deeper and look at this document number directly in the the G/L Entry table, and there I can see the journal has been grouped and each group has been given a separate “Transaction No.”

So now the question is why has a journal with the same Posting Date and Document Number been broken up and given two separate “Transaction No.”?

The answer lies in how I posted the journal. If I look back at my original journal I’d inadvertently balanced the journal in two sections.

It seems even though the journal lines have the same posting date, and more importantly the same document number, the system gave these a different “Transaction No” based on how its balanced part way through.

Finally, it seems the “Reverse Transaction” function pulls back the entries based on this “Transaction No.” which causes a complication if I wanted to reverse the whole Document Number.

Quickly Reverse all Entries

So how do I reverse all the lines?

In my example its quick and easy as the journal is small. I could stay in the “General Ledger Entries” page and select one of the first three lines, and click “Reverse Transaction” and then select the fourth line and use the same “Reverse Transaction” option. However what happens if the journal contained many lines that had been grouped into lots of different “Transaction No.”?

In this scenario you can reverse via the G/L Registers page.

As the G/L Register contains all the G/L entries in my journal I just click click “Reverse > Reverse Register” as per below

This gives me all the G/L Entries in my journal that I can then reverse.


Conclusion

It was great investigating and finding out more about how Business Central works in the background. In some ways you could argue this behaviour is advantageous, as you have the opportunity to only reverse part of one huge journal.

My biggest takeaway from this is when reversing an entry via the “Reverse Transaction” in the “General Ledger Entries” page ensure all the expected lines are being reversed.

Also, if you do want to reverse all the journal lines consider using the “Reverse Register” function on the “G/L Register” page rather than “Reverse Transaction”.

Thanks for reading!

Dynamics 365 Business Central – How to Post a VAT only Invoice

Although not a regular occurrence, from time to time users need to post a VAT only invoice onto the sales or purchase ledger. If this happens below are the steps you can take to do this:

Please note these steps are specifically for a Purchase Invoice however it should work the same for a Sales Invoice.

Step 1: Add a new VAT Product Posting Group

First go to “VAT Product Posting Group” and add a new VAT Product Posting group for FULL VAT as per below

Step 2: Configure VAT Posting Setup

Next we need an additional FULL VAT entry in the “VAT Posting Setup” matrix as per the screen shot below. I’ve included FULL VAT twice, once with a blank “VAT Bus. Posting Group”, and once with a “VAT Bus. Posting Group” of DOMESTIC.

Please note I’ve set the system up this way so if entering a VAT only invoice via a “Purchase Journal” I only have to populate the “VAT Prod. Posting Group” whereas if I were to enter the invoice via “Purchase Invoice” its likely the supplier would have a “VAT Bus. Posting Group” of DOMESTIC. Setting the system up this way covers both scenarios.

Step 3: Entering the VAT only invoice via a “Purchase Journal” window.

In this example I’m entering a £100.00 VAT only invoice via the “Purchase Journal” window.

To do this I’ve entered the invoice details as follows:

Just to confirm, I’ve selected a document type of “Invoice” and the Vendor account of “1000”. Then for the “VAT only” element to work I’ve selected the following for the various columns:

Bal. Account Type = G/L Account

Bal Account No. = 46330 (this is the VAT account specified in the VAT Posting Setup)

Bal. Gen Posting Type = Purchase

Bal. VAT Bus. Posting Group = BLANK (I could select DOMESTIC as well if I like)

Bal. VAT Prod. Posting Group = FULL VAT

Please note using the “VAT Posting Group” setup outlined in my screen shot above a combination of a blank “VAT Bus. Posting Group” and a “VAT Prod. Posting Group” of “FULL VAT” equates to 100% full VAT

If I now preview the posting on this transaction I can see 3 G/L Entries and one VAT entry:

If I drill down on the G/L Entries this shows the full amount posting to the VAT control account specified in the VAT Posting Setup. (as this is a Purchase Invoice the credit side goes to the Creditors Control Account):

And when I drill back on the VAT entries I can see a VAT entry for the full amount:

Step 4: Entering the VAT only invoice via the “Purchase Invoice” option

Once in the “Purchase Invoice” page I enter the invoice line as per below.

To confirm, I’ve entered the line as per below:

Type = G/L Account

No. = 46330 (this is the VAT account specified in the VAT Posting Setup)

Vat Prod. Posting Group = FULL VAT

Now when I check the “Statistics” on the invoice it shows the full amount to VAT:

Also, when I preview the posting I can see G/L entries to the VAT account and a VAT Entry of the full amount:

Step 5: Ensure FULL VAT is included on the VAT Statement

Once you have posted the transaction you should also ensure the new “VAT Prod. Posting Group” (i.e. FULL VAT) is included on the VAT statement so any transactions posted to this are picked up correctly when reporting the VAT.

As you can see below I’ve included this in the Purchases section:

I hope this helps anyone who needs to post a VAT only invoice in Dynamics 365 Business Central.

Thanks for reading!

Dynamics 365 Business Central – A walk through of Unit Cost, FIFO Items and the Automatic Cost Adjustment option

Introduction

Recently I’ve been looking into how the “Unit Cost” is calculated for items using the “FIFO” costing method. I’ve found even if the item is set to “FIFO” the Unit Cost still displays an “Average Cost”. What’s more, depending on your “Inventory Setup”, this “Average Cost” might not be an as it seems either.

The answer lies in the role of the “Automatic Cost Adjustment” setting in “Inventory Setup”. (and therefore the “Adjust Costs – Item Entries” batch job)

In this post I’ll walk through a series of postings and check back on the “Unit Cost” while also checking the “Value Entries” to see what costs are being posted to the GL.

Here goes 🙂

Unit Cost when “Automatic Cost Adjustment” set to ALWAYS

In the first test I set the option “Automatic Cost Adjustment” to ALWAYS in my Inventory Setup as per below:

I then created a brand new item with a costing method of FIFO and gave it an initial “Unit Cost” of £10.00

With this configuration I then received and invoiced the following quantities and values:

A Purchase Order with a Quantity of 5 and a unit cost of £20.00

This changes my original “Unit Cost” to £20.00.

A Purchase Order with a Quantity of 5 with a unit cost of £50.00

Now when I check the Unit Cost on the Item page its showing as £35.00.

So why is this, and what cost will be posted when I sell the item?

If you drill back on the “Unit Cost” the answer becomes apparent via the “Average Cost Calc. Overview” page:

This is showing that we have a quantity of 10 in inventory at a total value of £350.00 giving an average cost, and therefore a Unit Cost, of £35.00.

Selling an item

Things get really interesting when I now sell an item.

According to FIFO rules, when we sell an item, the system should post the actual cost from the first receipt (i.e. first in, first out). This cost isn’t £35.00, as is shown in the Unit Cost, it should be £20.00.

To see what happens I’ll ship and invoice a Sales Order for a Quantity of 1 as per below. (as you can see the Unit Cost displayed is £35.00)

Firstly, let’s check the “Unit Cost” on the item.

After posting the sales order if I now check the “Unit Cost” on the item you can see this has *automatically* (more on this later) been recalculated as per below:

Its now showing £36.66667 because we have a total of 9 in inventory at a total cost of £330.00 giving us an “Average Cost” of £36.66667 i.e. 330/9=36.66667.

So, what cost was posted to the GL for the Sales Order? Did the system post £35.00 or £20.00?

For this we’ll dig into the “Value Entries” on the item. As we can see the system posted an initial cost amount of -£35.00, as per the “Unit Cost” and then *automatically* posted an adjustment entry of £15.00 giving the correct FIFO cost of £20.00

The key takeaway here is the reason we get the additional “adjustment” posting of £15.00 *automatically* is because we have the “Automatic Cost Adjustment” setting switched to ALWAYS in the Inventory Setup.

As I understand it having this switched to ALWAYS runs the “Adjust Costs – Item Entries” batch job every time you post a transaction therefore posting the correct FIFO cost *automatically*.

Unit Cost when “Automatic Cost Adjustment” set to NEVER

Now, let’s test what happens to the “Unit Cost” and the FIFO costs when we have the “Automatic Cost Adjustment” set to NEVER.

First, I’ll create a new item as per below using the same initial “Unit Cost” and a costing method of “FIFO” as the previous item:

I’ll raise the same Purchase Orders as before however for brevity I won’t include screen shots of the Purchase Orders on this occasion.

When we now check the “Unit Cost” the system is still showing £20.00 as per the first purchase order we received and invoiced. It hasn’t been *automatically* changed to the correct “Average Cost” of £35.00.

However if I check the “Average Cost Calc. Overview” by drilling back on the “Unit Cost” it shows we have a quantity of 10 at a total cost of £350.00.

So, why hasn’t the system updated the “Unit Cost” on the item on this occasion?

This is because we have switched “Automatic Cost Adjustment” to NEVER. This means the “Adjust Costs – Item Entries” batch job hasn’t ran when we posted the receipt and invoice for the Purchase Orders.

Therefore if we want to update the “Unit Cost” we must run the “Adjust Costs – Item Entries” batch job manually as per below:

Then click OK to run this for all items. (I could add a filter for my item if I’d prefer)

Now when I check the “Unit Cost” the correct Average Cost is now showing

Its also worth noting that if I leave the setting “Automatic Cost Adjustment” set as NEVER, when I post a shipment the “Value Entries” produced will be for the “Unit Cost” on the item i.e. now that is -£35.00 as we have ran the “Adjust Cost – Item Entries” batch job. The additional “Adjustment” Value Entry will only be produced when I next run the “Adjust Costs – Item Entries” batch job.

Conclusion

The “Automatic Cost Adjustment” setting has a direct impact on what is shown in the “Unit Cost” of an item. This determines when the “Adjust Costs – Item Entries” batch job runs which, amongst other things, also recalculates the Unit Cost on items.

Thanks for reading.