Simulation of Average Cost Calculation

When using Average Costing method it’s sometimes difficult to interpret the assigned costs of an outbound entry. 

In the scenario below there is process description for simulating the average cost calculation. This is often used when investigating costing issues related to the average costing method. It has been helpful in verifying the recognized COGS, to describe the average cost calculation or using it as identification that somewhere in time the average cost is unexpected. It helps to identify the area for deeper research of the records demonstrating unexpected values.

This blog post describes the process for how the average cost calculation can be simulated only and does not describe possible causes or possible correction processes.

The following scenario is carried out to create the basic data set. The data set is then used as base for processing and analyzing the average cost calculation when having the setup Average Cost Period as Day and Month respectively.

The scenario is carried out in a W1 Cronus database.

The first two steps create the basic data set which later on is used in respective simulation of average cost calculation.

1. Create Item: TEST

Average Costing method

Unit Cost: 10

2. Create and post the following documents.

 

If you are aiming for creating the full scenario and working through the respective simulations of Average cost calculation for Day and Month, it’s a good thing to create a backup now.

Inventory setup, Average Cost Calc Period = Day

3. Run the Adjust Cost – Item Entries batch job.

4. Filter the Item Ledger Entry table on Item TEST and review the fields specified below.

5. Open Revaluation Journal

6. Run Function Calculate Inventory Value:

Filter Item: TEST

Posting Date: September 15, 2011

Per Item

7. Change Unit Cost (Revalued) to 12 as above.

8. Post Line.

9. Run Adjust Cost – Item Entries batch job.

10. Filtering the Value Entries table on Item TEST, the following records are available:

Simulation of Average Cost Calculation with Average Cost period = Day

Now we are moving into the process of simulating the Average cost Calculation when Average cost period is Day, using the data for item TEST created in the scenario above.

When you have identified the Item that you need to further analyze the following process can be used. Below the described process, there is a screenshot showing the results of the simulation of the Average Cost calculation of item TEST.
In addition there is an Excel sheet attached where the full data set is available where used formulas, etc. can be reviewed more closely.

1. In the Value Entries table, filter on the particular Item that is to be analyzed.    
If Average Cost Calc. Type is per Item&Location&Variant the filter has to cover also these fields with particular values in scope for the analysis.

2. Paster filtered Value entries into Excel.

3. Do a Custom Sorting using the fields as below:

Comments to respective field being a part of the sorting:

Valuation Date: is the date for when the entry shall be part of the Average cost calculation.

Partial Revaluation: a field that states Yes on Value entries with Type Revaluation. Revaluations affect the valuation of the following period’s outbound entries, not the outbound entries of the same period.

Valued quantity: is populated on every Value entry, corresponds to Item ledger entry quantity, Invoiced Quantity or Revalued quantity. Largest to smallest brings the inbound entries to come before the outbound entries of the period and thereby create the base for calculating the average cost of the period.

Item Ledger entry No.: Is to group the value entries attached to same Item ledger entry no.

4. Insert Summary lines where you want to establish the periods Average Cost (grey lines below). A summary line shall be inserted above the first outbound entry of a period. To identify the breakpoint for inserting the summary line follow these steps:

a. Establish the Valuation Date to be in scope for the investigation and locate these entries in the sorted Value entry list.

b. Then follow the stated quantities in field Valued Quantity for the chosen Valuation date. Identify the first line with negative quantity and you have the first outbound entry of the period.

