1 answer

Help!! - I need excel with formula shown 1) Sungram, an active lifestyle company, manufactures three models of novel fitness

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.


Answers

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 Sh

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

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 SB

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 constraint

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 Cos

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.

.

Similar Solved Questions

1 answer
4. (a) The density of a Poisson (-1) random variable is f1(y;) = t4e-1/ Use this...
4. (a) The density of a Poisson (-1) random variable is f1(y;) = t4e-1/ Use this density to find the MLE for and the MLE for u = 1-1. Hint: you might want to find the MLE for u first, and then apply the principle of invariance to find the MLE for 1. (b) Find the MLE for u = 1-1 using the original sa...
1 answer
SHORT ANSWER # 28: Draw a full mechanism for the following transformation Aldol condensation reaction and...
SHORT ANSWER # 28: Draw a full mechanism for the following transformation Aldol condensation reaction and predict the product. Show all the elementary step(s) being sure to include all arrows, important electron pairs and any formal charges if needed. (8 points) NaOH, HẠO heat Aldol condensati...
1 answer
The classic experiment that examined DNAse I sensitivity of chicken embryonic DNA from different tissues and...
The classic experiment that examined DNAse I sensitivity of chicken embryonic DNA from different tissues and at different developmental stages shows that: (Select all that apply.) A. the gene expression pattern changes during development. B. DNAse I sensitivity comes from sporadic mutations occurred...
1 answer
A chord of a circle is a line segment whose endpoints are on the circle. Find the length of the common chord of the two circles whose equations are #x^2 + y^2 =4# and #x^2 +y^2 -6x +2 = 0#?
A chord of a circle is a line segment whose endpoints are on the circle. Find the length of the common chord of the two circles whose equations are #x^2 + y^2 =4# and #x^2 +y^2 -6x +2 = 0#?...
1 answer
2. For each of the following situations, determine the amount of gross income that the taxpayers...
2. For each of the following situations, determine the amount of gross income that the taxpayers Explain why any amounts are excluded from gross income. a. Harvey is injured in an automobile accident. The driver of the other car is found to be judge awards Harvey $10,000 for pain and suffering, $20,...
1 answer
How does temperature affect cell membrane?
How does temperature affect cell membrane?...
1 answer
Write a java program to remove duplicate elements from an array. Do not use Java library...
Write a java program to remove duplicate elements from an array. Do not use Java library utilities or 3rd party tools to solve this problem. You need to use the “for loop” and manage the index as you traverse thru the array [i ] while looking for duplicates. For example: Example 1 ------...
1 answer
Company A sells $600 of merchandise on account to Company B with credit terms of 2/10,n/30....
Company A sells $600 of merchandise on account to Company B with credit terms of 2/10,n/30. If Company B remits a check taking advantage of the discount offered, what is the amount of Company B's check? $588 O $600 $540 $560...
5 answers
Write an essay comparing Baldwin's account of the Titanic with Rosenthal's account of his visit to Auschwitz
Write an essay comparing Baldwin's account of the Titanic with Rosenthal's account of his visit to Auschwitz.In your paragraph, explain how the tone and purpose of each essay is different. Support your argument with at least one example from each essay....
1 answer
The following questions concern experimental parameters for obtaining 13C spectra and how to expl...
The following questions concern experimental parameters for obtaining 13C spectra and how to explain unexpected results. The first spectrum is a standard broadband decoupled spectrum of camphor Collection parameters 45o tip angle, D16 s Low power broadband 1H irradiation during D1 to buildup NOE (30...
1 answer
Given that V. - 5 V in the network in the figure, find Vs. +-WSW 51...
Given that V. - 5 V in the network in the figure, find Vs. +-WSW 51 6ko + o Vs 4kn 10ka 5mA 9kn V. - 5 V...
1 answer
(1 point) If C is the curve given by r (t) = (1 + 4 sin...
(1 point) If C is the curve given by r (t) = (1 + 4 sin t)i + (1+2 sin’t)j + (1 + 3 sinºt) k, 0 <15 and F is the radial vector field F(x, y, z) = xi + yj + zk, compute the work done by F on a particle moving along C....
1 answer
11. Write out a step wise synthesis of the following compounds starting from acetylene. You may...
11. Write out a step wise synthesis of the following compounds starting from acetylene. You may use any other reagents necessary. a. b. HO...
1 answer
Question 3 0/1 pts You can buy 98 yen for a dollar today, and can enter...
Question 3 0/1 pts You can buy 98 yen for a dollar today, and can enter into a one year forward contract with a rate of 118 yen per dollar. A one year riskless zero coupon bond denominated in yen has a yield to maturity of 1%. What must the YTM be on a similar dollar-denominated bond to avoid an arb...
1 answer
Review | Constants Periodic Table MISSED THIS? Read Section 6.4 (Pages 222 - 225) A sample...
Review | Constants Periodic Table MISSED THIS? Read Section 6.4 (Pages 222 - 225) A sample of nitrogen gas in a 1.83-L container exerts a pressure of 1.50 atm at 19 °C Part A What is the pressure if the volume of the container is maintained constant and the temperature is raised to 326 °C? E...
1 answer
Place the correct words in each sentence to asses your understanding of the cyclic and noncyclic...
place the correct words in each sentence to asses your understanding of the cyclic and noncyclic electron pathways of photosynthesis z Chapter 8 G z Chapter 8 https/ Seved ATP synthase Two types of light reactions occur in called noncyclic, because electron flow can be traced from water to a mol...
1 answer
Part B Classification and total liabilities and total revenues ($ millions) 1 R&D Expenses 2 Wages...
Part B Classification and total liabilities and total revenues ($ millions) 1 R&D Expenses 2 Wages Payable 3 Depreciation and amortization expense 4 Pre-paid Rent 5 Retained earnings 6 Net income 7 Property, plant & equipment 8 Selling, general & admin expense 9 Total Assets 10 Total sto...
1 answer
The box in the figure has 3 holes with the same diameter 13 cm. Aliquid with...
The box in the figure has 3 holes with the same diameter 13 cm. Aliquid with density p=1073 enten the box through the central hole and exit from the upper and lower ones. The volume flow rate through the upper and lower holes has the same value Q -0.0014 m/s while the one the enters through the cent...
1 answer
QUESTION 1: Why must project manager should have good technical skills but also good management skills?...
QUESTION 1: Why must project manager should have good technical skills but also good management skills? QUESTION 2: **Communication and Communicator are related" This quote from the text suppose that the communication process is lead by the spokeperson. Do you think is it a gift" to be a goo...