I have the following dilemma: we recieve multiple vendor quotes on parts lists with each vendor listing their own price and freight terms. Freight is almost always a simple percentage of the total order. The spread can basicaly end up that the vendor with the lowest price may also have the highest freight terms and therefore, when added together, ultimately have the highest price overall. I have this sort of worked out with a simple array formula but since accounting enters material costs and freight costs separately, I need a way for formula to sort of reverse engineer itself and tell me where it got it's results from (with cell references).
Sorry for the overly complex explanation, here's an example:
The results of this are that Vendor 2 has the best pricing at $962.00 overall even though their base material cost is the second highest and so are their freight terms. I've got this worked out with {=MIN(Vendor_Pricing*(1+Freight_Terms))}.
So what'd I'd like to know is a method for breaking this down into the Vendor Pricing and Freight Terms cell references (or which Vendor reference was the cheapest overall) as these costs have to be accounted for separately and taxed differently. Thanks very much for reading!
Bookmarks