Linear Programming_Solver_Statistics

1) Sungrum Problem.

a) No. Currently, the optimal solution uses 400 wifi modules when the number of wifi modules available is 450. Thus reducing the number of available wifi modules to 415 will not change the solution.

b) It is not profitable to produce the Walk model. The profit margin for this product should increase by $7.5 to make it profitable to produce the Walk model (the reduced cost).

c) The current coefficient on the Jump model is $65. The sensitivity output indicates the optimal solution remains the same for an increase of $10 and a decrease of $15. Since the new coefficient on the Jump model is an increase of $5, the optimal solution remains the same (i.e., the production plan remains the same). The increase in profit is $5*150=$750.

There for the new profit is $28,500 + $750 = $29,250.

d) The shadow price on the heart rate monitor constraint is $32.5, with an allowable decrease of 300. Since the reduction is 100 units which is less than 300, we can calculate the reduction in profit as $32.5*100 = $3,250. Hence, the new profit is $28,500 - $3,250 =

$25,250.

e) The shadow price on Cellular modules is $10. Hence, having one more Cellular module will increase the objective by $10. Since the additional Cellular module will cost us $8 extra, this will be a net of $10 - $8 = $2 increase in profit for each extra Cellular module. So yes, Sungrum should go ahead and purchase these Cellular modules. The allowable increase is 150; therefore, they can buy up to 150 additional Cellular modules.

f) The reduced cost of the RunLite is obtained by taking its profit and subtracting out the marginal cost of the resources used for its production. So, the reduced cost is $50 – (1*$0+1*$10+1*$32.5+0*$0+1*$0) = $7.5. Since this reduced cost is positive, it means that the objective value will increase if we produce a unit of the RunLite fitness tracker. Thus, Sungrum should produce the RunLite. To determine the new optimal production plan Sungrum needs to introduce the RunLite fitness tracker as a variable in the model and resolve the problem.

Microsoft Excel 16.0 Sensitivity Report

Worksheet: [hw2soln.xlsx]Sungram Problem

Report Created: 5/18/2019 10:34:28 PM

Variable Cells

Constraints

2) Braxton Oil Company. It’s probably easier to follow the model in Solver (for completeness an algebraic model is attached).

Decision variables

C1R, C2R, C3R, C4R = amount of crude stock 1, 2, 3, and 4 in Regular Brand.

C1M, C2M, C3M, C4M = amount of crude stock 1, 2, 3, and 4 in Multigrade Brand. C1S, C2S, C3S, C4S = amount of crude stock 1, 2, 3, and 4 in Supreme Brand

C1E, C2E, C3E, C4E = amount of crude stock 1, 2, 3, and 4 in Extreme Brand.

Objective:

Maximize Profit = 8.5(C1R+C2R+C3R+C4R)+9(C1M+C2M+C3M+C4M)+10(C1S+C2S+C3S+C4S)+

10.5(C1E+C2E+C3E+C4E) – 7.1(C1R+C1M+C1S+C1E) – 8.5(C2R+C2M+C2S+C2E) –

7.7(C3R+C3M+C3S+C3E) – 9(C4R+C4M+C4S+C4E)

Constraints:

Blending constraints (before linearizing, make sure to linearize):

(20C1R+40C2R+30C3R+55C4R)/(C1R+C2R+C3R+C4R) >= 25

(20C1M+40C2M+30C3M+55C4M)/ C1M+C2M+C3M+C4M) >= 32

(20C1S+40C2S+30C3S+55C4S)/(C1S+C2S+C3S+C4S) >= 42

(20C1E+40C2E+30C3E+55C4E)/(C1E+C2E+C3E+C4E) >= 50

Blending constraints (same as above after linearizing):

-5C1R+15C2R+5C3R+30C4R >= 0

-12C1M+8C2M-2C3M+23C4M >= 0

-22C1S-2C2S-12C3S+13C4S >= 0

-30C1E-10C2E-20C3E+5C4E >= 0

Supply/availability of crude stock

C1R+C1M+C1S+C1E <= 1100 supply of crude stock 1

C2R+C2M+C2S+C2E <= 1100 supply of crude stock 2 C3R+C3M+C3S+C3E <= 1100 supply of crude stock 3 C3R+C3M+C3S+C4E <= 1100 supply of crude stock 4

Demand constraints

C1R+C2R+C3R+C4R <= 1500

C1M+C2M+C3M+C4M <= 1000

C1S+C2S+C3S+C4S <= 1000

C1E+C2E+C3E+C4E <= 750

All variables are non-negative.

Please See Solver model for each part in the file hw2soln.xlsx.

Part a model: Braxton(a).

Part b model: Braxton(b), demand constraints changed to equal to. Part c model: Braxton(c), demand constraints changed to >=.

a) There is more than one optimal solution. The optimal profit is $5482.50 and the blends are made as follows.

b) The demand constraints are all changed to equal to. The optimal profit is $5445 and the blends are made as follows.

c) The demand constraints are all changed to greater than or equal to. The optimal profit is $5670 and the blends are made as follows.