Question $1(32$ marks$)$

A logistics company maintains delivery vehicles at its six operational centres. The following network shows the locations of the operational centres as nodes and the route between two locations as arcs. In the network, a positive number near a node indicates an excess number of vehicles available for dispatch. Nodes with a negative number represent locations requiring delivery vehicles, and the requirement is the absolute value of the negative number. The cost to move a vehicle between two locations depends on the route distance and is indicated by the dollar value near the arc.

$($a$)$ Develop your own spreadsheet model which should be executable and solved. Generate the "Sensitivity Report" from your model. $($Use the above to verify your model results.$)(8$ marks$)$

Based on the optimal results and the sensitivity report in part $($a$),$ answer the following independent questions:

$($b$)$ What is the least expensive redistribution plan for the vehicles? Which location still has excess vehicle$($s$)$ and bow many? $(7$ marks$)$

$($c$)$ Is there any alternative redistribution plan with the same total cost? Explain. $(2$ marks$)$

$($d$)$ The route from location $1$ to location $5$ usually has less traffic. Holding all other factors remain unchangod, how mueh does the movement cost $($per vehiele$)$ on this route nood to be changed for it to be worthy for use? $(2$ marks$)$

$($e$)$ Suppose a vehicle at location $4$ needs to go for repair and maintenance, how would the minimum total cost in part $($b$)$ be affected? $(2$ marks$)$

The problem on redistributing the vehicles among the locations in the least expensive way in order to meet the requirement on vehicles can be formulated by a linear program. Decision variables are defined in the following for every arci$j$ in the network:

$x_{ij}=$ number $of$ vehicles $to$ move along route from location $ito$ location $j$

The linear program is developed into an Excel spreadsheet model and solved using Excel Solver. The optimal results are shown on the next page.