Question

please solve the excel solver and please SHARE excel file or pdf

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: AM Variable Number of waste totam *Number of starting com Number of sing alom

excel file

share inside excel file

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