digest
Welcome to Highline BI 348 class, video number 63. If you want to download this workbook, BI 348 Chapter 8, and follow along, click on the link below the video. We're still in Chapters 8 and 9, doing linear programming with Excel Solver, and this is our last example. JAL Boomerangs, a manufacturer, is trying to decide between four projects, and JAL Boomerangs has limited resources for the next four years.
Here is our criteria for choosing a project: net present value. 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 that all of the projects have positive net present value, so we'd like to take them all. But here are the constraints for each year. Year one, two, three, and four are the capital requirements for each project. We also have constraints for each year: years one to four, we're limited to 40, 50, 40, and 35 thousand 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: one or zero. In all of our prerequisite classes we've talked about true and false, ones and zeroes, Boolean, and binary. Here it's going to be the same: one is true, zero is false.
So I'm going to define a binary variable for each of the four projects: a new CNC router machine, a potential project; a new delivery van; a research project; and new paint room equipment. We are going to designate each one of these: C for CNC router, V for new van, R for new research project, and P for new painting project. This is binary, so it's going to be 0 and 1. I'm going to start out with dummy data and put a 1, so I'll press Ctrl+Enter. That means if we were to multiply 1 times each one of these values, we would be taking all of the values. In our linear programming Excel Solver, if one of them or more than one of them comes out zero, it means we're not going to take that project.
We're going to start out with one. That leads to the objective function, and the math 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: when it's 1, we multiply and add it to the rest, so we get the amount; if it's 0, it's like off, and that becomes 0, so we're not taking that project. We do that for each variable: amount times binary variable, and so on.
To do that as our objective function, the SUMPRODUCT function is a perfect fit. It multiplies corresponding elements and then adds them. Equal SUMPRODUCT, array 1 as one row by four columns, comma, array 2 as one row by four columns, close parentheses, and Ctrl+Enter. To show how this works, if I change one of these to a 0, our objective function instantly updates. You could prove this to yourself by highlighting these two sets of values, holding Ctrl, and then highlighting 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. I'm going to delete these, since they shouldn't be here. Each constraint is going to be similar to our objective function: the amount for the first project, project C, in year 1, times the binary variable; plus the amount from project V in year 1, times its binary variable; and so on. The binary variable is an on-off switch. If we simply added these up and they were all one, it would be exactly the same, but as soon as we change one to zero, for example project R, the term becomes zero in our constraint function.
So I'm going to click in the top cell and enter equals SUMPRODUCT, because we're multiplying four cells to the left, arranged as one row by four columns, comma. This range is one row by four columns, but we need to lock it with the F4 key, because when we copy down we need to stay locked on our decision variable, the binary variable. Close parentheses, Ctrl+Enter, and copy it down.
Now we're ready to go. We go to our objective function cell, Data, Solver, and set the objective: that's our objective function. We definitely want to maximize by changing these binary variables, all ones right now, though we'll see what they change to. Make unconstrained variables non-negative, and use the Simplex LP method. Those are the settings we've been using all along. Let's click Add.
This is convenient: all of our constraint functions and all of the right-hand-side constraints have exactly the same comparison operator, so we can enter them as an array. Constraint functions, less than or equal to, and the right-hand-side constraints as an array. Click Add.
We have a very important constraint to define next: the decision variables. Guess what? They have to be binary, 1 or 0. Now I can click Add, and then Cancel. There is my entire setup: objective function, decision variables, and constraints. Click Solve.
Solver found a solution. I click OK. Our new paint room equipment doesn't look like we can do it at this time, but the CNC router, the new van, and the research project we can do. The values here reflect that we're only taking these projects. Similarly, down here, if we add up the members, we highlight and look down in the status bar: 27,000. There it is.
In this video we saw how to use a binary variable in linear programming with Excel Solver for a limited resource problem. That's it for Chapters 8 and 9. We'll see you in the next video.