0:00

Hi, I'm Sergei Sav and

we're starting session two of the second week of Operations and Litics course.

In session one, we have looked at the Zooter example,

a resource allocation problem in which limited resources

must be allocated among two competing products in the most profitable way.

We have identified the three main components of the optimization model.

Decision variables, an objective function, and constraints.

We have also written down an algebraic formulation of this other problem.

In this session, we'll create a spreadsheet formulation of the model, and

find the best decision using the Solver Optimization tool.

Okay, in session two,

we will take an algebraic model we put together in session one.

Express it in the spreadsheet terms, and we'll use Solver to optimize it.

0:47

As a reminder, here's the algebraic model of the Zooter problem.

We have the decision variables, R,

the number of razor scooters to produce, and N, the number of scooters to produce.

The objective function which is the profit, 150 x R + 160 x N, that we want

to maximize any constraints on available resources and on our decision variables.

2:04

To go over this example, we will use Excel 2013 on the Windows platform.

If you're using Mac or Google Sheets instead,

you should still look at the steps we're following here first.

Before we build the model, let's figure out where to find the Solver.

The Solver's is called Excel add in.

We're not in Excel 2013 and in this version of Excel, the Solver's located

under the tab called Data in the portion of the tab called Analysis.

Here's my Solver button.

2:31

If your setup is just like mine and

you see the silver button there, you're good to go.

If you do not see the silver button,

then you should go to File > Options > Add Ins.

And here at the bottom where it says Manage Excel Add Ins, you click Go,

and you wanna make sure that the Solver Add In is checked.

You click OK, and the Solver should appear here on the Data tab.

3:00

In the early versions of Excel, the steps you need to take may be different but

in any case just keep in mind the Solver is an Excel add in.

Find where the add ins are in your version of Excel and

make sure that the Solver is selected.

Okay we're now ready to set up our model.

For this first example we will proceed in a very detailed manner to make sure that

we cover every important aspect of the model setup, and

of the solver functionality.

3:24

Now we need to translate our algebraic model into a spreadsheet formulation.

In other words, we need to describe on the spreadsheet

three key components of an optimization model.

Decision variables, an objective function, and constraints.

Let's start with the decision variables.

In the Zooter problem we have two products, Razor and Navajo scooters.

So let's create cells that will hold the values for

each type of scooter to be produced.

Let's say we select cell C10 and D10 for this purpose.

3:54

C10 will hold the value of the number of Razor scooters to be produced,

that is the value of the decision variable R.

And D10 will be reserved for the number of Navajo scooters.

That is, the value of the decision variable N.

Let us put the header in cell A10, units to make,

4:34

One last thing, let's highlight the decision variable cells by

using the blue color and bold font, and by putting a frame around them.

[SOUND] Of course whether you do this embellishment or

not is completely up to you.

It will not effect the optimization but if you do,

the decision variables will be easily recognizable as soon as

the Excel file is opened and this will make you're file easier to navigate.

We have to find results to hold our decision variable values.

Later, we'll point the cells out to Solver and ask the Solver to chang

the values in cell C10 and D10 to identify the best possible production plan.

Okay, we're done with the decision variables and are now ready for

the objective function.

The objective in this model is the total profit.

So we should be able to calculate how much profit Zooter will be making for

any values of the decision variables.

Now, let's calculate and record the profit value

corresponding to our production plan of 500 units of each model.

Let's select the cell F10 to hold the value of the objective function,

that is the total profit value.

In the cell F10 we will be writing a formula

that calculates the value of the total profit.

We'll start with the equality sign to tell Excel that we have a formula in this cell.

Now, for each of 500 Razor scooters, Zooter gets $150.00.

So we multiply 150 by 500.

And to that value we must add the profit earned from the Navajo scooters.

That is 160 x 500.

So the total is $155,000.

So we have a formula for the objective function cell.

This formula will calculate the total profit value for

any choice of the decision variables.

For example, if we try a production plan of say,

