Creating an "Adjusted" Unit Cost (for sales purposes only)

Kinetic ERP
1

Looking for a sanity check and sounding board… (sorry, this is going to be a long one).

Problem Statement:
We randomly (infrequently) have jobs that go awry for various reasons. All companies do, I suppose. Here’s an example:

An operator is supposed to finish his operation in 8 hours. Instead he takes 40+. This occurred a couple times with this specific operator/employee and he was subsequently let go.

The problem is, the part unit costs now have an outlier. The cost on the latest job(s) are sky high. That’s business. Swallow the pill and move on. EXCEPT… when our sales group goes to quote the next job, they look at historical cost. So this bad apple is now skewing the data.

A GOOD sales person will scrutinize the outlier and spend time to dig in to see the discrepancy, ask questions, etc. “why did this last job cost so much?” Our manufacturing group investigates it… our financial group investigates it. A LOT of lost time in repetitive efforts. And we all come to the same conclusion: This job is not a good data point.

A BAD sales person won’t scrutinize it. Take it for gospel, use it as the latest cost point and we’ll lose future sales because his/her next quotation will be way too high.

Task:
Management has asked me to provide a way to adjust the part unit cost that is visible to sales when they evaluate historical costs on a part. This would both save time and sales struggles.

In the example above, manufacturing knew the hours were outliers relatively quickly. If they had been able to make an adjustment somewhere early on, then our sales group and financial group wouldn’t have had do repetitive work (spending their time/hours) researching the job and ultimately having to ask our manufacturing group what happened.

We don’t want to do any kind of job adjustment and change the actual costs hitting the books. Costs are costs. The 40 hours of labor and terrible cost for that particular job DID occur. The company will absorb it and move on. BUT, I need to find a way to flag this job and present a more realistic cost in the system so sales will use (yes, doctored… but…) more realistic historical cost values.

Possible Solution(s):
The easy way… add a UDColumn on… I’m not sure which table, that an appointed user can manually update via dashboard and I can then pull the value in to various forms, dashboards and reports to display an adjusted unit cost for a given part on a given job.

Basically the appointed person would recalculate the job cost outside of Epicor and insert an updated unit cost into one column that I would then push out to forms/reports where needed.

In this approach, I’m struggle to determine which table that UD column would live on. It MAY be simpler to use a UD table and use the job, assembly and part as keys and then use one number column for an adjusted unit cost value. Perhaps also a char column so they can log an explanation of the adjustment.

The hard (but probably correct way)… I’ve been pondering this and trying to devise the best approach. I’m thinking the right way to approach this would be to add UD columns to tables such as the LaborDtl table (for example).

I could then create an updateable dashboard (similar to the job status dashboard) that pulls in LaborDtl records for a given job. The appointed user could then make adjustments to the records (adds an AdjustedLaborHrs value).

I would then have to adjust any subsequent queries, dashboards and report that utilizes LaborDtl data and have it run two calculations… one to get the ACTUAL costs (which is already done, obviously)… but also have it recalculate based on ADJUSTED costs (using the “LaborDtl.AdjustedLaborHrs_c” instead of “LaborDtl.LaborHrs” when doing its calculation).

Again, been pondering this and this approach would mean I would also then have to add UD columns to tables like JobAsmbl, so it can calculate/display new “adjusted” TLALabor and LLALabor, etc. culminating in a new adjusted total assembly costs and adjusted unit costs.

This approach, though probably the best solution, will snowball quickly and require me to duplicate the entire job cost process across multiple tables as well as customizing the forms, dashboards, and reports where we want to see the “adjusted” costs.

~*~

For anybody who survived and is still with me… what are your thoughts? Are there better solutions?

2

Sounds like a most statistical result set is needed for this, but as for the costing maybe you could create a BPM to reverse the applied costs against that outlier job for the part.

3

But I don’t want to change the ACTUAL job cost. In any approach, I want the ACTUAL job cost to stay intact. I just need to present an alternate reality (if that makes sense).

4

Still here.

We had an incident where the main paper component was missing from a BOM, so the costs implied an excessively false profit.

We do our costing as STD on manufactured and AVG on purchased…so the simple fix for us was to get the BOM and standard cost back in line for future transactions.

