Monday, 9 March 2009

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.


Karen Beaulieu said...

I know it's been a while since you posted this, but if you'd like an easier way to view real time average costs in Navision you should take a look at the Pivotier Report Writer from Centerline Software ( Pivotier allows NAV users that have upgraded to SQL Server to create SQL Queries against their NAV database for real time reports that can be output automatically to Excel, Word, PDF, Email and SSRS/Sharepoint. You won't have to cut and paste to Excel, so no room for errors. Pivotier does not require programming knowledge or expertise. It's reports are faster, real-time, colorful with font control and follow user security settings down to the field level in NAV. Thought I'd pass the information along.


Chen said...

Hi Karen,
Thank you for sharing.

Anonymous said...

Thanks for sharing this tutorial. I work for a Navision implementation and integration company, so it's always helpful to find information like this. It helps us improve our own work :)