8.2.6 An Introduction to Linear Optimization - Video 4 Solving the Problem

In this video, we'll solve our linear optimization problem in the software LibreOffice. LibreOffice is similar to Microsoft Excel, but it's an open source software, and is available for free on the internet. Another option we could use is OpenOffice. You're welcome to use Excel, OpenOffice, or LibreOffice in this course, and whenever we mention LibreOffice, keep in mind that you could be using one of the other softwares instead. For more information about the options, see the download instructions on edX. You should have already downloaded and installed LibreOffice. If not, follow the instructions on edX before continuing with this video. Go ahead and open the file, Week9_AirlineRM.ods. I've already set up the data for our problem and places for us to build our decisions, our objective, and our constraints. The decisions are highlighted in yellow. These are the number of regular seats to sell, and the number of discount seats to sell. We'll just leave these cells blank for now, since the solver will be finding the optimal values. Our objective, which we saw in the previous video, is to maximize total revenue. Let's go ahead and build the objective in this blue cell. It should equal the price of regular seats, times the number of regular seats we sell, plus the price the discount seats, times the number of discount seats we sell. Go ahead and hit Enter. You should see 0 in this cell. That's because right now, we're not selling any seats. Our decision cells are blank. This could be a little tedious if we had more than two decisions. To make it easier, we can use a nice function called sumproduct to build our objective. So go ahead and clear the objective. Now in the objective cell, let's type equals, and then sumproduct, and then in parentheses, select the two prices, type a semicolon, and then select the two seats. This will multiply the first price times the first decision variable, and the second price times the second decision variable, and add them up. Close the parentheses and hit Enter. Note that if you're using Excel, you should use a comma instead of a semicolon. We should again see 0 in our objective. This is going to have the exact same value it did before. Now let's construct our constraints. The first constraint is the capacity constraint. The green table here allows us to easily write out our constraints in terms of what's on the left-hand side, LHS, what the sign is, like equals, less than or equals, or greater than or equals, and what's on the right-hand side, or RHS, of the constraint. So for the capacity constraint, the left-hand side is equal to the number of regular seats plus the number of discount seats. The sign is less than or equals, and the right-hand side is 166, the capacity of our aircraft. The regular demand constraint should be the regular number of seats, which should be less than or equal to the regular demand, which equals 100. The discount demand should be the number of discount seats, which should be less than or equal to the demand, which is equal to 150. Note here that whenever I pick the seats or the demand, I pick those cells up on the top. That's because if we want to change our demand, we could easily change it up at the top, and all of our constraints will change too. Now, let's add in our non-negativity constraints. So the number of regular seats should be greater than or equal to 0, and the number of discount seats should be greater than or equal to 0. Now we're ready to solve our problem. To do this, we just go to the Tools menu in LibreOffice and select Solver. Now we need to fill in the information about our problem. The "Target cell" should be the objective. So with the blinking cursor in the target cell, select the objective cell. We should also be selecting "Maximum", since we're trying to maximize the total revenue. The area called "By changing cells" should be our decision variables, so go ahead and select that blank area, and select the decision variables. The "Limiting conditions" are our constraints. The "Cell reference" should be the left-hand side of the constraint, the "Operator" is the sign, and the "Value" is the right-hand side. For constraints with the same sign, if they're in a row, we could select them at once to be more efficient. So first, let's select the first three less than or equal to constraints. We want to make sure the operator is less than or equal to, the integer and binary options you see here, we'll explain next week, and the value should be the right-hand side of these constraints. Then we need to add in the greater than or equal to constraints. So select the two left-hand sides. The operator should be greater than or equal to, and the value should be the two right-hand sides. The last thing we want to do is in Options, make sure that the LibreOffice Linear Solver is selected. Click OK, and then hit Solve. The solving result should say: "Solving successfully finished. Result: 77,408". This is the objective of our optimal solution, and is the total revenue we get. Go ahead and click Keep Result. And now back in our spreadsheet, we can see that our solution is to sell 100 regular seats and 66 discount seats. You may be thinking that you could have done this without the Solver. But when the problems become more complicated, it's very difficult and often impossible to solve them by hand. We'll make our problem more complicated later in the lecture, and solve it in LibreOffice.