Question

please solve excel solver and to.excel file

WORKFORCE PLANNING-BUS SCHEDULING MODEL Example 2.4.5 on page 68 of Taha's book Number of busses as a function of the time of the day: MOD AM * Number of art 1201 Xe Number of buses strihy at 4:01am *Number of buses starting at 8: lam Number of buses kurting at 12:01 : Number of buses starting at 4:01pm Number of buses starting at 1pm 33

Answer 1

Set up Excel model as follows:

H ㄴ с X2 E X4 F X5 1 X3 X6 Required 1 4 1 8 1 1 Available =SUMPRODUCT(B3:G3,$B$11:$G$11) =SUMPRODUCT(B4:G4,$B$11:$G$11) =SUMPRODUCT(B5:G5,$B$11:$G$11) =SUMPRODUCT(B6:46,$B$11:$G$11) =SUMPRODUCT(B7:47,$B$11:$G$11) =SUMPRODUCT(B8:G8,$B$11:$G$11) 10 7 1 1 1 1 A B X1 2 3 12AM - 4AM 1 4 4AM - 8AM 1 5 8AM - 12PM 6 12PM - 4PM 7 4PM - 8PM 8 8PM - 12AM 9 10 11 Result: 12 13 14 Total buses =SUM(B11:G11) 15 l2 1 1 4 16 17

Set up Solver Parameters as follows:

Solver Parameters X Set Objective: $B$14 1 To: Max Min Value Of: 0 By Changing Variable Cells: $B$11:$G$11 1 Subject to the Constraints: $H$3:$H$8 >= $J$3:$J$8 Add 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

Click Solve to generate the solution.

After that, values appear automatically in variable (yellow colored) cells

A H K L M N B X1 с X2 D X3 E X4 F. X5 G X6 1 1 1 1 1 1 1 Available 4 8 10 7 12 11 Required 4 8 10 7 12 4 1 1 1 1 1 > 2 3 12AM - 4AM 4 4AM - 8AM 5 8AM - 12PM 6 12PM - 4PM 7 4PM - 8PM 8 8PM - 12AM 9 10 11 Result: 12 13 14 Total buses 15 16 17 4 4 6 1 11 0 2 28 Solver Results X LON Solver found a solution. All Constraints and optimality conditions are satisfied. 00 19 Reports Answer Sensitivity Limits Keep Solver Solution Restore Original Values Return to Solver Parameters Dialog Outline Reports 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 OK Cancel Save Scenario... Solver found a solution. All constraints and optimality conditions are satisfied. 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.

Click OK

Optimal schedule of buses is :

Starting time	Number of buses
12:01 AM	4
4:01 AM	4
8:01 AM	6
12:01 PM	1
4:01 PM	11
8:01 PM	0

Total number of buses = 26