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
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:
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:
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:
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.
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:
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.
Any feedback to how this process and documentation can be further developed to provide more insight in the average cost calculation is very welcome.
Helene Holmin
Escalation Engineer NAV Costing EMEA
hholmin@microsoft.com
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.
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: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.
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.
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.
Any feedback to how this process and documentation can be further developed to provide more insight in the average cost calculation is very welcome.
Helene Holmin
Escalation Engineer NAV Costing EMEA
hholmin@microsoft.com
Attachment:
Average Cost Calc Analysis_process.xlsx
Average Cost Calc Analysis_process.xlsx 
