Excel-Model 25% of optimal mix of vendor/product code 2

[IngDep]

Have data within an Excel worksheet whereby the product code is in the leftmost column, the vendors (14 of them) in row 1 across the 14 columns) and the cost savings for each product code at the intersect of each vendor and product code.

Desired objective is to use a solution involving solver, etc. to determine the optimal mix of vendor(s) and product code(s) that will result in 25% of the total amount of savings.

Is this possible?

Example of data is below:

Product Cd----Vendor1---Vendor2----Vendor3----Total
ABC2----------2.00-------4.50--------(1.00)---5.50
DOT3----------3.50-------2.00---------10.50---16.00
4ADC----------(4.00)-----4.65---------6.00----6.65
-------TOTAL---1.50------11.15--------15.50---28.15

28.15*25% = 7.04

 

[lionelhill]

I think you could use the solver as you suggest, simply by adding a column containing the column-number of the desired vendor, and a second column containing INDEX functions to fish out the appropriate cost saving. Then you sum the cost savings (minus 25% if that is the magic number you want), and set the solver to optimise the column numbers used by index (preferably adding constraints so it can't go outside the range 1-14).

But I haven't done this, because it isn't going to work. There's a serious logic problem in summing all the savings and then aiming for 25% of the total saving. If all your vendors give you a 10$ saving, then summing the savings for 14 vendors suggests a $140 saving. 25% of this is $35, but the maximum saving you can ever achieve is $10, because you have to choose one vendor, and they are all offering just $10. Or am I missing something?

 

[fneily]

Don't know the version you're using, but see if you have C:\Program File\Microsoft Office\Office\Samples\Solvsamp.xls
I think you can get it off of Microsoft's web site. It's a good example that looks like yours.

Your request, though is vague. If you cut all the numbers by 25%, you'll have your goal. You say your showing the cost savings. What's it base on? A formula, simple subtraction? You need to set up formulas and comparisons to set your constraints. Right now, solver has nothing to work with. Can a vendor cost save .1? Does a negative mean their losing money? So one constaint is >0.

 

[IngDep]

Thanks for the insight.

Additional context - Currently evaluating the movement of several vendors to the use of a "standard rate schedule."

The negative numbers indicate that the current charge for a particular product code was less than the amount that the vendor would have been charged if the standard rate schedule was in place. The assumption going in was that the amounts on the standard rate schedule would be less than the current amount charged. In some cases, it is not.

Initially discussed with management was the prototyping of a 25% move to the "standard rate schedule." Interpreting this can imply selecting just 1 vendor based on the total overall savings or selecting a subset of all of the vendors with those select product codes that will enable us to maximize the profits.

 

[fneily]

Do you have the Solvsamp.xls I mentioned? 'Cause it'll show you how to play around with those options you mentioned. And that's the nice thing about solver is that you can choose to first test an individual vendor, and then multiple ones with multiple products. Obviously, the more combinations you try, the better business decision you can make.