Welcome to Highline Bi348 class video number 62.
Hey, 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 9 doing linear programming
using Excel Solver, and this is our fifth video.
Here we have a finance problem.
The maximum available funds for portfolio investments
is 250,000.
The three investment categories for this portfolio
are tech sector, retail sector, and corporate bond index fund.
That's not really a category.
That's an exact fund they're going to invest in.
The individual investments being considered
Microsoft, Apple, Target, Amazon, e-trade corporate bond
fund.
Here are the expected returns.
Remember we saw how to calculate expected returns earlier
in this class, but there they are.
There are some constraints.
The maximum amount invested in the tech sector, 125,000.
So half of our total investment potentially
could go into the tech sector.
The minimum amount to invest in the retail sector is 75,000.
And then there is a specific requirement.
The minimum amount to invest in a particular stock, Target,
is 15,000.
The final constraint or requirement, the minimum amount
to invest in the corporate bond index fund
is 20% of the amount invested in the tech sector.
The goal, what is the amount that
should be invested in each investment
so that profits are maximized?
Now in earlier videos, we spent a lot of time translating this
into our parameters-- formula inputs, decision
variables, and constraints-- but I've already done that here.
Our decision variable will be amount invested,
so I'm going to list amount invested
in each one of the investments.
And I can see that this is off, so watch us
on the highlight this and then point to the edge with my move
cursor and click and drag up.
Each one of the investments we need
to determine the amount to invest in each one
to maximize portfolio profits.
So I'm going to call each one of the amounts
x sub 1, x sub 2, all the way to x sub 5.
Here are the decision variable cells.
These are the cells that we will point solver
to say, hey, figure out what amount we
can invest in each one of these to maximize returns.
Here are the returns.
Down here we have our constraints, available funds
250,000, max to invest in the tech sector
125,000, minimum in retail sector 75, and the minimum
in Target 15,000.
And then we have two other constraints here,
the minimum amount to invest in corporate bond index
fund has to be 20% of the total amount invested in the tech
sector.
And of course, for each one of these variables-- as we have
done in all previous five videos--
we have each amount must be non-negative.
That's our non negativity constraint.
We don't need to enter this into our solver dialog box,
but were going to put it right here so we
have our complete list of constraints.
Now let's scroll down.
Here is our objective function.
All it is is there's the amount to invest in Microsoft.
That's decision variable number one.
The amount of times the return for Microsoft plus each one
of the other decision variables amount
to invest in the investment times the expected return
for each.
I don't know, if we're thinking ahead
that sounds like the perfect job for the sum product function.
We're going to take a bunch of returns
times a bunch of amounts invested,
multiply, and then add.
So that will be our objective math function
and we'll do the sum product, multiply
those two range for our Excel objective function.
Now our constraints, here's our math formulas.
The first constraint is simply please
add up all of the amounts from the decision variable.
The total amount for all the stocks
has got to be less than the total amount we have to invest.
x sub 1, x sub 2, that's the tech sector amount.
There's a maximum so we have to say tech sector amount less
than or equal to 125,000.
For the retail sector amount, we have a minimum of 75,000.
And so that amount has to be greater than
or equal to 75,000.
Target, greater than or equal to the minimum, which is 75,000.
Finally we have the amount invested
in the bond fund has to be bigger than--
and there's the sum of the tech sector times 0.2.
All right, just like in earlier videos,
we're going to set up our objective function and all
of our constraints smartly, so I'm
going to start with the objective function.
Remember, we need to multiply these two and add them.
That's going to be the total portfolio returns that we're
going to try and maximize.
So I'm using equal sum product.
Array one I'm going to select five rows and one
column, comma, the second array five rows and one column.
Expected return, amount invested which is our decision variable.
Notice that objective function is dependent on those decision
variables.
Similarly, our constraints will also
be dependent on those decision variables.
Close parentheses and Control Enter,
so there is our objective function
and our goal is to maximize.
Now let's list our constraints.
We need total invested.
Remember that's x sub 1 all the way to x sub 5 an and them up.
So I'm simply going to use this sum function.
I want to add these, close parentheses and Control Enter.
Just as we did in earlier videos,
I'm going to make sure to list my comparative operator.
And then the right-hand side constraint,
I'm going to say equals and click on 250,000.
The idea here is we list the objective function,
the operator, and the constraint visually
on the spreadsheet so we can see it.
We make sure everything's OK.
And when we open up our solver dialog box,
it'll be simple to just enter this into the dialog box.
All right, now our next function we
need the total of this tech sector,
so I use the sum function, total for Microsoft and Apple,
close parentheses, Tab.
The right-hand side constraint equals 125,000.
That is a maximum amount.
So the operator is hey that amount has to be less than
or equal to.
Now we get to our third constraint.
We need to add up the retail sector,
and there is our minimum amount.
Equals sum of the retail sector, that's Target and Amazon,
so we add that up.
Tab, greater than or equal to the minimum amount
equals 75,000, and Enter.
Our next constraint, we need simply one cell.
x 3, that's the amount invested in Target,
Tab, greater than or equal to the minimum amount of 15,000,
and Enter.
Now this formula right here, we have
two sides we're going to have to calculate.
The first one is simply the amount
in e-trade corporate bond fund, Tab,
that amount has to be greater than or equal.
And now I'm going to use equals sum
to add up the tech sector, close parentheses,
times our formula input of 20%, Control Enter.
Now we're ready to use Excel Solver.
Objective function, constraints, I
click in the objective function cell, Data, Solver.
All right, here we go.
Set objective, there's the objective function.
We definitely want to maximize by changing
variable cells, that's always our decision variables.
That's the amount that will be changed
by this linear programming Excel Solver process,
tell us exactly amount to invest in each one of the investments.
Make unconstrained variables non-negative,
that's our non-negativity constraint.
Simplex LP, that's us telling, hey
Solver, use linear programming.
Now we click the Add button.
We have 5-- 1, 2, 3, 4, 5-- and notice
we have a bunch of functions and some right-hand side
constraints, but there's different operators.
So unlike last time where we just
entered the functions and the constraints one time,
we're going to have to be careful and enter
multiple different constraints.
All right so let's click Add.
And I'm going to do this one by one since each constraint is
quite different.
Total invested, that has to be less than
or equal to our maximum amount of 250,000.
Click Add.
The objective function, its total amount
invested in the tech sector has to be less than
or equal to our right-hand side constraint.
That's the maximum amount of the 125,000.
Click Add.
Cell reference, that's our objective function, which
is simply be told amount invested in the retail sector.
That has to be greater than or equal to our minimum of 75,000.
Click Add.
Now we go down and get the objective function.
The amount for Target greater than
or equal to our minimum amount of 15,000.
Click Add.
Cell reference, that's going to be our total amount
invested in corporate bond index fund.
That has to be greater than or equal to our calculated minimum
amount.
Click Add.
We have no more constraints.
I click Cancel.
I see the objective function.
We're maximizing.
We can look through this.
And by the way, you can come back and change or delete
as needed.
We have all of our settings.
I'm going to click Solve.
And just like that, it found a solution.
Remember, earlier video we saw some of the error messages
you can get, what causes it, and how to fix it.
But we don't have an error message,
so I'm going to click OK.
And there it is.
We're not investing anything in Microsoft's.
Too bad, Excel is so awesome.
We're investing 125,000 in Apple, 15,000 in Target,
85,000 in Amazon, and $25,000 in e-trade corporate bond fund.
All right, so in this video we saw our fourth example
of linear programming.
And we used Excel Solver to calculate
the optimal amount, the maximum amount, for portfolio returns.
All right, we have one more video.
In our next video where you will look at a finance example
where we have a limited capital and we'll
look at a binary variable.
All right, we'll see you next video.