Welcome to Highline BI348 Class Video Number 61.
If you want to download this workbook, BI348 Chapter Eight,
and follow along, click on the link below the video.
In last video we did a transportation problem
using Excel Solver to find an optimal solution.
And this is a slightly different set of problems.
We have different constraints and different shipping costs.
But we already ran it and got our minimum shipping costs.
Here's all of the functions and variables
we entered into our dialog box.
And I'm going to close it.
So we've already run this.
And we got our objective function.
But if we see a bunch of zeros here,
we might suspect that there is an alternative solution.
So I want to go back to our PowerPoints.
And we looked at this slide earlier in the class.
If we have a situation like this--
we have plotted all of our constraint lines--
and right here, this is the objective function contour
line.
Now notice, I have a constraint here.
But if I plot this contour line, if it
happens to sit exactly on a constraint,
we have multiple optimal solutions.
And the range, in essence, for this multiple optimal solutions
is wherever the vertices are, the line between there
and there, there's a multiple potential x and y solutions.
This is a graph of a two-variable problem.
Our problem has many variables.
But you get the idea.
There's multiple solutions here.
Well notice, when we plot an objective function contour
line, we actually set it exactly equal to the optimal solution.
And of course, there's multiple x and y's that will give us
that optimal solution.
So this is a simple graph kind of explanation
of what it means to have alternative optimal solutions.
So let's go back over to Excel.
Well, how could we possibly solve that?
And actually over in PowerPoint there's a slide with the steps.
Well, the first thing is we have to create a new constraint.
We've already entered in all of our supply and demand
constraints, but we need to enter one more.
And watch this.
It's really clever.
Remember, the contour line is a line
where every single shipping cost, in our case,
would be exactly equal to $39,500.
But there's multiple x's and y's.
So watch this.
I'm going to say equal sign and point
to the objective function.
Now remember, that's looking up here.
That is calculating all the decision variables
times the shipping cost per unit to get the total shipping cost.
We've already minimized it.
But that's going to be our new constraint function.
And I'm going to say it's set equal to,
and the constraint amount is going
to be exactly the same amount that it came out to, $39,500.
Remember, the contour line always comes out to $39,500.
But there's multiple potential x values.
And watch this.
The new objective function is going
to be the sum of all of the variables
that came out to be equal to 0.
And I've highlighted three of them.
Type a comma.
And then I'm going to click on this one.
Type a comma.
And then select these two.
So the sum of all the variables that are equal to 0.
And the goal is, or the objective, is to maximize this.
So it's going to search through all of the possible decision
variable answers for these zeros,
find the biggest possible combination
of these that's exactly equal to the optimal solution.
In the process it will change some of these other ones.
So you're ready?
Let's try this.
I'm going to click here.
So I've selected A23.
Now I'm going to go up to Data.
Over to Solver.
Now it's remembering the last time
we ran Solver, where we tried to minimize that cell.
So the set objective, the new objective function
will be right there.
Our new objective is going to be to maximize.
Changing Cell is correct.
We want to keep all the existing constraints but add a new one.
And the new constraint is going to be the original objective
function.
But guess what?
We're going to make you only equal to hard-coded value
$39,500.
Click Add.
Click Cancel.
And now when I click Solve, I will
get an alternative solution.
Click OK.
And there it is.
So a manager can choose between alternative solutions.
Notice we still get exactly the same minimized costs.
In this video we saw briefly how to deal
with an alternative solution situation.
Next video we're going to look at a great finance
example, where we maximize portfolio returns.
We'll see you next video.