The Spencer Shoe Company manufactures a line of inexpensive shoes in one plant in Pontiac and distributes to five main distribution centers (Milwaukee, Dayton, Cincinnati, Buffalo, and Atlanta) from which the shoes are shipped to retail shoe stores. Distribution costs include freight, handling, and warehousing costs. To meet increased demand, the company has decided to build at least one new plant with a capacity of 40,000 pairs per week. Surveys have narrowed the choice to three locations, Cincinnati, Dayton, and Atlanta. As expected, production costs would be low in the Atlanta plant, but distribution costs are relatively high compared to the other two locations. Other data are as follows. Assume that Spencer Shoe Company will keep operating at Pontiac and build a plant at one of the three new alternatives. Which alternative will lead to the lowest total cost, including production, distribution, and fixed costs, and what is the minimum weekly cost? Distribution costs per pair from To Pontiac Cincinnati Dayton Atlanta Demand (pairs/wk) Milwaukee $0.42 $0.46 $0.44 $0.48 10,000 Dayton 0.36 0.37 0.3 0.45 15,000 Cincinnati 0.41 0.3 0.37 0.43 16,000 Buffalo 0.39 0.42 0.38 0.46 19,000 Atlanta 0.5 0.43 0.45 0.27 12,000 Capacity (pairs/wk) 32,000 40,000 40,000 40,000 Production Cost/pair $2.70 $2.64 $2.69 $2.62 Fixed Cost/wk $7,000 $4,000 $6,000 $7,000 Hint: Solve this problem as a Fixed charge problem. What is the value of the objective function for the Plant location problem? Do not include, comma, dollar sign or decimal points Hint: 1) This is a fixed charge problem. yi =1 if plant i is operational, 0 otherwise Total supplies from plant 1 <= capacity of the plant * y1 Please demonstrate the way to set this up in excel please. Thanks!
The Spencer Shoe Company manufactures a line of inexpensive shoes in one plant in Pontiac and distributes to five main distribution centers (Milwaukee, Dayton, Cincinnati, Buffalo, and Atlanta) from which the shoes are shipped to retail shoe stores. Distribution costs include freight, handling, and warehousing costs. To meet increased demand, the company has decided to build at least one new plant with a capacity of 40,000 pairs per week. Surveys have narrowed the choice to three locations, Cincinnati, Dayton, and Atlanta. As expected, production costs would be low in the Atlanta plant, but distribution costs are relatively high compared to the other two locations. Other data are as follows. Assume that Spencer Shoe Company will keep operating at Pontiac and build a plant at one of the three new alternatives. Which alternative will lead to the lowest total cost, including production, distribution, and fixed costs, and what is the minimum weekly cost?
|
Distribution costs per pair from To |
Pontiac |
Cincinnati |
Dayton |
Atlanta |
Demand (pairs/wk) |
|
Milwaukee |
$0.42 |
$0.46 |
$0.44 |
$0.48 |
10,000 |
|
Dayton |
0.36 |
0.37 |
0.3 |
0.45 |
15,000 |
|
Cincinnati |
0.41 |
0.3 |
0.37 |
0.43 |
16,000 |
|
Buffalo |
0.39 |
0.42 |
0.38 |
0.46 |
19,000 |
|
Atlanta |
0.5 |
0.43 |
0.45 |
0.27 |
12,000 |
|
Capacity (pairs/wk) |
32,000 |
40,000 |
40,000 |
40,000 |
|
|
Production Cost/pair |
$2.70 |
$2.64 |
$2.69 |
$2.62 |
|
|
Fixed Cost/wk |
$7,000 |
$4,000 |
$6,000 |
$7,000 |
Hint: Solve this problem as a Fixed charge problem. What is the value of the objective function for the Plant location problem? Do not include, comma, dollar sign or decimal points Hint: 1) This is a fixed charge problem. yi =1 if plant i is operational, 0 otherwise Total supplies from plant 1 <= capacity of the plant * y1
Please demonstrate the way to set this up in excel please. Thanks!
Trending now
This is a popular solution!
Step by step
Solved in 2 steps with 3 images
