Cost changes happen. How are you all analyzing the impact of these changes?
If raw material xyz goes up $1.00, what do you do to analyze that change and present it to the stakeholders in the company?
We use standard costing so right now I am doing a cost roll in the costing workbench and then printing the cost group report to show overall change to unit cost and then adding in some extra info like total sales $ of the parent part and the % of sales that the specific part makes up and then what the increase in the raw material cost would do to the profit margin/cost of sales.
For custom, part-on-the-fly, sales I am pretty limited though. I can see how much raw material xyz was used on a custom job and then tell them how much more it would have cost if the raw material was now $1.00 more… (not sure how helpful this really is though) because who knows if we will do that custom job again or if our volume of custom jobs using material xyz will increase or decrease in the future.
The thing I am struggling with is getting this to all come together. I am using some built in functionality and reports but then I am adding more info from other queries to it to try and make it more useful.
All in all, it’s been tedious and I have yet to arrive at a final product/first draft. And it’s going to be a rough draft at that… very manual and one by one execution of data sets for each raw material price change compiled to be one big report on overall impact…
Is there any functionality I am missing in Epicor that could help with this task or is this custom through and through?
Any ideas, examples, or guidance is appreciated. I will try to post what I come up with to see if it helps anyone.
@utaylor I have a margin report that I wrote years ago that I am reworking for our new management that I will share once it is done. We are 100% custom, so this is basically our backlog with anticipated margins under X or over Y. We are lot fifo costed, so I use the standard fields to build costing for each release to predict our margins and flag issues. Since you are standard you may need to get component costs from PODetail to compare to standard.
I use excel also for such analysis. i find Epicor not much helpful when i comes to analysis in the costing side.
If i updated a single or group of purchase part cost and need to know what impact it has on parent mfg parts, then my current method is as follow (not ideal but it gets what i need).
Take the purchase part(s) i updated the standard cost for and then run the “where used” report on them to get all the parent parts. Unfortunately, the standard “Where used” report is in pdf. format, so not much useful for data analysis.
I have created a basic Crystal Report version of the report that gives me the parent parts and revisions (crawl up BOMs and find all the parent parts and indented levels where they are used). The report i created is not in Epicor, so when i run the regular "Where used’ report, instead of pdf. output, i tell it to give it to me in XML format. I take the XML and update my crystal report which gives it to me in a human readable format. I could download that into excel. The output is a list of all the parts where the purchase part is used in any BOM.
I take those parent parts and use the Costing Workbench to mock the cost roll (not posted -delete the group after i get what i want), which gives me the new cost of the parent parts which include the updated purchase parts.
If you just updated only 1 purchase part then you might be able to run the standard Epicor “where used” report but change the Output format to Excel. This should give you a list of all the parent parts.
I usually analyze 50 or more purchase parts at once, for that i dont like to use the standard where used report in excel format because the component purchase part is not listed next to the parent but instead at the top. That’s why i used the crystal report which shows me the purchase parts next to each parent part.
I can help you with this Al whenever you want. I made a report style that is a simple table of all the parts in the where used reports so I can download it in table format by changing the output type to “EXCEL” instead of PDF, and use it in paste insert fashion as a filter to run other reports.
The where used report has an indented option. What I have been doing is taking the raw material part and then running the where used indented. This gives me all the parts that are potentially affected and then I run those into the costing workbench like you do and update the raw material part’s cost and then do the roll (not post) which updates any parts in the costing workbench to their newer cost given the change to the raw material part cost. Any parts that changed cost are ones that the raw material part impacted…
So while the where used report, indented option, gives every material in every bill related to your raw material, the costing workbench clarifies which ones are directly impacted when you do the roll and print the cost group report showing changed items only.
The difficult part for me has been eliminating other changes to bills (i.e. opreation prod std, scrap, etc.) and those impacts to the cost so that I can isolate what the raw material cost change alone is going to do to the part.
To isolate the costs and only see the raw material impact, I have had to run the BOM cost report for single level parts to get what their currrent cost is and then compare that to the cost on the part in the costing workbench after the roll. In other words, BOM Cost report shows current cost of current bill (which may or may not be different than the current standard cost). A cost roll in the workbench would yield the same result as running the BOM cost report. However, the cost group change report doesn’t calculate based on iterations of rolls, it uses the current standard cost (if you’re standard cost). And I know my standard costs are out of date… If we kept them up I could trust them and use the cost group report as-is…
After writing this I could actually just make two costing workbenches and compare the costs between them. Roll one and then roll one with the cost change… no need for the BOM cost report. And the costing workbench uses lot sizes which the BOM cost report isn’t as clear about…
Doesn’t the BOM Cost Report kind of simulate the cost roll and show all the components with the new costs? That’s what i have noticed. If i run the BOM cost report on a part with other mfg. component parts in the BOM, they all get updated in the report. The BOM Cost Report and the Cost Workbench showing the same cost after the cost roll.
Have you tried using the quote? You can bring in BOM’s from past jobs I believe, and there is a whole worksheet section to help with looking at the costing and pricing and profit.
It even can take into account price breaks.
But… this is only one assembly at a time. So maybe not what you need.
My idea is to create a baq that returns the required materials with quantities, and required operations with labor hours for the relevant set of parts, and pull that into excel. And then set up some named ranges for adjustable parameters such as labor cost, burden, cost per pound for metals, and things like that. And then set up the sheet with formulas (or perhaps a small VBA routine) to automatically calculate the “what if” cost for each part based on the method of manufacturing details and the variable parameters. I haven’t gotten too far with this idea (too many other fires) but throwing it out there in case it gets you thinking of other solutions to what you are trying to do. The main challenge I find is that the logic to go from the method of manufacturing to the standard cost is obscured within Epicor, so that logic has to be reproduced completely from scratch (would love to be wrong about this).
