We use average cost at our company. We bought some material in 2008 and in May 2009 we used the last of it. We bought the material for $100.00. It was a one time buy. Epicor showed a average cost and a last cost of $100.00. We received customer supplied material at $0.00 in July of this year. Our cost stayed the same. Can somebody explain to me how come?
Was the customer supplied material direct to job? A screenshot of the part transaction history tracker should explain it.
2 Likes
How did you “receive” the customer supplied material? I believe that your options typically are:
- you ADJUSTED it into stock. in this case, the cost would not change, because adjustments are always done at the CURRENT average.
- you PURCHASED it for zero dollars… in this case, it SHOULD have affected the price.
Remember that when the quantity on hand reaches zero, the next purchase (or make) of that part will be posted at the actual cost rather than averaging it because there is nothing to average the new cost with.
Thank you everybody. It seems our issue is because we do not issue $o.oo PO’s. Also, since we do not know how much material the customer is sending us there is no PO issued. We have setup the Customer as a supplier and then we do a misc. type receipt in receiving against the part. Since the PO# is then 0 it is not adjusting the average cost.
One thing I like to do is create “models” in Excel for all types of transactions (I have a folder of “models” on my computer)… Attached is an Average Costing model that simulates average cost transactions. You can change the values to test different scenarios. Download this Average Cost Model.xlsx that is not perfect, but it works.
13 Likes
This is excellent Tim! Great way to give a visual explanation of what happens with costing. Thanks for sharing!
-Jeff
1 Like
@timshuwy your Excel model is super helpful to understand how average costing works. I just downloaded it and ran through a couple scenarios. The scenario below didn’t align with what I expected. If we bought qty 100 at $1.00 then another qty 100 at $2.00 it makes sense the average cost is $1.50. Although if we sold 100, I’m confused as to why the average cost is still $1.50. I would have thought average cost should have been $2.00(we sold the 100 parts we purchased at $1.00)…? Can you explain a little bit more how average costing works?
Hey Eddie,
What you’re describing is not Average Cost but Average Lot Costing. What Epicor does with every RECEIPT, is calculate the Average cost. If it doesn’t go into inventory, it doesn’t change the Average cost. If you maintain the lot costs, then you can get where you want. Warning however, both Average and even more so Lot, your transaction MUST be done in order or you’ll garbage up the calculation.
1 Like
Average costs are updated only when an inventory transaction (pur-stk, mfg-stk) changes the unit cost of the inventory in which case the weighted average is calculated. You simply shipped parts which removed the inventory, you didn’t add or change the material unit cost, so at the time you shipped those parts the inventory cost was $1.50
2 Likes
Thanks @Mark_Wonsil and @mjfwagner. It’s not so much what I want - I’m not in finance just trying to understand so I can explain to others what the average cost for materials means…
If I’m following @mjfwagner the average cost is a weighted average of what we buy. Selling / using materials has no impact on average cost. Am I following correctly?
Assuming I’m understanding correctly I think average cost “resets” when we hit qty 0(average cost = next purchase price). Given this, and the understanding that we have very few inventory turns(don’t hit qty 0 for years on many parts) also that we have a high variance in costs(parts are cheap for many years, then go end of life and are really expensive) then end result of this scenario is average cost can be very misleading.
Is my understanding correct?
You are not alone in understanding Average cost. I think it should be named, “Average Inventory Cost” because just Average Cost lets people adopt a personal meaning that doesn’t match what happens. I don’t think anything in Epicor is more misunderstood than inventory costing.
Each time something is received to inventory, Make OR Buy, Epicor extends out the value of the current on hand inventory and the current Average price, adds the value to this, adds the qty of the receipt to the on hand balance then divides the sum of the value by the sum of the quantities to calculate a new value. This confirms what you asked, if the quantity of the on hand is zero (or less!), the new value is going to be the last receipt.
These are the rules. 
I think it is used for accounting purposes which is not misleading and not for any other kind of analysis, like quotation which it would be misleading. What you describe is more of a current “running average” but one would have to create a report to do that.
1 Like
@Mark_Wonsil thanks so much this is exactly what I needed - now I know this is a generic concept not specific to Epicor and understand what it means so know when to use it…
What we’re actually after is a costing method we can use for quotation purposes. What is the recommended/best practice costing method in Epicor for quotation?
Of is there no “silver bullet”, that is it’s very specific to the company where everyone figures out how they want to calculate costs and writes a BPM / custom report to calculate and write it to a UD field / custom report?
Eddie there is no costing method in quoting if you are actually using the manufacturing tab to build a method of manufacturing (BOM and BOO) The labor and burden costs will come from your resource groups/resources tied to your operation, and you can set quoting burden and labor rates on those resource groups which allow you to add a little padding to your labor costs when quoting
For your materials, they will display current cost for each part, whether that part is standard, FIFO, last cost, etc. And you can always override the material costs on your quote BOM based on special agreements you may have made with a supplier based on the size of the quote opportunity, or you may base it on potential future price increases for those materials to protect your future margin
Either way the cost method you decide on really depends on your company and its business model. Many customers use average cost, but there are also many that buy in lot sizes and use lot cost or lot average cost so they know specifically the cost of materials they may use on a specific project or customer order
You may want to check out FIFO and the FIFO cost layers it creates, but even with FIFO at some point you will be looking at an average cost if you have different FIFO layers and different costs across those layers
There are a lot of options for costing in Epicor so hard to say what is best for your business without some more feedback. Be glad to offer additional assistance or answer questions as needed
it
1 Like
for average cost, the Cost only changes on RECEIPTS (as others said). Consumption transaction always uses the “Current” average, and will not change the average.
What you describe may be closer to Lot costing, OR FIFO costing… in Lot costing, the cost that is charged to the sales order is whatever the cost is for that lot… for example, if you receive lot A1 for $1 and then lot B1 for $2… then you ship some of lot B1 first, the cost will be $2 for that lot.
FIFO is totally different. When you do FIFO costing, the system will always consume FIRST cost first. so in your example, you receive 100 @ $1, and 100 @ $2… THEN if you ship 101 pieces… 100 of them will be at $1, and then 1 piece will be shipped at $2. In other words, the “True” cost is consumed in a FIFO manner.
There is another costing method called FIFO Lot costing… this is the most obscure, because it keeps track of the FIFO layer for each lot. but you can ship lot B1 before lot A1, and therefore the cost for lot B1 would be consumed first. This is confusing… but that is because you get to choose the lot number. The “Fifo” part of fifo lot costing is that if you receive the SAME LOT multiple times with multiple costs, it will consume the first lot/cost first.
My excel model does NOT attempt to demonstrate FIFO in any way. it is only designed to fully demonstrate how Average Costing works.
For testing purpose, create a Costing Group and load all purchase parts. Write a BAQ against CostPart table and display all costs (Std, average, last, lot average, etc ) and add supplier price. This will uncover lot of things and help you to decide what cost to use.
Hi Tim,
I independently created a spreadsheet and came up with a AVG Cost of a test part after buy 10, then another 10 (different purchase prices) and got $1,051.50 as the cost. Your spreadsheet confirmed the same. My Epicor shows this though in part transaction history tracker
Even adding 2.5% burden into the formula (2.5% is applied to all our parts) shows different values in Excel compared to Epicor. Now idea how Epicor got to $1,103.00
I love this! Thank you for sharing!
1 Like