Good morning,
We are thinking about making some of our financial reports a bit easier to run. As it is now, we have some parts that are in inventory and have been there for over a year. After a year of sitting in stock, these parts no longer have value. Currently, we use Cost adjustment to 0 out the cost for parts in stock longer than a year. I think we call these obsolete inventory.
You might already see a problem with this approach. The biggest problem is that when we 0 out cost for a part number, any new parts that get added to inventory change the value for all the old parts too. So what was 10 old parts in stock with 0 value, now has 10 old parts with the value of the newest part added to stock, plus one new part.
My question to you all is, what is the best way to do this in Epicor? The goal is to be able to keep these old parts with 0 value off of some specific financial reports. We do not want to use a “reserve” journal entry for reasons. The ideal solution would allow us to flag specific lots in inventory and keep those off of our reports. Having 0 value is not a critical part of this, as long as those parts are not included in our reports.
We have a backup solution that involves essentially duplicating our current warehouses and bins, and including a prefix like Zero_ or something to identify the warehouses differently. Then we could transfer the old lots out to the new duplicate bins. The part would still be stored in the same physical location, but now would be separated by warehouse in Epicor. This would allow finance to run their inventory reports on just the warehouses that have newer parts. Leaving out the warehouses that contain older parts. We wouldn’t even have to 0 out the value for old parts if we went this route.
What do you all think? I know there are a lot of features in Epicor that we don’t fully use. Perhaps this is a common business case that Epicor has already thought of, and I just don’t know the right way to ask it.
We don’t use the stock provision codes yet.
We use “Last” costing.
We have mostly lot-tracked parts.
What I have seen in the past was a report to identify slow and/or obsolete parts. Every so often, finance would calculate an “Inventory Reserve” which is an contra-asset account. The costs on the parts remain but the inventory value in the balance sheet is reduced by the reserve amount. When the parts are actually disposed, we used a reason code that would remove the value from the reserve as well as inventory. It worked pretty well.
The process Mark described is what we’re setting up at one of our facilities and is the way our CFO has handled this at previous companies. We have a report showing lots older than one year and then Finance will make quarterly entries to the Inventory Reserve account that makes the value of these parts zero on the balance sheet but allows us to keep our costing in Epicor.
@NateS - If you remove obsolete lots from your stock reports, how is Finance expecting that to affect or tie into the Inventory balance sheet accounts? Unless you make cost adjustments to set the cost of these parts to zero, there will be an inventory value in Epicor that you’ll need a reserve account for.
Our Finance people said that “reserve” journal entry would come up as the go-to solution. I don’t know finance well enough to say why we can’t, suffice to say that I’ve been told we aren’t going the reserve journal entry route. There could be good reasons for this on their end.
EDIT: It sounds like we technically could use this approach. But for some reason it is more complicated and less desirable for finance.
We do set our part cost to 0 using cost adjustment after a year in stock, and no other action (last ref date >= 1 year). In some cases, we might know that a part is going to be ordered by a customer soon. Rather than move these old stock to another warehouse, or 0 out their value, we would leave them alone. Once they have been in stock for a year, they are eligible to have 0 value. But we don’t want to assign 0 value as a blanket. There may be some parts we want to retain value for because an order is coming up in the near future.
We do this, but we are FIFO Costing on our parts so we are able to use the FIFO date to calculate how long a given quantity has been in inventory and base the reserve on that.
If you use Lot Average cost then you could set the cost for old lots to zero without affecting the cost of newer lots. That may be a bit easier than going the full FIFO costing route.
Just to clarify, we dont ever adjust the Part cost in epicor it is only an SSRS report that tells finance how to make the adjustment on the financial reports.
Lot Average keeps a different cost for each lot, so you can adjust each lot’s cost separately.
Of course, to do this, you must turn on lot tracking and then also use Lot Average cost method. When you use Lot Average, and if you receive to a new lot with every receipt, you essentially have an “actual lot cost” system. ie…
Day 1, you receive 10 parts to lot A at $1
Day 2, you receive 15 parts to lot b at $2
Day 3, you receive 5 parts to lot C at $3
On day four, you issue material from stock… it will ask you which lot. the cost that is consumed from stock will always be dependent on which lot you choose… if you need 12, the stockroom might choose to issue from lot B, which would cost $2 each.
“Last cost” is typically the least recommended costing method. the logic of last cost does some behaviour that is not always intended, for example:
1000 parts received at $1 = GL now has $1000 in stock
1 additional part received but it was a sample, so it had a $0 value. Now your inventory value for the 1000 parts is zero. This caused a negative $1000 std cost variance to post
again… another 1 part is received but this time, it was a $3 charge… now you have 1002 parts all set to $3, totaling $3006.
Each time you receive something at a last cost that is different from the previous last cost, all the inventory that is in stock gets adjusted in value. Most companies dont like this.
If you use AVERAGE, it does a WEIGHTED average of the receipts. There is no adjustment. These three transactions above would result in a final value of $1003 total, or $1.003 each per part. (assuming not using Lot Average)… if you are in Lot Average, then as long as you use a new lot number, the new cost would be specifically for that lot.
We have added a field into the PART table to mark OBSOLETE. have not censored these parts from the inventory reports yet, but we do a reserve adjustment in Accounting, so we may not change the reports.
Just curious, but why not use the canned “run out” check box? Not clear enough to users or are you using that function separate from obsoleting parts?
Run Out is for depletion of stock, but marking somehting obsolete is different. We have some customers that stop programs without notice and then parts are obsoleted.
You might want to check out Slow moving inventory and excess inventory reports in Inventory management.
Make sure to first setup Stock Provisioning for the reports.
This way you leave the cost as is, but if there hasn’t been any movement for a while, the report will reflect your suggested value.
You are an inventory manager. At the end of the year you need to calculate stock provision for excess inventory, slow moving inventory, and inventory that passed its due date and can no longer be sold at a regular price. In Kinetic you have the following reports that can help you: Excess Stock Provision Report, Slow Moving Stock Provision Report, and Obsolete Stock Report. However, before you run any of them, you need to define its settings. Do it in Stock Provision Report Format Maintenance.
Can anyone comment on this alternative approach? It seems like it will work. This would even allow us to arbitrarily move parts between warehouses if the need arises.
If you are truly going to have 2 different warehouses and store them differently then it might work.
If you are physically keeping the parts together, but virtually in separate warehouses it will end up causing problems. Im always against lying to the system.
Cycle counting is by Warehouse, so would would end up increasing your inventory when someone physically counts the actual number of parts in that bin.
You’re right! this could be tricky. Thankfully we store our parts in bins with lot labels along with other identifying information that should help us keep things separate.
Thanks for your feedback guys!