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.

Â