Question

Please Include Excel Formulation!

Mid-Atlantic Bus Lines CASE 7.3 Mid-Atlantic Bus Lines was founded by a group of manag- was collected over several weeks and represents a typical day's ers from Trailways when that company was acquired by operation. Greyhound. They launched a first-class, express bus service Questions operating between the major coastal cities from Philadelphia, 1. Use DEA to identify efficient and inefficient terminal opera- Pennsylvania, to Jacksonville, Florida. By hiring laid-off Trailways drivers and leasing buses, they established franchises tions. Formulate the problem as a linear programming model, and solve using computer software such as Excel in each city with local entrepreneurs, who were given the right to operate a Mid-Atlantic bus terminal. A percentage of the Solver that permits input file editing between runs. passenger ticket sales and freight sales would be kept by the 2. Using the appropriate reference set of efficient terminals, terminal operator to cover his or her costs and profit. make recommendations for changes in resource inputs for After several months of operation, some franchisees com- each inefficient terminal. plained about inadequate profits and threatened to close 3. What recommendations would you have for the one seri- their terminals. Because other franchisees were pleased with ously inefficient terminal in regard to increasing its outputs? their experiences, however, a study of all terminal operations 4 Discuss any shortcomings in the application of DEA to Mid- in the system was undertaken. The information in Table 7.10 Atlantic Bus Lines TABLE 7.10 Outputs and Inputs for Mid-Atlantic Bus Lines Bus Depot 1 Freight Sales 300 600 700 Facility Dollars 500 500 400 City Served Philadelphia, Pa. Baltimore, Md. Washington, D.C. Richmond, Va. Raleigh, N.C. Charleston, S.C. Savannah, Ga. Jacksonville, Fla. Ticket Sales 700 300 200 400 500 500 800 300 2 3 4 5 6 7 8 600 Labor-Hours 40 50 50 50 40 50 40 30 500 400 500 500 200 400 500 600 400

Answer 1

Depot (1)

Answer:

A F G H 1 Variables B C D E U1 U2 V1 V2 700 300 0 0 0.001428571 0 0 0.002 2 3 4 5 Objective function 6 1 7 Constrains 8 9 D1 10 D2 11 D3 12 D4 700 300 -40 300 600 -50 200 700 -50 400 600 -50 500 400 -40 500 500 -50 800 500 -40 300 200 -30 -500 0 <= 0 -500 -0.571428571 <= -400 -0.514285714 <= -500 -0.428571429 <= 0 -400 -0.085714286 <= -500 -0.285714286 <= 0 -600 -0.057142857 <= 0 -400 -0.371428571 <= 0 13 D5 14 D6 15 D7 16 D8 17 0 0 40 500 1 1 1

Formulation:

А B с E F G H 1 Variables V2 2 U1 U2 V1 700 300 0 0.00142857142857143 0 soo 0 0.002 3 4 =SUMPRODUCT(B2:E2,B3:53) 5 Objective function 6 7 Constrains 8 9 D1 <= 0 10 D2 700 300 200 0 11 D3 0 300 -40 600 -50 700 -50 600 -50 400 -40 500 -50 12 D4 400 CE 0 13 D5 500 -500 =SUMPRODUCT(B9:E9,$B$3:$E$3) -500 =SUMPRODUCT(B10:E10,$B$3:$E$3) -400 =SUMPRODUCT(B11:E11,$B$3:$E$3) -500 =SUMPRODUCT(B12:E12,$B$3:$E$3) -400 =SUMPRODUCT(B13:E13,$B$3:$E$3) -500 =SUMPRODUCT(B14:E14,$B$3:$E$3) -600 =SUMPRODUCT(B15:E15,$B$3:$E$3) -400 =SUMPRODUCT(B16:516,$B$3:$E$3) 500 =SUMPRODUCT(B17:E17,$B$3:$E$3) <= 0 500 0 14 D6 15 D7 800 500 -40 <- 0 300 200-30 <= 0 16 D8 17 0 0 40 1

Solver:

Solver Parameters X Set Objective: SDS5 To: Max Min O Value of: 0 By Changing Variable Cells: SB$3:SES3 1 Add Subject to the Constraints: $F$10 <= SH$10 SFS11 <= SHS11 SF$12 <= SH$12 $F$13 <= SH$13 SF$14 <= SH$14 SF$15 <= SH$15 $F$16 <= SH$16 $F$17 = SH$17 $F$9 <= 0 Change Delete Reset All Load/Save Make Unconstrained variables Non-Negative Select a Solving Simplex LP Method: Options 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 Solver problems that are non-smooth. Help Solve Close

