Show me the steps to solve Question $2(58$ marks$)$

During an emergency period lasting for months, a frequently used medical item has been urgently demanded by many healthcare organizations. However, suppliers of this medical item are limited in their daily production capacity that healthcare organizations often experience shortages. One of the strategies implemented by a consulting firm is to set up an online platform to match suppliers to requesting healthcare organizations, accounting for request urgency, request quantity, suppliers' production capacity and geographic location. The aim is to produce matching in a fair and automated manner which could save time for both suppliers and requesters. This automated matching process could be run multiple times every week. If any requesting organization has unfulfilled demand in one run, it could be carried over to the next matching day. The relevant information of a list of $8$ suppliers and $13$ requesting organizations who have registered with this platform are given in Table $1$ and Table $2,$ respectively. The types of medical item offered and requested are compatible. The prices offered and requested are within acceptable range that all possible pairs of suppliers and requesters can be considered.

Table $1.$ Relevant information of suppliers $($in data file$)$

Table $2.$ Relevant information of requesting organizations $($in data file$)$

The urgency level $($Table $2)$ is ranked on a scale from $1$ to $3.$ For example, hospitals treating critical patients are ranked level $3,$ outpatient clinics are level $2$ and district community centres are level $1.$ The matching should first consider satisfying as much as demand as possible. If there were shortages, requesters should be prioritized using their urgency levels as priority weights. Question $2($continued$)$

The subsequent concern is to send shipments to the requesting organizations as soon as possible due to urgent frequent needs of the medical item. In general, a cost parameter $\backslash ($ c$\_\{$i j$\}\backslash )$ representing the unit cost $($e$.$g$.,$ distance, time, monetary value$)$ of shipping from supplier $\backslash ($ i $\backslash )$ to requesting organization $\backslash ($ j $\backslash )$ helps quantify the preference on suppliers with smaller cost. Table $3($in data file$)$ contains the cost parameters $\backslash ($ c$\_\{$i j$\},$ i$=1,2,\backslash$ldots$,8,$ j$=1,2,\backslash$ldots$,13\backslash ).$

$($a$)$ Formulate the problem by a linear program to determine an optimal matching between suppliers and requesting organizations that would minimize a composite function of the following two objectives:

$-$ Total demand shortages over all requesters, weighted by their urgency level

$-$ Total shipment cost, weighted by a penalty term $\backslash (\backslash$delta$=0\mathrm{.}00001\backslash )$ or $\backslash (10^\{-5\}\backslash )($indicating the lower priority of this objective as compared with the first objective$)$

$(14$ marks$)$

$($b$)$ Develop an Excel spreadsheet model for part $($a$)$ and use Solver to compute an optimal matching. $($Make sure model is executable with the same results as reported in part $($c$).)$

$(16$ marks$)$

$($c$)$ Report the following results obtained in part $($b$)$:

$($i$)$ The shipments between suppliers and requesting organizations

$(6$ marks$)$

$($ii$)$ Total shortages and total shipment cost

$(2$ marks$)$

$($iii$)$ Utilization of suppliers and shortages of requesting organizations

$(2$ marks$)$

When there are numerous data, results can be presented in a properly labelled table.

On examining the first set of optimal results, large suppliers $2,3$ and $8$ express their unwillingness to accept orders of small quantities. Suppose the minimum shipment quantity specified by suppliers $2,3$ and $8$ are $35,30$ and $30$ units, respectively.

$($d$)$ To account for the minimum order quantities from suppliers $2,3$ and $8$ :

$($i$)$ Modify the linear program in part $($a$)$ to ONE mixed integer linear program to determine the revised optimal matching. $($You may simply state the changes made.$)$

$(8$ marks$)$

$($ii$)$ Modify the Excel spreadsheet model in part $($b$)$ to incorporate changes made in part $($d$)($i$).$ Use Solver to compute an optimal matching. Report your results $($as in part $($c$))$ clearly on the spreadsheet. $($Make sure model is executable.$)$

$(10$ marks$)$

Table $1.$ Relevant information of suppliers

Table $2.$ Relevant information of requesting organizations

Table $3.$ Unit shipping cost $\backslash ($ c$\_\{$i j$\}\backslash )$ from supplier $($row$)$ to requesting organization $($column$)$