1st Picture- The problem itself.

2nd Picture- My almost completed attempt (which I am almost positive is right) I just included it to save the tutor/expert's time.

3rd Picture- A close up of the 2nd part of the problem.

4th Picture- The completed problem, but with,"Show Formulas" turned on

5th Picture-**----P05_67.xlsx----**

**----P05_67.xlsx----**

1st Picture- The problem itself.

2nd Picture- My almost completed attempt (which I am almost positive is right) I just included it to save the tutor/expert's time.

3rd Picture- A close up of the 2nd part of the problem.

4th Picture- The completed problem, but with,"Show Formulas" turned on

5th Picture-**----P05_67.xlsx----**

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 P05_67.xlsx. There is a penalty for each unsatisfied customer unit of demand, and these penalties are also listed in the same file. Determine how to minimize the sum of penalty and shipping costs. Use SolverTable to see how a change in the unit penalty cost of customer 1 affects the optimal cost. . Use SolverTable to see how a change in the capacity of warehouse 1 affects the optimal cost. C D E F G H I Customer 3 5 1. Problem #67 (40 points) 6a. Unit shipping and penalty costs (20 points) TO -> Customer 1 8 From Warehouse 1 Warehouse 2 30 Penalty cost 125 Customer 2 15 10 100 Shipments Available 400 From To Customer 1 Customer 2 Warehouse 1 Warehouse 2 150 Shortage Total received 250 Customer 3 Total shipped 200 400 0 200 G 100 200 150 200 Required 150 250 300 Total demand Total capacity 600 Total shipping cost Total shortage cost Total cost $13,000.00 $9,000.00 $22,000.00 29 b. Discuss the result of SolverTable below. Use Justify and make sure the text is within the box below. (10 pints) 38 . Discuss the result of SolverTable below. Use justify and make sure the text is within the box below. 110 pints) Total demana Total capacity 600 Total shipping cost Total shortage cost Total cost $13,000.00 $9,000.00 $22,000.00 Discuss the result of SolverTable below. Use Justify and make sure the text is within the box below. (10 pints) Discuss the result of solverTable below. Use Justif and make sure the text is within the box below. (10 pints) Customer 1 Customer 2 Customer 3 Unit shipping and penalty costs (20 points) TO --> From Warehouse 1 Warehouse 2 Penalty cost 10 100 Shipments Customer 1 Customer 3 Customer 2 200 From 200 Total shipped SUM(D14:F14) SUM(D15:F15) Warehouse 1 Warehouse 2 Shortage Total received 150 =D19-D17 =SUM(D14:015) =E19-E17 =F19-F17 SUM(E14:E15) =SUM(F14:F15) Required 150 250 300 Total demand Total capacity SUM(Required) SUM(Available) Total shipping cost =SUMPRODUCT(08:F9,Shipper Total shortage cost =SUMPRODUCT(D10:F10, D16:F Total cost SUM(C24:C25) 1 Allied Freight data Unit shipping costs Customer 2 6 From Warehouse 1 Warehouse 2 Customer 1 $35 $30 $15 Customer 3 $25 $40 Available units Warehouse 1 Warehouse 2 400 200 $ 10 9 Requirements Customer 1 150 Customer 2 250 Customer 3 300 13 Penalty cost per unit of requirement not satisfied Customer 1 Customer 2 Customer 3 -------$125 --- $100_-_-_$90.- 5 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 P05_67.xlsx. There is a penalty for each unsatisfied customer unit of demand, and these penalties are also listed in the same file. Determine how to minimize the sum of penalty and shipping costs. Use SolverTable to see how a change in the unit penalty cost of customer 1 affects the optimal cost. . Use SolverTable to see how a change in the capacity of warehouse 1 affects the optimal cost. C D E F G H I Customer 3 5 1. Problem #67 (40 points) 6a. Unit shipping and penalty costs (20 points) TO -> Customer 1 8 From Warehouse 1 Warehouse 2 30 Penalty cost 125 Customer 2 15 10 100 Shipments Available 400 From To Customer 1 Customer 2 Warehouse 1 Warehouse 2 150 Shortage Total received 250 Customer 3 Total shipped 200 400 0 200 G 100 200 150 200 Required 150 250 300 Total demand Total capacity 600 Total shipping cost Total shortage cost Total cost $13,000.00 $9,000.00 $22,000.00 29 b. Discuss the result of SolverTable below. Use Justify and make sure the text is within the box below. (10 pints) 38 . Discuss the result of SolverTable below. Use justify and make sure the text is within the box below. 110 pints) Total demana Total capacity 600 Total shipping cost Total shortage cost Total cost $13,000.00 $9,000.00 $22,000.00 Discuss the result of SolverTable below. Use Justify and make sure the text is within the box below. (10 pints) Discuss the result of solverTable below. Use Justif and make sure the text is within the box below. (10 pints) Customer 1 Customer 2 Customer 3 Unit shipping and penalty costs (20 points) TO --> From Warehouse 1 Warehouse 2 Penalty cost 10 100 Shipments Customer 1 Customer 3 Customer 2 200 From 200 Total shipped SUM(D14:F14) SUM(D15:F15) Warehouse 1 Warehouse 2 Shortage Total received 150 =D19-D17 =SUM(D14:015) =E19-E17 =F19-F17 SUM(E14:E15) =SUM(F14:F15) Required 150 250 300 Total demand Total capacity SUM(Required) SUM(Available) Total shipping cost =SUMPRODUCT(08:F9,Shipper Total shortage cost =SUMPRODUCT(D10:F10, D16:F Total cost SUM(C24:C25) 1 Allied Freight data Unit shipping costs Customer 2 6 From Warehouse 1 Warehouse 2 Customer 1 $35 $30 $15 Customer 3 $25 $40 Available units Warehouse 1 Warehouse 2 400 200 $ 10 9 Requirements Customer 1 150 Customer 2 250 Customer 3 300 13 Penalty cost per unit of requirement not satisfied Customer 1 Customer 2 Customer 3 -------$125 --- $100_-_-_$90.- 5