Sensitivity Report:

Variable Cells Cell Name $B$3 U1 $C$3 U2 $D$3 V1 $E$3 V2 Final Value 0.00142857 0 0 0.002 Reduced Objective Allowable Allowable Cost Coefficient Increase Decrease 0 700 1E+30 0 0 300 0 1E+30 0 0 0 1E+30 0 0 1E+30 0 Constraints Cell Name $F$10 D2 $F$11 D3 $F$12 D4 $F$13 D5 $F$14 D6 $F$15 D7 $F$ 16 D8 $F$17 $F$9 D1 Final Value -0.57142857 -0.51428571 -0.42857143 -0.08571429 -0.28571429 -0.05714286 -0.37142857 1 0 Shadow Constraint Allowable Allowable Price R.H. Side Increase Decrease 0 0 1E+30 0.57142857 0 0 1E+30 0.51428571 0 0 1E+30 0.42857143 0 0 1E+30 0.08571429 0 0 1E+30 0.28571429 0 0 1E+30 0.05714286 0 0 1E+30 0.37142857 1 1 1E+30 1 1 0 0.05 1

Depot (2)

Answer:

ח A B с D E G H 1 Variables U1 U2 V1 V2 0 0 0.02 3 0 0.87391 8 2 300 600 0.00013 0.00139 4 5 Objective function 6 7 Constrains 8 9 D1 700 300 10 D2 300 600 11 D3 200 700 12 D4 400 600 13 D5 500 400 14 D6 500 500 15 D7 800 500 -40 0 -50 0 0 0 -50 -50 -40 -50 -500 -0.2913 <= -500 -0.12609 <= -400 0 <= -500 -0.11304 <= -400 -0.17826 <= -500 -0.23913 <= -600 1.1E-16 <= -400 -0.28261 <= 500 1 0 0 -40 0 16 D8 300 200 -30 0 17 0 0 50 1

Formulation:

D F G H E V2 B C U1 U2 V1 300 600 0 0.000130434782608696 0.00139130434782609 0.02 0 0 =SUMPRODUCT(B2:E2,B3:13) A 1 Variables 2 3 4 5 Objective function 6 7 Constrains 8 9 D1 10 D2 11 D3 12 D4 13 D5 -40 <= 0 <= 0 700 300 200 400 500 500 800 300 600 700 600 400 -50 -50 -50 -40 -500 =SUMPRODUCT(B9:19,$B$3:$E$3) -500 =SUMPRODUCT(B10:510,$B$3:$E$3) -400 =SUMPRODUCT(B11:E11,$B$3:$E$3) <= 0 -500 =SUMPRODUCT(B12:E12,$B$3:$E$3) <= 0 -400 =SUMPRODUCT(B13:E13,$B$3:$E$3) <= 0 -500 =SUMPRODUCT(B14:14,$B$3:$E$3) <= 0 -600 =SUMPRODUCT(B15:€15,$B$3:$E$3) <= 0 -400 =SUMPRODUCT(B16:116,$B$3:$E$3) <= 0 500 =SUMPRODUCT(B17:E17,$B$3:$E$3) 500 -50 14 D6 15 D7 500 -40 -30 16 D8 300 200 17 0 0 50 1

Solver:

Solver Parameters х Set Objective: SDS5 To: Max Min O Value of: 0 By Changing Variable Cells: SBS3: SES3 1 Add Subject to the Constraints: $F$10 <= SH$10 SFS11 <= SHS11 SF$12 <= SH$12 $F$13 <= SH$13 SF$14 <= SH$14 SF$15 <= SH$15 $F$16 <= SH$16 $F$17 = SH$17 $F$9 <= 0 Change Delete Reset All Load/Save Make Unconstrained variables Non-Negative Select a Solving Simplex LP Method: Options 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 Solver problems that are non-smooth. Help Solve Close

Sensitivity Report:

