Inventory Valuation Change

(0) Share

Report

Posted on by Community Member

I have a client who recently went live. They decided during setup that they wished to use FIFO. Recently changed Accounting firms and the new accountant is insisting that we change to average perpetual. He has told us that whatever the cost they must change. They have 500 to 600 open orders and multiple PO in various stages of the process.

I know the rules for changing inventory valuation. What is the easiest way to get around those rules? I am assuming some sort of SQL hack adjusting the tables but I am not sure how to best accomplish this. Any invoices going forward should cost at the average perpetual cost but existing and posted invoices can be left as it.

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (0)

Frank Hamelly | MVP... 46,623 Super User 2025 Season 2 on at

Like (0)

Report

First of all, as a recovering accountant - I would question why the new accountant is so insistent on changing the valuation method. Is this a publicly traded company? if not, then they should be able to use whatever valuation method for inventory they choose, so long as they're consistent in the method year-to-year. Even if they are a public company, why is the accountant insisting on the change? If your client hasn't questioned the strategy thoroughly, they need to so they understand why they need to make the change before going through the pain of doing so.

You cannot 'hack' the tables to make this change. There are complex calculation algorithms used to derive the correct inventory valuation under both valuation methods.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Frank thanks for your answer. There is really no reason why they want to change except that they want to. They have said it has to be done, if you won't do it we will find another var who will.

I guess hack was too strong a term. I plan to do the following:

1. Post all sales and receiving batches.

2. I will zero out the inventory quantities currently in the system using integration manager.

3. Here is where it becomes a bit tricky. I am going to try taking a copy of the SOP10200 table and then deleting the transactions. I will then run reconcile in inventory. I am hoping this will take the backordered and allocated quantities to zero.

4. If the quantities are now zero I will change the valuation for all items.

5. I will reenter the inventory using integration manager.

6. I will copied back in the SOP10200 table and run reconcile. Hopefully all will be fine as I know the actual cost is not assigned to the item until it is posted. If it does not work during testing I will need to delete the line items in the SOP10200 table then re-enter them using Integration manager.

So basically my only SQL hack if you will is to move the SOP10200 out and in.

Was this reply helpful? Yes No
Frank Hamelly | MVP... 46,623 Super User 2025 Season 2 on at

Like (0)

Report

Okay Tom, sounds like you know what you're doing. Good luck and let us know how it works out!

Thx,

Was this reply helpful? Yes No
In-Consulting 150 on at

Like (0)

Report

I would worry about the current costs, as normally cost of sales is posted upon receipt of the items. You need to rather, (and I have done this before as a 'hack') use an inventory adjustment transaction (which you can also create in IM) to reverse out all the quantities, and then bring them back into stock afterwards with a positive transaction. that is the only way you are going to keep the costs in line with the evaluation method. I feel for you on this one :) what a mess. Let me know if you come unstuck.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Could you create new items? Process or delete whatever orders are there for the old items. Then zeroise their stock. Then import an increase transaction for the new items? Then re-enter whatever orders need to be entered.

None of this requires you to make any table changes through SQL. It is not the shortest method, and there could be a lot of re-setting up of items maybe, but it uses standard GP functionality, so you can be sure the underlying business logic is 100% correct.

It is also auditable...you can be sure the auditors will question this one and want backup reports to support what was done. You can also be sure that your customer will want you to proove the figures next year when the auditors have a problem - and they will throw it all back on you.

As to the threat....if you can afford to walk away, think about it. Doesn't seem like a very healthy relationship if they are holding a gun to your head ' sort it out, or we will get someone else who will'

Ian.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

OK tell me if this makes sense. It has so far worked well in testing.

I am making ATYAlLLOC equal to zero in the IV00102.

I am making all line items Non Inventory in SOP10200.

I then Zero out the inventory qtys.

When that is done I can then change the valuation.

Iam then reverse the process adding the QTYS back, upding the ATYALLOC back to the orginal qty and finally changing the line item in SOP10200 back to inventory.

Was this reply helpful? Yes No
In-Consulting 150 on at

Like (0)

Report

As long as you are using inventory transactions to zero out the stock, and bring it back, there are no receipts hanging about open and unposted, there are no other inventory transactions about, then I would say you are safe to proceed. Obviously backup, and take all other necessary precautions, as well as trying to post the SOP orders afterwards, create a new PO, receipt, post an new Sales order using the new stock to test the valuation method has changed. Run a test inventory stock status report as now the values will have changed and check this as well.

I am presuming you went through all this in test. You know what they say about presumption.

Also do not be caught out by the current cost, std cost run around, as it could even be that the majority of the stock you still have was receipted at neither of those. Hence the stock status report suggestion.

I do not know what their stock movement is like, but you could have a scenario where 500 were bought then someone else bought 20, then someone else bought another 50 and so far only 200 have been sold but the current cost is from the 50 and the std cost is whatever they set it to or it could even be zero. So you could end up writing the stock off and bringing it back in at an inflated cost, the auditors are going to love that.

Just some more things to consider :)

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thank you for considering my posting. I did consider the cost so I wrote a crystal report to break it out based on the fifo layers and calculate the average cost of each item going forward.

Was this reply helpful? Yes No
L Vail 65,271 on at

Like (0)

Report

Tom,

I'll pipe in here with a random thought. Beware of the increase in posting time that may result from using average perpetual, since the value of the inventory is continually being recalculated. If they often take IV negative and then bring it back up again or the receipts and the sales are not posted in order, they may see some significant increases in posting time.

Good Luck Tom, I have done the same thing only I was changing from average perpetual to LIFO. Make sure you do not have the item on an outstanding PO. If you do, export the data from the PO table, zero out the qtys, change the valuation method and then import the data back into the PO table.

Kind regards,

Leslie

Was this reply helpful? Yes No