Sage 100

Client is on 4.40, average cost inventory. They buy used product and might pay $1000 for 20 pallets (2000 items) of same or many different products. They want to put the inventory they really wanted (100 items of item A) in at a reasonable cost. Currently they will received in the 100 items @ $10/each and have a UDF for 'excess inventory' for the remaining 1900 items. I realize this is 'kluge' but that's what they do. When the 100 items are sold, they do some wierd transactions to put another 100 into inventory and reduce the 'excess inventory'. Not sure if they do anything with cost at that point! So my job is to figure out how to help them fix this. They don't want their inventory to show 2000 @ $10 for vaulation, they only want to show $1000 total valuation for all items received in the purchase but don't want to see a unit cost of $.50/ea. So in other words, how does anyone else account for items bought in a 'lot' for a good price without overstating/understating the inventory value and yet accounting for all inventory received?

Do they know which items they are keeping for sale when receiving the lot? Not sure i understand the UDF for excess inventory for remainging 1900 items OR the wierd transaction. I imagine it's not as simple as bring the 100 pcs at $10 and others at zero into stock.

No not that easy as it's average cost so the $1000 would get spread over the 2000 items. They may eventually or never sell the remaining items but will keep them available for sale. The UDF is for the sales order people to know they really have 1900 more available to them to sell. What I'm not sure of yet is how they adjust in from the 1900 to put more into inventory and what they do with cost at that point (the weird transaction). Will be getting more explanation tomorrow but curious as to how anyone else handles this.

but couldn't you receive the 100 at a cost and the 1900 at zero cost under a different part #.? Or a different warehouse.? More info might be helpful.

Too bad you can't convince them to go with lot or FIFO costing. While it might be a bit more processing time, you could probably make it work. Assign the $2K upfront to various lots, then assign the ""junk"" to a zero value lot. Do the physically tag/track the mdse or just do it all withing MAS. Try to get their accountant/CPA involved for their ideas.

I agree about using lots. One thing we've done in the past is to use Hightower's multibin (now SWK) because Multibine allows the automatic selection of the oldest lots based on using dates for lot numbers.

I agree that lot's would be nice. and ACE has a nice multi_bin product. But they may want to stay with average due to the type of market they are in. I think an alternate whs for the class b or not so desirable stock could work. I'vs had clients that do this, buy a bulk container or lot of stuff. What is their process of deciding what to keep? What is the likelihood of class b stuff being sold. ? Just some questions to ask. And if they sell class b stuff, do they want to show. 100 % profit.?

Thanks everyone for the responses/suggestions. Proposed the whse option (lots won't work) and he did like that, just have to convince the Company Founder/President that it's better than a UDF.

The client may not have liked the suggestion for using lot costing, but that is exactly what they are doing. They are assigning different costs to different groups of the same inventory item.

This is mostly an accounting issue and not so much technical.

This is definitely an accounting question, not a systems issue. We have had several clients in the business of buying bulk lots of inventory, breaking down the surplus purchased and then preparing some for resell and others for disposal or storage. We had one client on MAS 90 who had been a family owned business for years and played fast and loose with inventory valuation for items purchased in bulk lots. They would adjust the value of the item sold at the time of sale to minimize taxes. We always told them that this was not in compliance with GAAP and that they were opening themselves to possible problems with the IRS and lenders. However, they were small and ran the way they wanted. Since we weren't their CPA's, they nodded nicely and did what they wanted. Then one day, they went public and sold stock in their company over the counter. When asked how to better automate inventory valuation adjustments at the time of sale as their business volume had grown significantly, we had the discussion of proper valuation methods and allocating valuation of items at the time of purchase. Once again we were dismissed out of hand but this time they changed VAR's to someone who was not a CPA. Our vindication came several years later when we read in the paper that the company was under investigation by the SEC for fraudulent accounting practices relating to inventory valuation. The moral of the story is that the items should be valued at the time of purchase at the lower of cost or market. If the purchaser buys a lot of 1,000 items and believes that only 100 have a commercial value, then set up inventory that way. They would set up 100 times or 1 item with a quantity of 100 with the cost of the lot allocated across the items. The remaining times can be stored in the warehouse / yard and not put in to inventory. When sold, run them through as a miscellaneous item without a cost. The proceeds are pure profit as you essentially sold scrap material. If you really want to track the non-valued purchased items, set them up as items without a cost. Good luck.