Variable Cells Cell Name $B$3 U1 $C$3 U2 $D$3 V1 $E$3 V2 Final Reduced Value Cost 0.00013043 0 0.0013913 0 0.02 o 0 -32.6086957 Objective Allowable Allowable Coefficient Increase Decrease 300 78.9473684 128.571429 600 450 125 0 1E+30 3.26086957 0 32.6086957 1E+30 Constraints Cell Name $F$10 D2 $F$11 D3 $F$12 D4 $F$13 D5 $F$14 D6 $F$15 D7 $F$16 D8 $F$17 $F$9 D1 Final Shadow Value Price -0.12608696 0 0 0.7173913 -0.11304348 0 -0.17826087 0 -0.23913043 0 1.1102E-16 0.19565217 -0.2826087 0 1 0.87391304 -0.29130435 0 Constraint Allowable Allowable R.H. Side Increase Decrease 0 1E+30 0.12608696 0 0.12 0.8 0 1E+30 0.11304348 0 1E+30 0.17826087 0 1E+30 0.23913043 0 0.3037037 0.08571429 0 1E+30 0.2826087 1 1E+30 1 0 1E+30 0.29130435

Depot (3)

Answer:

F G H A 1 Variables B с D E U1 U2 V1 V2 200 700 0 0 0 0.001428571 0 0.0025 N 3 4 5 Objective function 6 1 7 Constrains 8 700 9 D1 10 D2 300 300 -40 600 -50 700 -50 600 -50 11 D3 200 400 500 12 D4 13 D5 14 D6 15 D7 -500 -0.821428571 <= 0 -500 -0.392857143 <= 0 -400 0 <= 0 -500 -0.392857143 <= 0 -400 -0.428571429 <= 0 -500 -0.535714286 <= 0 -600 -0.785714286 <= 0 -400 -0.714285714 <= 0 400 1 500 400 -40 500 -50 500 -40 200 -30 0 50 800 16 D8 300 17 0 1

Formulation:

A B C D E F G H 1 Variables U1 U2 V1 V2 2 200 700 0 0 3 0 0.00142857142857143 0 0.0025 =SUMPRODUCT(B2:E2,B3:E3) 4 5 Objective function 6 7 Constrains 8 9 D1 -500 <= 0 10 D2 -500 11 D3 -40 -50 -50 -50 -40 -400 12 D4 -500 700 300 300 600 200 700 400 600 500 400 500 500 800 500 300 200 0 0 13 D5 -400 =SUMPRODUCT(B9:19,$B$3:$E$3) =SUMPRODUCT(B10:E10,$B$3:$E$3) <= 0 =SUMPRODUCT(B11:E11,$B$3:$E$3) <= 0 =SUMPRODUCT(B12:E12,$B$3:$E$3) <= 0 =SUMPRODUCT(B13:E13,$B$3:$E$3) <= 0 =SUMPRODUCT(B14:E14,$B$3:$E$3) <= 0 =SUMPRODUCT(B15:€15,$B$3:$E$3) <= 0 =SUMPRODUCT(B16:E16,$B$3:$E$3) <= 0 =SUMPRODUCT(B17:E17,$B$3:$E$3) 1 14 D6 -500 -50 -40 15 D7 -600 -400 16 D8 17 -30 50 400

Solver:

Solver Parameters х Set Objective: SDS5 I To: Max Min Value of: 0 By Changing Variable Cells: SB$3:SES3 Add Subject to the Constraints: SF$10 <= SH$10 SF$11 <= SH$11 $F$12 <= SH$12 $F$13 <= SH$13 $F$14 <= SH$14 SF$15 <= SH$15 $F$16 <= SH$16 $F$17 = SH$17 SF$9 <= 0 Change Delete Reset All Load/Save Make Unconstrained variables Non-Negative Select a Solving Simplex LP Method: Options 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 Solver problems that are non-smooth. Help Solve Close

Sensitivity Report:

Variable Cells Allowable Increase Cell Name Allowable Decrease 1E+30 Final Reduced Objective Value Cost Coefficient 0 o 200 0.001428571 0 700 0 0 0 0.0025 0 0 $B$3 U1 $C$3 U2 $D$3 V1 $E$3 V2 0 0 1E+30 0 1E+30 1E+30 0 Constraints 1 Cell Name $F$10 D2 $F$11 D3 $F$12 D4 $F$13 D5 $F$14 D6 $F$15 D7 $F$16 D8 $F$17 $F$9 D1 Final Value -0.39285714 0 -0.39285714 -0.42857143 -0.53571429 -0.78571429 -0.71428571 1 -0.82142857 Shadow Constraint Allowable Allowable Price R.H. Side Increase Decrease 0 0 1E+30 0.39285714 1 0 0.45833333 0 0 1E+30 0.39285714 0 0 1E+30 0.42857143 0 0 1E+30 0.53571429 0 0 1E+30 0.78571429 0 0 1E+30 0.71428571 1 1 1E+30 1 0 0 1E+30 0.82142857

