On an Excel worksheet, transfer the algebraic formulation of the problem given below: then find the...

Transcribed Image Text:

On an Excel worksheet, transfer the algebraic formulation of the problem given below: then find the solution (values of the decision variables) using Solver. Submit only the accomplished Excel Worksheet. A D E F G H 1 Big M Company Distribution Problem 2 3 Shipping Cost 4 (per Lathe) Customer I Customer 2 Customer 3 5 Factory 1 $700 $900 $800 6 Factory 2 $800 $900 $700 7 8 9 Total Shipped 10 Units Shipped Customer I Customer 2 Customer 3 Out Output 11 Factory 1 10 2 0 12 12 12 Factory 2 0 6 15 15 13 Total to Customer 10 8 9 14 == 15 Order Size 10 Total Cost $20,500 Solver Parameters Set Objective Cell: TotalCost To: Min By Changing Variable Cells: UnitsShipped Subject to the Constraints: TotalShipped Out Output TotalToCustomer OrderSize Solver Options: Range Name F Cells 8 Total OrderSize C15:E15 Output HII:H12 9 Shipped ShippingCost C5:E6 10 TotalCost H15 11 Out =SUM(C11:E11) TotalShippedOut TotalToCustomer UnitsShipped F11:F12 C13:E13 12 =SUM(C12:E12) C11:E12 Make Variables Nonnegative Solving Method: Simplex LP B C D E 13 Total to Customer =SUM(C11:C12) =SUM(D11:D12) =SUM(E11:E12) H 14 Total Cost 15 =SUMPRODUCT(ShippingCost,UnitsShipped) On an Excel worksheet, transfer the algebraic formulation of the problem given below: then find the solution (values of the decision variables) using Solver. Submit only the accomplished Excel Worksheet. A D E F G H 1 Big M Company Distribution Problem 2 3 Shipping Cost 4 (per Lathe) Customer I Customer 2 Customer 3 5 Factory 1 $700 $900 $800 6 Factory 2 $800 $900 $700 7 8 9 Total Shipped 10 Units Shipped Customer I Customer 2 Customer 3 Out Output 11 Factory 1 10 2 0 12 12 12 Factory 2 0 6 15 15 13 Total to Customer 10 8 9 14 == 15 Order Size 10 Total Cost $20,500 Solver Parameters Set Objective Cell: TotalCost To: Min By Changing Variable Cells: UnitsShipped Subject to the Constraints: TotalShipped Out Output TotalToCustomer OrderSize Solver Options: Range Name F Cells 8 Total OrderSize C15:E15 Output HII:H12 9 Shipped ShippingCost C5:E6 10 TotalCost H15 11 Out =SUM(C11:E11) TotalShippedOut TotalToCustomer UnitsShipped F11:F12 C13:E13 12 =SUM(C12:E12) C11:E12 Make Variables Nonnegative Solving Method: Simplex LP B C D E 13 Total to Customer =SUM(C11:C12) =SUM(D11:D12) =SUM(E11:E12) H 14 Total Cost 15 =SUMPRODUCT(ShippingCost,UnitsShipped)