I am working on uplifting our customer price Evaluation process. Currently this involves:
Updating a dummy vendor price list with before and after material costs (distinction based on effective date) for the purposes of evaluating material cost changes.
Running a dashboard to take these before and after material costs and marry them to customer pricing through the BOM. This projects the costs/ profitability at the “before” costs when customer pricing was last evaluated and the impact of the cost change. The BAQ is a monster and takes a while to run. (5-6 minutes)
Review and determination of a new price for impacted items.
Communication to customer service of new prices by sales/management.
Slightly automated new price list entry by Customer Service. The current price list is exported to excel and paste inserted into a manually created new price list.
The new price list contains only the prices that changed
Price list hierarchy is maintained so the new price list is selected first on orders.
Some goals:
Uplift all to kinetic
Automate and/or fundamentally prove the material cost “before and after” process
Ideas other than the vendor price dummy list?
We have tried to use combinations of comparing a parameter date to native vendor price list entries or receipts with some logic to find the most recent or most recent prior. This ends up being inaccurate since the time of customer price evaluation uses both prior and upcoming material cost changes. The dummy list allows the “after” prices to become the next evaluation’s “before” prices.
Improve the query performance
Could it be run on a schedule to populate UD fields to store some of the more demanding information on the top level?
Other ideas?
Combine 2-5 into the dashboard by having an updatable field for the new price, deal with the customer price hierarchy for the new list.
Two steps- 1) determine new price to save in a UD field on the current list. This will allow users to save their work as the progress through the new price decisions 2) second grid to move that dataview to and action to perform the creation of the new price list.
Improve how price lists are managed.
Currently many, many active price lists per customer.
Need to retain price history
No way to track the “real” effective date of parts added between price updates, since the effective date is on the PL level. ( could add a UD field on the price break table and update it via BPM?)
We make custom parts for our customers, so each part is specific customer/ groups of customer ID that are the same end customer. Price groups won’t work for us (I think).
I’ve read all the topics on here about customer price lists without finding what I am looking for, but maybe this context will help or there are new ideas floating around.
I appreciate any input on lessons learned/ best practices.
Add your ERP database as an external data source to the ERP. Then recreate your original BAQ as an External BAQ. This sidesteps some of the overhead that normal BAQ’s have with complex queries.
Paste the BAQ pseudo-SQL into SSMS/VS and run an analysis of the execution plan. Add any needed indexes.
I don’t have to take these measures very often, but I’ve seen greater than 10x performance improvements (~30 sec to ~2) when using them.
For reconstructing the costs, I am using the standard labor and burden and adding in the cost elements from the BOM using the dummy supplier price list. This is complicated due to the number/layers of subassemblies and UOM differences for both the materials and manufactured part (IUM vs SUM) along the way. For Materials not in the dummy vendor price list, I am using the current effective price of the primary vendor for the material.
Mike, do you know that you can change the price of the raw material in the costing workbench and perform the rollup and then print the report that shows you what that change did to each material? obivously DO NOT post the group haha, but it’ll allow you to analyze what the change did to all the upper level parts that reference it. You can then save that data using the archive setting on your report and query that dataset and join the other price lists on that finished part in the saved dataset to see the impact to the margin, etc.
Is the comparison current standard cost vs the changed price? If so, we need to compare some past material cost to a proposed one. Many times, our prices are locked into a contract, and we need to delay many cost changes before we update our customer pricing.
@mzahn at the end of the day it’s not what this was really built for though I wish it was… so we are kind of hijacking it to do analysis. I would tread really carefully. I almost feel like doing it all in a copy of production that nobody can log into so you can just go in there and jack around creating new methods, costing sites, etc.