Depot (4)

Answer:

F G H A B с D E 1 Variables U1 U2 V1 V2 2 400 600 0 0 3 0.00085 0.00094 0.00255 0.00174 4 5 Objective function 0.90213 6 7 Constrains 8 0 700 300 -40 -50 0 300 600 700 600 -50 9 D1 10 D2 11 D3 12 D4 13 D5 14 D6 0 200 400 -50 0 500 400 -40 -500 -0.09787 <= -500 -0.18298 <= -400 1.1E-16 <= -500 -0.09787 <= -400 0 <= -500 -0.10638 <= -600 0 <= -400 -0.33191 <= 500 1 0 500 500 800 15 D7 500 -50 -40 -30 0 0 0 300 200 16 D8 17 0 0 50 1

Formulation:

E F G H V2 B D U1 U2 V1 600 0 0.000851063829787234 0.000936170212765957 0.0025531914893617 400 0 0.00174468085106383 А 1 Variables 2 3 4 5 Objective function 6 7 Constrains =SUMPRODUCT(B2:E2,B3:E3) 8 700 300 -40 300 600 -50 -500 -500 -400 -500 200 700 9 D1 10 D2 11 D3 12 D4 13 D5 14 D6 -50 400 600 -50 400 -40 -400 =SUMPRODUCT(B9:19,$B$3:$E$3) <= 0 =SUMPRODUCT(B10:110,$B$3:$E$3) <= 0 =SUMPRODUCT(B11:E11,$B$3:$E$3) <= 0 =SUMPRODUCT(B12:E12,$B$3:$E$3) <= 0 =SUMPRODUCT(B13:E13,$B$3:$E$3) =SUMPRODUCT(B14:€14,$B$3:$E$3) =SUMPRODUCT(B15:E15,$B$3:$E$3) =SUMPRODUCT(B16:E16,$B$3:$E$3) =SUMPRODUCT(B17:E17,$B$3:$E$3) 1 500 500 <= 0 500 -50 -500 <= 0 15 D7 800 500 -40 -600 <= 0 16 D8 300 200 -30 -400 <= 0 17 0 0 50 500

Solver:

Solver Parameters х Set Objective: SDSS To: Max Min O Value of: 0 By Changing Variable Cells: SB$3: SES3 Add Subject to the Constraints: SF$10 <= SH$10 $F$11 <= SH$11 SF$12 <= SH$12 SF$13 <= SH$13 SF$14 <= SH$14 SF$15 <= SH$15 $F$16 <= SH$16 $F$17 = SH$17 SFS9 <=0 Change Delete Reset All Load/Save Make Unconstrained variables Non-Negative Select a Solving Method: Simplex LP Options 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 Solver problems that are non-smooth. Help Solve Close

Sensitivity Report:

Variable Cells Reduced Cost 0 Cell Name $B$3 U1 $C$3 U2 $D$3 V1 $E$3 V2 Final Value 0.000851064 0.00093617 0.002553191 0.001744681 Objective Allowable Allowable Coefficient Increase Decrease 400 350 21.0526316 600 33.3333333 280 0 0.86956522 5.18518519 0 51.8518519 8.69565217 0 0 0 Constraints Final Shadow Value Price Cell Name $F$10 D2 $F$11 D3 $F$12 D4 $F$13 D5 $F$14 D6 $F$15 D7 $F$16 D8 $F$17 $F$9 D1 -0.18297872 0 1.11022E-16 0.59574468 -0.09787234 0 0 0.08510638 -0.10638298 0 0 0.29787234 -0.33191489 0 1 0.90212766 -0.09787234 0 Constraint Allowable Allowable R.H. Side Increase Decrease 0 1E+30 0.18297872 0 0.16428571 0.17142857 0 1E+30 0.09787234 0 0.02608696 0.17826087 0 1E+30 0.10638298 0 0.3037037 0.04444444 0 1E+30 0.33191489 1 1E+30 1 0 1E+30 0.09787234

Depot (5)

Answer:

