Welcome to Highline BI348 class video number 63.
If you want to download this workbook BI348 Chapter 8
and follow along, click on the link below the video.
Hey, we're still in chapter 8 and 9
doing linear programming with Excel Solver.
And this is our last example.
We have Gel Boomerangs Manufacturer
trying to decide between four projects.
And Gel Boomerangs has limited resources
for the next four years.
Now here is our criteria for choosing a project,
net present value.
Now that topic is for a finance class.
And if you look at YouTube, I have a whole class on finance.
But the idea here is boom, boom, boom, boom,
they are all positive net present value so we'd
like to take them all.
But here's the constraint.
Year 1 to 4 the capital requirements
for this project, capital requirements for this project,
and so on.
But guess what?
We also have some constraints for each year, year 1 to 4,
we're limited to $40,000, $50,000, $40,000 and $35,000
for capital available.
So we're going to learn about a new type of variable,
not continuous or integer like we did before, but binary.
1 or 0.
Now in all of our prereq classes we've
talked about true and false and ones and zeros and Boolean
and binary.
Here, it's going to be the same.
One is true, 0 is false.
So I'm going to define a binary variable for 1, 2, 3,
4 projects.
Gel Boomerang manufactures Boomerangs, right?
So a new C and C router machine.
That would be a potential project
that we'd like to take on.
A new delivery van, a research project, and new
paint room equipment.
So we're going to designate each one of these.
This will be C for C and C router.
Tab.
V for a new van.
Tab.
R for new research project.
P for new painting project.
Now this is a binary.
So it's going to be 0 and 1.
I'm going to start out with dummy data and put a 1.
So 1 Control Enter.
That means if we were to multiply
1 times each one of these values here,
we would be taking all of the values.
If in our linear programming Excel Solver, one of them
or more than one of them comes out zero
it means we're not going to take that project.
All right.
So we're going to start out with 1.
Well, that leads to the objective function.
And the math function is simple.
We take the amount of the particular project
times the binary variable 1 or 0.
Notice it's like an on, off switch because when it's 1
we'll multiply it and then add it to the rest
so we'll actually get the amount.
If it's 0 it's like off.
That'll become zero so we're not taking that project.
So we do that for each one of the variables.
Amount times the binary variable.
Amount binary variable.
And so on.
So to do that as our objective function
sounds like a perfect job for the sum product.
Multiply corresponding elements.
And then add.
Equals sum product.
Array 1, one row by four columns.
Comma.
Array 2, one row by four columns.
Close parentheses and Control Enter.
To show you how this works, if I change this to a 0
instantly our objective function updates.
Now you could prove this to yourself.
You could highlight these two values,
hold Control, and then highlight that value.
Come down to the status bar and you can see we get 157,000 just
like our objective function.
Now down to our constraints.
And I'm going to delete these.
These shouldn't be here.
Our constraints for each one of them
is going to be similar to our objective function.
The amount for first project, project
C in year 1, times the binary variable.
Amount from project V in year 1 times the binary variable.
And so on.
The binary variable is an on, off switch.
Now if we were to simply add these up and they're all one,
it would be exactly the same.
But as soon as we change it to zero then for example project
R, all of these would become zero
in our constraint function.
All right.
So I'm going to click in the top cell equal sum product
because yes, indeed.
We're multiplying four cells to the left arranged with one row
by four columns.
Comma.
This range is one row by four columns.
But we better lock it.
F4 key to lock it because when we copy down we
need to be locked on our decision
variable, that binary variable.
Close parentheses.
Control Enter.
And copy it down.
Now we're ready to go.
We can go up to our objective function cell.
Data.
Solver.
Our set objective, that's our objective function,
we definitely want to maximize by changing
these awesome binary variables.
All 1, 1, 1 right now, but we'll see what it changes to.
Make unconstrained variables non-negative.
Simplex LP.
Those are the settings we've been using all along.
Let's click Add.
Now this is convenient here.
All of our constraint functions and all of the right-hand side
constraints have exactly the same comparative operator.
So we can enter as an array constraint functions.
Boom.
Less than or equal to.
Boom.
Constraint.
Right-hand side as an array.
Click Add.
We have a very important variable to define.
Our next constraint will be the decision variables.
Guess what?
You have to be binary, 1 or 0.
Now, I can click Add.
Now, I can click Cancel.
There is my entire set up.
Objective function.
Decision variables.
Constraints.
Click Solve.
Solver found a solution.
I click OK.
And uh-oh.
Our new paint room equipment, doesn't
look like we're going to be able to do that one at this time.
But project C, V, and R, C and C router, new van, and research,
we can do those.
We can see that the value here reflects that we're only
taking these projects.
Similarly down here, if we were to add up
these-- remember our trick.
We highlight and look down in the status bar.
27,000.
Boom.
There it is.
All right.
So in this video, we saw how to use
a binary variable linear programming Excel Solver
for a limited resource problem.
All right.
That's it for chapter 8 and 9.
We'll see you next video.