0:00

Hi, I'm Sergei Savin and

Â we're starting session two of the second week of Operations In Linux 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've also written down an algebraic formulation of the Zooter problem.

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

Â find the best decision using the Solver optimization tool.

Â 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 Navajo scooters to produce.

Â The objective function,

Â which is the profit 150 times r plus 160 times n that we want to maximize,

Â and the constraints on available resources and 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 is called Excel add-in.

Â We're not in Excel 2013, and in this version of Excel, the Solver is 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 Solver button there, you're good to go.

Â If you do not see the Solver 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 want to make sure that the Solver Add-in is checked.

Â You click OK, and the Solver should appear here under 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 that 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'll proceed in a very detailed manner to make sure that we cover

Â every important aspect of the model's 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

Â the numbers of each type of scooter to be produced.

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

Â 4:52

Of course, whether you do this embellishment or

Â not is completely up to you.

Â It will not affect 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 your file easier to navigate.

Â So we have defined the cells to hold our decision variable values.

Â Later we'll point the cells out to Solver and ask the Solver to change

Â 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 start with the equality sign to tell Excel that we have a formula in the cell.

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

Â So we multiply 150 by 500, and

Â 6:08

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

Â That is 160 times 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,

Â the number in F10 immediately changes to reflect the new profit value.

Â 7:09

In this problem we have two decision variables.

Â And the calculation of the profit value involves setting 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 1000th of decision variables?

Â Do we still have to write the profit formula as the sum of 1000th 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 F10, and let's replace the current profit formula

Â by its equivalent using sum product function.

Â So we type sum product.

Â 8:14

The sum production function uses two areas of sales of equal size and multiplies

Â the numbers in the first array by the corresponding numbers in the second array.

Â 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 sum product simply sums all of those products.

Â So the formula SUMPRODUCT(C9:D9,C10:D10) is exactly the same as

Â the formula C9*C10+D9*D10.

Â And you'll get this same optimization result no matter which one you use.

Â However, there's some product formulas that are 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 are 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 variables values,

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

Â Of course, we cannot use just any values in the blue cells, but

Â only those that do not require more resources to produce than what we have.

Â 9:58

Now that we are done with the decision variables and

Â the objective function, it is time to move on to the constraints.

Â The main constraints in our model express the limited availability of three

Â production resources.

Â Frame Manufacturing hours, Wheels 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:21

[TYPING]...

Â OK 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 QA and Packaging hours.

Â If you look at the algebraic formulations 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 fallen structure.

Â Expression on the left hand sign 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 Let's go the cell E-15 and put in the formula

Â that will calculate the number of Wheels and Deck Assembly hours required for

Â any pair of decision variables in cells C-10 and D-10.

Â Just like in the similar calculations 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 of C10:D10, and C15:D15.

Â 12:44

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 QA and

Â Packaging hours required by the production plan in

Â cells C10 and D10, we will put in the formula,

Â SUMPRODUCT of C10:D10, and C16:D16.

Â As we see the current production plan,

Â where [INAUDIBLE] requires 900 hours of this resource.

Â 13:28

Okay, what if we have thousands of different resources to keep track of?

Â Do we have to keep going into cells in the E column one by one and

Â type the SUMPRODUCT formula?

Â Fortunately, Excel provides a way of avoiding this by using copy and paste and

Â the so called absolute cell referencing or cell anchoring.

Â Let's compare the formula in the cell E14 to the formulas in the cell E15 and

Â E16 one more time.

Â 14:26

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.

Â 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 the Windows shortcut F4.

Â 15:58

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

Â the resource consumption of one resource.

Â Incur whatever 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 above the absolute cell referencing using Excel Help.

Â 16:31

Let's also use a visual queue to indicate that what we want in the optimization

Â is that the values in the cells E14, E15 and E16.

Â To be less than or equal to the values in the cells G14 G15, and G16, so

Â we put the less than equal signs here.

