Hello & welcome! In this tutorial I’m going to graphically
solve this linear programming problem in Excel. First, I’m going to click to the left of
A here to select all the cells and reduce their width to make my inputs closer together.
Next I’m going to type the variables X and Y up here.
Then the objective function coefficients. Then for the constraints I can type C1 here,
grab the handle and drag down for C2 and C3. Input 4, 2, 4 for X.
And then 1, 2, -5 for Y. Then I’m going to put the signs here.
Greater than for the first constraint And less than for the other 2 constraints.
And then input the right sides of the constraints. For visual purposes, I’m going to select
the constraints, right click, and select All-Borders. Next, I’m going to find points for drawing
the constraint lines. For constraint 1, I’m going to create a
table for the X and Y values. Highlight, right click and select All-Borders.
So when Y = 0 in C1, X = 12/4 which is 3. And when X = 0, Y just equals 12.
For constraints 2 and 3, I’m just going to copy Constraint 1 points here and paste
for 2 and 3. And then clean it up a little. So for Constraint 2, when Y = 0, X equals
9. And when X = 0, Y also equals 9. For Constraint 3, we can write 4X = 5Y so
when Y = 0, X equals 0. And when X = 0, Y again equals 0. We already
have that point so we need another point. So we choose any point for one of the variables
and then calculate the other one. For example, if we choose Y = 8, 4X = 40, so
X = 10.
Now to begin drawing the graph, I’m just going to highlight constraint 1 points, then
click on INSERT, then click Scatter, then select “Scatter with Straight Lines and
Markers”. Now I don’t need the title, so I’m going
to delete it. And then resize the graph to my liking.
Next I’m going to right-click anywhere on the graph and click “Select Data”. I’m
going to click on Series 1 and click Edit. For Series name, I’ll just type C1 for Constraint
1. Next, I’m going to delete the “Series
X values” and replace it with 3 and 0 here. I’m going to delete “Series Y values”
as well and replace it with 0, 12 here and click OK.
For constraint 2 I’m going to click Add, input C2 for “Series name”, select 9 and
0 here for X, make sure the content of “Series Y values” is deleted and select 0, 9. Click
OK. Click Add again, and then C3 for series name.
0, 10 for X values and then delete this {1} and replace it with Y values.
Then click OK and click OK.
Next I’m going to label the constraints by clicking on INSERT > Shapes, and then Text
Box. Click and drag, and type C1 for the first
constraint. Now press and hold the Ctrl button, click
and drag to copy the text to the other 2 constraints. Next we find the feasible region.
Note that C1 is a “greater than” constraint so it is satisfied here above the line.
C2 is a “less than” constraint so it is satisfied here below the line.
C3 however is a little tricky because of the negative sign. To determine which region actually
satisfies C3, let’s pick a point and test to see if it satisfies the constraint.
If we choose the point (4, 0) for example, we see that the 16 is not less than or equal
to 0. So the constraint is not satisfied in this direction. It must be satisfied in the
other direction. Judging by the direction of the arrows, we
see that the feasible region is the region here.
So I’m just going to Ctrl click and drag C1 here and label the region, Feasible Region.
Next I’m going to shade the feasible region. Click INSERT > Shapes, and then select the
“Freeform” tool. Next I’ll click on one of the corners, move
and click on the second one, and the third, and then move my cursor to the beginning point
to close it off. To make the shape transparent, I’ll right-click
on the shape and click “Format Shape”, click Fill, then increase the transparency
as desired. Next, I’ll label the extreme or corner points
A, B and C.
I’m now ready to find the coordinates of the extreme points.
At Point A, the two lines that intersect are C1 and C2 so I’ll copy and paste the constrains
here. I’ll also set up cells for X, Y and Z where
the coordinate values will appear. I’ll shade the cells for X and Y yellow, and use
green for Z. To find the coordinates at A, select the 2
yellow cells, both of them must be highlighted for this to work.
Then type =MMULT (that is, matrix multiply), open bracket, then MINVERSE, open bracket,
then select the coefficients, close bracket, comma, and then, select the RHS of the constraints.
And then close bracket. Don’t just hit ENTER. What we do here is
Ctrl, then shift, and then press Enter. And there you have it. That is the coordinate
at point A.
I’m just going to copy this and paste for Points B and C.
At point B, the two constraints are C1 and C3.
So I’m just going to copy C3 and paste on C2 here.
You can see that the coordinates are automatically adjusted to 2.5 and 2.
We do the same for point C. The two constraints for point C are C2 and C3. We copy them and
paste them here.
Next we calculate the value of Z for each point.
The objective function is to minimize 11X + 3Y
So we input 11 times the X value + 3 times the Y value.
We then copy the cell and paste the formula into the green cell for Points B and C.
Since this is a minimization problem, the optimal solution occurs at Point B and it
is X = 2.5 and Y =2. The corresponding minimum value of the objective
function is 33.5. And that concludes this video.
Thanks for watching.