I have a request to write a BAQ that someone can enter a date and get the standard costs that were active on that date. (ie. What were our standard costs on 12-31-2018?)
I have taken some looks at the CostPart and PartTran tables. PartTran seems most promising because it appears to track the Cost Adjustment transactions along with Cost Roll Postings (when costs change). However it also has Variance transactions (PO, Job, etc) as part of the ADJ-CST Trans Type and I am struggling to find a field that would filter out the Variances and just give me the Cost Roll and the Cost Adjust Transactions.
you can define multiple COST IDs in the Part Cost table
so… you could create a new cost id for each year.
each year, copy the year end cost from one cost ID to another using the costing workbench
by doing this, you will have historical costs, one for each year (or you could do this monthly as well).
@timshuwy routinely makes me feel stupid. That is a brilliant idea.
No, it’s a site setting (sort of) and be aware that’s “1” and not an integer.
You will want to be careful with this. It’s a little complicated. Each plant can have a costID or they can share them.
Although the costID has a setting for “default plant” or something, the actual assignment is done through a process that creates a bunch of transactions. I made a mess of a test environment (thankfully) by not understanding that.
Hahaha… It was NEVER my intention to make @JasonMcD “feel stupid”…
in reality, the “trick” that I suggested here is really the “old school” method of doing cost rolls… back in the olden days (1980s) the system I worked on didn’t actually do cost adjustment transactions for each part change during the cost roll… the old procedure was:
copy the cost table from 1988 table to 1989 table
adjust all the costs in 1989
when ready to activate the new table:
3.1 run inventory report against 1988 table
3.2 run inventory report against 1989 table
3.3 MANUALLY calculate the total variance
3.4 create a journal entry for the variance
3.5 start using the 1989 table for inventory.
You actually can simulate this same type of function in Epicor (without the manual variance).
with E10, you can:
copy the cost table from 2020 to a new 2021 table
Adjust and roll the cost table in costing workbench… POST when done… BUT NOTE: since this cost ID is not associated to any active SITE id, the “POST” will not do any journal entries.
When ready to activate the new 2021 table, you go into Site Maintenance, and change the cost ID from 2020 to 2021. Doing this will AUTOMATICALLY do all needed journal entries for any adjustments.
Using this method, you can actually have all the rollup of BOMS completed weeks ahead of the actual implementation of the new cost table. You can run reports to see how inventory is theoretically affected by the new cost id…
I favorited this post last year knowing I would eventually come back to it.
Aside from storing the cost elements historically, were you also storing what the original method was for a cost roll?
In other words, since a standard cost is based off of the method, if you want to analyze variances at a detailed level (what materials caused the variance) you would need to compare against the bill of materials that the standard was based off of.
How do you do this? Is there a table somewhere that stores the bill at the time the standard was rolled/created?
Sorry in advance, as I don’t think this helps your question, @utaylor.
But to the original topic, I made a BAQ for historical std. costs a few weeks ago so I might as well share it.
Some caveats:
We use a reason code of STDADJ for setting a new std. cost, so you would need to change that for your business.
We don’t do cost roll ups; not sure how that affects other people that do.
You may want to check my logic on the Plant Cost ID. Explanation
So, we have 2 sites. The newer one, it has its own cost ID that is set up in Site Config (or wherever) and that woks great. But the original site (MfgSys) has no “official” cost ID; it uses the old default of “1” and so I reflected that in the join on the PlantCost table.
We have never bothered to put labor standards on the parts, so rolling up BOMs would be false info. Instead they do it in Excel and calculate labor in the spreadsheet and manually cost-adjust the in-house parts.
There are better ways, yes I know. But that’s what we do today.
I know this is a old post but I donwload the BAQ you posted ACC_StandardCostAtMoment and I tested but didn’t pull any information. I know I am missing something here.
EDIT: Ah, your table jogged my memory. It was this. Well that probably does not work for anyone else. But it’s left-joined so it shouldn’t break it - it’s just useless.