Â This is just a visual embellishment to make sure we can

Â read our file in a easy manner.

Â 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 cell C10 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 they produced 500 units of each model,

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

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

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

Â Let's try produce 500 Razor scooters, but 750 Navajo scooters.

Â Our profit jumps to $195,000, but unfortunately,

Â we're run 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 hundred Navajo scooters.

Â So our profit goes down to 171,000 and

Â our required number of hours stays within the liable 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 are trying to do it manually,

Â let me spend to man some other 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 could be impossible for 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 thorough job

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

Â So let's bring in the Solver.

Â Let's go to Data, click on Solver,

Â the Solver parameters dialog comes up here in the Set Objective part.

Â We click on the cell selection tool and

Â choose the cell F10 to represent our objective.

Â As you can see, the Solver can maximize or minimize the objective.

Â 19:31

Minimization could be helpful if you're 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 the Zooter problem we maximize profit.

Â So we check max option.

Â Next, we use by changing variables 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 solve where the constraints are.

Â We use subject to the constraints apart and

Â click Add, the dialogue Add Constraint appears.

Â In this dialogue, we use cell reference to select Cells E14,

Â E15, and E16 on the left-hand side of the constraint.

Â 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're instructing the Solver to make sure that E14 does not exceed G14,

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

Â Know that we can also specify constraint of equal type or

Â greater equal type and there are other choices here.

Â We'll use one of them later, so

Â let's select our less or equal than sign, let's click OK.

Â You will see that this constraint is added.

Â 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 again click on Add,

Â aelect our decision variable, C10 and D10,

Â and then in the drop down menu, select I and T options.

Â Option, 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 plan.

Â 22:45

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

Â process rather than on distinctions between linear and non-linear models.

Â So I would leave this solving method as GRG Non-Linear.

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

Â with many different kinds of models, both linear and non-linear.

Â A word of caution, if you select the GRG non linear method, you do not have to

Â worry about whether your model is linear or non-linear which is a good thing.

Â 23:52

Okay, the last stop before optimizing, let's go to Options and

Â make sure that the Ignore Integer 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 a solution

Â is displayed in the dialog that appears and it does.

Â 24:28

Solver recommends producing 840 units of Razor scooters and

Â 450 units of Navajo scooters if Zooter wants to maximize its profit,

Â given the resources the company has.

Â The corresponding profit value is $198,000.

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

Â This time the solver came up with a message,

Â Solver found a solution, this is the message we want to see.

Â I would like to mention two other messengers 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 dialog and wipe out all of our constraints,

Â and then try to optimize our production plan.

Â So we go here and we 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.

Â 25:57

This three constraints should be less or equal than this three

Â values, okay.

Â And, let's add integer constraints on our decision variables, so

Â we'll select this and we'll click Int, Integer.

Â And we're back, let's click Solve just to make sure that we get everything right.

Â Here's our optimal solution.

Â 26:22

Okay, now let's add 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 plan not to exceed 5,610.

Â Suppose we had a constraint that also requires the number of used frame

Â manufacturing hours to be at least 6,000.

Â Of course, these two constraints cannot be satisfied at the same time.

Â But let's see what the Solver 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 frame manufacturing

Â should be at least greater or equal than 6,000.

Â 27:13

It's an incompatible constraint with our other constraints,

Â but let's see what this overreaction would be.

Â We'll 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 we are 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 that everything is fine.

Â Let's delete this [INAUDIBLE] constraint.

Â Let's click on Solve, 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's optimization problem.

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

Â setting up spreadsheet optimization models in Excel.

Â They use update some product function and

Â they 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 main three components of an optimization model.

Â Decision variables, the objective function, and constraints.

Â If you use an Excel format 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.

Â 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, solo will not be able to handle such problems.

Â But, commercial software packages that are powerful enough to deal with problems of

Â this size, will still operate using decision variable, objective function,

Â and constraints just like the solver.

Â