Cost of Goods Sold

[DonQuixote]

Products are purchased from various vendors, and Prelude has a 'Primary Vendor' for a product.  But, when it comes to knowing which vendor's product was sold from a box there is no method to trace it.  This even get worse with asterisk part numbers because they don't even had a 'primary vendor'.
Our Product Manager estimates Vendor Sales by using purchase percentages from each vendor and just guessing.
Ideally he wants a modified LIFO method so that we consider sales to come first from non-primary vendors (LIFO) and then from the primary vendor.  This is all to determine the Cost of Goods Sold per vendor.
Any suggestions?
Would I build a stack in the PRODUCT file keeping a stack of purchase quantities per Non-primary Vendors and removing them as sales occur?  And what happens if the primary vendor changes?

[Tom Pellitieri]

Not a recommendation, but a suggestion on an investigation path: could you use Lot Numbers to track this?

Let's say you bought 5 units of a Product Number from each of three vendors.  You then have 15 units available for sale, and you sell 11 of them to your customers.  Your Product Manager wants to know how much you sold from each vendor.  If each vendor's product was assigned to a different lot number, you should be able to determine the sales for each vendor.

I don't have any direct experience with this since we use cost averaging on products and don't use lots.  We track Vendor Purchases through PO.HISTORY, and sales through ORDER.HISTORY.

[precisonline]

I think anything that could be calculated would be an estimate at best without some wholesale changes to Prelude and how it's used. First, the asterisk part numbers; that's a whole lotta problems right there for tracking vendor sales.  We also have to assume we're talking about stocked products because those that are linked to a P/O would be easy to track back to a specific vendor.

It might be possible to cycle through the purchase orders and build a stack of products that were purchased over time and then cycle through the orders and deplete the purchases according to the rules of non-preferred vendor stock first, but that could be a lot of work to produce what is tantamount to an estimate anyway.  Furthermore, if there is more than one non-preferred vendor for a product, there's even more guessing going on.

The only sure way to get this kind of information - in my opinion - is to have each vendor's part be separately defined in the application with crossing substitutes defined to the other options.  This way the quantities and sales of each vendor's part is tracked completely separate.  Of course, that also complicates purchasing because you may run out of one vendor's product but still have a thousand of the same product from another vendor and the system will want to reorder when it may not be appropriate.  So to make that work there would have to be changes to purchasing to consider groups of products together in making P/O recommendations.

A challenging problem, indeeed.