Please use Excel Solver to find the numerical answers. Consider the following ne
ID: 443170 • Letter: P
Question
Please use Excel Solver to find the numerical answers.
Consider the following network with plants/factories (Pi), warehouses (W/Hi) and customers (Ci).
The total cost ($) associated with transporting one unit along the arcs is given in the technology table below as well as the maximum production for each facility and the customer demand.
P1
P2
P3
C1
C2
C3
C4
W/H 1
2
4
3
4
6
4
3
W/H 2
2
5
4
2
2
3
3
Max production for P’s, Demand for C’s
250
350
375
200
325
250
150
The costs and capacities (numbers in parentheses) of the direct shipping lanes is given below:
P1
P2
P3
C1
5 (100)
C2
5 (150)
C3
4 (100)
C4
5 (50)
There is a material handling cost for passing units through the warehouses of $0.50/unit through W/H 1 and $0.60/unit through W/H 2. Also, the maximum flow both W/H’s is 450.
a) Formulate the model that can be solved to find the network configuration provides the minimum cost for meeting customer demand. Be sure to define all variables and briefly describe what the objective function and constraints accomplish.
b) Solve the model in part a) to find the numerical solution.
c) An option that has been presented to management is to increase the size of each warehouse by leasing nearby space. The costs associated with the extra warehouse space would be identical (cost to transport to and from as well as material handling costs). In particular, the capacity of either warehouse can be increased in increments of 200 units for a fixed cost of $100 per increment. For example, the capacity of W/H 1 can be increased from 450 to 550 for $100 – or to 650 for $200. What network configuration provides the minimum cost for meeting customer demand?
d) Referring to your model in parts a) and b), suppose you could increase the capacity at any one plants by 50 units to decrease the total cost of this system. Which plant would you select, if any, to decrease the total cost by the maximum? What is the maximum you could pay for the added capacity and still reduce the total cost?
P1
P2
P3
C1
C2
C3
C4
W/H 1
2
4
3
4
6
4
3
W/H 2
2
5
4
2
2
3
3
Max production for P’s, Demand for C’s
250
350
375
200
325
250
150
C1 P 1 C2 W/H 1 P2 W/H 2 C3 P 3 C4Explanation / Answer
Please use Excel Solver to find the numerical answers. Consider the following ne