Rename the Excel file in the following way: Lastname_firstname_AGEC 3413_Fall 22_HW 4" while submitting. For question # 2 you need to use a word file. The word file should be named in the following way: Lastname_firstname_AGEC 3413_Fall 22_HW 4." Question 1: 'Replicate' the following problems from Chapter 6 on using Excel Solver. For each problem, generate a sensitivity report/table. a) The Transportation Problem b) The Transshipment Problem c) The Assignment Problem To solve each problem open a new "Sheet" on the same excel file. Rename each "Sheet" in the following way. Sheet 1 = Transportation Problem; Sheet 2 = Transshipment Problem; Sheet 3 = Assignment Problem Meaning of 'replication': Using the parameter values for objective function and constraint equations a table on an excel spreadsheet. Basically, you need to tables same as the tables given in Chapter 6 PowerPoint slides for each of the examples. You need use this table to generate final values of decision variables and also the final value of the objective function. However, you cannot just copy and paste the results from the PPT slides. You need to show me the formulas for each constraint equations and the objective function that you have plugged in on the Excel spreadsheet. Once you plug in the formulas use the Excel solver to generate results. Your results should match the results given in the PPT slides for each of the examples. Additionally, you need to generate a sensitivity table to answer Question 2. The final values are available on slide 9-10 for Transportation problem, slide 18-19 for Transshipment problem, and slide 26 for the Assignment problem.

Question 2: For each of the examples/problems, generate a sensitivity report/table. For each of the examples/problems, write down the sensitivity ranges for all the objective function coefficients and sensitivity ranges for all the right hand side parameters.