D E F. G H V2 1 2 0 A B с Variables U1 U2 V1 2. 500 400 3 0.00143 0.00071 4 5 Objective function 6 0 0.0025 0 1 7 Constrains 8 9 D1 700 300 -40 -500 -0.03571 <= 0 300 600 -50 0 10 D2 11 D3 200 700 -50 0 -500 -0.39286 <= -400 -0.21429 <= -500 -0.25 <= -400 0 <= 12 D4 400 600 -50 0 13 D5 500 400 -40 0 14 D6 500 500 -50 0 -500 -0.17857 <= -600 0 <= 15 D7 800 500 -40 0 16 D8 300 200 -30 0 -400 -0.42857 <= 400 1 17 0 0 40 1

Formulation:

A B с D E F G H 1 Variables U1 U2 V1 V2 2 500 400 0 0 3 0.00142857142857143 0.000714285714285715 0 0.0025 4 5 Objective function 6 =SUMPRODUCT(B2:E2,B3:23) 7 Constrains 8 9 D1 700 300 -500 <= 0 -40 -50 10 D2 300 600 -500 11 D3 200 700 -50 -400 12 D4 400 600 -50 -500 13 D5 500 400 -40 -400 =SUMPRODUCT(B9:09,$B$3:$E$3) =SUMPRODUCT(B10:E10,$B$3:$E$3) <= 0 =SUMPRODUCT(B11:E11,$B$3:$E$3) <= 0 =SUMPRODUCT(B12:E12,$B$3:$E$3) <= 0 =SUMPRODUCT(B13:E13,$B$3:$E$3) <= 0 =SUMPRODUCT(B14:E14,$B$3:$E$3) <= 0 =SUMPRODUCT(B15:E15,$B$3:$E$3) <= 0 =SUMPRODUCT(B16:E16,$B$3:$E$3) <= 0 =SUMPRODUCT(B17:E17,$B$3:$E$3) 1 14 D6 500 500 -50 -500 -600 15 D7 800 500 -40 16 D8 300 200 -30 -400 400 17 0 0 40

Solver:

Solver Parameters х Set Objective: SDSS 1 To: Max Min O Value of: 0 By Changing Variable Cells: SB$3: SES3 Add Subject to the Constraints: SF$10 <= SH$10 $F$11 <= SH$11 SF$12 <= SH$12 SF$13 <= SH$13 SF$14 <= SH$14 SF$15 <= SH$15 $F$16 <= SH$16 $F$17 = SH$17 SFS9 <=0 Change Delete Reset All Load/Save Make Unconstrained variables Non-Negative Select a Solving Method: Simplex LP Options 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 Solver problems that are non-smooth. Help Solve Close

Sensitivity Report:

Variable Cells Final Allowable Allowable Reduced Objective Cost Coefficient Value Increase Decrease 0 140 0 Cell Name $B$3 U1 $C$3 U2 $D$3 V1 $E$3 V2 0.001428571 0.000714286 500 400 0 0 87.5 0 0 0 0 1E+30 0.0025 0 0 1E+30 0 Constraints Final Value Cell Name $F$10 D2 $F$11 D3 $F$12 D4 $F$13 D5 $F$14 D6 $F$15 D7 $F$16 D8 $F$17 $F$9 01 -0.39285714 -0.21428571 -0.25 0 -0.17857143 0 -0.42857143 1 -0.03571429 Shadow Constraint Allowable Allowable Price R.H. Side Increase Decrease 0 0 1E+30 0.39285714 0 0 1E+30 0.21428571 0 0 1E+30 0.25 1 0 0.0326087 0.02272727 0 0 1E+30 0.17857143 0 0 0.01923077 0.05555556 0 0 1E+30 0.42857143 1 1 1E+30 1 0 0 1E+30 0.03571429

Depot (6)

Answer:

F. G I A B с D E 1 Variables U1 U2 V1 V2 2 500 500 0 0 3 0.000851 0.000936 0.002553 0.001745 4 0.893617 5 Objective function 6 7 Constrains 8 9 D1 700 300 -40 10 D2 300 600 -50 200 700 -50 11 D3 12 D4 13 D5 600 -50 400 500 500 O O O O O O O O 400 -40 -500 -0.09787 <= -500 -0.18298 <= -400 1.11E-16 <= -500 -0.09787 <= -400 1.11E-16 <= -500 -0.10638 <= -600 2.22E-16 <= -400 -0.33191 <= 500 1 14 D6 500 -50 15 D7 800 500 -40 16 D8 300 200 -30 17 0 0 50

