Looking to make BAQ that will give me the true cost of making a part. Want to have the material cost and labor cost contrasted against selling price. Would like to break it down by part number and dept to see potential profitability of that particular part. Looking for ideas on best potential ways to extract this info. Here’s what I have attempted so far.
select
[JobMtl].[JobNum] as [JobMtl_JobNum],
[OrderDtl].[OrderNum] as [OrderDtl_OrderNum],
[Part].[PartNum] as [Part_PartNum],
[Part].[ClassID] as [Part_ClassID],
[JobMtl].[MaterialMtlCost] as [JobMtl_MaterialMtlCost],
[Part].[PartDescription] as [Part_PartDescription]
from Erp.Part as Part
inner join Erp.JobMtl as JobMtl on
Part.Company = JobMtl.Company
And
Part.PartNum = JobMtl.PartNum
and ( JobMtl.MaterialMtlCost <> 0 )
inner join Erp.JobHead as JobHead on
JobMtl.Company = JobHead.Company
And
JobMtl.JobNum = JobHead.JobNum
and ( JobHead.ReqDueDate >= @Start and JobHead.ReqDueDate <= @End )
left outer join Erp.OrderDtl as OrderDtl on
Part.Company = OrderDtl.Company
And
Part.PartNum = OrderDtl.PartNum
where (Part.ClassID <> '' and not Part.PartNum like '%INK%' and not Part.PartNum like '%ppr' and not Part.PartNum like '%CTN' and not Part.PartNum like '%SW' and not Part.PartDescription like '%sharpie%' and not Part.PartDescription like '%marke%' and not Part.PartDescription like '%box%' and not Part.PartDescription like '%carton%')
Will,
How are you wanting to aggregate the costs? An average? Last? average or last # of months?
Depending on that there may be better ways to get to this data.
First have you taken a look at Part Adviser? It has a Profitable tab and a Produced tab that for the part entered will display various cost and price data. If you are looking at having many parts in a grid view, then you may have other options to collect this data.
Take a look at Part Tracker > General > Costs for a part you are interested in, notice that Std, Avg & Last all are populated. This data is in the PartCost table. (this tab is not available in Part Entry)
Also take a look at Job Tracker > Job Details > Assemblies > Costs and click Retrieve. Here the JobAsmbl table holds the summarized costs for each costing element. Assembly 0 would be for the whole job.
You can use the Field help to ID exactly which fields hold the data you need. It would probably be easier to aggregate off of PartCost table or the JobAsmbl tables instead of going down to the JobMtl of each job.
Who know’s depending on what you are after PartCost may already have it.
@Rick_Bird So, what does it mean when in both of those tables only SOME parts have numbers by them? I went and pulled the JobAssmbl table and all that populated was job number, part, description, qty/parent, required qty and that’s it! And in the PartCost table it is hit and miss. No part has every or even most fields higher than zero. Only about 70% of the parts have any field populated other than zero. So, I guess my next question should be, why, and how to change it.
Background: When I was hired on I knew nothing of Epicor. This site and everyone’s help has allowed me to learn rather quickly. My predecessor said something very cryptic when I started, he said Not everything is the way I like it. Maybe you can work on those things. And the more I dig in, the more i understand. I see things like this quite often and it does cause me to questions why, how, and then say let;s fix it.
In the end, I 'd like to be able to pull this type of info and separate it by department so we can see profitability for parts. Basically if I have part a that is in dept b and the waste % is high, but it’s so cheap to make and we are making gravy, it may be better to keep it there instead of moving it to a lower scrap but with higher manufacturing costs. So I need to look at everything from selling price to labor to material costs. Hope this helps in understanding what I’m trying to do and where I am at.
So, now I feel ignorant. I keep forgetting there is more than one field in a tablet hat may have info. I found in the JobAssmbl table the fields that had the info I was looking for. But the partcost table doesn’t have anything of much use. That still throws me for a loop and wondering. But I did notice that not all parts are entered with cost numbers. SO I am assuming that would play a role in this.
So the PartCost table values are updated when a transaction is processed and is able to impact them.
So when a MFG-STK transaction is processed the Cost Elements from JobAsmbl update the Last Cost Elements and Avg Cost Elements. Your Standard Cost Elements are only tracked and updated if the Part is set to be Standard Costed and if a Cost rollup is performed on the part and if appropriate costing was setup on that part’s method (usually done by your Cost Accountant on some interval, Annually, Quarterly and when new parts or methods are approved)
The Job Asmbl table has many many many fields for costs, you want to first make sure you are looking at the right ones, using Field Help in Job Tracker will help you with this. In Job Tracker take note of the field group labels, if you are after that Job’s total cost you want to make sure you have selected Assembly 0 and you are looking at the Assembly Totals section. The Estimated & Actuals are tracked, actual are for that job to date. So until a job is completed and closed the Actuals are probably not final. This is why, in my opinion the PartCost is usually more reliable as those costs are updated on the MFG-STK transaction and USUALLY costs have been posted. If you are missing cost elements in Part Cost and you know there have been jobs ran and received to stock for the part then it may be that Labor or Material transactions are being performed AFTER the Job Receipt to stock. That is now a timing issue with production and is a separate issue.
Could you post a screenshot of a Part’s Part Tracker > Detail > Costs that you suspect is wrong/incorrect/not of much use?
Someone was saying it could be because the items are not calculated until after an order. since pricing is based off of quantity. How does that sound to you?
What kind of activity has this part had?
It doesn’t look like anything at all, no cost roll, not jobs rec’d to stock nor shipped, no PO receipts, nothing… Anything for this part in Part Tran? Part Adviser? Purchase Adviser?
Screenshots perhaps?
But if I saw this I would think this is a brand new part that absolutely nothing has been done with yet.
Hmmm, very odd, based on the screen shots you have posted so far I would think that your Cost Accountant would be freaking out since this is a standard costed part with no cost, so when these jobs are going into inventory and shipping out, no cost would be hitting their accounts… on the job end there would be cost hitting WIP and all that… this is very confusing.
If I were you, I would be checking the PartTran for this part to see what the cost element values are per transaction, from the looks of things so far, I would say those would read zero too… and your cost accountant should advise on why that would be.
So you say this is happening on some but not all parts?
From what you have shared I would say you have bigger problems than trying to pull a BAQ together… no matter how you construct the BAQ you can only work with the data that is there and this data doesn’t look typical to me.
Cost for a part in the part cost is only updated on moving in or out of inventory. Is that part a make to order part? With the part going from MFG-CUS? If that’s the case, the system does not update the average cost. We have a lot of that here because most of our parts are made on the job and the final assembly gets shipped to the customer. Maybe that’s not your situation, but something to be aware of.
For stock parts, the average values for part cost update is triggered from receipts into inventory. For manufactured parts that would be a job receipt. For purchased parts that would be a purchase receipt. The average calculation then kicks in to weight the cost of on hand inventory.
It looks like the Std cost was never set for this part. You can do that using the Cost Adjustment screen.
For non-stock parts the triggers are slightly different since the parts do not go into inventory, but the receipt triggers are still used job to job receipt or the job shipment from WIP, buy to order receipt, buy to job receipt, etc.
Be careful of crossing the stock and non-stock line as part costing may not update as expected. An example is you have a non-stock part with a direct job and you take these as “extra” parts made from this direct job (order may have been cancelled?) and force it into inventory. I have seen where the this does not update the part costs in this case.
Also, it may be helpful to read the average cost calculation definition or the other costs if interested.
You can see the costs used in the part history transactions tracker. It is over to the right a ways, but this will show you the costing history.
We do have custom and stock parts. This one is a custom piece. Interesting info on custom vs stock. Will definitely dig deeper into this. @Rick_Bird you are right. You can only use the info that is in there and work with what is given.
Our setup is something like this: We create Jobs for our stock parts and have a supply on the shelf at all times. Then the custom is created on an order by order basis.
So this is something worth looking at as I am not fully understanding what happens from that moment on their end. All I see is the end result, which I’m not fond of. Anyway, thanks for all the info, I’ll be back when I dig through this and have more questions.
I echo the concerns that Rick raised. You definitely need to read the Costing Tech Ref guide, and make sure your accounts team knows of the issue straight away to give them a chance to fix before any further transactions occur at zero cost.
I’m in agreement with everyone else so far… but a far easier way to look at these numbers would be the Production Detail Report, which can be run directly from the Job Tracker (which will show costs on the displayed job) or from the Reports menu under Job Management. You probably don’t need to create anything fancy yet…
I have been working to craft a part review showing the job history by part.
The key table will be the JobAssmbl - - filtering it to Assy zero will give you the total cost of the part by component.
By focusing on the job vs the cost of sales will help see the trend in job costing.
When I get close - I will post the Dashboard up here for an evaluation.