600 units of Razors, and 600 units of Navajos.

7:09

In this problem we have two decision variables.

And the calculation of the profit value involves studying two products.

The product of the number of Razor scooters and the profit contribution for

each Razor scooter and the product of the number of Navajo scooters and

the profit contribution for each Navajo scooter.

But what if our problem contained 1,000 of decision variables,

do we still have to write the profit formula as a sum of 1,000 of products,

one for each decision variable?

Fortunately not.

The Excel function sum product allows us to use a kind of

shorthand notation in such cases.

Here's how the sum product works for the case of two scooter models.

We are in the cell left hand.

And let's replace the current profit formula by its

equivalent using some product function.

So we type SUMPRODUCT Of, C9 and

D9 are profit contributions.

And C10 and D10 are decision variables.

8:14

The sum product function uses two areas of cells of equal size.

And multiplies the numbers in the first area by the corresponding numbers in

the second area.

First number in the first array is multiplied by the first number in

the second array.

Second number in the first array is multiplied by the second number in

the second array, and so on.

After that the SUMPRODUCT simply sums all of these products.

So, the formula SUMPRODUCT C9:D9, C10:D10 is exactly the same

as the formula C9 x C10, + D9, x D10,

and you'll get the same optimization result no matter which one you use.

However, the sum product formula is a lot more convenient when working with

the models with large numbers of variables and large numbers of constraints.

9:26

And put the thick border.

Now every time we open the file, we see the decision variable cells in blue and

the objective function cell in red.

So those cells have visually distinguishable from other cells.

Later on we will instruct the solver to change the values in our blue cells,

C10 and D10, our decision variable values,

to maximize the value in the red cell F10, our objective function value.

Of course, we can not just use any values in the blue cells, but

only those who require more resources to produce, than what we have.

9:58

Now that we're done with the decision variables in the objective function,

it is time to move on to the constraints.

The main constraints in our model express the limited developability of

free production resources.

Frame manufacturing hours, wheel and deck assembly hours, and

QA and packaging hours.

Let's look first at the frame manufacturing hours.

We need to make sure that whatever production plan we consider,

the number of frame manufacturing hours used by this plan

does not exceed the number of frame manufacturing hours available.

11:28

Okay, we have two more constraints to convert into a spreadsheet format.

The constraint on the number of wheels and deck assembly hours and

the constraint on the number of Q&A and packaging hours.

If you look at the algebraic formulation of each of those constraints, you

will notice that both have a very similar structure to that of the constraint we

already dealt with, the constraint on the number of frame manufacturing hours.

All three constraints have the full instructure, expression on the left-hand

side of the constraint, the number of required hours, cannot exceed the number

on the right-hand side of the constraint, the number of available hours.

12:16

Just like in the similar calculation for the number of required frame manufacturing

hours, we need to multiply each decision variable by the number of wheels and deck

assembly hours that a respective scooter model uses and add the resulting products.

In other words, the formula that we put in the cell

E15 is =SUMPRODUCT(C10:D10,C15:D15).

12:45

For the production plan we're currently considering,

500 scooters of each scooter model,

we have the number of required wheels and deck manufacturing hours as 1750.

Similarly, if we use cell E16 to calculate the number of Q&A and

packaging hours required by the production plan in cells C10 and D10,

we will put in the formula =SUMPRODUCT,

14:11

And the consumption amounts of each respective resource.

C14 and D14 for this cell E14.

C15 and D15 for the cell E15.

And C16 and D16 for the cell E16.

So if we would take a formula in the cell E14 and

copy and paste it into the cells E15 and

E16, we would need to instruct Excel to leave C10 and

D10 unchanged during this copy and paste operation, and

to change C14 and D14 into C15 and D15 and into C16 and D16.

14:50

The way to accomplish this is to use the absolute cell referencing, or

cell anchoring, for the cells C10 and

D10 before doing the regular Excel copy and paste operation.

Here's how we accomplish this.

