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.
PCT – percentage of population already vaccinated.
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:
Cannot exceed Need for a region.
Cannot exceed MAX for a vaccine supply location, though you can ship less than MAX.
Must send at least 20% of Need to each region.
The overall distribution plan must have an average POL measure of at least 65 per unit shipped. This is an overall plan measure to at least meet a political threshold.
The overall distribution plan must have an average PCT measure of no more than 9.5 per unit shipped. This is an overall plan measure to make sure the supplies go to regions that have a lower existing vaccination rate.
Each region must have vaccines shipped to them that average at least 3.5 in VQUAL. Again, this is each region individually.
No more than 1500 units can be sent from any one supply location to any one geographic region.
At the very end, force solutions to have integer values.
Using Excel’s LP Solver, find the LEAST COST way to ship the Vaccines from the 4 Locations to the 7 geographic regions.