Supply Chain Network Design with FLP Spreadsheet Solver

In Ohio, USA company has customers in 12 cities to deliver product. These cities and demands are given in the table below.

Table 1: Cities and Demands

City Name	Demand (in tones)
Columbus, OH	75
Dayton, OH	70
Cleveland, OH	65
Cincinnati, OH	60
Warren, OH	45
Akron, OH	40
Youngstown, OH	35
Toledo, OH	30
Lima, OH	25
Canton, OH	20
Sandusky, OH	20
Mansfield, OH	15

Choose 12 cities from your province or state and using FLP Spreadsheet Solver solve different scenarios given below. Capture solution screens and paste to your report; explain the results from total cost, maximum service distance, average distance to customers and other important perspectives.

1- Without capacity limitation and no facility setup costlocate 2 and 3 facility (P=2, P=3) and create outputs and explain in your report under the related scenario. In this scenario Columbus, OH must be facility.

2- Without capacity limitation but with $1500 facility setup cost locate 2 and 3 facilities respectively (P=2, P=3) and create and explain outputs in your report under the related scenario.

3- With 200 tones fixed capacity and $1500 facility setup cost for each plant locate 2 and 3 facilities respectively (P=2, P=3) and write outputs and explain this output in your report under the related scenario.

4- Add different capacities (given in Table 2) to your model and $1500 facility setup cost for every location than locate 2 and 3 facilities (P=2, P=3) and create and explain outputs in your report under the related scenario.

Table 2: Potential Facility Capacities

City Name	Capacity (in tones)
Columbus	200
Dayton	250
Cleveland	200
Cincinnati	250
Warren	150
Akron	200
Youngstown	300
Toledo	250
Lima	150
Canton	200
Sandusky	200
Mansfield	150

5- Company logistics team created a short list for potential facility locations instead of using all 12 cities as a candidate location. This short list is Columbus, Cleveland, Toledo, Cincinnati, and Dayton. Capacities for these potential facilities are also same with capacities given on Table 2. Facility setup cost same for all these cities. Using this short list locate 2, 3 and 4 facilityrespectively (P=2, P=3, P=4) and create outputs and explain this output in your report under the related scenario.

6- Company logistics team decided not to open facility in Cleveland and Columbus because of the high facility cost, all other 10 cities are potential facility. Without capacity limitation and same setup cost for all locations, locate 3 and 4 facility (P=3, P=4) and create and explain results in your report under the related scenario.