Formulation:

A E F G H 1 Variables V2 B C D U1 U2 V1 500 500 0 0.000851063829787234 0.000936170212765957 0.0025531914893617 2 0 3 0.00174468085106383 4 =SUMPRODUCT(B2:E2,B3:E3) 5 Objective function 6 7 Constrains 8 700 300 -40 -500 <= 0 300 600 -50 -500 <= 0 9 D1 10 D2 11 D3 12 D4 13 D5 200 -50 -400 700 600 400 -50 -500 400 -40 -400 =SUMPRODUCT(B9:19,$B$3:$E$3) =SUMPRODUCT(B10:E10,$B$3:$E$3) =SUMPRODUCT(B11:E11,$B$3:$E$3) <= 0 =SUMPRODUCT(B12:E12,$B$3:$E$3) <= 0 =SUMPRODUCT(B13:E13,$B$3:$E$3) <= 0 =SUMPRODUCT(B14:14,$B$3:$E$3) =SUMPRODUCT(B15:E15,$B$3:$E$3) <= 0 =SUMPRODUCT(B16:516,$B$3:$E$3) =SUMPRODUCT(B17:E17,$B$3:$E$3) 500 500 800 500 14 D6 15 D7 <= 0 -50 -40 500 -500 -600 -400 500 16 D8 300 200 -30 <= 0 17 0 0 50 1

Solver:

Solver Parameters X Set Objective: SDSS ti To: Max Min O Value Of: 0 By Changing Variable Cells: $B$3:$E$3 1 Add Subject to the Constraints: SF$10 < = SH$10 $F$11 <= SHS11 $F$12 <= SH$12 SF$13 <= SH$13 SF$14 <= SH$14 SF$15 <= SHS15 SF$16 <= SH$16 SF$17 = SH$17 SF$9 <= 0 Change Delete Reset All Load/Save Make Unconstrained variables Non-Negative Select a Solving Simplex LP Method: Options 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 Solver problems that are non-smooth. Help Solve Close

Sensitivity Report:

Variable Cells Reduced Cost Cell Name $B$3 U1 $C$3 U2 $D$3 V1 $E$3 V2 Final Value 0.00085106 0.00093617 0.00255319 0.00174468 0 0 0 Objective Allowable Allowable Coefficient Increase Decrease 500 125 184.210526 500 291.666667 100 0 7.60869565 1.85185185 0 18.5185185 76.0869565 0 Constraints Cell Name $F$10 D2 $F$11 D3 $F$12 D4 $F$13 D5 $F$14 D6 $F$15 D7 $F$16 D8 $F$17 $F$9 D1 Final Shadow Value Price -0.18297872 o 1.1102E-16 0.21276596 -0.09787234 0 1.1102E-16 0.74468085 -0.10638298 0 2.2204E-16 0.10638298 -0.33191489 0 1 0.89361702 -0.09787234 0 Constraint Allowable Allowable R.H. Side Increase Decrease 0 1E+30 0.18297872 0 0.16428571 0.17142857 0 1E+30 0.09787234 0 0.02608696 0.17826087 0 1E+30 0.10638298 0 0.3037037 0.04444444 0 1E+30 0.33191489 1 1E+30 1 0 1E+30 0.09787234

Depot (7)

Answer:

B D E F G H V2 А. с 1 Variables U1 U2 V1 2 800 500 3 0.00106 0.0003 0 0 0.00167 0 4 5 Objective function 6 1 7 Constrains 8 9 D1 700 300 300 600 10 D2 11 D3 -40 -50 -50 -50 0 0 0 200 700 12 D4 600 0 400 500 -500 1.1E-16 <= -500 -0.33333 <= -400 -0.24242 <= -500 -0.22727 <= -400 -0.01515 <= -500 -0.15152 <= -600 0 <= -400 -0.28788 <= -40 0 13 D5 14 D6 400 500 500 -50 0 15 D7 500 -40 0 800 300 16 D8 200 -30 0 17 0 0 40 600 1 1

Formulation:

