STEPS WITH EXCEL Assignment $3$

Question $1$

$1.$ Consider the following LP problem, in which X and Y denote the number of units of products X and Y to produce, respectively:

Maximize profit $=$ $$5$X$+$$$5$Y

Subject to the constraints

$2$X$+3$Y $<=60($resource $1)$

$4$X$+2$Y $<=80($resource $2)$

X $<=18($resource $3)$

X$,$ Y $>=0($nonnegativity$)$

The Excel Sensitivity Report for this problem is shown below.

Variable Cells

Cell Name Final Value Reduced Cost Objective Coefficient Allowable Increase Allowable Decrease

$B$$4$ Solution value X $15\mathrm{.}000\mathrm{.}005\mathrm{.}005\mathrm{.}001\mathrm{.}67$

$C$$4$ Solution value Y $10\mathrm{.}000\mathrm{.}005\mathrm{.}002\mathrm{.}502\mathrm{.}50$

Constraints

Cell Name Final Value Reduced Cost Objective Coefficient Allowable Increase Allowable Decrease

$D$$7$ Resource $160\mathrm{.}001\mathrm{.}2560\mathrm{.}0060\mathrm{.}0012\mathrm{.}00$

$D$$8$ Resource $280\mathrm{.}000\mathrm{.}6380\mathrm{.}008\mathrm{.}0040\mathrm{.}00$

$D$$9$ Resource $315\mathrm{.}000\mathrm{.}0018\mathrm{.}001$E$+303\mathrm{.}00$

Subquestions

Calculate the explain what happens to the optimal solution for each of the following situations. Each question is independent of the other questions.

a$)$ What is the optimal solution to this problem?

b$)$ For what ranges of values, holding all else constant, could each of the objective function coefficients be changed without changing the optimal solution?

c$)$ If we could obtain one additional unit of resource $1,$ how would it impact profit? Over what range of RHS values could we rely upon this value?

d$)$ If we were to give up one unit of resource $2,$ how would it impact profit? Over what range of RHS values could we rely upon this value?

e$)$ If we were to increase the profit for product X by $$4,$ how would the solution change? What would be the new solution values and the new profit?

f$)$ If we were to decrease the profit for product Y by $$2,$ how would the solution change? What would be the new solution values and the new profit?

g$)$ Suppose that two units of resource $3$ were found to be unusable, how would the solution change?

h$)$ If we were able to obtain five more units of resource $3,$ would you be interested in the deal? Why or why not?

i$)$ If the profit for product X was increased to $$7$ while at the same time the profit for product Y was reduced to $$4,$ what would be the new solution values and the new profit?

j$)$ Suppose you were to be offered $50$ units of resource $1$ at a premium of $$1$ each over the existing cost price for that resource. Would you purchase this? If so$,$ by how much would your profit increase?