Welcome to Highline BI348, class video number 58.
And if you want to download this Excel file,
BI348-Chapter08, and follow along, click on the link
below the video.
Hey, in this video, we're talking linear programming
to optimize an objective function.
And we're going to use Excel Solver.
Now, in the last video, we used linear algebra and paper
and the graphical method to solve this problem.
Now, here's our problem.
Computer company sells laptop 1 and laptop 2.
We have a price of $295 and $450, respectively,
and a cost of goods sold of $250 and $400, respectively.
And we would like to maximize contribution margin given
some constraints.
Now, the first constraint is the max monthly demand for laptop 1
is 200 units.
The min monthly demand for laptop 2 is 100.
That means we already signed a contract, we have to sell 100.
And our total cost of goods sold cannot exceed $700,000.
Now, our goal is what number of units produced and sold
will maximize contribution margin.
And contribution margin is just sales minus cost of goods sold.
Whatever that amount is can contribute
to other expenses and profit.
Now, we're going to have to-- just like we did last video--
list our goal, our variables, including our decision
variables, list our objective function and the constraints.
Now, I've already set this up a little
bit, so I'm going to come here and type product laptop 1.
That will be product one.
And then laptop 2.
Now, our price for laptop 1, $295.
Cost of goods sold is $250.
Our price for laptop 2 is $450 and the cost of goods sold
is $400.
Now we can calculate our contribution margin.
I highlight both cells, and in the active cell
I say, hey, whatever the price is minus
the cost of goods sold, and Control-Enter.
Now, our decision variable is going
to be defined as number of units produced and sold.
I'm just going to put x to represent number
of units sold for laptop 1, and y for number of units
sold for laptop 2.
And now I'm going to put our dummy data in 1 and 1.
That's the number of units.
Now, it is the Excel Solver feature
which will find the optimal solution for this decision
variable that will maximize the contribution margin.
Now, here are some of the formula inputs in our decision
variable.
We have some more formula inputs.
The constraint, so max monthly demand
for laptop 1, that's going to be 200.
The min monthly demand for laptop 2 will be 100.
The max cost of goods sold for this project
is going to be $70,000.
Right.
So that's most of our formula inputs.
Now, I'm going to scroll down, and now we
want to list our goal or our objective.
Calculate the number of units for laptop 1
and laptop 2, that will be that decision variable, which will
maximize contribution margin.
Now, if we're going to maximize contribution margin,
we need an objective function.
Written out, total contribution margin equals $45 times x.
That just means there's the contribution
margin times the number of units plus contribution
margin for laptop 2 times the number of units.
Now, that's math, right?
And we did that last video.
But we need an Excel formula which
we can point to using Solver and tell Solver,
maximize this formula.
So here it is.
We take these two cells times these two cells.
That sounds like the perfect job for Sum Product function.
Now, array 1, I'm going to select the two contributions
margins.
Those are also called the coefficients, the coefficients
of our decision variables.
Comma array 2, I'm going to take our decision variables.
Now, here we only have two, right?
But later we'll have many, and so the Sum Product
will come in quite handy.
And Enter.
Our goal is to maximize.
Now, when we get to our Data Solver dialogue box,
it's going to be convenient to have the formula and the goal,
or down here, to have our formulas for our constraint,
the operator-- meaning the comparative operator-- and then
the actual constraint.
It's going to be convenient to have them listed
next to each other like this, because it'll
help us enter all of the correct numbers into the dialog box.
And this is a relatively small problem.
Later, we'll have some much bigger problem.
So following a format like this will be helpful.
Now, our math constraints.
We wrote all of these out last video on paper
and then plotted them on an x-y chart.
But here, we still need to have all of the formulas,
but they're going to be Excel formulas.
Now, for the non-negativity constraint,
that just means our decision variables
have to be greater than or equal to 0.
x greater than or equal to 0, y greater than or equal to 0.
In earlier versions of Solver, we
had to actually put these formulas explicitly
into the dialog box.
But now they just have a check box where we check it,
and it will make sure that our decision variables are not
negative.
Now we gotta think about our constraint.
This is the actual number of units
for x have to be less than or equal to 200.
That means a maximum amount.
Well, we're going to need a formula here.
Remember, our function, as we talked about last video,
there's the left-hand side for the actual functions,
and the right-hand side for our constraints.
So the function, well, it's just x, right?
So I say, equals there's my x, Tab.
Now, if your comparative operator
is less than or equal to, I'm going
to put a lead apostrophe, less than or equal to, and Tab.
That lead apostrophe, sometimes when
you put comparative operators into cells,
Excel thinks it's a formula.
Now, this is not necessary to do.
I do this so visually on the spreadsheet we can see it,
and when we enter it into the dialogue box,
we don't make any mistakes.
Now, the constraint, I'm going to go up and point
that cell to my formula input 200, and Enter.
We'll talk about what slack is later.
All right.
Total units for y, well, I'm simply
going to get, hey, there's my y decision variable, Tab.
And notice y greater than or equal to 100, that's the min,
so this comparative operation will
be greater than or equal to.
And our constraint, our right-hand side number,
is going to be 100.
Now there's our formula for total cost of goods sold.
You know, we're looking at it in an algebra formula,
maybe it looks confusing.
But we know this is easy.
We simply go, hey, Sum Product, I'm
going to take the actual cost of goods sold, $250 and $400,
that's array 1, comma array 2, that's going to be our decision
variables.
Close parentheses.
And I must have entered something incorrect,
F2-- two and I did, look at that.
I had a minus there.
So I get rid of that, Control-Enter.
Now, this is less than or equal to because that
is a maximum amount.
So lead apostrophe less than or equal to, Tab.
And our constraint, our right-hand side number,
$70,000.
Now we're ready to go.
We have an objective function, which
is now an Excel formula, which we can maximize,
our formulas for our constraints,
and our right-hand side numbers.
Now, I'm going to click in the cell, go up to Data,
over to Analysis.
Now, you have to add this in, and if you don't have it
in, File, Options, Add-Ins, down here we say Go,
and there it is right there.
Escape.
All right.
So I'm going to go up to Solver.
All right.
So here we go.
Here's the Set Objective.
That dialog box with the Collapse button
means we click on the cell with the Excel formula
we want to maximize.
We can choose Max, Min, or set to a particular value.
By changing variable cells, this is the decision variable.
We have two of them.
Now Excel knows what two numbers to change in order to maximize.
It will report back the number of units we should
produce and sell for each.
Now, we come down here.
Make Unconstrained Variables Non-Negative,
that's that checkbox that doesn't require that we put
those formulas in explicitly.
Select Solving Method.
Now, this usually comes up GRG Non-Linear.
You want to make sure and set it to Simplex LP.
We talked about that last video.
This is the method that searches the vertices or extreme points
of the feasible region and reports back
only the x-y coordinates for the max or optimal value.
Now, we need to click Add to start adding our constraints.
And here's where setting it up so nice and neat is easy.
The cell reference means the actual formula, so there it is.
There's our x variable.
And we've already listed it, so we know that's the correct one.
And our constraint, that's the right-hand side.
Now we click Add.
Cell reference.
That's the actual function.
The comparative operator, I have to click the dropdown
and select Greater Than or Equal To.
So we got that.
And then the right-hand side constraint, that's the 100.
Add.
Cell reference, that is our function.
We need to make sure we have the right comparative operator.
We do.
And the constraint is 70,000.
Now, I could click OK here, but I always make this mistake.
I click Add.
Now if you click OK, then you get an error, right,
because it's empty.
So I click OK.
So if you have entered them all and you're done,
you click Cancel.
It doesn't cancel the actual constraints.
They are already in there.
And so there it is.
The objective function, we're going to max.
The decision variables.
There's our constraints.
There's the non-negativity constraint, linear programming.
Now I'm going to click Solve.
Solver has found a solution.
All constraints and optimality conditions are satisfied.
Keep Solver Solution.
I'm going to click on two reports, Answer
and Sensitivity.
These will be inserted as new sheets.
Now when I click OK, there is my optimal solution.
124 laptop 1, 100 for laptop 2.
That will maximize our profits at $10,400 bucks.
Now, I want to go look at Answer Report.
It lists the objective sell, the actual sell,
and this is really clever, total contribution margin formula.
I want to go look at how Solver actually created that label.
We go back over here, and there, C27, it
actually took the text from the left and above
and concatenated those to create a label.
Later, when we look at the constraints,
it will do the same thing for this cell.
It will take the text to the left, total units for x,
and concatenate it with the label up here, formula.
So we're go to go back over to Answer.
And sure enough, total contribution margin formula.
If you look down here for that constraint,
total units for x formula.
So this name is very convenient.
It concatenates the left and right.
So it's usually a good practice to have smart text to the left
and to the right.
So there's the cell.
There's the name.
There's the original value.
There is the final optimized value.
Down here, under variable cells, the cell, the name,
the original value, the final value,
and what type of variable it is.
We didn't change it to integer, so it's telling us
it's a continuous variable.
Later, we'll see how to change it to integer and to binary.
Now, down here under constraints, there's the cell,
there's the name.
The cell value, that's the actual final value
that they came out to be, formula, and status and slack.
Now, status, when it says binding,
it means we actually hit the constraint.
If this is the cell value, well, we
know the constraint was $70,000, so we hit it.
We were binding, we hit the constraint,
and there is no slack.
There's no wiggle room.
Here, that demand for y, well, we
hit-- that's the actual y value at the end
in the optimal solution.
That exactly equal to the constraint,
so it's binding with zero slack.
The 120 that is exactly 80 below the max demand of 200, so it
is not binding.
If we actually go look at a slide over in our PowerPoints,
we actually plotted this by hand last video,
what those constraints mean is that is the intersection of two
lines on the outside of the feasible region right there.
It found at one of the extreme points
an optimal value, that is x equal 120 and y equal 100.
Notice because the point is right on these lines,
we hit the constraints.
We're binding.
But notice we're also exactly 80 away from that constraint.
Back over to Excel.
Now we want to go over to the Sensitivity report.
And we're going to look at the constraints.
First, there's the cell.
There's the name.
The final value, you can see, we hit these two constraints
but not this one.
Now shadow price.
Shadow price tells you if you were
to increase the right-hand side of that constraint by 1,
how much would the objective function change?
Well, here, we're 80 away.
So if we increase this by 1, it doesn't change at all
the objective function value.
But let's think about this when we hit the constraint here.
The constraint is 100.
If I increase it by 1 to 101, that
means the value of the objective function will go down by 22.
For this third constraint right here,
we hit the constraint, right?
Here's $70,000.
If I increase it by 1, which means
I make it bigger by 1, $70,001, the objective function
will go up 0.18.
Now, if we're changing this, it might
be helpful from the allowable increase and decrease column
to know what the range is for this number right here.
That means we can increase it by 75 and decrease it by 50
and the shadow price will still be valid.
Now, up here to our variable cells, the cell, the name,
the final value.
Reduced cost means if we increase the non-negativity,
for example, from 0 to 1, how much
will the objective function change by?
In this case, 0 for both of them.
This is the objective function coefficient, that 45 and 50,
and this is the range for how much we
can change these and still have the optimal solution.
Now, I want to go back over to Manufacturing Mix.
So in this video, we saw how to use Excel Solver to get
an optimal solution, in our case,
maximize contribution margins given a set of constraints.
Now, when we come back in our next video,
we'll talk about special cases that we
might get when we run Solver.
All right, we'll see you next video.