Question

The following model has been formualted to describe the manufacture of two products X and Y. maximize 4X +5Y subject to: X +2Y ≤ 10 (labour in hours) 6X +6Y ≤ 36 (materials used in kg) 8X +4Y ≤ 40 (storage space in m3 ) X,Y ≥ 0 Using Excel to solve gives the following output and sensitivity report: State the final recommendation as fully as you can. 2 Calculate and explain what happens to the optimal solution in each of the following situations: (a) You acquire 2 additional kilos of material. (b) You acquire an additional 1.5 hours of labour. (c) You give up 1 hour of labour and get 1.5 kilos of material. (d) The profits from X and Y are changed to $4.75 each. (e) You introduce a new product that will sell for $2. Each unit of this item will use 1 hour of labour, 1 kilo of material and 2 m2 of space.

Answer 1

We solve the given LPP in Excel using Excel Solver and generate a Sensitivity Report as shown below:

The above solution in the form of formulas along with Excel Solver extract is shown below for better understanding and reference:

We have generated the Sensitivity Report as shown below:

As seen from above,

No. of Product X to be produced = 2 nos.

No. of Product Y to be produced = 4 nos.

Total Profit for this production plan = $28

We answer the other given questions as:

(a) As seen from the Sensitivity report, the Constraint for the material is given in Cell B10 under Constraints table. The shadow Price is 0.5. Hence, for every 1 kg increase in the material available, the Total Profit will increase by $0.5. Hence, for 2 kg increase in material, the Total Profit will increase by 2 * 0.5 = $1.

(b) As seen from the Sensitivity report, the Constraint for the Labor hours is given in Cell B9 under Constraints table. The shadow Price is 1. Hence, for every 1 hour increase in the material available, the Total Profit will increase by $1. Hence, for 1.5 hour increase in material, the Total Profit will increase by 1.5*1 = $1.5

(c) As seen from the Sensitivity report, the Constraint for the Labor hours is given in Cell B9 and the Constraint for the material is given in Cell B10 under Constraints table. The shadow Price is 1 for Lbor hour and 0.5 for material. Hence, for every 1 hour increase in the material available, the Total Profit will increase by $1 and vice-versa for decrease. Similarly, for labor. Hence, for 1 hour decrease in material, and 1.5kg increase in material, the Total Profit will change by 1*(-1)+1.5*1.5 = $1.25. Hence, the Total profit will increase by $1.25

(d) As seen from the Sensitivity report, the profit are the objective coefficients which are given under Variables table. When the Profit i.e. Objective Coefficient for X is changed to $4.75 i.e. 4.75 - 4 = 0.75 increase which is less than allowable increase, the solution remains same. Only the value of objective function changes. When the Profit i.e. Objective Coefficient for X is changed to $4.75 i.e. 5 - 4.75 = 0.25 increase which is less than allowable decrease, the solution remains same. Only the value of objective function changes. Change in Objective function = Change in X * No. of Optimal X + Change in Y * No. of Optimal Y = (0.75*2) + (-0.25*4) = 1.5-1 = $0.5 increase in total profit.

(e) By introducing a new product, we solve the LPP again as shown below:

As seen from above, there is no change in the solution, when we consider all no. of products as integers.

-----------------------------------------------------------------------------------------------------------------------

In case of any doubt, please ask through the comment section before Upvote/downvote.

If you liked the answer, please give an upvote. This will be quite encouraging for me, thank-you!!!