Analyzing Bid Results controlling for number of vendors

[bmack11235813]

I am conducting a bid. I have 5 items I want to purchase at varying quantities and am receiving bids from 4 different vendors. I know how to calculate total min spend and show which vendor to utilize for each item.

Say I want to only set up purchase agreements with only 2 vendors. How can I analyze the data to determine which two vendors I should utilize? Is there a way to make this scalable - say I had 100 items and 20 vendors? Can I set a 'maximum' number of successful vendors and minimize total spend?

I was attempting to use solver but I can't think of the design that makes it work.

-Bryce

[TABLE="class: grid, width: 580, align: left"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Quantity[/TD]
[TD]Vendor 1[/TD]
[TD]Vendor 2[/TD]
[TD]Vendor 3[/TD]
[TD]Vendor 4[/TD]
[TD]Min Bid[/TD]
[TD]Min Vendor[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]$10[/TD]
[TD="align: right"]$2[/TD]
[TD="align: right"]$76[/TD]
[TD="align: right"]$3[/TD]
[TD="align: right"]$2[/TD]
[TD]Vendor 2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]$45[/TD]
[TD="align: right"]$24[/TD]
[TD="align: right"]$6[/TD]
[TD="align: right"]$10[/TD]
[TD="align: right"]$6[/TD]
[TD]Vendor 3[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]$78[/TD]
[TD="align: right"]$8[/TD]
[TD="align: right"]$40[/TD]
[TD="align: right"]$27[/TD]
[TD="align: right"]$8[/TD]
[TD]Vendor 2[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]$45[/TD]
[TD="align: right"]$96[/TD]
[TD="align: right"]$38[/TD]
[TD="align: right"]$4[/TD]
[TD="align: right"]$4[/TD]
[TD]Vendor 4[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]$40[/TD]
[TD="align: right"]$56[/TD]
[TD="align: right"]$88[/TD]
[TD="align: right"]$79[/TD]
[TD="align: right"]$40[/TD]
[TD]Vendor 1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Total Spend[/TD]
[TD][/TD]
[TD] $ 784[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]