To: Customer $1$ Customer $2$ Customer $3$ Customer $4$ Customer $5$ Customer $6$ Total Factory Capacity

From

Factory A $505045294420721$

Factory B $292647453723620$

Factory C $443150262830450$

Factory D $462149254140587$

Factory E $484743403930690$

Total Demand $560580459700668$

ABC Company wishes to supply its six customers $(1$ to $6)$ from its five factories $($A to E$)$ as shown above.

The relevant transportation cost per unit for shipments from factories to customers is give in the cost table above $($in yellow$)$

eg If you assign $1$ unit demand from Customer $1$ to Factory A $1,$ the cost is $$50$

If you assign $1$ unit demand from Customer $1$ to Factory B the cost is $$29.$ And so on$..$

The capacity of the $5$ factories and total demand from the $6$ customers is give in column I and row $11$ above.

eg Customer $1$ demand is $560,$ Factory A capacity is $721$ units and so on$.$

Q$1$ What is the optimal assignment of demand to the $5$ factories, to minimize transportation costs and also maximize quantity assigned given the capacity constraints?

Determine total quantity assigned and the corresponding total transportation costs.

Q$2$ Run a sensitivity analysis to quickly detemine the changes in the optimal total costs and total quantity assigned if Factory $1$ increases it capacity by $100$ units from current $721$ to $821$ units, demand data stays same the same as above table.

Note you can not resolve Q$1$ using new capacity numbers, buy only use the Sensitivity Analysis report obtained from Q$1$ answer.

Solve the above $2$ questions using the Excel $($ and Solver$)$ capability provided in Microsoft Excel.

Upload your excel work sheet$($s$),$ including the Sensitivity Analysis Solver output on to the Course Canvas.