Question

Suppose an optimization problem is trying to minimize the cost function : Cost = 7S + 10R.   The company is producing two products, product S and product R, subject to the following constraints:

                                S + R ≤ 90

                        3S + 10R ≥ 620

                        R ≤ 100

                        S ≥ 20

                        S, R ≥ 0

Program this into a blank Excel worksheet and use Solver to find the solution for S and R such that the objective function is minimized.

Can you please explain how to do that in excel ? I need the process . I don't really need the handwritten format .I just need to understns how to do that in excel .

Answer 1

Final Answer : S=20 , R=56

Part 1 : Constructing the sheet to specify the whole LP problem formulation

1. Create Two column headings : each for S and R , name them "S" and "R" respectively . (say we do this in cells : I3 and J3 )

2. In next row, under the column headings, leave blank cells, these are the two cells which will act as place holder for both variables S and R's varying values. (So these will be cells I4 and J4)

3. Then in next row, enter the coefficient of objective function i.e. 7 and 10 in our case. (Cells : I5 , and J5 ; I5 has "7" and J5 has "10" )

4. Now, in cell K5 , we enter the formula : = I4*I5 + J4*J5 ( This will specify the objective function , so if current values in I3 and J3 are zero then this value in K5 will be zero )

5. This step will list down all the constraints one after the other starting from Row 7 i.e. in Row 7, we will have following entries in order to encode the constraint "S+R<=90 "  :

I7 : 1 , J7 : 1 , L7 : =I4*I7 + J4*J7 , M7 : 90

Similarly, other constraints are encoded , so after doing these steps, our sheet will look like :

Part 2: Using Solver to solve the formulated LP in part 1 :

1. Open "Solver" tab under "DATA" ribbon

2. Set Objective : $K$5

By changing variables cells : $I$4:$J$4

3. Subject to constraints : Put appropriate sign <= , >= as needed in respective constraint,

for eg. to add S+R<=90 , choose "Add" button , then in Cell Reference, choose the cell in which the LHS of constrain was coded, so in our example this was cell K7, then choose <= and finally chooce the cell in which RHS of constrain was recorded. i.e. L7 in our case .

then after doing all these, choose : "Solve" , you will get the answers in cell I4, J4, and K5 .

Final Solver window before entering "Solve" button will look like :

Example windows for LP formulations :