Question

2. The Chicago Board of Education is taking bids on the city's four school bus routes. Four companies have made the bids given in the following table. **Use excel solver Company 1 Company 2 Company 3 Company 4 Route 1 Route 2 Route 3 Route 4 2500 3000 1500 4000 4000 3000 2000 4000 5000 a. Suppose that each bidder can only be assigned to one route. Develop a model to find out how Chicago could minimize its cost of running the four bus routes. b. Suppose that each company can be assigned to at most two routes. Develop a model to find out how the companies should be assigned to the routes to minimize costs.

Answer 1

a.

Below excel screenshot show the approach to solve using excel -> solver

Bock7-Excel Q Tell me what you want to do. Pumeet Sabharwal S Page Layout File Home Insert Formulas Data Roview View Shate E AutoSum XCut A A Wrap Text Calibn General Normal Bad Good 11 Fill E Copy e Conditional Furmal as Neutral Formatting Table Pasle Iusert Delele Furmal Surt & Find & B IU A Merge & Center Calculation Check Cell Furmal Painter Clcar Filter Select dling dpboard Alignmen Number Styles Cels Fom f K8 K C D E G M. D S A H. L U V 1 2 Route 1 Route 2 Route 3 Houte 4 3 Company 1 2500 3000 1500 4 Company 4000 4000 Curmpary 3 3000 5 2000 Company 4 4000 5000 7 We will setup table to solve using Excel -Solver 10 11 Below table shows company aligned to each route, only binary values (0,1) -highlighted cells only Houte 1 Houte 2 12 Route 3 Route 4 SUMIC13:E13) SUMID14,F14) 13 Company 1 Спmрaпу 2 Curnpary 3 0 Each company can be assigned only one route 14 1 15 0 SUMIC15,15) 1 16 SUME16:F16) Company 4 1 17 SUMIE13:E16 18 SUMIC13:C16) 19 = 20 1 1 1 1 21 22 Objective function 23 Objoctive is to minimize total cost 24 SUMPRODUCTIC3:r6,c13:F16) 25 MIN BID COST 26 27 28 29 30 Sheet - + 100% Ready еве 207 AM O Type here to search г. x 9/28/2019

Bock7 - Excel Puneet Sabharwal Share Data Reviw Tell me what you want to do. ile Home Insert Page Layout Formulas View Clear Reapply Filter Advanced Columns FillDuplicates Validation Uata Analysis Show Queries Connections EE From Table 2, Solver Properties From From From Frum Ouher Access Web Text Sources Whal- If Furecast Group Ungroup Sublotal Analysis Sheet Texl to lashReove Consolidale Relationships Existing Connections Query Recent Sources New Refresh Dala Sor All Cdit Links Get & Transorm Dal Toals Cel Falerral Dela Connetions Sort & iter Ferecas Ouine Aralye E25 X Solver Parameters C D G A E F H. 1 SES25 Sat Ohjective Route 4 2 Route 1 Route 2 Route 3 3 Company 1 2500 3000 1500 O Oyaue U Mar To Min 4 Company 4000 4000 E Changng Varisble Cels Curmpary 3 3000 5 2000 ScS13:SFS10 Company 4 4000 5000 7 Subject to the Constrants SC$13:5FS16- hinary scS175517-$cs0F520 Add We will setup table to solve using Excel -Solver ss51356516 S/51351516 10 Changs 11 Below table shows company aligned to each route, only binary values (0,1) -highlighted cells only Houte 1 Houte 2 12 elete Route 3 Route 4 Each company can be as SUMIC13:E13) SUMID14,F14) 13 Company 1 Company 2 Curnpary 3 14 1 Eeset All 15 0 SUMIC15,15) 1 Load/Save 16 SUME16:F16) Company 4 1 Make Uncenstrained Variables Non Negalive 17 SUME13:E16) 18 SUMIC13:C16) Selett a Solving Methuc GHG Noelines tions 19 = 20 1 1 1 1 Soving Method 21 Select the GRS Nonlnear enaire tor Schver Brcblems that are smooth noninear, Selact the LP Simolex 22 Objective function engine tar inpar Solver Problems and select the Evalutonary engine for Salver problems that are nonsmooth 23 Objoctive is to minimize total cost 24 SUMPRODUCTIC3:r6,C13:F16) 25 MIN BID COST Eclve Hep 26 27 28 29 30 Sheet1 Point 100% 208 AM e Type here to search 2 г. x 9/28/2019

