Average Cost - History

Purchasing department was very surprised today when they noticed some Average costs we’re being set as Last Cost. I found the below article on the Epicare site, basically states that if a part goes to zero qty, the average cost resets. Their department would like something so they can see trends in averages, so i’ll probably start working on a dashboard with pretty bar graph. I think i can use PO transactions for that.

Curious if anyone has similar situation with their department using Cost Average.

Frequently Asked Questions

1. What do we need to check first if we believe that the Average Cost for a part does not seem to be correct?

The first thing to consider is if the part was received directly to the job. If the part was received directly to a job and not into inventory, then the cost will go directly to the job and not to the part. Review the Part Transaction History Tracker for PUR-MTL transactions.

2. What are other areas to check if the parts were not received to a job?

Look for parts that were received directly to inspection and verify if the costs are still in inspection.

3. How are the costs calculated for the Average Costing method?

This method calculates the weighted average of all receipt costs for all parts in this cost group. Costs recalculate every time a receipt transaction is processed. The application calculates Average cost for all parts regardless of their default costing method.

(previous onhand qty * previous avg unit cost) + (receipt qty * receipt unit cost)/new onhand qty)

4. How are the costs calculated for Average Costed parts that are Purchase Direct or Make Direct?

These are never received into stock so there is no receipt to average into the calculation. The costs go directly to the job.

5. Why does the system show both Average Cost and Standard Cost for parts that are set to Average costing?

Regardless of which costing method you use, the application always maintains both the average Unit Material
cost and average Unit Material Burden cost (if calculated)of each part. You can display these average unit costs
within Cost Adjustment. You can then see these values as you modify the unit costs of the part.

6. What does the weighted part of the calculation mean to me when verifying costs?

The above formula will only work and be accurate when the onhand quantity is greater than 0. When the onhand qty drops to 0 or below, the Average cost will be overwritten with the full new value from the next PO to stock (PUR-STK) or next incoming Job qty to Stock rather than being recomputed using the Weighted Average Cost formula. This is because the formula will only work and be accurate when the on-hand quantity is greater than 0. Then once the onhand qty remains above zero in successive incoming receipts to stock, from Purchase Orders or Jobs, then it will again start recomputing the average cost using the Weighted Average cost formula.

Only everybody.

Average cost is for inventory currently in stock. It is not a rolling average of makes or buys. It’s sole purpose is to calculate inventory value. I have petitioned Education to change the title of the field to “Avg Inv Cost” to make the distinction. It is one of the most misunderstood concepts in Epicor.

You are on the right track by creating a dashboard. Do one for make parts as well if you do any Make Direct work.

I appreciate that i’m not alone on this =)

Not at all.

Usually, the next question is, “This is so simple. Why doesn’t Epicor provide this?”

And then you go and start to write your own. Simple. Just average all PO receipts and bingo.

“No. These are wrong. There are some buys that are much too high”

“Those were for engineering”

“Eliminate those please”

Here’s the new version.

“These receipts are zero. Why?”

“Some were free samples and others were replacements for bad parts”

“Eliminate those”

Here’s the latest version.

“Still too high. The price was wrong on that one PO and we received a credit memo for the difference”

Simple query my :peach:



Haha, looking forward to that!

1 Like

Got an SSRS report started. I’ll have to check for accuracy. I’m using PartTran table which PUR-* (PUR-STK) (PUR-CUS) etc.





1 Like

Do you use Inspection Processing? (“PUR-INS”) If so, do you count it if it goes there and fails?

I will have to fix that. Negative PO works (aka unreceiving)

Month average is bottom right which is charted.

$812.00 / 20 = $40.60

1 Like


I may have shared this before… but attached is an Excel Spreadsheet that emulates how the Average Cost is calculated. Yes I agree that the term “Average Cost” is sometimes confused by people who were not trained on the old school average costing method… Average is not an average of all the unit costs. Instead it is a weighted average of the total cost of all the units in stock. The formula is:
New Average Cost = ((Old Qty * Old Average Cost) + (Additional Qty * Cost)) / (old qty + Additional Qty)
In other words, if you have qty 100 @ $1 and you just purchased 10 @ $5, the average is NOT going to be $3… it will be a weighted average of $150/105 or $1.429. The new average cost is continually calculated based on the above formula after each and every RECEIPT. Also note that ISSUE and ADJUSTMENT transactions do not change the average cost. Only new receipts will affect the average cost.
This spreadsheet allows you to model these transactions. You can play with the transaction types, quantities, and costs to see the results over time.

Average Cost Model.xlsx (12.9 KB)

1 Like

This is why I suggested that the documentation call this out. This pops up often in this group, in the EUG, and I’m sure many times for Epicor Support. Adding this explanation to the help file would eliminate a lot of wasted energy, IMHO.