Please using the Excel Solver program
Windy City Company makes cardboards in three factories (A, B and C). Then the company ships the products to two distribution centers (C1 and C2). Then from the distribution centers to five warehouses (W1, W2, W3, W4 and W5) The distribution centers have no limits on demand and capacity However, warehouses have demand limits such as: W1 must meet a demand of 1250 W2 must meet a demand of 1000 W3 must meet a demand of 1600 W4 must meet a demand of 1750 W5 must meet a demand of 1500 The factories have capacity limits such as: A should not exceed 2400 B should not exceed 2750 C should not exceed 2500 The unit cost to ship from the factories to the distribution centers are as follow: A to C1. $1.36 B to C1: $1.28 A to C2: $1.28 B to C2: $1.35 C to C1: $1.68 C to C2: $1.55 The unit cost to ship from the distribution centers to the warehouses are as follow: C1 to W1: $0.60 C2 to W1: $0.80 C1 to W2: $0.36 C2 to W2: $0.56 C1 to W3: $0.32 C2 to W3: $0.42 C1 to W4: $0.44 C2 to W4: $0.40 C1 to W5: $0.72 C2 to W5: $0.55 Your tasks are to formulate the problem so that it can be solved by Solver. Thus, you need to: 1. Specify and formulate the Changing Cells (use yellow color to indicate the changing cells) 2. Specify and formulate the Target Cell (use a light blue color to indicate the target cell) 3. Specify and formulate the Constraints. 4. Solve this problem with the Solver and determine what is the minimum cost and how many units will be shipped from factories to distribution centers, and from distribution centers to warehouses. Hint: Think of this problem as consisting of two stages. Stage 1: from factories to distribution centers. Stage2 from distribution centers to warehouse. Calculate the cost for each stage then the total cost for all stages The total units shipped to each distribution center should be equal to the total units shipped out from each distribution center Stage 1 C1 $ $ $ A B TO C2 1.36 $ 1.28 $ 1.68 $ FROM Capacity 2400 2750 2500 1.28 1.35 1.55 Stage2 Demand C1 $ W1 W2 W3 W4 W5 FROM C2 0.60 $ 0.36 $ 0.32 $ 0.44 $ 0.72 $ $ $ $ $ TO 0.80 0.56 0.42 0.40 1250 1000 1600 1750 1500 0.55 You can start writing your answer below this row. Windy City Company makes cardboards in three factories (A, B and C). Then the company ships the products to two distribution centers (C1 and C2). Then from the distribution centers to five warehouses (W1, W2, W3, W4 and W5) The distribution centers have no limits on demand and capacity However, warehouses have demand limits such as: W1 must meet a demand of 1250 W2 must meet a demand of 1000 W3 must meet a demand of 1600 W4 must meet a demand of 1750 W5 must meet a demand of 1500 The factories have capacity limits such as: A should not exceed 2400 B should not exceed 2750 C should not exceed 2500 The unit cost to ship from the factories to the distribution centers are as follow: A to C1. $1.36 B to C1: $1.28 A to C2: $1.28 B to C2: $1.35 C to C1: $1.68 C to C2: $1.55 The unit cost to ship from the distribution centers to the warehouses are as follow: C1 to W1: $0.60 C2 to W1: $0.80 C1 to W2: $0.36 C2 to W2: $0.56 C1 to W3: $0.32 C2 to W3: $0.42 C1 to W4: $0.44 C2 to W4: $0.40 C1 to W5: $0.72 C2 to W5: $0.55 Your tasks are to formulate the problem so that it can be solved by Solver. Thus, you need to: 1. Specify and formulate the Changing Cells (use yellow color to indicate the changing cells) 2. Specify and formulate the Target Cell (use a light blue color to indicate the target cell) 3. Specify and formulate the Constraints. 4. Solve this problem with the Solver and determine what is the minimum cost and how many units will be shipped from factories to distribution centers, and from distribution centers to warehouses. Hint: Think of this problem as consisting of two stages. Stage 1: from factories to distribution centers. Stage2 from distribution centers to warehouse. Calculate the cost for each stage then the total cost for all stages The total units shipped to each distribution center should be equal to the total units shipped out from each distribution center Stage 1 C1 $ $ $ A B TO C2 1.36 $ 1.28 $ 1.68 $ FROM Capacity 2400 2750 2500 1.28 1.35 1.55 Stage2 Demand C1 $ W1 W2 W3 W4 W5 FROM C2 0.60 $ 0.36 $ 0.32 $ 0.44 $ 0.72 $ $ $ $ $ TO 0.80 0.56 0.42 0.40 1250 1000 1600 1750 1500 0.55 You can start writing your answer below this row