Bock7 - Excel Punoet Sabharwal AShare Data Reviw Tell me what you want to do... File Home Insert Page Layout Formulas View 3ConnectionS Clear Pata AnalysiS Show Queries A TReapply Filler 2, Solver Fram Table From Frum From Frum Ouher Text Sources RefeshPropegins Whal-If Furecast Gruup Ungroup Sublolal Analysis Sheet Flash Remove Existing New Reltesh Dala Advanced Columns Fill Duplicates Validation Texl lo Consolidate Relationships So Connections Query Recent Sources Get & Transform All Edit Links Access Web Cel Falerral Dela Connections Sert &iler Dala Topls Ouline ralye Tereas fi1 D14 L C D E G S A F H. J K M N R U V 1 Route 4 2. Route 1 Route 2 Route 3 Company 1 Company X 3 2500 3000 1500 4 4000 4000 5 Curmpary 3 3000 2000 Company 4 4000 5000 7 We will setup table to solve using Excel -Solver 10 Below table shows company aligned to each route, only binary values (0,1) highlighted cells only 11 Route 1 Houte a 12 Route 3 Route 4 13 Company 1 Company 2 Curmpary 3 1 0 0 1 SUMIC13:E13) SUMID14,F14) Each company can be assigned only one route 14 0 1 0 0 1 1 SUMIC15,15) 15 0 1 0 1 1 16 SUME16:F16) Company 4 0 o 0 1 1 1 17 1 1 1 SUMIC13:C16) 18 SUME13:E16) 19 = 20 1 1 1 1 21 22 Objective function 23 Objoctive is to minimize total cost 24 13500 SUMPRODUCTICa:r6,C13:F16) 25 MIN BID COST 26 27 28 29 30. Sheet1 - Resdy 100% e e 208 AM O Type here to search г. x 9/28/2019

So, Company 1 - Route 1, Company 2 - Route 2, Company 3- Route 3, Company 4 - Route 4 are assigned for optimal solution.

b.

Each company can be assigned at most routes. Revised solution is below

Bock7 - Excel Data Reviw View Tell me what you want to do.. Punoet Sabharwal AShare File Home Insert Page Layout Formulas 3ConnectionS Clear Pata AnalysiS 2, Solver Show Queries A! TReapply Filter Fram Table From Frum From Frum Ouher Text Sources RefteshProperties Texl Lo lashs Advanced Columns Fill Duplicates Validation Whal-If Furecast Gruup Ungroup Sublolal Analysis Sheet Existing New Reltesh Sort Remove Dala Consolidate Relationships Connections Query Recent Sources Get & Transorm All dit Links Access Web Cel Falerral Dela Connections Sert & iler Dala Topls Ouline Aralye Tereas J21 J C D G O T A E F H K M N Q V 1 2 Route 1 Route 2 Route 3 Route 4 Company 1 3 2500 3000 1500 4 Company 4000 4000 3000 5 Curmpary 3 2000 Company 4 4000 5000 7 We will setup table to solve using Excel -Solver 10 11 Below table shows company aligned to each route, only binary values (0,1) -highlighted cells only Houte 1 Houte 2 12 Route 4 Route 3 13 Company 1 Company 2 0 SUMIC13:E13 2 Each company can be assigned at most routes 14 C SUMID14,F14) 15 Curmpary 3 SUMIC15,E15 C 2 16 SUME16F16) Company 4 17 18 C13i C15 D13:014 E131E15IE16 F14 F16 19 20 1 1 1 21 22 Objective function 23 Objoctive is to minimize total cost 24 C3*C13+D3D13+E3*E13+D4*D1 4+F4 *F1 4+C15*C5+E5 E15+ E6*E16+F6*F16 0 25 MIN BID COST 26 27 28 29 30. Sheet - Resdy + 100% 216 AM e O Type here to search 2 S г. 4x 9/28/2019

