A pharmaceutical company is hiring five new salespeople to expand its sales in a western state. Pharmaceutical sales representatives do not sell directly to doctors because doctors do not purchase and distribute drugs. However, doctors do write prescriptions, and it is that activity the sales representatives try to influence. The pharmaceutical company is focusing its efforts on the 10 counties in the state and estimated the number of doctors in each county as follows:

Additionally, 10 possible sales regions (comprising contiguous sets of counties) have been identified as follows:

For example, if a sales representative is assigned to region 1, he or she would be responsible for counties 1, 3, and 5. Each sales representative may be assigned to only a single sales region, so not all of the possible sales regions will be used. The company would like to assign its five sales representatives to these possible regions in such a way as to ensure that at least one sale representative covers each county. If regions are assigned in such a way that more than one sales representative covers the same county, the doctors within that county would be split equally among the relevant sales representatives. Additionally, the company would like to assign regions so that the total number of doctors assigned to each sales representative is as equal as possible. (Note that if exactly the same number of doctors is assigned to each sales representative, the variance of the number of doctors assigned to each sales representative would be zero.)
a. Create a spreadsheet model for this problem, and use Solver's evolutionary engine to solve it.
b. What is the optimal solution?
c. What other criteria can you think of that might be relevant to the decision makers or sales representatives in this problem?

Transcribed Image Text:

County Doctors 1 2 345 6 78910 113 Possible Sales Region 4 County 23 45 6 78 10 10