Question

RentR Cars is a multisite car rental company in the city. It is trying out a new "return the car to the location most convenient for your policy to improve customer service But this means that the company has to constantly move cars around the city to maintain required levels of vehicle availability. The supply and demand for economy cars, and the total cost of moving these vehicles between sites, are shown below 59 $5 45 30 90 8 12 10 L6 Demand 30 40 50 45 1651165 moving costs using Microsof Excel. (Leave no cells blank-be certain to enter "0" wherever required.) Candidale solution Total shipped Cost Total cost

Answer 1

The model is shown below

FromTo G Supply 1 FromTo 45 12 10 90 40 50 45 5 Demand Supply 0 G 7 Solution 8 A 0 10 C 11 Total shipped 0 12 13 Cost 14 A 15 B 16 C 17 Total cost 18 19 0 0 21 回凹 + 130% Sheet1073 Sheet1074 Sheet508 (2) Sheet1075 Sheet1076 Sheet1077 Sheet1078

The formula is shown below

F17 -SUM(B14:E16) Supply 1 FromTo 45 6 14 12 10 45 5 Demand Supply 7 Solution 8 A 9 B 10 C 11 Total shipped 12 13 Cost 14 A 15 B 16 C 17 Total cost 18 19 SUM(B8:E8) SUM(B9:E9) SUM(B10:E10) 0 0 0 0 -SUM(B8:B10) -SUM(C8:C10) -SUM(D8:D10) SUM(E8:E10) -B8*B2 -89"ВЗ B10 B4 -C8 C2 -C9*C3 -C10*C4 -D8 D2 -D9 D3 -D10 D4 -E8 E2 -E9E3 -E10*E4 -SUM(B14:E16) 21 ...Sheet1073 Sheet074 Sheet508 (2) Sheet1075Sheet1076 Sheet1077 Sheot1078 +130% 囲回凹

The solver parameters are shown below

F17 SUM(B14:E16) Solver Parameters 1 FromTo G Supply 45 Set Objective o: O Max By Changing Variable Cells: 12 10 4 C 5 Demand 14 BS8 SES10 40 50 45 Subject to the Constraints: $B511:SES11SBS5:SES5 7 Solution G Supply 0 0 10 C 11 Total shipped 0 12 13 Cost 14 A 15 B 16 C 17 Total cost 18 19 0 Reset All Select a Solving Method Simplex LP 0 0 0 Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Sover problems that are non-smooth. Solve Clgse 21 Sheet107 She074 Sheet508 (2 Sheet1075 Sheet076 Sheet1077Sheet1078

The result is shown below

F17 SUM(B14:E16) 1 FromTo G Supply 45 Solver Results 12 10 4 C 5 Demand 14 50 Solver found a solution, All Constraints and optimality conditions are satisfied. 40 45 OKeep Solver Solution O Restore Original Values Return to Solver Parameters Dialog Sensitivity 7 Solution G Supply 45 45 25 20 45 0 0 40 40 Outline Reports 10 C 11 Total shipped 12 13 Cost 14 A 15 B 16 C 17 Total cost 18 19 Save Scenario... 30 50 Solver found a solution. All Constraints and optimality conditions are 135 30 When the GRG engine is used, Solver has found at least a local optimal solution. When Simplex LP is used, this means Solver has found a global optimal solution. 0 50 40 150 240 645 21 Sheet107 She074 Sheet508 (2 Sheet1075 Sheet076 Sheet1077Sheet1078 +130%