Assignment Model Example Computer Solution with Excel (3 of 3) Chibit 15 - Microsoft Darcel Hom Inert Pape Layout View Cal Connections & Clear Show Detail 2. Solver LA Properties fo Reapply " Hide Detail 7 Data Analysis From from Other rdsting 41 Sort Remove Data Consolidate What SOURCES - Connection Refresh Edit Links ritter Advanced Columns Duplicates Validation Group Ungroup Subtotal ACCELI Web Analywin Get External Data Connections Sart & MBer Data Taah Outling Analyun C11 G SUMPRODUCTICS:F8,C16:F19) A B C D E F G H K L M N The ACC Basketball Example Game Sites Teams Teams Officials Team Raleigh Atlanta Durham Clemson Available Assigned 0 0 0 0 Teams Demanded 10 Teams Assigned 11 Total Mileage = 450 12 13 Mileage: 14 Game Sites 15 Officials Team Raleigh Atlanta Durham Clemson 16 A 210 90 180 160 17 100 70 130 200 18 175 105 140 170 19 80 65 105 120 20 Exhibit 6.15 6-26Assignment Model Example Problem Definition and Data Problem: Assign four teams of officials to four games in a way that will minimize total distance traveled by the officials. Supply is always one team of officials, demand is for only one team of officials at each game. Game Sites Officials RALEIGH ATLANTA DURHAM CLEMSON 210 90 180 160 100 70 130 200 175 105 140 170 80 65 105 120 6-22Transportation Model Example Computer Solution with Excel (4 of 4) Kansas City 25 A Chicago (150) (200) 125 175 Omaha B St. Louis (175) 2 175 100 (100) Des Moines Cincinnati (275) 3 C (300) Figure 6.2 Transportation network solution for wheat-shipping example 6-10Transportation Model Example: Computer Solution with Excel A B C D E F G H K L 1 Transportation Model Demand (Destination) 4 Unit Cost ($/ton) Chicago St. Louis Cincinnati 5 Kansas City 56 58 $10 Supply (Source) Omaha 57 $11 $11 7 Des Moines $12 9 10 Demand (Destination) 11 Shipment Quantity (tons) Chicago St. Louis Cincinnati Total Shipped Supply 12 Kansas City 25 125 150 150 13 Omaha 175 175 175 600 total tons 14 Supply (Source) Des Moines 175 100 0 275 275 15 Total Received 200 10.0 300 16 Total Cost 17 Demand 200 100 300 54.525 18 600 19 total tons Solver Parameters X Set Target Cell: $3417 Solve Model Solution: Equal To: OMax OMn Value of: Close By Changing Cels: KC-Ch = 25 tons $E$12:$6$14 Guess KC-Cin = 125 tons Subject to the Constraints: Options Om-Cin = 175 tons #E$15:$6$15 = $E$17:$6$17 Add $H$12:$H$14 - $3$12:$3$14 DM-Ch = 175tons Change Reset All DM-StL = 100 tons Delete Help Z = $4,525 (minimum cost) 6-9Transportation Model Example Problem Definition and Data How many tons of wheat to transport from each grain elevator to each mill on a monthly basis in order to minimize the total cost of transportation? Grain Elevator Supply Mill Demand 1. Kansas City 150 A. Chicago 200 2. Omaha 175 B. St. Louis 100 3. Des Moines 275 C. Cincinnati 300 Total 600 tons Total 600 tons Transport Cost from Grain Elevator to Mill ($/ton) Grain Elevator | A. Chicago B. St. Louis C. Cincinnati 1. Kansas City $ 6 $ 8 $ 10 2. Omaha 11 11 3. Des Moines 5 12 6-4Transshipment Model Example: Spreadsheet Formulation with Excel A B C D E F G K Transshipment Model Transshipment Point Unit Cost (S/ton) Kansas City Omaha Des Moines Obj. Function Coefficients Nebraska $16 $10 $12 (Cost per Unit) Supply (Source) Colorado $15 $14 $17 Supply Constraints 8 & RHS Values 9 Transshipment Point 10 Shipment Quantity (tons) Kansas City Omaha Des Moines Total Shipped Supply 11 Nebraska 0 300 12 Supply (Source) Colorado 0 10 300 600 total tons 13 Total Received 0 0 14 15 Decision Variables Total Cost 16 (Qty. Shipped) 17 Demand (Destination) 18 Unit Cost (S/ton) Chicago St. Louis Cincinnati 19 Kansas City $6 $10 Objective Function 20 Transshipment Point Omaha $7 $11 $11 "Target Cell" 21 Des Moines 54 $5 $12 22 23 24 Demand (Destination) Transshipment Constraints 25 Shipment Quantity (tons) Chicago St. Louis Cincinnati Total Shipped (Tot. Shipped = Tot. Received) 26 Kansas City 10 0 27 Transshipment Point Omaha 28 Des Moines 0 29 Total Received 0 0 = 11 Demand Constraints Demand 200 100 300 600 & RHS Values total tons 10 /3/2011Transshipment Model Example: Solver Parameters X Computer Solution with Excel Set Target Cell: $1$16 Solve B E Equal To: Max OMD OYalue of: Close 1 Transshipment Model By Changing Cells: $1$1 1 106$12,$1$26:06420 Guess Transshipment Point Subject to the Constraints: Options A W N Unit Cost (S/ton) Kansas City Omaha Des N $6$13:$6$13 = $H$26:$41$28 add 5 Nebraska $16 $10 $E$29:$6$29 - $6$31:$6$31 Supply (Source) Colorado $15 $14 #H$11:$4$12 - $3$11:3412 change 6 Baset All 7 Delete Help 8 9 Transshipment Point 10 Shipment Quantity (tons) Kansas City Omaha Des Moines Total Shipped Supply 11 Nebraska 0 300 300 300 12 Supply (Source) Colorado 300 300 300 600 total tons 13 Total Received 0 300 300 14 15 Total Cost 16 $12,400 17 Demand (Destination) 18 Unit Cost (S/ton) Chicago St. Louis Cincinnati 19 Kansas City $6 $8 $10 20 Transshipment Point Omaha $11 $11 21 Des Moines SA $5 $12 22 23 24 Demand (Destination) 25 Shipment Quantity (tons) Chicago St. Louis Cincinnati Total Shipped 26 Kansas City 0 0 27 Transshipment Point Omaha 0 300 300 28 Des Moines 200 100 0 300 29 Total Received 200 100 300 30 = = 31 Demand 200 100 300 32 600 33 total tons 10 /3/2011 6-19Transshipment Model Example Problem Definition and Data Extension of the transportation model in which intermediate transshipment points are added between sources and destinations. Shipping Costs Grain Elevator Farm 3. Kansas City 4. Omaha 5. Des Moines 1. Nebraska $16 10 12 2. Colorado 15 14 17 Wheat is being harvested at Nebraska nad Colorado before being shipped to the three Grain-elevators at Kansas City, Omaha, and Des Moines. The amount of wheat harvested at each farm is 300 tons. 1-15