Tools of excel
AutoSave . OFF 3 Tools_Hwk4 Home Insert Draw Page Layout Formulas Data Review View Developer Tell me Share Comments Arial 10 A* General Insert v x Delete v Paste B IUV A $ ~ % " Conditional Format Cell Sort & Find & Analyze Sensitivity Formatting as Table Styles Format v Filter Select Data 22 X V fx B C D E G H M N O P 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 C to C1: $1.68 A to C2: $1.28 B to C2: $1.35 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 Sheet1AutoSave . OFF 3 Tools_Hwk4 Home Insert Draw Page Layout Formulas Data Review View Developer Tell me Share Comments Insert v Arial 10 A General 2x Delete v Past B IUV LAVA $ ~ % " Conditional Format Cell Sort & Find & Analyze Sensitivity Formatting as Table Styles Format v Filter Select Data 22 X V fx A B C D E F G H M N O P 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. Stage1: 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 Stage1 TO C2 Capacity 1.36 $ 1.28 2400 FROM OW D 1.28 leaf 1.35 2750 1.68 $ 1.55 2500 Stage2 FROM C1 C2 Demand W1 $ 0.60 $ 0.80 1250 W2 $ 0.36 0.56 1000 TO W3 $ 0.32 $ 0.42 1600 W4 0.44 $ 0.40 1750 W5 0.72 $ 0.55 1500 You can start writing your answer below this row. Sheet1 +
