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.

Dynamics 365 Business Central – Using a Negative Quantity on a Sales Order to Return Items

I ran into a situation recently where a user had shipped some goods from a Sales Order, which had subsequently been returned prior to the Sales Order being Invoiced. This led me to discover an interesting way to return the goods, and then invoice the Sales Order all at the same time.

After some testing I realised they could add a negative line for the same item, and ship and invoice this all at the same time.

The exact situation and steps I took are described below.

Below is a Sales Order that has a quantity of 10 that have been shipped:

Prior to invoicing this the client has returned 2 therefore I’ve reopened the Sales Order and added an additional line item for -2 for the same item.

I can now Ship and Invoice all the lines which will return 2 into my inventory and also only invoice a total of 8.

If I now check my Item Ledger Entries I can see both entries for this item. There’s a negative 10, as we originally shipped 10, and a positive 2 for the items returned.

I tested this with and without different location settings such as “Require Warehouse Receive” and “Require Warehouse Shipment” and it seems to work fine. I didn’t test it with full WMS switched on and I suspect it may hit problems there.

Thanks for reading!

Dynamics 365 Business Central – Message “Nothing to Handle” creating a Warehouse Pick from a Warehouse Shipment

I’ve seen this error message a few times and although there are likely many reasons for this I wanted to document the most common one I’ve found when troubleshooting it.

My scenario is trying to create a Warehouse Pick directly from a Warehouse Shipment using the options below:

After selecting the option you are presented with the error:

The first assumption is that the error is occurring because there’s no inventory of the item. However on checking the Item Card we can see we have 25 in inventory for this item:

The next thing I checked is to ensure there is adequate Bin Content and at first glance this also seems ok.

However if we look closely we have 25 in Bin Code A4 however this is the Bin Code we have selected to “Ship” from on the Warehouse Shipment.

Therefore when you create the Warehouse Pick the system is looking for Inventory in other Bins that you can “Take” and “Place” into the A4 Bin Code. (its irrelevant that we already have inventory in the A4 Bin code).

As there is no inventory in any other Bin Code you are presented with the “Nothing to Handle” message when you attempt to create the pick.

The fix here is to either move Inventory from the A4 Bin Code to another Bin Code or alternatively change the Bin Code on the “Warehouse Shipment”.

Below I’ve changed the Bin Code on the “Warehouse Shipment” and when I now create the pick this works fine.

If we look at the “Warehouse Pick” you can see the system is now suggesting to “Take” from Bin Code A4 , which has an inventory of 25, and “Place” into Bin Code “A1”. (as “A1” is now the “Ship” bin on the Warehouse Shipment)

In this scenario you can see that in essence the wrong Bin Code had been used on the “Warehouse Shipment”. To prevent this we recommend adding a default “Shipment Bin Code” on the location. As per below I’ve now selected “A1” as the default “Shipment Bin Code”. Therefore, going forwards, this will be the Bin Code that will be used when we create the “Warehouse Shipment” from this location.

As this hadn’t been specified originally the system had chosen “A4” as the Bin Code on the shipment thus creating the problems and confusion.

Thanks for reading!

Dynamics 365 Business Central: Posting a Sales Refund and applying to a Credit Memo

Introduction

I come from a Dynamics GP background and I’ve always found the process for refunding in Dynamics GP to be rather long winded and not very intuitive. The main issue is there’s no specific option for creating a refund, so I was very interested to test out the option in Dynamics 365 Business Central.

In this article I’ll first explain a potential scenario when a refund would need raising and applying to a credit note, then briefly explain how I’d do this in Dynamics GP, before finally going through the refund process in Dynamics 365 Business Central.

The Scenario

The scenario is as follows:

A customer buys some goods so we issue an invoice. The customer sends us payment which we apply to the invoice however they subsequently send the goods back so a credit note is issued. Finally some time later the customer requests their money back as they aren’t planning on doing business with us anymore.

Therefore, prior to issuing the refund, the account would have an Invoice applied to a Payment and a unapplied Credit Memo as per below

To complete the process we now need to issue the customer with a refund and apply this to the credit memo.

Dynamics GP

In Dynamics GP its not possible to post a “Refund” type cash receipt and apply it to the credit memo. One solution would be to void the credit memo and post a “Return with cash” similar to below:

Alternatively you could post a debit type document, apply this to the credit memo and then create the customer in payables and post a payment, however both cases aren’t ideal. (you can also use “Refund Cheques”, but again this involves having the customer setup in payables which again isn’t ideal)

Dynamics 365 Business Central

In Dynamics 365 Business Central you can post a “Refund” and apply it to the “Credit Memo” using the “Cash Receipts Journal”. Below are the steps to achieve this:

First go to the “Cash Receipt Journal” page:

Enter the refund as per below:

Next apply this to the credit memo we are refunding:

And then post the Cash Receipt Journal.

If we now review the account we can see the refund has been successfully applied to the Credit Memo.

Conclusion

I do like this functionality in Dynamics 365 Business Central. Its intuitive and doesn’t require lots of different steps. Although I’m a firm fan of Dynamics GP I do find this an improvement when compared to the current functionality available in Dynamics GP.

Thanks for reading.

Dynamics 365 Business Central – Error Message “Select Dimension Value Code for Dimension Code” when Applying Documents

I encountered this issue while investigating a dimension error a user received when applying an Invoice to a Credit note in Dynamics 365 Business Central.

In this case the user had just started assigning dimensions to customers however some customers had posted documents without any dimension analysis. They found when they attempted to apply documents with no dimension analysis they now received the error “Select Dimension Value Code for Dimension Code”.

To illustrate the issue below are some customer ledger entries for customer C00070 that have no dimension analysis (as this wasn’t required when the documents were posted)

We will now configure a requirement for analysis to the dimension “DEPARTMENT” on the customer:

Now when you attempt to post the application of the invoice to the credit note you are presented with the error below:

The only solution I could find other than reversing both the invoice and the credit was to temporarily remove the “Code Mandatory” for the dimension on the customer.

I guess as the system may have to post GL entries as a result of the application it therefore checks for the existence of dimensions on the source documents.

Something to watch out for when adding new dimensions onto existing customers and suppliers.

Thanks for reading!

Dynamics 365 Business Central – Posting a Payables invoice to multiple GL codes using a Purchase Journal

Introduction

As I learn and familiarise myself with Dynamics 365 Business Central I often post something in Dynamics GP and then wonder how this same task could be achieved in Dynamics 365 Business Central.

Today’s pondering was looking for the equivalent to the “Payables Transaction Entry” window that we have in Dynamics GP.

I know you can post to G/L accounts via the “Purchase Invoice” page in Dynamics 365 Business Central but to me this is primarily a “Purchase Order Processing” type window. I wanted to post a sundry payables invoice, onto a vendor, to multiple GL codes, without the need to touch the “Purchase Invoice” window.

As usual there is more than one way to do this however I focus on the “Purchase Journal” page. I also elude to why I think you can achieve the same using other “journal” pages, although you might not necessarily want to use those anyway 🙂

Dynamics GP – Payables Transaction Entry

In Dynamics GP you can post a sundry payables invoice in a very straight forward and easy to understand window called “Payables Transaction Entry”.

This window has no link to Purchase Order Processing. We tend to advise users to use this window for posting invoices for sundry items and things you wouldn’t necessarily have a Purchase Order for. You can click “Distributions” and record multiple GL codes for this one invoice. Its also handy to import transactions into very quickly and users seem to prefer this window for speed of entry.

Dynamics 365 Business Central – Purchase Journal

There are various “journal” pages in Dynamics 365 Business Central so I turned to the “Purchase Journal” page to achieve my goal.

The first thing I found I had to select was the option “Show more columns” as per below.

Crucially this adds the “Account Type” option which gives the user the ability to add “G/L Account” when keying in the Payables document:

Now I had the “Account Type” field available for entry I found as long as you keep the Document Number, External Document No and Posting Date the same you are able to add a payables document with multiple lines.

First you add the “Account Type” of “Vendor” and key in the first line with the total amount of the document. On the subsequent lines you can choose “G/L Account” as the account type and enter the G/L distribution breakdown. Also, if you wish to analyse tax to any of the distributions you must populate the “Gen. Posting Type” with “Purchases” and then populate the “VAT Prod. Posting Group”.

In the end you should have something like this and the document will post successfully

Other takeaways

As with any investigation you usually find some interesting things along the way.

The major takeaway I found is that you can post the same purchase invoice using the “General Journal” page. After looking more closely it seems this is possible as both pages are based on the “Gen. Journal Line” table. Therefore all the fields (and business logic) you need are available on the “General Journal” page as well.

See below. The “Purchase Journal” and “General Journal” pages are based on the same table:

Purchase Journal
General Journal

However, as a word of caution, if you were to post the same purchase invoice from the “General Journal” page the transaction is given a “Source Document” of “GENJNL” in the “G/L Register” rather than “PURCHJNL”.

See below. The top G/L Register was posted using the “General Journal” page and the other using the “Purchase Journal” page.

Therefore I’d suspect its best to use the specific “Purchase Journal” for these postings.

Thanks for reading!

Brief overview and comparison of how summary values are stored and calculated in Dynamics GP, Dynamics NAV and Dynamics 365 Business Central

Introduction

There was an interesting conversation on twitter over the weekend with regards summary values and Dynamics 365 Business Central. Much like myself the person asking the question came from a Dynamics GP background and was wondering where in the database Dynamics 365 Business Central stored summary values i.e. GL period balances.

