Stock status report break down?

So I will start this out saying I’m FAR from a financial guru here so I’m only relaying the question the best I can. I was asked by our controller if there was any report out there that would give a break down of your stock status into the same buckets that are present on the WIP report, “Labor, Burden, Material, SubContract, Mtl Burden, Total”
I’m not sure what he’s asking for so I figured I would check here. :man_shrugging:

First off… Stock Status Report (SSR) uses the specified date for the qtys, but the current value for the costs. So the SSR should never be used by financial people to reconcile a past date. The only exception, would be if STD costing was strictly used (and no cost changes between today and the desired SSR date.

You best bet will be a BAQ report or dashboard. But that would only give you the current QOH’s. It could not be used for historical info without A VERY complicated BAQ.

He did make a statement saying what he is looking for is a “standard costing 101” type item. I have no idea if that means everything we do is STD or not. As noted I’m not financial in any way so I’m not sure how to even determine that.

I believe he only wants to see the current QOH breakdowns…

Any insight on how to go about setting up a BAQ or dashboard to show this info (for current QOH’s) would be appreciated. I’m not sure what tables I should even be looking at to get that data.

PartCost will hold the costs (about 15 fields) for each part. You’ll only need the 5 for the cost method that part uses.

Part Qty’s can be found in PartBin. You’d get multiple records per part if it exists in multiple locations. Sum these together to get the total QOH. I think this may be already summed together in the PartWarehouse or PartPlant table.

If you have multiple plants, with multiple cost ID’s you’ll have to compensate for that as well.

1 Like

Ok, so to get totals I would have to take those standard costs and then multiply them by the QOH to get totals for each PN correct?

Yes. The BAQ should be very straightforward

Looks like I have something that he is looking for. Now he has the process of determining how to filter which parts are shown.


Ideally your BAQ would have fields like Calculated_MtlUnitCost with the expression choosing which MtlCost column (Avg, Std, Last, etc…) based on the parts Cost Method. You get this from the PartPlant table.

Something like

(case when PartPlant.CostMethod = 'A' then PartCost.AvgMaterialCost
 case when PartPlant.CostMethod = 'L' then PartCost.LastMaterialCost
 case when PartPlant.CostMethod = 'S' then PartCost.StdMaterialCost
else -1

Thats a good point. I will tinker with it some more. Thanks.