For the sake of our internally-built sales reporting (Access/ODBC) we set up an override table (company, partnum, invcnum, invcline, unit cost)…and kept all Kinetic data intact so our auditors don’t squawk.

In your case, a UD table might be a better solution if you do a lot with Kinetic-based objects like BAQs/dashboards for your sales team. Use the UD table’s unit cost if found (see option below), otherwise use the five cost buckets in InvcDtl…{Mtl/Bur/Lbr/MtlBur/Sub}UnitCost.

  • Option: a total unit cost or, if necessary, set up the five buckets in the UD.
3 Likes
5

It’s funny because you’re not the only one.

Create a Notational Standard Cost | Epicor Kinetic Ideas Portal

1 Like
6

If you want that outlier to be ignored against the true part cost (to mfg) you (most likely) would need a reversing adjustment to the mfg part cost to ignore it on other forms etc. Not changing the JOB cost just the cost applied to the mfg part of the job.

7

And then the man himself responded with a novel idea:

image
image839×168 20.2 KB

1 Like
8

Nice! I’m just a few years behind you and your idea :rofl:

Tim’s suggestion of an alternate Costing method is intriguing. I would still need to set up the UD columns to house the adjusted values… but maybe that would do the calculating for me? Is that the idea?

9

Is that Site Cost Maint? Trying read up on that.

10

David, I’m not 100% sure if his idea would work for all types of costing, but you can try it out.

I know that if you use a different cost site ID you can maintain a whole different set of costs there and roll and post them independently. So you could do whatever you want I guess and then use the cost workbench to roll everything and post it.

As job costing goes, those costs won’t use this cost site ID… so his idea won’t help you when it comes to looking at job costs.

1 Like
11

I’d have to reference the help section in Kinetic to see, but that’s the general area, yeah. Site cost maintenance.

12

That is actually a really intriguing idea. But don’t you set the cost method for the part on the sites tab (PartPlant table) and then there is one cost ID per plant? I am trying to understand the comment about using different cost methods for different cost IDs. Although, I guess the costing workbench lets you roll up a standard cost for a part regardless of the costing method? Or worst case you can just do your own cost outside the system and dmt it into the relevant fields?

1 Like
13

Too bad its expired.

1 Like
14

It’s this. you would just use whatever costs you want and get those into the system via DMT or whatever. And then you roll and post those to the cost site ID which you DONT assign to any plant, they are just hanging out there in cyberspace for you to reference for your own enjoyment.

15

Ah, can I submit it again?

16

I think you just have to make a new one?

1 Like
17

But yeah the ideal, ideal functionality would be a notational cost in whatever costing method you wanted so that it actually updates like the rest of the costs you have assigned to your site.

18

It seems to me that one approach is to use a ‘low-pass filter’. This is a way to statistically filter out spikes in the data, hopefully without disturbing any trending changes.

So, the idea would be to implement this in a BAQ which is then driving a dashboard or report that the sales people would use to examine historical job costs. This prevents having to hand-adjust historical data or use UD fields and such.

4 Likes
19

Nothing is ever going to replace a data engineer and data scientist working data in ways that model the business need, we can try all we want with the BAQ that filters things or uses stats. But bad data is bad data, it needs to be removed or cleansed or whatever. Stats off bad data give bad stats. I get what you’re saying though, if your data is pretty consistent and you’ve looked at it and the anomalies don’t impact a certain SKU and everything is rosy we can just do an easy filter or whatever.

20

This is just extra rows in the PartCost table

No, it’s just static data in that table. Without assigning it to a Plant (and you don’t want to), then they are just abandoned data. Not quite orphan, but close.

FYI, there is no DMT for this, so you have to do it via uBAQ.

Can you clarify that? Do they look at individual jobs? Or do they look at actual “Last Cost” (meaning the data in Part Tracker > Costs)?

And what costing method are the manufactured parts? Standard or Last? I looked all through the post but didn’t see if you said.

Yes.

I’d never heard of a cost accountant/analyst until about 6 years ago, and we’ve only recently actually created the position.

But now that I understand it, I can’t unsee it.

Without a cost analyst, you end up with every manager trying to find “that kid who is good with Excel” in their department and having ALL OF THEM do the same work without talking to each other.

And they all do their level best even though none have ever heard of a pivot table. But most usually miss some critical part of costing since they don’t know how it all works together.

2 Likes