Question

A small firm makes three products, which all follow the same three-step process consisting of milling, inspection, and drilling.

Product A requires 5 minutes of milling, 5 minutes of inspection, and 4 minutes of drilling; Product B requires 3 minutes of milling, 4 minutes of inspection, and 5 minutes of drilling; and Product C requires 5 minutes of milling, 4 minutes of inspection, and 8 minutes of drilling.

The department has 200 hours available during the next period for milling, 150 hours for inspection, and 240 hours for drilling. The milling process costs $60 per hour, the inspection costs $30 per hour and finally drilling costs $90 per hour. The costs are the same for all product types. Product A is sold for $32.00 per unit, product B is sold for $25.00 per unit, and product C is sold for $32.00 per unit. There is an outstanding order of 800 Product A that the company has to satisfy. You are trying to determine the best production mix that maximizes your profit.

a) Formulate the problem as a linear programming model and solve it in Excel. Briefly describe your solution.

b) Is it feasible to produce 1200 units of Product A, 500 units of Product B, and 600 units of Product C? Create a sensitivity report and answer the following questions based on the sensitivity report.

c) What would happen to the optimal values of A, B, and C if the profit per unit of Product A is decreased by $5.00?

d) If the available amount of inspection hours is decreased by 1500 minutes, what would happen to the optimal value of the objective function?

Answer 1

a) Let, xi = number of units of product i to be produced where i = {A=1,B=2,C=3}

per unit profit of product A = 32-60*(5/60)-30*(5/60)-90*(4/60) = 18.5
per unit profit of product B = 25-60*(3/60)-30*(4/60)-90*(5/60) = 12.5
per unit profit of product C = 32-60*(5/60)-30*(4/60)-90*(8/60) = 13

Objective is to maximize profit => Z = max 18.5x1+12.5x2+13x3

subject to,

5x1+3x2+5x3 <= 12000 (Milling)
5x1+4x2+4x3 <= 9000 (Inspection)
4x1+5x2+8x3 <= 14400 (Drilling)
x1 >= 800 (Outstanding order - product A)

xi >= 0

Solving in solver we get,

number of units of product 1 =1800,product 2 =0,product 3 =0

maximized profit = 33300

Solver screenshot

Solver formula

Solver window

Sensitivity report

b) No it is not feasible as there is not enough inspection time availble. Total inspection time required to produce 1200 units of Product A, 500 units of Product B, and 600 units of Product C = 1200*5+500*4+600*4 = 10400 minutes but we have only 9000 minutes (150 hours) of inspection time

c)

Optimal solution will change as reduction of per unit profit of A by $5 is beyond the allowable decrease limit of $2

d)
Inspection hours constraint has shadow price of 3.7 which is valid for decrease of 5000 minutes. So, 1500 minutes reduction will reduce optimal profit by 3.7*1500 = 5550