A E ד G H 1 Variables V2 B с U1 U2 V1 800 500 0 0.00106060606060606 0.000303030303030303 0 2 0 3 0.00166666666666667 4 =SUMPRODUCT(B2:E2,B3:53) 5 Objective function 6 7 Constrains 8 9 D1 -40 10 D2 700 300 200 300 600 700 11 D3 12 D4 13 D5 -50 -50 -50 400 600 -500 -500 -400 -500 -400 -500 -600 -400 500 -40 =SUMPRODUCT(B9:59,$B$3:$E$3) <= 0 =SUMPRODUCT(B10:E10,$B$3:$E$3) <= 0 =SUMPRODUCT(B11:E11,$B$3:$E$3) <= 0 =SUMPRODUCT(B12:E12,$B$3:$E$3) <= 0 =SUMPRODUCT(B13:E13,$B$3:$E$3) <= 0 =SUMPRODUCT(B14:E14,$B$3:$E$3) <= 0 =SUMPRODUCT(B15:E15,$B$3:$E$3) <= 0 =SUMPRODUCT(B16:E16,$B$3:$E$3) <= 0 =SUMPRODUCT(B17:E17,$B$3:$E$3) 14 D6 500 400 500 500 200 -50 15 D7 800 -40 16 D8 300 -30 17 0 0 40 600 1

Solver:

Solver Parameters X Set Objective: SDS5 To: Min Max 0 O Value of: By Changing Variable Cells: $B$3:SES3 Add Subject to the Constraints: $F$10 <= SH$10 $F$11 <= SHS11 $F$12 <= SH$12 $F$13 <= SH$13 $F$14 <= SH$14 $F$15 <= SH$15 $F$16 <= SH$16 $F$17 = SH$17 SF$9 <= 0 Change Delete Reset All Load/Save Make Unconstrained variables Non-Negative Select a Solving Simplex LP Method: Options 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 Solver problems that are non-smooth. Help Solve Close

Sensitivity Report:

Variable Cells Cell Name $B$3 U1 $C$3 U2 $D$3 V1 $E$3 V2 Final Reduced Objective Value Cost Coefficient 0.00106061 0 800 0.00030303 0 500 0 0 0 0.00166667 0 0 Allowable Increase 0 0 0 1E+30 Allowable Decrease 0 0 1E+30 0 Constraints Cell Name $F$10 D2 $F$11 D3 $F$12 D4 $F$13 D5 $F$14 D6 $F$15 D7 $F$16 D8 $F$17 $F$9 D1 Final Value -0.33333333 -0.24242424 -0.22727273 -0.01515152 -0.15151515 0 -0.28787879 Shadow Constraint Allowable Allowable Price R.H. Side Increase Decrease 0 0 1E+30 0.33333333 0 0 1E+30 0.24242424 o 0 1E+30 0.22727273 0 0 1E+30 0.01515152 0 0 1E+30 0.15151515 1 0 0.01282051 0.04761905 0 0 1E+30 0.28787879 1 1 1E+30 1 0 0 0.04166667 0.02380952 1 1.1102E-16

Depot (8)

Answer:

C E F G H V2 0 A B D 1 Variables U1 U2 V1 2 300 200 0 3 0.001429 0.000714 0 4 5 Objective function 0.571429 6 0.0025 7 Constrains 8 700 300 -40 0 9 D1 10 D2 300 600 -50 0 11 D3 200 700 -50 -500 -0.03571 <= -500 -0.39286 <= -400 -0.21429 <= -500 -0.25 <= -400 0 <= 0 12 D4 400 600 -50 0 13 D5 400 -40 0 500 500 14 D6 500 -50 -500 -0.17857 <= 0 15 D7 800 500 -40 -600 0 <= 0 300 200 -30 0 16 D8 17 -400 -0.42857 <= 400 0 0 30 1 1

Formulation:

B C D E F G H A 1 Variables U1 U2 V1 V2 2 300 200 0 0 3 0.00142857142857143 0.000714285714285715 0 0.0025 4 =SUMPRODUCT(B2:E2,B3:E3) 5 Objective function 6 7 Constrains 8 9 D1 700 300 -40 -500 10 D2 300 600 -50 -500 11 D3 200 700 -50 -400 12 D4 400 600 -50 -500 13 D5 500 -40 -400 400 500 =SUMPRODUCT(B9:19,$B$3:$E$3) <= 0 =SUMPRODUCT(B10:E10,$B$3:$E$3) <= 0 =SUMPRODUCT(B11:E11,$B$3:$E$3) <= 0 =SUMPRODUCT(B12:E12,$B$3:$E$3) <= 0 =SUMPRODUCT(B13:E13,$B$3:$E$3) <= 0 =SUMPRODUCT(B14:E14,$B$3:$E$3) <= 0 =SUMPRODUCT(B15:E15,$B$3:$E$3) <= 0 =SUMPRODUCT(B16:E16,$B$3:$E$3) <= 0 =SUMPRODUCT(B17:E17,$B$3:$E$3) 1 14. D6 500 -50 800 500 -40 15 D7 16 D8 -500 -600 -400 300 200 -30 17 0 0 30 400

