Hello All,
Does anyone know how the Sales Gross Margin report is getting the material cost for a purchased part and what table it is getting it from?
More specifically, how does it know what the material cost is for an order line fulfilled 3 years ago. Is it looking at the POs received at that time or does it have this info stored in a table?
Any help is greatly appreciated.
Respectfully,
Utah
All costs are stored in the partTran table with the transaction.
Vinay Kamboj
I believe costs are also in the InvcDtl table; MtlUnitCost, LbrUnitCost, BurUnitCost, SubUnitCost, and MtlBurUnitCost
@Vinaykamboj the part tran table is pretty detailed, but you are correct, there are costs associated to each transaction there. The hard part would be rolling those transactions up to the invoice line to get the cost elements.
@lgraham I just noticed that last night, the only issue I have with these costs at the invoice level is trying to understand how they were computed which might be where @Vinaykamboj 's answer comes in.
I just have a feeling that if I report on the invoicedtl costs, my boss will ask me how those costs were captured in which case I won’t know. Do either of you know how those costs are computed and from which tables the values are sourced from?
This may be a stretch, but would you be able to give an example of a ship from stock invoice line? For example, the material cost comes from the last PO received for that part, or maybe the material cost comes from the average of some of the POs or the part tran table.
In the end whether I get the cost from the invoicedtl table or whether I get it from part tran table, I will have to figure out how those costs were computed.
Excuse the train of thought post…
Thanks for your answers and help 
On a ship-from-stock part, and that part is set for Average cost method, the cost shown will be the average cost of all that part numbers’ quantity at time of shipment. For instance, if there were 30 in stock, and you’d bought 10 for $5/ea, 10 more for $6/ea, and 10 more for $5.78/ea, Epicor would understand that you had 30 in stock at a value of $5.59333/ea. Unless the parts were lot- or serial-tracked, it would be virtually impossible to tell which individual part came from which individual purchase order.
Epicor also doesn’t do a very good job of calculating historical stock cost levels. If you run a Stock Status report with a date in the past, it will give you correct quantities for the time but not the correct costs (there are lots of threads about that here).
What kind of data is your boss looking for? There may be other ways of getting what he needs.
@Ernie Thank you for your insights! We are starting a project with gross margin analysis and so I am trying to understand the background of the cost elements and the way that Epicor is calculating them so I can explain to my boss that it is using an average cost like you said.
In short, just trying to find out the why and how behind the cost elements.
Thanks so much again for your insights.
Respectfully,
Utah
I would point you to the Job Costing Technical Reference Guide (downloadable from Epicweb). For Average Cost it says, “This costing method maintains a rolling average of the part cost. Using this method, the application totals the costs of the parts received to inventory and then divides this amount by the current On-Hand Quantity.”
All the other cost types are described as well. I’d say it’s exciting reading, but I’m really not THAT much of a geek.
@Ernie How about a guide for the ship from stock cost? Is there something out there for that as well? Would that just be the inventory transaction hierarchy reference guide?
If your part is Average costed, then it would be what I sent you earlier. The way it gets transacted OUT of inventory (shipment, adjustment, issuance to a job, whatever) has no bearing.
has anyone got any epicor 9 gross magrin reports they could share please.