PLEEEASE INCLUDE SCREENSHOTS FROM EXCEL WITH THE FORMULAS SHOWING SO I CAN SEE WHAT YOU DID THANK YOU

Question 1 (25 pts)

GreatFit, 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---Wi-Fi 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 Wi-Fi module, 2 heart rate monitors, a GPS module, and 2 LCD screens. A Run model requires a Wi-Fi 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:

(Wi-Fi 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. Your submission to this question should be a word or pdf file with all of the answers for the parts a) through f). Make sure to explain how you used the sensitivity report to figure out your answer. Also submit your Solver model.

a) (3 pts) Does the solution change if only 415 Wi-Fi modules are available?

b) (3 pts) 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?

c) (3 pts) Because of a change in production technology the profit margin on the Jump model has increased to $70. Should the production plan of GreatFit change? What is their new profit?

d) (3 pts) 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?

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

f) (5 pts) GreatFit is considering introducing a new fitness tracker model called the RunLite. This product uses a Wi-Fi module, a cellular module, a heart rate monitor, and an LCD screen, and is expected to make a profit of $50. Should GreatFit produce the RunLite? Why or Why not?

g) (5 pts) Copy and paste the solver model with formulas and the sensitivity report you used for this question.

Question 2 (25 pts) A fertilizer company blends silicon and nitrogen to produce two types of fertilizers. Fertilizer 1 must be at least 40% nitrogen and sells for $70 per pound. Fertilizer 2 must be at least 70% silicon and sells for $40 per pound. The company can purchase up to 8000 pounds of nitrogen at $15 per pound and up to 10,000 pounds of silicon at $10 per pound. Assuming that all fertilizer can be sold, the company wishes to maximize its profit.

a) (2 pts) What are the decision variables in this problem?

b) (2 pts) State the objective (as a function of the inputs specified in the problem and the decision variables defined in (a)).

c) (4 pts) What are the constraints in this problem? Write an algebraic expression for each.

d) (8 pts) Solve the problem using Excel solver.

Please submit your Solver model. Copy and paste your solve model with formulas and the sensitivity analysis report.

What is the maximum profit?

What mix of fertilizers should be made to achieve this profit (interpret the optimal solution)?

e) (3 pts) Suppose that the availability of nitrogen and silicon both increase by the same percentage from their current value. How will the optimal value change? Explain your answer using the results from part (d).

f) (3 pts) Use SolverTable to explore the effect on profit of changing the minimum percentage of nitrogen required in fertilizer 1.

g) (3 pts) Use Solver Sensitivity Report to explore the effect on profit of changing the availability of nitrogen.