We go into the cell E14, we highlight cells C10 and D10,

and we use a Windows shortcut, F4 to put the dollar signs

around the addresses for sales C10 and D10.

Those dollar signs instruct Excel not to change the addresses of the cells

when doing copy and paste.

If I now just copy and paste the formula in E14 into E15 and

E16, I get the correct formulas in those cells.

15:45

For example if I now look at the cell E16,

I see the formula =SUMPRODUCT(C10:D10,

C16:D16) and that is the correct formula.

Clearly using this anchoring technique, I can simply type a formula for

the resource consumption of one resource And curve what ever cells I want,

in this case decision variable cells.

And then just copy and paste the formula to

all cells that calculate consumption amounts of all other resources.

And it does not really matter if I have hundreds of those.

I can still do it all in one copy and paste operation.

You can learn more about the absolute cell referencing using Excel Help.

17:00

Okay, we have created cells that hold values of the decision variables.

The objective function and the resource consumption values.

How will we go about finding the best production plan?

In short, we want to find the values in the cells C0 and D10, our decision

variable cells, that make the value in the cell F10, our objective function cell,

as large as possible while making sure that the values in E14, E15, and

E16 do not exceed the values in G14, G15, and G16, respectively.

Those are resource constraints.

For example if we produce 500 units of each model,

we earn the profit of $155,000 as Excel tells us.

And all of our resource consumption values stay within allowable ranges.

Well, can we make more money by increasing the production?

Let's try producing 500 Razor scooters.

That's 750 Navajo scooters.

Our profit jumps to $195,000 but unfortunately we ran out of

frame manufacturing and wheels and deck assembly resources.

So we cannot simply implement this production plan.

Now, let's tone it down to say, 600 Navajo scooters, so

our profit goes down to $171,000,

and our required number of hours stay within the allowable limits.

So we can keep checking different values of decision variables,

trying to improve the profit while staying within the resource limits.

The problem is, if we're trying to do it manually,

then we spend a tremendous amount of time checking out various possibilities.

And even then we might not find the best production plan,

especially if we have to deal with many decision variables.

This is where the solver comes in.

It would be impossible any human to check all possible alternatives,

just because there could be so many of those alternatives.

The solver, though, does a much faster, and much more through job

of checking those alternatives in trying to come up with the best.

So let's bring in a solver.

Let's go to Data, click on Solver.

19:31

Minimization could be helpful if you are dealing with minimization of the cost, or

it could select a values of decision variables to

produce a desired value of the objective function.

In a scooter problem we maximize profits, so recheck max option.

Next, we use by changing variable cells to specify where our decision variables are.

So we use the cell selection tool again to point to the cells C10 and D10, and we go

back and we see that Excel now understands where our decision variables are.

Finally, we need to specify to sole where the constraints are.

We use Subject to Constraints, a part, and click Add.

20:27

We also use constraint part to select the values G14,

G15, and G16 on the right-hand side of that constraint.

So, now we instructing this over to make sure that E14 does not exceed G14,

E15 does not exceed G15, and E16 does not exceed G16.

21:06

What is left is adding constraints that tell the solver that our decision

variables must be integer and non-negative.

Let's add the integer constraint first.

We can click on Add, select our decision variable, C10 and D10, and

then in the drop down menu select INT options, which means integer.

So now when we click OK, the Excel solver understands that

it must only use integer values in the cells C10 and

D10 when it searches for the best production plant.

22:17

This is a solving method that can only be applied to linear models, in other words,

the models where the objective function and

the constraints are linear functions of decision variables.

So if you are sure that your model is built as a linear model,

use this option since it solves linear models very efficiently.

22:38

If your model is not linear however, and

you're trying to use the LP Simplex option, the solver will complain.

Now in this discussion I would like to focus on the details of the modeling

process rather than on distinctions between linear and nonlinear models.

So I would leave the solving method at GRG Nonlinear.

This solution method is very general and will allow you to work with

