Solve using Excel Solver and set the Solving Method to Simplex LP

Here is a fictitious example of this scenario. Find the least cost way to ship surplus vaccines from 4 supply locations in the US (NY, LA, KC, ATL) to 7 geographic regions around the world (see table). These geographic regions have attributes of:

Need maximum number of vaccines needed (in 000). POL political measure of importance to serve the region with vaccines.The 4 supply locations have attributes of:MAX maximum number of vaccines that can be shipped (in 000). VQUAL quality of a vaccine shipped from that location. The 4 x 7 table shown (outlined) is the per vaccine (in 000) cost measure. Note: The (000) is just for context and integer reasons do NOT alter the numbers in your model).

Requirements for the shipping plan:

a) Cannot exceed Need for a region.

b) Cannot exceed MAX for a vaccine supply location, though you can ship less than MAX.

c) Must send at least 45% of Need to each region.

d) Must use at least 50% of MAX supply for each vaccine supply location.

e) The overall distribution plan must have an average POL measure of at least 78 per unit shipped. This is an overall plan measure to at least meet a political threshold.

f) Each region must have vaccines shipped to them that average at least 3.4 in VQUAL. Again, this is each region individually.

g) No more than 700 units can be sent from any one supply location to any one geographic region.

h) At the very end, force solutions to have integer values. If this (integers) takes too long in the solver, stop the solution, submit the non-integer solution and make a note for me.

EU Middle East Africa SE Asia Canada Mexico S America MAX VQUAL NY LA KC Atlanta Need POL 500 900 950 750 2000 80 950 1250 1150 1200 1500 75 1200 1400 1275 1325 1800 90 1500 975 1255 1600 2100 64 300 355 275 575 1200 85 750 450 475 500 1250 1100 1220 1200 2200 74 2200 3140 1150 2550 3.5 3.25 4 1400 65