Solver:

Solver Parameters х Set Objective: SDS5 ti To: Max Min Value of: 0 By Changing Variable Cells: SBS3: SES3 Add Subject to the Constraints: $F$10 <= SH$10 SF$11 <= SH$11 $F$12 <= SH$12 $F$13 <= SH$13 $F$14 <= SH$14 SF$15 <= SH$15 $F$16 <= SH$16 $F$17 = SH$17 SF$9 <= 0 Change Delete Reset All Load/Save Make Unconstrained variables Non-Negative Select a Solving Simplex LP Method: Options 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 Solver problems that are non-smooth. Help Solve Close

Sensitivity Report:

Variable Cells Reduced Cost Allowable Decrease 0 Cell Name $B$3 U1 $C$3 U2 $D$3 V1 $E$3 V2 Final Value 0.00142857 0.00071429 0 0.0025 0 0 0 Objective Coefficient 300 200 0 0 Allowable Increase 20 0 0 1E+30 12.5 1E+30 0 0 Constraints Cell Name $F$10 D2 $F$11 D3 $F$12 D4 $F$13 D5 $F$14 D6 $F$15 D7 $F$16 D8 $F$17 $F$9 D1 Final Shadow Value Price -0.39285714 0 -0.21428571 0 -0.25 0 0 0.14285714 -0.17857143 0 0 0.28571429 -0.42857143 0 1 0.57142857 -0.03571429 0 Constraint Allowable Allowable R.H. Side Increase Decrease 0 1E+30 0.39285714 0 1E+30 0.21428571 0 1E+30 0.25 0 0.0326087 0.02272727 0 1E+30 0.17857143 0 0.01923077 0.05555556 0 1E+30 0.42857143 1 1E+30 1 0 1E+30 0.03571429

SUMMARY

VARIABLES	U1: TICKET SALE	U2: FREIGHT SALE	V1: LABOR HOURS	V2: FACILITY DOLLARS
DEPOT 1 PHILADELPHIA					DEPOT 2 BALTIMORE
Variables	U1	U2	V1	V2			Variables	U1	U2	V1	V2
	700	300	0	0				300	600	0	0
	0.001429	0	0	0.002				0.00013	0.001391	0.02	0
Objective function			1				Objective function			0.873913
DEPOT 3 WASHINGTON D C					DEPOT 4 RICHMOND
Variables	U1	U2	V1	V2			Variables	U1	U2	V1	V2
	200	700	0	0				400	600	0	0
	0	0.001429	0	0.0025				0.000851	0.000936	0.002553	0.001745
Objective function			1				Objective function			0.902128
DEPOT 5 RALEIGH						DEPOT 6 CHARLESTON
Variables	U1	U2	V1	V2			Variables	U1	U2	V1	V2
	500	400	0	0				500	500	0	0
	0.001429	0.000714	0	0.0025				0.000851	0.000936	0.002553	0.001745
Objective function			1				Objective function			0.893617
DEPOT 7 SAVANNAH					DEPOT 8 JACKSONVILLE
Variables	U1	U2	V1	V2			Variables	U1	U2	V1	V2
	800	500	0	0				300	200	0	0
	0.001061	0.000303	0	0.001667				0.001429	0.000714	0	0.0025
Objective function			1				Objective function			0.571429

D1	40	500
D3	50	400
D5	40	400
D7	40	600
D2	50	500
D4	50	500
D6	50	500
D8	30	400

700 D7, 40, 600 600 D6, 50, 500 D1, 40, 500 500 D2, 50, 500 D4, 50, 500 400 D8, 30, 400 DOLLARS D5, 40, 400 D3, 50, 400 300 200 100 0 0 10 20 30 40 50 60 LABOR HOUR

I hope I have addressed each and every part of the question you’ve asked. Please leave a like if you find this answer helpful, it really helps me a lot and motivates me in providing better answers in future. If you have any doubts please let me know before leaving a dislike, I would surely assist you. Thanks in advance for liking this answer.