inear Programing, Statistical Analysis and excel solver

Individual Assignment 2

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.

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

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

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

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

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. Please also attach the solver model and sensitivity report you used for this question.

2) Braxton oil company produces four brands of oil: regular, multigrade, supreme, and extreme. Each brand of oil is composed of one or more of four crude stocks, each having a different lubrication index. The relevant data concerning the crude stocks are as follows.

Crude Stock	Lubrication Index	Cost ($/Barrel)	Daily Supply (Barrels)
1	20	7.1	1100
2	40	8.5	1100
3	30	7.7	1100
4	55	9	1100

Each brand of oil must meet a standard for a lubrication index, and each brand thus sells at a different price. The relevant data concerning the four brands of oil are as follows.

Brand	Minimum Lubrication Index	Sales Price ($/Barrel)	Daily Demand (Barrels)
Regular	25	8.5	1500
Multigrade	32	9	1000
Supreme	42	10	1000
Extreme	50	10.5	750

The task is to determine an optimal output plan for a single day, assuming that production can be either sold or else stored at negligible cost. The daily demand figures are subject to alternative interpretations. Investigate the following.

(a) The daily demands represent potential sales. In other words, the model should contain demand ceilings (upper limits). What is the optimal profit?

(b) The daily demands are strict obligations. In other words, the model should contain demand constraints that are met precisely. What is the optimal profit?

(c) The daily demands represent minimum sales commitments, but all output can be sold. In other words, the model should permit the production to exceed the daily commitments. What is the optimal profit?

Your submission to this question should be a spreadsheet containing a Solver Model for each part. Please make sure to answer the questions explicitly in words. Your Solver Model needs to be clear so that someone who looks at it can follow what you are doing without going into Solver (use colors for decision variables, objective, constraints etc as in the examples). If necessary, explain the equations you are modeling mathematically. Make sure to also paste your Solver dialog box in the spreadsheet. Explain how you change the models between parts a, b, and c.