Question

Help!! - I need excel with formula shown

1) Sungram, an active lifestyle company, manufactures three models of novel fitness trackers called Jump (J), Run (R), and Walk (W). They have a limited supply of common parts---wifi module (450 in inventory), cellular module (250 in inventory), heart rate monitor (800 in inventory), GPS module (450 in inventory), LCD screen (600 in inventory)---that these products use. A Jump model requires a wifi module, 2 heart rate monitors, a GPS module, and 2 LCD screens. A Run model requires a wifi module, a cellular module, 2 heart rate monitors, a GPS, and an LCD screen. A Walk model requires a heart rate monitor and an LCD screen. The profit on the Jump model is $65, the profit on the Run model is $75, and the profit on the Walk Model is $25. The following is a linear programming formulation of the problem.

Let J = Number of Jump models produced R = Number of Run models produced W = Number of Walk models produced We may write a model for this problem as follows. Maximize 65J + 75R + 25W subject to: (wifi module constraint) J + R ≤ 450 (cellular module constraint) R ≤ 250 (heart rate monitor constraint) 2J + 2R + W ≤ 800 (GPS module constraint) J + R ≤ 450 (LCD screen constraint) 2J + R + W ≤ 600 (non-negativity) J, R, W ≥ 0. Implement the above model in Solver (make sure to choose Simplex as the solving method and to choose the option “Make Unconstrained Variables non-negative”---do not explicitly put in the non-negativity constraints in the model) and using the sensitivity report only (do not resolve the problem and explain your calculation using the sensitivity report) answer the following questions.

Does the solution change if only 415 wifi modules are available?

Is it profitable to produce the Walk model? If not, by how much should the profit margin on the Walk model be increased to make it profitable to produce the Walk model?

Because of a change in production technology the profit margin on the Jump model has increased to $70. Should the production plan of Sungram change? What is their new profit?

100 heart rate monitors were found to be defective, making the number of available heart rate monitors 700. What will the profit be in this situation?

Another supplier is willing to sell cellular modules to Sungram. However, their prices for a cellular module are $8 higher than what Sungram pays its regular supplier. Should Sungram go ahead and purchase these cellular modules? If yes, at most how many units should they purchase?

Sungram is considering introducing a new fitness tracker model called the RunLite. This product uses a wifi module, a cellular module, a heart rate monitor, and an LCD screen, and is expected to make a profit of $50. Should Sungram produce the RunLite? Why or Why not?

Make sure to explain how you used the sensitivity report to figure out your answer. Please also attach the solver model and sensitivity report you used for this question.

Answer 1

The solver model is shown below

2 Dec Var 0 0 3 Coeff 65 4 Obj func 5 Wifi 0 仁450 6 Cellular 250 7 HR monitor 800 <= 8 GPS <450 9 LCD 0 600 10 12 13 18 19 Sheet1159Sheet1160 Sheet1162 Sheet1163 Sensitivity Report 75 Sheet1164 囲回凹 14596

The solver formula is shown below

2 Dec Var 0 0 SUMPRODUCT(SB$2:$D$2,B3: D3) 3 Coeff 25 65 75 4 Obj func -SUMPRODUCT($B$2:$D$2,B5:D5) 5 Wifi 450 <= 6 Cellular -SUMPRODUCT(SB$2:$D$2,BG:D6) <= 250 SUMPRODUCT($B$2:$D$2,B7D7) 仁 800 SUMPRODUCT(SB$2:$D$2,B8:D8)<450 7 HR monitor 8 GPS -SUMPRODUCT($B$2:$D$2,B9:D9) 600 9 LCD <= 10 12 13 15 18 19 ...Sheetl159 Sheet1160 Sheet162 Sheet1163 Sensitivity Report 75 Sheet1164 囲回凹 145%

The solver parameters are shown below

ЕЗ Solver Parameters Set Objective: 2 Dec Var 0 0 Max Min alue Of: 3 Coeff 65 75 25 By Changing Variable Cells: 4 Obj func SBS2:SDS2 5 Wifi 0 450 Subject to the Constraints: 250 6 Cellular SESS SES9SGS5:$GS9 7 HR monitor 800 <= 8 GPS <450 9 LCD 0 K600 10 Beset All Load/Save 12 Make Unconstrained Variables Non-Negative 13 Select a Solving Method: Simplex LP Ogtions Solving Method 15 Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solrer problems that are non-smooth 18 19 Sheet1160Sheet1162 Sheet1163 Sensitivity Report 75 Sensitivity Report 76 Sheet1164

The result is shown below

2 Dec Var 200 250 100 3 Coeff 65 75 25 29250 Solver Results Obj func 4 450 5 Wifi 450 Solver found a solution. All constraints and optimality conditions are satisfied 6 Cellular 250 250 <= 7 HR monitor O seep Solver Solution 仁800 800 Sensitivity Limits 450 8 GPS <= 450 ORestore Original Values 9 LCD 550 600 <= Return to Solver Parameters Dialog outline Reports 10 Save Scenario 12 Solver found a solution. All Constraints and optimality conditions are 13 When the GRG engine is used, Solver has found at least a local optimal solution. When Simplex LP is used, this means Solver has found a global optimal solution. 15 16 18 ...Sheet1159 Sheet1160 Sheet1162 Sheet1163 Sensitivity Report 75 Sheet1164 囲回凹 + 14596

The sensitivity analysis is shown below

Xx Microsoft Excel 15.0 Sensitivity Report A1 A B 6 Variable Cells Final Reduced Objective Allowable Allowable Name Value Cost Coefficient Increase Decrease Cell 9 $B$2 Dec Var J 10 15 200 65 10 $C$2 Dec Var R 11 $D$2 Dec Var W 1E+30 10 250 75 7.5 25 100 25 12 13 Constraints Final Shadow Constraint Allowable Allowable 14 15 Cell Value Price R.H. Side Increase Decrease Name 16 SE$5 Wifi Obj func 17 SE$6 Cellular Obj func 450 15 1E+30 450 0 10 50 250 1E+30 250 18 SE$7 HR monitor Obj func 800 25 800 1E+30 ŞEŞ8 GPS Obj func 1E+30 19 450 450 0 $E$9 LCD Obj func 1E+30 50 20 550 600 21 ...Sheet1160 Sheet1162 Sheet1163 Sensitivity Report 75 Sensitivity Report 76 Sheet1164 + 145%

Does the solution change if only 415 wifi modules are available?

Yes. The shadow price of the constraint is non-zero. This means that the solution will change if the constraint’s RHS is changed.

Is it profitable to produce the Walk model? If not, by how much should the profit margin on the Walk model be increased to make it profitable to produce the Walk model?

It is not profitable to product W model. That is why the value of objective coefficient for W is in negative. However, we can increase the profit by more than 25 and then the model may become profitable.

Because of a change in production technology the profit margin on the Jump model has increased to $70. Should the production plan of Sungram change? What is their new profit?

The production plan of Sungram change. However, considering that the overall reduced cost of the coefficients are 0, there will be no change in overall new profit.

100 heart rate monitors were found to be defective, making the number of available heart rate monitors 700. What will the profit be in this situation?

The heart rate monitor’s shadow price is 25. This means if the constrain is reduced by 100, the overall profit will reduce by 100*25 = 2500.