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...
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.