Assignment $2$

Please save your files as Asm$2-$XXXXXXXX$.$xlsx$/.$pdf$/.$docx as appropriate, where $xxxxxxxx$

is your student id number.

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.

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 arc $ij$ 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.

$($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 how 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 unchanged, how much does the movement cost $($per vehicle$)$ on this route need 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$)$

$($f$)$ The logistics company could consider an option of rentiA 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.

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 arc i $->$ j in the network:

xij $=$ number of vehicles to move along route from location i to 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.

$($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 how 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 unchanged, how much does the movement cost $($per vehicle$)$ on this route need 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$)$

$($f$)$ The logistics company could consider an option of renting a vehicle to fulfill the requirement at location $1,2$ or $6$ if it could achieve cost saving. To save as much cost as possible, which of these locations should be offered a rented vehicle, and up to what rental cost should the company consider this option? Explain. $(5$ marks$)$

$($g$)$ Suppose the fuel cost will increase simultaneously by the same percentage, denoted by p$,$ on every route. For example, if p $=10\%,$ the cost to move a vehicle from location $4$ to location $1$ will increase by $$2\mathrm{.}50(=$ $$25*10\%).$ What is the maximum value of p$,$ denoted by pmax, so that the redistribution plan in part $($b$)$ remains unchanged? Accordingly, determine the new total cost in terms of pmax. $(6$ marks$)$