Answer:
Check the explanation
Explanation:
a) Linear program model:
Decision variables: Let
P1 = Number of PT-100 products produced at Philippines plant
P2 = Number of PT-200 products produced at Philippines plant
P1 = Number of PT-300 products produced at Philippines plant
M1 = Number of PT-100 products produced at Mexico plant
M2 = Number of PT-200 products produced at Mexico plant
M3 = Number of PT-300 products produced at Mexico plant
Objective: Min (0.95+0.15)P1 + (0.98+0.15)P2 + (1.34+0.15)P3 + (0.98+0.08)M1 + (1.06+0.08)M2 + (1.15+0.08)M3
or,
Min 1.10P1 + 1.13P2 + 1.49P3 + 1.06M1 + 1.14M2 + 1.23M3
s.t.
P1 + M1 ≥ 200,000
P2 + M2 ≥ 100,000
P3 + M3 ≥ 150,000
P1 + P2 ≤ 175,000
M1 + M2 ≤ 160,000
P3 ≤ 75,000
M3 ≤ 100,000
P1, P2, P3, M1, M2, M3 ≥ 0
(b) Solution of the linear program using Excel Solver can be seen in the first attached image below.
Formula: H2 =SUMPRODUCT(B2:G2,$B$11:$G$11) copy to H2:H9
Optimal Solution:
Decision Variable Value
P1 40000
P2 100000
P3 50000
M1 160000
M2 0
M3 100000
Total production and shipping cost = $ 524,100
Sensitivity report can be seen in the second attached image below.
Referring to above sensitivity analysis,
(c) Allowable decrease in objective coefficient of P1 is 0.04 therefore production and/or shipping cost per unit has to decrease by $ 0.04 to produce additional units of PT-100 in Philippines plant.
(d) Allowable decrease in objective coefficient of M2 is 0.05 therefore production and/or shipping cost per unit have to be decreased by $ 0.05 to produce additional units of PT-200 in Mexico plant.
