Question

How do I set this problem up in excel?

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.

Answer 1

In Excel, setup the linear programming problem in Excel as follows:

D17 3 Constraints: -B1+D1 450 4 -D1 250 =2* B1+2*D1+F1 -2"в 1+D1+F1 600 -65*B1+75 D1+25* F1 8 Maximise 9 10 12 13 14 15

Go to Data->Solver and do the following to run it:

Solver Parameters Set Objective SBS8 Value Of o Max Min By Changing aiable Cells: SBS1,SDS1,SFS1 Subject to the Constraints: Add Change Delete Reset AlI Load/Save Make Unconstrained Variables Non-Negative Select a Solving Method: Simplex LP Options Solving Method 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 Solver problems that are non-smooth. Help Solve Close

We have to maximise B8 cell and the solving method is Simplex LP. Click on Solve after adding all the constraints.

The solution comes out to be:

J = Jump = 150

R = Run = 250

W = Walk = 0

Maximum Profit = 28500