Microsoft Dynamics NAV
Costing Error Detection and Data Correction White Paper
October 2011
Introduction ................................................................................................................... 3 Diagnosing Problems in Inventory Costing ................................................................ 3 Costing Error Detection Report ................................................................................................................. 3 How to use the Costing Error Detection Report ........................................................................................ 4 Using the results of the Costing Error Detection report ............................................................................. 5
Data Correction ............................................................................................................. 6 Steps needed for correcting data .............................................................................................................. 6 How to readjust entries after a code fix has been added .......................................................................... 6 Average cost items ................................................................................................................................ 6 Items with costing methods other than average .................................................................................... 7 How to correct an incorrect setting of the Valued by Average Cost field in value entry ........................... 7 How to correct invoiced quantities ............................................................................................................. 7 Item ledger entry .................................................................................................................................... 7 Associated value entries ........................................................................................................................ 8 How to correct the Cost Application field in Item Application Entry in relation to valued by average cost in value entries .......................................................................................................................................... 8 How to correct valuation dates .................................................................................................................. 8 How to correct incorrect Item Application Entries ..................................................................................... 8 How to correct item application entries with the same Item Ledger Entry No., Inbound Item Entry No., and Outbound Item Entry No. .................................................................................................................... 9 How to correct expected cost 0 on completely invoiced entries .......................................................... 9 Item ledger entry .................................................................................................................................... 9 Associated value entries ........................................................................................................................ 9
Basic Design of Inventory Costing Data ................................................................... 10 Inventory costing tables and how they are created ................................................................................. 10 Item Ledger Entry table (32) and Value Entry table (5802) ................................................................. 10 Item Application Entry table (339) ........................................................................................................ 10 Transfers and fixed applications specifications ....................................................................................... 11 Transfers .............................................................................................................................................. 11 Fixed applications ................................................................................................................................ 12 Average cost ............................................................................................................................................ 13 Avg. Cost Adjmt. Entry Point table (5804) ........................................................................................... 13 Valued By Average Cost field in the Value Entry table (5802) ............................................................ 13 Valuation date ...................................................................................................................................... 14
Additional Resources ................................................................................................. 14
2
COSTING ERROR DETECTION AND DATA CORRECTION
INTRODUCTION This white paper discusses common inventory costing issues and how you can correct erroneous data after inventory costing issues have been identified. This document focuses on the data and the fields that typically cause problems in the cost adjustment process. This is meant to be a guide, instead of a complete manual. For more detailed information about costing in Microsoft Dynamics NAV, see the Costing white papers. The Costing Error Detection report was developed to help you find common costing data problems. If this report shows that there are errors in your database, you can use the suggestions in this document to correct the data. There are no automated fixes for repairing costing data. For Microsoft Dynamics NAV 5.0 and Microsoft Dynamics NAV 2009 with service packs, use Report 60010. For earlier releases of Microsoft Dynamics NAV, use Report 60000, which can be found in KB 893788.
DIAGNOSING PROBLEMS IN INVENTORY COSTING Diagnosing problems in inventory costing varies. If there is a problem in the Adjust Cost - Item Entries batch job, such as an endless loop, there is most likely something wrong. Perhaps the unit cost on the Item card does not contain the expected value. Or, the Inventory Valuation report may have unexpected values, such as the values are not logical because Quantity = 0 while there is a Value 0 or the Value is not expected by the user. Diagnosing these problems can be difficult due to the number of inventory items and the complex relationships between entries in different tables.
COSTING ERROR DETECTION REPORT In addition to using the usual methods of error detection, you can also use the Costing Error Detection report (Report 60010). This report examines the costing data in your database to determine whether everything is as it should be. The report produces a list of item ledger entries showing where errors have been found and a description of what the error is. An error could be in associated value entries or item application entries, but to avoid any confusion, the report lists only item ledger entries. The description contains the error and the type of entry that the error was found in, such as item ledger entry, item application entry, or value entry. For example, if the Costing Error Detection report shows that there is more than one item application entry with the same combination of item ledger entry number, inbound item entry number, and outbound item entry number values, the report will generate a line that shows the Item Ledger Entry No., Item No., Entry Type, Quantity, Remaining Quantity, and the values of the Positive and Open fields. Under the entry, the report will list any errors that were found in relation to that entry. In this example, the report would generate the error, "There is more than one Item Application Entry with the same combination of Item Ledger Entry No., Inbound Item Entry No. and Outbound Item Entry No." At the end of the report, all item numbers with descriptions are listed for items that are affected by data inconsistencies. This is useful if the report is run for a group of items. Before running the report, you need to add an additional key to the Item Application Entries table (339). The key should contain the Item Ledger Entry No., Inbound Item Entry No., and Outbound Item Entry No fields.
3 COSTING ERROR DETECTION AND DATA CORRECTION
HOW TO USE THE COSTING ERROR DETECTION REPORT When you run the Costing Error Detection report, you have the option to select the tests that you want to perform. The first option, Basic Data Test, performs basic tests on all entries and takes a while to run. Since this check can reveal some very basic problems, it is a good idea to run the Basic Data Test first, correct any errors that are shown, and then run the Basic Data Test until all inconsistencies are no longer detected. After that, you can run the remaining checks in the report. The Costing Error Detection report will check the following data in your database. BASIC DATA TEST
No. field must not be blank in an item record
Entry No. field in an item ledger entry must not be zero or negative.
At least one value entry must exist for every item ledger entry.
The values in the Positive and Open fields are correct.
The sign of the values in the Quantity and Remaining Quantity fields in the item ledger entry must be the same.
The value in the Remaining Quantity field must not be greater than the value in the Quantity field of an item ledger entry.
The value of the Valued by Average Cost field is the same in all value entries that are associated with the same item ledger entry.
The value in the Valuation Date field is the same in all value entries (except for revaluation and rounding entries) that are associated with the same item ledger entry.
The Valuation Date field in value entries for positive consumption must not be 12.31.9999.
The value in the Valued by Average Cost field must be False for all value entries where the item does not use costing method average. For inbound item ledger entries of items with costing method average, the Valued by Average Cost field must be False for corresponding value entries. An exception is the item ledger entry where Correction is set to True for associated value entries; the Valued By Average Cost field must be True.
The summarized Invoiced Quantity field for value entries, associated to a completely invoiced item ledger entry, must be the same as the invoiced quantity on the item ledger entry.
The Invoiced Quantity in the value entry must equal zero if Adjustment is set to True.
The Item Ledger Entry Quantity field in a value entry must equal zero if Adjustment is set to True.
The Item Ledger Entry Type in the value entries must equal Entry Type in the corresponding item ledger entry. An exception is for value entries that represent an item charge created by a purchase. In that case, the Item Ledger Entry Type field state is Purchase regardless of Entry Type in the corresponding item ledger entry.
ITEM LEDGER ENTRY – ITEM APPLICATION ENTRY QUANTITY CHECK
The Quantity and Remaining Quantity values in an item ledger entry must correspond to the quantities recorded in the item application entries. 4
COSTING ERROR DETECTION AND DATA CORRECTION
Any combination of Item Ledger Entry No., Inbound Item Entry No., and Outbound Item Entry No. should occur only once in the Item Application Entry table.
APPLICATION QUANTITY CHECK
The values in the Item Ledger Entry No. field and the Inbound Item Entry No. field are equal for positive entries in the Item Application Entry table.
The values in the Item Ledger Entry No. field and the Inbound Item Entry No. field are not the same for negative entries in the Item Application Entry table.
The values in the Item Ledger Entry No. field and the Outbound Item Entry No. field are the same for negative entries in the Item Application Entry table.
The Remaining Quantity on an inbound (positive) item ledger entry corresponds to the sum of the Quantity fields in corresponding item application entries.
CHECK FOR VALUED BY AVERAGE COST AND COST APPLICATION (ONLY FOR ENTRIES WHERE THE ITEM USES THE COSTING METHOD AVERAGE)
For outbound entries, if the Valued By Average Cost field in a corresponding value entry is set to True, then Cost Application on the associated item application entries must be set to False.
For outbound entries, if the Valued By Average Cost field in a corresponding value entry is set to False, then Applies-to Entry in a corresponding item ledger entry must be filled in. Cost Application on the associated item application entries must be set to True.
CHECK FOR VALUATION DATE
The Valuation Date for an outbound entry that is applied to an inbound entry must be equal to or later than the Valuation Date of the inbound entry.
CHECK EXPECTED COST ON COMPLETELY INVOICED ENTRY
When an item ledger entry is Completely Invoiced, the summarized value of the following fields for the attached value entries must equal zero: o
Cost Amount (Expected)
o
Cost Amount (Expected) (ACY)
o
Expected Cost Posted to G/L
o
Exp. Cost Posted to G/L (ACY)
CHECK ITEM LEDGER. ENTRY NO. FROM VALUE ENTRIES
The Item Ledger Entry Number that the value entry refers to must be located in the Item Ledger Entry table.
USING THE RESULTS OF THE COSTING ERROR DETECTION REPORT If the Costing Error Detection report list errors that were found in the database, you must search for the cause for each error. For example, a hotfix might be available in a Knowledge Base article, or the issue might be related to a customization or to a previous upgrade. When the cause of the inconsistency is established, you must ensure the following: 5 COSTING ERROR DETECTION AND DATA CORRECTION
All defective code is corrected so that the data error does not occur again.
The existing data is corrected.
If you have installed all of the hotfixes for your release of Microsoft Dynamics NAV, you may have already installed a code fix for your problem. Review the hotfix descriptions to determine if a code error that typically caused the problem reported by the Costing Error Detection report has been resolved in a hotfix. After you have established that the error will no longer be found by the report, your next task is to correct the data. How you decide to do that will depend on the size of the problem. The next section of this document, Data Correction, contains suggestions about how to correct these errors.
DATA CORRECTION STEPS NEEDED FOR CORRECTING DATA You can use the following steps to correct data in your database. After you have found a problem, you need to implement all code fixes to ensure that problems do not recur. 1. Create a backup of the database and use it in a test environment. Steps 3 through 7 should be performed in the test environment to ensure the result of the correction process. 2. Run the Costing Error Detection report to find any issues with inventory costing data. 3. Perform the necessary corrections. 4. Repeat steps 2 and 3 until the Costing Error Detection report shows no errors. 5. Create a backup of the database. If old entries must be readjusted, follow the steps in How to readjust old entries after a code fix has been added. 6. Run the Adjust Cost - Item Entries batch job. 7. Review the results and allow the customer to review the results. 8. Implement the changes in the live database. Make sure to allow for new transactions that have occurred after you created the backup for the test environment.
HOW TO READJUST ENTRIES AFTER A CODE FIX HAS BEEN ADDED If you want the Adjust Cost Item Entries batch job to adjust old entries, you must make sure that the appropriate fields are set so that the batch job processes them. The methods for doing this are different for average cost items and for items with other costing methods. Regardless of the costing method, to bring the item into the scope of the Adjust Cost Item Entries batch job, ensure that the Cost is Adjusted field in the Item card is set to False, if not, the field must be made editable and the field should not be selected. AVERAGE COST ITEMS In the Average Cost Adjustment Entry Point table (5804), filter on the Item No. Do not select the Cost is Adjusted field for those periods (valuation dates) that you want to force through an additional cost adjustment process.
6
COSTING ERROR DETECTION AND DATA CORRECTION
If an outbound item ledger entry is fixed applied to an inbound entry, set the Applied Entry to Adjust field for the inbound entry to True. You can determine if outbound entries are fixed applied to inbound entries by checking the Applies-to Entry No. field in the outbound item ledger entry. When this field is filled in, the entry is fixed applied. ITEMS WITH COSTING METHODS OTHER THAN AVERAGE For any item where the applied outbound entries need to be readjusted, set the Applied Entry to Adjust field for inbound item ledger entries to True. When you run the adjustment, the related entries will be adjusted again.
HOW TO CORRECT AN INCORRECT SETTING OF THE VALUED BY AVERAGE COST FIELD IN VALUE ENTRY The following guidelines can be used to evaluate if the Valued by Average Cost field in the value entry is set to True or False:
The value in the Valued by Average Cost field must be False for all value entries if the item does not use the costing method average.
The value of the Valued by Average Cost field must be the same in all value entries that are associated with the same item ledger entry. General rule:
If the Valued Quantity field in the value entry is positive (inbound entry), then the field must be False. An exception is if the parent item ledger entry, the Correction field is set to True and the item uses the costing method average, then the Valued by Average Cost state is True
If the Valued Quantity field in the value entry is negative (outbound entry), then the field must be True, unless an outbound entry is fixed applied to an inbound entry. If the parent item ledger entry indicates that there is an entry number in the Applies-to Entry No. field, the outbound entry is fixed applied to that entry. In that case, the Valued by Average Cost field must be False.
For more information, see Basic Design of Inventory Costing Data.
HOW TO CORRECT INVOICED QUANTITIES The summarized invoiced quantity of value entries, associated to a completely invoiced item ledger entry, must be the same as the invoiced quantity on the item ledger entry. When item ledger entry is completely invoiced, the invoiced quantity must equal Quantity. ITEM LEDGER ENTRY Entry No.
Item No.
Posting Date
Entry Type
Document Type
Quantity
Invoiced Quantity
Completely Invoiced
100
A
01.21.11
Sale
Sales Shipment
-5
-5
Yes
7 COSTING ERROR DETECTION AND DATA CORRECTION
ASSOCIATED VALUE ENTRIES Entry No.
Item No.
Posting Date
Item Ledger Entry Type
Entry Type
Item Ledger Entry No.
Item Ledger Entry Quantity
Invoiced Quantity
Cost Amount (Actual)
Cost Amount (Expected))
150
A
01.21.11
Sale
Direct Cost
100
-5
0
0
-100
160
A
01.25.11
Sale
Direct Cost
100
0
-5
-100
100
-5
If there are additional value entries that make up an incorrect sum of Invoiced Quantity, the Invoiced Quantity needs to be cleared in one or more value entries. The choice of which value entry, Invoiced Quantity field, that needs to be cleared, needs to be considered in accordance with design. For example, a value entry stating Expected Cost is True or Adjustment is True must only state Invoiced Quantity = 0.
HOW TO CORRECT THE COST APPLICATION FIELD IN ITEM APPLICATION ENTRY IN RELATION TO VALUED BY AVERAGE COST IN VALUE ENTRIES The following guidelines should be used for issues with the value in the Cost Application field. If the outbound item ledger entry has attached value entries with the Valued by Average Cost field set to True, then the Cost Application field on the associated item application entries must be False. For inbound item ledger entries, the associated item application entry, the Cost Application field must be True.
HOW TO CORRECT VALUATION DATES The following guidelines should be used for correcting valuation dates. The Valuation Date in value entries associated to an outbound item ledger entry that is applied to an inbound item ledger entry, must be equal to or later than the Valuation Date of value entries associated to the inbound item ledger entry. The assignment of valuation dates can be found in the Inventory Costing training material.
HOW TO CORRECT INCORRECT ITEM APPLICATION ENTRIES Use the following guidelines to identify and correct erroneous item application entries:
For a positive item ledger entry, the sum of the Quantities of all related item application entries (filter on Item Ledger Entry No.) must be its Quantity.
For a negative item ledger entry, the sum of the Quantities of all related item application entries (filter on Item Ledger Entry No.) must be its Quantity minus Remaining Quantity.
The sign of the Quantity of an item ledger entry and the sign of the Quantity of all related item application entries (filter on Item Ledger Entry No.) must be equal.
The Remaining Quantity of a positive item ledger entry must be equal to the sum of the quantities of all item application entries with the same Inbound Item Entry No. (filter on Inbound Item Entry No.).
8
COSTING ERROR DETECTION AND DATA CORRECTION
HOW TO CORRECT ITEM APPLICATION ENTRIES WITH THE SAME ITEM LEDGER ENTRY NO., INBOUND ITEM ENTRY NO., AND OUTBOUND ITEM ENTRY NO. If more than one item application entry has the same Item Ledger Entry No., Inbound Item Entry No., and Outbound Item Entry No., the cost adjustment may not run correctly. To correct the item application entries: 1. Combine these item application entries into one entry by summing up the Quantity. Ensure that the quantities are in correspondence to the item ledger entry that it represents and in accordance to what is mentioned in this document. 2. After you have updated one of the item application entries so that it holds all the summed information, you can delete the other item application entries.
HOW TO CORRECT EXPECTED COST 0 ON COMPLETELY INVOICED ENTRIES For an item ledger entry where Completely Invoiced is to set to True, the summarized Cost Amount (Expected) must equal zero. The following is an example where a purchase order has been recorded as received; item ledger entry, and the first value entry with a recognized Cost Amount (Expected) has been created. In the next step, the goods have been partially invoiced where the second value entry is created. Two days later, the received goods are completely invoiced. ITEM LEDGER ENTRY Entry No. 329
Posting Date
Entry Type
05.03.11
Purchase
Document Type
Document No.
Item No.
Purchase Receipt
107036
A
Cost Amount (Actual)
Cost Amount (Expected))
Quantity
Invoiced Quantity
Completely Invoiced
0
5
5
Yes
100
ASSOCIATED VALUE ENTRIES Item Entry Posting Ledger No. Date Entry Type
Valuation Date
Cost Entry Document Document Amount Type No. Type (Actual)
Cost Amount (Expected)
Item Ledger Entry Quantity
Invoiced Quantity
514 05.03.11 Purchase
05.03.11
Direct Cost
107036
Purchase Receipt
0
100
5
0
515 05.05.11 Purchase
05.03.11
Direct Cost
108033
Purchase Invoice
60
-60
0
3
516 05.07.11 Purchase
05.03.11
Direct Cost
108034
Purchase Invoice
40
-40
0
2
0
When the invoice is recorded, the earlier recognized Cost Amount (Expected) is reversed. When the item ledger entry is Completely Invoiced, the summarized Cost Amount (Expected) equals zero. The same applies to the Cost Amount (Expected) (ACY), Expected Cost Posted to G/L, and Exp. Cost Posted to G/L (ACY) fields if additional reporting currency or expected cost posting is used. The 9 COSTING ERROR DETECTION AND DATA CORRECTION
correction process in these situations is to clear the Cost Amount (Expected) in one record that causes the summarized Cost Amount (Expected) not to equal zero. If Expected Cost Posting is used, ensure if the incorrect Cost Amount (Expected) is also recorded to the general ledger. If it is, then it has to be reversed (cleared). The Value Entry No., Item No., and Posting Date need to be entered into the Post Inventory Cost to G/L table (5811). The Post Inventory Cost to G/L batch job will then process the value entry and post the difference between the Cost Amount (Expected) (now cleared) and the Expected Cost Posted to G/L in the value entry to the general ledger.
BASIC DESIGN OF INVENTORY COSTING DATA INVENTORY COSTING TABLES AND HOW THEY ARE CREATED ITEM LEDGER ENTRY TABLE (32) AND VALUE ENTRY TABLE (5802) When an inventory transaction is posted in Microsoft Dynamics NAV, an item ledger entry is generated to record the change in quantity. At the same time, one or more value entries are created to record the value of the transaction. If the transaction has not yet been invoiced, the expected cost of the transaction is recorded in the value entry's Cost Amount (Expected) field. The Expected Cost field is also set to True, the Valued Quantity field is filled in with the quantity, and the Invoiced Quantity field is blank. When the transaction has been invoiced, a new value entry is created by using the cost in the Cost Amount (Actual) field and the quantity in both the Valued Quantity and the Invoiced Quantity fields. The Cost Amount (Actual) field is calculated by multiplying the Invoiced Quantity by the Cost Per Unit. The earlier recorded Cost Amount (Expected) is reversed. The purpose of the Adjust Cost - Item Entries batch job is to push the costs of the inbound entries forward to the applied outbound entries. If a cost-changing event has occurred, such as an item charge being invoiced for an inbound entry like a purchase, the Adjust Cost - Item Entries batch job recognizes the new cost of the inbound entry and forwards the cost to any outbound entries that are applied to the inbound entry. The Adjust Cost - Item Entries batch job calculates the average cost for the item and updates the Unit Cost on the Item card accordingly. The Adjust Cost - Item Entries batch job adjusts only those value entries that have not yet been adjusted. It determines which entries these are by using the Applied Entry to Adjust field on the item ledger entry. In general, this field is set to True on an item ledger entry when another entry has been applied to it. For example, the Applied Entry to Adjust field is set to True on a positive purchase entry when a negative sale entry is applied to it. As discussed in the next sections, it is also possible for this field on a negative entry to be set to True. Note, the Applied Entry to Adjust field is not set to True for entries for items with costing method average unless a fixed application is used. How average cost items are handled is generally different from the other costing methods, and therefore, in the following sections there will be separate reference to this costing method. ITEM APPLICATION ENTRY TABLE (339) When an item ledger entry is created to represent an increase of goods to inventory, such as a positive adjustment, a purchase, or a manufacturing output, an entry is also created in the Item Application Entry table. This resembles the following example entry. Entry No.
Item Ledger Entry No. 732
740
Inbound Item Entry No. 740
Outbound Item Entry No.
Quantity 0
80
Posting Date 01.21.11 10
COSTING ERROR DETECTION AND DATA CORRECTION
This example entry does not show all of the fields in this table. The Item Ledger Entry No. shows that this entry was created for the transaction corresponding to Item Ledger Entry number 740. The Inbound Item Entry No. field contains the same item ledger entry, 740, which shows that this was an inventory increase. The Outbound Item Entry No. of 0 indicates that there were no earlier outbound entries associated with this transaction when it was made. When a transaction is made that subtracts a quantity of goods from inventory, such as a negative adjustment, sale, or consumption, and there is a positive quantity to which the negative quantity can be applied, an item application entry is created that resembles the following example. Entry No.
Item Ledger Entry No.
Inbound Item Entry No.
Outbound Item Entry No.
741
740
741
733
Quantity
Posting Date
-25
01.21.11
The Item Ledger Entry No. field shows that this entry was created by the transaction for item ledger entry number 741. This entry number also appears as the Outbound Item Entry No., which reveals that this entry is an inventory decrease, because it is the same entry number as the Item Ledger Entry No. The Inbound Item Entry No. field shows that the inventory increase to which this inventory decrease was applied to entry 740. The negative quantity confirms that this is an inventory decrease. When this entry is created, the Remaining Quantity in the inbound item ledger entry is reduced by the quantity in the Quantity field. The Remaining Quantity in the outbound item ledger entry is also adjusted to show only the unapplied quantity. If an outbound entry is applied to more than one inbound entry, then several outbound item application entries with different inbound item entry numbers are created for each applied entry. If an outbound entry cannot be applied, then an item application entry is not created. The corresponding item application entry is created only when an inbound entry has been posted to which the outbound entry can be applied.
TRANSFERS AND FIXED APPLICATIONS SPECIFICATIONS TRANSFERS Transfers from location to location have two components —shipping from the original location and receiving in the new location. These two components create four item ledger entries:
A negative adjustment to the original location.
A positive adjustment to the in-transit location.
A negative adjustment to the in-transit location.
A positive adjustment to the new location.
When the transfer shipment is posted, the first two entries are created. These entries are applied to each other in the Item Application Entry table. When the transfer receipt to the new location is posted, the last two item ledger entries and value entries are created. The item application entries for these transactions resemble those in the following example. EXAMPLE The inventory manager at CRONUS International Ltd. creates a positive adjustment for 100 PCS of item 70000 in the Blue location. This creates item ledger entry 360. The manager then creates a transfer moving 50 PCS to the Red location. The shipment and receipt result in the following item ledger entries: 11 COSTING ERROR DETECTION AND DATA CORRECTION
Entry 361, with a negative transfer of -50 PCS from the Blue location.
Entry 362, with a positive transfer of 50 PCS to the Out. Log. location.
Entry 363, with a negative transfer of -50 PCS from the Out. Log. location.
Entry 364, with a positive transfer of 50 PCS to the Red location.
The item application entries appear as follows. Entry No.
Item Ledger Entry No.
Inbound Item Entry No.
Outbound Item Entry No.
Quantity
Posting Date
Transferred-From Entry No.
338
360
360
0
100
01.01.11
339
361
360
361
-50
01.02.11
340
362
362
361
50
01.02.11
341
363
362
363
-50
01.03.11
342
364
364
363
50
01.03.11
360
362
As shown in this example, the positive transfer item application entries differs from typical positive item application entries because there is an associated outbound item entry number and there is a Transferred-From Entry No. that marks these entries as transfers from a different location. The adjustment uses these entries to push the cost from the original inbound entry 360 to the transfer shipment entry 361. This cost is then pushed to the inbound in-transit entry 362, to the outbound in-transit entry 363, and finally to the inbound transfer receipt entry 364. Transfers of average-cost items differ from the entries shown above, in that the Transferred-From Entry No. field on new item application entries is not filled in. FIXED APPLICATIONS Fixed applications are transactions where the user specifies, at the time of making the transaction, that the transaction must be applied to or from a specific entry. A common type of fixed application is a sales credit memo, which is applied to the original sale to ensure that the correct inventory value is put back into inventory as when it left inventory. EXAMPLE The inventory manager creates a positive adjustment for 100 PCS of item 70062. This creates item ledger entry 365. A salesperson posts a sales order for 50 PCS. This creates item ledger entry 366. Two days later, the salesperson posts a return order for 25 PCS and specifies entry 366 as the Applies-From Entry No. in the return order line. The Item Ledger Entry No. for the return order is 367. The item application entries appear as follows. Entry No.
Item Ledger Entry No.
Inbound Item Entry No.
Outbound Item Entry No.
Quantity
Posting Date
343
365
365
0
100
01.01.11
344
366
365
366
-50
01.02.11
345
367
367
366
25
01.04.11
The item application entry for the return order shows that it is fixed applied by specifying an Outbound Item Entry Number. This is used in the Adjust Cost - Item Entries batch job to forward the cost from the sales order to the return order. 12
COSTING ERROR DETECTION AND DATA CORRECTION
AVERAGE COST The calculation of the adjusted cost for items with the costing method average is significantly different from that of items with other costing methods, so that the entries are created with different fields selected. AVG. COST ADJMT. ENTRY POINT TABLE (5804) The Avg. Cost Adjmt. Entry Point table is used to store all items, regardless of the costing method, to support the Average Cost Calc. Overview window, which is accessed by drill down in the Unit Cost field in the Item card. The Average Cost Calc. Overview window provides an overview of all entries that were used to calculate the average unit cost per average cost period. The overview is most useful for items with costing method average but it is available for all items. For items with the costing method average, the Adjust Cost - Item Entries batch job uses the entries in this table to find which items to adjust and for which valuation dates. In general, entries are created in this table when an inventoriable value entry is posted in codeunit 22. When the Adjust Cost - Item Entries batch job has processed the average cost period (valuation date) the Cost is Adjusted field is set to True. Table 5804 has five fields: Item No., Variant Code, Location Code, Valuation Date, and Cost is Adjusted. If the Average Cost Calc. Type on the Inventory Setup is Item, then the Item No. and Valuation Date fields are the only fields used. If the Average Cost Calc. Type is Item & Location & Variant, then all fields are used. The Valuation Date reflects the Average Cost Period. If set to Day, the Valuation Date in the Avg. Cost Adjmt. Entry point table reflects the valuation dates where value entries exists. If Average Cost Period is, for example, Month, the Valuation Date states the last date in a month for which value entries exists. VALUED BY AVERAGE COST FIELD IN THE VALUE ENTRY TABLE (5802) The Valued by Average Cost field in the Value Entry table is one of the most important fields used when calculating average costs for items with the costing method average. The purpose of this field is to identify entries for which costs should be calculated using the average cost of the item valid on a certain valuation date. Some of the rules regarding how this field is set are as follows:
The Valued by Average Cost field must never be set to True for items with a costing method other than average.
The Valued by Average Cost field must always be set to True for outbound, direct cost value entries of average cost items except for a fixed application. However, the value entry of an item charge that is associated with an outbound item ledger entry is not Valued By Average Cost.
If there is more than one value entry for the same item ledger entry, the Valued by Average Cost of all these value entries where Inventoriable is set to True must be the same.
Fixed applied outbound entries are not valued by the average cost because their costs are transferred directly from the applied inbound entry to the outbound entry. You can identify a fixed applied outbound item ledger entry by checking whether the entry has a value in the Applies-to Entry field. If this field is filled in for an outbound entry, it holds the item ledger entry number of the inbound entry. This means that Valued by Average Cost of fixed applied outbound entries must be False; for all other outbound entries Valued by Average Cost must be True.
Valued by Average Cost of inbound entries must be False. The exception is if the parent item ledger entry Correction is set to True, then Valued by Average Cost is True.
Valued by Average Cost for positive manufacturing output entries must always be False.
13 COSTING ERROR DETECTION AND DATA CORRECTION
For outbound entries, there is a direct relation between the Valued by Average Cost field in the Value Entry table and the Cost Application field in the Item Application Entry table. If Valued by Average Cost of an outbound value entry is True, Cost Application of the associated item application entry must be False. If Valued by Average Cost is False for an outbound value entry, Cost Application must be True. For inbound entries, Cost Application must be True.
An exception to these rules is that Valued by Average Cost should not be set to value entries that were generated during an upgrade. You can identify these entries by looking at the Valuation Date. The upgrade tool does not fill in the Valuation Date. If a transaction is posted in Microsoft Dynamics NAV version 3 or greater, this creates a value entry in the Valuation Date field.
VALUATION DATE The valuation date in the value entries is important in calculating average costs. The average cost of an entry is dependent upon the valuation date with which the average cost is calculated. The following are some general rules for setting the valuation date:
If the costs of an entry depend on the costs of another entry, the valuation date of the dependent entry must always be equal to or later than the valuation date of the original entry. For example, the valuation date of an outbound entry must always be equal to or later than the date of the applied inbound entry. The valuation date of an inbound entry where the costs are retrieved from an outbound entry, such as a transfer, must always be equal to or later than the valuation date of the outbound entry.
If quantity on inventory is less than zero after posting an inventory decrease, then the valuation date is first set to the posting date of the inventory decrease. According to the rules described above, this date may be changed later when an inventory increase is applied.
For more information about the assignment of valuation dates, see the Inventory Costing training material.
ADDITIONAL RESOURCES For more information about inventory costing, see:
Inventory Costing training material (2009) and Manufacturing Costing training material (4.0), both found at: https://mbs.microsoft.com/partnersource/deployment/documentation/userguides/TrainingManaul OverviewNAV2009.htm
Costing White paper (5.0): https://mbs.microsoft.com/partnersource/documentation/whitepapers/msdynav50_inventorycostin g_wp.htm?printpage=false
Microsoft Dynamics NAV Team Blog: http://blogs.msdn.com/b/nav/archive/tags/costing/
14
COSTING ERROR DETECTION AND DATA CORRECTION
Microsoft Dynamics is a line of integrated, adaptable business management solutions that enables you and your people to make business decisions with greater confidence. Microsoft Dynamics works like and with familiar Microsoft software, automating and streamlining financial, customer relationship, and supply chain processes in a way that helps you drive business success. U.S. and Canada Toll Free (888) 477-7989 Worldwide (1) (701) 281-6500 www.microsoft.com/dynamics The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, this document should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication. This white paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED, OR STATUTORY, AS TO THE INFORMATION IN THIS DOCUMENT. Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.
Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property. © 2010 Microsoft. All rights reserved. Microsoft, Microsoft Dynamics, and the Microsoft Dynamics logo are trademarks of the Microsoft group of companies.
15 COSTING ERROR DETECTION AND DATA CORRECTION