Bock7 - Excel Data Reviw Tell me what you want to do. ile Home Insert Page Layout Formulas View Punegt Sabharwal Shate Clear Reapply Puata Analysis Show Queries Connections 2, Solver From Table Properties From From From Frum Ouher Access Web lext Sources Reltesh All Edit Links Text lo Flash Remove Atdvanced Columns Fill Duplicates Validation Consolidale Relationships Whal-If Furecast Gruup Ungroup Sabtotal Analysis Sheet Existing Connections Query Recent Sources New 7Sont Filter Dala Sert & lile Get & Iransarm Cel Falerral Dela Connections Dala Tools Ouine Aralyze ereas fi E25 C D G A E F H U V Solver Parameters X 1 2. Houte 1 Route 2 Route 3 Route 4 Set Objective SES23 Company 1 Company 2 Curmpary 3 3 2500 3000 1500 4 4000 4000 To: O Mac Mn Ovalue Of 5 3000 2000 By Changng Varisble Cole: Company 4 4000 5000 7 SCS13:5FS1 G Sbject to the Constrants We will setup table to solve using Excel -Solver scS13:SFS16 binary sC$17:SFS17 SC$20:SFS20 SGS13SGS16 sSS15510 Add 10 11 Below table shows company aligned to each route, only binary values (0,1) -highlighted cells only henge Houte 1 Houte 2 12 Route 4 Route 3 Eac 13 Company 1 0 SUMIC13:E13 2 Relete Company 2 14 C SUMD14,F14) SUMIC15,E15 15 Curmpary 3 C Raset Al 16 SUME16F16) Company 4 LosdSa 17 Mabe Unccnstrained Variables Non-Negative 18 C13C15 D13 014 E131E15 E16 F14F16 19 Sglect a Sovn9 GRG Noninear Ophens 20 1 1 1 Methoc 21 Solving Methed Select the GRG Nunineer engine for Screr Froblems thst are smooth nanlinear. Seledt the LP Simplex engine for inear Solver Problems, and select the Evolutionary engine for Solver problems that are 22 Objective function 23 Objoctive is to minimize total cost 24 non-smcoth c3*c13+D*D13+E3*E13+D4*D14+F4*F1 4+C15 *C5+E5 E15 + E6 * E1 25 MIN BID COST 26 Help Sche Cicse 27 28 29 30 Sheet1 Point 100% 216 AM e Type here to search х‑ 2 г. 4x 9/28/2019

Bock7 - Excel Punoet Sabharwal AShare Data Reviw Tell me what you want to do... File Home Insert Page Layout Formulas View 3ConnectionS Clear Pata AnalysiS 2, Solver Show Queries A TReapply Filter Fram Table From Frum From Frum Ouher Text Sources RefteshPropertins Texl lo lashs Advanced Columns Fill Duplicates Validation Whal-If Furecast Gruup Ungroup Sublolal Analysis Sheet Existing New Reltesh Sort Remove Dala Consolidate Relationships Connections Query Recent Sources Get & Transorm All dit Links Access Web Cel Falerral Dela Connelions Sert & iler Dala Topls Ouline Aralye Tereas 110 C D G O T A E F H L M N Q V 1 2. Route 1 Route 2 Route 3 Route 4 Company 1 Company X Curmpary 3 3 2500 3000 1500 4 4000 4000 5 3000 2000 Company 4 4000 5000 7 We will setup table to solve using Excel -Solver 10 11 Below table shows company aligned to each route, only binary values (0,1) -highlighted cells only 12 Houte 1 Route 4 Route 2 Route 3 Each company can be assigned at most routes 13 Company 1 Company 2 Curnpary 3 Company 4 1 1 2 SUMIC13:E13 2 14 0 0 1 1 SUMD14,F14) 15 SUMIC15,E15 1 C 0 1 2 16 SUMIE16:F16) 0 0 1 17 1 1 1 18 C13C15 D13 014 E131E15 E16 F14F16 19 20 1 1 1 21 22 Objective function Objoctive is to minimize total cost 23 24 25 MIN BID COST 11500 C3C13+D3*D13+E3*E13+D4*D14+F4*F1 4+C15 *C5+ES E15 +E6 * E 16+r6*F16 26 27 28 29 30. Sheet1 Resdy + 100% He e 216 AM O Type here to search 2 S 4x 9/28/2019

Hence as per optimal solution, Company 1 should be Route 2 and Route 3. Company 3 should be assigned route 1. Company 2 should be assigned route 4.

If you liked my answer, please give thumbs up/ likes. Thank you!