Steps to calculate Average Cost in Navision

The following steps are not foolproof but it has helped me solved many average cost issues.

1) At item card, click on Item --> Entries --> Value Entries to open the Value Entries screen.

2) Copy all records in the Value Entries screen to Excel.

3) In the Excel worksheet, sort the records by Valuation Date.

4) Add a new column called Cost Amount to the Excel worksheet. Cost Amount = Cost Amount (Expected) + Cost Amount (Actual). Use Excel formula to populate the Cost Amount column for all records.

5) Add another column called Cost Amount (Accumulated). As indicated by the column name, it is the accumulated value of Cost Amount from first record to the last record. Again use Excel formula to populate this column.

6) Add a new column called Quantity (Accumulated). Quantity (Accumulated) is the accumulated quantity from first record to the last record. Use Excel formula to populate this column.

7) Add a column called Avg. Cost. Avg. Cost = Cost Amount (Accumulated) / Quantity (Accumulated).

8) Now, look for the last inbound entry record for each Valuation Date. The Avg. Cost for the last inbound record for each valuation date is the average cost for the day.

Average Cost (LCY) in Item Card

The Average Cost (LCY) field in Item card is calculated as the Sum of Cost Amount (Actual) divide by Invoiced Quantity in Value Entries table for the item.

Average Cost (LCY) = SUM (Cost Amount (Actual)) / SUM (Invoiced Quantity)

You can check the value entries used to calculate the Average Cost (LCY) by clicking on Assist Button for Average Cost (LCY) value to drill down to the value entries.

Average Cost (LCY) field in Item Card

The Average Cost (LCY) field in Item card is updated by Navision when you

1) Post Invoice
2) Inventory Increase
3) Inventory Decrease
4) Adjust Cost - Item Entries

The formula used to calculate the Average Cost (LCY) is as follow:

Average Cost (LCY) = (Cost Amount (Actual) + Cost Amount (Expected)) / Total Item Ledger Entry Quantity