ALL DONE IN EXCEL USING SOLVER _ PLEASE SHOW FORMULAS! ILL LIKE (:

The following table shows the county name, its population, the cost to locate an MTS (mobile testing site) in that county, and then a measure of how severe or how unknown the severity of the virus is in that county.

Your task as a modeler find the optimal location of which counties to locate MTSs such that the following requirements are met.

1. Each county is covered traditional definition of a set covering problem. A county is covered when either an MTS is located int hat county or in an adjacent county.
2. Each county has a maximum number of coverage i.e. overlap. Initially, allow up to 3 different MTSs to cover a particular county.
3. At least 50% of the MTSs must be placed in counties with populations less than 10,000.
4. The total cost of the MTSs cannot exceed 125 units.
5. The weighted average of the COVID severity county measures where MTSs are located must be >= 2.7.

Your objective measure (objective function) in determining which counties should house an MTS subject to these requirements is a function of its population plus a proportion (25%) of the population of the counties that are contiguous. For example, look at Grant county.

Grant = 5144, it is next to Garfield (57813) and Alfalfa (6105). So Grants WPOM (weighted population measure) would be 5144 + (0.25)(57813) + (0.25)(6105) = 21123.5.

The location of the MTSs should maximize the WPOM parameter.

Compare this solution to Scenario B.

Scenario B: Using WPOM, alter constraint 2) above to limit the maximum coverage of any county to at most 2.

Compare the original solution to Scenario A and B. Nothing elaborate. They should be similar but a little different.

12 10 11 Alfalfa County Beckham County Blaine County Caddo County Canadian County Custer County Dewey County Ellis County Garfield County Grant County Greer County Harmon County Harper County Jackson County Kingfisher County Kiowa County Major County Roger Mills County Washita County Woods County Woodward County POPCOST COVID 6105 19799 18 11976 21 30150 30.1 87697 42.1 26142 14 4743 8 4075 57813 23.4 5144 6061 14.1 3283 11.6 3562 28439 13.9 13926 - 24 10227 18.7 7545 16.3 3436 9.2 11508 12.3 9089 14.8 18486 26.8 NNNWNNW NWNW NWU AAN 8.5 12 10 11 Alfalfa County Beckham County Blaine County Caddo County Canadian County Custer County Dewey County Ellis County Garfield County Grant County Greer County Harmon County Harper County Jackson County Kingfisher County Kiowa County Major County Roger Mills County Washita County Woods County Woodward County POPCOST COVID 6105 19799 18 11976 21 30150 30.1 87697 42.1 26142 14 4743 8 4075 57813 23.4 5144 6061 14.1 3283 11.6 3562 28439 13.9 13926 - 24 10227 18.7 7545 16.3 3436 9.2 11508 12.3 9089 14.8 18486 26.8 NNNWNNW NWNW NWU AAN 8.5