Now this is one area of the system that I really like about Dynamics NAV and Dynamics 365 Business Central so I thought I’d write this up.

The Dynamics GP way

Dynamics GP stores summary values in a variety of different summary tables. For example the GL open years transactional data is stored in one table (GL20000) and its summarised in a summary balance table (GL10110).

This means you can quickly access the summary values in an enquiry window. For example below is a GL enquiry window showing the GL period balances for a cash account.

What’s great is as this window is accessing a summary table rather than summing and sub totalling the main transaction table the performance is great, so you have access to summary data very quickly. i.e. you could 10 million rows of transactional data for this GL code but its all nicely summarised into a handful of rows in the summary table.

As I mentioned this provides fantastic performance however anyone who has worked with Dynamics GP for any length of time will realise the one issue that comes with storing data in this way. Unfortunately if there is an issue when posting a transaction “sometimes” the summary values aren’t updated and therefore they become “out of sync” with the actual transaction data i.e. if you were to manually sum the transaction data it wouldn’t match the summary data. This can cause problems and prompt awkward conversations with clients as they (understandably) ask “why” this is the case, and (even worse) how they can prevent this in the future. (I hate it when this question crops up)

*** A wider discussion here would be why Dynamics GP doesn’t update all the SQL tables (transaction table and summary table) using one SQL transaction which would negate this issue however that is beyond of the scope of this article.

To compensate for this Dynamics GP has “reconcile” features for nearly all ledgers so you can re-align the summary balances with the actual transactions. For example below is the “reconcile” utility for the General Ledger. Running this will recalculate all the summary balances based on the transactional data and update the summary table I mentioned earlier.

The Dynamics NAV \ Dynamics 365 Business Central way

The first thing to say is that Dynamics NAV and Dynamics 365 Business Central doesn’t store summary values in specific SQL tables like Dynamics GP. Instead it uses something called “Flow fields” for the summary totals which are implemented using SQL Indexed views. (therefore technically the summary values are materialised in the database just not in actual SQL tables). In the Dynamics NAV and Dynamics 365 Business Central world they are referred to as SIFT Indexes.

So for example, when you view the GL balances in Dynamics NAV as per the screen shot below the system is running queries using the SQL indexed views to present the summary values and therefore performance is still extremely good. (technically its the SQL optimiser that will choose to use this index when asked to return the summary values for this page because its much more efficient)

Digging a little deeper if you go into the Dynamics NAV development environment you can see how the SQL indexed views (SIFT Indexes) are implemented.

For example if I select the GL entry table and click “Design”

And then select “View > Keys” to look at the indexes

You can now see the keys (Indexes) on the left and some of the keys have an associated “SumIndexField” (i.e. a SIFT Index which is implemented via a SQL Indexed View). In the highlighted example an SQL indexed view is created summing the fields Amount, Debit Amount, Credit Amount, Additional-Currency Amount, Add.-Currency Debit Amount, Add.-Currency Credit Amount and grouping by GL Account No and Posting Date.

Therefore if I open SQL Management Studio and browse to my Dynamics NAV database I can see all the SQL indexed views on the GL Entry table (there are four which matches the number of enabled keys with SumIndexFields columns in my development environment)

Finally if I take a look at the definition of the SQL indexed view named “CRONUS UK Ltd_$G_L Entry$VSIFT$1” we can see this is implementing the key that I highlighted earlier

CREATE VIEW [dbo].[CRONUS UK Ltd_$G_L Entry$VSIFT$1]
WITH schemabinding
AS
SELECT “17”.”g_l account no_”,
“17”.”posting date”,
Count_big(*) AS “$Cnt”,
Sum(“17″.”amount”) AS “SUM$Amount”,
Sum(“17″.”debit amount”) AS “SUM$Debit Amount”,
Sum(“17″.”credit amount”) AS “SUM$Credit Amount”,
Sum(“17″.”additional-currency amount”) AS
“SUM$Additional-Currency Amount”,
Sum(“17″.”add_-currency debit amount”) AS
“SUM$Add_-Currency Debit Amount”,
Sum(“17″.”add_-currency credit amount”) AS
“SUM$Add_-Currency Credit Amount”
FROM dbo.”cronus uk ltd_$g_l entry” “17”
GROUP BY “17”.”g_l account no_”,
“17”.”posting date”

I suppose you could argue the one draw back on this is that whenever a transaction is posted in Dynamics NAV and Dynamics 365 Business Central then SQL has extra CPU and disk work to do as it maintains the SQL indexed views associated with the transaction tables.

Conclusion

Although I love so many things about Dynamics GP I do think Dynamics NAV and Dynamics 365 Business Central has the edge here. By allowing the database to handle the maintenance of summary values through the implementation of SQL indexed views the data can never go out of sync with the summary values.

Thanks for reading.