[Todd Hughes]

Good morning NAVUG community…for anyone using standard costing in NAV, I am trying to determine a way to calculate historic standard costs per item.  Has anyone been able to successfully build a routine / report (internal or external) that accurately calculates the standard cost of an item?  If so, I would be curious the table(s) used and the calculations / parameters applied.  I have been informed that this information is not stored within NAV and a mod would be required to do so.  Thank you for any information you are willing to share.

——————————
Todd Hughes
IT Manager
ITW Pro Brands
Tucker GA
——————————

[Jason Nicolaou]

?Good morning Todd.  If you are calculating the cost why have you selected standard cost?  NAV does a beautiful job calculating it for you in order to provide real-time costing when the items are consumed by production or a sale.

If you environment requires standard cost, then will you share your equation used for calculating the standard cost today?  Maybe a slight modification to the out-of-the-box logic of one of the current offerings will get you there inexpensively. 

Best of luck!

——————————
Jason Nicolaou
Solutions Architect
Socius
Dublin OH
——————————
——————————————-

[Jason Wilder]

This won’t help you with the past but we created a table that stores some infomration by item whenever we calculate standard cost.  We store Item No., Date of change, Old Standard Cost, New Standard Cost, User ID and Quantity On Hand.  Tell me if you are interested and I can tell you how we did it (it’s a mod), fairly easy.

——————————
Jason Wilder
Senior Application Developer
Stonewall Kitchen
York ME
——————————
——————————————-

[Ben Baxter]

This would be a challenging calculation, and I don’t think the calculation exists in base NAV.  You could build a table to store the historical standard costs, and a function to fill the table.

Both Routing Versions and BOM Versions have an Effective Starting Date.  This sounds great, except that the Work/Machine Centers and Item Records don’t track a Unit Cost change over time.

You would need the calculation to find Item Ledger Entries and Capacity Ledger Entries for the given time period to come up with a Unit Cost for the Items and W/M Centers.  If you can get an accurate Unit Cost for the time period, you would be able to calculate the Standard based on the Date of the Versions.

The downfall is you will eventually hit a time period where there is no historical ledger.  You would also have to try to build your historical unit cost as close to the Costing Method of the Item, which will probably have some gaps as well.

Essentially you could get close, but I doubt it will ever be an accurate representation of the historical standard cost of the Item.

On the positive side, you could build a routine that starts storing historical standard cost calculations today, so that in the future you have this history, and it will be far more accurate.

——————————
Ben Baxter
Consultant
Accent Software, Inc.
Carmel IN
——————————
——————————————-

[James Ellard]

?Hi Todd

I am not sure that I have understood your question fully but a simple solution may be just to enable the change log for the relevant table and field(s) you require. This will store any new, modified and deleted entries as you require.

——————————
James Ellard

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

[Rick Dill]

?HI Todd, from the title of your topic, my assumption is that you are looking to reference an old standard cost to be able to reference it.

There is a perception that NAV does not store historical standard cost information, but that is not technically true. If you use the Standard Cost worksheet, then you have the tool you need to store historical standard cost information.

The standard Cost worksheet allows you to have multiple Standard Cost Worksheet batches. So, if you are rolling costs for fiscal year 2017, you have a batch called 2017. If you rolled costs for 2016 and kept that batch, then you have your historical Standard Cost table.

The problem is that it would be a challenge to back in time to do so, but you can do so by creating a Standard Cost Worksheet for that past cost. You could pull in all your purchased items, but you would need to know what your purchase standard cost was for these items. You would be able to find this by looking at your ILE for these records, but that may take some work. You would also be able to load your previous year’s labor and overhead rates, and if you have routings with versioned dates, then you can use standard NAV BOM and Routing date information to roll your costs based on your historical costs.

My recommendation to all who want to hold “Historical” standard cost information is to use the Standard Cost worksheet, and keep your final version of your costs in the standard cost worksheet after you have implemented them.

NAV does store historical standard cost information, but you need to know how to use the system to do so.

I hope this helps

——————————
Rick Dill
Consultant
ArcherPoint Inc.
Waupaca WI
——————————
——————————————-

[Ian Ray]

To add to Rick’s suggestion, you can retrieve old cost information with Jet Express, although it is some effort.

To get started, a basic setup could be as follows

Build a table with “Value Entry” table.

For fields, you will want (at minimum) Posting Date, Entry Type, Document No., Item No., and Cost per Unit. This can be filtered to where “Item Ledger Entry Type” = “Purchase” and “Entry Type” = “Direct Cost|Variance”. You will also want to have Posting Date and Item No. left as user defined.

I would suggest running the report for a set of items over specific date ranges. The result will something look like this

The above is a hypothetical situation where item 12345 was $1.00 standard cost today and $2.00 tomorrow, with the actual cost being $1.50 and variances -$0.50 and +$0.50 respectively. Thus, Direct Cost + Variance = Standard Cost.

You can then make  a pivot table summing the variance and direct cost by date, document no., and item no. This will produce the standard cost on a particular purchase invoice for a particular item at a particular date.

——————————
Ian Ray
Cypress Grove
Arcata CA
——————————
——————————————-