In this video, I’m going to solve this LP problem using Excel’s Solver add-in.
When you click the Data tab you should see the Solver addin on the right here if you
already installed it.
If it’s not there, you can install it by going to File > Options > Addins
Then at the bottom here, click Go.
Select the Solver add-in and click OK.
On a mac go to Tools > Excel Add-ins > Solver Add-in
When you now click on the Data tab, you should see the Solver command on the right here.
Now, I’m going to place X1 here, drag to the right for X2 and X3.
I’m going to shade the cells beneath them yellow.
That’s where I want the optimal solution to be placed.
Next I’ll place the objective function coefficients on this row.
2.9 1.9 2.5 I’ll start the constraints here.
Copy down for 2 and 3.
For Constraint 1, the coefficient of X1 is zero, so we can either enter zero here or
just leave it blank.
The coefficient of X2 is 3 and for X3 -1.
For Constraint 2, the coefficients are 1 for X1 and 1 for X2.
For Constraint 3, we have 3 2 and 3.
Next, we set up a formula for the objective function Zee.
Recall that these yellow cells are set up to hold the values of X1, X2, and X3.
Thus, the objective function will be the sum of the products of each coefficient and the
yellow cell above it.
I’m going to colour the cell light green… and use =SUMPRODUCT, select the coefficients,
comma, then select the variable cells.
ENTER The value is zero here because we are currently
multiplying the coefficients by zero in the variable cells.
I’m going to colour the constraint formula cells blue here…
and again use =SUMPRODUCT, select the coefficients, comma,
then select the variable cells.
This time, I’m going to lock the variables down using the F4 key on Windows.
On mac use “cmd+T”.
You can also manually type the dollar signs.
Use the fill handle to copy it down.
And double-clicking on the last one here shows that the formula copied correctly.
Next, I’m going to place inequality signs for each constraint here.
>= for Constraint 1 And <= for constraints 2 and 3.
The formulas in blue here represent the constraint Left Hand Side
For the Right Hand Side, we have 81 for Constraint 1,
89 for Constraint 2, and 400 Constraint 3.
The setup is now complete.
Now to solve this, I’m going to click on the data tab, then click Solver.
For the set Objective, I’m going to select the Objective Function formula.
For Changing Variable Cells, select the yellow cells.
To add the constraints, click Add.
For Constraint 1, click the left hand side for cell reference,
Change the sign to greater or equal And select the right hand side for the constraint
value.
Click Add.
For Constraint 2, select the left hand side, the sign is already less than…
In fact both Constraints 2 and 3 are less than so we can select both left side at the
same time and also select both right sides at the same
time.
Since that’s all the constraints we have, I can click OK.
The added constraints appear here.
Next, ensure that this non-negativity box is checked …
And that the solving method is Simplex LP.
Click Solve.
The coloured cells are now populated with values
with the optimal solution here and the objective function value here.
You can stop there and click OK in this dialog box if you’re only interested in the Optimal
solution.
Otherwise, you can select whichever of these reports you want to see.
I’m going to select all 3 and click OK.
The Answer report shows the optimal objective function value here.
The values of the decision variables at optimal solution here.
And if a constraint is non-binding, you will see a positive value for its slack or surplus
here.
You can also view the Sensitivity Report and the Limit Reports here if needed.
And that’s it for this video.
Thanks for watching.