We have some down-costing to do on old stock and trying to find the best method.

We would like to get it done in advance of year end since the physical count and subsequent data entry is an overwhelming amount of work.

I thought I could kill 2 birds with one stone by creating a clearance list in Excel that was produced with Adagio Inventory's F6 finder, however, "Average Unit Cost" is not available to add to the grid.

How then can we produce a report that shows the average unit cost and quanties on hand so that we can down-cost?

Also, to do the data entry on the down-costing we run into the nightmarish problem of the multiple lots. Can these lots be merged to one?

Please let me know if I'm on the right track or if there is a better method for down-costing.

Hi Susan,

Indeed the Average Unit Cost is not stored on the Item Master file. It is calculated from the Total Cost of the item divided by the Total Quantity on Hand (across all locations). Cost is not stored separately for each location.

You would have to create a custom report (such as with GridView) to sum the costs of all 5 costing lots, and divide by the sum of the quantities of the 5 lots, if you use FIFO or LIFO costing.

If you use another costing method, it is a simpler calculation by simply dividing the cost of lot #1 by the quantity of lot #1.

If you have outstanding invoices in OE that have not been processed by Day End, you may also need to incorporate the 'Quantity Shipped not Costed' value in the above calculations, which will have a non-zero value until Day End is performed.

We use FIFO. That is how we were originally set up many years ago with AccPac. Perhaps "Moving Average" would be a more suitable costing method that might help us improve our inventory control? Can the costing method be changed on an active inventory?

They can be changed on active inventory, but currently only one item at a time. There currently is no 'mass change costing method' in Adagio Inventory.

Should you do this, FIFO costing lots would get merged together into one. I shouldn't advise (or reject) doing this change, as it should reflect how you do business. I likely wouldn't change solely for making reports on average cost easier to do - there should be a business case for making changes.

Hello Susan:

It sounds as if you are building an Adjustment batch under LIFO/FIFO.

One of the columns in the Adjustment batch is Lot #. However, unless you have a lot of patience to edit the "batch" after importing, the practical solution is to use Lot #1 and fix the problems later.

Fortunately there is an easy way to fix the problems after posting (where Lot 1 is now negative and Lot 2 or 3 is too high).
-Create an Account Set with "Moving Average" as the costing method.
-Locate the first problem item
-Change the Account Set from the current to the new one you just created.
-Save the item
-Re-open the item - you will notice that all items have been rolled up into Lot 1.
-Change the Account Set back to the original (the FIFO/LIFO) one.

Use the same technique for the other items. This must be done item-by-item; the changes cannot be imported successfully. Steve Schwartz originally suggested this technique so you should be able to find his postings on the topic elsewhere in the Forum.

Thanks Bruce for showing me a way to fix the lot issues. After giving this all some thought, I am considering changing our costing method from FIFO to Moving Average. The reasons are more than just for ease of the report mentioned above. The main reason is for better inventory control. The lots are continually creating headaches for us whenever we do inventory adjustments. I don't know how FIFO is benefiting us in our business. We base all of our selling prices on average unit cost. If OE sent the average unit cost to cost of goods sold when an item is sold instead of the cost from the oldest lot, it would reflect our financial reports more accurately.

Is there anything I'm overlooking? Any reason why FIFO is better?

So if I do decide to proceed with changing our costing method, couldn't I just create a new control account with Moving Average as the costing method, then export all our inventory, rename the control account on the spreadsheet to reflect the new control account and import it back into Adagio?

Well, I decided to try the import, and it did not work. It says, the control account cannot be imported. We have 5,000 items on our inventory. Changing the control account one at a time will take probably 3 employees a day or more to enter? This could have been accomplished in 2 minutes with an import. Why exactly is this feature blocked in Adagio Inventory???

Hi Susan:

You can't import the Control Account because a change in Costing method requires changes to the Lots and Costs (they have to be rolled up into Lot 1). The import process isn't designed to do that. It only loads numbers and strings into fields.

However there is a solution (again I have to give credit to Steve Schwartz). Do you have ACCPAC Plus Inventory Control 6.5? If you do, you can edit the Control Account(s) and change it from FIFO to Average Costing. Once you save that change, it ripples through the inventory list and rolls everything into Lot 1.

Currently you cannot do the same thing in Adagio.

I would suggest 2 things:
1) Run Day End, Make a backup; Compare Valuation Report before & after; Run Data Integrity Check before & after
2) Talk with your accountant before changing the costing method.

At one point changing inventory valuation methods required IRS approval, so changing inventory valuation methods should not be taken lightly. I think changing from LIFO to other methods required and may still require approval, because income recognition is changed.

Maybe that's part of the reason.....

David

I called our accountant and he had no issue with it. I changed a few items to "Moving Average" and really like it so much better. Wish we had done this years ago. I can't believe that we can't simply change the costing method on the Account set. That would have been so easy! I could live with having to import a new account set. But one by one is really bad! Not only will it take days to enter but then.. when you create a new item, it defaults to the account set in the #1 position. I guess I will have to delete it and after I've changed 5000 codes to #3 position then move the #2's to a #4 so that I make sure anyone adding a new code gets the default "Moving Average" control account.