c. Insert a line for calculation, above the first outbound entry of the period (example in the screenshot below and in attached spreadsheet; column M row 3 and 5, row 3 positive Valued Quantity, row 5 negative Valued Quantity, Summary line is inserted, row 4.

5. Make a Sum of the columns; Cost Amount (Actual), Cost Amount (Expected) and Item Ledger Entry Quantity. Calculate the Average Unit Cost of the period (column R) with the following formula:

If you have several Summary lines inserted, make sure to include the previous summary line into the calculation of respective column for the next period.

6. Choose an outbound entry, usually the first outbound entry of the period and then a couple of others, randomly selected in the period or those that for some reasons is of particular interest, and calculate the average cost per unit with the formula above (green, purple and blue sections in screenshot below).

– Does it correspond to the average unit cost of the period?

If not, ensure it is not fixed applied to an inbound entry: If field Valued By Average Cost is False, it is fixed applied to an inbound entry. 
To which entry?; Follow up on the parent Item Ledger entry, field Applies-to Entry shall carry the entry no. of the supplying Item Ledger Entry.
If not fixed applied; establish the Amount Rounding precision and investigate if that has an effect on the Calculated Average cost.

These are the Value entries for item TEST when they have been sorted as described in step 3, where Summary lines has been inserted to establish Average cost for a certain period (step 4, 5) and where the  first outbound entry of the period is calculated (step 6).

In attached spreadsheet used formulas can be checked by clicking in respective field.

Inventory setup, Average Cost Calc Period = Month

Another Average Cost Calc Period to use is Month, so let’s work with the basic scenario, create some additional data and see the effects having Month as Average Cost Calc Period and finally look into the simulation of the Average Cost calculation and its specifics.

The scenario continues using the basic data set created until step 2. If you did a backup after step 2 and have been working with the Average Cost Calc Period of Day you now have the opportunity to restore the backup and you will be able to start with step 3 below. 

3. Change Inventory setup; Average Cost Calc Period to Month.

4. Run Adjust Cost – Item entries batch job.

5. Filter the Item Ledger Entry table, Item TEST, and review the fields specified below.

6. Open Revaluation Journal

7. Run Function Calculate Inventory Value:

Filter Item: TEST

Posting Date: September 30, 2011

Per Item

8. Change Unit Cost (Revalued) to 12 as above.

9. Post Line.

10. Run Adjust Cost – Item Entries batch job.

11. Filtering the Value Entries table, Item TEST, the following records are available:

Simulation of Average Cost Calculation with Average Cost period = Month

Now we are moving into the process of simulating the Average cost Calculation when Average Cost period is Month, using the data for item TEST created in the scenario.

When you have identified the Item that you need to further analyze the following process can be used. Below the described process, there is a screenshot showing the result of the simulation of the Average Cost calculation of item TEST using the Value entries created in the scenario. In addition there is an Excel sheet attached where the full data set is available where used formulas etc can be reviewed more closely.

1. In the Value Entries table filter on the particular Item that is to be analyzed.               
If Average Cost Calc. Type is per Item&Location&Variant the filter has to cover also these fields with particular values in scope for the analysis.

2. Paste filtered Value entries into Excel.

3. Conversion of Valuation Date into Period:   
Having another Average Cost Calc Period than Day requires the Valuation date to be translated into the chosen Average Cost Calc Period. In this case it’s Month.
In the screenshot below and in the attached Excel sheet the mentioned columns can be found.

a. Column F is added: The Valuation Date column is copied into column F. Thereafter column F is selected and the Format is changed to Number, no decimals. The Valuation Date is now converted to a number in column F.

b. Column G is added and is intended to carry the Year of the Valuation Date: 
Select column G and change Format to Number, no decimals.
Add formula: =YEAR(F2) in cell G2, then double click on the plus sign in the right corner of the cell and the Year is generated for the rest of the lines.

c. Column H is added and is intended to carry the Period No. of the Valuation Date:
Select column H and change Format to Number, no decimals.
Add formula: =MONTH(F2) in cell H2, then double click on the plus in the right corner of the cell and the Month is generated for the rest of the lines.

4. Do a Custom Sorting using the fields as below:

Comments to respective field being a part of the sorting:

Year and Period No.: is the time for when the entry shall be part of the Average cost calculation.

Partial Revaluation: a field that states Yes on Value entries with Type Revaluation. Revaluations affect the valuation of the following period’s outbound entries, not the outbound entries of the same period

Valued quantity: is populated on every Value entry, corresponds to Item ledger entry quantity, Invoiced Quantity or Revalued quantity. Largest to smallest brings the inbound entries to come before the outbound entries of the period and thereby create the base for calculating the average cost of the period.

Item Ledger entry No.: Is to group the value entries attached to same Item ledger entry no.

5. Insert Summary lines where you want to establish the periods Average Cost (grey lines below). A summary line shall be inserted above the first outbound entry of a period. To identify the breakpoint for inserting the summary line follow these steps:

a. Establish the time period to be in scope for the investigation and locate these entries in the sorted Value entry list.  

b. Then follow the stated quantities in field Valued Quantity for the chosen time period.
Identify the first line with negative quantity and you have the first outbound entry of the period.

c. Insert a line for calculations. (Column P, row 4 and 6, row 4 positive Valued Quantity, row 6 negative Valued Quantity, Summary line is inserted as row 4).

6. Make a Sum of the columns; Cost Amount (Actual), Cost Amount (Expected) and Item Ledger Entry Quantity. Calculate the Average Unit Cost of the period (column U) with the following formula:

If you have several Summary lines inserted, make sure to include the previous summary line into the calculation of respective column for the next period.

7. Choose an outbound entry, usually the first outbound entry of the period and then a couple of others, randomly selected in the period or those that for some reasons is of particular interest, and calculate the average cost per unit with the formula above (green, purple and blue sections in screenshot below).

– Does it correspond to the average unit cost of the period?

If not, ensure it is not fixed applied to an inbound entry: If field Valued By Average Cost is False, it is fixed applied to an inbound entry. 
To which entry?; Follow up on the parent Item Ledger entry, field Applies-to Entry shall carry the entry no. of the supplying Item Ledger Entry.
If not fixed applied; establish the Amount Rounding precision and if that has an effect on the Calculated Average cost.

These are the Value entries for item TEST when they have been sorted as described in step 4, where Summary lines has been inserted to establish Average cost for a certain period (step 5,6) and where the  first outbound entry of the period (+ the 2nd in period 10) is calculated (step 7).

Note that all inbound entries in September (Period No. 9) is sorted at the top and demonstrate the effect on all outbound entries in September regardless of the specific valuation date.

To follow the process and be able to review used formulas etc., an Excel sheet is attached and contains the following tabs:

Basic Data

—————

Contains the scenario and what data it creates. Thereafter the basic scenario moves into two paths, one for using Day as Average Cost period and the other for using Month as Average Cost Period. The respective set of Value entries are thereafter pasted into the next tabs.

Average Cost simulation – Day

—————————————-

At the top the Value entries are pasted from the Basic Data scenario addressing the Average cost period of Day.

The Value entries are processed; sorted and calculated as described beneath the section of value entries.

Average Cost simulation – Month

——————————————-

At the top the Value entries are pasted from the Basic Data scenario addressing the Average cost period of Month.

The Value entries are processed; sorted and calculated as described beneath the section of value entries.

Adjust Item Costs/Prices… batch job

Just purchase one material on different rates with different vendors and consume it in manufacturing or do the negative adjustment and then run the batch job to see the effect.

The batch job is in the inventory section of Finance Module.

All this Batch does is updating the CARD Costs and Prices for an Item; it really has no connection to the costs posted to inventory, and in terms of Sales only affects the default sales price of an item. A better name might be “Item Card Cost/Price Update”

To test an example, run the batch on a test database, leave the defaults, but change the Adjustment factor to 1.1.

When you run this, all the Item Card Prices will be increased by 10%

Most of the processes relating to costing, discuss posted documents and entries. Like Posted Sales Invoice and Posted Purchase receipt and Item ledger entry and Value Entry and General Ledger and GL entries. All these tables (and others) are used to calculate the actual costs and prices in Navision, and are what is used for Accounting reporting such as financial Statements. This is all covered under Inventory Costing. The batch processes (periodic Activities) “Post Inventory Cost to G/L” and “Adjust Cost – Item Entries” all belongs in this category. But on the other side BEFORE you post anything you need to do setup, and amongst all the setup, are the fields on the Item card which ARE NOT POSTED to the GL, but are used as the default values before posting. For example Unit Price from the Item card is copied into the Line of a Sales Order as a SUGGESTED sales price. But you can change it here and post a different price. Most of the numbers that you see on the Item card are just defaults or suggestions; they are not the final number that is posted to the GL. All of this is covered by Inventory Setup, and includes the Batch Process “Adjust Item Costs/Prices”.

If you check the code you will find that:

In summary

Inventory Setup is what you do to setup Item Costs and Prices BEFORE they hit the GL. (including “Adjust Item Costs/Prices”). Inventory Costing is what you do with entries AFTER they are posted to correct any changes due to the difference between Expected costs and actual costs.
Just go through these steps and you will know what this batch job does-

1. Create a new Item. Complete all the fields with Unit cost and Unit price. Take a note of all the values you write.

2. Create a purchase order for any quantity with Unit cost other than what is on item card and post the order as received & invoiced.

3. Create a sales order with any quantity other than that of Item card and post as shipped and invoiced.

4. Check the invoicing tab on item card and make a note of all values.

5. Repeat steps 2 and 3 for two-three times by changing the cost and prices every time (also note the values)

6. Now run Adjust cost-item entries and check the Invoicing tab on card. The Cost fields must have updated, but not the prices.

7. Now run Adjust item-cost prices with adjustment factor 1.5 and check the values. The Unit price over the card must have been updated with 50%.

You can see that the batch job is used to change the prices (Sales prices) of items. After running this batch job, every time when you make a Sales order, the updated Price will be copied to Sales Order field “Unit price Excluding VAT”.

Above listed all fields can be adjusted accordingly, specifying Adjustment Factor and Rounding Method.

Similarly it works for SKU also.

Give it a try to understand it more precisely. Follow above steps defined above.

COPY PASTE OPTION IN 2016

Hi All,

As you know with Microsoft Dynamics NAV 2013 onwards, we can copy and paste data from client to excel.

But with Microsoft Dynamics NAV 2016, there is an issue in this functionality.

In this article I would like to discuss one issue that you might face in Microsoft Dynamics NAV 2016.



So Below is the image of issue that you may find in Microsoft Dynamics NAV 2016 -

As you can see only option that we have in Right Click is Copy Rows and there is no Paste Rows.

How to Resolve it?

 - Check the Source Table of Page where you have the issue from About this page.

 - Open Developer Environment, Design the table. In my case the table is 81 Gen. Journal Line.

- Navigate to Table Properties and Change Property "PasteIsValid" To TRUE, as below.

Now Let's see in the page after changing the property.

Hope Microsoft Fix issue in Upcoming Microsoft Dynamics NAV Cumulative Update for 2016.