many different kinds of models, both linear and nonlinear.

23:16

Since it is a general method of solving optimization problems,

it will try to find the solution for any model that you formulate.

However, it may not always be able to guarantee that what it finds, it gives you

as a solution, is actually the best possible alternative in every case.

In addition the output of optimization using the GRG nonlinear method

may depend on the trial values of the decision variables.

So when optimizing using this method,

run it several times with different trial values of the decision variables to see if

you can improve the objective function value.

23:52

Okay, the last stop before optimizing.

Let's go to Options, and

make sure that the Ignore Exchange Constraints is unchecked.

This way, we're really making sure that the solver will

not try to produce something like 54.6 scooters.

We're ready to find the best production plan.

Let's click solve button, and

make sure that solver found is solution is displayed in the dialogue that appears.

And it does.

24:28

Solver recommends producing 840 unit of razor scooters and

450 of Navajo scooters,

if Zooter wants to maximize it's profit given the resources the company has.

The corresponding profit value is 100 and $98,000.

Before we leave Excel, a few words about solver messages.

This time the solver came up with the message, solver found a solution.

This is the message we want to see.

I would like to mention two other messages that we do not want to see.

Suppose that we made a mistake in setting up our model and

forgot to include one or more important constraints.

Let's go to our solver dialogue and wipe out all of our constraints,

and then try to optimize our production plan.

So we go here.

I'm going to say delete, and we say delete, so we have no constraints.

Of course, that's silly, but let's try to solve the model and

see what kind of message the solver comes up with.

Well, the solver comes up with a huge red explanation sign,

and the message, objective cell values do not converge.

This means that the profit in this model can grow to infinity.

When you see a message like that when trying to optimize your model, please

remember that it most probably indicates that you forgot an important constraint.

26:22

Okay. Now, let's set a constraint that will

make it impossible for the solver to find the values of the decision variables

that will satisfy all of the constraints in the problem.

For example, right now we want the number of frame manufacturing hours

used by the production and not exceed 5,610.

Suppose with a constraint that also requires the number used manufacturing,

frame manufacturing hours to be at least 6,000.

Of course, these two constraints cannot be set aside at the same time.

But let's see what the software tells us when we are trying to solve the model

with this constraint added.

So we're going to solver, and we're saying, let's add this constraint.

Let's say the number of required hours of freight manufacturing

should be at least greater or equal than 6000.

27:13

It's an incompatible constraint with your other constraints,

but let's see what the cell reaction would be.

We click solve and the solver comes up with another red exclamation sign and

a message that it could not find a feasible solution.

In other words, it could not satisfy all the constraints at the same time.

So when we see one of these messages, there's something wrong with our model.

Either, in the case of the first message we are probably missing an important

constraint, given the objective function we're trying to optimize, or

we have some incompatible constraints in the case of the second message.

Let's restore our model to its original state and

solve it one more time to make sure everything is fine.

Let's delete this spurious constraint.

Let's click unsolved.

And here we go, we have our optimal solution.

29:31

In this session, we have used the solver optimization tool to set up and

solve Zooter optimization problem.

While doing this, we have learned two approaches that are often useful in

setting up spreadsheet optimization models in Excel, the use of the sum-product

function and the use of cell anchoring or absolute cell referencing.

While details of solver operation may be slightly different in different platforms,

for example Windows versus Mac,

the main features of the optimization process are pretty much the same.

In particular, we must identify for the solver the three main components of

an optimization model, decision variables, the objective function and constraints.

If you an excel for Mac or google sheets,

have a look at two brief videos we created for you to go over some minor differences,

in how optimization problems are set up and solved.

30:21

The Zooter's problem had two decision variables and three resource constraints.

Real resource allocation applications may contain hundreds of thousands of variables

and constraints or more.

Of course, solver will not be able to handle such large problems.

But commercial optimization software packages that are powerful enough to deal

with problems of this size will still operate using decision variables,

objective function and constraints just like the solver.