PLEASE SHOW FORMULAS IN EXCEL AND SOLVER!!
67. Allied Freight supplies goods to three customers. The company has two warehouses. The warehouse availabilities, the customer requirements, and the unit shipping costs from warehouses to customers are shown in the file PO5_67.xlsx. There is a penalty for each unsatisfied customer unit of demand, and these penalties are also listed in the same file. a. Determine how to minimize the sum of penalty and shipping costs. b. Use SolverTable to see how a change in the unit penalty cost of customer 1 affects the optimal cost. c. Use SolverTable to see how a change in the capacity of warehouse 1 affects the optimal cost. H . Availabi 40 20 4A B D E F G 4 5 1. Problem # 67 6 a. Unit shipping and penalty costs 7 TO --> Customer 1 Customer 2 Customer 3 8 From Warehouse 1 35 15 25 9 Warehouse 2 30 10 40 10 Penalty cost 125 100 90 11 12 Shipments 13 Customer 1 Customer 2 Customer 3 otal shipped 14 From Warehouse 1 15 Warehouse 2 16 Shortage 17 Total received 18 19 Required 150 250 300 20 21 Total demand 22 Total capacity 23 24 Total shipping cost 25 Total shortage cost 26 Total cost 27 Answer key: 22000 28 J B D E F H . 25 26 27 28 29 b. Discuss the result of SolverTable below. Use Justify and make sure the text is within the box below. (10 pints) 30 31 32 33 34 35 36 37 38 c. Discuss the result of SolverTable below. Use Justify and make sure the text is within the box below. (10 pints) 39 40 41 42 44 45 46 47 67. Allied Freight supplies goods to three customers. The company has two warehouses. The warehouse availabilities, the customer requirements, and the unit shipping costs from warehouses to customers are shown in the file PO5_67.xlsx. There is a penalty for each unsatisfied customer unit of demand, and these penalties are also listed in the same file. a. Determine how to minimize the sum of penalty and shipping costs. b. Use SolverTable to see how a change in the unit penalty cost of customer 1 affects the optimal cost. c. Use SolverTable to see how a change in the capacity of warehouse 1 affects the optimal cost. H . Availabi 40 20 4A B D E F G 4 5 1. Problem # 67 6 a. Unit shipping and penalty costs 7 TO --> Customer 1 Customer 2 Customer 3 8 From Warehouse 1 35 15 25 9 Warehouse 2 30 10 40 10 Penalty cost 125 100 90 11 12 Shipments 13 Customer 1 Customer 2 Customer 3 otal shipped 14 From Warehouse 1 15 Warehouse 2 16 Shortage 17 Total received 18 19 Required 150 250 300 20 21 Total demand 22 Total capacity 23 24 Total shipping cost 25 Total shortage cost 26 Total cost 27 Answer key: 22000 28 J B D E F H . 25 26 27 28 29 b. Discuss the result of SolverTable below. Use Justify and make sure the text is within the box below. (10 pints) 30 31 32 33 34 35 36 37 38 c. Discuss the result of SolverTable below. Use Justify and make sure the text is within the box below. (10 pints) 39 40 41 42 44 45 46 47
